Details
-
Bug
-
Status: Open
-
Major
-
Resolution: Unresolved
-
2.1.0
-
None
-
None
Description
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.