openjpa-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Mark Struberg (JIRA)" <j...@apache.org>
Subject [jira] [Resolved] (OPENJPA-1819) ORDER BY will append additional column to the SELECT clause which may potentialy cause ORA-00979 error
Date Fri, 18 Oct 2013 15:08:44 GMT

     [ 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)

Mime
View raw message