Обсуждение: except on nulls?

I had the following weird experience. Hopefuly iy's my ignorance on the matter 
(but I sort of think this worked before...)

On PostgreSQL 7.0.2, I have an database with two tables, holding different 
versions of the data set (some rows missing in one of the tables). Example is:

CREATE TABLE "test1" (       "f1" text,       "f2" date,       "f3" "_text",       "f4" int4,       "f5" character
varying(16)
);

Then table test2 with the same definition.

SELECT f1 FROM test2 EXCEPT SELECT f1 from test1;

produced empty result, although test2 contained few more rows.

Closer examination showed that both tables contained an 'null' row, with all 
fields null.

After removing both rows, the query would return the proper results.

Another query,

SELECT * from test2 EXCEPT SELECT * from test1;

failed too, giving the following error:

ERROR:  Unable to identify an operator '<>' for types '_text' and '_text'       You will have to retype this query
usingan explicit cast
 

Any idea why this is happening?

Daniel

Daniel Kalchev <daniel@digsys.bg> writes:
> [ EXCEPT behaves oddly in the presence of NULLs ]

Yup, it does, because it's implemented like NOT IN, and NOT IN on a
set containing nulls can never return 'true', only 'false' or 'unknown'.
For example,    1 NOT IN (1,2,NULL)
is clearly FALSE.  But    3 NOT IN (1,2,NULL)
is not clearly either true or false --- the null is effectively "I don't
know what this value is", and so it's unknown whether 3 is equal to it
or not.  The SQL92 spec mandates that this NOT IN result be 'unknown'
(NULL), which is then treated like 'false' by EXCEPT.  Net result:
nulls in EXCEPT's right-hand set cause its output set to be empty.

While this behavior is all according to spec for IN/NOT IN, it's *not*
according to spec for EXCEPT, because the spec defines UNION/INTERSECT/
EXCEPT in terms of a different concept, of rows being "distinct" or "not
distinct".  NULLs are distinct from non-NULLs and so a null row behaves
the way you'd expect.

UNION/INTERSECT/EXCEPT are reimplemented for 7.1 in a way that behaves
according to spec.  There's no simple patch for 7.0.* unfortunately.

> (but I sort of think this worked before...)

Could be.  Before 7.0, IN/NOT IN were not up to spec on NULL handling
either, so EXCEPT probably worked differently in this case then.

> ERROR:  Unable to identify an operator '<>' for types '_text' and '_text'
>         You will have to retype this query using an explicit cast

There are no comparison operators for array types ...
        regards, tom lane

Daniel Kalchev <daniel@digsys.bg> writes:
> [ EXCEPT behaves oddly in the presence of NULLs ]

Yup, it does, because it's implemented like NOT IN, and NOT IN on a
set containing nulls can never return 'true', only 'false' or 'unknown'.
For example,    1 NOT IN (1,2,NULL)
is clearly FALSE.  But    3 NOT IN (1,2,NULL)
is not clearly either true or false --- the null is effectively "I don't
know what this value is", and so it's unknown whether 3 is equal to it
or not.  The SQL92 spec mandates that this NOT IN result be 'unknown'
(NULL), which is then treated like 'false' by EXCEPT.  Net result:
nulls in EXCEPT's right-hand set cause its output set to be empty.

While this behavior is all according to spec for IN/NOT IN, it's *not*
according to spec for EXCEPT, because the spec defines UNION/INTERSECT/
EXCEPT in terms of a different concept, of rows being "distinct" or "not
distinct".  NULLs are distinct from non-NULLs and so a null row behaves
the way you'd expect.

UNION/INTERSECT/EXCEPT are reimplemented for 7.1 in a way that behaves
according to spec.  There's no simple patch for 7.0.* unfortunately.

> (but I sort of think this worked before...)

Could be.  Before 7.0, IN/NOT IN were not up to spec on NULL handling
either, so EXCEPT probably worked differently in this case then.

> ERROR:  Unable to identify an operator '<>' for types '_text' and '_text'
>         You will have to retype this query using an explicit cast

There are no comparison operators for array types ...
        regards, tom lane