Uploaded image for project: 'OpenJPA'
  1. OpenJPA
  2. OPENJPA-2633

Case Expression in GROUP BY clause does not support dynamic bind parameters

    XMLWordPrintableJSON

    Details

    • Type: Bug
    • Status: Open
    • Priority: Major
    • Resolution: Unresolved
    • Affects Version/s: 2.1.0
    • Fix Version/s: None
    • Component/s: jpa
    • Labels:
      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.

        Attachments

          Activity

            People

            • Assignee:
              Unassigned
              Reporter:
              vinodkumarbolla vinod kumar bolla
            • Votes:
              0 Vote for this issue
              Watchers:
              1 Start watching this issue

              Dates

              • Created:
                Updated: