You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@openjpa.apache.org by "Azuo Lee (JIRA)" <ji...@apache.org> on 2010/09/30 11:58:33 UTC
[jira] Created: (OPENJPA-1819) ORDER BY will append additional
column to the SELECT clause which may potentialy cause ORA-00979 error
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.
[jira] Updated: (OPENJPA-1819) ORDER BY will append additional
column to the SELECT clause which may potentialy cause ORA-00979 error
Posted by "Fay Wang (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/OPENJPA-1819?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Fay Wang updated OPENJPA-1819:
------------------------------
Attachment: OPENJPA-1819.patch
> 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.1.0
> Environment: OpenJPA 2.1
> Oracle 9.2
> Reporter: Azuo Lee
> Priority: Critical
> 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.
-
You can reply to this email to add a comment to the issue online.
[jira] Commented: (OPENJPA-1819) ORDER BY will append additional
column to the SELECT clause which may potentialy cause ORA-00979 error
Posted by "Fay Wang (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/OPENJPA-1819?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12916529#action_12916529 ]
Fay Wang commented on OPENJPA-1819:
-----------------------------------
This apparently is not specific to Oracle. When running against DB2, the generated SQL is:
SELECT t1.id, t1.name, SUM(t0.score) AS r3, t0.person
FROM Exam t0
LEFT OUTER JOIN Person t1 ON t0.PERSON_ID = t1.id
GROUP BY t1.id, t1.name ORDER BY t0.person ASC
and DB2 throws the following exception:
com.ibm.db2.jcc.am.ro: DB2 SQL Error: SQLCODE=-119, SQLSTATE=42803, SQLERRMC=person, DRIVER=3.58.82
> 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.1.0
> Environment: OpenJPA 2.1
> Oracle 9.2
> Reporter: Azuo Lee
> Priority: Critical
>
> 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.
[jira] Updated: (OPENJPA-1819) ORDER BY will append additional
column to the SELECT clause which may potentialy cause ORA-00979 error
Posted by "Azuo Lee (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/OPENJPA-1819?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Azuo Lee updated OPENJPA-1819:
------------------------------
Affects Version/s: 2.1.0
Priority: Critical (was: Major)
Component/s: sql
> 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.1.0
> Environment: OpenJPA 2.1
> Oracle 9.2
> Reporter: Azuo Lee
> Priority: Critical
>
> 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.
[jira] Resolved: (OPENJPA-1819) ORDER BY will append additional
column to the SELECT clause which may potentialy cause ORA-00979 error
Posted by "Azuo Lee (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/OPENJPA-1819?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Azuo Lee resolved OPENJPA-1819.
-------------------------------
Resolution: Fixed
Fix Version/s: 2.1.0
Fixed in 2.1.0-20101001.064809-70.
> 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.1.0
> Environment: OpenJPA 2.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.
-
You can reply to this email to add a comment to the issue online.