[
https://issues.apache.org/jira/browse/OPENJPA-1819?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13055350#comment-13055350
]
Azuo Lee commented on OPENJPA-1819:
-----------------------------------
Could any ASF user please copy the code that fixes this issure from trunk to
branch 2.0.x ?
> ORDER BY will append additional column to the SELECT clause which may
> potentialy cause ORA-00979 error
> ------------------------------------------------------------------------------------------------------
>
> Key: OPENJPA-1819
> URL: https://issues.apache.org/jira/browse/OPENJPA-1819
> Project: OpenJPA
> Issue Type: Bug
> Components: sql
> Affects Versions: 2.0.1, 2.0.2
> Environment: OpenJPA 2.0
> WebSphere 6.1
> Oracle 9.2
> Reporter: Azuo Lee
> Priority: Critical
> Fix For: 2.1.0
>
> Attachments: OPENJPA-1819.patch
>
>
> Assuming entity Person and entity Exam has one-to-many association, the
> following JPQL statement:
> SELECT p.id r1, p.name r2, sum(e.score) r3 FROM Exam e LEFT JOIN e.person p
> GROUP BY p.id, p.name ORDER BY p.id
> will produce the following SQL, which is not executable if the underlining
> database is Oracle 9.2:
> SELECT t1.id, t1.name, SUM(t0.score) AS r3, t0.person FROM exams t0, persons
> t1 WHERE t0.person = t1.id(+) GROUP BY t1.id, t1.name ORDER BY t0.person ASC
> The additional column "t0.person" will cause error ORA-00979: not a GROUP BY
> expression.
> The correct SQL should be:
> SELECT t1.id, t1.name, SUM(t0.score) AS r3 FROM exams t0, persons t1 WHERE
> t0.person = t1.id(+) GROUP BY t1.id, t1.name ORDER BY t1.id ASC
--
This message is automatically generated by JIRA.
For more information on JIRA, see: http://www.atlassian.com/software/jira
