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

[Christopher Laco]

Darren Duncan wrote:
> Hello,
> =

> This email is primarily concerned with the possible future internals of =

> DBIx-Class with respect to an intermediate AST based on Muldis D that =

> you can convert to/from SQL, but this email is secondarily concerned =

> with a possible future enhancement to the public interface of DBIx-Class.
> =

> In a nutshell, one issue that often trips up new (and experienced) SQL =

> users is its 3-valued logic such that you can't just say "foo =3D NULL" t=
o =

> match null values, requiring "IS NULL" instead, or that both "foo =3D 3" =

> and "foo <> 3" will not match foo that are NULL.  And AFAIK in some =

> situations SQL treats 2 NULL as matching and other times non-matching.
> =

> My question for you is whether, from a typical or new or experienced =

> user's point of view, it would be preferable for the DBMS to treat NULL =

> like an actual single value, which is equal to itself while not equal to =

> every other normal value that is not NULL?  Consequences of this include =

> that "foo =3D NULL" will DWIM (result in true when foo is NULL), and "foo =

> <> 3" will be true when foo is NULL (foo is anything but 3).
> =

> (Other consequences are that null-allowing columns with a single-column =

> unique key constraint will allow a NULL value to appear exactly once in =

> the column.  In a naive case, a foreign key relationship would then =

> match a NULL in one table with one in another, or require one in the =

> parent.  Though if this isn't desired, then the foreign key constraint =

> could be defined to only apply to child table values that do not equal =

> NULL.  Both of which I think SQL already does and that doesn't =

> necessarily need to change.)
> =

> Regardless of whether any updates to the DBIx-Class public interface are =

> made, I believe it is preferrable to think in terms of NULL =3D NULL and =

> structure the AST along those principles, so you get more AST simplicity =

> or DWIM when you want that to be the case, while you may or may not get =

> more AST complexity when you don't want that to be the case.  Or =

> regardless of whether that happens, the AST is simpler when dealing with =

> not-nullable data, and marginally more complex when dealing with =

> nullable data.
> =

> The latest release 0.18.0 of the Muldis D spec, =

> http://search.cpan.org/dist/Language-MuldisD/ , clarifies that any 2 =

> table ("relation") values are identical when they contain zero rows =

> ("tuples") and they have the same column ("attribute") names, regardless =

> of the declared types of those columns.  Also, new subtypes Nothing,Just =

> of Maybe were added.  Now, Muldis D is optimized towards not-nullable =

> data, so in SQL terms, if you simply say "foo Int" as a field type, that =

> is implicitly not nullable, and you have to explicitly say nullable to =

> make it so, as "foo Maybe{Int}".  Defining a field nullable is actually =

> defining the field to contain a table value whose sole column name is =

> 'value' and which may contain only zero rows or 1 row; if it contains =

> zero, it is null, if it contains 1 row, namely the normal value, it is =

> not null. You could say that not-nullable means unboxed while nullable =

> means boxed.  But those are details that users typically don't have to =

> know about.  The single Nothing value matches the empty case, and Just =

> constructs a full case.  Testing a not nullable field is simple as "foo =

> =3D 3" or "foo <> 3", while testing a nullable is "foo =3D Nothing" (SQL =

> "foo IS NULL") "foo =3D Just(3)" (SQL "foo =3D 3") or "foo <> Just(3)" (S=
QL =

> "foo <> 3 OR foo IS NULL") "foo <> Just(3) and foo <> Nothing" (SQL "foo =

> <> 3").  In this system, "Nothing =3D Nothing" is true, "Nothing =3D 3" a=
nd =

> "Nothing =3D Just(3)" are both false, "Nothing <> 3" and "Nothing <> =

> Just(3)" are both true, "3 =3D Just(3)" is false, "Just(3) =3D Just(3)" i=
s =

> true, "3 =3D Just(3).attr" is true.  To do math with not-nullable, you =

> just say eg "row.x / row.y", with nullable but known not null you say =

> "row.x.attr / row.y.attr".
> =

> So, assuming it is clearly documented, do you forsee any problems in =

> using an AST that is 2-valued-logic and assumes NULL =3D NULL, where you =

> have to explicitly say when you don't want that behaviour?  It should =

> still be possible to generate SQL with the desired semantics, so this is =

> more a useability question.
> =

> Thank you. -- Darren Duncan

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/tsqlref/=
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 NU=
LL'

...assuming I understand the question.

-=3DChris

-------------- 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/11=
e272a8/signature.pgp