SQLite Forum

STRICT tables and UNIQUE indices
> NULL values from different rows ARE considered equal in SELECT DISTINCT.

This is true, simply because with DISTINCT Queries you face the same logic problem as UNIQUE indexes, but this time inverted - think of it like this: for a UNIQUE constraint, you have to prove two values ARE EQUAL to constrain them, which cannot be done with NULLS as described elsewhere, and for a DISTINCT requirement on a query you have to assert that two values are definitely NOT EQUAL (distinct) to add them both, and that is again something you cannot do with NULL/undefined/unset/unknown values. Ok, so both equality AND inequality is not provable for NULLs, what to do then?

Well, what DISTINCT queries (and sorters) use is the most sensible alternate: to lump all the unknowns together. Perfectly sensible and fine, you will find nobody crying about this and I think near all engines agree on it. In order to facilitate this "lumping together" of the unknowns, the engine and its documentation "considers them equal" - but that is merely a consideration, a means to an end and not an admission of equality. To think otherwise would be a logic mistake, and doesn't transfer/apply to the rest of the discussion regarding UNIQUE constraints.

As to your proposed language extension - sure, if you can make a case for needing distinct queries to separate all the null values into different rows in stead of lumping them together, perhaps it might be considered.