[
https://issues.apache.org/jira/browse/OPENJPA-1715?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12885906#action_12885906
]
Azuo Lee commented on OPENJPA-1715:
-----------------------------------
Thanks for your kind reply and sorry for my incomplete description. The
complete code is as following:
String jpql = "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";
Query q = entityManager.createQuery(jpql);
q.setParameter("_p0_", Boolean.FALSE);
q.setParameter("_p1_", id);
q.setFirstResult(startRow + 1);
q.setMaxResults(maxRows);
List results = q.getResultList();
There will be no problem if I comment out "q.setFirstResult" and
"q.setMaxResults" calls, but if not, the problem occurs, and OpenJPA generates
the following wrong SQL:
SELECT * FROM (SELECT r.*, ROWNUM RNUM FROM (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)
r WHERE ROWNUM <= ?) WHERE RNUM > ?
> 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.
