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/07/01 12:23:49 UTC
[jira] Created: (OPENJPA-1715) OpenJPA generates wrong SQL if a
result variable that references an aggregate expression is used in ORDER BY
clause
OpenJPA generates wrong SQL if a result variable that references an aggregate expression is used in ORDER BY clause
-------------------------------------------------------------------------------------------------------------------
Key: OPENJPA-1715
URL: https://issues.apache.org/jira/browse/OPENJPA-1715
Project: OpenJPA
Issue Type: Bug
Components: jpa
Affects Versions: 2.0.0
Environment: Tomcat 6 + Oracle 9i
Reporter: Azuo Lee
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?
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
Re: [jira] Created: (OPENJPA-1715) OpenJPA generates wrong SQL if a
result variable that references an aggregate expression is used in ORDER BY
clause
Posted by catalina wei <ca...@gmail.com>.
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
On Thu, Jul 1, 2010 at 3:23 AM, Azuo Lee (JIRA) <ji...@apache.org> wrote:
> OpenJPA generates wrong SQL if a result variable that references an
> aggregate expression is used in ORDER BY clause
>
> -------------------------------------------------------------------------------------------------------------------
>
> Key: OPENJPA-1715
> URL: https://issues.apache.org/jira/browse/OPENJPA-1715
> Project: OpenJPA
> Issue Type: Bug
> Components: jpa
> Affects Versions: 2.0.0
> Environment: Tomcat 6 + Oracle 9i
> Reporter: Azuo Lee
>
>
> 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?
>
>
> --
> This message is automatically generated by JIRA.
> -
> You can reply to this email to add a comment to the issue online.
>
>
[jira] Work stopped: (OPENJPA-1715) OpenJPA generates wrong SQL if
a result variable that references an aggregate expression is used in ORDER
BY clause
Posted by "Catalina Wei (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/OPENJPA-1715?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Work on OPENJPA-1715 stopped by Catalina Wei.
> OpenJPA generates wrong SQL if a result variable that references an aggregate expression is used in ORDER BY clause
> -------------------------------------------------------------------------------------------------------------------
>
> Key: OPENJPA-1715
> URL: https://issues.apache.org/jira/browse/OPENJPA-1715
> Project: OpenJPA
> Issue Type: Bug
> Components: jpa
> Affects Versions: 2.0.0
> Environment: Tomcat 6 + Oracle 9i
> Reporter: Azuo Lee
> Assignee: Catalina Wei
> Fix For: 2.0.1, 2.1.0
>
>
> 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?
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Resolved: (OPENJPA-1715) OpenJPA generates wrong SQL if a
result variable that references an aggregate expression is used in ORDER BY
clause
Posted by "Catalina Wei (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/OPENJPA-1715?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Catalina Wei resolved OPENJPA-1715.
-----------------------------------
Fix Version/s: 2.0.1
2.1.0
Resolution: Fixed
> OpenJPA generates wrong SQL if a result variable that references an aggregate expression is used in ORDER BY clause
> -------------------------------------------------------------------------------------------------------------------
>
> Key: OPENJPA-1715
> URL: https://issues.apache.org/jira/browse/OPENJPA-1715
> Project: OpenJPA
> Issue Type: Bug
> Components: jpa
> Affects Versions: 2.0.0
> Environment: Tomcat 6 + Oracle 9i
> Reporter: Azuo Lee
> Assignee: Catalina Wei
> Fix For: 2.0.1, 2.1.0
>
>
> 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?
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Commented: (OPENJPA-1715) OpenJPA generates wrong SQL if a
result variable that references an aggregate expression is used in ORDER BY
clause
Posted by "Catalina Wei (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/OPENJPA-1715?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12886917#action_12886917 ]
Catalina Wei commented on OPENJPA-1715:
---------------------------------------
Azuo,
Thank you very much, I got the problem reproduced.
Fix has checked in under trunk.
> OpenJPA generates wrong SQL if a result variable that references an aggregate expression is used in ORDER BY clause
> -------------------------------------------------------------------------------------------------------------------
>
> Key: OPENJPA-1715
> URL: https://issues.apache.org/jira/browse/OPENJPA-1715
> Project: OpenJPA
> Issue Type: Bug
> Components: jpa
> Affects Versions: 2.0.0
> Environment: Tomcat 6 + Oracle 9i
> Reporter: Azuo Lee
> Assignee: Catalina Wei
>
> 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?
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Commented: (OPENJPA-1715) OpenJPA generates wrong SQL if a
result variable that references an aggregate expression is used in ORDER BY
clause
Posted by "Catalina Wei (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/OPENJPA-1715?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12886021#action_12886021 ]
Catalina Wei commented on OPENJPA-1715:
---------------------------------------
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
> OpenJPA generates wrong SQL if a result variable that references an aggregate expression is used in ORDER BY clause
> -------------------------------------------------------------------------------------------------------------------
>
> Key: OPENJPA-1715
> URL: https://issues.apache.org/jira/browse/OPENJPA-1715
> Project: OpenJPA
> Issue Type: Bug
> Components: jpa
> Affects Versions: 2.0.0
> Environment: Tomcat 6 + Oracle 9i
> Reporter: Azuo Lee
> Assignee: Catalina Wei
>
> 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?
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Commented: (OPENJPA-1715) OpenJPA generates wrong SQL if a
result variable that references an aggregate expression is used in ORDER BY
clause
Posted by "Catalina Wei (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/OPENJPA-1715?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12884871#action_12884871 ]
Catalina Wei commented on OPENJPA-1715:
---------------------------------------
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
> OpenJPA generates wrong SQL if a result variable that references an aggregate expression is used in ORDER BY clause
> -------------------------------------------------------------------------------------------------------------------
>
> Key: OPENJPA-1715
> URL: https://issues.apache.org/jira/browse/OPENJPA-1715
> Project: OpenJPA
> Issue Type: Bug
> Components: jpa
> Affects Versions: 2.0.0
> Environment: Tomcat 6 + Oracle 9i
> Reporter: Azuo Lee
>
> 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?
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Work started: (OPENJPA-1715) OpenJPA generates wrong SQL if
a result variable that references an aggregate expression is used in ORDER
BY clause
Posted by "Catalina Wei (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/OPENJPA-1715?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Work on OPENJPA-1715 started by Catalina Wei.
> OpenJPA generates wrong SQL if a result variable that references an aggregate expression is used in ORDER BY clause
> -------------------------------------------------------------------------------------------------------------------
>
> Key: OPENJPA-1715
> URL: https://issues.apache.org/jira/browse/OPENJPA-1715
> Project: OpenJPA
> Issue Type: Bug
> Components: jpa
> Affects Versions: 2.0.0
> Environment: Tomcat 6 + Oracle 9i
> Reporter: Azuo Lee
> Assignee: Catalina Wei
>
> 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?
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Commented: (OPENJPA-1715) OpenJPA generates wrong SQL if a
result variable that references an aggregate expression is used in ORDER BY
clause
Posted by "Azuo Lee (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/OPENJPA-1715?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12886298#action_12886298 ]
Azuo Lee commented on OPENJPA-1715:
-----------------------------------
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.
> OpenJPA generates wrong SQL if a result variable that references an aggregate expression is used in ORDER BY clause
> -------------------------------------------------------------------------------------------------------------------
>
> Key: OPENJPA-1715
> URL: https://issues.apache.org/jira/browse/OPENJPA-1715
> Project: OpenJPA
> Issue Type: Bug
> Components: jpa
> Affects Versions: 2.0.0
> Environment: Tomcat 6 + Oracle 9i
> Reporter: Azuo Lee
> Assignee: Catalina Wei
>
> 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?
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Work stopped: (OPENJPA-1715) OpenJPA generates wrong SQL if
a result variable that references an aggregate expression is used in ORDER
BY clause
Posted by "Catalina Wei (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/OPENJPA-1715?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Work on OPENJPA-1715 stopped by Catalina Wei.
> OpenJPA generates wrong SQL if a result variable that references an aggregate expression is used in ORDER BY clause
> -------------------------------------------------------------------------------------------------------------------
>
> Key: OPENJPA-1715
> URL: https://issues.apache.org/jira/browse/OPENJPA-1715
> Project: OpenJPA
> Issue Type: Bug
> Components: jpa
> Affects Versions: 2.0.0
> Environment: Tomcat 6 + Oracle 9i
> Reporter: Azuo Lee
> Assignee: Catalina Wei
>
> 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?
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Assigned: (OPENJPA-1715) OpenJPA generates wrong SQL if a
result variable that references an aggregate expression is used in ORDER BY
clause
Posted by "Catalina Wei (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/OPENJPA-1715?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Catalina Wei reassigned OPENJPA-1715:
-------------------------------------
Assignee: Catalina Wei
> OpenJPA generates wrong SQL if a result variable that references an aggregate expression is used in ORDER BY clause
> -------------------------------------------------------------------------------------------------------------------
>
> Key: OPENJPA-1715
> URL: https://issues.apache.org/jira/browse/OPENJPA-1715
> Project: OpenJPA
> Issue Type: Bug
> Components: jpa
> Affects Versions: 2.0.0
> Environment: Tomcat 6 + Oracle 9i
> Reporter: Azuo Lee
> Assignee: Catalina Wei
>
> 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?
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Commented: (OPENJPA-1715) OpenJPA generates wrong SQL if a
result variable that references an aggregate expression is used in ORDER BY
clause
Posted by "Azuo Lee (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/OPENJPA-1715?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12885906#action_12885906 ]
Azuo Lee commented on OPENJPA-1715:
-----------------------------------
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 > ?
> OpenJPA generates wrong SQL if a result variable that references an aggregate expression is used in ORDER BY clause
> -------------------------------------------------------------------------------------------------------------------
>
> Key: OPENJPA-1715
> URL: https://issues.apache.org/jira/browse/OPENJPA-1715
> Project: OpenJPA
> Issue Type: Bug
> Components: jpa
> Affects Versions: 2.0.0
> Environment: Tomcat 6 + Oracle 9i
> Reporter: Azuo Lee
>
> 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?
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Commented: (OPENJPA-1715) OpenJPA generates wrong SQL if a
result variable that references an aggregate expression is used in ORDER BY
clause
Posted by "Azuo Lee (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/OPENJPA-1715?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12885914#action_12885914 ]
Azuo Lee commented on OPENJPA-1715:
-----------------------------------
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.
> OpenJPA generates wrong SQL if a result variable that references an aggregate expression is used in ORDER BY clause
> -------------------------------------------------------------------------------------------------------------------
>
> Key: OPENJPA-1715
> URL: https://issues.apache.org/jira/browse/OPENJPA-1715
> Project: OpenJPA
> Issue Type: Bug
> Components: jpa
> Affects Versions: 2.0.0
> Environment: Tomcat 6 + Oracle 9i
> Reporter: Azuo Lee
>
> 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?
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Work started: (OPENJPA-1715) OpenJPA generates wrong SQL if
a result variable that references an aggregate expression is used in ORDER
BY clause
Posted by "Catalina Wei (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/OPENJPA-1715?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Work on OPENJPA-1715 started by Catalina Wei.
> OpenJPA generates wrong SQL if a result variable that references an aggregate expression is used in ORDER BY clause
> -------------------------------------------------------------------------------------------------------------------
>
> Key: OPENJPA-1715
> URL: https://issues.apache.org/jira/browse/OPENJPA-1715
> Project: OpenJPA
> Issue Type: Bug
> Components: jpa
> Affects Versions: 2.0.0
> Environment: Tomcat 6 + Oracle 9i
> Reporter: Azuo Lee
> Assignee: Catalina Wei
>
> 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?
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.