openjpa-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Catalina Wei (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 Sat, 03 Jul 2010 00:27:49 GMT

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


Mime
View raw message