From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | "Oliver Elphick" <olly(at)lfix(dot)co(dot)uk> |
Cc: | Kevin Dalley <kevind(at)rahul(dot)net>, 57466(at)bugs(dot)debian(dot)org, pgsql-sql(at)postgreSQL(dot)org |
Subject: | Re: [SQL] Re: Bug#57466: select ... except fails when there are nulls in second clause |
Date: | 2000-02-09 15:48:23 |
Message-ID: | 5630.950111303@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
"Oliver Elphick" <olly(at)lfix(dot)co(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | Martin Kresse | 2000-02-10 17:59:26 | How to quietly increment a SEQUENCE? |
Previous Message | Bjorn Segers (dig) | 2000-02-09 15:45:12 | Problem with the connection using.odbc.ini |