News for hackers. Stuff that matters.
Re:Postgres 15 improves UNIQUE and NULL by anamexis on Monday July 11, 2022 @08:40AM (#32055373)
> As an example of the irreconciliable weirdness of NULL, consider that "NULL = NULL" is false, and so is "NULL != NULL

This isn't quite true - these comparisons don't evaluate to false, they evaluate to NULL.

I intuitively think of NULL as "unable to compute," a generalized NaN.

Re:Postgres 15 improves UNIQUE and NULL by magicalhippo on Monday July 11, 2022 @08:51AM (#32055507)
> these comparisons don't evaluate to false, they evaluate to NULL

In the databases I've used, such as MSSQL[1], they evaluate to UNKNOWN.

[1]: https://docs.microsoft.com/en-us/sql/t-sql/language-elements...

Re:Postgres 15 improves UNIQUE and NULL by bfgoodrich on Monday July 11, 2022 @01:11PM (#32058816)
The document you link uses unknown as a synonym for null. If you inserted the result of such a comparison into a table, the value inserted would be NULL.
Re:Postgres 15 improves UNIQUE and NULL by magicalhippo on Monday July 11, 2022 @03:55PM (#32060952)
I'm self-taught, could you show how to insert the result of such a comparison into a table in mssql? I can only think of using CASE or similar, which would mean no direct conversion.
Re:Postgres 15 improves UNIQUE and NULL by orf on Monday July 11, 2022 @06:02PM (#32062590)
insert into table (c) values (null != null)
Re:Postgres 15 improves UNIQUE and NULL by magicalhippo on Monday July 11, 2022 @08:00PM (#32063854)
Can you provide a complete working example? I'm just getting "incorrect syntax near !" with both mssql and sqlanywhere.

    create table #nulltest (a bit null);
    insert into #nulltest(a) values (0),(1);
    insert into #nulltest(a) values (null != null);
Re:Postgres 15 improves UNIQUE and NULL by distalzou on Monday July 11, 2022 @10:48PM (#32064992)
Some SQL dialects don't recognize != and use <> instead
Re:Postgres 15 improves UNIQUE and NULL by magicalhippo on Monday July 11, 2022 @11:50PM (#32065386)
True, I'll blame vacation mode for not catching that one.

However, that doesn't change things, I can't get any of these to parse:

    insert into #nulltest(a) values (1 <> 0);
    insert into #nulltest(a) values (null <> null);
    insert into #nulltest(a) values (true and true);
    insert into #nulltest(a) values (null and null);
So my question remains, how do you insert an UNKNOWN?

The point of me asking is that it was my belief contrary to what was suggested, UNKNOWN is not the same as NULL and, crucially, does not evaluate as NULL.

Re:Postgres 15 improves UNIQUE and NULL by ratsmack on Monday July 11, 2022 @03:36PM (#32060724)
It's good to see that everyone agrees on the definition of NULL.
Re:Postgres 15 improves UNIQUE and NULL by DaiPlusPlus on Monday July 11, 2022 @04:01PM (#32061028)
Yes: a billion-dollar mistake.
Re:Postgres 15 improves UNIQUE and NULL by bazoom42 on Monday July 11, 2022 @05:07PM (#32061862)
This is s misunderstandig of Hoares “billion dollar mistake”. The mistake was not the existence of nulls, the mistake was type systems where every reference could be null.

Sql does not have this problem.

Re:Postgres 15 improves UNIQUE and NULL by lmm on Monday July 11, 2022 @09:26PM (#32064461)
SQL absolutely does have that problem. You have no way of enforcing or checking that an expression is supposed to be non-NULL; at best you get an error at runtime when you try to insert it into a given column, which is usually far too late.
Re:Postgres 15 improves UNIQUE and NULL by anamexis on Tuesday July 12, 2022 @09:32AM (#32069645)
This is only a problem insofar as any data constraint is a problem.
Re:Postgres 15 improves UNIQUE and NULL by lmm on Tuesday July 12, 2022 @05:53PM (#32076173)
No, it's a specific problem of SQL that you can apply constraints to stored or quasistored columns but not to expressions. In a real typed language every expression has a type.
Re:Postgres 15 improves UNIQUE and NULL by erichocean on Monday July 11, 2022 @04:46PM (#32061590)
And with RDBMSs, a multi-trillion dollar success!
Re:Postgres 15 improves UNIQUE and NULL by paulryanrogers on Monday July 11, 2022 @09:34AM (#32055912)
This is a good way to remember the SQL behavior. Though I do hope it'll change in the future. So many other languages interpret null as a comparable value, and it will be less surprising to folks coming from those.
Re:Postgres 15 improves UNIQUE and NULL by afiori on Tuesday July 12, 2022 @02:43AM (#32066427)
I personally don't have issues with languages that have incomparable values, just with languages that have a generic equality and incomparable values.

If every type has it's own equality operation (e.g. Number.eq) then I have no issue with it being weird in unexpected types (e.g. Number.eq(NULL, NULL) =false)

For example in JavaScript almost every use of === should be replaced by Object.is.

Re:Postgres 15 improves UNIQUE and NULL by goldenkey on Tuesday July 12, 2022 @05:41AM (#32067585)
I agree, however that's all fine and well but it turns the languages syntax into dogshit. A babel plugin (transpiling) would be ideal.

On the same note, the original intent was for NaN to be insidious and propagate into every derived calculation or comparison, thus making it easy to tell if a mathematical function had failed, without having to add conditionals at every step within it. Which makes a lot of sense. However, not being able to know exactly where the NaN was produced is kind of shit. Now, if NaN had a stack trace property like Error (exception) objects...it would make this behavior much more useful. Makes me wonder if the prototype of NaN can be extended, hm.

Re:Postgres 15 improves UNIQUE and NULL by afiori on Tuesday July 12, 2022 @06:02AM (#32067723)
NaNs cannot be extended in any way, in many interpreters NaN is used to "hide" pointers in 64 bit floats using the 52 free bits in NaN values.

IMO NaN should have never existed, like signed zeros, (I am more agnostic about [signed] Infinity) I think that they were implemented in IEEE 754 because the encoding had space left for them.

I not an expert in any way on this this is literally a personal opinion.

Re:Postgres 15 improves UNIQUE and NULL by goldenkey on Tuesday July 12, 2022 @06:12AM (#32067785)
Right. I was thinking that maybe NaN in JS was more than just the IEEE version we see in languages closer to the metal.
Re:Postgres 15 improves UNIQUE and NULL by Pxtl on Monday July 11, 2022 @11:08AM (#32057159)
> This isn't quite true - these comparisons don't evaluate to false, they evaluate to NULL.

Which large parts of SQL treat as equivalent to false.

    WITH vars AS (
        SELECT CAST(null AS INT) as X
    )
    SELECT 'yes this is a tautology' as "is this is a tautology"
    FROM vars
    WHERE X = X
The above query returns nothing, not an error because the value of X is unknowable. In a sane language, X=X and so it would yield "yes this is a tautology". SQL standard wants it both ways - it wants to say "boolean null isn't false" except when for its own convenience it decides that yes, null is false.

The truth, imho, is that SQL has an archaic type system. In this day and age it should be offering algebraic types where we can implement our own "empty" values and the like that may not use three-value logic as appropriate, so that I can say that for example End Date is blank instead of either unknown or Date.MaxValue.

Re:Postgres 15 improves UNIQUE and NULL by anamexis on Monday July 11, 2022 @12:00PM (#32057881)
> SQL standard wants it both ways - it wants to say "boolean null isn't false" except when for its own convenience it decides that yes, null is false.

I don't see it that way. Null cast to boolean is always false. Null equality is undefined. There is no contradiction there.

Re:Postgres 15 improves UNIQUE and NULL by saltcured on Monday July 11, 2022 @03:59PM (#32061012)
I think that this topic gets tripped up with people wanting to assume boolean means two possible states... NULL adds an extra state to every type much like the "bottom" type/value in some functional programming languages or type systems.

It's not the `=` operator that is special or significant here. It is the `WHERE` clause semantics, which are defined as returning rows when the boolean expression is true. This treats both false and null cases as the other possible outcome, when rows are not returned. Not understanding this distinction is also where people write buggy queries using WHERE NOT ..., because like equality the NOT operator will return NULL for a NULL input (so NULL is not like false and does not negate to true).

SELECT 'hidden by null result' WHERE CAST(NULL AS boolean);

SELECT 'also hidden by null result' WHERE NOT CAST(NULL AS boolean);

SELECT 'shown by true result' WHERE CAST(NULL AS boolean) IS NULL;

SELECT 'hidden by false result' WHERE NOT (CAST(NULL AS boolean) IS NULL);

The existing GROUP BY behavior is different than WHERE because it does not have a special rule for handling true values. It just returns a group for each distinct value.

The prior (new default) UNIQUE constraint behavior made sense when you think of NULL as this bottom value and meaning "unknown", while also thinking that a uniqueness constraint is only applied to complete keys. The check is effectively deferred, and the partial or incomplete tuple might become valid and unique once its missing part(s) are filled in with real values. The new optional behavior makes more sense if you think that the uniqueness constraint enforces distinct records even with partial keys, just as the GROUP BY clause will determine distinct groups.

There is also a similar issue with FOREIGN KEY constraints. The default MATCH SIMPLE behavior is consistent with the default UNIQUE constraint behavior. An incomplete foreign key is essentially ignored and not subject to integrity checks. The MATCH FULL behavior is almost a style checker to say that a compound foreign key should be all null or all non-null, while still ignoring incomplete foreign keys. As far as I know, the MATCH PARTIAL case is not implemented, but I think it might complement this new UNIQUE NULLS NOT DISTINCT behavior?

One can imagine a further branching point in behaviors for foreign keys and I'm not sure how MATCH PARTIAL will be defined. One mode might be easily implementable with the new index method, if we want partial foreign keys to be able to reference partial keys in the referred table, i.e. the partial key with NULLs is considered an actual row key. But we might also imagine another partial matching mode where the the non-null components of a compound foreign key have to all exist in at least one key in the referred table, while ignoring the null fields as incompletely formed references. This would be an improvement on MATCH SIMPLE which allows the non-null columns in a foreign key to hold values which could not possibly match the referring table no matter which values are filled in for the remaining null fields of the foreign key. However, this enforcement would not be easily implemented without a powerset of indexes for all partial combinations allowed in the referencing tables!