Hello Michael,
Please file a high priority bug for this issue. This is mostlikely happening because we are converting the '1' to a positional parameter in our custom SQL parameter support.
--Gordon
-----Original Message-----
From: Michael Bouschen [mailto:Michael.Bouschen_at_Sun.COM]
Sent: Friday, March 10, 2006 10:57 AM
To: Peter Krogh
Cc: persistence_at_glassfish.dev.java.net
Subject: Re: EJB QL: IS NULL vs. = NULL (glassfish issue 340)
Hi Peter,
just a heads up when binding will be turned on by default.
It looks like when setting the property toplink.bind-all-parameters to
true, all parameters and literals are replaced by ? in the SQL string
and the value is bound to the PreparedStatement. We might run into a
problem with the EXISTS subquery on derby. There was a problem in case
the subquery selected an entity:
SELECT c FROM Customer c WHERE EXISTS
(SELECT o FROM Order o WHERE o.orderId > 10)
The SQL subquery used multiple columns in the SELECT clause which is not
supported by derby. We decided to generate a SELECT 1 instead, because
for an EXISTS it really does not matter what gets returned.
With the property set the EJB QL query is mapped to
SELECT t0.ID, ... FROM CUSTOMER_TABLE t0 WHERE
EXISTS (SELECT ? FROM ORDER_TABLE t1 WHERE (t1.ID > ?))
bind => [1, 10]
which results in a SQLException on derby:
Caused by: SQL Exception: There is a ? parameter in the select list.
This is not allowed.
Do you have an idea?
Regards Michael
> Hi Peter,
>
> I tried using the toplink.bind-all-parameters property and it seems to
> work. It generates 'NAME = ?' which executes on derby. I hope this is
> all we need to do to fix the IS NULL vs. = NULL issue.
>
> Any idea when the next push to CVS will happen?
>
> Regards Michael
>
>> If binding the value works with Derby, then this will likely be solved
>> with the next push to CVS as binding will be turned on by default.
>>
>> You can try it by turning binding on yourself to see that it will
>> work: <property name="toplink.bind-all-parameters" value="true"/>
>>
>> Peter
>>
>> -----Original Message-----
>> From: Michael Bouschen [mailto:Michael.Bouschen_at_Sun.COM]
>> Sent: Thursday, March 09, 2006 9:07 AM
>> To: Peter Krogh
>> Cc: persistence_at_glassfish.dev.java.net
>> Subject: EJB QL: IS NULL vs. = NULL (glassfish issue 340)
>>
>>
>> Hi Peter,
>>
>> I started looking at glassfish issue 340 ("the famous IS NULL vs. = NULL
>> issue). I experimented with setting setShouldPrepare(false) on the
>> DatabaseQuery instance that is generated by the EJB QL compiler. With
>> this flag set to false the generated SQL WHERE clause uses 'IS NULL'
>> instead of '= NULL'.
>>
>> However, I'm not sure whether this is the right approach to fix the
>> issue. According to the spec 'IS NULL' and '= NULL' have different
>> semantics. Section "4.12 Null Values" says: "Comparison or arithmetic
>> operations with a NULL value always yield an unknown value.". Section
>> "4.6.4 Input Parameters" explicitly mentions null input parameter: "Note
>> that if an input parameter value is null, comparison operations or
>> arithmetic operations involving the input parameter will return an
>> unknown value.". I read this that a query
>> SELECT p FROM Product p WHERE p.name = NULL
>> would not return any products, even if there are products w/o name. The
>> same holds true for a query using an input parameter
>> SELECT p FROM Product p WHERE p.name = :name
>> when setting the actual value of the input parameter to null (this is
>> the scenario described in issue 340).
>>
>> When setting the shouldPrepare flag to false the generated SQL includes
>> 'NAME IS NULL'. This returns products w/o a name, which (from my point
>> of view) does not implement the semantics of the EJB QL query.
>>
>>
>> I think the issue is that the generated SQL 'NAME = NULL' runs ok on
>> Oracle, but leads to an exception on derby:
>> Syntax error: Encountered "NULL" at line 1, column 200.Error Code: -1
>>
>> Is there a way to map an EJB QL input parameter to a SQL parameter in
>> the generated SQL? The WHERE clause of the generated SQL would be 'WHERE
>> NAME = ?' and we would bind the value null to the parameter. This works
>> with derby.
>>
>> BTW, the same applies if the WHERE clause of the EJB QL query checks an
>> input parameter being null, e.g. ':name IS NULL'. This is mapped to
>> 'NULL IS NULL' in SQL which leads to the same exception in derby. When
>> using SQL parameter the SQL would be '? IS NULL' which executes ok on
>> derby.
>>
>> Regards Michael