openjpa-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From fa...@apache.org
Subject svn commit: r826135 - in /openjpa: branches/1.3.x/openjpa-jdbc/src/main/java/org/apache/openjpa/jdbc/sql/ branches/1.3.x/openjpa-persistence-jdbc/src/test/java/org/apache/openjpa/persistence/subquery/ trunk/openjpa-jdbc/src/main/java/org/apache/openjpa...
Date Sat, 17 Oct 2009 00:07:05 GMT
Author: fancy
Date: Sat Oct 17 00:07:04 2009
New Revision: 826135

URL: http://svn.apache.org/viewvc?rev=826135&view=rev
Log:
OPENJPA-1185 Subquery overhaul
fix subquery correlation involving association table

Added:
    openjpa/branches/1.3.x/openjpa-persistence-jdbc/src/test/java/org/apache/openjpa/persistence/subquery/Person.java
Modified:
    openjpa/branches/1.3.x/openjpa-jdbc/src/main/java/org/apache/openjpa/jdbc/sql/SelectImpl.java
    openjpa/branches/1.3.x/openjpa-persistence-jdbc/src/test/java/org/apache/openjpa/persistence/subquery/Account.java
    openjpa/branches/1.3.x/openjpa-persistence-jdbc/src/test/java/org/apache/openjpa/persistence/subquery/TestJPQLSubquery.java
    openjpa/trunk/openjpa-jdbc/src/main/java/org/apache/openjpa/jdbc/sql/SelectImpl.java
    openjpa/trunk/openjpa-persistence-jdbc/src/test/java/org/apache/openjpa/persistence/criteria/TestJPQLSubquery.java

Modified: openjpa/branches/1.3.x/openjpa-jdbc/src/main/java/org/apache/openjpa/jdbc/sql/SelectImpl.java
URL: http://svn.apache.org/viewvc/openjpa/branches/1.3.x/openjpa-jdbc/src/main/java/org/apache/openjpa/jdbc/sql/SelectImpl.java?rev=826135&r1=826134&r2=826135&view=diff
==============================================================================
--- openjpa/branches/1.3.x/openjpa-jdbc/src/main/java/org/apache/openjpa/jdbc/sql/SelectImpl.java
(original)
+++ openjpa/branches/1.3.x/openjpa-jdbc/src/main/java/org/apache/openjpa/jdbc/sql/SelectImpl.java
Sat Oct 17 00:07:04 2009
@@ -2894,13 +2894,21 @@
                 boolean createIndex = true;
                 Table table2 = (inverse) ? fk.getTable() 
                     : fk.getPrimaryKeyTable();
-                if (table2.isAssociation())
-                    createIndex = true;
+                boolean created = false;
+                int alias2 = -1;
+                if (table2.isAssociation()) {
+                    alias2 = _sel.getTableIndex(table2, this, false);
+                    if (alias2 == -1)
+                        createIndex = true;
+                    else 
+                        created = true;
+                }
                 else if (context == _sel.ctx()) 
                    createIndex = true;
                 else if (correlatedVar != null)
                     createIndex = false;
-                int alias2 = _sel.getTableIndex(table2, this, createIndex);
+                if (!created)
+                    alias2 = _sel.getTableIndex(table2, this, createIndex);
                 Join j = new Join(table1, alias1, table2, alias2, fk, inverse);
                 j.setType((outer) ? Join.TYPE_OUTER : Join.TYPE_INNER);
 

Modified: openjpa/branches/1.3.x/openjpa-persistence-jdbc/src/test/java/org/apache/openjpa/persistence/subquery/Account.java
URL: http://svn.apache.org/viewvc/openjpa/branches/1.3.x/openjpa-persistence-jdbc/src/test/java/org/apache/openjpa/persistence/subquery/Account.java?rev=826135&r1=826134&r2=826135&view=diff
==============================================================================
--- openjpa/branches/1.3.x/openjpa-persistence-jdbc/src/test/java/org/apache/openjpa/persistence/subquery/Account.java
(original)
+++ openjpa/branches/1.3.x/openjpa-persistence-jdbc/src/test/java/org/apache/openjpa/persistence/subquery/Account.java
Sat Oct 17 00:07:04 2009
@@ -39,6 +39,9 @@
     private int balance;
     private Integer loan;
     
+    @OneToOne
+    private Person owner;
+    
     @ManyToOne
     private Customer customer;
     
@@ -51,6 +54,14 @@
         return id;
     }
 
+    public Person getOwner() {
+        return owner;
+    }
+
+    public void setOwner(Person owner) {
+        this.owner = owner;
+    }
+
     public String getName() {
         return name;
     }

Added: openjpa/branches/1.3.x/openjpa-persistence-jdbc/src/test/java/org/apache/openjpa/persistence/subquery/Person.java
URL: http://svn.apache.org/viewvc/openjpa/branches/1.3.x/openjpa-persistence-jdbc/src/test/java/org/apache/openjpa/persistence/subquery/Person.java?rev=826135&view=auto
==============================================================================
--- openjpa/branches/1.3.x/openjpa-persistence-jdbc/src/test/java/org/apache/openjpa/persistence/subquery/Person.java
(added)
+++ openjpa/branches/1.3.x/openjpa-persistence-jdbc/src/test/java/org/apache/openjpa/persistence/subquery/Person.java
Sat Oct 17 00:07:04 2009
@@ -0,0 +1,74 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.    
+ */
+package org.apache.openjpa.persistence.subquery;
+
+import java.util.Set;
+
+import javax.persistence.Entity;
+import javax.persistence.GeneratedValue;
+import javax.persistence.Id;
+import javax.persistence.Table;
+
+import org.apache.openjpa.persistence.PersistentCollection;
+
+@Entity
+@Table(name="CR_PSN")
+
+public class Person {
+    @Id
+    @GeneratedValue
+    private int id;
+
+    private String name;
+    
+	@PersistentCollection
+	private Set<String> nickNames;
+	
+	protected Person() {
+	    this("?");
+	}
+	
+	public Person(String name) {
+	    setName(name);
+	}
+
+    public int getId() {
+        return id;
+    }
+
+    public String getName() {
+        return name;
+    }
+    
+    public void setName(String name) {
+        this.name = name;
+    }
+
+    public Set<String> getNickNames() {
+        return nickNames;
+    }
+    
+    public void setNickNames(Set<String> nickNames) {
+        this.nickNames = nickNames;
+    }
+    
+    public void addNickName(String nickName) {
+        nickNames.add(nickName);
+    }
+}

Modified: openjpa/branches/1.3.x/openjpa-persistence-jdbc/src/test/java/org/apache/openjpa/persistence/subquery/TestJPQLSubquery.java
URL: http://svn.apache.org/viewvc/openjpa/branches/1.3.x/openjpa-persistence-jdbc/src/test/java/org/apache/openjpa/persistence/subquery/TestJPQLSubquery.java?rev=826135&r1=826134&r2=826135&view=diff
==============================================================================
--- openjpa/branches/1.3.x/openjpa-persistence-jdbc/src/test/java/org/apache/openjpa/persistence/subquery/TestJPQLSubquery.java
(original)
+++ openjpa/branches/1.3.x/openjpa-persistence-jdbc/src/test/java/org/apache/openjpa/persistence/subquery/TestJPQLSubquery.java
Sat Oct 17 00:07:04 2009
@@ -49,7 +49,7 @@
                 DependentId.class, Dependent.class, FrequentFlierPlan.class,
                 LineItem.class, Magazine.class, Manager.class,
                 Order.class, Phone.class, Product.class,
-                Publisher.class, Request.class,
+                Publisher.class, Request.class, Person.class,
                 "openjpa.jdbc.JDBCListeners", new JDBCListener[] {  auditor },
                 DROP_TABLES
                 );
@@ -1080,6 +1080,96 @@
 
         executeAndCompareSQL(jpql, expectedSQL);
     }
+    
+    public void testPluralCorrelatedJoin1() {
+        String jpql = "SELECT o.quantity, o.totalCost, "
+            + "a.zipCode FROM Customer c JOIN c.orders o JOIN c.address a "
+            + "WHERE a.state = " 
+            + "(SELECT o.name from Customer c1 JOIN c1.orders o1 where o.quantity = o1.quantity)";
+        
+        String expectedSQL = "SELECT t2.quantity, t2.totalCost, t1.zipCode "
+            + "FROM CR_CUST t0 INNER JOIN CR_ODR t2 ON t0.id = t2.CUSTOMER_ID "
+            + "INNER JOIN CR_ADDR t1 ON t0.ADDRESS_ID = t1.id "
+            + "WHERE (t1.state = "
+            + "(SELECT t2.name "
+            + "FROM CR_CUST t3 INNER JOIN CR_ODR t4 ON t3.id = t4.CUSTOMER_ID "
+            + "WHERE (t2.quantity = t4.quantity)) AND 1 = 1)";
+
+        executeAndCompareSQL(jpql, expectedSQL);
+    }
+
+    public void testPluralCorrelatedJoin2() {
+        String jpql = "SELECT d.name FROM Department d JOIN d.employees e JOIN e.manager
m "
+           + "WHERE m.name = (SELECT e1.name from Employee e1 JOIN e1.manager m1 "
+           + "where m.name = m1.name)";
+        String expectedSQL = "SELECT t0.name FROM CR_DEPT t0 "
+            + "INNER JOIN CR_DEPT_CR_EMP t1 ON t0.deptNo = t1.DEPARTMENT_DEPTNO "
+            + "INNER JOIN CR_EMP t2 ON t1.EMPLOYEES_EMPID = t2.empId "
+            + "INNER JOIN CR_EMP t6 ON t1.EMPLOYEES_EMPID = t6.empId "
+            + "INNER JOIN CR_MGR t3 ON t2.MANAGER_ID = t3.id "
+            + "INNER JOIN CR_MGR t7 ON t6.MANAGER_ID = t7.id "
+            + "WHERE (t3.name = (SELECT t4.name FROM CR_EMP t4 "
+            + "INNER JOIN CR_MGR t5 ON t4.MANAGER_ID = t5.id "
+            + "WHERE (t7.name = t5.name)) AND 1 = 1)";
+
+        executeAndCompareSQL(jpql, expectedSQL);
+    }
+
+    public void testPluralCorrelatedJoin3() {
+        String jpql = "SELECT o FROM Order o JOIN o.customer c JOIN c.accounts a WHERE c.name
= "
+            + "ANY (SELECT a1.name FROM Account a1 WHERE a.owner = a1.owner)";
+        String expectedSQL = "SELECT t0.id, t0.count, t6.id, t6.accountNum, t7.id, t7.city,
"
+            + "t7.country, t7.county, t7.state, t7.street, t8.userid, t8.DTYPE, t8.age, "
+            + "t8.compName, t8.creditRating, t8.name, t7.zipCode, t6.balanceOwed, "
+            + "t6.creditRating, t6.filledOrderCount, t6.firstName, t6.lastName, t6.name,
"
+            + "t6.status, t0.delivered, t0.name, t0.orderTs, t0.quantity, t0.totalCost "
+            + "FROM CR_ODR t0 "
+            + "INNER JOIN CR_CUST t1 ON t0.CUSTOMER_ID = t1.id "
+            + "LEFT OUTER JOIN CR_CUST t6 ON t0.CUSTOMER_ID = t6.id "
+            + "INNER JOIN CR_CUST_CR_ACCT t2 ON t1.id = t2.CUSTOMER_ID "
+            + "LEFT OUTER JOIN CR_ADDR t7 ON t6.ADDRESS_ID = t7.id "
+            + "INNER JOIN CR_ACCT t3 ON t2.ACCOUNTS_ID = t3.id "
+            + "INNER JOIN CR_ACCT t4 ON t2.ACCOUNTS_ID = t4.id "
+            + "LEFT OUTER JOIN CompUser t8 ON t7.id = t8.ADD_ID WHERE (t1.name = "
+            + "ANY (SELECT t5.name FROM CR_ACCT t5 "
+            + "WHERE (t4.OWNER_ID = t5.OWNER_ID)) AND 1 = 1)";
+
+        executeAndCompareSQL(jpql, expectedSQL);
+    }    
+
+    public void testPluralCorrelatedJoin4() {
+        String jpql = 
+        "SELECT o.quantity FROM Order o JOIN o.customer c JOIN c.accounts a JOIN a.owner
owner WHERE c.name = "
+        + "ANY (SELECT a1.name FROM Account a1 JOIN a1.owner owner1 WHERE owner.name = owner1.name)";
+        String expectedSQL = "SELECT t0.quantity FROM CR_ODR t0 "
+            + "INNER JOIN CR_CUST t1 ON t0.CUSTOMER_ID = t1.id "
+            + "INNER JOIN CR_CUST_CR_ACCT t2 ON t1.id = t2.CUSTOMER_ID "
+            + "INNER JOIN CR_ACCT t3 ON t2.ACCOUNTS_ID = t3.id "
+            + "INNER JOIN CR_ACCT t7 ON t2.ACCOUNTS_ID = t7.id "
+            + "INNER JOIN CR_PSN t4 ON t3.OWNER_ID = t4.id "
+            + "INNER JOIN CR_PSN t8 ON t7.OWNER_ID = t8.id WHERE (t1.name = "
+            + "ANY (SELECT t5.name "
+            + "FROM CR_ACCT t5 INNER JOIN CR_PSN t6 ON t5.OWNER_ID = t6.id "
+            + "WHERE (t8.name = t6.name)) AND 1 = 1)";
+
+        executeAndCompareSQL(jpql, expectedSQL);
+    }    
+
+    public void testPluralCorrelatedJoin5() {
+        String jpql = "SELECT o.quantity FROM Order o JOIN o.customer c JOIN c.accounts a
WHERE c.name = "
+            + "ANY (SELECT owner.name FROM a.owner owner WHERE owner.id = 1)";
+        String expectedSQL = "SELECT t0.quantity FROM CR_ODR t0 "
+            + "INNER JOIN CR_CUST t1 ON t0.CUSTOMER_ID = t1.id "
+            + "INNER JOIN CR_CUST_CR_ACCT t2 ON t1.id = t2.CUSTOMER_ID "
+            + "INNER JOIN CR_ACCT t3 ON t2.ACCOUNTS_ID = t3.id "
+            + "INNER JOIN CR_ACCT t4 ON t2.ACCOUNTS_ID = t4.id "
+            + "WHERE (t1.name = "
+            + "ANY (SELECT t5.name FROM CR_PSN t5 WHERE ("
+            + "CAST(t5.id AS BIGINT) = CAST(? AS BIGINT) AND t4.OWNER_ID = t5.id)) "
+            + "AND 1 = 1)";
+
+        executeAndCompareSQL(jpql, expectedSQL);
+    }    
 
     void executeQueryAndCompareSQL(Query q, String jpql, String expectedSQL) {
         JDBCConfiguration conf = (JDBCConfiguration) emf.getConfiguration();

Modified: openjpa/trunk/openjpa-jdbc/src/main/java/org/apache/openjpa/jdbc/sql/SelectImpl.java
URL: http://svn.apache.org/viewvc/openjpa/trunk/openjpa-jdbc/src/main/java/org/apache/openjpa/jdbc/sql/SelectImpl.java?rev=826135&r1=826134&r2=826135&view=diff
==============================================================================
--- openjpa/trunk/openjpa-jdbc/src/main/java/org/apache/openjpa/jdbc/sql/SelectImpl.java (original)
+++ openjpa/trunk/openjpa-jdbc/src/main/java/org/apache/openjpa/jdbc/sql/SelectImpl.java Sat
Oct 17 00:07:04 2009
@@ -2859,13 +2859,21 @@
                 boolean createIndex = true;
                 Table table2 = (inverse) ? fk.getTable() 
                     : fk.getPrimaryKeyTable();
-                if (table2.isAssociation())
-                    createIndex = true;
+                boolean created = false;
+                int alias2 = -1;
+                if (table2.isAssociation()) {
+                    alias2 = _sel.getTableIndex(table2, this, false);
+                    if (alias2 == -1)
+                        createIndex = true;
+                    else 
+                        created = true;
+                }
                 else if (context == _sel.ctx()) 
                    createIndex = true;
                 else if (correlatedVar != null)
                     createIndex = false;
-                int alias2 = _sel.getTableIndex(table2, this, createIndex);
+                if (!created)
+                    alias2 = _sel.getTableIndex(table2, this, createIndex);
                 Join j = new Join(table1, alias1, table2, alias2, fk, inverse);
                 j.setType((outer) ? Join.TYPE_OUTER : Join.TYPE_INNER);
 

Modified: openjpa/trunk/openjpa-persistence-jdbc/src/test/java/org/apache/openjpa/persistence/criteria/TestJPQLSubquery.java
URL: http://svn.apache.org/viewvc/openjpa/trunk/openjpa-persistence-jdbc/src/test/java/org/apache/openjpa/persistence/criteria/TestJPQLSubquery.java?rev=826135&r1=826134&r2=826135&view=diff
==============================================================================
--- openjpa/trunk/openjpa-persistence-jdbc/src/test/java/org/apache/openjpa/persistence/criteria/TestJPQLSubquery.java
(original)
+++ openjpa/trunk/openjpa-persistence-jdbc/src/test/java/org/apache/openjpa/persistence/criteria/TestJPQLSubquery.java
Sat Oct 17 00:07:04 2009
@@ -34,8 +34,6 @@
 import javax.persistence.criteria.SetJoin;
 import javax.persistence.criteria.Subquery;
 
-import org.apache.openjpa.persistence.test.AllowFailure;
-
 /**
  * Tests type-strict version of Criteria API.
  * 
@@ -1575,13 +1573,22 @@
         q.where(cb.exists(sq).not());
         assertEquivalence(q, jpql);
     }
-
-    // Plural correlated Join
-    public void testCollectionJoin1() {
+    
+    public void testPluralCorrelatedJoin1() {
         String jpql = "SELECT o.quantity, o.totalCost*1.08, "
             + "a.zipCode FROM Customer c JOIN c.orders o JOIN c.address a "
             + "WHERE a.state = " 
             + "(SELECT o.name from Customer c1 JOIN c1.orders o1 where o.quantity = o1.quantity)";
+        
+        String expectedSQL = "SELECT t2.quantity, (t2.totalCost * ?), t1.zipCode "
+            + "FROM CR_CUST t0 INNER JOIN CR_ODR t2 ON t0.id = t2.CUSTOMER_ID "
+            + "INNER JOIN CR_ADDR t1 ON t0.ADDRESS_ID = t1.id "
+            + "WHERE (t1.state = "
+            + "(SELECT t2.name "
+            + "FROM CR_CUST t3 INNER JOIN CR_ODR t4 ON t3.id = t4.CUSTOMER_ID "
+            + "WHERE (t2.quantity = t4.quantity)) AND 1 = 1)";
+        executeAndCompareSQL(jpql, expectedSQL);
+
         CriteriaQuery<?> q = cb.createQuery();
         Root<Customer> cust = q.from(Customer.class);
         SetJoin<Customer, Order> order = cust.joinSet("orders");
@@ -1599,10 +1606,55 @@
         assertEquivalence(q, jpql);
     }
 
-    // correlated Singular Join => Plural Join
-    public void testCollection3() {
+    public void testPluralCorrelatedJoin2() {
+        String jpql = "SELECT d.name FROM Department d JOIN d.employees e JOIN e.manager
m "
+           + "WHERE m.salary = (SELECT e1.salary from Employee e1 JOIN e1.manager m1 "
+           + "where m.name = m1.name)";
+        String expectedSQL = "SELECT t0.name FROM CR_DEPT t0 "
+            + "INNER JOIN CR_DEPT_CR_EMP t1 ON t0.deptNo = t1.DEPARTMENT_DEPTNO "
+            + "INNER JOIN CR_EMP t2 ON t1.EMPLOYEES_EMPID = t2.empId "
+            + "INNER JOIN CR_EMP t6 ON t1.EMPLOYEES_EMPID = t6.empId "
+            + "INNER JOIN CR_MGR t3 ON t2.MANAGER_ID = t3.id "
+            + "INNER JOIN CR_MGR t7 ON t6.MANAGER_ID = t7.id "
+            + "WHERE (t3.salary = (SELECT t4.salary FROM CR_EMP t4 "
+            + "INNER JOIN CR_MGR t5 ON t4.MANAGER_ID = t5.id "
+            + "WHERE (t7.name = t5.name)) AND 1 = 1)";
+        executeAndCompareSQL(jpql, expectedSQL);
+
+        CriteriaQuery<String> q = cb.createQuery(String.class);
+        Root<Department> d = q.from(Department.class);
+        SetJoin<Department, Employee> e = d.joinSet("employees");
+        Join<Employee, Manager> m = e.join("manager");
+        Subquery<Long> sq = q.subquery(Long.class);
+        Root<Employee> e1 = sq.from(Employee.class);
+        Join<Employee, Manager> m1 = e1.join("manager");
+        Join<Employee, Manager> corrJoin = sq.correlate(m);
+        sq.where(cb.equal(corrJoin.get(Manager_.name), m1.get(Manager_.name)));
+        q.where(cb.equal(m.get(Manager_.salary), sq.select(e1.get(Employee_.salary))));
+        q.select(d.get(Department_.name));
+        
+        assertEquivalence(q, jpql);
+    }
+
+    public void testPluralCorrelatedJoin3() {
         String jpql = "SELECT o FROM Order o JOIN o.customer c JOIN c.accounts a WHERE 10000
< "
             + "ANY (SELECT a1.balance FROM Account a1 WHERE a.owner = a1.owner)";
+        String expectedSQL = "SELECT t0.id, t0.count, t6.id, t6.accountNum, t7.id, t7.city,
"
+            + "t7.country, t7.county, t7.state, t7.street, t8.userid, t8.DTYPE, t8.age, "
+            + "t8.compName, t8.creditRating, t8.name, t7.zipCode, t6.balanceOwed, "
+            + "t6.creditRating, t6.filledOrderCount, t6.firstName, t6.lastName, t6.name,
"
+            + "t6.status, t0.delivered, t0.name, t0.orderTs, t0.quantity, t0.totalCost "
+            + "FROM CR_ODR t0 "
+            + "INNER JOIN CR_CUST t1 ON t0.CUSTOMER_ID = t1.id "
+            + "LEFT OUTER JOIN CR_CUST t6 ON t0.CUSTOMER_ID = t6.id "
+            + "INNER JOIN CR_CUST_CR_ACCT t2 ON t1.id = t2.CUSTOMER_ID "
+            + "LEFT OUTER JOIN CR_ADDR t7 ON t6.ADDRESS_ID = t7.id "
+            + "INNER JOIN CR_ACCT t3 ON t2.ACCOUNTS_ID = t3.id "
+            + "INNER JOIN CR_ACCT t4 ON t2.ACCOUNTS_ID = t4.id "
+            + "LEFT OUTER JOIN CompUser t8 ON t7.id = t8.ADD_ID WHERE (? < "
+            + "ANY (SELECT t5.balance FROM CR_ACCT t5 "
+            + "WHERE (t4.OWNER_ID = t5.OWNER_ID)) AND 1 = 1)";
+        executeAndCompareSQL(jpql, expectedSQL);
         
         CriteriaQuery<Order> q = cb.createQuery(Order.class);
         Root<Order> o = q.from(Order.class);
@@ -1618,4 +1670,64 @@
 
         assertEquivalence(q, jpql);
     }    
+
+    public void testPluralCorrelatedJoin4() {
+        String jpql = 
+        "SELECT o.quantity FROM Order o JOIN o.customer c JOIN c.accounts a JOIN a.owner
owner WHERE 10000 < "
+        + "ANY (SELECT a1.balance FROM Account a1 JOIN a1.owner owner1 WHERE owner.name =
owner1.name)";
+        String expectedSQL = "SELECT t0.quantity FROM CR_ODR t0 "
+            + "INNER JOIN CR_CUST t1 ON t0.CUSTOMER_ID = t1.id "
+            + "INNER JOIN CR_CUST_CR_ACCT t2 ON t1.id = t2.CUSTOMER_ID "
+            + "INNER JOIN CR_ACCT t3 ON t2.ACCOUNTS_ID = t3.id "
+            + "INNER JOIN CR_ACCT t7 ON t2.ACCOUNTS_ID = t7.id "
+            + "INNER JOIN CR_PSN t4 ON t3.OWNER_ID = t4.id "
+            + "INNER JOIN CR_PSN t8 ON t7.OWNER_ID = t8.id WHERE (? < "
+            + "ANY (SELECT t5.balance "
+            + "FROM CR_ACCT t5 INNER JOIN CR_PSN t6 ON t5.OWNER_ID = t6.id "
+            + "WHERE (t8.name = t6.name)) AND 1 = 1)";
+        executeAndCompareSQL(jpql, expectedSQL);
+        
+        CriteriaQuery<Integer> q = cb.createQuery(Integer.class);
+        Root<Order> o = q.from(Order.class);
+        Join<Order,Customer> c = o.join(Order_.customer);
+        ListJoin<Customer,Account> a = c.joinList("accounts");
+        Join<Account,Person> owner = a.join(Account_.owner);
+        
+        Subquery<Integer> sq = q.subquery(Integer.class);
+        Root<Account> a1 = sq.from(Account.class);
+        Join<Account,Person> owner1 = a1.join(Account_.owner);
+        Join<Account,Person> corrJoin = sq.correlate(owner);
+        sq.where(cb.equal(corrJoin.get(Person_.name), owner1.get(Person_.name)));
+        q.where(cb.lt(cb.literal(10000), cb.any(sq.select(a1.get(Account_.balance)))));
+        q.select(o.get(Order_.quantity));
+        assertEquivalence(q, jpql);
+    }    
+
+    public void testPluralCorrelatedJoin5() {
+        String jpql = "SELECT o.quantity FROM Order o JOIN o.customer c JOIN c.accounts a
WHERE c.name = "
+            + "ANY (SELECT owner.name FROM a.owner owner WHERE owner.id = 1)";
+        String expectedSQL = "SELECT t0.quantity FROM CR_ODR t0 "
+            + "INNER JOIN CR_CUST t1 ON t0.CUSTOMER_ID = t1.id "
+            + "INNER JOIN CR_CUST_CR_ACCT t2 ON t1.id = t2.CUSTOMER_ID "
+            + "INNER JOIN CR_ACCT t3 ON t2.ACCOUNTS_ID = t3.id "
+            + "INNER JOIN CR_ACCT t4 ON t2.ACCOUNTS_ID = t4.id "
+            + "WHERE (t1.name = "
+            + "ANY (SELECT t5.name FROM CR_PSN t5 WHERE (t5.id = ? AND t4.OWNER_ID = t5.id))
"
+            + "AND 1 = 1)";
+        executeAndCompareSQL(jpql, expectedSQL);
+        
+        CriteriaQuery<Integer> q = cb.createQuery(Integer.class);
+        Root<Order> o = q.from(Order.class);
+        Join<Order,Customer> c = o.join(Order_.customer);
+        ListJoin<Customer,Account> a = c.joinList("accounts");
+         
+        Subquery<String> sq = q.subquery(String.class);
+        ListJoin<Customer,Account> a1 = sq.correlate(a);
+        Join<Account,Person> owner = a1.join(Account_.owner);
+        sq.where(cb.equal(owner.get(Person_.id), 1));
+        q.where(cb.equal(c.get(Customer_.name), cb.any(sq.select(owner.get(Person_.name)))));
+        q.select(o.get(Order_.quantity));
+        assertEquivalence(q, jpql);
+    }    
+
 }



Mime
View raw message