Received: from localhost (maia-4.hub.org [200.46.204.183])
	by postgresql.org (Postfix) with ESMTP id 1F7A39FB299
	for <pgsql-sql-postgresql.org@postgresql.org>;
	Wed, 31 Jan 2007 16:18:20 -0400 (AST)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.204.183]) (amavisd-new, port 10024)
	with ESMTP id 92659-09 for <pgsql-sql-postgresql.org@postgresql.org>;
	Wed, 31 Jan 2007 16:18:18 -0400 (AST)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.4
Received: from svr4.postgresql.org (svr4.postgresql.org [66.98.251.159])
	by postgresql.org (Postfix) with ESMTP id 2D0BF9FB282
	for <pgsql-sql@postgresql.org>; Wed, 31 Jan 2007 16:18:16 -0400 (AST)
X-Greylist: domain auto-whitelisted by SQLgrey-1.7.3
Received: from kgb08.kgbinternet.com (kgb08.kgbinternet.com [66.199.152.35])
	by svr4.postgresql.org (Postfix) with ESMTP id 4CFC85AF8BB
	for <pgsql-sql@postgresql.org>; Wed, 31 Jan 2007 20:18:15 +0000 (GMT)
Received: from [127.0.0.1] (216.19.188.70.novuscom.net [216.19.188.70])
	(authenticated bits=0)
	by kgb08.kgbinternet.com (8.12.10/8.12.8) with ESMTP id l0VKI6E3010696;
	Wed, 31 Jan 2007 14:18:07 -0600
Message-ID: <45C0F97B.4080104@williamrosmus.com>
Date: Wed, 31 Jan 2007 12:18:03 -0800
From: BillR <iambill@williamrosmus.com>
User-Agent: Thunderbird 1.5.0.9 (Windows/20061207)
MIME-Version: 1.0
To: Peter Eisentraut <peter_e@gmx.net>
Cc: pgsql-sql@postgresql.org, "D'Arcy J.M. Cain" <darcy@druid.net>,
	Andrew Sullivan <ajs@crankycanuck.ca>
Subject: Re: Differentiate Between Zero-Length String and NULLColumn
 Values
References: <20070130131334.GD31676@phlogiston.dyndns.org>
	<20070130142332.GA31905@phlogiston.dyndns.org>
	<20070130113834.fa8d09e0.darcy@druid.net>
	<200701301959.37775.peter_e@gmx.net>
In-Reply-To: <200701301959.37775.peter_e@gmx.net>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=0.033 tagged_above=0 required=5 tests=AWL, BAYES_50
X-Spam-Level: 
X-Archive-Number: 200702/36
X-Sequence-Number: 27370



Peter Eisentraut wrote:
> D'Arcy J.M. Cain wrote:
>   
>>    SELECT * FROM table WHERE column IS NULL;
>>    SELECT * FROM table WHERE column = NULL;
>>
>> The latter violates the SQL spec and is not allowed by PostgreSQL
>> without setting a special flag.
>>     
>
> It doesn't violate any spec and it's certainly allowed by PostgreSQL 
> without any flags.  It's just that the result is not what some people 
> expect.
>
>   

"= NULL" violates the SQL-92 Specification.  Relevant section posted below.
Additionally *none of the example code* in the SQL-92 specification 
document uses the expression "WHERE #value# = NULL"
*All* the example code in the specification use the expression as:  
"WHERE #value# IS NULL"


         8.6  <null predicate>

         Function

         Specify a test for a null value.

         Format

         <null predicate> ::= <row value constructor> IS [ NOT ] NULL


         Syntax Rules

            None.

         Access Rules


            None.

         General Rules

         1) Let R be the value of the <row value constructor>.

         2) If all the values in R are the null value, then "R IS NULL" is
            true; otherwise, it is false.

         3) If none of the values in R are the null value, then "R IS NOT
            NULL" is true; otherwise, it is false.

            Note: For all R, "R IS NOT NULL" has the same result as "NOT
            R IS NULL" if and only if R is of degree 1. Table 12, "<null
            predicate> semantics", specifies this behavior.

         ________________Table_12-<null_predicate>_semantics________________

                           R IS    R IS NOT      NOT R IS      NOT R IS NOT
         _Expression_______NULL____NULL__________NULL__________NULL_________

        | degree 1: null | true  | false       | false      |  true        |
        |                |       |             |            |              |
        | degree 1: not  | false | true        | true       |  false       |
          null

        | degree > 1:    | true  | false       | false      |  true        |
        | all null       |       |             |            |              |
        |                |       |             |            |              |
        | degree > 1:    | false | false       | true       |  true        |
        | some null      |       |             |            |              |
        |                |       |             |            |              |
        | degree > 1:    | false | true        | true       |  false       |
        |_none_null______|_______|_____________|____________|______________|
        |                |       |             |            |              |
        |Leveling Rules  |       |             |            |              |
        |                |       |             |            |              |
         218  Database Language SQL