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

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(dot)Elphick(at)lfix(dot)co(dot)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