Skip site navigation (1) Skip section navigation (2)

Re: Issue with NULL varchars

From: Craig Ringer <craig(at)postnewspapers(dot)com(dot)au>
To: antony baxter <antony(dot)baxter(at)gmail(dot)com>
Cc: pgsql-jdbc(at)postgresql(dot)org
Subject: Re: Issue with NULL varchars
Date: 2008-03-25 10:00:40
Message-ID: 47E8CD48.3030801@postnewspapers.com.au (view raw or flat)
Thread:
Lists: pgsql-jdbc
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

In response to

pgsql-jdbc by date

Next:From: Craig RingerDate: 2008-03-25 10:25:59
Subject: Re: Issue with NULL varchars
Previous:From: antony baxterDate: 2008-03-25 09:49:11
Subject: Issue with NULL varchars

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group