[hibernate-issues] [Hibernate-JIRA] Updated: (HHH-4150) EntityManager.createQuery does not recognize COUNT CASE statement

[ck (JIRA)]

     [ http://opensource.atlassian.com/projects/hibernate/browse/HHH-4150?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

ck updated HHH-4150:
--------------------

    Attachment: HHH-4150.patch

Here is a basic patch plus some unit tests for this issue.  There are two unit tests- one for the sum(case when...) and one for the count(distinct case when ...).

I also included a very basic patch to hql.g that makes the 2nd unit test work.  However, antlr prints out some warnings upon compilation.  I would guess my patch won't quite cut it for this bug, although the other tests do pass with it in place.  I don't know much about antlr so I really can't say too much more.  Here are the warnings:

[INFO] Using Antlr grammar: core/src/main/antlr/hql.g
ANTLR Parser Generator   Version 2.7.6 (2005-12-22)   1989-2005
warning: invalid command-line argument: -traceTreeParser; ignored
core/src/main/antlr/hql.g:656:65: warning:nondeterminism between alts 2 and 3 of block upon
core/src/main/antlr/hql.g:656:65:     k==1:"all"
core/src/main/antlr/hql.g:656:65:     k==2:"all","any","avg","count","elements","exists","false","indices","max","min","null","some","sum","true","case","empty","key","value","entry",NUM_DOUBLE,NUM_FLOAT,NUM_LONG,OPEN,PLUS,MINUS,COLON,PARAM,NUM_INT,QUOTED_STRING,IDENT
core/src/main/antlr/hql.g:656:65:     k==3:"all","any","avg","count",DOT,"elements","exists","false","from","group","indices","max","min","not","null","order","select","some","sum","true","union","where","case","when","empty","key","value","entry",NUM_DOUBLE,NUM_FLOAT,NUM_LONG,OPEN,CLOSE,PLUS,MINUS,STAR,DIV,OPEN_BRACKET,COLON,PARAM,NUM_INT,QUOTED_STRING,IDENT


> EntityManager.createQuery does not recognize COUNT CASE statement
> -----------------------------------------------------------------
>
>                 Key: HHH-4150
>                 URL: http://opensource.atlassian.com/projects/hibernate/browse/HHH-4150
>             Project: Hibernate Core
>          Issue Type: Bug
>          Components: entity-manager
>         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
>         Attachments: HHH-4150.patch
>
>
> 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