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

[Steve Ebersole (JIRA)]

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

Steve Ebersole commented on HHH-3796:
-------------------------------------

There is a big difference.  The first form we know up front before we render the sql (prepared statement).  In the second we'd have to check the parameter values every time the query is executed and potentially render a whole new sql statement.  Not gonna happen.

> 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