[
https://issues.apache.org/jira/browse/OPENJPA-1715?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12884871#action_12884871
]
Catalina Wei commented on OPENJPA-1715:
---------------------------------------
Azuo,
(1). This problem seems already fixed, because I was not able to recreate the
problem using openjpa branch 2.0.x nor trunk level code.
Could you extract code from svn and build openjpa-2.0.1-SNAPSHOT.jar and give
it a try ?
Here is a JPQL string which is similar to your JPQL which worked under 2.0.x
and trunk level code.
3875 Test TRACE [main] openjpa.Query - Executing query: SELECT c.name
as name, SUM(c.age) as sage FROM CompUser c group by c.name order by sage
desc, name
3890 Test TRACE [main] openjpa.jdbc.SQL - <t 13079028, conn 22694519>
executing prepstmnt 15922565 SELECT t0.name, SUM(t0.age) AS sage FROM CompUser
t0 GROUP BY t0.name ORDER BY sage DESC, t0.name ASC
(2). Order by "NULLS LAST" is not supported in OpenJPA.
Your workaround is to use DESC or ASC depending on NULL is ordered high or not
to get the right order as you intended to. Some database backends consider NULL
is higher than non-null values; Oracle is one of them.
Catalina Wei
> 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.
