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

Hi,

Many thanks to Maciek for his work.

May I ask for suggestions for a way forward towards a solution? I hope
I understand Maciek's comments correcly - they make me think that it
would be too hard or inefficient to solve this on the JDBC driver side
alone. The workaround reminds me of the pain that I feel when coding
JPA application level workarounds. My gut feeling is also that a JDBC
driver should not contain too much logic of this kind, and that the DB
engine query planner would be better suited to figure out what to do
with NULL IS NULL, which is in a way what is needed. I know that the
el cheapo Java database engine HSQL can do it, so there would be
source code to look into. I have a testcase version for HSQL, too if
required.

Kind Regards,

Bernard

On Mon, 23 Apr 2012 10:00:35 -0700, you wrote:

>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