Re: Differentiate Between Zero-Length String and NULLColumn Values

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
degree1. 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
DatabaseLanguage SQL