Hi Gordon,
ok, I filed a P2 and assinged it to Tom:
https://glassfish.dev.java.net/issues/show_bug.cgi?id=388
Regards Michael
> 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
>
>