Received: from sss.pgh.pa.us ([192.204.191.242]) by postgresql.org (8.11.3/8.11.1) with ESMTP id f5H1Nra64417 for ; Sat, 16 Jun 2001 21:23:53 -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.4/8.11.4) with ESMTP id f5H1Na328533; Sat, 16 Jun 2001 21:23:36 -0400 (EDT) To: Peter Eisentraut cc: john@august.com, Josh Berkus , pgsql-sql@postgresql.org Subject: Re: not null - trivial, unexpected behavior In-reply-to: References: Comments: In-reply-to Peter Eisentraut message dated "Sun, 17 Jun 2001 02:15:01 +0200" Date: Sat, 16 Jun 2001 21:23:36 -0400 Message-ID: <28530.992741016@sss.pgh.pa.us> From: Tom Lane X-Archive-Number: 200106/239 X-Sequence-Number: 3497 Peter Eisentraut writes: > John Scott writes: >> but, as i understand the sql92 standard, both att = null and att != null >> are NOT sql92. > They are. We just don't implement att = null right because of reasons > that can be found in the archives. In a very narrow sense, they're not SQL92, because SQL92 doesn't actually allow an unadorned keyword NULL to appear in arbitrary expression contexts. You could legally write the expression as att = CAST (NULL AS type-of-att) and then the required result would always be NULL, a/k/a UNKNOWN (nb. this is NOT the same as FALSE). And indeed that's what Postgres will produce if you do it that way. In practice, since Postgres extends the spec to allow the unadorned keyword NULL to appear in arbitrary expressions (with implicit resolution of the datatype of the null), you'd expect that att = NULL would behave the same as if the NULL came from a CAST, evaluation of a data value, etc. But it doesn't, for reasons that have been discussed already. regards, tom lane