From dev-return-16973-apmail-openjpa-dev-archive=openjpa.apache.org@openjpa.apache.org Sat Jul 24 16:14:12 2010 Return-Path: Delivered-To: apmail-openjpa-dev-archive@www.apache.org Received: (qmail 8797 invoked from network); 24 Jul 2010 16:14:11 -0000 Received: from unknown (HELO mail.apache.org) (140.211.11.3) by 140.211.11.9 with SMTP; 24 Jul 2010 16:14:11 -0000 Received: (qmail 58600 invoked by uid 500); 24 Jul 2010 16:14:11 -0000 Delivered-To: apmail-openjpa-dev-archive@openjpa.apache.org Received: (qmail 58551 invoked by uid 500); 24 Jul 2010 16:14:11 -0000 Mailing-List: contact dev-help@openjpa.apache.org; run by ezmlm Precedence: bulk List-Help: List-Unsubscribe: List-Post: List-Id: Reply-To: dev@openjpa.apache.org Delivered-To: mailing list dev@openjpa.apache.org Received: (qmail 58543 invoked by uid 99); 24 Jul 2010 16:14:10 -0000 Received: from athena.apache.org (HELO athena.apache.org) (140.211.11.136) by apache.org (qpsmtpd/0.29) with ESMTP; Sat, 24 Jul 2010 16:14:10 +0000 X-ASF-Spam-Status: No, hits=-2000.0 required=10.0 tests=ALL_TRUSTED X-Spam-Check-By: apache.org Received: from [140.211.11.22] (HELO thor.apache.org) (140.211.11.22) by apache.org (qpsmtpd/0.29) with ESMTP; Sat, 24 Jul 2010 16:14:10 +0000 Received: from thor (localhost [127.0.0.1]) by thor.apache.org (8.13.8+Sun/8.13.8) with ESMTP id o6OGDn7l028652 for ; Sat, 24 Jul 2010 16:13:49 GMT Message-ID: <13676567.568421279988029405.JavaMail.jira@thor> Date: Sat, 24 Jul 2010 12:13:49 -0400 (EDT) From: "Catalina Wei (JIRA)" To: dev@openjpa.apache.org Subject: [jira] Work stopped: (OPENJPA-1715) OpenJPA generates wrong SQL if a result variable that references an aggregate expression is used in ORDER BY clause In-Reply-To: <9860682.151721277979829289.JavaMail.jira@thor> MIME-Version: 1.0 Content-Type: text/plain; charset=utf-8 Content-Transfer-Encoding: 7bit X-JIRA-FingerPrint: 30527f35849b9dde25b450d4833f0394 [ 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.