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