openjpa-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Azuo Lee (JIRA)" <j...@apache.org>
Subject [jira] Commented: (OPENJPA-1715) OpenJPA generates wrong SQL if a result variable that references an aggregate expression is used in ORDER BY clause
Date Thu, 08 Jul 2010 09:51:49 GMT

    [ 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.


Mime
View raw message