OpenJPA
  1. OpenJPA
  2. OPENJPA-1819

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

    Details

    • Type: Bug Bug
    • Status: Resolved
    • Priority: Critical Critical
    • Resolution: Fixed
    • Affects Version/s: 2.0.1, 2.0.2
    • Fix Version/s: 2.1.0, 2.3.0
    • Component/s: sql
    • Labels:
      None
    • Environment:
      OpenJPA 2.0
      WebSphere 6.1
      Oracle 9.2

      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

        Activity

        Azuo Lee created issue -
        Azuo Lee made changes -
        Field Original Value New Value
        Affects Version/s 2.1.0 [ 12314542 ]
        Priority Major [ 3 ] Critical [ 2 ]
        Component/s sql [ 12311310 ]
        Hide
        Fay Wang added a comment -

        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

        Show
        Fay Wang added a comment - 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
        Fay Wang made changes -
        Attachment OPENJPA-1819.patch [ 12456026 ]
        Hide
        Azuo Lee added a comment -

        Fixed in 2.1.0-20101001.064809-70.

        Show
        Azuo Lee added a comment - Fixed in 2.1.0-20101001.064809-70.
        Azuo Lee made changes -
        Status Open [ 1 ] Resolved [ 5 ]
        Fix Version/s 2.1.0 [ 12314542 ]
        Resolution Fixed [ 1 ]
        Hide
        Michael Dick added a comment -

        Closing issue which has been resolved for some time. If you believe the issue is not resolved please reopen or open a new issue.

        Show
        Michael Dick added a comment - Closing issue which has been resolved for some time. If you believe the issue is not resolved please reopen or open a new issue.
        Michael Dick made changes -
        Status Resolved [ 5 ] Closed [ 6 ]
        Hide
        Azuo Lee added a comment -

        I'm afraid the issure is not fixed in 2.0.x versions, but i have to use OpenJPA with websphere 6.1 which does not support 2.1.x versions.

        Show
        Azuo Lee added a comment - I'm afraid the issure is not fixed in 2.0.x versions, but i have to use OpenJPA with websphere 6.1 which does not support 2.1.x versions.
        Azuo Lee made changes -
        Resolution Fixed [ 1 ]
        Status Closed [ 6 ] Reopened [ 4 ]
        Azuo Lee made changes -
        Affects Version/s 2.0.1 [ 12314532 ]
        Affects Version/s 2.0.2 [ 12315257 ]
        Affects Version/s 2.1.0 [ 12314542 ]
        Environment OpenJPA 2.1
        Oracle 9.2
        OpenJPA 2.0
        WebSphere 6.1
        Oracle 9.2
        Hide
        Azuo Lee added a comment -

        Could any ASF user please copy the code that fixes this issure from trunk to branch 2.0.x ?

        Show
        Azuo Lee added a comment - Could any ASF user please copy the code that fixes this issure from trunk to branch 2.0.x ?
        Hide
        Mark Struberg added a comment -

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

        Show
        Mark Struberg added a comment - 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).
        Mark Struberg made changes -
        Status Reopened [ 4 ] Resolved [ 5 ]
        Fix Version/s 2.3.0 [ 12319463 ]
        Resolution Fixed [ 1 ]

          People

          • Assignee:
            Unassigned
            Reporter:
            Azuo Lee
          • Votes:
            0 Vote for this issue
            Watchers:
            2 Start watching this issue

            Dates

            • Created:
              Updated:
              Resolved:

              Development