[OPENJPA-1819] ORDER BY will append additional column to the SELECT clause which may potentialy cause ORA-00979 error

• Type: Bug
• Status: Closed
• Priority: Critical
• Resolution: Fixed
• Affects Version/s: 2.0.1, 2.0.2
• Fix Version/s: 2.1.0, 2.3.0
• Component/s: sql
• Environment: OpenJPA 2.0; WebSphere 6.1; Oracle 9.2
• Assignee: Unassigned
• Reporter: Azuo Lee
• Created: 30/Sep/10 09:57
• Updated: 06/Jun/15 21:19
• Resolved: 18/Oct/13 15:08

Description

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