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

[Christopher Laco]

Darren Duncan wrote:
> At 9:01 PM -0500 1/26/08, Christopher Laco wrote:
>> In perl terms, I think it's great if {foo =3D> NULL} translates to foo =

>> IS NULL. DWIM++
>>
>> Now, according to SQL92, any comparison of =3D or !=3D against NULL =

>> returns false. So, even a {foo =3D> 3} should not return rows that are =

>> NULL, because NULL is neither =3D or !=3D to 3.
>>
>> http://msdn.microsoft.com/library/default.asp?url=3D/library/en-us/tsqlr=
ef/ts_set-set_9rec.asp =

>>
>>
>> The SQL-92 standard requires that an equals (=3D) 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 =3D 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 =3D NULL yields 'foo IS NULL'
>>    foo =3D 3 should yield 'foo =3D 3' and NOT this 'foo =3D 3 and fo IS =
NULL'
>>
>> ...assuming I understand the question.
> =

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

> You partly understood, partly not.
> =

> Under my proposal, saying "foo !=3D 3" would return foo that are NULL, =

> because they are not 3.
> =

> I'm certainly *not* saying that "foo =3D 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 =3D  NULL                 foo IS NULL
> =

>   foo !=3D NULL                 foo IS NOT NULL
> =

>   foo =3D  3                    foo =3D 3
> =

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

To me, this is the dangerous one since it does't match normal ANSI SQL =

behavior. I'd rather that if I want nulls because they aren't 3, that I =

have to specifically say that:

   foo !=3D 3 or foo =3D NULL


-------------- next part --------------
A non-text attachment was scrubbed...
Name: signature.asc
Type: application/pgp-signature
Size: 249 bytes
Desc: OpenPGP digital signature
Url : http://lists.scsys.co.uk/pipermail/dbix-class/attachments/20080126/52=
0c83f3/signature.pgp