pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Tom Lane <tgl@sss.pgh.pa.us>
To: Oliver Elphick <olly@lfix.co.uk>
Cc: Kevin Dalley <kevind@rahul.net>
Cc: 57466@bugs.debian.org
Cc: pgsql-sql@postgreSQL.org
Subject: Re: [SQL] Re: Bug#57466: select ... except fails when there are nulls in second clause
Date: Wed, 09 Feb 2000 10:48:23 -0500
Message-ID: <5630.950111303@sss.pgh.pa.us> (raw)
In-Reply-To: <200002090908.JAA06657@linda.lfix.co.uk>
References: <200002090908.JAA06657@linda.lfix.co.uk>

"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



view thread (2+ messages)

Message-ID: <5630.950111303@sss.pgh.pa.us>
Permalink:  ../5630.950111303@sss.pgh.pa.us/
Also on:    postgresql.org/message-id/5630.950111303@sss.pgh.pa.us

 · 

reply

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: tgl@sss.pgh.pa.us, olly@lfix.co.uk, kevind@rahul.net, 57466@bugs.debian.org
  Subject: Re: [SQL] Re: Bug#57466: select ... except fails when there are nulls in second clause
  In-Reply-To: <5630.950111303@sss.pgh.pa.us>
  References: <200002090908.JAA06657@linda.lfix.co.uk> <5630.950111303@sss.pgh.pa.us>

* 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