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 dy...@apache.org on 2008/02/05 10:29:33 UTC
svn commit: r618586 - in /db/derby/code/trunk/java:
engine/org/apache/derby/impl/sql/compile/
testing/org/apache/derbyTesting/functionTests/master/
testing/org/apache/derbyTesting/functionTests/tests/lang/
Author: dyre
Date: Tue Feb 5 01:29:32 2008
New Revision: 618586
URL: http://svn.apache.org/viewvc?rev=618586&view=rev
Log:
DERBY-3301: Incorrect result from query with nested EXIST
Prevent the optimizer from flattening subqueries that
need to be evaluated to get correct results.
Patch contributed by Thomas Nielsen
Patch files: derby-3301-8.diff, derby-3301-test-master-2.diff,
derby-3301-test-3.diff
Added:
db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/NestedWhereSubqueryTest.java (with props)
Modified:
db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/SelectNode.java
db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/SubqueryList.java
db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/SubqueryNode.java
db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/subqueryFlattening.out
db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/_Suite.java
Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/SelectNode.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/SelectNode.java?rev=618586&r1=618585&r2=618586&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/SelectNode.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/SelectNode.java Tue Feb 5 01:29:32 2008
@@ -112,6 +112,8 @@
private boolean orderByAndDistinctMerged;
+ boolean originalWhereClauseHadSubqueries;
+
/* Copy of fromList prior to generating join tree */
private FromList preJoinFL;
@@ -138,6 +140,16 @@
this.groupByList = (GroupByList) groupByList;
this.havingClause = (ValueNode)havingClause;
bindTargetListOnly = false;
+
+ this.originalWhereClauseHadSubqueries = false;
+ if (this.whereClause != null){
+ CollectNodesVisitor cnv =
+ new CollectNodesVisitor(SubqueryNode.class, SubqueryNode.class);
+ this.whereClause.accept(cnv);
+ if (!cnv.getList().isEmpty()){
+ this.originalWhereClauseHadSubqueries = true;
+ }
+ }
}
/**
@@ -456,7 +468,7 @@
whereClause = whereClause.bindExpression(fromListParam,
whereSubquerys,
whereAggregates);
-
+
/* RESOLVE - Temporarily disable aggregates in the HAVING clause.
** (We may remove them in the parser anyway.)
** RESOLVE - Disable aggregates in the WHERE clause. Someday
@@ -868,6 +880,13 @@
*/
if (whereClause != null)
{
+ // DERBY-3301
+ // Mark subqueries that are part of the where clause as such so
+ // that we can avoid flattening later, particularly for nested
+ // WHERE EXISTS subqueries.
+ if (whereSubquerys != null){
+ whereSubquerys.markWhereSubqueries();
+ }
whereClause.preprocess(numTables,
fromList, whereSubquerys,
wherePredicates);
Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/SubqueryList.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/SubqueryList.java?rev=618586&r1=618585&r2=618586&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/SubqueryList.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/SubqueryList.java Tue Feb 5 01:29:32 2008
@@ -255,5 +255,20 @@
subqueryNode.setHavingSubquery(true);
}
}
+
+ /**
+ * Mark all of the subqueries in this list as being part of a where clause
+ * so we can avoid flattening later if needed.
+ */
+ public void markWhereSubqueries() {
+ int size = size();
+ for (int index = 0; index < size; index++)
+ {
+ SubqueryNode subqueryNode;
+
+ subqueryNode = (SubqueryNode) elementAt(index);
+ subqueryNode.setWhereSubquery(true);
+ }
+ }
}
Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/SubqueryNode.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/SubqueryNode.java?rev=618586&r1=618585&r2=618586&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/SubqueryNode.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/SubqueryNode.java Tue Feb 5 01:29:32 2008
@@ -44,6 +44,7 @@
import java.lang.reflect.Modifier;
+import java.util.Iterator;
import org.apache.derby.impl.sql.compile.ExpressionClassBuilder;
import org.apache.derby.impl.sql.compile.ActivationClassBuilder;
import org.apache.derby.impl.sql.execute.OnceResultSet;
@@ -110,6 +111,9 @@
/* Whether or not this subquery began life as a distinct expression subquery */
boolean distinctExpression;
+ /* Whether or not this subquery began life as a subquery in a where clause */
+ boolean whereSubquery;
+
/* Since we do not have separate subquery operator nodes, the
* type of the subquery is stored in the subqueryType field. Most subquery
* types take a left operand (except for expression and exists). We could
@@ -614,9 +618,14 @@
* o It is not a subquery in a having clause (DERBY-3257)
* o It is an expression subquery on the right side
* of a BinaryComparisonOperatorNode.
+ * o Either a) it does not appear within a WHERE clause, or
+ * b) it appears within a WHERE clause but does not itself
+ * contain a WHERE clause with other subqueries in it.
+ * (DERBY-3301)
*/
flattenable = (resultSet instanceof RowResultSetNode) &&
underTopAndNode && !havingSubquery &&
+ !isWhereExistsAnyInWithWhereSubquery() &&
parentComparisonOperator instanceof BinaryComparisonOperatorNode;
if (flattenable)
{
@@ -677,11 +686,16 @@
*
* OR,
* o The subquery is NOT EXISTS, NOT IN, ALL (beetle 5173).
+ * o Either a) it does not appear within a WHERE clause, or
+ * b) it appears within a WHERE clause but does not itself
+ * contain a WHERE clause with other subqueries in it.
+ * (DERBY-3301)
*/
boolean flattenableNotExists = (isNOT_EXISTS() || canAllBeFlattened());
flattenable = (resultSet instanceof SelectNode) &&
underTopAndNode && !havingSubquery &&
+ !isWhereExistsAnyInWithWhereSubquery() &&
(isIN() || isANY() || isEXISTS() || flattenableNotExists ||
parentComparisonOperator != null);
@@ -2310,4 +2324,67 @@
public void setHavingSubquery(boolean havingSubquery) {
this.havingSubquery = havingSubquery;
}
+
+
+ /**
+ * Is this subquery part of a whereclause?
+ *
+ * @return true if it is part of a where clause, otherwise false
+ */
+ public boolean isWhereSubquery() {
+ return whereSubquery;
+ }
+
+ /**
+ * Mark this subquery as being part of a where clause.
+ * @param whereSubquery
+ */
+ public void setWhereSubquery(boolean whereSubquery) {
+ this.whereSubquery = whereSubquery;
+ }
+
+ /**
+ * Check whether this is a WHERE EXISTS | ANY | IN subquery with a subquery
+ * in its own WHERE clause. Used in flattening decision making.
+ *
+ * DERBY-3301 reported wrong results from a nested WHERE EXISTS, but
+ * according to the derby optimizer docs this applies to a broader range of
+ * WHERE clauses in a WHERE EXISTS subquery. No WHERE EXISTS subquery with
+ * anohter subquery in it own WHERE clause can be flattened.
+ *
+ * @return true if this subquery is a WHERE EXISTS | ANY | IN subquery with
+ * a subquery in its own WHERE clause
+ */
+ public boolean isWhereExistsAnyInWithWhereSubquery()
+ throws StandardException
+ {
+ if ( isWhereSubquery() && (isEXISTS() || isANY() || isIN()) ) {
+ if (resultSet instanceof SelectNode){
+ SelectNode sn = (SelectNode) resultSet;
+ /*
+ * Flattening happens in lower QueryTree nodes first and then
+ * removes nodes from the whereSubquerys list or whereClause.
+ * Hence we check the original WHERE clause for subqueries in
+ * SelectNode.init(), and simply check here.
+ */
+ if (sn.originalWhereClauseHadSubqueries){
+ /*
+ * This is a WHERE EXISTS | ANY |IN subquery with a subquery
+ * in its own WHERE clause (or now in whereSubquerys).
+ */
+ return true;
+ }
+ }
+ /*
+ * This is a WHERE EXISTS | ANY | IN subquery, but does not contain
+ * a subquery in its WHERE subquerylist or clause
+ */
+ return false;
+ } else {
+ /*
+ * This isn't a WHERE EXISTS | ANY | IN subquery
+ */
+ return false;
+ }
+ }
}
Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/subqueryFlattening.out
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/subqueryFlattening.out?rev=618586&r1=618585&r2=618586&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/subqueryFlattening.out (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/subqueryFlattening.out Tue Feb 5 01:29:32 2008
@@ -1207,123 +1207,129 @@
Begin Execution Timestamp : null
End Execution Timestamp : null
Statement Execution Plan Text:
-Project-Restrict ResultSet (6):
-Number of opens = 1
-Rows seen = 1
-Rows filtered = 0
-restriction = false
-projection = true
- constructor time (milliseconds) = 0
- open time (milliseconds) = 0
- next time (milliseconds) = 0
- close time (milliseconds) = 0
- restriction time (milliseconds) = 0
- projection time (milliseconds) = 0
-Source result set:
- Nested Loop Exists Join ResultSet:
- Number of opens = 1
- Rows seen from the left = 1
- Rows seen from the right = 1
- Rows filtered = 0
- Rows returned = 1
+Attached subqueries:
+ Begin Subquery Number 0
+ Any ResultSet (Attached to 2):
+ Number of opens = 2
+ Rows seen = 2
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
- Left result set:
- Nested Loop Join ResultSet:
- Number of opens = 1
- Rows seen from the left = 1
- Rows seen from the right = 1
+ Source result set:
+ Project-Restrict ResultSet (6):
+ Number of opens = 2
+ Rows seen = 1
Rows filtered = 0
- Rows returned = 1
+ restriction = false
+ projection = true
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
- Left result set:
- Index Scan ResultSet for IDX2 using index IDX2_1 at serializable isolation level using share table locking chosen by the optimizer
- Number of opens = 1
- Rows seen = 1
- Rows filtered = 0
- Fetch Size = 16
- constructor time (milliseconds) = 0
- open time (milliseconds) = 0
- next time (milliseconds) = 0
- close time (milliseconds) = 0
- next time in milliseconds/row = 0
- scan information:
- Bit set of columns fetched={0, 1}
- Number of columns fetched=2
- Number of deleted rows visited=0
- Number of pages visited=1
- Number of rows qualified=1
- Number of rows visited=2
- Scan type=btree
- Tree height=1
- start position:
- None
- stop position:
- None
- qualifiers:
-Column[0][0] Id: 1
-Operator: =
-Ordered nulls: false
-Unknown return value: false
-Negate comparison result: false
- Right result set:
- Table Scan ResultSet for OUTER1 at serializable isolation level using share table locking chosen by the optimizer
- Number of opens = 1
- Rows seen = 1
+ restriction time (milliseconds) = 0
+ projection time (milliseconds) = 0
+ Source result set:
+ Nested Loop Exists Join ResultSet:
+ Number of opens = 2
+ Rows seen from the left = 1
+ Rows seen from the right = 1
Rows filtered = 0
- Fetch Size = 16
+ Rows returned = 1
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
- next time in milliseconds/row = 0
- scan information:
- Bit set of columns fetched=All
- Number of columns fetched=3
- Number of pages visited=1
- Number of rows qualified=1
- Number of rows visited=2
- Scan type=heap
- start position:
-null stop position:
-null qualifiers:
-Column[0][0] Id: 0
-Operator: =
-Ordered nulls: false
-Unknown return value: false
-Negate comparison result: false
- Right result set:
- Index Scan ResultSet for IDX1 using index IDX1_1 at serializable isolation level using share row locking chosen by the optimizer
- Number of opens = 1
- Rows seen = 1
- Rows filtered = 0
- Fetch Size = 1
- constructor time (milliseconds) = 0
- open time (milliseconds) = 0
- next time (milliseconds) = 0
- close time (milliseconds) = 0
- next time in milliseconds/row = 0
- scan information:
- Bit set of columns fetched={0}
- Number of columns fetched=1
- Number of deleted rows visited=0
- Number of pages visited=1
- Number of rows qualified=1
- Number of rows visited=1
- Scan type=btree
- Tree height=1
- start position:
+ Left result set:
+ Index Scan ResultSet for IDX2 using index IDX2_1 at serializable isolation level using share row locking chosen by the optimizer
+ Number of opens = 2
+ Rows seen = 1
+ Rows filtered = 0
+ Fetch Size = 1
+ constructor time (milliseconds) = 0
+ open time (milliseconds) = 0
+ next time (milliseconds) = 0
+ close time (milliseconds) = 0
+ next time in milliseconds/row = 0
+ scan information:
+ Bit set of columns fetched={0, 1}
+ Number of columns fetched=2
+ Number of deleted rows visited=0
+ Number of pages visited=2
+ Number of rows qualified=1
+ Number of rows visited=1
+ Scan type=btree
+ Tree height=1
+ start position:
+ >= on first 2 column(s).
+ Ordered null semantics on the following columns:
+ stop position:
+ > on first 2 column(s).
+ Ordered null semantics on the following columns:
+ qualifiers:
+None
+ Right result set:
+ Index Scan ResultSet for IDX1 using index IDX1_1 at serializable isolation level using share row locking chosen by the optimizer
+ Number of opens = 1
+ Rows seen = 1
+ Rows filtered = 0
+ Fetch Size = 1
+ constructor time (milliseconds) = 0
+ open time (milliseconds) = 0
+ next time (milliseconds) = 0
+ close time (milliseconds) = 0
+ next time in milliseconds/row = 0
+ scan information:
+ Bit set of columns fetched={0}
+ Number of columns fetched=1
+ Number of deleted rows visited=0
+ Number of pages visited=1
+ Number of rows qualified=1
+ Number of rows visited=1
+ Scan type=btree
+ Tree height=1
+ start position:
>= on first 1 column(s).
Ordered null semantics on the following columns:
- stop position:
+ stop position:
> on first 1 column(s).
Ordered null semantics on the following columns:
- qualifiers:
+ qualifiers:
+None
+ End Subquery Number 0
+Project-Restrict ResultSet (2):
+Number of opens = 1
+Rows seen = 2
+Rows filtered = 1
+restriction = true
+projection = false
+ constructor time (milliseconds) = 0
+ open time (milliseconds) = 0
+ next time (milliseconds) = 0
+ close time (milliseconds) = 0
+ restriction time (milliseconds) = 0
+ projection time (milliseconds) = 0
+Source result set:
+ Table Scan ResultSet for OUTER1 at serializable isolation level using share table locking chosen by the optimizer
+ Number of opens = 1
+ Rows seen = 2
+ Rows filtered = 0
+ Fetch Size = 16
+ constructor time (milliseconds) = 0
+ open time (milliseconds) = 0
+ next time (milliseconds) = 0
+ close time (milliseconds) = 0
+ next time in milliseconds/row = 0
+ scan information:
+ Bit set of columns fetched=All
+ Number of columns fetched=3
+ Number of pages visited=1
+ Number of rows qualified=2
+ Number of rows visited=2
+ Scan type=heap
+ start position:
+null stop position:
+null qualifiers:
None
ij> -- only flatten bottom
select * from outer1 o where exists
@@ -1516,121 +1522,127 @@
Begin Execution Timestamp : null
End Execution Timestamp : null
Statement Execution Plan Text:
-Project-Restrict ResultSet (6):
-Number of opens = 1
-Rows seen = 1
-Rows filtered = 0
-restriction = false
-projection = true
- constructor time (milliseconds) = 0
- open time (milliseconds) = 0
- next time (milliseconds) = 0
- close time (milliseconds) = 0
- restriction time (milliseconds) = 0
- projection time (milliseconds) = 0
-Source result set:
- Nested Loop Exists Join ResultSet:
- Number of opens = 1
- Rows seen from the left = 1
- Rows seen from the right = 1
- Rows filtered = 0
- Rows returned = 1
+Attached subqueries:
+ Begin Subquery Number 0
+ Any ResultSet (Attached to 2):
+ Number of opens = 2
+ Rows seen = 2
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
- Left result set:
- Nested Loop Join ResultSet:
- Number of opens = 1
- Rows seen from the left = 1
- Rows seen from the right = 1
+ Source result set:
+ Project-Restrict ResultSet (6):
+ Number of opens = 2
+ Rows seen = 1
Rows filtered = 0
- Rows returned = 1
+ restriction = false
+ projection = true
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
- Left result set:
- Index Scan ResultSet for IDX2 using index IDX2_1 at serializable isolation level using share table locking chosen by the optimizer
- Number of opens = 1
- Rows seen = 1
- Rows filtered = 0
- Fetch Size = 16
- constructor time (milliseconds) = 0
- open time (milliseconds) = 0
- next time (milliseconds) = 0
- close time (milliseconds) = 0
- next time in milliseconds/row = 0
- scan information:
- Bit set of columns fetched={0, 1}
- Number of columns fetched=2
- Number of deleted rows visited=0
- Number of pages visited=1
- Number of rows qualified=1
- Number of rows visited=2
- Scan type=btree
- Tree height=1
- start position:
- None
- stop position:
- None
- qualifiers:
-Column[0][0] Id: 1
-Operator: =
-Ordered nulls: false
-Unknown return value: false
-Negate comparison result: false
- Right result set:
- Table Scan ResultSet for OUTER1 at serializable isolation level using share table locking chosen by the optimizer
- Number of opens = 1
- Rows seen = 1
+ restriction time (milliseconds) = 0
+ projection time (milliseconds) = 0
+ Source result set:
+ Nested Loop Exists Join ResultSet:
+ Number of opens = 2
+ Rows seen from the left = 1
+ Rows seen from the right = 1
Rows filtered = 0
- Fetch Size = 16
+ Rows returned = 1
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
- next time in milliseconds/row = 0
- scan information:
- Bit set of columns fetched=All
- Number of columns fetched=3
- Number of pages visited=1
- Number of rows qualified=1
- Number of rows visited=2
- Scan type=heap
- start position:
-null stop position:
-null qualifiers:
-Column[0][0] Id: 0
-Operator: =
-Ordered nulls: false
-Unknown return value: false
-Negate comparison result: false
- Right result set:
- Index Scan ResultSet for IDX1 using index IDX1_1 at serializable isolation level using share table locking chosen by the optimizer
- Number of opens = 1
- Rows seen = 1
- Rows filtered = 0
- Fetch Size = 1
- constructor time (milliseconds) = 0
- open time (milliseconds) = 0
- next time (milliseconds) = 0
- close time (milliseconds) = 0
- next time in milliseconds/row = 0
- scan information:
- Bit set of columns fetched={}
- Number of columns fetched=0
- Number of deleted rows visited=0
- Number of pages visited=1
- Number of rows qualified=1
- Number of rows visited=1
- Scan type=btree
- Tree height=1
- start position:
+ Left result set:
+ Index Scan ResultSet for IDX2 using index IDX2_1 at serializable isolation level using share row locking chosen by the optimizer
+ Number of opens = 2
+ Rows seen = 1
+ Rows filtered = 0
+ Fetch Size = 1
+ constructor time (milliseconds) = 0
+ open time (milliseconds) = 0
+ next time (milliseconds) = 0
+ close time (milliseconds) = 0
+ next time in milliseconds/row = 0
+ scan information:
+ Bit set of columns fetched={0, 1}
+ Number of columns fetched=2
+ Number of deleted rows visited=0
+ Number of pages visited=2
+ Number of rows qualified=1
+ Number of rows visited=1
+ Scan type=btree
+ Tree height=1
+ start position:
+ >= on first 2 column(s).
+ Ordered null semantics on the following columns:
+ stop position:
+ > on first 2 column(s).
+ Ordered null semantics on the following columns:
+ qualifiers:
+None
+ Right result set:
+ Index Scan ResultSet for IDX1 using index IDX1_1 at serializable isolation level using share table locking chosen by the optimizer
+ Number of opens = 1
+ Rows seen = 1
+ Rows filtered = 0
+ Fetch Size = 1
+ constructor time (milliseconds) = 0
+ open time (milliseconds) = 0
+ next time (milliseconds) = 0
+ close time (milliseconds) = 0
+ next time in milliseconds/row = 0
+ scan information:
+ Bit set of columns fetched={}
+ Number of columns fetched=0
+ Number of deleted rows visited=0
+ Number of pages visited=1
+ Number of rows qualified=1
+ Number of rows visited=1
+ Scan type=btree
+ Tree height=1
+ start position:
None
- stop position:
+ stop position:
None
- qualifiers:
+ qualifiers:
+None
+ End Subquery Number 0
+Project-Restrict ResultSet (2):
+Number of opens = 1
+Rows seen = 2
+Rows filtered = 1
+restriction = true
+projection = false
+ constructor time (milliseconds) = 0
+ open time (milliseconds) = 0
+ next time (milliseconds) = 0
+ close time (milliseconds) = 0
+ restriction time (milliseconds) = 0
+ projection time (milliseconds) = 0
+Source result set:
+ Table Scan ResultSet for OUTER1 at serializable isolation level using share table locking chosen by the optimizer
+ Number of opens = 1
+ Rows seen = 2
+ Rows filtered = 0
+ Fetch Size = 16
+ constructor time (milliseconds) = 0
+ open time (milliseconds) = 0
+ next time (milliseconds) = 0
+ close time (milliseconds) = 0
+ next time in milliseconds/row = 0
+ scan information:
+ Bit set of columns fetched=All
+ Number of columns fetched=3
+ Number of pages visited=1
+ Number of rows qualified=2
+ Number of rows visited=2
+ Scan type=heap
+ start position:
+null stop position:
+null qualifiers:
None
ij> -- flatten a subquery that has a subquery in its select list
-- verify that subquery gets copied up to outer block
Added: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/NestedWhereSubqueryTest.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/NestedWhereSubqueryTest.java?rev=618586&view=auto
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/NestedWhereSubqueryTest.java (added)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/NestedWhereSubqueryTest.java Tue Feb 5 01:29:32 2008
@@ -0,0 +1,208 @@
+/**
+ * Derby - Class org.apache.derbyTesting.functionTests.tests.lang.NestedWhereSubqueryTest
+ *
+ * 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.derbyTesting.functionTests.tests.lang;
+
+import java.sql.ResultSet;
+import java.sql.SQLException;
+import java.sql.Statement;
+
+import junit.framework.Test;
+
+import org.apache.derbyTesting.junit.BaseJDBCTestCase;
+import org.apache.derbyTesting.junit.JDBC;
+import org.apache.derbyTesting.junit.TestConfiguration;
+
+/**
+ * Nested WHERE subquery tests. Tests nested WHERE EXISTS | ANY | IN functionality.
+ *
+ * Please refer to DERBY-3301 for more details.
+ */
+public class NestedWhereSubqueryTest extends BaseJDBCTestCase {
+
+ public NestedWhereSubqueryTest(String name) {
+ super(name);
+ }
+
+ /**
+ * Main test body
+ *
+ * @throws SQLException
+ */
+ public void testBasicOperations()
+ throws SQLException {
+ Statement s = createStatement();
+
+ /*
+ * Create tables needed for DERBY-3301 regression test
+ */
+ StringBuffer sb = new StringBuffer();
+ sb.append("CREATE TABLE departments ( ");
+ sb.append("ID INTEGER NOT NULL, ");
+ sb.append("NAME VARCHAR(32) NOT NULL, ");
+ sb.append("COMPANYID INTEGER, ");
+ sb.append("CONSTRAINT DEPTS_PK PRIMARY KEY (ID) ");
+ sb.append(")");
+ s.executeUpdate(sb.toString());
+
+ sb = new StringBuffer();
+ sb.append("CREATE TABLE employees ( ");
+ sb.append("EMPID INTEGER NOT NULL, ");
+ sb.append("FIRSTNAME VARCHAR(32) NOT NULL, ");
+ sb.append("DEPARTMENT INTEGER, ");
+ sb.append("CONSTRAINT PERS_DEPT_FK FOREIGN KEY (DEPARTMENT) REFERENCES departments, ");
+ sb.append("CONSTRAINT EMPS_PK PRIMARY KEY (EMPID) ");
+ sb.append(")");
+ s.executeUpdate(sb.toString());
+
+ sb = new StringBuffer();
+ sb.append("CREATE TABLE projects ( ");
+ sb.append("PROJID INTEGER NOT NULL, ");
+ sb.append("NAME VARCHAR(32) NOT NULL, ");
+ sb.append("CONSTRAINT PROJS_PK PRIMARY KEY (PROJID) ");
+ sb.append(")");
+ s.executeUpdate(sb.toString());
+
+ sb = new StringBuffer();
+ sb.append("CREATE TABLE project_employees ( ");
+ sb.append("PROJID INTEGER REFERENCES projects NOT NULL, ");
+ sb.append("EMPID INTEGER REFERENCES employees NOT NULL ");
+ sb.append(")");
+ s.executeUpdate(sb.toString());
+
+ /*
+ * Fill some data into the tables
+ */
+ s.executeUpdate("INSERT INTO departments VALUES (1, 'Research', 1)");
+ s.executeUpdate("INSERT INTO departments VALUES (2, 'Marketing', 1)");
+
+ s.executeUpdate("INSERT INTO employees VALUES (11, 'Alex', 1)");
+ s.executeUpdate("INSERT INTO employees VALUES (12, 'Bill', 1)");
+ s.executeUpdate("INSERT INTO employees VALUES (13, 'Charles', 1)");
+ s.executeUpdate("INSERT INTO employees VALUES (14, 'David', 2)");
+ s.executeUpdate("INSERT INTO employees VALUES (15, 'Earl', 2)");
+
+ s.executeUpdate("INSERT INTO projects VALUES (101, 'red')");
+ s.executeUpdate("INSERT INTO projects VALUES (102, 'orange')");
+ s.executeUpdate("INSERT INTO projects VALUES (103, 'yellow')");
+
+ s.executeUpdate("INSERT INTO project_employees VALUES (102, 13)");
+ s.executeUpdate("INSERT INTO project_employees VALUES (101, 13)");
+ s.executeUpdate("INSERT INTO project_employees VALUES (102, 12)");
+ s.executeUpdate("INSERT INTO project_employees VALUES (103, 15)");
+ s.executeUpdate("INSERT INTO project_employees VALUES (103, 14)");
+ s.executeUpdate("INSERT INTO project_employees VALUES (101, 12)");
+ s.executeUpdate("INSERT INTO project_employees VALUES (101, 11)");
+
+ /*
+ * Preliminary data check
+ */
+ ResultSet rs = s.executeQuery("select * from employees");
+ String[][] expectedRows = {{"11", "Alex", "1"},
+ {"12", "Bill", "1"},
+ {"13", "Charles", "1"},
+ {"14", "David", "2"},
+ {"15", "Earl", "2"}};
+ JDBC.assertUnorderedResultSet(rs, expectedRows);
+
+ rs = s.executeQuery("select * from departments");
+ expectedRows = new String [][] {{"1", "Research", "1"},
+ {"2","Marketing","1"}};
+ JDBC.assertUnorderedResultSet(rs, expectedRows);
+
+ rs = s.executeQuery("select * from projects");
+ expectedRows = new String [][] {{"101","red"},
+ {"102","orange"},
+ {"103","yellow"}};
+ JDBC.assertUnorderedResultSet(rs, expectedRows);
+
+ rs = s.executeQuery("select * from project_employees");
+ expectedRows = new String [][] {{"102","13"},
+ {"101","13"},
+ {"102","12"},
+ {"103","15"},
+ {"103","14"},
+ {"101","12"},
+ {"101","11"}};
+ JDBC.assertUnorderedResultSet(rs, expectedRows);
+
+ /*
+ * DERBY-3301: This query should return 7 rows
+ */
+ sb = new StringBuffer();
+ sb.append("select unbound_e.empid, unbound_p.projid ");
+ sb.append("from departments this, ");
+ sb.append(" employees unbound_e, ");
+ sb.append(" projects unbound_p ");
+ sb.append("where exists ( ");
+ sb.append(" select 1 from employees this_employees_e ");
+ sb.append(" where exists ( ");
+ sb.append(" select 1 from project_employees this_employees_e_projects_p ");
+ sb.append(" where this_employees_e_projects_p.empid = this_employees_e.empid ");
+ sb.append(" and this_employees_e.department = this.id ");
+ sb.append(" and unbound_p.projid = this_employees_e_projects_p.projid ");
+ sb.append(" and unbound_e.empid = this_employees_e.empid) ");
+ sb.append(" )");
+
+ rs = s.executeQuery(sb.toString());
+ expectedRows = new String [][] {{"13", "101"},
+ {"12", "101"},
+ {"11", "101"},
+ {"13", "102"},
+ {"12", "102"},
+ {"15", "103"},
+ {"14", "103"}};
+ JDBC.assertUnorderedResultSet(rs, expectedRows);
+
+ /* A variation of the above WHERE EXISTS but using IN should return the same rows */
+ sb = new StringBuffer();
+ sb.append("select unbound_e.empid, unbound_p.projid ");
+ sb.append("from departments this, ");
+ sb.append(" employees unbound_e, ");
+ sb.append(" projects unbound_p ");
+ sb.append("where exists ( ");
+ sb.append(" select 1 from employees this_employees_e ");
+ sb.append(" where this_employees_e.empid in ( ");
+ sb.append(" select this_employees_e_projects_p.empid ");
+ sb.append(" from project_employees this_employees_e_projects_p ");
+ sb.append(" where this_employees_e_projects_p.empid = this_employees_e.empid ");
+ sb.append(" and this_employees_e.department = this.id ");
+ sb.append(" and unbound_p.projid = this_employees_e_projects_p.projid ");
+ sb.append(" and unbound_e.empid = this_employees_e.empid) ");
+ sb.append(" )");
+
+ rs = s.executeQuery(sb.toString());
+ JDBC.assertUnorderedResultSet(rs, expectedRows);
+
+ /*
+ * Clean up the tables used.
+ */
+ s.executeUpdate("drop table project_employees");
+ s.executeUpdate("drop table projects");
+ s.executeUpdate("drop table employees");
+ s.executeUpdate("drop table departments");
+
+ s.close();
+ }
+
+ public static Test suite() {
+ return TestConfiguration.defaultSuite(NestedWhereSubqueryTest.class);
+ }
+}
\ No newline at end of file
Propchange: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/NestedWhereSubqueryTest.java
------------------------------------------------------------------------------
svn:eol-style = native
Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/_Suite.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/_Suite.java?rev=618586&r1=618585&r2=618586&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/_Suite.java (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/_Suite.java Tue Feb 5 01:29:32 2008
@@ -122,6 +122,7 @@
suite.addTest(Bug4356Test.suite());
suite.addTest(SynonymTest.suite());
suite.addTest(CommentTest.suite());
+ suite.addTest(NestedWhereSubqueryTest.suite());
// Add the XML tests, which exist as a separate suite
// so that users can "run all XML tests" easily.