Unexpected behaviour of Max(<float column>) if first row contains NAN [CORE2160]

[firebird-automations]

Submitted by: Paul Faid (pfaid)

Attachments: maxnantest.fbk

I have a float column which contains some NANs as well as some valid numbers. When I use MAX(column) sometimes I get NAN and sometimes I get the value I wanted. After playing around the only difference I can see is what value is in the first record, NAN or a valid number.

If the column value in the first record is a valid number , max(column) behaves as I expect and returns the the highest value in the column that is not NAN If the column value in the first record is NAN, max(column) returns NAN, irrespective of what else is in the column.

I'll try and attach a db backup which helps illustrate the problem.

The sql I'm using is 'SELECT Max(CapacityRemaining) FROM Datalog WHERE RpsId = 270. The result = NAN, but if you edit the first record for rpsid:270 and change the NAN to a valid number the query outcome is different. I've left a selection of other values in the datalog table and by deleting rows you can get NAN's to the first row for any given rpsid.

[firebird-automations]

Commented by: Paul Faid (pfaid)

I've attached a db backup that helps illustrate the problem.

[firebird-automations]

Modified by: Paul Faid (pfaid)

Attachment: maxnantest.fbk [ 11141 ]

[firebird-automations]

Commented by: Sean Leyne (seanleyne)

If you mean by "NAN" that the value is "NULL" (which is the correct SQL term), then what you are seeing is functionality which <B>explicitly meets the SQL Standard</B>.

"NULL" is not a value but a state, as such any operation/function applied to a NULL value will always yield a NULL. So, a SUM of a field which has 1 or more NULL value will always return NULL.

[firebird-automations]

Modified by: Sean Leyne (seanleyne)

status: Open [ 1 ] => Resolved [ 5 ]

resolution: Won't Fix [ 2 ]

[firebird-automations]

Commented by: @asfernandes

Sean, NaN == Not a Number (floating point term).

[firebird-automations]

Commented by: Philip Williams (unordained)

a) this report was for max(), not sum() b) please refer to the Firebird NULL Guide, as it explicitly states that aggregates such as min, max, sum, and avg consider only the non-null values! (http://www.firebirdsql.org/pdfmanual/Firebird-Null-Guide.pdf on pages 20 and 21) c) the bug report indicates that the physical order of the rows matters, which should never be the case (unless we're talking about FIRST 1 without an ORDER BY clause)

[firebird-automations]

Commented by: @asfernandes

The report is valid, see my comment. Depending on the order, NaNs are considered as MAX or not.

[firebird-automations]

Modified by: @asfernandes

status: Resolved [ 5 ] => Reopened [ 4 ]

resolution: Won't Fix [ 2 ] =>

[firebird-automations]

Commented by: Smirnoff Serg (wildsery)

I realized that Firebird knows nothing about NaN, and try process it like common float, that's why such artefacts appears. Author may use NULL instead of NaN, substitute while insert params assigning.

[firebird-automations]

Commented by: Paul Faid (pfaid)

Further to this report, when I'm using the Firebird embedded server, any sql which tries to interpret the NAN values causes an exception in my Delphi application: EInvalidOp: 'Invalid floating point operation'.

The following SQL statements cause an exception if there are any NAN's in CapacityRemaining: 'UPDATE datalog set AhDischarged = CapacityRemaining where CapacityRemaining > -1' or 'SELECT Max(CapacityRemaining) FROM Datalog WHERE RpsId = 270'

I get around this problem by pulling all the values back into my Delphi app and processing them as Delphi Singles (only when using the embedded server)

Regards Paul (This comment only applies to the embedded server 1.5.4, I haven't tested this with any later versions)