[ https://issues.apache.org/jira/browse/OPENJPA-1819?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Mark Struberg resolved OPENJPA-1819. ------------------------------------ Resolution: Fixed Fix Version/s: 2.3.0 This has been fixed in trunk already since quite some time. Azuo, if you still have this problem in the old WebSphere version you reported it for, then I suggest creating a PMR for it and let IBM deal with it (with a pointer to this JIRA). > 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.3.0, 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 was sent by Atlassian JIRA (v6.1#6144)