[
https://issues.apache.org/jira/browse/OPENJPA-1715?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12886298#action_12886298
]
Azuo Lee commented on OPENJPA-1715:
-----------------------------------
I'm currently using openjpa-2.1.0-SNAPSHOT.jar, and the problem is still there.
I think the problem can not be reproduced by the test case because it does not
involve any JOINs.
After my test, the problem occurs only if all of the following conditions are
met:
1. Entity A has a collection property (e.g., "bs") which references entity B (A
and B has one-to-many association);
2. the from clause is based on A, and left joins to B (e.g., "from A as a left
join a.bs as b");
3. there exists a result variable which references an aggregate of some
property of B (e.g., "select avg(b.age) as bage");
4. group by A and order by the result variable (e.g., "group by a.id order by
bage");
5. setFirstResult() and setMaxResults() are used;
6. the underlining database is Oracle.
> 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
> Assignee: Catalina Wei
>
> 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.
