OpenJPA
  1. OpenJPA
  2. OPENJPA-1715

OpenJPA generates wrong SQL if a result variable that references an aggregate expression is used in ORDER BY clause

    Details

    • Type: Bug Bug
    • Status: Reopened
    • Priority: Major Major
    • Resolution: Unresolved
    • Affects Version/s: 2.0.0, 2.0.1, 2.0.2
    • Fix Version/s: 2.1.0
    • Component/s: sql
    • Labels:
      None
    • Environment:
      OpenJAP 2.0
      WebSphere 6.1
      Oracle 9i

      Description

      For the following JPQL (According to JPA sepcification v2.0, section 4.9, it is legal to use result variables in the order by clause):

      select v.id r0, sum(_v0.score) r1 from Stall _v left join v.scores v0 where v.deleted = :p0 and v.market = :p1 group by v.id order by _r1 desc, _r0

      but OpenJPA generates a wrong SQL as following:

      SELECT t0.id AS c0, SUM(t1.score) AS _r1 AS c1 FROM stalls t0, scores t1 WHERE (t0.deleted = ? AND t0.market = ? AND 1 = 1) AND t0.id = t1.stall GROUP BY t0.id ORDER BY _r1 DESC, t0.id ASC

      The second result item in the select clause has 2 aliases specified: "SUM(t1.score) AS _r1 AS c1", which is obviously not acceptable by the underlining database.

      Additional question:
      How can i order NULL values, like the behavior achieved by using Oracle "ORDER BY SUM(t1.score) DESC NULLS LAST", by using JPQL?

        Activity

        Hide
        Azuo Lee added a comment -

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

        Show
        Azuo Lee added a comment - Could any ASF user please copy the code related to this issure from trunk to branch 2.0.x ?
        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.
        Hide
        Michael Dick added a comment -

        Closing issues which have been resolved for some time. If the problem persists, please reopen.

        Show
        Michael Dick added a comment - Closing issues which have been resolved for some time. If the problem persists, please reopen.
        Hide
        Catalina Wei added a comment -

        Azuo,
        Thank you very much, I got the problem reproduced.
        Fix has checked in under trunk.

        Show
        Catalina Wei added a comment - Azuo, Thank you very much, I got the problem reproduced. Fix has checked in under trunk.
        Hide
        Azuo Lee added a comment -

        I'm currently using openjpa-2.1.0-SNAPSHOT.jar, and the problem is still there.

        I think the problem can not be reproduced by the test case because it does not involve any JOINs.

        After my test, the problem occurs only if all of the following conditions are met:
        1. Entity A has a collection property (e.g., "bs") which references entity B (A and B has one-to-many association);
        2. the from clause is based on A, and left joins to B (e.g., "from A as a left join a.bs as b");
        3. there exists a result variable which references an aggregate of some property of B (e.g., "select avg(b.age) as bage");
        4. group by A and order by the result variable (e.g., "group by a.id order by bage");
        5. setFirstResult() and setMaxResults() are used;
        6. the underlining database is Oracle.

        Show
        Azuo Lee added a comment - I'm currently using openjpa-2.1.0-SNAPSHOT.jar, and the problem is still there. I think the problem can not be reproduced by the test case because it does not involve any JOINs. After my test, the problem occurs only if all of the following conditions are met: 1. Entity A has a collection property (e.g., "bs") which references entity B (A and B has one-to-many association); 2. the from clause is based on A, and left joins to B (e.g., "from A as a left join a.bs as b"); 3. there exists a result variable which references an aggregate of some property of B (e.g., "select avg(b.age) as bage"); 4. group by A and order by the result variable (e.g., "group by a.id order by bage"); 5. setFirstResult() and setMaxResults() are used; 6. the underlining database is Oracle.
        Hide
        Catalina Wei added a comment -

        Azuo,
        This problem can not be reproduced with trunk level and 2.0.x branch code.
        I have included a test case 'testAggregateResultVariable() in TestJPQLScalarExpressions, and checked in.
        Could you pick up 2.0.1 or 2.1.0 jar from nightly snapshots and give it a try ?

        Catalina

        Show
        Catalina Wei added a comment - Azuo, This problem can not be reproduced with trunk level and 2.0.x branch code. I have included a test case 'testAggregateResultVariable() in TestJPQLScalarExpressions, and checked in. Could you pick up 2.0.1 or 2.1.0 jar from nightly snapshots and give it a try ? Catalina
        Hide
        Azuo Lee added a comment -

        Maybe the problem is caused by some code in method "org.apache.openjpa.jdbc.sql.OracleDictionary.getSelects(Select, boolean, boolean)", which introduces new aliases to the existing select clause, if the select uses joins and requires subselects to select a proper range.

        Hope this can be fixed.

        Show
        Azuo Lee added a comment - Maybe the problem is caused by some code in method "org.apache.openjpa.jdbc.sql.OracleDictionary.getSelects(Select, boolean, boolean)", which introduces new aliases to the existing select clause, if the select uses joins and requires subselects to select a proper range. Hope this can be fixed.
        Hide
        Azuo Lee added a comment -

        Thanks for your kind reply and sorry for my incomplete description. The complete code is as following:

        String jpql = "select v.id r0, sum(v0.score) r1 from Stall v left join v.scores v0 where v.deleted = :p0 and v.market = :p1 group by v.id order by r1 desc, r0";
        Query q = entityManager.createQuery(jpql);
        q.setParameter("p0", Boolean.FALSE);
        q.setParameter("p1", id);
        q.setFirstResult(startRow + 1);
        q.setMaxResults(maxRows);
        List results = q.getResultList();

        There will be no problem if I comment out "q.setFirstResult" and "q.setMaxResults" calls, but if not, the problem occurs, and OpenJPA generates the following wrong SQL:

        SELECT * FROM (SELECT r.*, ROWNUM RNUM FROM (SELECT t0.id AS c0, SUM(t1.score) AS r1 AS c1 FROM stalls t0, scores t1 WHERE (t0.deleted = ? AND t0.market = ? AND 1 = 1) AND t0.id = t1.stall GROUP BY t0.id ORDER BY r1 DESC, t0.id ASC) r WHERE ROWNUM <= ?) WHERE RNUM > ?

        Show
        Azuo Lee added a comment - Thanks for your kind reply and sorry for my incomplete description. The complete code is as following: String jpql = "select v .id r0, sum( v0 .score) r1 from Stall v left join v .scores v0 where v .deleted = : p0 and v .market = : p1 group by v .id order by r1 desc, r0"; Query q = entityManager.createQuery(jpql); q.setParameter(" p0 ", Boolean.FALSE); q.setParameter(" p1 ", id); q.setFirstResult(startRow + 1); q.setMaxResults(maxRows); List results = q.getResultList(); There will be no problem if I comment out "q.setFirstResult" and "q.setMaxResults" calls, but if not, the problem occurs, and OpenJPA generates the following wrong SQL: SELECT * FROM (SELECT r.*, ROWNUM RNUM FROM (SELECT t0.id AS c0, SUM(t1.score) AS r1 AS c1 FROM stalls t0, scores t1 WHERE (t0.deleted = ? AND t0.market = ? AND 1 = 1) AND t0.id = t1.stall GROUP BY t0.id ORDER BY r1 DESC, t0.id ASC) r WHERE ROWNUM <= ?) WHERE RNUM > ?
        Hide
        Catalina Wei added a comment -

        Azuo,
        (1). This problem seems already fixed, because I was not able to recreate the problem using openjpa branch 2.0.x nor trunk level code.
        Could you extract code from svn and build openjpa-2.0.1-SNAPSHOT.jar and give it a try ?

        Here is a JPQL string which is similar to your JPQL which worked under 2.0.x and trunk level code.

        3875 Test TRACE [main] openjpa.Query - Executing query: SELECT c.name as name, SUM(c.age) as sage FROM CompUser c group by c.name order by sage desc, name
        3890 Test TRACE [main] openjpa.jdbc.SQL - <t 13079028, conn 22694519> executing prepstmnt 15922565 SELECT t0.name, SUM(t0.age) AS sage FROM CompUser t0 GROUP BY t0.name ORDER BY sage DESC, t0.name ASC

        (2). Order by "NULLS LAST" is not supported in OpenJPA.
        Your workaround is to use DESC or ASC depending on NULL is ordered high or not to get the right order as you intended to. Some database backends consider NULL is higher than non-null values; Oracle is one of them.

        Catalina Wei

        Show
        Catalina Wei added a comment - Azuo, (1). This problem seems already fixed, because I was not able to recreate the problem using openjpa branch 2.0.x nor trunk level code. Could you extract code from svn and build openjpa-2.0.1-SNAPSHOT.jar and give it a try ? Here is a JPQL string which is similar to your JPQL which worked under 2.0.x and trunk level code. 3875 Test TRACE [main] openjpa.Query - Executing query: SELECT c.name as name, SUM(c.age) as sage FROM CompUser c group by c.name order by sage desc, name 3890 Test TRACE [main] openjpa.jdbc.SQL - <t 13079028, conn 22694519> executing prepstmnt 15922565 SELECT t0.name, SUM(t0.age) AS sage FROM CompUser t0 GROUP BY t0.name ORDER BY sage DESC, t0.name ASC (2). Order by "NULLS LAST" is not supported in OpenJPA. Your workaround is to use DESC or ASC depending on NULL is ordered high or not to get the right order as you intended to. Some database backends consider NULL is higher than non-null values; Oracle is one of them. Catalina Wei

          People

          • Assignee:
            Catalina Wei
            Reporter:
            Azuo Lee
          • Votes:
            0 Vote for this issue
            Watchers:
            0 Start watching this issue

            Dates

            • Created:
              Updated:

              Development