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

[Bernard (JIRA)]

    [ https://hibernate.onjira.com/browse/JPA-28?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=45692#comment-45692 ] 

Bernard commented on JPA-28:
----------------------------

Kristian, Thanks for the assessment. As you noted, there are probably two issues, at least one on the derby side and possibly one on the Hibernate side. The succeeding HSQL variant of the testcase may help to identify the VARBINARY issue.

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

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