[hibernate-issues] [Hibernate-JIRA] Created: (EJB-376) EntityManager.createQuery does not recognize COUNT CASE statement

[Mark Plutowski (JIRA)]

EntityManager.createQuery does not recognize COUNT CASE statement
-----------------------------------------------------------------

                 Key: EJB-376
                 URL: http://opensource.atlassian.com/projects/hibernate/browse/EJB-376
             Project: Hibernate Entity Manager
          Issue Type: Bug
          Components: EntityManager
    Affects Versions: 3.4.0.CR2, 3.3.1.GA
         Environment: Tested with both Hibernate 3.2.5.ga and Hibernate 3.3.0.CR2, Postgres 8.1, Postgres 8.2, Linux Ubuntu. 
            Reporter: Mark Plutowski


I searched the Hibernate forums and did not find mention of this issue or one that was closely related. I also posted this to the Hibernate Users forum, and did not receive any replies recognizing the bug or stating that it has been resolved or has a workaround. 

To summarize, EntityManager.createQuery does not recognize SQL related to applying a COUNT to a CASE, whereas it does correctly run SQL that applies a SUM to a CASE.  More detail follows. 

--- Summary Overview ---  

EntityManager.createQuery runs a SELECT query containing the following HQL/JQL snippet runs correctly: 

" SUM ( CASE WHEN (cs.serveTs BETWEEN :beginTs AND :endTs ) THEN 1 ELSE 0 END )  "

However, the following do not work: 

" COUNT ( DISTINCT   CASE WHEN (cs.serveTs BETWEEN :beginTs AND :endTs ) THEN cs.componentId ELSE NULL END )  "

" COUNT ( DISTINCT   (CASE WHEN (cs.serveTs BETWEEN :beginTs AND :endTs ) THEN cs.componentId ELSE NULL END ) )  "   

" COUNT ( CASE WHEN (cs.serveTs BETWEEN :beginTs AND :endTs ) THEN cs.componentId ELSE NULL END )  "

The first two statements run and return correct results when translated to SQL and run against the Postgresql database in PgAdmin.  

Here's the exception thrown when using COUNT ( DISTINCT CASE WHEN

101976 [main] ERROR org.hibernate.hql.PARSER - line 1:93: unexpected token: CASE
101977 [main] ERROR org.hibernate.hql.PARSER - line 1:98: unexpected token: WHEN

Here's the exception thrown when the extra parenthesis is added to give COUNT ( DISTINCT (CASE WHEN

85587 [main] ERROR org.hibernate.hql.PARSER - line 1:93: unexpected token: (
85588 [main] ERROR org.hibernate.hql.PARSER - line 1:99: unexpected token: WHEN


--- Additional Detail --- 

Here is the complete SQL for the COUNT (DISTINCT (CASE  use case: 

        String queryString = "Select c.id.pubId, c.id.clipId, c.duration,  " +
                "count(distinct s.slotId) , " +  
              " COUNT ( DISTINCT   (CASE WHEN (cs.serveTs BETWEEN :beginTs AND :endTs ) THEN cs.componentId ELSE NULL END ) )  " +    
                "from  CmClip c " +
                "join c.cmClipSlots s " +
                "left outer join c.csComponents cs " +
                "where (s.adSizeX * c.width) >= :adWidth " +
                "and   (s.adSizeY * c.height)  >= :adHeight " +
                "and   (c.isQaDone = true or c.isQaDone is null) " +
                "and   c.isEnabled = true " + 
                "group by c.id.pubId, c.id.clipId, c.duration " +
                "order by " + orderByCol;

This results in the following exception:

85587 [main] ERROR org.hibernate.hql.PARSER - line 1:93: unexpected token: (
85588 [main] ERROR org.hibernate.hql.PARSER - line 1:99: unexpected token: WHEN


Here is the SQL for the SUM CASE sql snippet that does run and return correct results: 

        String queryString = "Select c.id.pubId, c.id.clipId, c.duration,  " +
                "count(distinct s.slotId) , " +  
              " SUM ( CASE WHEN (cs.serveTs BETWEEN :beginTs AND :endTs ) THEN 1 ELSE 0 END )  " +                          
                 "from  CmClip c " +
                "join c.cmClipSlots s " +
                "left outer join c.csComponents cs " +
                "where (s.adSizeX * c.width) >= :adWidth " +
                "and   (s.adSizeY * c.height)  >= :adHeight " +
                "and   (c.isQaDone = true or c.isQaDone is null) " +
                "and   c.isEnabled = true " + 
                "group by c.id.pubId, c.id.clipId, c.duration " +
                "order by " + orderByCol;

Here is the code snippet that performs the transaction: 

        float roFactor = (float) 0.5;   
        transactionMgr.begin(); 
        List<Object[]> result = entityMgr.createQuery(queryString)   
        		.setParameter("adWidth", (widthF-roFactor)).
                setParameter("adHeight", (heightF-roFactor))
				.setParameter("beginTs", beginTs, javax.persistence.TemporalType.TIMESTAMP)		
				.setParameter("endTs", endTs, javax.persistence.TemporalType.TIMESTAMP)			
				.getResultList();
        transactionMgr.commit(); 

The fact that "SUM (CASE"  is parsed and run correctly, whereas "COUNT (CASE"  throws an exception seems to indicate a bug.    I replicated this using both (Hibernate 3.2.5.ga with EntityManager 3.3.1.GA) and (Hibernate 3.3.0.CR2 with EntityManager 3.4.0.CR2). 




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