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

        Azuo Lee created issue -
        Catalina Wei made changes -
        Field Original Value New Value
        Assignee Catalina Wei [ fancy ]
        Catalina Wei made changes -
        Status Open [ 1 ] In Progress [ 3 ]
        Catalina Wei made changes -
        Status In Progress [ 3 ] Open [ 1 ]
        Catalina Wei made changes -
        Status Open [ 1 ] In Progress [ 3 ]
        Catalina Wei made changes -
        Status In Progress [ 3 ] Open [ 1 ]
        Catalina Wei made changes -
        Status Open [ 1 ] Resolved [ 5 ]
        Fix Version/s 2.0.1 [ 12314532 ]
        Fix Version/s 2.1.0 [ 12314542 ]
        Resolution Fixed [ 1 ]
        Michael Dick made changes -
        Status Resolved [ 5 ] Closed [ 6 ]
        Azuo Lee made changes -
        Resolution Fixed [ 1 ]
        Status Closed [ 6 ] Reopened [ 4 ]
        Azuo Lee made changes -
        Environment Tomcat 6 + Oracle 9i OpenJAP 2.0
        WebSphere 6.1
        Oracle 9i
        Fix Version/s 2.0.1 [ 12314532 ]
        Affects Version/s 2.0.1 [ 12314532 ]
        Affects Version/s 2.0.2 [ 12315257 ]
        Component/s sql [ 12311310 ]
        Component/s jpa [ 12311304 ]

          People

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

            Dates

            • Created:
              Updated:

              Development