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

[Darren Duncan]

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.
>
>I other words:
>    foo = NULL yields 'foo IS NULL'
>    foo = 3 should yield 'foo = 3' and NOT this 'foo = 3 and fo IS NULL'
>
>...assuming I understand the question.

At 9:04 PM -0500 1/26/08, Christopher Laco wrote:
>My bad:
>
>foo = 3 should yield 'foo = 3' and NOT this 'foo = 3 OR foo IS NULL'

You partly understood, partly not.

Under my proposal, saying "foo != 3" would return foo that are NULL, 
because they are not 3.

I'm certainly *not* saying that "foo = 3" would return NULL foo.

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 =  NULL                 foo IS NULL

   foo != NULL                 foo IS NOT NULL

   foo =  3                    foo = 3

   foo != 3                    foo != 3 OR foo IS NULL
                         or?   NOT (foo = 3)

   foo != 3 AND foo != NULL    foo != 3

   foo = 3 OR foo = NULL       foo = 3 OR foo IS NULL

Now, when the system knows that foo is not nullable, then the 
scenarios reduce to and optimize to:

   foo =  3    foo =  3

   foo != 3    foo != 3

And I anticipate that known not-nullable fields will be in use more 
than half of the time in practice, and ideally a lot more often than 
that.

I hope this is more clear now.

-- Darren Duncan