OpenJPA generates wrong SQL if a result variable that references an aggregate
expression is used in ORDER BY clause
-------------------------------------------------------------------------------------------------------------------
Key: OPENJPA-1715
URL: https://issues.apache.org/jira/browse/OPENJPA-1715
Project: OpenJPA
Issue Type: Bug
Components: jpa
Affects Versions: 2.0.0
Environment: Tomcat 6 + Oracle 9i
Reporter: Azuo Lee
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?
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
