[hibernate-issues] [Hibernate-JIRA] Commented: (HHH-2722) incorrect sql on custom types when using dynamic update, optimistic-lock="dirty", select-before-update when null value in where clause

[Alex McCarrier (JIRA)]

    [ http://opensource.atlassian.com/projects/hibernate/browse/HHH-2722?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_27473 ] 

Alex McCarrier commented on HHH-2722:
-------------------------------------

Okay, more digging. :)  I upgraded to hibernate 3.2.4 sp1 and got the source and traced into it a little more, here's what appears to be happening.

AbstractEntityPersister.generateSQLDeleteStrings(Object[] loadedState) is depending on the loaded state of an object to determine wheter to use "= ?" or "is null" ...

The loaded state of an object, as loaded by AbstractEntityPersister.hydrate will use the CustomType.nullSafeGet to load the state of the object.  So, for a value that matches a null field in the database, we will have a loaded state with a non-null value, as nullSafeGet is intended to do.  

AbstractEntityPersistert.generateSQLDeleteStrings(..) then sees that loaded state as a non null value via CustomType.toColumnNullness and thinks, "oh, I should use = ?" and does so, even if the value matches our substitute value for null database fields.  

What I think should happen is CustomType.toColumnNullness should not only check if the value is null, but should check if the value matches the substitute value for null as defined in the customType.  This might involve changing the interface to UserType a bit.  I'll see if I can write a patch before you guys get to it, and will submit if I do.

> incorrect sql on custom types when using dynamic update, optimistic-lock="dirty", select-before-update when null value in where clause
> --------------------------------------------------------------------------------------------------------------------------------------
>
>                 Key: HHH-2722
>                 URL: http://opensource.atlassian.com/projects/hibernate/browse/HHH-2722
>             Project: Hibernate3
>          Issue Type: Bug
>          Components: core
>    Affects Versions: 3.2.2
>         Environment: hibernate 3.2.2, oracle 10g
>            Reporter: Alex McCarrier
>            Priority: Minor
>
> We are using optimistic-lock="dirty", dynamic-update="true" and select-before-update="true" on some of our classes.  This seems to be isolated to cases where we are using custom types that implement the UserType interface.
> The SQL that is generated by hibernate for the PreparedStatement looks something like this:
> delete from TABLE_NAME where PK = :1 and SOME_NUMBER = :2
> The problem happens when the value for SOME_NUMBER is null.  It still tries to use the above sql, rather than using the correct:
> delete from TABLE_NAME where PK = :1 and SOME_NUMBER is null.
> In this case, SOME_NUMBER is defined in Oracle as a FLOAT, and in the mapped object as double.  Interestingly, another one of our custom types which is a Binary type (Hibernate.BINARY.sqlType) has no problems.

-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: http://opensource.atlassian.com/projects/hibernate/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira