Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

SQL nulls are weird

jirevwe.github.io

Post reply on HN
Print view View original

31–40 of 293 posts

Re: SQL nulls are weird

#31

Post by setr »

grahamlee wrote: post #11
datadrivenangel wrote: post #7

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

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.

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

Re: SQL nulls are weird

#32

Post by bunderbunder »

> ... and this is even less obvious if you’re used to using ORMs.

Which is why I continue to be such an ORM skeptic. I agree that they're convenient. But I do worry that we've now got an entire generation of engineers who regularly interact with relational databases, but have largely been spared the effort of learning how they actually work.

As another commenter pointed out, if you've learned basic relational algebra then the way SQL nulls behave seems obvious and logically consistent. The logic is the same as the logic behind the comparison rules for NaN in IEEE floats. It's the behavior of C-style nulls that is, always and forever, a billion-dollar mistake.

Re: SQL nulls are weird

#33

Post by poincaredisk »

whstl wrote: post #24
datadrivenangel wrote: post #7

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

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

We should start adjusting that billion for inflation.

Re: SQL nulls are weird

#34

Post by mrkeen »

hiAndrewQuinn wrote: post #27

SQL NULLs aren't weird , they're just based off of Kleene's TRUE-FALSE-UNKNOWN logic! If you want you can read NULL as UNKNOWN and suddenly a whole bunch of operations involving them become a lot more intuitive: 1. TRUE OR UNKNOWN = TRUE, because you know you have at least one TRUE already. 2. TRUE AND UNKNOWN = UNKNOWN, because you don't know whether you have two TRUEs or not. It's just out there. 3. UNKNOWN XOR UNK…

It's unfortunate to namesquat on 'boolean' if your elements have three possible values. Just call it 'ternary logic' and let individuals decide which system to use.

Re: SQL nulls are weird

#35

Post by chuckadams »

mrkeen wrote: post #34
hiAndrewQuinn wrote: post #27

SQL NULLs aren't weird , they're just based off of Kleene's TRUE-FALSE-UNKNOWN logic! If you want you can read NULL as UNKNOWN and suddenly a whole bunch of operations involving them become a lot more intuitive: 1. TRUE OR UNKNOWN = TRUE, because you know you have at least one TRUE already. 2. TRUE AND UNKNOWN = UNKNOWN, because you don't know whether you have two TRUEs or not. It's just out there. 3. UNKNOWN XOR UNK…

It's unfortunate to namesquat on 'boolean' if your elements have three possible values. Just call it 'ternary logic' and let individuals decide which system to use.

Maybe GP was edited, but it doesn't use the word "boolean" anywhere.

Re: SQL nulls are weird

#36

Post by [deleted] »

chuckadams wrote: post #35
mrkeen wrote: post #34

Earlier quoted context omitted.

It's unfortunate to namesquat on 'boolean' if your elements have three possible values. Just call it 'ternary logic' and let individuals decide which system to use.

Maybe GP was edited, but it doesn't use the word "boolean" anywhere.

[deleted]

Re: SQL nulls are weird

#37

Post by hiAndrewQuinn »

chuckadams wrote: post #35
mrkeen wrote: post #34

Earlier quoted context omitted.

It's unfortunate to namesquat on 'boolean' if your elements have three possible values. Just call it 'ternary logic' and let individuals decide which system to use.

Maybe GP was edited, but it doesn't use the word "boolean" anywhere.

Correct, I edited "boolean" out prior to ^^P's comment. My apologies.

Re: SQL nulls are weird

#38

Post by chuckadams »

whstl wrote: post #24
datadrivenangel wrote: post #7

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

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

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.

Re: SQL nulls are weird

#39

Post by homebrewer »

throwaway10235 wrote: post #21
demurgos wrote: post #15

Earlier quoted context omitted.

The main pain point for smaller apps is that every major Postgres version requires an explicit migration of the underlying data representation. I get why it's there, but for simpler apps I would appreciate a flag to do it transparently.

I'm not sure what you mean. I have migrated versions without having to update any applications that connects to it? Maybe it is a driver specific issue? I have used Python/Java, and haven't updated any of my code or dependencies because of a major Postgre update

It requires manual interventions because the upgrade process is basically dump + restore. MySQL and MariaDB upgrade between major versions automatically — you simply install the next version (or change the version tag in your container definition) and restart the server.

Usually it takes almost no time, altought might be just as slow as PG when major changes to the data format are introduced. The only example I can remember is 8.0 when oracle completely rewrote the data format (making things like atomic ddl possible).

Re: SQL nulls are weird

#40

Post by homebrewer »

stronglikedan wrote: post #9
NoMoreNicksLeft wrote: post #6

Earlier quoted context omitted.

>also that since Postgresql 15, you can use `NULLS NOT DISTINCT` when creating a unique index [0]. I'm less familiar with other databases. Why would anyone want to use another database?

Simplicity. PG is often overkill for simple apps, where MySQL/Maria/et al is easier to maintain, or even SQLite for very simple apps where zero DB maintenance is preferable.

Also the reverse — MySQL et al support much more complex replication topologies out of the box, including multi master for the very rare use case when you need it.

It's also much easier to tune, most database instances require setting innodb_buffer_pool_size, and that's basically it. Newer versions can even set it automatically if you're fine with consuming all memory on that machine, thus requiring no tuning at all.

Post reply on HN