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

[Tom van den Berge (JIRA)]

    [ http://opensource.atlassian.com/projects/hibernate/browse/HHH-3796?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=32558#action_32558 ] 

Tom van den Berge commented on HHH-3796:
----------------------------------------

Your statement is not quite correct, since Hibernate *does* convert "column = null" to "column is null" when null is passed in as a literal instead of a parameter.

In order to have consistent behaviour, Hibernate should treat comparison with a null literal similar to comparison with a parameter with null value.

> 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
>            Assignee: Steve Ebersole
>
> 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