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