3 ms·

My bigger concern regarding NULLs is that its a ternary logic shoved into a binary logic system, and it all invisibly becomes nonsense when your dataset has NUL

by setr 6y ago

My bigger concern regarding NULLs is that its a ternary logic shoved into a binary logic system, and it all invisibly becomes nonsense when your dataset has NULLs in it, and you don't explicitly address it WHERE col1 > col2 is wrong, and it'll break in terrible ways and in the face of negation + NULLs, everything falls apart[0], giving you both false positive and false negatives in your answerset, and everything will look perfectly fine. Every column being made NOT NULL is the only sane solution. [0] https://databasetheory.org/sites/default/files/2018-05/03_principles_Guagliardo.pdf https://databasetheory.org/sites/default/files/2018-05/03_pr...

exabrial 6y ago
you're not wrong... Null by itself is sort of crazy in a binary system, but I see that as more a problem with binary representing reality rather than the other way around. It's a handy abstraction in that sense but a nightmare for systems level programming.
- setr 6y ago
  The concept is useful, the implementation is not. You really just want better ergonomics for enums, and encode the many ways NULL is meant to mean that way
samatman 6y ago
This is basically the correct take, but I would say that NULL is a real and useful concept which databases should have. That implies that comparison and boolean operators should be ternary, which would be ugly and confusing: but an ugly and confusing that reflects reality. This would give us three "greater than" operators: WHERE col1 > col2 WHERE col1 ?> col2 WHERE col1 >? col2 The first is always false for NULL, the second always picks the NULL column, the third never picks the NULL column. It doesn't seem coherent to order two NULLs, so that would always be false. I'm not attached to the syntax, which is intended to be illustrative. The most important of such operators would be WHERE col1 ?= col2 which coerces two NULLs to be equivalent. It says "yes NULLs aren't comparable, but for this query, I want to treat them as equal". Because that is only usually true, not invariably so.

by setr 6y ago

exabrial 6y ago

you're not wrong... Null by itself is sort of crazy in a binary system, but I see that as more a problem with binary representing reality rather than the other way around. It's a handy abstraction in that sense but a nightmare for systems level programming.

setr 6y ago
The concept is useful, the implementation is not. You really just want better ergonomics for enums, and encode the many ways NULL is meant to mean that way

samatman 6y ago

This is basically the correct take, but I would say that NULL is a real and useful concept which databases should have. That implies that comparison and boolean operators should be ternary, which would be ugly and confusing: but an ugly and confusing that reflects reality. This would give us three "greater than" operators: WHERE col1 > col2 WHERE col1 ?> col2 WHERE col1 >? col2 The first is always false for NULL, the second always picks the NULL column, the third never picks the NULL column. It doesn't seem coherent to order two NULLs, so that would always be false. I'm not attached to the syntax, which is intended to be illustrative. The most important of such operators would be WHERE col1 ?= col2 which coerces two NULLs to be equivalent. It says "yes NULLs aren't comparable, but for this query, I want to treat them as equal". Because that is only usually true, not invariably so.