11:45 a.m.
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....
-
For more information on JIRA, see: http://www.atlassian.com/software/jira