Thread

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

Lincoln Yeoh <lylyeoh@mecomb.com> — 1999-10-26T03:10:57Z

News at 11: "Nothing beats Postgres comparison method"

Yeah, I got same prob in Linux with 6.5.2 too.

Only way to get result was:
select * from foo,foo2 where foo.fieldname is null AND foo2.fieldname is null;

Maybe comparing nothings is a no-no.

Cheerio,

Link.

At 09:51 PM 25-10-1999 -0400, Todd Vierling wrote:
>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)
>
>
>************
>
>
>