Uploaded image for project: '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


    • Type: Bug
    • Status: Reopened
    • Priority: 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:
    • Environment:
      OpenJAP 2.0
      WebSphere 6.1
      Oracle 9i


      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?




            • Assignee:
              fancy Catalina Wei
              azuo_lee Azuo Lee
            • Votes:
              0 Vote for this issue
              0 Start watching this issue


              • Created: