Comparisons on NULLs (was Re: A small problem...)

darcy(at)druid(dot)net (D'Arcy J.M. Cain) writes:
>> but I can see the reasonableness of defining "3 != NULL" as TRUE.

> Actually I see it as FALSE. That's what I was suggesting earlier. All
> comparisons to null should be false no matter what the sense of the
> test.

Hmm. That yields extremely unintuitive results for = and !=. That is,

SELECT * FROM t WHERE b = NULL;

will never return any rows, even if there are some where b is null;
and

SELECT * FROM t WHERE b != NULL;

will never return any rows, even if there are some where b isn't null.

If this is the definition then you cannot select rows with null entries
using the same syntax as for selecting rows with particular non-null
values, which is what I thought the point of the CREATE FUNCTION example
was.

> That way you can always decide in the select statement whether
> you want a particular comparison to null to be included or not.
> [D'Arcy proposes that these ops need not give the same result:
> SELECT * FROM t WHERE i1 < i2;
> SELECT * FROM t WHERE NOT (i1 >= i2);

Ugh. I think it'd be a lot more intuitive to write something like

SELECT * FROM t WHERE i1 < 33 OR i1 IS NULL;

But getting this to work without introducing unintended consequences
might be pretty tricky too. If "NULL < 33" returns NULL, as I'd prefer,
then OR has to be non-strict, and in fact NULL OR 't' has to give 't'.
That looks pretty reasonable at first glance, but there are probably
other examples where it does something undesirable.

Maybe, the boolean-combining operators (AND, OR, NOT) can safely be
made non-strict (treating NULL as FALSE), but I'm wary of that.

We probably ought to go re-read the IEEE float math specs. What I think
you are getting at is almost the same as their distinction between
"NaN-aware" and "non-NaN-aware" comparison operators, but I've forgotten
the details of how those work. (And I have to leave in a minute, so I
can't look them up right now...)

regards, tom lane