Commented by: Paul Faid (pfaid)

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

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.

Commented by: @asfernandes

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

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)

Commented by: @asfernandes

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

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.

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)