(null) != (null) ?

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(at)pobox(dot)com)