Mailing-List: contact dev-help@openjpa.apache.org; run by ezmlm
Precedence: bulk
Reply-To: dev@openjpa.apache.org
Message-ID: <682043754.1218179564419.JavaMail.jira@brutus>
Date: Fri, 8 Aug 2008 00:12:44 -0700 (PDT)
From: "Catalina Wei (JIRA)" <jira@apache.org>
To: dev@openjpa.apache.org
Subject: [jira] Closed: (OPENJPA-681) Subquery generates SQL with syntax
 error
In-Reply-To: <989846863.1218159524216.JavaMail.jira@brutus>
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: 7bit


     [ https://issues.apache.org/jira/browse/OPENJPA-681?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Catalina Wei closed OPENJPA-681.
--------------------------------

       Resolution: Fixed
    Fix Version/s: 1.3.0
                   1.2.1
                   1.0.4

 fix checked in under svn revisions:
 1.0.x - r683875
 trunk - r683876
 1.2.x - r683877


> Subquery generates SQL with syntax error
> ----------------------------------------
>
>                 Key: OPENJPA-681
>                 URL: https://issues.apache.org/jira/browse/OPENJPA-681
>             Project: OpenJPA
>          Issue Type: Sub-task
>            Reporter: Catalina Wei
>             Fix For: 1.0.4, 1.2.1, 1.3.0
>
>
> This is another kind of subquery that is not handled correctly in resolving table aliases, the failed JPQL:
>         "select o1.oid, c.name from Order o1, Customer c where o1.amount = " +
>             " any(select o2.amount from in(c.orders) o2)",
> The incorrect SQL contains  t4.name where t4  is not defined in FROM clause:
> SELECT t0.oid, t4.name FROM Order t0 JOIN Customer t1 ON (1 = 1) WHERE (t0.amount = ANY (SELECT t3.amount FROM Customer t1, Order t2, Order t3 WHERE (t2.oid = t3.oid) AND t1.countryCode = t2.CUSTOMER_COUNTRYCODE AND t1.id = t2.CUSTOMER_ID )) 
> The correct SQL should be:
> SELECT t0.oid, t1.name FROM Order t0 JOIN Customer t1 ON (1 = 1) WHERE (t0.amount = ANY (SELECT t3.amount FROM Order t2, Order t3 WHERE (t2.oid = t3.oid) AND t1.countryCode = t2.CUSTOMER_COUNTRYCODE AND t1.id = t2.CUSTOMER_ID )) 

-- 
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.