September 26, 2009

Oracle: a reason to hate it for ORM developers

We're fighting with one quite well-known and annoying issue with Oracle: it considers empty string equal to null (but not vice versa!). Just think about this:
C#:  someString==""   // false, if @someString!=""; otherwise, true
SQL: @someString = '' -- NULL in any case:
                      -- Oracle represents this as @someString = NULL!

C#:  someString.Length==0    // true, if @someString==""
SQL: LENGTH(@someString) = 0 -- NULL in the same case!

C#:  someString.Trim.Length==0       // true, if @someString==""
SQL: LENGTH(TRIM(BOTH FROM ' ')) = 0 -- NULL in the same case!

So we (actually, Denis Krjuchkov - he fights with Oracle now) really don't know how to deal with this. In many cases (e.g. in case with string.Length) we implement "backward" logic (null.Length==0), which is correct from the point of logic, we even have similar issue for IMDB provider. But it's really unclear what to do with many other cases.

If you have any ideas on how to deal with this, please notify us. But don't offer e.g. to prefix all stored strings and query parameters of string type with " " - this might work, but it will be a complete hell to study our queries in this case.

I'm curious, why they made this decision... It is absolutely crazy: why empty string means "undefined value" (NULL) there? It is precisely defined value. As Denis has suggested, probably they just wanted to save few bytes per each of such value in these early times than Oracle was established (1970).

You might know that earlier I wrote about another similar issue (see "7: Oracle support" there) with nested SELECT in SELECT clause. There was nearly the same case - intentionally broken genericity, and this is really disappointing. Why there are no such issues with free PostgreSQL? Oracle proves once more it is so cool that only humans are admitted to write its queries. Machines, get your hands off Oracle!

It's interesting to know, what other ORM developers do with this? Do they deal with this at all?

Conclusion: think twice before making optimizations that change default logic in few particular cases. Likely, breaking the genericity is much worse.