[Dbix-class] Re: rfc, treating NULL as a value like any other

[Dagfinn Ilmari Mannsåker]

Darren Duncan <darren at DarrenDuncan.net> writes:

> At 9:01 PM -0500 1/26/08, Christopher Laco wrote:
>> In perl terms, I think it's great if {foo => NULL} translates to foo
>> IS NULL. DWIM++
>>
>> Now, according to SQL92, any comparison of = or != against NULL
>> returns false. So, even a {foo => 3} should not return rows that are
>> NULL, because NULL is neither = or != to 3.
>>
>>http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_set-set_9rec.asp
>>
>> The SQL-92 standard requires that an equals (=) or not equal to (<>)
>> comparison against a null value evaluates to FALSE. When SET
>> ANSI_NULLS is ON, a SELECT statement using WHERE column_name = NULL
>> returns zero rows even if there are null values in column_name. A
>> SELECT statement using WHERE column_name <> NULL returns zero rows
>> even if there are nonnull values in column_name.

This is not entirely correct. Comparison to NULL returns unknown¹ (i.e.
NULL), and NOT(unkwnown) is unknown².

[1]: SQL-92, 8.2   <comparison predicate>, general rule 1a.
[2]: SQL-92, 8.12  <search condition>, general rule 2.

> What I'm saying is, for each example below, take the left side as being
> what users say, and the right side as what SQL semantics they get,
> assuming the system knows that foo is nullable, or plays it like it is
> to be safe in the general case:
[…]
>   foo != 3                    foo != 3 OR foo IS NULL
>                         or?   NOT (foo = 3)

These two are not equivalent: if foo IS NULL, the comparison, and hence
its negation, returns NULL (which is not TRUE).

-- 
ilmari
"A disappointingly low fraction of the human race is,
 at any given time, on fire." - Stig Sandbeck Mathisen