Return-Path: Delivered-To: apmail-db-derby-commits-archive@www.apache.org Received: (qmail 22955 invoked from network); 5 Feb 2008 09:30:01 -0000 Received: from hermes.apache.org (HELO mail.apache.org) (140.211.11.2) by minotaur.apache.org with SMTP; 5 Feb 2008 09:30:01 -0000 Received: (qmail 3053 invoked by uid 500); 5 Feb 2008 09:29:53 -0000 Delivered-To: apmail-db-derby-commits-archive@db.apache.org Received: (qmail 3016 invoked by uid 500); 5 Feb 2008 09:29:53 -0000 Mailing-List: contact derby-commits-help@db.apache.org; run by ezmlm Precedence: bulk list-help: list-unsubscribe: List-Post: Reply-To: "Derby Development" List-Id: Delivered-To: mailing list derby-commits@db.apache.org Received: (qmail 3005 invoked by uid 99); 5 Feb 2008 09:29:53 -0000 Received: from nike.apache.org (HELO nike.apache.org) (192.87.106.230) by apache.org (qpsmtpd/0.29) with ESMTP; Tue, 05 Feb 2008 01:29:53 -0800 X-ASF-Spam-Status: No, hits=-100.0 required=10.0 tests=ALL_TRUSTED X-Spam-Check-By: apache.org Received: from [140.211.11.3] (HELO eris.apache.org) (140.211.11.3) by apache.org (qpsmtpd/0.29) with ESMTP; Tue, 05 Feb 2008 09:29:43 +0000 Received: by eris.apache.org (Postfix, from userid 65534) id D892B1A9832; Tue, 5 Feb 2008 01:29:34 -0800 (PST) Content-Type: text/plain; charset="utf-8" MIME-Version: 1.0 Content-Transfer-Encoding: 7bit Subject: 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/ Date: Tue, 05 Feb 2008 09:29:33 -0000 To: derby-commits@db.apache.org From: dyre@apache.org X-Mailer: svnmailer-1.0.8 Message-Id: <20080205092934.D892B1A9832@eris.apache.org> X-Virus-Checked: Checked by ClamAV on apache.org 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.