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

Re: Bug#57466: select ... except fails when there are nulls in second clause

From: "Oliver Elphick" <olly(at)lfix(dot)co(dot)uk>
To: Kevin Dalley <kevind(at)rahul(dot)net>, 57466(at)bugs(dot)debian(dot)org
Cc: pgsql-sql(at)postgresql(dot)org
Subject: Re: Bug#57466: select ... except fails when there are nulls in second clause
Date: 2000-02-09 09:08:20
Message-ID: (view raw, whole thread or download thread mbox)
Lists: 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
  >pgbug=> select value from first except select value from second;
  >(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 
  >    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;
  >COPY "second" FROM stdin;
  >-- 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
  >ii  libpgsql2                  6.5.3-10      Shared library for P
  >ii  libreadline4               4.1-0beta3db3 GNU readline and history librar
  >ii  postgresql-client          6.5.3-10      Front-end programs for PostgreS
  >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" ]
  >    POSTGRES_HOME=/var/postgres

Oliver Elphick                                Oliver(dot)Elphick(at)lfix(dot)co(dot)uk
Isle of Wight                    
               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 


pgsql-sql by date

Next:From: Bjorn Segers (dig)Date: 2000-02-09 15:45:12
Subject: Problem with the connection using.odbc.ini
Previous:From: Javi PiolDate: 2000-02-09 00:40:16

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