Received: from mail.enterprise.net (mail.enterprise.net [194.72.192.18]) by hub.org (8.9.3/8.9.3) with ESMTP id EAA90336 for ; Wed, 9 Feb 2000 04:07:50 -0500 (EST) (envelope-from olly@lfix.co.uk) Received: from linda.lfix.co.uk (max03-013.enterprise.net [194.72.196.13]) by mail.enterprise.net (8.8.5/8.8.5) with ESMTP id JAA07372; Wed, 9 Feb 2000 09:07:48 GMT Received: from lfix.co.uk (olly@localhost [127.0.0.1]) by linda.lfix.co.uk (8.9.3/8.9.3/Debian 8.9.3-6) with ESMTP id JAA06657; Wed, 9 Feb 2000 09:08:20 GMT Message-Id: <200002090908.JAA06657@linda.lfix.co.uk> X-Authentication-Warning: linda.lfix.co.uk: Host olly@localhost [127.0.0.1] claimed to be lfix.co.uk X-Mailer: exmh version 2.1.1 10/15/1999 (debian) X-URL: http://www.lfix.co.uk/oliver X-face: "xUFVDj+ZJtL_IbURmI}!~xAyPC"Mrk=MkAm&tPQnNq(FWxv49R}\>0oI8VM?O2VY+N7@F- KMLl*!h}B)u@TW|B}6, 57466@bugs.debian.org cc: pgsql-sql@postgresql.org Subject: Re: Bug#57466: select ... except fails when there are nulls in second clause In-reply-to: Message from Kevin Dalley of Mon, 31 Jan 2000 01:40:51 PST. <200001310940.BAA09905@laminaria.rahul.net> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Date: Wed, 09 Feb 2000 09:08:20 +0000 From: "Oliver Elphick" 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