Received: from sss.pgh.pa.us (sss.pgh.pa.us [209.114.132.154]) by hub.org (8.10.1/8.11.0) with ESMTP id e9REdpq34685 for ; Fri, 27 Oct 2000 10:39:51 -0400 (EDT) (envelope-from tgl@sss.pgh.pa.us) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.11.1/8.11.1) with ESMTP id e9REdX529117; Fri, 27 Oct 2000 10:39:33 -0400 (EDT) To: Daniel Kalchev cc: pgsql-sql@postgresql.org Subject: Re: except on nulls? In-reply-to: <200010270602.JAA16927@dcave.digsys.bg> References: <200010270602.JAA16927@dcave.digsys.bg> Comments: In-reply-to Daniel Kalchev message dated "Fri, 27 Oct 2000 09:02:43 +0300" Date: Fri, 27 Oct 2000 10:39:32 -0400 Message-ID: <29114.972657572@sss.pgh.pa.us> From: Tom Lane X-Archive-Number: 200010/258 X-Sequence-Number: 1925 Daniel Kalchev 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