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

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.