Re: Interpretability with xDBC specification

Oliver Jowett <oliver@opencloud.com> wrote:

> If you'd like to prepare a compliance table yourself, I'm sure the
> list would be interested in the results.

FWIW, we took a great deal of care to keep our code portable and had
few problems converting to PostgreSQL.  Probably the biggest problem
for *us* was the fact that {fn IFNULL(NULL, NULL)} didn't return the
same thing as a bare NULL literal.  A bare NULL is of unknown type,
whereas this escape sequence returns a NULL coerced to the TEXT
type, which didn't work very well in some contexts when the
arguments were dates or numbers.

I understand why this is hard to handle in the PostgreSQL backend
for COALESCE, but we were able to get past it in JDBC by hacking the
driver to replace "{fn IFNULL(NULL, NULL)}" with "NULL" -- as a
stopgap until we could push type information far enough down into
statement generation to wrap the NULL arguments, so that we were
generating "{fn IFNULL(CAST(NULL AS INTEGER), CAST(NULL AS
INTEGER))}" instead.  While I agree that pushing that type
information down and generating the CASTs into the statement is
better, I don't believe that current behavior here is strictly
compliant for the {fn IFNULL()} portability escape.  In any event,
something similar to the hack I used (although it would have to be
made more bullet-proof) might ease migration to PostgreSQL for some.

-Kevin