2 ms·
> 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 distinc
by masklinn 4y 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.
- feanaro 4y agoSo 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.
- irrational 4y agoOracle considers null to be equal to empty string.
- mike_hock 4y agoThat is truly huffing glue.
- irrational 4y agoAnd 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.
- layer8 4y agoThey 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.
- to11mtm 4y agoIDK 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.