Horton

Thread

  1. Re: [BUGS] (null) != (null) ?

    Tom Lane <tgl@sss.pgh.pa.us> — 1999-10-26T06:56:10Z

    Todd Vierling <tv@pobox.com> writes:
> (1) SELECT ... FROM table1 a,table2 b WHERE a.fieldname = b.fieldname;

> Both "fieldname" definitions are identical (verified with char(2) and
> varchar(100) in particular), and both tables contain a row with a "null" in
> that field.  However, the results don't contain the row with the "null"
> value.

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.  The easiest way to
think about NULL that I've heard of is: "NULL means I don't know
what the value should be".  So, for example, NULL = NULL is asking
whether two things are equal when you don't know exactly what
either of them is.  The answer cannot be "yes", it cannot be "no",
it has to be "I don't know" --- ie, NULL.

Nearly all Postgres operators yield NULL if any input is NULL.
This is perfectly sensible; for example, if you don't know what
x is, you don't know what x+1 is, either.  The main exceptions
are the special operators IS NULL and IS NOT NULL.  I think we also
put in a dirty hack to treat "x = NULL" (when NULL is written as a
literal constant) as "x IS NULL", because some clueless programmer
at Microsloth made MS SQL act that way, and now people expect it.
But it's bogus by any strict interpretation :-(

The WHERE clause treats a NULL test result as false (ie, the row
doesn't get selected), which accounts for the behavior you cite.
A really hard-line view of the semantics would be that WHERE NULL
should raise an error --- after all, if you don't know the result
of the test, how can you say if the row should be in or out?  But I
guess the SQL committee felt that that would be sacrificing too much
usability in the name of logical purity.  If it worked that way you
could hardly ever write a WHERE clause without explicit tests for
NULLs.

If you really want to match up nulls in your example, you can do
something like
	WHERE (a.fieldname = b.fieldname) OR
	      (a.fieldname IS NULL AND b.fieldname IS NULL)
This is pretty grotty, of course, so my inclination would be to
use a special non-NULL value --- an empty string, for example ---
for rows that you wanted to match like this.

PS: The above WHERE does succeed where both fields are NULL.
Exercise for the student: explain why.  (Hint: OR is just a
little bit special.)

> (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?

			regards, tom lane

  2. Re: [BUGS] (null) != (null) ?

    Todd Vierling <tv@pobox.com> — 1999-10-26T14:08:01Z

    On Tue, 26 Oct 1999, Tom Lane wrote:

: > Both "fieldname" definitions are identical (verified with char(2) and
: > varchar(100) in particular), and both tables contain a row with a "null" in
: > that field.  However, the results don't contain the row with the "null"
: > value.
: 
: 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?  (I'll check up on exactly which database(s) I can use this type of
construct when I get back to work tomorrow....)

It seems _extremely_ counter-intuitive, especially in cases where both
fields are in fact the same type.

: Nearly all Postgres operators yield NULL if any input is NULL.

Interesting ... so see my clarification of (2) below.

: If you really want to match up nulls in your example, you can do
: something like
: 	WHERE (a.fieldname = b.fieldname) OR
: 	      (a.fieldname IS NULL AND b.fieldname IS NULL)

Which I already described in my text, sigh.

: This is pretty grotty, of course, so my inclination would be to
: use a special non-NULL value --- an empty string, for example ---

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

: > (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.  :>

-- 
-- Todd Vierling (tv@pobox.com)

  3. Re: [BUGS] (null) != (null) ?

    Todd Vierling <tv@pobox.com> — 1999-10-26T14:41:52Z

    On Tue, 26 Oct 1999, Todd Vierling wrote:

: : 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.

: It seems _extremely_ counter-intuitive, especially in cases where both
: fields are in fact the same type.

Although I did find a SQL92 document on the web in the amount of time this
took to copy back to me, and I see the clause about NULL <comp op>
<anything> being unknown.  Which, I imagine, means "implementation
dependent".

-- 
-- Todd Vierling (tv@pobox.com)

  4. Re: [BUGS] (null) != (null) ?

    Bruce Momjian <maillist@candle.pha.pa.us> — 1999-10-26T16:48:05Z

    > On Tue, 26 Oct 1999, Tom Lane wrote:
> 
> : > Both "fieldname" definitions are identical (verified with char(2) and
> : > varchar(100) in particular), and both tables contain a row with a "null" in
> : > that field.  However, the results don't contain the row with the "null"
> : > value.
> : 
> : 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?  (I'll check up on exactly which database(s) I can use this type of
> construct when I get back to work tomorrow....)
> 
> It seems _extremely_ counter-intuitive, especially in cases where both
> fields are in fact the same type.

But NULL is unknown.  How do you know they are equal if both values are
unknown?

-- 
  Bruce Momjian                        |  http://www.op.net/~candle
  maillist@candle.pha.pa.us            |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

  5. Re: [BUGS] (null) != (null) ?

    Bruce Momjian <maillist@candle.pha.pa.us> — 1999-10-26T17:44:02Z

    I have just added a paragraph about this comparison in my book.  You can
see it on the documentation web page under "Published book".


> On Tue, 26 Oct 1999, Tom Lane wrote:
> 
> : > Both "fieldname" definitions are identical (verified with char(2) and
> : > varchar(100) in particular), and both tables contain a row with a "null" in
> : > that field.  However, the results don't contain the row with the "null"
> : > value.
> : 
> : 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?  (I'll check up on exactly which database(s) I can use this type of
> construct when I get back to work tomorrow....)
> 
> It seems _extremely_ counter-intuitive, especially in cases where both
> fields are in fact the same type.
> 
> : Nearly all Postgres operators yield NULL if any input is NULL.
> 
> Interesting ... so see my clarification of (2) below.
> 
> : If you really want to match up nulls in your example, you can do
> : something like
> : 	WHERE (a.fieldname = b.fieldname) OR
> : 	      (a.fieldname IS NULL AND b.fieldname IS NULL)
> 
> Which I already described in my text, sigh.
> 
> : This is pretty grotty, of course, so my inclination would be to
> : use a special non-NULL value --- an empty string, for example ---
> 
> Doesn't work for datetime, which is an important application in my case
> which rather needs null to indicate "no datestamp at all".
> 
> : > (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.  :>
> 
> -- 
> -- Todd Vierling (tv@pobox.com)
> 
> 
> ************
> 
> 


-- 
  Bruce Momjian                        |  http://www.op.net/~candle
  maillist@candle.pha.pa.us            |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026