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
Environment: OpenJPA 2.1
Oracle 9.2
Reporter: Azuo Lee
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.
-
You can reply to this email to add a comment to the issue online.
