From: Oliver Elphick <olly@lfix.co.uk>
To: Kevin Dalley <kevind@rahul.net>
To: 57466@bugs.debian.org
Cc: pgsql-sql@postgresql.org
Subject: Re: Bug#57466: select ... except fails when there are nulls in second clause
Date: Wed, 09 Feb 2000 09:08:20 +0000
Message-ID: <200002090908.JAA06657@linda.lfix.co.uk> (raw)
In-Reply-To: <kevind@rahul.net> <200001310940.BAA09905@laminaria.rahul.net>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-sql@postgresql.org
Cc: olly@lfix.co.uk, kevind@rahul.net, 57466@bugs.debian.org
Subject: Re: Bug#57466: select ... except fails when there are nulls in second clause
In-Reply-To: <200002090908.JAA06657@linda.lfix.co.uk>
References: <200002090908.JAA06657@linda.lfix.co.uk>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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