Return-Path: Mailing-List: contact derby-commits-help@db.apache.org; run by ezmlm Delivered-To: mailing list derby-commits@db.apache.org Received: (qmail 55356 invoked by uid 500); 14 Jan 2005 01:56:20 -0000 Delivered-To: apmail-incubator-derby-cvs@incubator.apache.org Received: (qmail 55353 invoked by uid 99); 14 Jan 2005 01:56:19 -0000 X-ASF-Spam-Status: No, hits=-9.8 required=10.0 tests=ALL_TRUSTED,NO_REAL_NAME X-Spam-Check-By: apache.org Received: from minotaur.apache.org (HELO minotaur.apache.org) (209.237.227.194) by apache.org (qpsmtpd/0.28) with SMTP; Thu, 13 Jan 2005 17:56:18 -0800 Received: (qmail 4736 invoked by uid 65534); 14 Jan 2005 01:56:16 -0000 Date: 14 Jan 2005 01:56:16 -0000 Message-ID: <20050114015616.4733.qmail@minotaur.apache.org> From: djd@apache.org To: derby-cvs@incubator.apache.org Subject: svn commit: r125133 - /incubator/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/subqueryFlattening.out /incubator/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/subqueryFlattening.sql MIME-Version: 1.0 Content-Type: text/plain; charset=UTF-8 X-Virus-Checked: Checked Author: djd Date: Thu Jan 13 17:56:14 2005 New Revision: 125133 URL: http://svn.apache.org/viewcvs?view=rev&rev=125133 Log: Fix subqueryFlattening test for linux. Modified: incubator/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/subqueryFlattening.out incubator/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/subqueryFlattening.sql Modified: incubator/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/subqueryFlattening.out Url: http://svn.apache.org/viewcvs/incubator/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/subqueryFlattening.out?view=diff&rev=125133&p1=incubator/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/subqueryFlattening.out&r1=125132&p2=incubator/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/subqueryFlattening.out&r2=125133 ============================================================================== --- incubator/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/subqueryFlattening.out (original) +++ incubator/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/subqueryFlattening.out Thu Jan 13 17:56:14 2005 @@ -79,7 +79,7 @@ ij> -- subqueries that should get flattened call SYSCS_UTIL.SYSCS_SET_RUNTIMESTATISTICS(1); 0 rows inserted/updated/deleted -ij> maximumdisplaywidth 2000; +ij> maximumdisplaywidth 40000; ij> -- simple IN select * from outer1 o where o.c1 in (select c1 from idx1); C1 |C2 |C3 @@ -150,7 +150,33 @@ null qualifiers: None Right result set: - Index Scan ResultSet for IDX1 using index IDX1_1 at ser& + Index Scan ResultSet for IDX1 using index IDX1_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} + Number of columns fetched=1 + 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 1 column(s). + Ordered null semantics on the following columns: + stop position: + > on first 1 column(s). + Ordered null semantics on the following columns: + qualifiers: +None ij> -- simple EXISTS select * from outer1 o where exists (select * from idx1 i where o.c1 = i.c1); C1 |C2 |C3 @@ -221,7 +247,33 @@ null qualifiers: None Right result set: - Index Scan ResultSet for IDX1 usi& + Index Scan ResultSet for IDX1 using index IDX1_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} + Number of columns fetched=1 + 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 1 column(s). + Ordered null semantics on the following columns: + stop position: + > on first 1 column(s). + Ordered null semantics on the following columns: + qualifiers: +None ij> -- simple ANY select * from outer1 o where o.c1 = ANY (select c1 from idx1); C1 |C2 |C3 @@ -292,7 +344,33 @@ null qualifiers: None Right result set: - Index Scan ResultSet for IDX1 using index IDX1_1 at& + Index Scan ResultSet for IDX1 using index IDX1_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} + Number of columns fetched=1 + 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 1 column(s). + Ordered null semantics on the following columns: + stop position: + > on first 1 column(s). + Ordered null semantics on the following columns: + qualifiers: +None ij> -- another simple ANY select * from outer1 o where o.c2 > ANY (select c1 from idx1 i where o.c1 = i.c1); C1 |C2 |C3 @@ -363,7 +441,37 @@ null qualifiers: None Right result set: - Index Scan ResultSet fo& + Index Scan ResultSet for IDX1 using index IDX1_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} + Number of columns fetched=1 + 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 1 column(s). + Ordered null semantics on the following columns: + stop position: + > on first 1 column(s). + Ordered null semantics on the following columns: + qualifiers: +Column[0][0] Id: 0 +Operator: < +Ordered nulls: false +Unknown return value: false +Negate comparison result: false ij> -- comparisons with parameters prepare p1 as 'select * from outer1 o where exists (select * from idx1 i where i.c1 = ?)'; ij> execute p1 using 'values 1'; @@ -438,7 +546,31 @@ Ordered null semantics on the following columns: stop position: > on first 1 column(s). - Ordered null semantics on the foll& + Ordered null semantics on the following columns: + qualifiers: +None + 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 = 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> prepare p2 as 'select * from outer1 o where ? = ANY (select c1 from idx1)'; ij> execute p2 using 'values 1'; IJ WARNING: Autocommit may close using result set @@ -512,7 +644,31 @@ Ordered null semantics on the following columns: stop position: > on first 1 column(s). - Ordered null semantics on the following columns: & + Ordered null semantics on the following columns: + qualifiers: +None + 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 = 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> -- mix constants with correlation columns select * from outer1 o where exists (select * from idx2 i where o.c1 = i.c1 and i.c2 = 2); C1 |C2 |C3 @@ -587,7 +743,35 @@ qualifiers: Column[0][0] Id: 1 Operator: = -Ordered nulls: & +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 + 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=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 ij> -- multiple tables in subquery select * from outer1 o where exists (select * from idx2 i, idx1 where o.c1 = i.c1 and i.c2 = idx1.c1 and i.c2 = 1); C1 |C2 |C3 @@ -654,72 +838,147 @@ Fetch Size = 16 constructor time (milliseconds) = 0 open time (milliseconds) = 0 - next time (milliseconds) = & -ij> -- comparisons with non-join expressions -select * from outer1 o where exists (select * from idx1 where idx1.c1 = 1 + 0); -C1 |C2 |C3 ------------------------------------ -1 |2 |3 -4 |5 |6 -ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); -1 ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -Statement Name: - null -Statement Text: - -- comparisons with non-join expressions -select * from outer1 o where exists (select * from idx1 where idx1.c1 = 1 + 0) -Parse Time: 0 -Bind Time: 0 -Optimize Time: 0 -Generate Time: 0 -Compile Time: 0 -Execute Time: 0 -Begin Compilation Timestamp : null -End Compilation Timestamp : null -Begin Execution Timestamp : null -End Execution Timestamp : null -Statement Execution Plan Text: -Project-Restrict ResultSet (4): -Number of opens = 1 -Rows seen = 2 -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 Join ResultSet: - Number of opens = 1 - Rows seen from the left = 1 - Rows seen from the right = 2 - Rows filtered = 0 - Rows returned = 2 - constructor time (milliseconds) = 0 - open time (milliseconds) = 0 - next time (milliseconds) = 0 - close time (milliseconds) = 0 - Left 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 + 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 + 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=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: + >= on first 1 column(s). + Ordered null semantics on the following columns: + stop position: + > on first 1 column(s). + Ordered null semantics on the following columns: + qualifiers: +None +ij> -- comparisons with non-join expressions +select * from outer1 o where exists (select * from idx1 where idx1.c1 = 1 + 0); +C1 |C2 |C3 +----------------------------------- +1 |2 |3 +4 |5 |6 +ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); +1 +---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- +Statement Name: + null +Statement Text: + -- comparisons with non-join expressions +select * from outer1 o where exists (select * from idx1 where idx1.c1 = 1 + 0) +Parse Time: 0 +Bind Time: 0 +Optimize Time: 0 +Generate Time: 0 +Compile Time: 0 +Execute Time: 0 +Begin Compilation Timestamp : null +End Compilation Timestamp : null +Begin Execution Timestamp : null +End Execution Timestamp : null +Statement Execution Plan Text: +Project-Restrict ResultSet (4): +Number of opens = 1 +Rows seen = 2 +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 Join ResultSet: + Number of opens = 1 + Rows seen from the left = 1 + Rows seen from the right = 2 + Rows filtered = 0 + Rows returned = 2 + constructor time (milliseconds) = 0 + open time (milliseconds) = 0 + next time (milliseconds) = 0 + close time (milliseconds) = 0 + Left 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 @@ -727,7 +986,32 @@ >= on first 1 column(s). Ordered null semantics on the following columns: stop position: - > on first 1& + > on first 1 column(s). + Ordered null semantics on the following columns: + qualifiers: +None + 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 = 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> select * from outer1 o where exists (select * from idx2 i, idx1 where o.c1 + 0 = i.c1 and i.c2 + 0 = idx1.c1 and i.c2 = 1 + 0); C1 |C2 |C3 ----------------------------------- @@ -793,7 +1077,90 @@ constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 - close time (mi& + 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: + Project-Restrict ResultSet (5): + 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 + 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: + > on first 1 column(s). + Ordered null semantics on the following columns: + qualifiers: +None ij> -- multilevel subqueries -- flatten all select * from outer1 o where exists @@ -867,7 +1234,83 @@ 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 + 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=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: + >= on first 1 column(s). + Ordered null semantics on the following columns: + stop position: + > on first 1 column(s). + Ordered null semantics on the following columns: + qualifiers: +None ij> -- only flatten bottom select * from outer1 o where exists (select * from idx2 i where exists @@ -938,7 +1381,95 @@ Hash key is column number 0 Rows seen = 1 Rows filtered = 0 - constructor time (milliseconds) = 0& + 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=2 + Number of rows visited=2 + Scan type=btree + Tree height=1 + start position: + None + stop position: + None + scan qualifiers: +None + next 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: + >= on first 1 column(s). + Ordered null semantics on the following columns: + stop position: + > on first 1 column(s). + Ordered null semantics on the following columns: + 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 innermost into exists join, then flatten middle -- into outer select * from outer1 o where exists @@ -1011,7 +1542,82 @@ Rows seen = 1 Rows filtered = 0 Fetch Size = 16 - constructor time (millise& + 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 + 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=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: + None + stop position: + None + qualifiers: +None ij> -- flatten a subquery that has a subquery in its select list -- verify that subquery gets copied up to outer block select * from outer1 o where c1 in @@ -1086,7 +1692,79 @@ Rows filtered = 0 restriction = true projection = true - construct& + 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 Join ResultSet: + Number of opens = 1 + Rows seen from the left = 1 + Rows seen from the right = 1 + Rows filtered = 0 + Rows returned = 1 + 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 + 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=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 ij> -- expression subqueries -- simple = select * from outer1 o where o.c1 = (select c1 from idx1 i where o.c1 = i.c1); @@ -1159,7 +1837,37 @@ null qualifiers: None Right result set: - Index Scan & + Index Scan ResultSet for IDX1 using index IDX1_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} + Number of columns fetched=1 + 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 1 column(s). + Ordered null semantics on the following columns: + stop position: + > on first 1 column(s). + Ordered null semantics on the following columns: + qualifiers: +Column[0][0] Id: 0 +Operator: = +Ordered nulls: false +Unknown return value: false +Negate comparison result: false ij> select * from outer1 o where o.c1 <= (select c1 from idx1 i where o.c1 = i.c1); C1 |C2 |C3 ----------------------------------- @@ -1228,7 +1936,37 @@ null qualifiers: None Right result set: - Index Scan ResultSet for IDX1 using index IDX1_1 & + Index Scan ResultSet for IDX1 using index IDX1_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} + Number of columns fetched=1 + 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 1 column(s). + Ordered null semantics on the following columns: + stop position: + > on first 1 column(s). + Ordered null semantics on the following columns: + qualifiers: +Column[0][0] Id: 0 +Operator: < +Ordered nulls: false +Unknown return value: true +Negate comparison result: true ij> -- multiple tables in subquery select * from outer1 o where c1 = (select i.c1 from idx2 i, idx1 where o.c1 = i.c1 and i.c2 = idx1.c1 and i.c2 = 1); C1 |C2 |C3 @@ -1295,7 +2033,87 @@ Fetch Size = 16 constructor time (milliseconds) = 0 open time (milliseconds) = 0 - next time (milliseconds) & + 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 + 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=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 +Column[0][1] 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: + >= on first 1 column(s). + Ordered null semantics on the following columns: + stop position: + > on first 1 column(s). + Ordered null semantics on the following columns: + qualifiers: +None ij> -- flattening to an exists join -- no index on subquery table select * from outer1 where c1 in (select c1 from noidx); @@ -1368,7 +2186,34 @@ null qualifiers: None Right result set: - Hash Scan Resul& + Hash Scan ResultSet for NOIDX at serializable isolation level using share table locking: + Number of opens = 2 + Hash table size = 1 + Hash key is column number 0 + Rows seen = 1 + Rows filtered = 0 + 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=1 + Number of pages visited=1 + Number of rows qualified=2 + Number of rows visited=2 + Scan type=heap + start position: +null stop position: +null scan qualifiers: +None + next qualifiers: +Column[0][0] Id: 0 +Operator: = +Ordered nulls: false +Unknown return value: false +Negate comparison result: false ij> -- no unique index on subquery table select * from outer1 where c1 in (select c1 from nonunique_idx1); C1 |C2 |C3 @@ -1421,25 +2266,56 @@ Number of opens = 1 Rows seen = 2 Rows filtered = 0 - Fetch Size = 16 + 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 + Right result set: + Hash Scan ResultSet for NONUNIQUE_IDX1 using index NONUNIQUE_IDX1_1 at serializable isolation level using share table locking: + Number of opens = 2 + Hash table size = 1 + Hash key is column number 0 + Rows seen = 1 + Rows filtered = 0 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 + 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=2 Number of rows visited=2 - Scan type=heap + Scan type=btree + Tree height=1 start position: -null stop position: -null qualifiers: + None + stop position: + None + scan qualifiers: None - Right result set: - Hash Scan ResultSet for NONUNIQU& + next qualifiers: +Column[0][0] Id: 0 +Operator: = +Ordered nulls: false +Unknown return value: false +Negate comparison result: false ij> -- columns in subquery are not superset of unique index select * from outer1 where c1 in (select c1 from idx2); C1 |C2 |C3 @@ -1510,7 +2386,38 @@ null qualifiers: None Right result set: - Hash Scan ResultSet for& + Hash Scan ResultSet for IDX2 using index IDX2_1 at serializable isolation level using share table locking: + Number of opens = 2 + Hash table size = 1 + Hash key is column number 0 + Rows seen = 1 + Rows filtered = 0 + 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=2 + Number of rows visited=2 + Scan type=btree + Tree height=1 + start position: + None + stop position: + None + scan qualifiers: +None + next qualifiers: +Column[0][0] Id: 0 +Operator: = +Ordered nulls: false +Unknown return value: false +Negate comparison result: false ij> -- single table subquery, self join on unique column select * from outer1 where exists (select * from idx1 where c1 = c1); C1 |C2 |C3 @@ -1582,7 +2489,44 @@ null qualifiers: None Right result set: - Proje& + Project-Restrict ResultSet (4): + Number of opens = 2 + Rows seen = 2 + Rows filtered = 0 + 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: + Index Scan ResultSet for IDX1 using index IDX1_1 at serializable isolation level using share table locking chosen by the optimizer + Number of opens = 2 + Rows seen = 2 + 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=2 + Number of rows qualified=2 + Number of rows visited=2 + Scan type=btree + Tree height=1 + start position: + None + stop position: + None + qualifiers: +None ij> -- flattening values subqueries -- flatten unless contains a subquery select * from outer1 where c1 in (values 1); @@ -1700,11 +2644,90 @@ close time (milliseconds) = 0 restriction time (milliseconds) = 0 projection time (milliseconds) = 0 - optimizer estimated c& + 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={0} + Number of columns fetched=1 + 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 + End Subquery Number 1 +Project-Restrict ResultSet (14): +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 Join ResultSet: + Number of opens = 1 + Rows seen from the left = 1 + Rows seen from the right = 1 + Rows filtered = 0 + Rows returned = 1 + constructor time (milliseconds) = 0 + open time (milliseconds) = 0 + next time (milliseconds) = 0 + close time (milliseconds) = 0 + Left 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 + 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=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: + Row ResultSet: + Number of opens = 1 + Rows returned = 1 + constructor time (milliseconds) = 0 + open time (milliseconds) = 0 + next time (milliseconds) = 0 + close time (milliseconds) = 0 ij> -- beetle 4459 - problems with flattening to exist joins and then flattening to -- normal join -- non correlated exists subquery with conditional join -maximumdisplaywidth 5000; +maximumdisplaywidth 40000; ij> select o.c1 from outer1 o join outer2 o2 on (o.c1 = o2.c1) where exists (select c1 from idx1); C1 @@ -2132,7 +3155,42 @@ Number of columns fetched=1 Number of deleted rows visited=0 Number of pages visited=2 - Number of rows q& + Number of rows qualified=2 + Number of rows visited=2 + Scan type=btree + Tree height=1 + start position: + None + stop position: + None + qualifiers: +None + Right result set: + Table Scan ResultSet for OUTER2 at serializable isolation level using share table locking chosen by the optimizer + Number of opens = 2 + Rows seen = 2 + 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 pages visited=1 + Number of rows qualified=2 + Number of rows visited=4 + 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 ij> -- original reported bug create table business(businesskey int, name varchar(50), changedate int); 0 rows inserted/updated/deleted @@ -2309,7 +3367,6 @@ Ordered nulls: false Unknown return value: false Negate comparison result: false - & ij> -- clean up drop table outer1; 0 rows inserted/updated/deleted @@ -2366,7 +3423,7 @@ 6 rows inserted/updated/deleted ij> call SYSCS_UTIL.SYSCS_SET_RUNTIMESTATISTICS(1); 0 rows inserted/updated/deleted -ij> maximumdisplaywidth 10000; +ij> maximumdisplaywidth 40000; ij> -- NOT IN is flattened SELECT COUNT(*) FROM ( SELECT ID FROM DOCS WHERE @@ -5550,25 +6607,22 @@ Ordered nulls: false Unknown return value: false Negate comparison result: false -ij> -- watch out result, should return 2,3,4,2 -select * from t1 where not exists (select * from t2 where t1.c1=t2.c1 and t2.c1 -not in (select t3.c1 from t3, t4)); +ij> select * from t1 where not exists (select * from t2 where t1.c1=t2.c1 and t2.c1 not in (select t3.c1 from t3, t4)); C1 ----------- 2 3 4 2 -ij> -- can not be flattened, should be materialized +ij> -- watch out result, should return 2,3,4,2 +-- can not be flattened, should be materialized values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); 1 ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Statement Name: null Statement Text: - -- watch out result, should return 2,3,4,2 -select * from t1 where not exists (select * from t2 where t1.c1=t2.c1 and t2.c1 -not in (select t3.c1 from t3, t4)) + select * from t1 where not exists (select * from t2 where t1.c1=t2.c1 and t2.c1 not in (select t3.c1 from t3, t4)) Parse Time: 0 Bind Time: 0 Optimize Time: 0 @@ -5779,25 +6833,351 @@ Rows seen from the right = 3 Rows returned = 50 constructor time (milliseconds) = 0 - & -ij> -- should return 1,5,1 -select * from t1 where exists (select * from t2 where t1.c1=t2.c1 and t2.c1 not -in (select t3.c1 from t3, t4)); + open time (milliseconds) = 0 + next time (milliseconds) = 0 + close time (milliseconds) = 0 + Left result set: + Union ResultSet: + Number of opens = 5 + Rows seen from the left = 44 + Rows seen from the right = 3 + Rows returned = 47 + constructor time (milliseconds) = 0 + open time (milliseconds) = 0 + next time (milliseconds) = 0 + close time (milliseconds) = 0 + Left result set: + Union ResultSet: + Number of opens = 5 + Rows seen from the left = 41 + Rows seen from the right = 3 + Rows returned = 44 + constructor time (milliseconds) = 0 + open time (milliseconds) = 0 + next time (milliseconds) = 0 + close time (milliseconds) = 0 + Left result set: + Union ResultSet: + Number of opens = 5 + Rows seen from the left = 38 + Rows seen from the right = 3 + Rows returned = 41 + constructor time (milliseconds) = 0 + open time (milliseconds) = 0 + next time (milliseconds) = 0 + close time (milliseconds) = 0 + Left result set: + Union ResultSet: + Number of opens = 5 + Rows seen from the left = 35 + Rows seen from the right = 3 + Rows returned = 38 + constructor time (milliseconds) = 0 + open time (milliseconds) = 0 + next time (milliseconds) = 0 + close time (milliseconds) = 0 + Left result set: + Union ResultSet: + Number of opens = 5 + Rows seen from the left = 32 + Rows seen from the right = 3 + Rows returned = 35 + constructor time (milliseconds) = 0 + open time (milliseconds) = 0 + next time (milliseconds) = 0 + close time (milliseconds) = 0 + Left result set: + Union ResultSet: + Number of opens = 5 + Rows seen from the left = 29 + Rows seen from the right = 3 + Rows returned = 32 + constructor time (milliseconds) = 0 + open time (milliseconds) = 0 + next time (milliseconds) = 0 + close time (milliseconds) = 0 + Left result set: + Union ResultSet: + Number of opens = 5 + Rows seen from the left = 26 + Rows seen from the right = 3 + Rows returned = 29 + constructor time (milliseconds) = 0 + open time (milliseconds) = 0 + next time (milliseconds) = 0 + close time (milliseconds) = 0 + Left result set: + Union ResultSet: + Number of opens = 5 + Rows seen from the left = 23 + Rows seen from the right = 3 + Rows returned = 26 + constructor time (milliseconds) = 0 + open time (milliseconds) = 0 + next time (milliseconds) = 0 + close time (milliseconds) = 0 + Left result set: + Union ResultSet: + Number of opens = 5 + Rows seen from the left = 20 + Rows seen from the right = 3 + Rows returned = 23 + constructor time (milliseconds) = 0 + open time (milliseconds) = 0 + next time (milliseconds) = 0 + close time (milliseconds) = 0 + Left result set: + Union ResultSet: + Number of opens = 5 + Rows seen from the left = 17 + Rows seen from the right = 3 + Rows returned = 20 + constructor time (milliseconds) = 0 + open time (milliseconds) = 0 + next time (milliseconds) = 0 + close time (milliseconds) = 0 + Left result set: + Union ResultSet: + Number of opens = 5 + Rows seen from the left = 14 + Rows seen from the right = 3 + Rows returned = 17 + constructor time (milliseconds) = 0 + open time (milliseconds) = 0 + next time (milliseconds) = 0 + close time (milliseconds) = 0 + Left result set: + Union ResultSet: + Number of opens = 5 + Rows seen from the left = 11 + Rows seen from the right = 3 + Rows returned = 14 + constructor time (milliseconds) = 0 + open time (milliseconds) = 0 + next time (milliseconds) = 0 + close time (milliseconds) = 0 + Left result set: + Union ResultSet: + Number of opens = 5 + Rows seen from the left = 8 + Rows seen from the right = 3 + Rows returned = 11 + constructor time (milliseconds) = 0 + open time (milliseconds) = 0 + next time (milliseconds) = 0 + close time (milliseconds) = 0 + Left result set: + Union ResultSet: + Number of opens = 5 + Rows seen from the left = 5 + Rows seen from the right = 3 + Rows returned = 8 + constructor time (milliseconds) = 0 + open time (milliseconds) = 0 + next time (milliseconds) = 0 + close time (milliseconds) = 0 + Left result set: + Row ResultSet: + Number of opens = 5 + Rows returned = 5 + constructor time (milliseconds) = 0 + open time (milliseconds) = 0 + next time (milliseconds) = 0 + close time (milliseconds) = 0 + Right result set: + Row ResultSet: + Number of opens = 3 + Rows returned = 3 + constructor time (milliseconds) = 0 + open time (milliseconds) = 0 + next time (milliseconds) = 0 + close time (milliseconds) = 0 + Right result set: + Row ResultSet: + Number of opens = 3 + Rows returned = 3 + constructor time (milliseconds) = 0 + open time (milliseconds) = 0 + next time (milliseconds) = 0 + close time (milliseconds) = 0 + Right result set: + Row ResultSet: + Number of opens = 3 + Rows returned = 3 + constructor time (milliseconds) = 0 + open time (milliseconds) = 0 + next time (milliseconds) = 0 + close time (milliseconds) = 0 + Right result set: + Row ResultSet: + Number of opens = 3 + Rows returned = 3 + constructor time (milliseconds) = 0 + open time (milliseconds) = 0 + next time (milliseconds) = 0 + close time (milliseconds) = 0 + Right result set: + Row ResultSet: + Number of opens = 3 + Rows returned = 3 + constructor time (milliseconds) = 0 + open time (milliseconds) = 0 + next time (milliseconds) = 0 + close time (milliseconds) = 0 + Right result set: + Row ResultSet: + Number of opens = 3 + Rows returned = 3 + constructor time (milliseconds) = 0 + open time (milliseconds) = 0 + next time (milliseconds) = 0 + close time (milliseconds) = 0 + Right result set: + Row ResultSet: + Number of opens = 3 + Rows returned = 3 + constructor time (milliseconds) = 0 + open time (milliseconds) = 0 + next time (milliseconds) = 0 + close time (milliseconds) = 0 + Right result set: + Row ResultSet: + Number of opens = 3 + Rows returned = 3 + constructor time (milliseconds) = 0 + open time (milliseconds) = 0 + next time (milliseconds) = 0 + close time (milliseconds) = 0 + Right result set: + Row ResultSet: + Number of opens = 3 + Rows returned = 3 + constructor time (milliseconds) = 0 + open time (milliseconds) = 0 + next time (milliseconds) = 0 + close time (milliseconds) = 0 + Right result set: + Row ResultSet: + Number of opens = 3 + Rows returned = 3 + constructor time (milliseconds) = 0 + open time (milliseconds) = 0 + next time (milliseconds) = 0 + close time (milliseconds) = 0 + Right result set: + Row ResultSet: + Number of opens = 3 + Rows returned = 3 + constructor time (milliseconds) = 0 + open time (milliseconds) = 0 + next time (milliseconds) = 0 + close time (milliseconds) = 0 + Right result set: + Row ResultSet: + Number of opens = 3 + Rows returned = 3 + constructor time (milliseconds) = 0 + open time (milliseconds) = 0 + next time (milliseconds) = 0 + close time (milliseconds) = 0 + Right result set: + Row ResultSet: + Number of opens = 3 + Rows returned = 3 + constructor time (milliseconds) = 0 + open time (milliseconds) = 0 + next time (milliseconds) = 0 + close time (milliseconds) = 0 + Right result set: + Row ResultSet: + Number of opens = 3 + Rows returned = 3 + constructor time (milliseconds) = 0 + open time (milliseconds) = 0 + next time (milliseconds) = 0 + close time (milliseconds) = 0 + Right result set: + Row ResultSet: + Number of opens = 3 + Rows returned = 3 + constructor time (milliseconds) = 0 + open time (milliseconds) = 0 + next time (milliseconds) = 0 + close time (milliseconds) = 0 + Right result set: + Row ResultSet: + Number of opens = 3 + Rows returned = 3 + constructor time (milliseconds) = 0 + open time (milliseconds) = 0 + next time (milliseconds) = 0 + close time (milliseconds) = 0 + Right result set: + Row ResultSet: + Number of opens = 3 + Rows returned = 3 + constructor time (milliseconds) = 0 + open time (milliseconds) = 0 + next time (milliseconds) = 0 + close time (milliseconds) = 0 + Right result set: + Row ResultSet: + Number of opens = 3 + Rows returned = 3 + constructor time (milliseconds) = 0 + open time (milliseconds) = 0 + next time (milliseconds) = 0 + close time (milliseconds) = 0 + Right result set: + Row ResultSet: + Number of opens = 3 + Rows returned = 3 + constructor time (milliseconds) = 0 + open time (milliseconds) = 0 + next time (milliseconds) = 0 + close time (milliseconds) = 0 + Right result set: + Row ResultSet: + Number of opens = 3 + Rows returned = 3 + constructor time (milliseconds) = 0 + open time (milliseconds) = 0 + next time (milliseconds) = 0 + close time (milliseconds) = 0 + Right result set: + Row ResultSet: + Number of opens = 3 + Rows returned = 3 + constructor time (milliseconds) = 0 + open time (milliseconds) = 0 + next time (milliseconds) = 0 + close time (milliseconds) = 0 + Right result set: + Row ResultSet: + Number of opens = 3 + Rows returned = 3 + constructor time (milliseconds) = 0 + open time (milliseconds) = 0 + next time (milliseconds) = 0 + close time (milliseconds) = 0 + Right result set: + Row ResultSet: + +ij> select * from t1 where exists (select * from t2 where t1.c1=t2.c1 and t2.c1 not in (select t3.c1 from t3, t4)); C1 ----------- 1 5 1 -ij> -- can not be flattened, should be materialized +ij> -- should return 1,5,1 +-- can not be flattened, should be materialized values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); 1 ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Statement Name: null Statement Text: - -- should return 1,5,1 -select * from t1 where exists (select * from t2 where t1.c1=t2.c1 and t2.c1 not -in (select t3.c1 from t3, t4)) + select * from t1 where exists (select * from t2 where t1.c1=t2.c1 and t2.c1 not in (select t3.c1 from t3, t4)) Parse Time: 0 Bind Time: 0 Optimize Time: 0 @@ -6008,7 +7388,336 @@ Rows seen from the right = 3 Rows returned = 50 constructor time (milliseconds) = 0 - open time& + open time (milliseconds) = 0 + next time (milliseconds) = 0 + close time (milliseconds) = 0 + Left result set: + Union ResultSet: + Number of opens = 5 + Rows seen from the left = 44 + Rows seen from the right = 3 + Rows returned = 47 + constructor time (milliseconds) = 0 + open time (milliseconds) = 0 + next time (milliseconds) = 0 + close time (milliseconds) = 0 + Left result set: + Union ResultSet: + Number of opens = 5 + Rows seen from the left = 41 + Rows seen from the right = 3 + Rows returned = 44 + constructor time (milliseconds) = 0 + open time (milliseconds) = 0 + next time (milliseconds) = 0 + close time (milliseconds) = 0 + Left result set: + Union ResultSet: + Number of opens = 5 + Rows seen from the left = 38 + Rows seen from the right = 3 + Rows returned = 41 + constructor time (milliseconds) = 0 + open time (milliseconds) = 0 + next time (milliseconds) = 0 + close time (milliseconds) = 0 + Left result set: + Union ResultSet: + Number of opens = 5 + Rows seen from the left = 35 + Rows seen from the right = 3 + Rows returned = 38 + constructor time (milliseconds) = 0 + open time (milliseconds) = 0 + next time (milliseconds) = 0 + close time (milliseconds) = 0 + Left result set: + Union ResultSet: + Number of opens = 5 + Rows seen from the left = 32 + Rows seen from the right = 3 + Rows returned = 35 + constructor time (milliseconds) = 0 + open time (milliseconds) = 0 + next time (milliseconds) = 0 + close time (milliseconds) = 0 + Left result set: + Union ResultSet: + Number of opens = 5 + Rows seen from the left = 29 + Rows seen from the right = 3 + Rows returned = 32 + constructor time (milliseconds) = 0 + open time (milliseconds) = 0 + next time (milliseconds) = 0 + close time (milliseconds) = 0 + Left result set: + Union ResultSet: + Number of opens = 5 + Rows seen from the left = 26 + Rows seen from the right = 3 + Rows returned = 29 + constructor time (milliseconds) = 0 + open time (milliseconds) = 0 + next time (milliseconds) = 0 + close time (milliseconds) = 0 + Left result set: + Union ResultSet: + Number of opens = 5 + Rows seen from the left = 23 + Rows seen from the right = 3 + Rows returned = 26 + constructor time (milliseconds) = 0 + open time (milliseconds) = 0 + next time (milliseconds) = 0 + close time (milliseconds) = 0 + Left result set: + Union ResultSet: + Number of opens = 5 + Rows seen from the left = 20 + Rows seen from the right = 3 + Rows returned = 23 + constructor time (milliseconds) = 0 + open time (milliseconds) = 0 + next time (milliseconds) = 0 + close time (milliseconds) = 0 + Left result set: + Union ResultSet: + Number of opens = 5 + Rows seen from the left = 17 + Rows seen from the right = 3 + Rows returned = 20 + constructor time (milliseconds) = 0 + open time (milliseconds) = 0 + next time (milliseconds) = 0 + close time (milliseconds) = 0 + Left result set: + Union ResultSet: + Number of opens = 5 + Rows seen from the left = 14 + Rows seen from the right = 3 + Rows returned = 17 + constructor time (milliseconds) = 0 + open time (milliseconds) = 0 + next time (milliseconds) = 0 + close time (milliseconds) = 0 + Left result set: + Union ResultSet: + Number of opens = 5 + Rows seen from the left = 11 + Rows seen from the right = 3 + Rows returned = 14 + constructor time (milliseconds) = 0 + open time (milliseconds) = 0 + next time (milliseconds) = 0 + close time (milliseconds) = 0 + Left result set: + Union ResultSet: + Number of opens = 5 + Rows seen from the left = 8 + Rows seen from the right = 3 + Rows returned = 11 + constructor time (milliseconds) = 0 + open time (milliseconds) = 0 + next time (milliseconds) = 0 + close time (milliseconds) = 0 + Left result set: + Union ResultSet: + Number of opens = 5 + Rows seen from the left = 5 + Rows seen from the right = 3 + Rows returned = 8 + constructor time (milliseconds) = 0 + open time (milliseconds) = 0 + next time (milliseconds) = 0 + close time (milliseconds) = 0 + Left result set: + Row ResultSet: + Number of opens = 5 + Rows returned = 5 + constructor time (milliseconds) = 0 + open time (milliseconds) = 0 + next time (milliseconds) = 0 + close time (milliseconds) = 0 + Right result set: + Row ResultSet: + Number of opens = 3 + Rows returned = 3 + constructor time (milliseconds) = 0 + open time (milliseconds) = 0 + next time (milliseconds) = 0 + close time (milliseconds) = 0 + Right result set: + Row ResultSet: + Number of opens = 3 + Rows returned = 3 + constructor time (milliseconds) = 0 + open time (milliseconds) = 0 + next time (milliseconds) = 0 + close time (milliseconds) = 0 + Right result set: + Row ResultSet: + Number of opens = 3 + Rows returned = 3 + constructor time (milliseconds) = 0 + open time (milliseconds) = 0 + next time (milliseconds) = 0 + close time (milliseconds) = 0 + Right result set: + Row ResultSet: + Number of opens = 3 + Rows returned = 3 + constructor time (milliseconds) = 0 + open time (milliseconds) = 0 + next time (milliseconds) = 0 + close time (milliseconds) = 0 + Right result set: + Row ResultSet: + Number of opens = 3 + Rows returned = 3 + constructor time (milliseconds) = 0 + open time (milliseconds) = 0 + next time (milliseconds) = 0 + close time (milliseconds) = 0 + Right result set: + Row ResultSet: + Number of opens = 3 + Rows returned = 3 + constructor time (milliseconds) = 0 + open time (milliseconds) = 0 + next time (milliseconds) = 0 + close time (milliseconds) = 0 + Right result set: + Row ResultSet: + Number of opens = 3 + Rows returned = 3 + constructor time (milliseconds) = 0 + open time (milliseconds) = 0 + next time (milliseconds) = 0 + close time (milliseconds) = 0 + Right result set: + Row ResultSet: + Number of opens = 3 + Rows returned = 3 + constructor time (milliseconds) = 0 + open time (milliseconds) = 0 + next time (milliseconds) = 0 + close time (milliseconds) = 0 + Right result set: + Row ResultSet: + Number of opens = 3 + Rows returned = 3 + constructor time (milliseconds) = 0 + open time (milliseconds) = 0 + next time (milliseconds) = 0 + close time (milliseconds) = 0 + Right result set: + Row ResultSet: + Number of opens = 3 + Rows returned = 3 + constructor time (milliseconds) = 0 + open time (milliseconds) = 0 + next time (milliseconds) = 0 + close time (milliseconds) = 0 + Right result set: + Row ResultSet: + Number of opens = 3 + Rows returned = 3 + constructor time (milliseconds) = 0 + open time (milliseconds) = 0 + next time (milliseconds) = 0 + close time (milliseconds) = 0 + Right result set: + Row ResultSet: + Number of opens = 3 + Rows returned = 3 + constructor time (milliseconds) = 0 + open time (milliseconds) = 0 + next time (milliseconds) = 0 + close time (milliseconds) = 0 + Right result set: + Row ResultSet: + Number of opens = 3 + Rows returned = 3 + constructor time (milliseconds) = 0 + open time (milliseconds) = 0 + next time (milliseconds) = 0 + close time (milliseconds) = 0 + Right result set: + Row ResultSet: + Number of opens = 3 + Rows returned = 3 + constructor time (milliseconds) = 0 + open time (milliseconds) = 0 + next time (milliseconds) = 0 + close time (milliseconds) = 0 + Right result set: + Row ResultSet: + Number of opens = 3 + Rows returned = 3 + constructor time (milliseconds) = 0 + open time (milliseconds) = 0 + next time (milliseconds) = 0 + close time (milliseconds) = 0 + Right result set: + Row ResultSet: + Number of opens = 3 + Rows returned = 3 + constructor time (milliseconds) = 0 + open time (milliseconds) = 0 + next time (milliseconds) = 0 + close time (milliseconds) = 0 + Right result set: + Row ResultSet: + Number of opens = 3 + Rows returned = 3 + constructor time (milliseconds) = 0 + open time (milliseconds) = 0 + next time (milliseconds) = 0 + close time (milliseconds) = 0 + Right result set: + Row ResultSet: + Number of opens = 3 + Rows returned = 3 + constructor time (milliseconds) = 0 + open time (milliseconds) = 0 + next time (milliseconds) = 0 + close time (milliseconds) = 0 + Right result set: + Row ResultSet: + Number of opens = 3 + Rows returned = 3 + constructor time (milliseconds) = 0 + open time (milliseconds) = 0 + next time (milliseconds) = 0 + close time (milliseconds) = 0 + Right result set: + Row ResultSet: + Number of opens = 3 + Rows returned = 3 + constructor time (milliseconds) = 0 + open time (milliseconds) = 0 + next time (milliseconds) = 0 + close time (milliseconds) = 0 + Right result set: + Row ResultSet: + Number of opens = 3 + Rows returned = 3 + constructor time (milliseconds) = 0 + open time (milliseconds) = 0 + next time (milliseconds) = 0 + close time (milliseconds) = 0 + Right result set: + Row ResultSet: + Number of opens = 3 + Rows returned = 3 + constructor time (milliseconds) = 0 + open time (milliseconds) = 0 + next time (milliseconds) = 0 + close time (milliseconds) = 0 + Right result set: + Row ResultSet: + ij> drop table colls; 0 rows inserted/updated/deleted ij> drop table docs; Modified: incubator/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/subqueryFlattening.sql Url: http://svn.apache.org/viewcvs/incubator/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/subqueryFlattening.sql?view=diff&rev=125133&p1=incubator/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/subqueryFlattening.sql&r1=125132&p2=incubator/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/subqueryFlattening.sql&r2=125133 ============================================================================== --- incubator/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/subqueryFlattening.sql (original) +++ incubator/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/subqueryFlattening.sql Thu Jan 13 17:56:14 2005 @@ -49,7 +49,7 @@ -- subqueries that should get flattened call SYSCS_UTIL.SYSCS_SET_RUNTIMESTATISTICS(1); -maximumdisplaywidth 2000; +maximumdisplaywidth 40000; -- simple IN select * from outer1 o where o.c1 in (select c1 from idx1); @@ -148,7 +148,7 @@ -- beetle 4459 - problems with flattening to exist joins and then flattening to -- normal join -- non correlated exists subquery with conditional join -maximumdisplaywidth 5000; +maximumdisplaywidth 40000; select o.c1 from outer1 o join outer2 o2 on (o.c1 = o2.c1) where exists (select c1 from idx1); values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); @@ -214,7 +214,7 @@ insert into docs values '24', '25', '36', '27', '124', '567'; call SYSCS_UTIL.SYSCS_SET_RUNTIMESTATISTICS(1); -maximumdisplaywidth 10000; +maximumdisplaywidth 40000; -- NOT IN is flattened SELECT COUNT(*) FROM @@ -361,15 +361,13 @@ -- should be flattened values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); +select * from t1 where not exists (select * from t2 where t1.c1=t2.c1 and t2.c1 not in (select t3.c1 from t3, t4)); -- watch out result, should return 2,3,4,2 -select * from t1 where not exists (select * from t2 where t1.c1=t2.c1 and t2.c1 -not in (select t3.c1 from t3, t4)); -- can not be flattened, should be materialized values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); +select * from t1 where exists (select * from t2 where t1.c1=t2.c1 and t2.c1 not in (select t3.c1 from t3, t4)); -- should return 1,5,1 -select * from t1 where exists (select * from t2 where t1.c1=t2.c1 and t2.c1 not -in (select t3.c1 from t3, t4)); -- can not be flattened, should be materialized values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();