haskelldb-users

[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

Re: [Haskelldb-users] strange/wrong NULL handling

[Justin B. <jgb...@gm...>]

Mathjis,

I am glad you are putting this much thought into haskelldb. My comments below.

On Tue, May 26, 2009 at 4:32 PM, Mathijs Kwik <blu...@gm...> wrote:
> 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.
>

Even though this is SQL-like behavior, it takes away some of what
HaskellDB gives you. Like Haskell, HaskellDB forces you to think about
types. Allowing .==. to match (Maybe expr) to (expr) is like allowing
null values for any object in Java or C#. It's convenient but
dangerous. HaskellDB takes the route that you need to explicitly deal
with the situation, even if SQL will silently fail for you. I think
you have pointed out a weakness with (Maybe expr) compared to (Maybe
expr), in that you need to know the SQL behavior for NULLs, but I
don't think that asks a lot.

Back to your point about .==., I could see an operator which behaves
the way you describe, but I don't think the meaning of .==. should be
changed.

> So the question seems to be:
> Do we want to abstract away from SQL by emulating haskell operators?
> (where .==. acts like ==)

...

> If we want to stay closer to SQL (.==. means sql =) ...

I think of HaskellDB taking a third route - a type-safe way to
generate SQL queries. When you write haskellDB code, you are thinking
of the query generated. In that case, .==. generates an equality
comparison in SQL. If you want to generate a null comparison, you use
isNull/fromNull. Still, the case of (constant Nothing .==. constant
Nothing) being translated to (NULL == NULL) is a wart. A small one,
though.

> 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?
>

For me, HaskellDB is a concise way to write SQL. It does not model a
relational DB. It leverages the Haskell type system to make it a
little easier to write mostly safe SQL. When combined with table
definitions generated from your database, it gives you a great way to
know when a query has broken because a table definition has changed.
The issues you have pointed out have merit, but I don't (yet) see a
significant benefit in solving them.

Justin