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

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