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

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(at)gmail(dot)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