Fwd: Cannot pass null in Parameter in Query for ISNULL

Forwarding to the list so others can take a look as well.
Unfortunately, these seem to be far from minimal, but may be a good
starting point. I'm also attaching a minimal test case that I threw
together (compile with "javac Test.java", run with "java -cp
.:${POSTGRESQL_JAR} Test", note that it's currently configured for the
non-standard port 5433), but looking more at Bernard's example, fixing
that may not be enough to handle his use case. I think the problem
with his query is that the server needs type information about the
parameter to find the right comparison operator for text (the result
of LOWER(a.lastName)) and the parameter. If the parameter is unknown,
I bet that (comparison of text and unknown) doesn't exist. Now, this
need not ever be evaluated, because the parameter is indeed null, but
I imagine the planner isn't happy if it has no idea how to plan
something (even things that won't actually be executed).

I do see now that getting this to work with the server and the current
extended query protocol could be a tricky proposition.

One potential workaround that I tossed out earlier would be for the
driver to treat null parameter values specially in prepared statements
and inline them as needed. That is, if you do

stmt = conn.prepareStatement("SELECT 1 WHERE ? IS NULL OR ? IS NULL");

the driver would actually manage *seperate* prepared statements
depending on what parameters you set:

stmt.setObject(1, null); stmt.setObject(2, null); : query is sent to
backend as "SELECT 1 WHERE NULL IS NULL OR NULL IS NULL" (with no
parameters)
stmt.setObject(1, "foo"); stmt.setObject(2, null); : query is sent to
backend as "SELECT 1 WHERE $1 IS NULL OR NULL IS NULL" (with $1 =
"foo")
stmt.setObject(1, null); stmt.setObject(2, "bar"); : query is sent to
backend as "SELECT 1 WHERE NULL IS NULL OR $1 IS NULL" (with $1 =
"bar")
stmt.setObject(1, "foo"; stmt.setObject(2, "bar"); : query is sent to
backend as "SELECT 1 WHERE $1 IS NULL OR $2 IS NULL" (with $1 = "foo",
$2 = "bar")

That is, the driver would have to manage separate wire statements for
every permutation of null and non-null values. The null ones could be
allocated lazily, but it's still a pain. It should work, though. Now,
is this circus worth more user-friendliness in the JDBC API,
user-friendliness that the spec explicitly warns you not to rely on
[1]? I'm leaning toward no, but maybe there's another way to address
this issue...

[1] (from PreparedStatement.setObject): "Note: Not all databases allow
for a non-typed Null to be sent to the backend. For maximum
portability, the setNull or the setObject(int parameterIndex, Object
x, int sqlType) method should be used instead of setObject(int
parameterIndex, Object x). "
---
Maciek Sakrejda | System Architect | Truviso

1065 E. Hillsdale Blvd., Suite 215
Foster City, CA 94404
(650) 242-3500 Main
www.truviso.com

---------- Forwarded message ----------
From: Bernard <bht237(at)gmail(dot)com>
Date: Sun, Apr 22, 2012 at 11:44 PM
Subject: Re: [JDBC] Cannot pass null in Parameter in Query for ISNULL
To: Maciek Sakrejda <msakrejda(at)truviso(dot)com>

Hi Maciek,

Thanks for following this up. Please see the attached unit tests. Most
of them are on JPA level, but I think they should pass because HSQL
passes all of them. There is of course still a chance that Hibernate
has the Postgresql dialect wrong. Therefore I think the JDBC testcase
is useful. Please let me know what else I can do.

Kind Regards,

Bernard

On Sun, 22 Apr 2012 20:07:39 -0700, you wrote:

>> This from Tom
>>
>> http://archives.postgresql.org/pgsql-jdbc/2011-12/msg00007.php
>>
>> Suggests that this is not a simple problem.
>
>Thanks, Dave. A follow-up from me to Tom's e-mail throws around some
>other ideas, but got no responses. I'll try to put my patches on
>github this week so we can discuss something more concrete. Bernard,
>having your (ideally, minimized) unit tests here would help as well.
>---
>Maciek Sakrejda | System Architect | Truviso
>
>1065 E. Hillsdale Blvd., Suite 215
>Foster City, CA 94404
>(650) 242-3500 Main
>www.truviso.com