[jira] [Created] (OPENJPA-2633) Case Expression in GROUP BY clause does not support dynamic bind parameters

["vinod kumar bolla (JIRA)" <ji...@apache.org>]

vinod kumar bolla created OPENJPA-2633:
------------------------------------------

             Summary: Case Expression in GROUP BY clause does not support dynamic bind parameters
                 Key: OPENJPA-2633
                 URL: https://issues.apache.org/jira/browse/OPENJPA-2633
             Project: OpenJPA
          Issue Type: Bug
          Components: jpa
    Affects Versions: 2.1.0
            Reporter: vinod kumar bolla


We are using criteria builder to frame the query, Select clause having CASE expression and same was placed in GROUP BY Clause as well.

Example Query:

SELECT  SUM(
  CASE
    WHEN t1.IND = 1
    THEN t0.HRS
    ELSE 0
  END ),
  CASE
    WHEN t0.TRANS_DAY_KY >= 20150322
    AND t0.TRANS_DAY_KY  <= 20150328
    THEN 'Wk - 13'
    WHEN t0.TRANS_DAY_KY >= 20150329
    AND t0.TRANS_DAY_KY  <= 20150404
    THEN 'Wk - 14'
    ELSE NULL
  END AS Period
  
  FROM TIME to,NEWTIME t1
  WHERE (t0.TRANS_DAY_KY                   >= 20150322
  AND t0.TRANS_DAY_KY                      <= 20150404)
  GROUP BY
  CASE
    WHEN t0.TRANS_DAY_KY >= 20150322
    AND t0.TRANS_DAY_KY  <= 20150328
    THEN 'Wk - 13'
    WHEN t0.TRANS_DAY_KY >= 20150329
    AND t0.TRANS_DAY_KY  <= 20150404
	THEN 'Wk - 14'
  ELSE NULL
  END 

  
We are using OpenJpa to implement this Query. OpenJPA Implementation for CASE expression is like below.
  
Case<String> selectCaseForSelections = cb.<String>selectCase();
selectCaseForSelections.when(cb.and(cb.greaterThanOrEqualTo(dayKey, cb.literal(startTime)), cb.lessThanOrEqualTo(dayKey, cb.literal(endTime))), interval.getName());
selections.add(selectCaseForSelections.alias("Period");  // Adding the CASE Expression to SELECT Clause
groupByCols.add(selectCaseForSelections);  // Adding the CASE Expression to GROUP BY Clause


Console TRACE Log shows below Query and Exception.

exception is:nested exception is: javax.ejb.EJBException: See nested exception; nested exception is: <openjpa-2.2.3-SNAPSHOT-r422266:1666312M fatal general error> org.apache.openjpa.persistence.PersistenceException: Missing IN or OUT parameter at index:: 120
SELECT  SUM( CASE WHEN t1.IND = ? THEN 
        t0.HRS ELSE 0 END ), 
        CASE WHEN t0.TRANS_DAY_KY >= ? AND t0.TRANS_DAY_KY <= ? THEN 
		'Wk - 13' WHEN t0.TRANS_DAY_KY >= ? AND t0.TRANS_DAY_KY <= ? 
        THEN 'Wk - 14' ELSE NULL END AS Period 
		FROM TIME to,NEWTIME t1
		WHERE (t0.TRANS_DAY_KY                   >= ?
        AND t0.TRANS_DAY_KY                      <= ?)
		GROUP BY
		CASE WHEN t0.TRANS_DAY_KY >= ? AND t0.TRANS_DAY_KY <= ? THEN 
		'Wk - 13' WHEN t0.TRANS_DAY_KY >= ? AND t0.TRANS_DAY_KY <= ? 
        THEN 'Wk - 14' ELSE NULL END
[params=(int) 1,(long) 20150322, (long) 20150328, (long) 20150329, (long) 20150404,(long) 20150322, (long) 20150404]
[err] 	at org.apache.openjpa.lib.jdbc.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:268)

The Problem i could see is that Bind parameters are not passed to the GROUP BY Clause only for SELECT clause they were passed.
Is it the OpenJPA Limitation ? Could someone help me out.

Thanks in advance.




--
This message was sent by Atlassian JIRA
(v6.3.4#6332)