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

[Timothy Braje (JIRA)]

    [ http://opensource.atlassian.com/projects/hibernate/browse/EJB-376?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=33128#action_33128 ] 

Timothy Braje commented on EJB-376:
-----------------------------------

Is the above test case good enough?  I just ran into this same problem, and am willing to help get you what you need in order to get this bug scheduled.

> 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.3.1.GA, 3.4.0.CR2
>         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