Обсуждение: Cannot pass null in Parameter in Query for ISNULL
Hi, We have a driver issue with postgresl that doesn't exist with other databases. It happens with Java JPA implementations when a very simple query is executed like: SELECT a FROM Author a WHERE :lastName IS NULL OR LOWER(a.lastName) = :lastName See http://en.wikipedia.org/wiki/Java_Persistence_Query_Language#Examples :lastName is a parameter, and the null check is on the parameter so there shouldn't be a problem with this at all. Maciek has suggested a solution for this driver issue at http://archives.postgresql.org/pgsql-jdbc/2011-12/msg00017.php I wonder when this could be resolved? Apparently there is a related back-end issue. I have a few JUnit test for this so please ask. Kind Regards, Bernard
Bernard, This from Tom http://archives.postgresql.org/pgsql-jdbc/2011-12/msg00007.php Suggests that this is not a simple problem. Can you send me the unit tests though. Dave Cramer dave.cramer(at)credativ(dot)ca http://www.credativ.ca On Sat, Apr 21, 2012 at 8:57 PM, Bernard <bht237@gmail.com> wrote: > Hi, > > We have a driver issue with postgresl that doesn't exist with other > databases. > > It happens with Java JPA implementations when a very simple query is > executed like: > > SELECT a FROM Author a WHERE :lastName IS NULL OR LOWER(a.lastName) = > :lastName > > See > http://en.wikipedia.org/wiki/Java_Persistence_Query_Language#Examples > > :lastName is a parameter, and the null check is on the parameter so > there shouldn't be a problem with this at all. > > Maciek has suggested a solution for this driver issue at > http://archives.postgresql.org/pgsql-jdbc/2011-12/msg00017.php > > I wonder when this could be resolved? Apparently there is a related > back-end issue. > > I have a few JUnit test for this so please ask. > > Kind Regards, > > Bernard > > > > -- > Sent via pgsql-jdbc mailing list (pgsql-jdbc@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-jdbc
> 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
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@gmail.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@truviso.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
Вложения
> [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). " > --- This was essentially Oliver's argument. The spec specifically tells you what to do. Bernard, if you point this out to the projects in question what do they say ? Dave
On Mon, Apr 23, 2012 at 12:15 PM, Dave Cramer <pg@fastcrypt.com> wrote: >> [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). " >> --- > > > This was essentially Oliver's argument. The spec specifically tells > you what to do. Spec or no, if everyone else supports this, it'd be useful to offer compatibility here (contingent, of course, on level of effort and hackiness required)... > Bernard, if you point this out to the projects in question what do they say ? That's a good question: it's probably easier to address the issue in the ORM than in the driver (the ORM should have enough information to call the proper setNull). --- Maciek Sakrejda | System Architect | Truviso 1065 E. Hillsdale Blvd., Suite 215 Foster City, CA 94404 (650) 242-3500 Main www.truviso.com
Dave, I have filed issues with Hibernate and EclipseLink. No activity at Hibernate. EclipseLink has a workaround for this but it does not work with Postgresql. It is quite difficult to get them to do anything because they are fine with their preferred databases e.g. Oracle, Mysql. I found this interesting: "Add support for setObject(<arg>, null)" https://issues.apache.org/jira/browse/DERBY-1938 Bernard On Mon, 23 Apr 2012 15:15:56 -0400, you wrote: >> [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). " >> --- > > >This was essentially Oliver's argument. The spec specifically tells >you what to do. > >Bernard, if you point this out to the projects in question what do they say ? > >Dave
More evidence that it's a contentious issue. Dave Cramer dave.cramer(at)credativ(dot)ca http://www.credativ.ca On Tue, Apr 24, 2012 at 2:59 AM, Bernard <bht237@gmail.com> wrote: > Dave, > > I have filed issues with Hibernate and EclipseLink. No activity at > Hibernate. EclipseLink has a workaround for this but it does not work > with Postgresql. It is quite difficult to get them to do anything > because they are fine with their preferred databases e.g. Oracle, > Mysql. > > > I found this interesting: > > "Add support for setObject(<arg>, null)" > https://issues.apache.org/jira/browse/DERBY-1938 > > Bernard > > > On Mon, 23 Apr 2012 15:15:56 -0400, you wrote: > >>> [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). " >>> --- >> >> >>This was essentially Oliver's argument. The spec specifically tells >>you what to do. >> >>Bernard, if you point this out to the projects in question what do they say ? >> >>Dave >
It no longer is contentious - they fixed it in Apache Derby. Bernard On Tue, 24 Apr 2012 05:37:37 -0400, you wrote: >More evidence that it's a contentious issue. > >Dave Cramer > >dave.cramer(at)credativ(dot)ca >http://www.credativ.ca > > >On Tue, Apr 24, 2012 at 2:59 AM, Bernard <bht237@gmail.com> wrote: >> Dave, >> >> I have filed issues with Hibernate and EclipseLink. No activity at >> Hibernate. EclipseLink has a workaround for this but it does not work >> with Postgresql. It is quite difficult to get them to do anything >> because they are fine with their preferred databases e.g. Oracle, >> Mysql. >> >> >> I found this interesting: >> >> "Add support for setObject(<arg>, null)" >> https://issues.apache.org/jira/browse/DERBY-1938 >> >> Bernard >> >> >> On Mon, 23 Apr 2012 15:15:56 -0400, you wrote: >> >>>> [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). " >>>> --- >>> >>> >>>This was essentially Oliver's argument. The spec specifically tells >>>you what to do. >>> >>>Bernard, if you point this out to the projects in question what do they say ? >>> >>>Dave >>
Bernard, Do you know what their fix was ? Dave Cramer dave.cramer(at)credativ(dot)ca http://www.credativ.ca On Tue, Apr 24, 2012 at 1:39 PM, Bernard <bht237@gmail.com> wrote: > It no longer is contentious - they fixed it in Apache Derby. > > Bernard > > On Tue, 24 Apr 2012 05:37:37 -0400, you wrote: > >>More evidence that it's a contentious issue. >> >>Dave Cramer >> >>dave.cramer(at)credativ(dot)ca >>http://www.credativ.ca >> >> >>On Tue, Apr 24, 2012 at 2:59 AM, Bernard <bht237@gmail.com> wrote: >>> Dave, >>> >>> I have filed issues with Hibernate and EclipseLink. No activity at >>> Hibernate. EclipseLink has a workaround for this but it does not work >>> with Postgresql. It is quite difficult to get them to do anything >>> because they are fine with their preferred databases e.g. Oracle, >>> Mysql. >>> >>> >>> I found this interesting: >>> >>> "Add support for setObject(<arg>, null)" >>> https://issues.apache.org/jira/browse/DERBY-1938 >>> >>> Bernard >>> >>> >>> On Mon, 23 Apr 2012 15:15:56 -0400, you wrote: >>> >>>>> [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). " >>>>> --- >>>> >>>> >>>>This was essentially Oliver's argument. The spec specifically tells >>>>you what to do. >>>> >>>>Bernard, if you point this out to the projects in question what do they say ? >>>> >>>>Dave >>> >
Hi Dave, Their fix is https://issues.apache.org/jira/browse/DERBY-1938 I don't think their fix is complete, but in the issue that contentious issue was discussed. Bernard On Tue, 24 Apr 2012 13:44:56 -0400, you wrote: >Bernard, > >Do you know what their fix was ? > >Dave Cramer > >dave.cramer(at)credativ(dot)ca >http://www.credativ.ca > > >On Tue, Apr 24, 2012 at 1:39 PM, Bernard <bht237@gmail.com> wrote: >> It no longer is contentious - they fixed it in Apache Derby. >> >> Bernard >> >> On Tue, 24 Apr 2012 05:37:37 -0400, you wrote: >> >>>More evidence that it's a contentious issue. >>> >>>Dave Cramer >>> >>>dave.cramer(at)credativ(dot)ca >>>http://www.credativ.ca >>> >>> >>>On Tue, Apr 24, 2012 at 2:59 AM, Bernard <bht237@gmail.com> wrote: >>>> Dave, >>>> >>>> I have filed issues with Hibernate and EclipseLink. No activity at >>>> Hibernate. EclipseLink has a workaround for this but it does not work >>>> with Postgresql. It is quite difficult to get them to do anything >>>> because they are fine with their preferred databases e.g. Oracle, >>>> Mysql. >>>> >>>> >>>> I found this interesting: >>>> >>>> "Add support for setObject(<arg>, null)" >>>> https://issues.apache.org/jira/browse/DERBY-1938 >>>> >>>> Bernard >>>> >>>> >>>> On Mon, 23 Apr 2012 15:15:56 -0400, you wrote: >>>> >>>>>> [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). " >>>>>> --- >>>>> >>>>> >>>>>This was essentially Oliver's argument. The spec specifically tells >>>>>you what to do. >>>>> >>>>>Bernard, if you point this out to the projects in question what do they say ? >>>>> >>>>>Dave >>>> >>
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@gmail.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@truviso.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
Bernard <bht237@gmail.com> writes:
> May I ask for suggestions for a way forward towards a solution?
The long and the short of this is that your application, which
presumably knows what it might pass for this parameter, has to provide
that type information (by means of using the appropriate setXXX call).
If the server's parser tries to guess a type, that will simply move the
pain from this case to other cases, namely those where the choice really
matters and it guesses wrong. Similarly, the JDBC driver doesn't have
enough information to make a correct guess.
Having said that, it's interesting to wonder how much would break if
setObject were to arbitrarily assume the data type is TEXT. The
response to anybody complaining would be more or less what I'm saying
to you, which is "don't use setObject if you don't like the results".
Again, this would only be shifting the pain from some cases to others,
but I wonder whether there would be more or fewer pain cases.
regards, tom lane
SELECT ID, NAME, region_id FROM CUSTOMERORDER WHERE ((? IS NULL) OR (region_id = ?)) Based on boolean algebra, the engine query planner can simply eliminate the entire WHERE clause if the parameter is in fact null. I should say that the "?" refers in both instances to the same parameter. So the JDBC driver would only need to allow this to happen. In the case of null being passed to it, the type of null parameters would never matter. In this SQL statement, "(? IS NULL) OR ..." is coded for the purpose of allowing this to happen smoothly. I have verified the positive result with all other database engines/drivers and so I am confident there must be a definite way forward - it is just the question of how we get this done. As long as possible answers look less promising, I obviously have to assume that there is a misunderstanding somewhere. Kind Regards, Bernard On Sat, 12 May 2012 11:06:21 -0400, you wrote: >Bernard <bht237@gmail.com> writes: >> May I ask for suggestions for a way forward towards a solution? > >The long and the short of this is that your application, which >presumably knows what it might pass for this parameter, has to provide >that type information (by means of using the appropriate setXXX call). >If the server's parser tries to guess a type, that will simply move the >pain from this case to other cases, namely those where the choice really >matters and it guesses wrong. Similarly, the JDBC driver doesn't have >enough information to make a correct guess. > >Having said that, it's interesting to wonder how much would break if >setObject were to arbitrarily assume the data type is TEXT. The >response to anybody complaining would be more or less what I'm saying >to you, which is "don't use setObject if you don't like the results". >Again, this would only be shifting the pain from some cases to others, >but I wonder whether there would be more or fewer pain cases. > > regards, tom lane
This might be interesting (meanwhile the Microsoft, Oracle and Derby drivers have been fixed). If I read this correctly, then postgresql needs to be tested as follows (quote) until compatible: "This behavior is in fact enforced as part of the J2EE compatibility in the CTS. The JDBC Driver Test Suite is publicly accessible and can be used to weed out the JDBC drivers that are not compatible." Here is the link for the discussion that resulted in a fix for derby: https://issues.apache.org/jira/browse/DERBY-1938?focusedCommentId=12441025&page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#comment-12441025 Quote starts: Dag H. Wanvik added a comment - 10/Oct/06 00:47 I googled and found a discussion on this on the mailinglist for the Jakarta taglibs project: http://www.mail-archive.com/taglibs-user@jakarta.apache.org/msg07198.html This thread (dated; 2004) seems to indicate that at least the jTDS driver for SQL server accepts this usage. Digging more, the next link says that the behavior is sanctioned an in deed checked by the J2EE CTS (the person claims to have consulted former spec lead Jonathan Bruce): http://www.mail-archive.com/taglibs-user@jakarta.apache.org/msg06987.html This is a link to mail by Lance, indicating the Oracle can also accept this in a "CTS compatibility mode": http://www.mail-archive.com/taglibs-user@jakarta.apache.org/msg06997.html The JDBC Tutorial books says "no", the 4.0 API says "OK, but not guaranteed portable". The API is more authoritative than the book, according to Lance. Given the above, and that the API is more authoritative than the book, I think it would perhaps be OK for Derby to support this in the interest of ease of portability of apps to Derby. Not my itch, though :) Quote ends Kind Regards, Bernard On Sat, 12 May 2012 11:06:21 -0400, you wrote: >Bernard <bht237@gmail.com> writes: >> May I ask for suggestions for a way forward towards a solution? > >The long and the short of this is that your application, which >presumably knows what it might pass for this parameter, has to provide >that type information (by means of using the appropriate setXXX call). >If the server's parser tries to guess a type, that will simply move the >pain from this case to other cases, namely those where the choice really >matters and it guesses wrong. Similarly, the JDBC driver doesn't have >enough information to make a correct guess. > >Having said that, it's interesting to wonder how much would break if >setObject were to arbitrarily assume the data type is TEXT. The >response to anybody complaining would be more or less what I'm saying >to you, which is "don't use setObject if you don't like the results". >Again, this would only be shifting the pain from some cases to others, >but I wonder whether there would be more or fewer pain cases. > > regards, tom lane
Hi again.
First of all thanks once again for your time. I know we are all doing
this in our spare time. Most importantly I want to avoid causing any
friction with my arguments as they might appear inflammatory which I
definitely want to avoid.
Because you asked, please let me explain. From my perspective, solving
this would be worth very much, perhaps even worth more that it was
for the other databases some of which are partly in the mickey mouse
category (HSQL). The reason is that an extra feature (as it was for
the pioneers) did not have as much impact at first as the negative
publicity of the lack of it due to pain caused by the last one before
implementation. That is true for any product development in a
competetive landscape.
Please let me explain where the value is on application programming
level.
Imagine a very basic but voluminous query that contains a large number
of optional parameters. This type of query is a PITA. At first there
seems to be no way to write it as "static" query that can be validated
without testing all combinations at runtime. I think everyone knows
what it means to chop SQL into pieces. One can use the criteria API to
let the ORM generate the query at runtime and buy the type safety at
quite an expense of coding effort and possibly hitting bugs in the
criteria API.
With modern Java EE validators, some well designed JPQL queries
("named queries") are actually compiled at deployment time and
validated. They are not created in Application code dynamically. So if
they refer to a field that does not exist etc. then deployment fails
without the query ever being executed. You test them once with a junit
case in a single combination and that's it. This provides 100% type
safety.
The sad fact is that some people don't actually know how to write good
JPQL because they don't know what they are missing. I showed a
develper (who has developed SQL for years) such a static JPQL query
with guarded parameters (the stuff that does not work with postgresql)
in a IBM WebSphere environment and his eyes popped out. He just could
not believe that his complex problem could be solved with such a
simple query in a type safe manner.
So there is a huge gain on the application programming side quality
wise. On the other side, it appears to me that there are problems with
the postgresql driver that look similar to the problems that we are
facing in application programming. I hope that eventually this will
not be the case. I hope this can be done elegantly, ideally with the
side effect of a performance gain. Perhaps this needs some work on the
query planner as you say (I am not an expert).
Perhaps there is some value in looking at other open source database
engine code. HSQL is 100% compliant with my expectations. I have a
Maven testcase for it. Derby is mostly compliant except it does not
like "NULL IS NULL" (which I have filed a issue for) but it can do the
JCBC parameter bit.
IMHO it could be worth to start even with a partial solution or just
kicking the tyres if it gets the ball rolling. I mean unproductive
type restrictions are, well, just unproductive. And testing stuff that
has zero relevance is definitely in vain.
Kind Regards,
Bernard
On Sun, 13 May 2012 15:17:55 -0500, you wrote:
>>If the server's parser tries to guess a type, that will simply move the
>>pain from this case to other cases, namely those where the choice
>really
>>matters and it guesses wrong.
>
>Well, the server already guesses parameter types, no? In a Parse
>protocol message, any parameter type specification is optional. If you
>leave it out, the server guesses for you (and tells you about it in the
>ParameterDescription message). This is just about making the server
>smarter.
>
>JDBC spec or no, having the server process
>
>"SELECT NULL IS NULL"
>
>but balk at
>
>"SELECT $1 IS NULL" ($1 = NULL)
>
>is pretty silly. The type system is working against us here. I
>understand that due to planning and so on, this leads through two very
>different code paths, but I think the complaint is fundamentally sound.
>Whether it's worth addressing is a separate issue, and there's also the
>question of whether addressing it will resolve Bernard's complaint (I
>decided not to pursue it last time because I felt it would not; maybe
>I'm wrong).
>
>>Having said that, it's interesting to wonder how much would break if
>>setObject were to arbitrarily assume the data type is TEXT.
>
>I take it you mean "assume TEXT if the parameter is a Java null?" If the
>parameter is not null, the type system gives the driver enough
>information to do some mapping based on the Java type of the parameter.
>
>I tried this and one of the JDBC tests fails, but it may still be worth
>considering. My (trivial) change is here:
>
>https://github.com/deafbybeheading/pgjdbc/tree/null-parameter-type
>
>The test that breaks is the following:
>
>https://github.com/deafbybeheading/pgjdbc/blob/master/org/postgresql/tes
>t/jdbc2/ArrayTest.java#L48
>
>The code in the test is a simple NULL insert with no additional type
>information. Should we avoid breaking that? I have no particularly
>strong feelings there either way, but it indicates this won't be a
>"free" change.
>
>-Maciek
>If the server's parser tries to guess a type, that will simply move the >pain from this case to other cases, namely those where the choice really >matters and it guesses wrong. Well, the server already guesses parameter types, no? In a Parse protocol message, any parameter type specification is optional. If you leave it out, the server guesses for you (and tells you about it in the ParameterDescription message). This is just about making the server smarter. JDBC spec or no, having the server process "SELECT NULL IS NULL" but balk at "SELECT $1 IS NULL" ($1 = NULL) is pretty silly. The type system is working against us here. I understand that due to planning and so on, this leads through two very different code paths, but I think the complaint is fundamentally sound. Whether it's worth addressing is a separate issue, and there's also the question of whether addressing it will resolve Bernard's complaint (I decided not to pursue it last time because I felt it would not; maybe I'm wrong). >Having said that, it's interesting to wonder how much would break if >setObject were to arbitrarily assume the data type is TEXT. I take it you mean "assume TEXT if the parameter is a Java null?" If the parameter is not null, the type system gives the driver enough information to do some mapping based on the Java type of the parameter. I tried this and one of the JDBC tests fails, but it may still be worth considering. My (trivial) change is here: https://github.com/deafbybeheading/pgjdbc/tree/null-parameter-type The test that breaks is the following: https://github.com/deafbybeheading/pgjdbc/blob/master/org/postgresql/tes t/jdbc2/ArrayTest.java#L48 The code in the test is a simple NULL insert with no additional type information. Should we avoid breaking that? I have no particularly strong feelings there either way, but it indicates this won't be a "free" change. -Maciek