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

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