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] [Updated] (OPENJPA-1715) OpenJPA generates wrong SQL if a result variable that references an aggregate expression is used in ORDER BY clause
Date Mon, 13 Jun 2011 16:45:51 GMT

     [ https://issues.apache.org/jira/browse/OPENJPA-1715?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]

Azuo Lee updated OPENJPA-1715:
------------------------------

          Component/s:     (was: jpa)
                       sql
        Fix Version/s:     (was: 2.0.1)
          Environment: 
OpenJAP 2.0
WebSphere 6.1
Oracle 9i

  was:Tomcat 6 + Oracle 9i

    Affects Version/s: 2.0.2
                       2.0.1

> 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: sql
>    Affects Versions: 2.0.0, 2.0.1, 2.0.2
>         Environment: OpenJAP 2.0
> WebSphere 6.1
> Oracle 9i
>            Reporter: Azuo Lee
>            Assignee: Catalina Wei
>             Fix For: 2.1.0
>
>
> 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.
For more information on JIRA, see: http://www.atlassian.com/software/jira

        

Mime
View raw message