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] Created: (OPENJPA-1819) ORDER BY will append additional column to the SELECT clause which may potentialy cause ORA-00979 error
Date Thu, 30 Sep 2010 09:58:33 GMT
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.


Mime
View raw message