[hibernate-issues] [Hibernate-JIRA] Commented: (JPA-28) Simple Query with guarded Null Value fails with Apache Derby

Simple Query with guarded Null Value fails with Apache Derby ------------------------------------------------------------ Key: JPA-28 URL: https://hibernate.onjira.com/browse/JPA-28 Project: Java Persistence API Issue Type: Bug Environment: Hibernate 3.6.9.Final derbyclient 10.8.2.2 Reporter: Bernard Priority: Critical Labels: jpa2 Attachments: NullParameterHibernateHsqlMaven.zip, NullParameterHibernateMaven.zip The attached testcase is similar to http://en.wikipedia.org/wiki/Java_Persistence_Query_Language#Examples that shows a JPQL query: SELECT a FROM Author a WHERE :lastName IS NULL OR LOWER(a.lastName) = :lastName Hibernate crashes in case where the parameter value is null: org.hibernate.exception.DataException: could not execute query Caused by: java.sql.SQLDataException: An attempt was made to get a data value of type 'VARBINARY' from a data value of type 'VARCHAR'. I would expect that this type of query succeeds, because an equivalent raw JDBC testcase (included) succeeds also. String sql = "SELECT ID, NAME, region_id FROM CUSTOMERORDER WHERE ((? IS NULL) OR (region_id = ?))"; PreparedStatement pStmt = conn.prepareStatement(sql); Integer regionId = null; pStmt.setObject(1, regionId); pStmt.setObject(2, regionId); ResultSet result = pStmt.executeQuery(); Success! For some background, please see https://issues.apache.org/jira/browse/DERBY-1938 "Add support for setObject(<arg>, null)" which is included since db-derby-10.7.1.1 It would be great to see an early assessment in case this is a database driver issue not Hibernate.