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

[Bernard (JIRA)]

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
         Attachments: 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.


--
This message is automatically generated by JIRA.
For more information on JIRA, see: http://www.atlassian.com/software/jira