[Haskelldb-users] strange/wrong NULL handling

[Mathijs K. <blu...@gm...>]

Hi,

I think there is a mismatch between how sql handles NULL and how
haskelldb handles it.

In haskelldb, fields that can be NULL are mapped to Maybe.
Comparing 2 fields that can be NULL using .==. will translate into
"where field1 = field2'
The sql = operator will not match any records where the field that is
being compared is NULL.

So if the .==. operator should mean something like "where the fields
have the exact same value", it doesn't work, since results that have
NULL for both the fields match this predicate but won't be returned.
The correct behaviour in that case would be "where field1 = field2 OR
(field1 IS NULL and field2 IS NULL)" for which there might be some
sql-helper-function.

Since this is not the current behaviour (nor wanted behaviour in most
cases), the .==. operator probably means "where both fields are filled
in (not NULL) and have the same value".
In this case, comparing a field that _can_ be NULL to a field that
can't be NULL should be allowed, since the meaning of .==. in this
case dictates that all values will be not NULL.

This example can probably be simplified by not looking at
field-comparison, but at simple constants.
If we want Nothing to be the representation of NULL, "field1 .==.
constant Nothing" should become "field1 IS NULL" in sql instead of
"field1 = NULL" (current behavior).

So the question seems to be:
Do we want to abstract away from SQL by emulating haskell operators?
(where .==. acts like ==)
If so, the operators and types should be extended somehow, so the
conversion/mapping will keep the meaning intact.
in that case:
"field1 .==. constant Nothing" should become "field1 IS NULL",
"field1 .==. field2" should become "where field1 = field2 OR (field1
IS NULL and field2 IS NULL)" and
"field1 .==. field2 .&&. notNull field1 .&&. notNull field2" should
become "where field1 = field2"

If we want to stay closer to SQL (.==. means sql =), some changes are
required to make Maybe Int comparable to Int, since sql's = operator
allows comparing a non-nullable field to a nullable one.
It's a bit more than just comparison operators, since sql allows other
stuff on nullable fields as well, like "select field1 * 3600 from
table1" so Maybe Int * Int -> Int

Maybe I'm missing something here, I'm not a haskell expert, but it
seems the current behaviour of the operators doesn't match SQL or
haskell, which leaves us with some hybrid that is less flexible than
both.

Any thoughts?

Mathijs