Skip site navigation (1) Skip section navigation (2)

(null) != (null) ?

From: Todd Vierling <tv(at)pobox(dot)com>
To: pgsql-bugs(at)postgresql(dot)org
Subject: (null) != (null) ?
Date: 1999-10-26 01:51:11
Message-ID: Pine.NEB.4.10.9910252135540.5352-100000@server.int.duh.org (view raw or flat)
Thread:
Lists: pgsql-bugs
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)


Responses

pgsql-bugs by date

Next:From: Lincoln YeohDate: 1999-10-26 03:10:57
Subject: Re: [BUGS] (null) != (null) ?
Previous:From: Krzysztof CzumaDate: 1999-10-24 23:43:30
Subject: postgresql creates huge files

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group