Re: Issue with NULL varchars

antony baxter wrote:

You should probably enable query tracing, hand-execute the problem
queries, and examine what's going on that way.

However, if I'm not mistaken (which I could very easily be) your issue
is probably here:

> p = c.prepareStatement("SELECT id FROM testing WHERE language =
> ? AND country = ? AND variant = ?");

If `variant' is NULL, this might be producing a query like:

SELECT id FROM testing WHERE language = 'en' AND country = 'GB' AND
variant = NULL ;

... which, because of the following rule for NULL equality:

craig=# SELECT (NULL = NULL) IS NULL;
?column?
----------
t
(1 row)

won't match anything.

You probably wanted to say:

SELECT id FROM testing WHERE language 'en' AND country = 'GB' AND
variant IS NULL;

Yes, that's a pain.

It's possible to override this behaviour using a variable provided for
MS Access compatibility that treats 'NULL = NULL' as 't' instead of
NULL, but it might be better (more portable, safer, more
standards-compliant) to fix up the query.

> which implies that the table is created, the row is inserted, the row
> is retrieved when selecting by its Id, but when we try and search for
> anything with a NULL varchar value, nothing is returned.

If you:

SELECT * FROM testing;

you should see the inserted row, and if you execute the query your JDBC
layer generates from your parameterised query (which you can discover by
enabling query logging) it'll probably fail to match unless you rewrite
it to use IS NULL.

--
Craig Ringer