pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Re: Bug#57466: select ... except fails when there are nulls in second clause
2+ messages / 2 participants
[nested] [flat]

* Re: Bug#57466: select ... except fails when there are nulls in second clause
@ 2000-02-09 09:08 Oliver Elphick <olly@lfix.co.uk>
  2000-02-09 15:48 ` Re: [SQL] Re: Bug#57466: select ... except fails when there are nulls in second clause Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 1 reply; 2+ messages in thread

From: Oliver Elphick @ 2000-02-09 09:08 UTC (permalink / raw)
  To: Kevin Dalley <kevind@rahul.net>; 57466@bugs.debian.org; +Cc: pgsql-sql

I think that this is not a bug at all, but a necessary consequence of how
nulls are treated, but I would be grateful for confirmation of this.

This is equivalent to saying "give me all items in first where value is
not found in second".  The point about a null is that you don't know
what value it is, so it might be a value that you want.  This is more
likely to give unexpected results when the condition is negative, as
here; nevertheless, I think the results of this query are correct.

Kevin Dalley wrote:
  >Package: postgresql
  >Version: 6.5.3-10
  >Severity: important
  >
  >I have included a dump of the database below.
  >
  >The following statement returns 0 rows when there is a null in table
  >second:
  >
  >pgbug=> select value from first except select value from second;
  >value
  >-----
  >(0 rows)
  >
  >But when null values are excluded, the new select statement returns a
  >non-zero value:
  >
  >pgbug=> select value from first except select value from second where value 
      >notnull;
  >value
  >-----
  >    2
  >(1 row)
  >
  >
  >This bug might be important, since it returns incorrect values.  Feel
  >free to downgrade the bug if you think it appropriate.  This problem
  >should go into the regression test.
  >
  >Here is the output from pg_dump:
  >
  >CREATE TABLE "first" (
  >	"value" int4);
  >CREATE TABLE "second" (
  >	"value" int4);
  >COPY "first" FROM stdin;
  >1
  >2
  >\.
  >COPY "second" FROM stdin;
  >1
  >\N
  >3
  >\.
  >
  >
  >-- System Information
  >Debian Release: potato
  >Architecture: i386
  >Kernel: Linux laminaria 2.2.14 #1 Sat Jan 22 11:25:24 PST 2000 i686
  >
  >Versions of packages postgresql depends on:
  >ii  debianutils                1.13.2        Miscellaneous utilities specifi
      >c t
  >ii  libc6                      2.1.2-12      GNU C Library: Shared libraries
      > an
  >ii  libncurses5                5.0-5         Shared libraries for terminal h
      >and
  >ii  libpgsql2                  6.5.3-10      Shared library libpq.so.2 for P
      >ost
  >ii  libreadline4               4.1-0beta3db3 GNU readline and history librar
      >ies
  >ii  postgresql-client          6.5.3-10      Front-end programs for PostgreS
      >QL 
  >ii  procps                     1:2.0.6-5     The /proc file system utilities
      >.  
  >
  >-- Configuration Files:
  >/etc/postgresql/postmaster.init changed:
  >POSTGRES_HOME=`grep '^postgres:' /etc/passwd | awk -F: '{print $6}'`
  >if [ -z "$POSTGRES_HOME" ]
  >then
  >    POSTGRES_HOME=/var/postgres
  >fi
  >POSTGRES_DATA=/var/postgres/data
  >PGFSYNC=no
  >PGDATESTYLE=US
  >PGALLOWTCPIP=yes
  >
  >

-- 
Oliver Elphick                                Oliver.Elphick@lfix.co.uk
Isle of Wight                              http://www.lfix.co.uk/oliver
               PGP key from public servers; key ID 32B8FAA1
                 ========================================
     "Let the words of my mouth, and the meditation of my 
      heart, be acceptable in thy sight, O LORD, my  
      strength, and my redeemer."          Psalms 19:14 





^ permalink  raw  reply  [nested|flat] 2+ messages in thread

* Re: [SQL] Re: Bug#57466: select ... except fails when there are nulls in second clause
  2000-02-09 09:08 Re: Bug#57466: select ... except fails when there are nulls in second clause Oliver Elphick <olly@lfix.co.uk>
@ 2000-02-09 15:48 ` Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 0 replies; 2+ messages in thread

From: Tom Lane @ 2000-02-09 15:48 UTC (permalink / raw)
  To: Oliver Elphick <olly@lfix.co.uk>; +Cc: Kevin Dalley <kevind@rahul.net>; 57466@bugs.debian.org; pgsql-sql

"Oliver Elphick" <olly@lfix.co.uk> writes:
>> The following statement returns 0 rows when there is a null in table
>> second:
>> pgbug=> select value from first except select value from second;

> I think that this is not a bug at all, but a necessary consequence of how
> nulls are treated, but I would be grateful for confirmation of this.
>
> This is equivalent to saying "give me all items in first where value is
> not found in second".  The point about a null is that you don't know
> what value it is, so it might be a value that you want.

Right.  This might be more clear if you look at the query in the form
that it gets rewritten into:

SELECT value FROM first WHERE value <> ALL (SELECT value FROM second);

which in turn can be visualized as

	WHERE (first.value <> second.value1) AND
	      (first.value <> second.value2) AND
	      ...
	      (first.value <> second.valueN);

If any of the values coming from second are NULL, then the result of the
AND cannot be TRUE: it can only be FALSE ("there's definitely a matching
value") or NULL ("I don't know whether there's a matching value").

Postgres 6.5.* does have some bugs in this area, because various places
fail to implement proper three-valued boolean logic; in particular
nodeSubplan.c didn't return a NULL boolean result when it should have.
(That makes no difference in this example, but would make a difference
if the result of the subselect operator were combined with other boolean
clauses.)  I believe I've fixed all those problems for 7.0.

			regards, tom lane



^ permalink  raw  reply  [nested|flat] 2+ messages in thread


end of thread, other threads:[~2000-02-09 15:48 UTC | newest]

Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2000-02-09 09:08 Re: Bug#57466: select ... except fails when there are nulls in second clause Oliver Elphick <olly@lfix.co.uk>
2000-02-09 15:48 ` Tom Lane <tgl@sss.pgh.pa.us>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox