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

21–30 of 293 posts

Re: SQL nulls are weird

#21

Post by throwaway10235 »

demurgos wrote: post #15
lcnPylGDnU4H9OF wrote: post #14

Earlier quoted context omitted.

Why would you say MySQL/Maria/et al are easier to maintain for simple apps than PG?

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

Re: SQL nulls are weird

#22

Post by badlibrarian »

lcnPylGDnU4H9OF wrote: post #14
stronglikedan wrote: post #9

Earlier quoted context omitted.

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.

Why would you say MySQL/Maria/et al are easier to maintain for simple apps than PG?

VACUUM

Re: SQL nulls are weird

#23

Post by giraffe_lady »

zokier wrote: post #4

SQL nulls in some ways behave in similar to floating point nans. Of course nans are also weird in their own way, but it is a bit comforting that its not so completely singularly weird.

NaN is cool because it's almost like a type that constrains uncertainty. What do we know about this entity? not much! but it's definitely not a number. Calling it anything else would have been a mistake.

Null is more confusing because it means different things in different languages. Sometimes it's a more constrained uncertainty, eg this definitely doesn't exist. But in sql it's a less constrained uncertainty, like "undefined" in math. The value of this thing couldn't make sense in this context, but we can't make assertions about its existence.

Re: SQL nulls are weird

#24

Post by whstl »

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

Re: SQL nulls are weird

#25

Post by magicalhippo »

demurgos wrote: post #2

> select null = null; returns NULL, because each NULL is basically a placeholder representing any “unknown value”. Two unknown values are not necessarily the same value; we can’t say that they are equal, because we don’t know the value of either of them. Agreed with all of this, it would probably have been better if they were named `unknown` instead of reusing the `null` keyword. Note also that since Postgresql 15, y…

The result of comparisons involving NULL values can result[1][2] in UNKNOWN, and in PostgreSQL for example you can test[3] for this using IS UNKNOWN.

That said, as someone self-taught in SQL, I agree NULL was not a good choice.

Replacing NULL with UNKNOWN and the third boolean value as INDETERMINATE for example would have been better.

[1]: https://stackoverflow.com/a/79270181

[2]: https://learn.microsoft.com/en-us/sql/t-sql/language-element...

[3]: https://www.postgresql.org/docs/current/functions-comparison...

Re: SQL nulls are weird

#26

Post by wvenable »

demurgos wrote: post #19
duncan-donuts wrote: post #13

Earlier quoted context omitted.

Introducing “unknown” feels like another kind of hell like undefined in JavaScript.

Just to clarify, I'm not advocating to introduce a new `unknown` keyword. I'm saying that the existing `null` in SQL was not named properly and that the name `unknown` would have been more fitting. SQL's `null` already has the semantics of `unknown` as explained in the part of the article that I quoted.

SQL's use of "null" is probably one of the oldest instances of that concept in computing. It's exactly equivalent to unknown. That is its definition.

Re: SQL nulls are weird

#27

Post by hiAndrewQuinn »

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 UNKNOWN = UNKNOWN, because it could darn near be anything: TRUE XOR TRUE, TRUE XOR FALSE, FALSE XOR FALSE, FALSE XOR TRUE... Internalizing this is where SQL's use of NULL / UNKNOWN really becomes intuitive.

4. (TRUE AND FALSE) XOR (TRUE OR UNKNOWN) = (FALSE) XOR (TRUE) per #1 = TRUE. See, it's consistent, you just need to keep in mind that if you have a lot of known UNKNOWNs they're quite parasitic and your final outcome is likely to be, itself, an UNKNOWN. Just like in real life!

Re: SQL nulls are weird

#28

Post by giraffe_lady »

jmyeet wrote: post #3

NULL is the absence of a value. If you try and treat it as a value, you're going to have a bad time. So an attempted UNIQUE(email_address, deleted_at) constraint is fundamentally flawed. If you treated NULL as a value that could be unique, you're going to break foreign keys. But let's continue the logic of deleted_at being NULL indicating an active account, which seems to the intent here. You end up doing things like…

You put the "is not null" on the index itself and then simply don't use it for the much rarer queries that are on deleted accounts. Or just use a view for active accounts.

Overloading timestamps to carry a boolean on null is awesome as long as you decide that's what you're doing and use one of the several standard techniques to dodge the easily avoided potential downside.

This isn't a valid security concern, more than any other incorrect sql query would be anyway. A dev can always write a bad Q, you need another way to address that it's not more likely here because of the null.

Re: SQL nulls are weird

#29

Post by layer8 »

demurgos wrote: post #2

> select null = null; returns NULL, because each NULL is basically a placeholder representing any “unknown value”. Two unknown values are not necessarily the same value; we can’t say that they are equal, because we don’t know the value of either of them. Agreed with all of this, it would probably have been better if they were named `unknown` instead of reusing the `null` keyword. Note also that since Postgresql 15, y…

SQL NULL doesn’t behave like “unknown” in all contexts. That’s one issue of NULL, that it doesn’t really have consistent semantics.

Re: SQL nulls are weird

#30

Post by poincaredisk »

NoMoreNicksLeft wrote: post #6
demurgos wrote: post #2

> select null = null; returns NULL, because each NULL is basically a placeholder representing any “unknown value”. Two unknown values are not necessarily the same value; we can’t say that they are equal, because we don’t know the value of either of them. Agreed with all of this, it would probably have been better if they were named `unknown` instead of reusing the `null` keyword. Note also that since Postgresql 15, y…

>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?

* legacy applications or vendor lock-in

* use of a cloud provider that favours another database (like SQL server)

* some people claim mysql/maria is faster for them, simpler, or has a better support for replication

* use of sqlite for lightweight or portable apps

* domain specific databases that still use sql as their query language

* someone may want to use another database for fun or to learn something new

Post reply on HN