[hibernate-issues] [Hibernate-JIRA] Commented: (EJB-376) EntityManager.createQuery does not recognize COUNT CASE statement
Mark Plutowski (JIRA)
noreply at atlassian.com
Fri Aug 15 12:56:07 EDT 2008
[ http://opensource.atlassian.com/projects/hibernate/browse/EJB-376?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_30896 ]
Mark Plutowski commented on EJB-376:
------------------------------------
Related to this issue, I noticed another anomalous behavior when attempting to perform the same task using createNativeQuery instead. One of the columns was overwritten with the data for a different column.
// This query overwrites column 4 (array element [3]) with column 5 (array element 4)
StringBuffer sqlQuery = new StringBuffer ();
sqlQuery.append(" Select c.pub_Id, c.clip_Id, CAST (c.duration AS INT), CAST ( count(distinct cs.slot_Id) AS INT ) , ")
.append(" CAST (COUNT (DISTINCT (CASE WHEN (comp.serve_ts BETWEEN :beginTs AND :endTs ) THEN comp.component_id ELSE NULL END ) ) AS INT ) " )
.append(" FROM Cm_Clip c INNER JOIN cm_Clip_Slot cs ON (cs.clip_id = c.clip_id) left outer join cs_component comp ON (cs.clip_id = comp.clip_id) ")
.append(" where (cs.ad_Size_X * c.width) >= :adWidth and (cs.ad_Size_Y * c.height) >= :adHeight ")
.append(" and (c.is_Qa_Done = true or c.is_Qa_Done is null) and c.is_Enabled = true ")
.append(" group by c.pub_Id, c.clip_Id, c.duration ")
.append(" order by ")
.append( orderByCol );
Here's the transaction and parameter setting step:
transactionMgr.begin();
List<Object[]> result = em.createNativeQuery(sqlQuery.toString())
.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();
We confirmed that every time this native query was run, result[3] == result[4] despite the data in the database being such that this should not be the case. Reverting back to the "SUM ( CASE WHEN " JQL (HQL?) approach mentioned above fixed this, such that result[3] and result[4] were no longer showing identical data and were indeed returning the correct result when compared to the same statement translated to SQL and run against the target database using Pgadmin (as well as confirmed via visual inspection of the underlying tables).
> 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
More information about the hibernate-issues
mailing list