Thread

1. (null) != (null) ?

   Todd Vierling <tv@pobox.com> — 1999-10-26T01:51:11Z

   Below are two minor bug issues which I can't find as `known' (then again, I
   can't seem to find an easy-to-identify `known issues' list for that matter 8-),
   in pgsql 6.5.2.
   
   Platform:  NetBSD/i386, 1.4.1 (a.out).
   
   =====
   
   (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.  A quick reproduction:
   
   => create temp table foo (fieldname char(2));
   => create temp table foo2 (fieldname char(2));
   => insert into foo values (null);
   => insert into foo2 values (null);
   => select foo.fieldname from foo,foo2 where foo.fieldname = foo2.fieldname;
   
   fieldname
   ---------
   (0 rows)
   
   In the above, only the following expression seems to DTRT:
   
   => select foo.fieldname from foo,foo2 where foo.fieldname = foo2.fieldname
      or (foo.fieldname = null and foo2.fieldname = null);
   
   fieldname
   ---------
   
   (1 row)
   
   =====
   
   (2) NOT IN doesn't seem to work at all.  I always get 0 results--and very
       rapidly at that!--regardless of the situation.
   
   -- 
   -- Todd Vierling (tv@pobox.com)
   
   
   

2. Re: (null) != (null) ?

   Thomas Pfau <pfau@maherterminals.com> — 1999-10-26T12:57:06Z

   Todd Vierling wrote:
   > (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.  A quick reproduction:
   
   This is standard SQL behavior.  NULL != NULL.  Essentially, NULL is an
   undefined value.  Since it is undefined, it can't be know to be equal to
   anything, even another undefined value.
   
   Oracle has the NVL function which can be used to replace nulls with
   known values allowing your statement above to work as you expect.  I
   don't think Postgres has a similar function but you could probably write
   your own.
   
   -- 
   Thomas Pfau
   pfau@maherterminals.com
   aka pfau@eclipse.net
   http://www.eclipse.net/~pfau/