db-derby-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From d..@apache.org
Subject svn commit: r958621 - in /db/derby/code/branches/10.6: ./ java/engine/org/apache/derby/impl/sql/compile/ResultColumnList.java java/testing/org/apache/derbyTesting/functionTests/tests/lang/JoinTest.java
Date Mon, 28 Jun 2010 16:21:00 GMT
Author: dag
Date: Mon Jun 28 16:20:59 2010
New Revision: 958621

URL: http://svn.apache.org/viewvc?rev=958621&view=rev
Log:
DERBY-4679 Several left outer joins causes unstable query with incorrect results

Follow-up patch derby-4679-2a, which makes the new (tn, cn) based
remapping work also for a CR to a subquery join participant being rebound
after flattening, see detailed comments in the code.  Extra test cases
are added to JoinTest#testDerby_4679.

Merged to 10.6 branch as

svn merge -c 958618 https://svn.apache.org/repos/asf/db/derby/code/trunk


Modified:
    db/derby/code/branches/10.6/   (props changed)
    db/derby/code/branches/10.6/java/engine/org/apache/derby/impl/sql/compile/ResultColumnList.java
    db/derby/code/branches/10.6/java/testing/org/apache/derbyTesting/functionTests/tests/lang/JoinTest.java

Propchange: db/derby/code/branches/10.6/
------------------------------------------------------------------------------
--- svn:mergeinfo (original)
+++ svn:mergeinfo Mon Jun 28 16:20:59 2010
@@ -1,2 +1,2 @@
-/db/derby/code/trunk:938547,938796,938959,939231,940462,940469,941627,942031,942286,942476,942480,942587,944152,946794,948045,948069,951346,952138,952237,954344,954421,954544,954748,955001,955634,956075,956234,956445,956569,956659,957260
+/db/derby/code/trunk:938547,938796,938959,939231,940462,940469,941627,942031,942286,942476,942480,942587,944152,946794,948045,948069,951346,952138,952237,954344,954421,954544,954748,955001,955634,956075,956234,956445,956569,956659,957260,958618
 /db/derby/docs/trunk:954344

Modified: db/derby/code/branches/10.6/java/engine/org/apache/derby/impl/sql/compile/ResultColumnList.java
URL: http://svn.apache.org/viewvc/db/derby/code/branches/10.6/java/engine/org/apache/derby/impl/sql/compile/ResultColumnList.java?rev=958621&r1=958620&r2=958621&view=diff
==============================================================================
--- db/derby/code/branches/10.6/java/engine/org/apache/derby/impl/sql/compile/ResultColumnList.java
(original)
+++ db/derby/code/branches/10.6/java/engine/org/apache/derby/impl/sql/compile/ResultColumnList.java
Mon Jun 28 16:20:59 2010
@@ -325,7 +325,7 @@ public class ResultColumnList extends Qu
      * {@code columnName} is used to assert that we find the right column.
      * If we found a match on (tn, cn) but columnName is wrong, return null.
      * Once we trust table numbers and column numbers to always be correct,
-     * cf. DERBY-4695, we can remove this parameter.
+     * cf. DERBY-4695, we could remove this parameter.
      *
      * @param tableNumber the table number to look for
      * @param columnNumber the column number to look for
@@ -352,30 +352,63 @@ public class ResultColumnList extends Qu
                     if (rsn instanceof FromTable) {
                         FromTable ft = (FromTable)rsn;
 
-                        if (ft.getTableNumber() == tableNumber &&
-                                rc.getColumnPosition() == columnNumber) {
+                        if (ft.getTableNumber() == tableNumber) {
+                            // We have the right table, now try to match the
+                            // column number. Looking at a join, for a base
+                            // table participant, we will find the correct
+                            // column position in the
+                            // JOIN's ColumnDescriptor. Normally, we could just
+                            // call rc.getColumnPosition, but this doesn't work
+                            // if we have a join with a subquery participant
+                            // (it would give us the virtualColumnId one level
+                            // too high up, since the column descriptor is null
+                            // in that case inside a JOIN's RC.
+                            //
+                            // If FromTable is a FromSubquery we need to look
+                            // at the JOIN RC's source column to match the
+                            // table column number. However, at that level, the
+                            // table number would be that of the underlying
+                            // SELECT (for example), rather than the
+                            // FromSubquery's, so we need to match the table
+                            // number one level above, cf the test cases in
+                            // JoinTest#testDerby_4679 which have subqueries.
+
+                            ColumnDescriptor cd = rc.getTableColumnDescriptor();
+
+                            if (SanityManager.DEBUG) {
+                                SanityManager.ASSERT(
+                                    cd != null || ft instanceof FromSubquery);
+                            }
 
-                            // Found matching (t,c) within this top
-                            // resultColumn. Now do sanity check that column
-                            // name is correct. Remove when DERBY-4695 is
-                            // fixed.
-                            if (columnName.equals(
-                                        vcn.getSourceColumn().getName())) {
-                                resultColumn.setReferenced();
-                                return resultColumn;
-                            } else {
-                                if (SanityManager.DEBUG) {
-                                    SanityManager.ASSERT(
-                                        false,
-                                        "wrong (tn,cn) for column " +
-                                        columnName +
-                                        " found: this pair points to " +
-                                        vcn.getSourceColumn().getName());
+                            if ( (cd != null && cd.getPosition() ==
+                                      columnNumber) ||
+                                 (vcn.getSourceColumn().getColumnPosition() ==
+                                     columnNumber) ) {
+
+                                // Found matching (t,c) within this top
+                                // resultColumn. Now do sanity check that column
+                                // name is correct. Remove when DERBY-4695 is
+                                // fixed.
+                                if (columnName.equals(
+                                            vcn.getSourceColumn().getName())) {
+                                    resultColumn.setReferenced();
+                                    return resultColumn;
+                                } else {
+                                    if (SanityManager.DEBUG) {
+                                        SanityManager.ASSERT(
+                                            false,
+                                            "wrong (tn,cn) for column " +
+                                            columnName +
+                                            " found: this pair points to " +
+                                            vcn.getSourceColumn().getName());
+                                    }
+                                    // Fall back on column name based lookup,
+                                    // cf. DERBY-4679. See ColumnReference#
+                                    // remapColumnReferencesToExpressions
+                                    return null;
                                 }
-                                // Fall back on column name based lookup,
-                                // cf. DERBY-4679. See ColumnReference#
-                                // remapColumnReferencesToExpressions
-                                return null;
+                            } else {
+                                rc = vcn.getSourceColumn();
                             }
                         } else {
                             rc = vcn.getSourceColumn();

Modified: db/derby/code/branches/10.6/java/testing/org/apache/derbyTesting/functionTests/tests/lang/JoinTest.java
URL: http://svn.apache.org/viewvc/db/derby/code/branches/10.6/java/testing/org/apache/derbyTesting/functionTests/tests/lang/JoinTest.java?rev=958621&r1=958620&r2=958621&view=diff
==============================================================================
--- db/derby/code/branches/10.6/java/testing/org/apache/derbyTesting/functionTests/tests/lang/JoinTest.java
(original)
+++ db/derby/code/branches/10.6/java/testing/org/apache/derbyTesting/functionTests/tests/lang/JoinTest.java
Mon Jun 28 16:20:59 2010
@@ -1890,23 +1890,112 @@ public class JoinTest extends BaseJDBCTe
                   "values ('dddd', '_5ZDlwWTeEd-Q8aOqWJPEIQ')," +
                   "       ('bbbb', '_5nN9mmTeEd-Q8aOqWJPEIQ')");
 
-        ResultSet rs =
-            s.executeQuery(
-                "select distinct t1.ITEM_ID, t1.state_id, t1.JZ_DISCRIMINATOR from " +
-                "((((((select * from ABSTRACT_INSTANCE z1 where z1.JZ_DISCRIMINATOR = 238)
t1 " +
-                "      left outer join LAB_RESOURCE_OPERATINGSYSTEM j1 on (t1.ITEM_ID = j1.JZ_PARENT_ID))
" +
-                "     left outer join ABSTRACT_INSTANCE t2 on (j1.ITEM_ID = t2.ITEM_ID))
" +
-                "    left outer join OPERATING_SYSTEM_SOFTWARE_INSTALL j2 on (t2.ITEM_ID
= j2.JZ_PARENT_ID))" +
-                "   left outer join ABSTRACT_INSTANCE t3 on (j2.ITEM_ID = t3.ITEM_ID) " +
-                "  inner join FAMILY t5 on (t2.FAMILY_ITEM_ID = t5.ITEM_ID)) " +
-                " inner join FAMILY t7 on (t1.FAMILY_ITEM_ID = t7.ITEM_ID)) " +
-                "where (t3.FAMILY_ITEM_ID IN('_5VetVWTeEd-Q8aOqWJPEIQ') and " +
-                "      (t5.ROOT_ITEM_ID = '_5ZDlwWTeEd-Q8aOqWJPEIQ') and " +
-                "      (t7.ROOT_ITEM_ID ='_5nN9mmTeEd-Q8aOqWJPEIQ') and " +
-                "      (t1.VISIBILITY = 0))");
+        ResultSet rs = s.executeQuery(
+            "select distinct t1.ITEM_ID, t1.state_id, t1.JZ_DISCRIMINATOR" +
+            "    from " +
+            "((((((select * from ABSTRACT_INSTANCE z1 " +
+            "      where z1.JZ_DISCRIMINATOR = 238) t1 " +
+            "      left outer join LAB_RESOURCE_OPERATINGSYSTEM j1 " +
+            "          on (t1.ITEM_ID = j1.JZ_PARENT_ID)) " +
+            "     left outer join ABSTRACT_INSTANCE t2" +
+            "         on (j1.ITEM_ID = t2.ITEM_ID)) " +
+            "    left outer join OPERATING_SYSTEM_SOFTWARE_INSTALL j2" +
+            "        on (t2.ITEM_ID = j2.JZ_PARENT_ID))" +
+            "   left outer join ABSTRACT_INSTANCE t3 on " +
+            "       (j2.ITEM_ID = t3.ITEM_ID) " +
+            "  inner join FAMILY t5 on (t2.FAMILY_ITEM_ID = t5.ITEM_ID)) " +
+            " inner join FAMILY t7 on (t1.FAMILY_ITEM_ID = t7.ITEM_ID)) " +
+            "where (t3.FAMILY_ITEM_ID IN('_5VetVWTeEd-Q8aOqWJPEIQ') and " +
+            "      (t5.ROOT_ITEM_ID = '_5ZDlwWTeEd-Q8aOqWJPEIQ') and " +
+            "      (t7.ROOT_ITEM_ID ='_5nN9mmTeEd-Q8aOqWJPEIQ') and " +
+            "      (t1.VISIBILITY = 0))");
+
         JDBC.assertFullResultSet(
             rs,
             new String[][]{{"aaaa", null, "238"}});
+
+        // Now, some subqueries instead of a base table t3, since our
+        // difficulty lay in binding t3.FAMILY_ITEM_ID in the where clause
+        // correctly. Subqueries still broke in the first patch for DERBY-4679.
+
+        // Select subquery variant, cf tCorr
+        rs = s.executeQuery(
+            "select distinct t1.ITEM_ID, t1.state_id, t1.JZ_DISCRIMINATOR " +
+            "    from " +
+            "((((((select * from ABSTRACT_INSTANCE z1 " +
+            "      where z1.JZ_DISCRIMINATOR = 238) t1 " +
+            "      left outer join LAB_RESOURCE_OPERATINGSYSTEM j1 " +
+            "          on (t1.ITEM_ID = j1.JZ_PARENT_ID)) " +
+            "     left outer join ABSTRACT_INSTANCE t2 " +
+            "         on (j1.ITEM_ID = t2.ITEM_ID)) " +
+            "    left outer join OPERATING_SYSTEM_SOFTWARE_INSTALL j2" +
+            "        on (t2.ITEM_ID = j2.JZ_PARENT_ID))" +
+            "   left outer join (select * from ABSTRACT_INSTANCE) tCorr " +
+            "       on (j2.ITEM_ID = tCorr.ITEM_ID) " +
+            "  inner join FAMILY t5 on (t2.FAMILY_ITEM_ID = t5.ITEM_ID)) " +
+            " inner join FAMILY t7 on (t1.FAMILY_ITEM_ID = t7.ITEM_ID)) " +
+            "where (tCorr.FAMILY_ITEM_ID IN('_5VetVWTeEd-Q8aOqWJPEIQ') and " +
+            "      (t5.ROOT_ITEM_ID = '_5ZDlwWTeEd-Q8aOqWJPEIQ') and " +
+            "      (t7.ROOT_ITEM_ID ='_5nN9mmTeEd-Q8aOqWJPEIQ') and " +
+            "      (t1.VISIBILITY = 0))");
+        JDBC.assertFullResultSet(
+            rs,
+            new String[][]{{"aaaa", null, "238"}});
+
+        // values subquery variant, cf tCorr
+        rs = s.executeQuery(
+            "select distinct t1.ITEM_ID, t1.state_id, t1.JZ_DISCRIMINATOR " +
+            "    from " +
+            "((((((select * from ABSTRACT_INSTANCE z1 " +
+            "      where z1.JZ_DISCRIMINATOR = 238) t1 " +
+            "      left outer join LAB_RESOURCE_OPERATINGSYSTEM j1 " +
+            "          on (t1.ITEM_ID = j1.JZ_PARENT_ID)) " +
+            "     left outer join ABSTRACT_INSTANCE t2 " +
+            "         on (j1.ITEM_ID = t2.ITEM_ID)) " +
+            "    left outer join OPERATING_SYSTEM_SOFTWARE_INSTALL j2 " +
+            "        on (t2.ITEM_ID = j2.JZ_PARENT_ID))" +
+            "   left outer join " +
+            "       (values (238, 'aaaa', 'bbbb', 0)," +
+            "       (0, 'cccc', 'dddd', 0)," +
+            "       (1, 'eeee', '_5VetVWTeEd-Q8aOqWJPEIQ', 0)) " +
+            "       tCorr(jz_discriminator,item_id,family_item_id,visibility)" +
+            "       on (j2.ITEM_ID = tCorr.ITEM_ID) " +
+            "  inner join FAMILY t5 on (t2.FAMILY_ITEM_ID = t5.ITEM_ID)) " +
+            " inner join FAMILY t7 on (t1.FAMILY_ITEM_ID = t7.ITEM_ID)) " +
+            "where (tCorr.FAMILY_ITEM_ID IN('_5VetVWTeEd-Q8aOqWJPEIQ') and " +
+            "      (t5.ROOT_ITEM_ID = '_5ZDlwWTeEd-Q8aOqWJPEIQ') and " +
+            "      (t7.ROOT_ITEM_ID ='_5nN9mmTeEd-Q8aOqWJPEIQ') and " +
+            "      (t1.VISIBILITY = 0))");
+        JDBC.assertFullResultSet(
+            rs,
+            new String[][]{{"aaaa", null, "238"}});
+
+
+        s.executeUpdate("create view tView as select * from ABSTRACT_INSTANCE");
+
+        // view subquery variant, cf tCorr
+        rs = s.executeQuery(
+            "select distinct t1.ITEM_ID, t1.state_id, t1.JZ_DISCRIMINATOR " +
+            "    from " +
+            "((((((select * from ABSTRACT_INSTANCE z1 " +
+            "      where z1.JZ_DISCRIMINATOR = 238) t1 " +
+            "      left outer join LAB_RESOURCE_OPERATINGSYSTEM j1 " +
+            "          on (t1.ITEM_ID = j1.JZ_PARENT_ID)) " +
+            "     left outer join ABSTRACT_INSTANCE t2 " +
+            "         on (j1.ITEM_ID = t2.ITEM_ID)) " +
+            "    left outer join OPERATING_SYSTEM_SOFTWARE_INSTALL j2 " +
+            "        on (t2.ITEM_ID = j2.JZ_PARENT_ID))" +
+            "   left outer join tView on (j2.ITEM_ID = tView.ITEM_ID) " +
+            "  inner join FAMILY t5 on (t2.FAMILY_ITEM_ID = t5.ITEM_ID)) " +
+            " inner join FAMILY t7 on (t1.FAMILY_ITEM_ID = t7.ITEM_ID)) " +
+            "where (tView.FAMILY_ITEM_ID IN('_5VetVWTeEd-Q8aOqWJPEIQ') and " +
+            "      (t5.ROOT_ITEM_ID = '_5ZDlwWTeEd-Q8aOqWJPEIQ') and " +
+            "      (t7.ROOT_ITEM_ID ='_5nN9mmTeEd-Q8aOqWJPEIQ') and " +
+            "      (t1.VISIBILITY = 0))");
+        JDBC.assertFullResultSet(
+            rs,
+            new String[][]{{"aaaa", null, "238"}});
+
         rollback();
     }
 



Mime
View raw message