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

Re: Bug: Cannot pass null in Parameter in Query for ISNULL

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Maciek Sakrejda <msakrejda(at)truviso(dot)com>
Cc: Oliver Jowett <oliver(at)opencloud(dot)com>, bht(at)actrix(dot)gen(dot)nz, pgsql-jdbc(at)postgresql(dot)org
Subject: Re: Bug: Cannot pass null in Parameter in Query for ISNULL
Date: 2011-12-02 00:21:41
Message-ID: 19011.1322785301@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-jdbc
Maciek Sakrejda <msakrejda(at)truviso(dot)com> writes:
> I guess one possible way to do this would be to only inline null
> values when binding parameters, although that seems on the dangerous
> side of clever (and you'd have a hell of a time getting it to work
> with named prepared statements).

That seems unlikely to help much.  Presumably, the OP isn't interested
in a constant-true result, so what he's really doing is hoping that
"$1 IS NULL" will report whether or not the passed parameter is null,
when sometimes it will be and sometimes it won't.

The difficulty from the server's point of view is really what data type
should be reported for the parameter symbol, if the client does a
Describe on the prepared statement.  There is no context that would let
us guess what the application is thinking will happen.  If we simply
removed the error checks about this, what would happen is that the
server would report "unknown" (yes, there is actually a type named
"unknown" in the Postgres system catalogs), and maybe the client-side
code would deal gracefully with that or maybe it wouldn't.  If we try to
resolve the unknown to any specific ordinary type, we're likely to break
things even worse, if the application is expecting something different.

A lesser problem is that if we let the type stand as "unknown", the
existing server I/O functions take that as equivalent to "text".
Which means that if you send some binary data (perhaps because the
application thinks the parameter is integer), the server will most
likely spit up, because the data isn't a well-formed string in the
proper encoding.  We could probably redefine things to avoid this
issue, but I'd be a bit worried about breakage.

In the end the nastiest issues are probably on the driver or interface
library.  If it doesn't know what data type the parameter is supposed to
be, how is it going to go about forwarding a value to the server?  Dumb
libraries like libpq may not have much of an issue here, but anything
with any intelligence is likely to be unhappy.

			regards, tom lane

In response to

Responses

pgsql-jdbc by date

Next:From: Merlin MoncureDate: 2011-12-02 16:16:17
Subject: Re: Java LISTEN/NOTIFY client library work-around
Previous:From: Maciek SakrejdaDate: 2011-12-01 19:41:10
Subject: Re: Bug: Cannot pass null in Parameter in Query for ISNULL

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