Why a NaN variable is not equal to Double.NaN?

Newbie & Debugging Questions
#21

In SQL, NULL pretty much behaves like NaN in Java.

[icode]SELECT NULL = NULL[/icode] -> [icode]NULL[/icode]
[icode]SELECT NULL != NULL[/icode] -> [icode]NULL[/icode]
[icode]SELECT NULL <> NULL[/icode] -> [icode]NULL[/icode]
[icode]SELECT NULL <=> NULL[/icode] -> [icode]1[/icode] (true)
[icode]SELECT NULL IS NULL[/icode] -> [icode]1[/icode] (true)
[icode]SELECT NULL IS NOT NULL[/icode] -> [icode]0[/icode] (false)
[icode]SELECT NULL = 13[/icode] -> [icode]NULL[/icode]
[icode]SELECT NULL <=> 13[/icode] -> [icode]0[/icode] (false)

You can equally reason that [icode]null[/icode] is nothing, unknown, and cannot be compared with another unknown.

The difference, however is, that in Java, NaN is a range of values, and null is basically the integer zero (for the VM). In theory the VM could have been implemented that a null value would be defined as any value below 1, but the language designers were sane enough to follow C’s example, as opposed to SQL’s.

One advantage of SQL’s NULL is that you can have unlimited NULLs in a set (as used by UNIQUE INDEX).

One advantage of Java’s NaN is that it’s ‘hardware accelerated’ (x87)

Slightly more on topic: it would be nice to have (additional) floating point types in Java that raised exceptions when the result of an operation was NaN, as to avoid silent errors. Just like dividing by zero for integers.

#22

Indeed, and the point is valid. I was just discussing the reasoning behind the Java devs choice to implement Nan as they did. When it comes to SQL implementation, that’s something directed by a body external to Oracle so there isn’t really a real choice of implementation in that instance.

I guess the best way I can describe my thinking on it is this: Null completely defines what something is whereas Nan partially defines what something isn’t. It’s an interesting discussion either way; I can’t say I’ve ever given much thought to the reasoning behind the implementation until now. It reminds me of this video discussing the foibles in various language implementations. :slight_smile:

#23

It’s beginning to look like it’s a binary-compatibility thing with the IEEE standards for NaN representation in binary that’s made it work this way. If that’s how they’ve had to implement NaN in hardware, then so be it.

Cas :slight_smile:

#24

Short answer: If NaNs were to compare as equal in no cases would the number of comparisons decrease and many cases the number would increase.

consider two variable a & b, all the comparisons except “!=” which result in ‘true’ tell you a & b are ordered and therefore neither are NaNs.

#25

Please, use, commas, :point:

#26

I still think it would have been reasonable to use a single binary value for NaN and thus allow == to work “as expected”. <, >, != could all be intrinsically designed to work with it too.

Cas :slight_smile: