7 ms·

Interesting that NULL = NULL is Unknown. I'm not a fan of random values trashing my equivalence relations (looking at you, javascript NaN), but I'm sure there a

Interesting that NULL = NULL is Unknown. I'm not a fan of random values trashing my equivalence relations (looking at you, javascript NaN), but I'm sure there are practical reasons.

Sharlin 9y ago
It's exactly what NULL is about: the NULL value signifies "unknown", and as soon as you have an unknown value on either side of a relational operator, the result must also be unknown.
IncRnd 9y ago
Just because you aren't aware of the value A or the value B, that doesn't mean that A is the same as B.
- SigmundA 9y ago
  Having done SQL development for a long time I think while that is technically correct in practice it causes too many issues and is a mistake. Many values in a database are approximate especially if representing real world values, but in our logic we treat them as equal even if they really aren't exactly equal, because that's practical. At least GROUP BY / aggreagates treats null equal, this was done for practicality and is a special exception which drives home the point. In the end x == y OR (x IS NULL AND y IS NULL) simply wastes my time and adds no value to me along with the other issues that come with not simply allowing x == y checks like every normal programming language I use.
  - IncRnd 9y ago
    Without a doubt. The root of many difficulties is SQL-X using NULL as a Not Set State instead of as a Value. However, Value representations have their own issues, which can be seen in vulnerabilities that have occurred in non-SQL where the corresponding checks had not been performed. In response to this is error based exceptions. Tradeoffs that we all live with.
nalllar 9y ago
You can use `A IS NOT DISTINCT FROM B` to compare if you want to treat NULLs as equal instead of unknown. Unfortunately it is poorly supported. See http://modern-sql.com/feature/is-distinct-from http://modern-sql.com/feature/is-distinct-from
dagw 9y ago
looking at you, javascript NaN That's not a JavaScript thing, it's an IEEE 754 floating point standard thing and is the same in every programming language that implements that standard (which is basically all of them). But for some reason only JavaScript developers seem to be bothered by this. Furthermore IEEE 754 floating point numbers don't have many properties that you'd expect from 'real' numbers. For example addition is not always associative and the distributive law does not always hold.
- deleted 9y ago
  [deleted]
- vorotato 9y ago
  it's because javascript is unityped.
  - sgustard 9y ago
    var x; 0+x => NaN is the gateway to frustration.
- schindlabua 9y ago
  I understand the motivation but I disagree that NaN should not equal NaN. The es docs even explicily mention that "to ECMAScript code, all NaN values are indistinguishable from each other." I'm writing javascript and I don't want to need to care about IEEE 754 floating point (all I know about is `Number` anyway). If you compare two NaNs in C that happen to have the same binary representation, they are equal. Which means that a typical javascript runtime will need to check for that case, no? So if they have to make the decision between making all NaNs equal or unequal, and there's going to be a runtime check either way, why not make NaN == NaN and save us a headache?

Interesting that NULL = NULL is Unknown. I'm not a fan of random values trashing my equivalence relations (looking at you, javascript NaN), but I'm sure there are practical reasons.

Sharlin 9y ago

It's exactly what NULL is about: the NULL value signifies "unknown", and as soon as you have an unknown value on either side of a relational operator, the result must also be unknown.

IncRnd 9y ago

Just because you aren't aware of the value A or the value B, that doesn't mean that A is the same as B.

SigmundA 9y ago
Having done SQL development for a long time I think while that is technically correct in practice it causes too many issues and is a mistake. Many values in a database are approximate especially if representing real world values, but in our logic we treat them as equal even if they really aren't exactly equal, because that's practical. At least GROUP BY / aggreagates treats null equal, this was done for practicality and is a special exception which drives home the point. In the end x == y OR (x IS NULL AND y IS NULL) simply wastes my time and adds no value to me along with the other issues that come with not simply allowing x == y checks like every normal programming language I use.
- IncRnd 9y ago
  Without a doubt. The root of many difficulties is SQL-X using NULL as a Not Set State instead of as a Value. However, Value representations have their own issues, which can be seen in vulnerabilities that have occurred in non-SQL where the corresponding checks had not been performed. In response to this is error based exceptions. Tradeoffs that we all live with.

nalllar 9y ago

You can use `A IS NOT DISTINCT FROM B` to compare if you want to treat NULLs as equal instead of unknown. Unfortunately it is poorly supported. See http://modern-sql.com/feature/is-distinct-from http://modern-sql.com/feature/is-distinct-from

dagw 9y ago

looking at you, javascript NaN That's not a JavaScript thing, it's an IEEE 754 floating point standard thing and is the same in every programming language that implements that standard (which is basically all of them). But for some reason only JavaScript developers seem to be bothered by this. Furthermore IEEE 754 floating point numbers don't have many properties that you'd expect from 'real' numbers. For example addition is not always associative and the distributive law does not always hold.

deleted 9y ago
[deleted]
vorotato 9y ago
it's because javascript is unityped.
- sgustard 9y ago
  var x; 0+x => NaN is the gateway to frustration.
schindlabua 9y ago
I understand the motivation but I disagree that NaN should not equal NaN. The es docs even explicily mention that "to ECMAScript code, all NaN values are indistinguishable from each other." I'm writing javascript and I don't want to need to care about IEEE 754 floating point (all I know about is `Number` anyway). If you compare two NaNs in C that happen to have the same binary representation, they are equal. Which means that a typical javascript runtime will need to check for that case, no? So if they have to make the decision between making all NaNs equal or unequal, and there's going to be a runtime check either way, why not make NaN == NaN and save us a headache?