pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedRe: Bug#57466: select ... except fails when there are nulls in second clause
2+ messages / 2 participants
[nested] [flat]
* Re: Bug#57466: select ... except fails when there are nulls in second clause
@ 2000-02-09 09:08 Oliver Elphick <olly@lfix.co.uk>
2000-02-09 15:48 ` Re: [SQL] Re: Bug#57466: select ... except fails when there are nulls in second clause Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 2+ messages in thread
From: Oliver Elphick @ 2000-02-09 09:08 UTC (permalink / raw)
To: Kevin Dalley <kevind@rahul.net>; 57466@bugs.debian.org; +Cc: 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
>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
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: [SQL] Re: Bug#57466: select ... except fails when there are nulls in second clause
2000-02-09 09:08 Re: Bug#57466: select ... except fails when there are nulls in second clause Oliver Elphick <olly@lfix.co.uk>
@ 2000-02-09 15:48 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 2+ messages in thread
From: Tom Lane @ 2000-02-09 15:48 UTC (permalink / raw)
To: Oliver Elphick <olly@lfix.co.uk>; +Cc: Kevin Dalley <kevind@rahul.net>; 57466@bugs.debian.org; pgsql-sql
"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
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2000-02-09 15:48 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2000-02-09 09:08 Re: Bug#57466: select ... except fails when there are nulls in second clause Oliver Elphick <olly@lfix.co.uk>
2000-02-09 15:48 ` Tom Lane <tgl@sss.pgh.pa.us>
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