[hibernate-issues] [Hibernate-JIRA] Created: (HHH-3796) Non-ANSI92 compliant SQL is generated when binding null values

[Tom van den Berge (JIRA)]

Non-ANSI92 compliant SQL is generated when binding null values
--------------------------------------------------------------

                 Key: HHH-3796
                 URL: http://opensource.atlassian.com/projects/hibernate/browse/HHH-3796
             Project: Hibernate Core
          Issue Type: Bug
    Affects Versions: 3.2.6
         Environment: HSQLDB
            Reporter: Tom van den Berge


When binding a parameter with a null value to a query (using the JPA interface), Hibernate generates SQL that is not ANSI-92 SQL compliant. The JPAQL

Query q = ..."from entity where column = :possibleNullValue";
q.setParameter("possibleNullValue", null);

results in the following SQL:

"select ... from entity where column = null".

According to the ANSI-92 SQL standard, comparing with null values must always be done with the "is" operator. Using the equals (=) operator always evaulates to FALSE, and will therefore not produce any results.

Many databases relax this requirement and treat the equals operator similar to the "is" operator when comparing null values. However,  HSQLDB does not (it enforces strict ANSI-92 compliance), which means that this kind of queries does not produce any results on this database. There are probably other database that do this, too.

Strangly enough, when the parameter is not bound to the query, but inserted in the JPAQL, Hibernate generates correct SQL:

"from entity where column = null"

is converted to SQL:

"select ... from entity where column is null".

So a workaround is to provide two different queries: one for non-null values (using parameter binding), and one for null values (using "null" in the JPAQL). Rather silly, but it works.


-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: http://opensource.atlassian.com/projects/hibernate/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira