4 ms·

SQL NULLs are not weird once you consider how you want relational logic to work when they is a record with non-existent values.

grahamlee 2y ago
Exactly this. SQL is based on the relational algebra and that's well-defined, NULL along with other features of SQL work in an entirely regular and predictable way. The only time it's weird is when a developer decides that it should work the way Javascript (or whatever) NULLs work because that's the last time they saw the same word used in a programming language, in which case it's the assumption that's weird.
- setr 2y ago
  The part that’s weird with nulls is that it’s a trinary logic stuffed into a boolean algebra. The use of x = NULL instead of x IS NULL is pretty much always a mistake. More importantly, x = value instead of (x = value and x IS NOT NULL) is almost always a mistake, and a stupidly subtle one at that. And for this curse, we get… nothing particularly useful from these semantics. Also the x != NULL case is completely cursed
  - grahamlee 2y ago
    > The part that’s weird with nulls is that it’s a trinary logic stuffed into a boolean algebra. It's a three-valued logic (though not trinary, which would use a base-3 number system) in a three-valued algebra: specifically, the relational algebra. The outcome of a logical test has three values: true, false, or NULL; this is distinct from Boole's algebra where outcomes have a continuous value between 0 and 1 inclusive.
- tzs 2y ago
  That's not the only time it is weird. There's even a whole book by one of the pioneers of the relational DB model, Date's "Database Technology: Nulls Considered Harmful" [1], covering many of the ways it is weird. [1] https://www.amazon.com/Database-Technology-Nulls-Considered-Harmful/dp/1634624769 https://www.amazon.com/Database-Technology-Nulls-Considered-...
whstl 2y ago
Agreed. I will die on the hill that regular C-like nulls are the actual thing that's weird. The real billion dollar mistake [1] was the damage it made on the minds of developers. [1] https://en.wikipedia.org/wiki/Tony_Hoare https://en.wikipedia.org/wiki/Tony_Hoare
- poincaredisk 2y ago
  We should start adjusting that billion for inflation.
- chuckadams 2y ago
  Even null in programming languages isn't so bad if it's a distinct type. The problem with null in languages like Java is that null is part of every reference type (C's pointers are another world of broken, null being basically just another unsafe invalid address). Most languages nowadays do get nulls right, even PHP of all things.
  - PhilipRoman 2y ago
    >unsafe invalid address Ironically NULL is probably the safest pointer value in C, as any dereferences from it (and thousands of pages that follow it) are guaranteed to crash.
    - bregma 2y ago
      Well, that's a problem on many of the devices I've seen: zero is a valid memory address and dereferencing it does not cause any kind of crash. In fact some hardware requires reading or maybe even writing to that address. In an age of virtual memory there's no reason why zero should cause a crash and it wastes an entire page of memory for every application to make that happen, if it does.
      - PhilipRoman 2y ago
        I haven't had the chance to work on any MMU-less devices, but I don't quite follow your remark about wasting a page. Crashing is just the default behaviour in the absence of a mapping and consumes no resources.
dataflow 2y ago
> SQL NULLs are not weird once you consider how you want relational logic to work when they is a record with non-existent values. Could you explain how this makes sense then? SELECT ... WHERE NULL If NULL is just "unknown" then shouldn't this be a type error? Moreover, could you explain why the heck this ought to be empty? WITH T AS (SELECT 1 AS C1, NULL AS C2) SELECT C1, C2 FROM T INNER JOIN T USING (C1, C2); As a human this looks insane to me, "relational algebra" be damned. You find a row, then you look it up again, and it's not there? What the hell?

grahamlee 2y ago

Exactly this. SQL is based on the relational algebra and that's well-defined, NULL along with other features of SQL work in an entirely regular and predictable way. The only time it's weird is when a developer decides that it should work the way Javascript (or whatever) NULLs work because that's the last time they saw the same word used in a programming language, in which case it's the assumption that's weird.

setr 2y ago
The part that’s weird with nulls is that it’s a trinary logic stuffed into a boolean algebra. The use of x = NULL instead of x IS NULL is pretty much always a mistake. More importantly, x = value instead of (x = value and x IS NOT NULL) is almost always a mistake, and a stupidly subtle one at that. And for this curse, we get… nothing particularly useful from these semantics. Also the x != NULL case is completely cursed
- grahamlee 2y ago
  > The part that’s weird with nulls is that it’s a trinary logic stuffed into a boolean algebra. It's a three-valued logic (though not trinary, which would use a base-3 number system) in a three-valued algebra: specifically, the relational algebra. The outcome of a logical test has three values: true, false, or NULL; this is distinct from Boole's algebra where outcomes have a continuous value between 0 and 1 inclusive.
tzs 2y ago
That's not the only time it is weird. There's even a whole book by one of the pioneers of the relational DB model, Date's "Database Technology: Nulls Considered Harmful" [1], covering many of the ways it is weird. [1] https://www.amazon.com/Database-Technology-Nulls-Considered-Harmful/dp/1634624769 https://www.amazon.com/Database-Technology-Nulls-Considered-...

whstl 2y ago

Agreed. I will die on the hill that regular C-like nulls are the actual thing that's weird. The real billion dollar mistake [1] was the damage it made on the minds of developers. [1] https://en.wikipedia.org/wiki/Tony_Hoare https://en.wikipedia.org/wiki/Tony_Hoare

poincaredisk 2y ago
We should start adjusting that billion for inflation.
chuckadams 2y ago
Even null in programming languages isn't so bad if it's a distinct type. The problem with null in languages like Java is that null is part of every reference type (C's pointers are another world of broken, null being basically just another unsafe invalid address). Most languages nowadays do get nulls right, even PHP of all things.
- PhilipRoman 2y ago
  >unsafe invalid address Ironically NULL is probably the safest pointer value in C, as any dereferences from it (and thousands of pages that follow it) are guaranteed to crash.
  - bregma 2y ago
    Well, that's a problem on many of the devices I've seen: zero is a valid memory address and dereferencing it does not cause any kind of crash. In fact some hardware requires reading or maybe even writing to that address. In an age of virtual memory there's no reason why zero should cause a crash and it wastes an entire page of memory for every application to make that happen, if it does.
    - PhilipRoman 2y ago
      I haven't had the chance to work on any MMU-less devices, but I don't quite follow your remark about wasting a page. Crashing is just the default behaviour in the absence of a mapping and consumes no resources.

dataflow 2y ago

> SQL NULLs are not weird once you consider how you want relational logic to work when they is a record with non-existent values. Could you explain how this makes sense then? SELECT ... WHERE NULL If NULL is just "unknown" then shouldn't this be a type error? Moreover, could you explain why the heck this ought to be empty? WITH T AS (SELECT 1 AS C1, NULL AS C2) SELECT C1, C2 FROM T INNER JOIN T USING (C1, C2); As a human this looks insane to me, "relational algebra" be damned. You find a row, then you look it up again, and it's not there? What the hell?