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

[Kristian Waagan (JIRA)]

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

Kristian Waagan commented on JPA-28:
------------------------------------

It would be interesting to know where Hibernate gets VARBINARY from, i.e. if there is a bug in Hibernate (or the Derby dialect?) or a bug in Apache Derby.
Note that Derby doesn't support the "? IS NULL" clause when ? is set to NULL, so the test case will fail also when the first issue has been fixed.
As noted in [1], "NULL IS NULL" is an extension to the SQL standard. Although supported by many/some database systems, the portable way is to use a CAST as in: "CAST(? AS INTEGER) IS NULL". If I understand correctly, this isn't supported by JPQL and must be dealt with elsewhere (i.e. rewriting by the Derby Hibernate dialect or by an extension to the Derby SQL [2]).

I'm pretty unfamiliar with HQL, JPQL and Hibernate, so feel free to help me understand the issue at hand better if I've gotten something wrong!


[1] https://issues.apache.org/jira/browse/DERBY-5629
[2] Standards compliance is on Derby's charter: http://db.apache.org/derby/derby_charter.html#Standards+based

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