Обсуждение: 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
databasebelow. > >The following statement returns 0 rows when there is a null in table >second: > >pgbug=> select value
fromfirst except select value from second; >value >----- >(0 rows) > >But when null values are excluded, the new select
statementreturns 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
todowngrade the bug if you think it appropriate.  This problem >should go into the regression test. > >Here is the
outputfrom 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
postgresqldepends 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
filesystem 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
ID32B8FAA1                ========================================    "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 
 

"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