You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-commits@db.apache.org by da...@apache.org on 2010/06/28 18:11:18 UTC
svn commit: r958618 - in /db/derby/code/trunk/java:
engine/org/apache/derby/impl/sql/compile/ResultColumnList.java
testing/org/apache/derbyTesting/functionTests/tests/lang/JoinTest.java
Author: dag
Date: Mon Jun 28 16:11:18 2010
New Revision: 958618
URL: http://svn.apache.org/viewvc?rev=958618&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
Modified:
db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/ResultColumnList.java
db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/JoinTest.java
Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/ResultColumnList.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/ResultColumnList.java?rev=958618&r1=958617&r2=958618&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/ResultColumnList.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/ResultColumnList.java Mon Jun 28 16:11:18 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();
@@ -389,8 +422,8 @@ public class ResultColumnList extends Qu
if (cr.getTableNumber() == tableNumber &&
cr.getColumnNumber() == columnNumber) {
// Found matching (t,c) within this top resultColumn
- resultColumn.setReferenced();
- return resultColumn;
+ resultColumn.setReferenced();
+ return resultColumn;
} else {
rc = null;
}
Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/JoinTest.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/JoinTest.java?rev=958618&r1=958617&r2=958618&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/JoinTest.java (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/JoinTest.java Mon Jun 28 16:11:18 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();
}