OpenJPA
  1. OpenJPA
  2. OPENJPA-1715

OpenJPA generates wrong SQL if a result variable that references an aggregate expression is used in ORDER BY clause

    Details

    • Type: Bug Bug
    • Status: Reopened
    • Priority: Major Major
    • Resolution: Unresolved
    • Affects Version/s: 2.0.0, 2.0.1, 2.0.2
    • Fix Version/s: 2.1.0
    • Component/s: sql
    • Labels:
      None
    • Environment:
      OpenJAP 2.0
      WebSphere 6.1
      Oracle 9i

      Description

      For the following JPQL (According to JPA sepcification v2.0, section 4.9, it is legal to use result variables in the order by clause):

      select v.id r0, sum(_v0.score) r1 from Stall _v left join v.scores v0 where v.deleted = :p0 and v.market = :p1 group by v.id order by _r1 desc, _r0

      but OpenJPA generates a wrong SQL as following:

      SELECT t0.id AS c0, SUM(t1.score) AS _r1 AS c1 FROM stalls t0, scores t1 WHERE (t0.deleted = ? AND t0.market = ? AND 1 = 1) AND t0.id = t1.stall GROUP BY t0.id ORDER BY _r1 DESC, t0.id ASC

      The second result item in the select clause has 2 aliases specified: "SUM(t1.score) AS _r1 AS c1", which is obviously not acceptable by the underlining database.

      Additional question:
      How can i order NULL values, like the behavior achieved by using Oracle "ORDER BY SUM(t1.score) DESC NULLS LAST", by using JPQL?

        Activity

        No work has yet been logged on this issue.

          People

          • Assignee:
            Catalina Wei
            Reporter:
            Azuo Lee
          • Votes:
            0 Vote for this issue
            Watchers:
            0 Start watching this issue

            Dates

            • Created:
              Updated:

              Development