The non-compliance fix is described here:
http://www.postgresql.org/docs/8.3/interactive/functions-
comparison.html says:
To check whether a value is or is not null, use the constructs
expression IS NULL
expression IS NOT NULL
or the equivalent, but nonstandard, constructs
....
Note: If the expression is row-valued, then IS NULL is true when
the row expression itself is null or when all the row's fields are
null, while IS NOT NULL is true when the row expression itself is
non-null and all the row's fields are non-null. This definition
conforms to the SQL standard, and is a change from the
inconsistent behavior exhibited by PostgreSQL versions prior to 8.2.
-- Andy
On May 16, 2008, at 12:54 PM, Alban Hertroys wrote:
On May 16, 2008, at 5:40 PM, Denis Gasparin wrote:
Hi all.
I have a problem with the IN operator in PostgreSQL 8.2.7. Here it
is an example that reproduce the problem:
test=# select * from test where b in(1,null);
a | b
---+---
1 | 1
In the last resultset, i was expecting two records the one with b
= 1 and the one with b = null.
PostgreSQL instead returns only the value with not null values.
Yes, of course it does. NULL means "unknown". Comparing it to
anything results in NULL, as the result is "unknown" again. What
happens is this:
development=> select b, coalesce( (b in (1, null))::text, 'NULL')
from test;
b | coalesce
---+----------
1 | true
2 | NULL
| NULL
(3 rows)
The where clause can only handle true or false (as per the SQL
spec), so it assumes "unknown" means the record wasn't a match.
I tested the example also in PostgreSQL 8.1 and it works correctly
(two records).
That looks like a bug in 8.1.
So the question is: what has changed from 8.1 to 8.2?
I think a bug was fixed ;)
Alban Hertroys
--
If you can't see the forest for the trees,
cut the trees and you'll see there is no forest.
!DSPAM:737,482dbc5e927668957138674!
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
