Hacker News new | ask | show | jobs
by masklinn 1445 days ago
> SQL is old enough and this debate so unsettled

Is the debate really unsettled?

MSSQL considers nulls to be equal, everyone else considers them to be distinct (aside from a few DBs which just refuse to create UNIQUE indexes on nullable columns), and Oracle is apparently huffling glue in a corner.

> As an example of the irreconciliable weirdness of NULL, consider that "NULL = NULL" is false, and so is "NULL != NULL", while rows with NULL still group together in GROUP BY.

Group by is the exception here. And arguably it is so because the alternative is never useful, or close enough as to not be worth it.
2 comments
feanaro 1445 days ago
So of the three major SQL implementations, you have one of them following the results of the settled debate, one of them being an exception and one of them huffling glue (whatever that means). In addition, there's a language exception even in the settled group, which you have to handwave away.

Sounds pretty unsettled to me.

link
irrational 1445 days ago
Oracle considers null to be equal to empty string.
link
mike_hock 1445 days ago
That is truly huffing glue.
link
irrational 1445 days ago
And oracle considers null to be equal to empty string. My understanding is oracle was created before the sql standard existed and they don’t want to change because of backwards comparability. I don’t understand why they can’t add a db parameter to allow you to set how nulls are handled.
link
to11mtm 1445 days ago
IDK if you ever saw the HN thread where they talked about oracle and change tests from a few years back, but if I had to guess, the pain just isn't worth the overall cost.

Overall I remember running into this but finding it fairly simple to add a couple specific checks around the condition where it came up in our code.

link
layer8 1445 days ago
They would need to change their storage format to be able to distinguish between NULL and empty string, and probably a lot of in-memory structures and even more code logic as well. It would be hugely expensive to support both.

Oracle introduced VARCHAR2 in the 90s(?) to mean “Oracle semantics” as opposed to VARCHAR which is documented as “may change to standard SQL semantics in a future version”. However I don’t think they’ll ever materialize that distinction.

link