openjpa-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From fayw...@apache.org
Subject svn commit: r790379 - in /openjpa/branches/subquery: openjpa-jdbc/src/main/java/org/apache/openjpa/jdbc/kernel/exps/ openjpa-jdbc/src/main/java/org/apache/openjpa/jdbc/sql/ openjpa-persistence-jdbc/src/test/java/org/apache/openjpa/persistence/criteria/
Date Wed, 01 Jul 2009 21:03:13 GMT
Author: faywang
Date: Wed Jul  1 21:03:12 2009
New Revision: 790379

URL: http://svn.apache.org/viewvc?rev=790379&view=rev
Log:
fix some mis-placed INNER JOIN problem

Modified:
    openjpa/branches/subquery/openjpa-jdbc/src/main/java/org/apache/openjpa/jdbc/kernel/exps/PCPath.java
    openjpa/branches/subquery/openjpa-jdbc/src/main/java/org/apache/openjpa/jdbc/sql/AbstractResult.java
    openjpa/branches/subquery/openjpa-jdbc/src/main/java/org/apache/openjpa/jdbc/sql/Joins.java
    openjpa/branches/subquery/openjpa-jdbc/src/main/java/org/apache/openjpa/jdbc/sql/SelectImpl.java
    openjpa/branches/subquery/openjpa-persistence-jdbc/src/test/java/org/apache/openjpa/persistence/criteria/TestJPQLSubquery.java

Modified: openjpa/branches/subquery/openjpa-jdbc/src/main/java/org/apache/openjpa/jdbc/kernel/exps/PCPath.java
URL: http://svn.apache.org/viewvc/openjpa/branches/subquery/openjpa-jdbc/src/main/java/org/apache/openjpa/jdbc/kernel/exps/PCPath.java?rev=790379&r1=790378&r2=790379&view=diff
==============================================================================
--- openjpa/branches/subquery/openjpa-jdbc/src/main/java/org/apache/openjpa/jdbc/kernel/exps/PCPath.java
(original)
+++ openjpa/branches/subquery/openjpa-jdbc/src/main/java/org/apache/openjpa/jdbc/kernel/exps/PCPath.java
Wed Jul  1 21:03:12 2009
@@ -497,8 +497,7 @@
                     isCorrelatedPath = true;
                     pstate.joins = pstate.joins.setCorrelatedVariable(action.var);
                 } else 
-                    pstate.joins = pstate.joins.
-                        setVariable((String) action.data);
+                    pstate.joins = pstate.joins.setVariable((String) action.data);
             }
             else if (action.op == Action.SUBQUERY) {
                 pstate.joins = pstate.joins.setSubselect((String) action.data);
@@ -576,7 +575,7 @@
             }
             prevaction = action;
             if (prevaction != null && prevaction.context != null) 
-                pstate.joins.setContext(prevaction.context);
+                pstate.joins = pstate.joins.setJoinContext(prevaction.context);
         }
         if (_varName != null)
             pstate.joins = pstate.joins.setVariable(_varName);
@@ -596,6 +595,7 @@
             // check if there are joins that belong to parent
             pstate.joins.moveJoinsToParent();
         }
+        pstate.joins.setJoinContext(null);
         return pstate;
     }
 

Modified: openjpa/branches/subquery/openjpa-jdbc/src/main/java/org/apache/openjpa/jdbc/sql/AbstractResult.java
URL: http://svn.apache.org/viewvc/openjpa/branches/subquery/openjpa-jdbc/src/main/java/org/apache/openjpa/jdbc/sql/AbstractResult.java?rev=790379&r1=790378&r2=790379&view=diff
==============================================================================
--- openjpa/branches/subquery/openjpa-jdbc/src/main/java/org/apache/openjpa/jdbc/sql/AbstractResult.java
(original)
+++ openjpa/branches/subquery/openjpa-jdbc/src/main/java/org/apache/openjpa/jdbc/sql/AbstractResult.java
Wed Jul  1 21:03:12 2009
@@ -883,7 +883,8 @@
             return this;
         }
 
-        public void setContext(Context context) {
+        public Joins setJoinContext(Context context) {
+            return this;
         }
 
         public void appendTo(SQLBuffer buf) {

Modified: openjpa/branches/subquery/openjpa-jdbc/src/main/java/org/apache/openjpa/jdbc/sql/Joins.java
URL: http://svn.apache.org/viewvc/openjpa/branches/subquery/openjpa-jdbc/src/main/java/org/apache/openjpa/jdbc/sql/Joins.java?rev=790379&r1=790378&r2=790379&view=diff
==============================================================================
--- openjpa/branches/subquery/openjpa-jdbc/src/main/java/org/apache/openjpa/jdbc/sql/Joins.java
(original)
+++ openjpa/branches/subquery/openjpa-jdbc/src/main/java/org/apache/openjpa/jdbc/sql/Joins.java
Wed Jul  1 21:03:12 2009
@@ -79,7 +79,7 @@
      * in transition from parent context to subquery.
      * @param context
      */
-    public void setContext(Context context);
+    public Joins setJoinContext(Context context);
     
     /**
      * Set the subquery alias.

Modified: openjpa/branches/subquery/openjpa-jdbc/src/main/java/org/apache/openjpa/jdbc/sql/SelectImpl.java
URL: http://svn.apache.org/viewvc/openjpa/branches/subquery/openjpa-jdbc/src/main/java/org/apache/openjpa/jdbc/sql/SelectImpl.java?rev=790379&r1=790378&r2=790379&view=diff
==============================================================================
--- openjpa/branches/subquery/openjpa-jdbc/src/main/java/org/apache/openjpa/jdbc/sql/SelectImpl.java
(original)
+++ openjpa/branches/subquery/openjpa-jdbc/src/main/java/org/apache/openjpa/jdbc/sql/SelectImpl.java
Wed Jul  1 21:03:12 2009
@@ -2024,16 +2024,21 @@
         Integer i = null;
         SelectImpl sel = this;
 
-//        System.out.println((_parent != null) ? "FindAliasForSUBQuery" :
-//            "FindAliasForQuery: " + key.toString());
-        String alias = (pj != null && pj.path() != null && 
-            pj.getCorrelatedVariable() == null) 
-            ? null : _schemaAlias;
-
-        if (table.isAssociation()) {
-            // create alias in this select
-             alias = null;
-        }
+        //currCtx is set from Action, it is reset to null after the PCPath initialization
+        Context currCtx = pj == null ? null : ((PathJoinsImpl)pj).context;
+        
+        // lastCtx is set to currCtx after the SelectJoins.join. pj.lastCtx and pj.path string
are 
+        // the last snapshot of pj. They will be used together for later table alias resolution
in
+        // the getColumnAlias(). 
+        Context lastCtx = pj == null ? null : ((PathJoinsImpl)pj).lastContext;
+        Context thisCtx = currCtx == null ? lastCtx : currCtx;
+        String corrVar = pj == null ? null : pj.getCorrelatedVariable();
+        
+        String alias = _schemaAlias;
+        if ((pj != null && pj.path() != null && (corrVar == null || ctx()
== thisCtx)) || 
+            table.isAssociation()) {
+            alias = null;
+        } 
 
         // find the context where this alias is defined
         Context ctx = (alias != null) ?
@@ -2042,14 +2047,16 @@
             sel = (SelectImpl) ctx.getSelect();
 
         if (!create) 
-            i = sel.findAlias(table, key);  // find in parent
+            i = sel.findAlias(table, key);  // find in parent and in myself
         else
-            i = sel.getAlias(table, key);
+            i = sel.getAlias(table, key); // find in myself
         if (i != null)
             return i;
         
-        if (create) {
+        if (create) { // create here
             i = sel.createAlias(table, key);
+        } else if (ctx != null && ctx != ctx()) { // create in other select
+            i = ((SelectImpl)ctx.getSelect()).createAlias(table, key);
         }
 
         return i;
@@ -2550,13 +2557,14 @@
             return new PathJoinsImpl().setSubselect(alias);
         }
 
-        public void setContext(Context context) {
-        }
-        
         public Joins setCorrelatedVariable(String var) {
             return this;
         }
 
+        public Joins setJoinContext(Context ctx) {
+            return this;
+        }
+
         public String getCorrelatedVariable() {
             return null;
         }
@@ -2575,6 +2583,7 @@
         protected String var = null;
         protected String correlatedVar = null;
         protected Context context = null;
+        protected Context lastContext = null;
 
         public Select getSelect() {
             return null;
@@ -2607,6 +2616,10 @@
         public void nullJoins() {
         }
 
+        public String getVariable() {
+            return var;
+        }
+        
         public Joins setVariable(String var) {
             this.var = var;
             return this;
@@ -2621,8 +2634,9 @@
             return correlatedVar;
         }
 
-        public void setContext(Context context) {
+        public Joins setJoinContext(Context context) {
             this.context = context;
+            return this;
          }
 
         public Joins setSubselect(String alias) {
@@ -2805,6 +2819,8 @@
             // until we get past the local table
             String var = this.var;
             this.var = null;
+            Context ctx = context; 
+            context = null; 
 
             // get first table alias before updating path; if there is a from
             // select then we shouldn't actually create a join object, since
@@ -2816,7 +2832,7 @@
                 boolean createIndex = true;
                 table1 = (inverse) ? fk.getPrimaryKeyTable() : fk.getTable();
                 if (correlatedVar != null)
-                    createIndex = false;
+                    createIndex = false;  // not to create here
                 alias1 = _sel.getTableIndex(table1, this, createIndex);
             }
 
@@ -2824,7 +2840,8 @@
             this.append(name);
             this.append(var);
             this.append(correlatedVar);
-
+            context = ctx; 
+            
             if (toMany) {
                 _sel._flags |= IMPLICIT_DISTINCT;
                 _sel._flags |= TO_MANY;
@@ -2835,11 +2852,11 @@
                 boolean createIndex = true;
                 Table table2 = (inverse) ? fk.getTable() 
                     : fk.getPrimaryKeyTable();
-                if (context != null && context == _sel.ctx()) {
+                if (table2.isAssociation())
                     createIndex = true;
-                    context = null;
-                }
-                else if (correlatedVar != null && !table2.isAssociation())
+                else if (context == _sel.ctx()) 
+                   createIndex = true;
+                else if (correlatedVar != null)
                     createIndex = false;
                 int alias2 = _sel.getTableIndex(table2, this, createIndex);
                 Join j = new Join(table1, alias1, table2, alias2, fk, inverse);
@@ -2853,6 +2870,8 @@
 
                 setCorrelated(j);
             }
+            lastContext = context;
+            context = null;
             return this;
         }
 
@@ -3131,6 +3150,12 @@
         return new SelectJoins(this).setCorrelatedVariable(var);
     }
     
+    public Joins setJoinContext(Context ctx) {
+        if (ctx == null)
+            return this;
+        return new SelectJoins(this).setJoinContext(ctx);
+    }
+
     public String getCorrelatedVariable() {
         return null;
     }

Modified: openjpa/branches/subquery/openjpa-persistence-jdbc/src/test/java/org/apache/openjpa/persistence/criteria/TestJPQLSubquery.java
URL: http://svn.apache.org/viewvc/openjpa/branches/subquery/openjpa-persistence-jdbc/src/test/java/org/apache/openjpa/persistence/criteria/TestJPQLSubquery.java?rev=790379&r1=790378&r2=790379&view=diff
==============================================================================
--- openjpa/branches/subquery/openjpa-persistence-jdbc/src/test/java/org/apache/openjpa/persistence/criteria/TestJPQLSubquery.java
(original)
+++ openjpa/branches/subquery/openjpa-persistence-jdbc/src/test/java/org/apache/openjpa/persistence/criteria/TestJPQLSubquery.java
Wed Jul  1 21:03:12 2009
@@ -39,12 +39,12 @@
     
     public void testSubqueries1() {
         String jpql = "SELECT goodCustomer FROM Customer goodCustomer WHERE "
-            + "goodCustomer.balanceOwed < (SELECT AVG(c.balanceOwed) " 
+            + "goodCustomer.balanceOwed < (SELECT AVG(c.balanceOwed) "
             + " FROM Customer c)";
         String expectedSQL = "SELECT t0.id, t0.accountNum, t2.id, t2.city, t2.country, t2.county,
t2.state, " + 
         "t2.street, t3.userid, t3.DTYPE, t3.age, t3.compName, t3.creditRating, t3.name, t2.zipCode,
" + 
         "t0.balanceOwed, t0.creditRating, t0.filledOrderCount, t0.firstName, t0.lastName,
t0.name, t0.status " + 
-        "FROM CR_CUST t0 LEFT OUTER JOIN CR_ADDR t2 ON t0.ADDRESS_ID = t2.id " + 
+        "FROM CR_CUST t0 LEFT OUTER JOIN CR_ADDR t2 ON t0.ADDRESS_ID = t2.id " +
         "LEFT OUTER JOIN CompUser t3 ON t2.id = t3.ADD_ID " + 
         "WHERE (t0.balanceOwed < (SELECT AVG(t1.balanceOwed) FROM CR_CUST t1 ))"; 
 
@@ -59,7 +59,7 @@
         "t2.street, t3.userid, t3.DTYPE, t3.age, t3.compName, t3.creditRating, t3.name, t2.zipCode,
t4.deptNo, " + 
         "t4.name, t5.id, t5.annualMiles, t5.name, t6.id, t7.deptNo, t7.name, t6.name, t6.salary,
t1.name, " + 
         "t1.rating, t1.salary, t8.empId, t8.EMP_TYPE, t8.ADDRESS_ID, t8.DEPARTMENT_DEPTNO,
t8.FREQUENTFLIERPLAN_ID, " +
-        "t8.MANAGER_ID, t8.name, t8.rating, t8.salary, t8.hireDate, t1.hireDate " + 
+        "t8.MANAGER_ID, t8.name, t8.rating, t8.salary, t8.hireDate, t1.hireDate " +
         "FROM CR_EMP t1 LEFT OUTER JOIN CR_ADDR t2 ON t1.ADDRESS_ID = t2.id " + 
         "LEFT OUTER JOIN CR_DEPT t4 ON t1.DEPARTMENT_DEPTNO = t4.deptNo " + 
         "LEFT OUTER JOIN FrequentFlierPlan t5 ON t1.FREQUENTFLIERPLAN_ID = t5.id " + 
@@ -131,7 +131,7 @@
         "t2.creditRating, t2.filledOrderCount, t2.firstName, t2.lastName, t2.name, t2.status
" + 
         "FROM CR_CUST t2 LEFT OUTER JOIN CR_ADDR t3 ON t2.ADDRESS_ID = t3.id " +
         "LEFT OUTER JOIN CompUser t4 ON t3.id = t4.ADD_ID " +
-        "WHERE ((SELECT COUNT(t1.id) " + 
+        "WHERE ((SELECT COUNT(t1.id) " +
         "FROM CR_CUST t0 INNER JOIN CR_ODR t1 ON t0.id = t1.CUSTOMER_ID WHERE (t2.id = t0.id)
) > ?)"; 
         execute(jpql, expectedSQL);
     }    
@@ -148,7 +148,28 @@
 
         execute(jpql, expectedSQL);
     }
-    
+
+    public void testSubqueries4d() {
+        String jpql = "SELECT c FROM Customer c WHERE (SELECT COUNT(o) "
+            + "FROM Customer c1 JOIN c1.orders o WHERE c.address.county = c1.address.county)
> 10";
+
+        String expectedSQL = "SELECT t2.id, t2.accountNum, t5.id, t5.city, t5.country, t5.county,
" +
+        "t5.state, t5.street, t6.userid, t6.DTYPE, t6.age, t6.compName, t6.creditRating,
t6.name, " +
+        "t5.zipCode, t2.balanceOwed, t2.creditRating, t2.filledOrderCount, t2.firstName,
t2.lastName, " +
+        "t2.name, t2.status " +
+        "FROM CR_CUST t2 " +
+        "INNER JOIN CR_ADDR t3 ON t2.ADDRESS_ID = t3.id " +
+        "LEFT OUTER JOIN CR_ADDR t5 ON t2.ADDRESS_ID = t5.id " +
+        "LEFT OUTER JOIN CompUser t6 ON t5.id = t6.ADD_ID " +
+        "WHERE ((SELECT COUNT(t1.id) " +
+        "FROM CR_CUST t0 " +
+        "INNER JOIN CR_ODR t1 ON t0.id = t1.CUSTOMER_ID " +
+        "INNER JOIN CR_ADDR t4 ON t0.ADDRESS_ID = t4.id " +
+        "WHERE (t3.county = t4.county) ) > ?)";
+
+        execute(jpql, expectedSQL);
+    }
+
     public void testSubqueries5() {
         String jpql = "SELECT o FROM Order o WHERE 10000 < ALL ("
             + "SELECT a.balance FROM o.customer c JOIN c.accounts a)";
@@ -167,7 +188,7 @@
         String jpql = "SELECT o FROM Order o WHERE o.name = SOME ("
             + "SELECT a.name FROM o.customer c JOIN c.accounts a)";
         String expectedSQL = "SELECT t3.id, t3.count, t4.id, t4.accountNum, t5.id, t5.city,
t5.country, t5.county, " + 
-        "t5.state, t5.street, t6.userid, t6.DTYPE, t6.age, t6.compName, t6.creditRating,
t6.name, t5.zipCode, " + 
+        "t5.state, t5.street, t6.userid, t6.DTYPE, t6.age, t6.compName, t6.creditRating,
t6.name, t5.zipCode, " +
         "t4.balanceOwed, t4.creditRating, t4.filledOrderCount, t4.firstName, t4.lastName,
t4.name, t4.status, " + 
         "t3.delivered, t3.name, t3.orderTs, t3.quantity, t3.totalCost " + 
         "FROM CR_ODR t3 LEFT OUTER JOIN CR_CUST t4 ON t3.CUSTOMER_ID = t4.id " + 
@@ -188,7 +209,7 @@
         "t4.balanceOwed, t4.creditRating, t4.filledOrderCount, t4.firstName, t4.lastName,
t4.name, t4.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 t4 ON t0.CUSTOMER_ID = t4.id " + 
+        "LEFT OUTER JOIN CR_CUST t4 ON t0.CUSTOMER_ID = t4.id " +
         "LEFT OUTER JOIN CR_ADDR t5 ON t4.ADDRESS_ID = t5.id " + 
         "LEFT OUTER JOIN CompUser t6 ON t5.id = t6.ADD_ID WHERE (? < ALL (" + 
         "SELECT t3.balance FROM  CR_CUST_CR_ACCT t2, CR_ACCT t3 WHERE (t2.ACCOUNTS_ID = t3.id)
AND " + 
@@ -255,7 +276,7 @@
         "t5.balanceOwed, t5.creditRating, t5.filledOrderCount, t5.firstName, t5.lastName,
t5.name, t5.status, " + 
         "t2.delivered, t2.name, t2.orderTs, t2.quantity, t2.totalCost " + 
         "FROM CR_ODR t2 INNER JOIN CR_CUST t3 ON t2.CUSTOMER_ID = t3.id " +
-        "LEFT OUTER JOIN CR_CUST t5 ON t2.CUSTOMER_ID = t5.id " + 
+        "LEFT OUTER JOIN CR_CUST t5 ON t2.CUSTOMER_ID = t5.id " +
         "INNER JOIN CR_ADDR t4 ON t3.ADDRESS_ID = t4.id " + 
         "LEFT OUTER JOIN CR_ADDR t6 ON t5.ADDRESS_ID = t6.id " + 
         "LEFT OUTER JOIN CompUser t7 ON t6.id = t7.ADD_ID WHERE (? < ALL (" + 
@@ -265,6 +286,7 @@
         execute(jpql, expectedSQL);
     }
 
+    //mis-placed INNER JOIN
     public void testSubqueries6e() {
         String jpql = "SELECT o FROM Order o JOIN o.customer c JOIN c.address a WHERE 10000
< "
             + "ALL (SELECT u.age FROM a.user u)";
@@ -277,53 +299,46 @@
         "LEFT OUTER JOIN CR_CUST t5 ON t0.CUSTOMER_ID = t5.id " + 
         "INNER JOIN CR_ADDR t2 ON t1.ADDRESS_ID = t2.id " + 
         "LEFT OUTER JOIN CR_ADDR t6 ON t5.ADDRESS_ID = t6.id " + 
-        "INNER JOIN CompUser t3 ON t2.id = t3.ADD_ID " + 
-        "LEFT OUTER JOIN CompUser t7 ON t6.id = t7.ADD_ID " + 
+        "INNER JOIN CompUser t3 ON t2.id = t3.ADD_ID " +  // <== mis-placed
+        "LEFT OUTER JOIN CompUser t7 ON t6.id = t7.ADD_ID " +  
         "WHERE (? < ALL (SELECT t4.age FROM CompUser t4 WHERE (t3.userid = t4.userid)
) AND 1 = 1)"; 
         
         execute(jpql, expectedSQL1);
     }
     
+    //mis-placed INNER JOIN
     public void testSubqueries6f() {
         String jpql = "SELECT o FROM Order o JOIN o.customer c WHERE 10000 < "
             + "ALL (SELECT u.age FROM c.address.user u)";
         
-        String expectedSQL = "SELECT t0.id, t0.count, t5.id, t5.accountNum, t6.id, t6.city,
t6.country, t6.county, " + 
-        "t6.state, t6.street, t7.userid, t7.DTYPE, t7.age, t7.compName, t7.creditRating,
t7.name, t6.zipCode, " + 
-        "t5.balanceOwed, t5.creditRating, t5.filledOrderCount, t5.firstName, t5.lastName,
t5.name, t5.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 t5 ON t0.CUSTOMER_ID = t5.id LEFT
OUTER JOIN CR_ADDR t6 ON " + 
-        "t5.ADDRESS_ID = t6.id LEFT OUTER JOIN CompUser t7 ON t6.id = t7.ADD_ID WHERE (?
< ALL (SELECT t4.age FROM " + 
-        "CR_ADDR t2 INNER JOIN CompUser t3 ON t2.id = t3.ADD_ID, CompUser t4 WHERE (t3.userid
= t4.userid) AND " + 
-        "(t1.ADDRESS_ID = t2.id) ))";
-
-        String expectedSQL1 = "SELECT t0.id, t0.count, t5.id, t5.accountNum, t6.id, t6.city,
t6.country, t6.county, " + 
+        String expectedSQL = "SELECT t0.id, t0.count, t5.id, t5.accountNum, t6.id, t6.city,
t6.country, t6.county, " +
         "t6.state, t6.street, t7.userid, t7.DTYPE, t7.age, t7.compName, t7.creditRating,
t7.name, t6.zipCode, " + 
         "t5.balanceOwed, t5.creditRating, t5.filledOrderCount, t5.firstName, t5.lastName,
t5.name, t5.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 t5 ON t0.CUSTOMER_ID = t5.id " + 
-        "INNER JOIN CR_ADDR t2 ON t1.ADDRESS_ID = t2.id " + 
+        "INNER JOIN CR_ADDR t2 ON t1.ADDRESS_ID = t2.id " + // <== mis-placed 
         "LEFT OUTER JOIN CR_ADDR t6 ON t5.ADDRESS_ID = t6.id " + 
-        "INNER JOIN CompUser t3 ON t2.id = t3.ADD_ID " + 
+        "INNER JOIN CompUser t3 ON t2.id = t3.ADD_ID " +   // <== misplaced
         "LEFT OUTER JOIN CompUser t7 ON t6.id = t7.ADD_ID " + 
         "WHERE (? < ALL (SELECT t4.age FROM CompUser t4 WHERE (t3.userid = t4.userid)
))"; 
 
         
-        execute(jpql, expectedSQL1);
+        execute(jpql, expectedSQL);
     }
     
+    // mis-placed INNER JOIN
     public void testSubqueries6g() {
         String jpql = "SELECT o FROM Order o JOIN o.customer.address a WHERE 10000 < "
             + "ALL (SELECT u.age FROM a.user u)";
         String expectedSQL = "SELECT t0.id, t0.count, t1.id, t1.accountNum, t6.id, t6.city,
t6.country, t6.county, " + 
-        "t6.state, t6.street, t7.userid, t7.DTYPE, t7.age, t7.compName, t7.creditRating,
t7.name, t6.zipCode, " + 
+        "t6.state, t6.street, t7.userid, t7.DTYPE, t7.age, t7.compName, t7.creditRating,
t7.name, t6.zipCode, " +
         "t1.balanceOwed, t1.creditRating, t1.filledOrderCount, t1.firstName, t1.lastName,
t1.name, t1.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 " + 
         "INNER JOIN CR_ADDR t2 ON t1.ADDRESS_ID = t2.id " + 
         "LEFT OUTER JOIN CR_ADDR t6 ON t1.ADDRESS_ID = t6.id " + 
-        "INNER JOIN CompUser t4 ON t2.id = t4.ADD_ID " + 
+        "INNER JOIN CompUser t4 ON t2.id = t4.ADD_ID " +  //<== misplaced
         "LEFT OUTER JOIN CompUser t7 ON t6.id = t7.ADD_ID WHERE (? < ALL (" + 
         "SELECT t5.age FROM  CR_CUST t3, CompUser t5 WHERE (t4.userid = t5.userid) AND (t0.CUSTOMER_ID
= t3.id) ))";
         execute(jpql, expectedSQL);



Mime
View raw message