Re: [BUGS] (null) != (null) ? - Mailing list pgsql-bugs

Todd Vierling <tv@pobox.com> writes:
> : NULL = NULL does not yield TRUE, it yields NULL.  For that matter,
> : NULL != NULL does not yield FALSE --- it yields NULL.  This is a
> : basic consequence of the semantics of NULL.

> !?

> I have been using such constructs on commercial databases for ages.  Do you
> have a link to a web-based SQL standard transcription that I could look this
> up?

The SQL92 standard expresses this notion in a very wordy, laborious
fashion: every single place that they define the result of an
expression, they start out by saying "if the input(s) are null the
result is null, otherwise it's ...".  Two examples:

         1) If the value of any <numeric primary> simply contained in a
            <numeric value expression> is the null value, then the result of
            the <numeric value expression> is the null value.

            .... etc etc ....

         2) If <concatenation> is specified, then let S1 and S2 be the re-
            sult of the <character value expression> and <character factor>,
            respectively.

            Case:

            a) If either S1 or S2 is the null value, then the result of the
              <concatenation> is the null value.

            .... etc etc ....

And the particular case at hand is defined in 8.1  <predicate> and
8.2  <comparison predicate>, which say

         8.1  <predicate>

         Function

         Specify a condition that can be evaluated to give a truth value of
         true, false, or unknown.

         ...

         1) Let X and Y be any two corresponding <row value constructor
            element>s. Let XV and YV be the values represented by X and Y,
            respectively.

            Case:

            a) If XV or YV is the null value, then "X <comp op> Y" is un-
              known.

Finally, WHERE is defined as selecting those rows which yield a true
predicate result.

If your other databases don't get this right, then they're broken.


> Doesn't work for datetime, which is an important application in my case
> which rather needs null to indicate "no datestamp at all".

IIRC datetime has several special values such as "infinity"; you could
use one of those, perhaps.  But NULL doesn't act the way you are looking
for.


> : > (2) NOT IN doesn't seem to work at all.  I always get 0 results--and very
> : >     rapidly at that!--regardless of the situation.
> :
> : I don't think it's quite *that* broken.  How about a concrete
> : example of what you're trying to do?

> Well, after reading your statement about "Nearly all Postgres ...", here's a
> very simple example that I was able to create based on that assumption:

> => create temp table foo (name varchar(10));
> => create temp table foo2 (name varchar(10));
> => insert into foo values (null); // <<- here's the tripwire!
> => insert into foo values ('a');
> => insert into foo2 values ('a');
> => insert into foo2 values ('b');
> => select * from foo2 where field not in (select field from foo);

> field
> -----
> (0 rows)

> Now *that* is awfully disturbing.  :>

Well, it falls out of the semantics:  the NOT IN is true if foo2's
field is not equal to *all* of the entries returned by the subselect.
If one of those is NULL, then the result of the NOT IN can't be "true",
it has to be "unknown", because you don't know whether the foo2 value
ought to be considered equal to the null or not.  The NOT IN can return
a definite "false" when it finds a match to one of the non-null
subselect values, but never a definite "true".  Of course when you are
using it as a WHERE condition you won't see the difference between
"false" and "unknown".

I do see a related bug here, though: I'd expect

select * from foo2 where (name not in (select name from foo)) is null;

to produce hits, and it doesn't.  I suspect the subselect evaluator
is being a little careless about nulls ... will look into it.

            regards, tom lane