[hibernate-issues] [Hibernate-JIRA] Commented: (HHH-3320) JPA Max/Min aggregate functions do not return same state-field type when argument is a java.sql.Timestamp

[Gene Smith (JIRA)]

    [ http://opensource.atlassian.com/projects/hibernate/browse/HHH-3320?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=32409#action_32409 ] 

Gene Smith commented on HHH-3320:
---------------------------------

I agree with Josef...  this was annoying to find and work around.  And I dread the thought that certain other people in my work group might have been the ones trying to find and avoid it.

Here is a more nasty case, and my work around...

There was already a query with a sub-select, but the table is really big, and joins were added to the query, so it would bog down the server.  And I needed alternative results where there were and were not parent records.  And I needed to sort.  And to join another table...  And grouping by virtual columns...  And sorting by virtual columns...

Note: The max date is selected only to make it valid SQL, as it is being sorted by.

I wont claim that the "moderation" table is simple, but when you build structures on top of structures... you don't get simple.  And this query does run fast:)  And is relatively easy to understand.

Names changed to protect the guilty;) 
    SELECT
            distinct coalesce(moderation.parentId, moderation.thisId),
            MAX(CAST (moderation.date as bigint))
        FROM
            moderation
            [and join(s)]
        WHERE
            moderation.type = ? and
            moderation.status = ?
            [and joined criteria]
        GROUP BY
            coalesce(moderation.parentId, moderation.thisId)
        ORDER BY
            max(CAST (moderation.date as bigint)) desc

> JPA Max/Min aggregate functions do not return same state-field type when argument is a java.sql.Timestamp
> ---------------------------------------------------------------------------------------------------------
>
>                 Key: HHH-3320
>                 URL: http://opensource.atlassian.com/projects/hibernate/browse/HHH-3320
>             Project: Hibernate Core
>          Issue Type: Bug
>          Components: core
>    Affects Versions: 3.3.0.CR1
>         Environment: JDK 1.6, Postgres 8.2, JDBC 4
>            Reporter: josef
>         Attachments: HibernateUnitTest.zip
>
>
> According the JPQL specification, MAX and MIN aggregate functions should return the type of the state-field to which they are applied. However, if one tries to query MAX or MIN on a java.sql.Timestamp entity field and tries to populate a class which expects a java.sql.Timestamp in the constructor with the result of MAX or MIN, Hibernate fails with javax.persistence.PersistenceException. The result of MAX or MIN in this case are being java.util.Date instead.
> The error result in type mismatch should ideally also be more helpful, because it is very difficult to understand what and where the problem is, unless you know about it beforehand.
> Here is an example Entity, with the aggregate class and query, together with the exception that occurs:
> ... 
> import java.sql.Timestamp; 
> @Entity 
> @Table(name="pending_events") 
> public class PendingEvent implements Serializable 
> { 
> ... 
>     @Column(name="event_time", updatable=false, nullable=false) 
>     private Timestamp eventTime; 
> ... 
> }
> import java.sql.Timestamp; 
> public class EventEntry 
> { 
>     private String sender; 
>     private Timestamp lastEventTime; 
>     public EventEntry(String sender, Timestamp lastEventTime) 
>     { 
>         this.sender= sender; 
>         this.lastEventTime = lastEventTime; 
>     } 
> ... 
> }
> SELECT NEW mt.com.jbx.EventEntry(p.sender, MAX(p.eventTime)) FROM PendingEvent p WHERE p.recipient.id=? GROUP BY p.sender ORDER BY MAX(p.eventTime) DESC
> java.lang.ExceptionInInitializerError 
> Caused by: javax.persistence.PersistenceException: [PersistenceUnit: EventsPU] Unable to build EntityManagerFactory 
>         at org.hibernate.ejb.Ejb3Configuration.buildEntityManagerFactory(Ejb3Configuration.java:677) 
>         at org.hibernate.ejb.HibernatePersistence.createEntityManagerFactory(HibernatePersistence.java:126) 
>         at javax.persistence.Persistence.createEntityManagerFactory(Persistence.java:52) 
>         at javax.persistence.Persistence.createEntityManagerFactory(Persistence.java:34) 
>         at mt.com.jbx.EventHandler.<clinit>(EventHandler.java:33) 
> Caused by: org.hibernate.HibernateException: Errors in named queries: getEventSummary 
>         at org.hibernate.impl.SessionFactoryImpl.<init>(SessionFactoryImpl.java:365) 
>         at org.hibernate.cfg.Configuration.buildSessionFactory(Configuration.java:1300) 
>         at org.hibernate.cfg.AnnotationConfiguration.buildSessionFactory(AnnotationConfiguration.java:859) 
>         at org.hibernate.ejb.Ejb3Configuration.buildEntityManagerFactory(Ejb3Configuration.java:669)

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