ij> -- -- 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. -- -- Test predicate pushdown into expressions in a FROM list. As of -- DERBY-805 this test only looks at pushing predicates into UNION -- operators, but this test will likely grow as additional predicate -- pushdown functionality is added to Derby. Note that "noTimeout" -- is set to true for this test because we print out a lot of query -- plans and we don't want the plans to differ from one machine -- to another (which can happen if some machines are faster than -- others when noTimeout is false). -- Create the basic tables/views for DERBY-805 testing. CREATE TABLE "APP"."T1" ("I" INTEGER, "J" INTEGER); 0 rows inserted/updated/deleted ij> insert into t1 values (1, 2), (2, 4), (3, 6), (4, 8), (5, 10); 5 rows inserted/updated/deleted ij> CREATE TABLE "APP"."T2" ("I" INTEGER, "J" INTEGER); 0 rows inserted/updated/deleted ij> insert into t2 values (1, 2), (2, -4), (3, 6), (4, -8), (5, 10); 5 rows inserted/updated/deleted ij> CREATE TABLE "APP"."T3" ("A" INTEGER, "B" INTEGER); 0 rows inserted/updated/deleted ij> insert into T3 values (1,1), (2,2), (3,3), (4,4), (6, 24), (7, 28), (8, 32), (9, 36), (10, 40); 9 rows inserted/updated/deleted ij> insert into t3 (a) values 11, 12, 13, 14, 15, 16, 17, 18, 19, 20; 10 rows inserted/updated/deleted ij> update t3 set b = 2 * a where a > 10; 10 rows inserted/updated/deleted ij> CREATE TABLE "APP"."T4" ("A" INTEGER, "B" INTEGER); 0 rows inserted/updated/deleted ij> insert into t4 values (3, 12), (4, 16); 2 rows inserted/updated/deleted ij> insert into t4 (a) values 11, 12, 13, 14, 15, 16, 17, 18, 19, 20; 10 rows inserted/updated/deleted ij> update t4 set b = 2 * a where a > 10; 10 rows inserted/updated/deleted ij> create view V1 as select i, j from T1 union select i,j from T2; 0 rows inserted/updated/deleted ij> create view V2 as select a,b from T3 union select a,b from T4; 0 rows inserted/updated/deleted ij> -- Run compression on the test tables to try to get a consistent -- set of row count stats for the tables (DERBY-1902, DERBY-3479). call SYSCS_UTIL.SYSCS_COMPRESS_TABLE('APP', 'T1', 1); 0 rows inserted/updated/deleted ij> call SYSCS_UTIL.SYSCS_COMPRESS_TABLE('APP', 'T2', 1); 0 rows inserted/updated/deleted ij> call SYSCS_UTIL.SYSCS_COMPRESS_TABLE('APP', 'T3', 1); 0 rows inserted/updated/deleted ij> call SYSCS_UTIL.SYSCS_COMPRESS_TABLE('APP', 'T4', 1); 0 rows inserted/updated/deleted ij> -- Now that we have the basic tables and views for the tests, run -- some quick queries to make sure that the optimizer will still -- consider NOT pushing the predicates and will instead do a hash -- join. The optimizer should choose do this so long as doing so is -- the best choice, which usually means that we don't have indexes -- on the tables or else we have relatively small tables. Start -- by checking the case of small (~20 row) tables. We should -- see hash joins and table scans in ALL of these cases. call SYSCS_UTIL.SYSCS_SET_RUNTIMESTATISTICS(1); 0 rows inserted/updated/deleted ij> maximumdisplaywidth 20000; ij> -- Basic cases. select * from V1, V2 where V1.j = V2.b; I |J |A |B ----------------------------------------------- 1 |2 |2 |2 2 |4 |4 |4 ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); 1 ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Statement Name: null Statement Text: -- Basic cases. select * from V1, V2 where V1.j = V2.b 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: Hash Join ResultSet: Number of opens = 1 Rows seen from the left = 7 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: Sort ResultSet: Number of opens = 1 Rows input = 10 Rows returned = 7 Eliminate duplicates = true In sorted order = false Sort information: Number of rows input=10 Number of rows output=7 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 Source result set: Union ResultSet: Number of opens = 1 Rows seen from the left = 5 Rows seen from the right = 5 Rows returned = 10 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 Left result set: Table Scan ResultSet for T1 at read committed isolation level using share row locking chosen by the optimizer Number of opens = 1 Rows seen = 5 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=All Number of columns fetched=2 Number of pages visited=2 Number of rows qualified=5 Number of rows visited=5 Scan type=heap start position: null stop position: null qualifiers: None Right result set: Table Scan ResultSet for T2 at read committed isolation level using share row locking chosen by the optimizer Number of opens = 1 Rows seen = 5 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=All Number of columns fetched=2 Number of pages visited=2 Number of rows qualified=5 Number of rows visited=5 Scan type=heap start position: null stop position: null qualifiers: None Right result set: Hash Table ResultSet (10): Number of opens = 7 Hash table size = 16 Hash key is column number 1 Rows seen = 21 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 next qualifiers: Column[0][0] Id: 1 Operator: = Ordered nulls: false Unknown return value: false Negate comparison result: false Source result set: Sort ResultSet: Number of opens = 1 Rows input = 31 Rows returned = 21 Eliminate duplicates = true In sorted order = false Sort information: Number of rows input=31 Number of rows output=21 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 Source result set: Union ResultSet: Number of opens = 1 Rows seen from the left = 19 Rows seen from the right = 12 Rows returned = 31 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 Left result set: Table Scan ResultSet for T3 at read committed isolation level using share row locking chosen by the optimizer Number of opens = 1 Rows seen = 19 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=All Number of columns fetched=2 Number of pages visited=2 Number of rows qualified=19 Number of rows visited=19 Scan type=heap start position: null stop position: null qualifiers: None Right result set: Table Scan ResultSet for T4 at read committed isolation level using share row locking chosen by the optimizer Number of opens = 1 Rows seen = 12 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=All Number of columns fetched=2 Number of pages visited=2 Number of rows qualified=12 Number of rows visited=12 Scan type=heap start position: null stop position: null qualifiers: None ij> select * from V2, V1 where V1.j = V2.b; A |B |I |J ----------------------------------------------- 2 |2 |1 |2 4 |4 |2 |4 ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); 1 ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Statement Name: null Statement Text: select * from V2, V1 where V1.j = V2.b 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 (11): 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: Hash Join ResultSet: Number of opens = 1 Rows seen from the left = 7 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: Sort ResultSet: Number of opens = 1 Rows input = 10 Rows returned = 7 Eliminate duplicates = true In sorted order = false Sort information: Number of rows input=10 Number of rows output=7 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 Source result set: Union ResultSet: Number of opens = 1 Rows seen from the left = 5 Rows seen from the right = 5 Rows returned = 10 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 Left result set: Table Scan ResultSet for T1 at read committed isolation level using share row locking chosen by the optimizer Number of opens = 1 Rows seen = 5 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=All Number of columns fetched=2 Number of pages visited=2 Number of rows qualified=5 Number of rows visited=5 Scan type=heap start position: null stop position: null qualifiers: None Right result set: Table Scan ResultSet for T2 at read committed isolation level using share row locking chosen by the optimizer Number of opens = 1 Rows seen = 5 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=All Number of columns fetched=2 Number of pages visited=2 Number of rows qualified=5 Number of rows visited=5 Scan type=heap start position: null stop position: null qualifiers: None Right result set: Hash Table ResultSet (10): Number of opens = 7 Hash table size = 16 Hash key is column number 1 Rows seen = 21 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 next qualifiers: Column[0][0] Id: 1 Operator: = Ordered nulls: false Unknown return value: false Negate comparison result: false Source result set: Sort ResultSet: Number of opens = 1 Rows input = 31 Rows returned = 21 Eliminate duplicates = true In sorted order = false Sort information: Number of rows input=31 Number of rows output=21 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 Source result set: Union ResultSet: Number of opens = 1 Rows seen from the left = 19 Rows seen from the right = 12 Rows returned = 31 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 Left result set: Table Scan ResultSet for T3 at read committed isolation level using share row locking chosen by the optimizer Number of opens = 1 Rows seen = 19 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=All Number of columns fetched=2 Number of pages visited=2 Number of rows qualified=19 Number of rows visited=19 Scan type=heap start position: null stop position: null qualifiers: None Right result set: Table Scan ResultSet for T4 at read committed isolation level using share row locking chosen by the optimizer Number of opens = 1 Rows seen = 12 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=All Number of columns fetched=2 Number of pages visited=2 Number of rows qualified=12 Number of rows visited=12 Scan type=heap start position: null stop position: null qualifiers: None ij> -- Nested unions. select * from (select * from t1 union select * from t2 union select * from t1 union select * from t2 ) x1, (select * from t3 union select * from t4 union select * from t4 ) x2 where x1.i = x2.a; I |J |A |B ----------------------------------------------- 1 |2 |1 |1 2 |-4 |2 |2 2 |4 |2 |2 3 |6 |3 |3 3 |6 |3 |12 4 |-8 |4 |4 4 |-8 |4 |16 4 |8 |4 |4 4 |8 |4 |16 ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); 1 ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Statement Name: null Statement Text: -- Nested unions. select * from (select * from t1 union select * from t2 union select * from t1 union select * from t2 ) x1, (select * from t3 union select * from t4 union select * from t4 ) x2 where x1.i = x2.a 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: Hash Join ResultSet: Number of opens = 1 Rows seen from the left = 7 Rows seen from the right = 9 Rows filtered = 0 Rows returned = 9 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 Left result set: Sort ResultSet: Number of opens = 1 Rows input = 12 Rows returned = 7 Eliminate duplicates = true In sorted order = false Sort information: Number of rows input=12 Number of rows output=7 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 Source result set: Union ResultSet: Number of opens = 1 Rows seen from the left = 7 Rows seen from the right = 5 Rows returned = 12 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 Left result set: Sort ResultSet: Number of opens = 1 Rows input = 12 Rows returned = 7 Eliminate duplicates = true In sorted order = false Sort information: Number of rows input=12 Number of rows output=7 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 Source result set: Union ResultSet: Number of opens = 1 Rows seen from the left = 7 Rows seen from the right = 5 Rows returned = 12 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 Left result set: Sort ResultSet: Number of opens = 1 Rows input = 10 Rows returned = 7 Eliminate duplicates = true In sorted order = false Sort information: Number of rows input=10 Number of rows output=7 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 Source result set: Union ResultSet: Number of opens = 1 Rows seen from the left = 5 Rows seen from the right = 5 Rows returned = 10 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 Left result set: Table Scan ResultSet for T1 at read committed isolation level using share row locking chosen by the optimizer Number of opens = 1 Rows seen = 5 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=All Number of columns fetched=2 Number of pages visited=2 Number of rows qualified=5 Number of rows visited=5 Scan type=heap start position: null stop position: null qualifiers: None Right result set: Table Scan ResultSet for T2 at read committed isolation level using share row locking chosen by the optimizer Number of opens = 1 Rows seen = 5 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=All Number of columns fetched=2 Number of pages visited=2 Number of rows qualified=5 Number of rows visited=5 Scan type=heap start position: null stop position: null qualifiers: None Right result set: Table Scan ResultSet for T1 at read committed isolation level using share row locking chosen by the optimizer Number of opens = 1 Rows seen = 5 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=All Number of columns fetched=2 Number of pages visited=2 Number of rows qualified=5 Number of rows visited=5 Scan type=heap start position: null stop position: null qualifiers: None Right result set: Table Scan ResultSet for T2 at read committed isolation level using share row locking chosen by the optimizer Number of opens = 1 Rows seen = 5 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=All Number of columns fetched=2 Number of pages visited=2 Number of rows qualified=5 Number of rows visited=5 Scan type=heap start position: null stop position: null qualifiers: None Right result set: Hash Table ResultSet (19): Number of opens = 7 Hash table size = 19 Hash key is column number 0 Rows seen = 21 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 next qualifiers: Column[0][0] Id: 0 Operator: = Ordered nulls: false Unknown return value: false Negate comparison result: false Source result set: Sort ResultSet: Number of opens = 1 Rows input = 33 Rows returned = 21 Eliminate duplicates = true In sorted order = false Sort information: Number of rows input=33 Number of rows output=21 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 Source result set: Union ResultSet: Number of opens = 1 Rows seen from the left = 21 Rows seen from the right = 12 Rows returned = 33 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 Left result set: Sort ResultSet: Number of opens = 1 Rows input = 31 Rows returned = 21 Eliminate duplicates = true In sorted order = false Sort information: Number of rows input=31 Number of rows output=21 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 Source result set: Union ResultSet: Number of opens = 1 Rows seen from the left = 19 Rows seen from the right = 12 Rows returned = 31 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 Left result set: Table Scan ResultSet for T3 at read committed isolation level using share row locking chosen by the optimizer Number of opens = 1 Rows seen = 19 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=All Number of columns fetched=2 Number of pages visited=2 Number of rows qualified=19 Number of rows visited=19 Scan type=heap start position: null stop position: null qualifiers: None Right result set: Table Scan ResultSet for T4 at read committed isolation level using share row locking chosen by the optimizer Number of opens = 1 Rows seen = 12 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=All Number of columns fetched=2 Number of pages visited=2 Number of rows qualified=12 Number of rows visited=12 Scan type=heap start position: null stop position: null qualifiers: None Right result set: Table Scan ResultSet for T4 at read committed isolation level using share row locking chosen by the optimizer Number of opens = 1 Rows seen = 12 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=All Number of columns fetched=2 Number of pages visited=2 Number of rows qualified=12 Number of rows visited=12 Scan type=heap start position: null stop position: null qualifiers: None ij> -- UNION ALLs. select * from (select * from t1 union all select * from t2) x1, (select * from t3 union select * from t4) x2 where x1.i = x2.a; I |J |A |B ----------------------------------------------- 1 |2 |1 |1 2 |4 |2 |2 3 |6 |3 |3 3 |6 |3 |12 4 |8 |4 |4 4 |8 |4 |16 1 |2 |1 |1 2 |-4 |2 |2 3 |6 |3 |3 3 |6 |3 |12 4 |-8 |4 |4 4 |-8 |4 |16 ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); 1 ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Statement Name: null Statement Text: -- UNION ALLs. select * from (select * from t1 union all select * from t2) x1, (select * from t3 union select * from t4) x2 where x1.i = x2.a 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: Hash Join ResultSet: Number of opens = 1 Rows seen from the left = 10 Rows seen from the right = 12 Rows filtered = 0 Rows returned = 12 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 Left result set: Union ResultSet: Number of opens = 1 Rows seen from the left = 5 Rows seen from the right = 5 Rows returned = 10 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 Left result set: Table Scan ResultSet for T1 at read committed isolation level using share row locking chosen by the optimizer Number of opens = 1 Rows seen = 5 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=All Number of columns fetched=2 Number of pages visited=2 Number of rows qualified=5 Number of rows visited=5 Scan type=heap start position: null stop position: null qualifiers: None Right result set: Table Scan ResultSet for T2 at read committed isolation level using share row locking chosen by the optimizer Number of opens = 1 Rows seen = 5 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=All Number of columns fetched=2 Number of pages visited=2 Number of rows qualified=5 Number of rows visited=5 Scan type=heap start position: null stop position: null qualifiers: None Right result set: Hash Table ResultSet (9): Number of opens = 10 Hash table size = 19 Hash key is column number 0 Rows seen = 21 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 next qualifiers: Column[0][0] Id: 0 Operator: = Ordered nulls: false Unknown return value: false Negate comparison result: false Source result set: Sort ResultSet: Number of opens = 1 Rows input = 31 Rows returned = 21 Eliminate duplicates = true In sorted order = false Sort information: Number of rows input=31 Number of rows output=21 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 Source result set: Union ResultSet: Number of opens = 1 Rows seen from the left = 19 Rows seen from the right = 12 Rows returned = 31 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 Left result set: Table Scan ResultSet for T3 at read committed isolation level using share row locking chosen by the optimizer Number of opens = 1 Rows seen = 19 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=All Number of columns fetched=2 Number of pages visited=2 Number of rows qualified=19 Number of rows visited=19 Scan type=heap start position: null stop position: null qualifiers: None Right result set: Table Scan ResultSet for T4 at read committed isolation level using share row locking chosen by the optimizer Number of opens = 1 Rows seen = 12 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=All Number of columns fetched=2 Number of pages visited=2 Number of rows qualified=12 Number of rows visited=12 Scan type=heap start position: null stop position: null qualifiers: None ij> select * from (select * from t1 union select * from t2) x1, (select * from t3 union all select * from t4) x2 where x1.i = x2.a; I |J |A |B ----------------------------------------------- 1 |2 |1 |1 2 |-4 |2 |2 2 |4 |2 |2 3 |6 |3 |3 3 |6 |3 |12 4 |-8 |4 |4 4 |-8 |4 |16 4 |8 |4 |4 4 |8 |4 |16 ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); 1 ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Statement Name: null Statement Text: select * from (select * from t1 union select * from t2) x1, (select * from t3 union all select * from t4) x2 where x1.i = x2.a 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: Hash Join ResultSet: Number of opens = 1 Rows seen from the left = 7 Rows seen from the right = 9 Rows filtered = 0 Rows returned = 9 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 Left result set: Sort ResultSet: Number of opens = 1 Rows input = 10 Rows returned = 7 Eliminate duplicates = true In sorted order = false Sort information: Number of rows input=10 Number of rows output=7 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 Source result set: Union ResultSet: Number of opens = 1 Rows seen from the left = 5 Rows seen from the right = 5 Rows returned = 10 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 Left result set: Table Scan ResultSet for T1 at read committed isolation level using share row locking chosen by the optimizer Number of opens = 1 Rows seen = 5 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=All Number of columns fetched=2 Number of pages visited=2 Number of rows qualified=5 Number of rows visited=5 Scan type=heap start position: null stop position: null qualifiers: None Right result set: Table Scan ResultSet for T2 at read committed isolation level using share row locking chosen by the optimizer Number of opens = 1 Rows seen = 5 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=All Number of columns fetched=2 Number of pages visited=2 Number of rows qualified=5 Number of rows visited=5 Scan type=heap start position: null stop position: null qualifiers: None Right result set: Hash Table ResultSet (9): Number of opens = 7 Hash table size = 19 Hash key is column number 0 Rows seen = 31 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 next qualifiers: Column[0][0] Id: 0 Operator: = Ordered nulls: false Unknown return value: false Negate comparison result: false Source result set: Union ResultSet: Number of opens = 1 Rows seen from the left = 19 Rows seen from the right = 12 Rows returned = 31 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 Left result set: Table Scan ResultSet for T3 at read committed isolation level using share row locking chosen by the optimizer Number of opens = 1 Rows seen = 19 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=All Number of columns fetched=2 Number of pages visited=2 Number of rows qualified=19 Number of rows visited=19 Scan type=heap start position: null stop position: null qualifiers: None Right result set: Table Scan ResultSet for T4 at read committed isolation level using share row locking chosen by the optimizer Number of opens = 1 Rows seen = 12 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=All Number of columns fetched=2 Number of pages visited=2 Number of rows qualified=12 Number of rows visited=12 Scan type=heap start position: null stop position: null qualifiers: None ij> select * from (select * from t1 union all select * from t2) x1, (select * from t3 union all select * from t4) x2 where x1.i = x2.a; I |J |A |B ----------------------------------------------- 1 |2 |1 |1 2 |4 |2 |2 3 |6 |3 |3 3 |6 |3 |12 4 |8 |4 |4 4 |8 |4 |16 1 |2 |1 |1 2 |-4 |2 |2 3 |6 |3 |3 3 |6 |3 |12 4 |-8 |4 |4 4 |-8 |4 |16 ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); 1 ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Statement Name: null Statement Text: select * from (select * from t1 union all select * from t2) x1, (select * from t3 union all select * from t4) x2 where x1.i = x2.a 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: Hash Join ResultSet: Number of opens = 1 Rows seen from the left = 10 Rows seen from the right = 12 Rows filtered = 0 Rows returned = 12 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 Left result set: Union ResultSet: Number of opens = 1 Rows seen from the left = 5 Rows seen from the right = 5 Rows returned = 10 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 Left result set: Table Scan ResultSet for T1 at read committed isolation level using share row locking chosen by the optimizer Number of opens = 1 Rows seen = 5 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=All Number of columns fetched=2 Number of pages visited=2 Number of rows qualified=5 Number of rows visited=5 Scan type=heap start position: null stop position: null qualifiers: None Right result set: Table Scan ResultSet for T2 at read committed isolation level using share row locking chosen by the optimizer Number of opens = 1 Rows seen = 5 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=All Number of columns fetched=2 Number of pages visited=2 Number of rows qualified=5 Number of rows visited=5 Scan type=heap start position: null stop position: null qualifiers: None Right result set: Hash Table ResultSet (8): Number of opens = 10 Hash table size = 19 Hash key is column number 0 Rows seen = 31 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 next qualifiers: Column[0][0] Id: 0 Operator: = Ordered nulls: false Unknown return value: false Negate comparison result: false Source result set: Union ResultSet: Number of opens = 1 Rows seen from the left = 19 Rows seen from the right = 12 Rows returned = 31 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 Left result set: Table Scan ResultSet for T3 at read committed isolation level using share row locking chosen by the optimizer Number of opens = 1 Rows seen = 19 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=All Number of columns fetched=2 Number of pages visited=2 Number of rows qualified=19 Number of rows visited=19 Scan type=heap start position: null stop position: null qualifiers: None Right result set: Table Scan ResultSet for T4 at read committed isolation level using share row locking chosen by the optimizer Number of opens = 1 Rows seen = 12 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=All Number of columns fetched=2 Number of pages visited=2 Number of rows qualified=12 Number of rows visited=12 Scan type=heap start position: null stop position: null qualifiers: None ij> -- Next set of queries tests pushdown of predicates whose -- column references do not reference base tables--ex. they -- reference literals, aggregates, or subqueries. We don't -- check the query plans here, we're just checking to make -- sure pushdown doesn't cause problems during compilation/ -- execution. In the case of regressions, errors that might -- show up here include compile-time NPEs, execution-time -- NPEs, errors saying no predicate was found for a hash join, -- and/or type comparison errors caused by incorrect column -- numbers for scoped predicates. create table tc (c1 char, c2 char, c3 char, c int); 0 rows inserted/updated/deleted ij> create view vz (z1, z2, z3, z4) as select distinct xx1.c1, xx1.c2, 'bokibob' bb, xx1.c from (select c1, c, c2, c3 from tc) xx1 union select 'i','j','j',i from t2; 0 rows inserted/updated/deleted ij> create view vz2 (z1, z2, z3, z4) as select distinct xx1.c1, xx1.c2, 'bokibob' bb, xx1.c from (select c1, c, c2, c3 from tc) xx1; 0 rows inserted/updated/deleted ij> create view vz3 (z1, z2, z3, z4) as select distinct xx1.c1, xx1.c2, 'bokibob' bb, xx1.c from (select c1, c, c2, 28 from tc) xx1 union select 'i','j','j',i from t2; 0 rows inserted/updated/deleted ij> create view vz4 (z1, z2, z3, z4) as select distinct xx1.c1, xx1.c2, 'bokibob' bb, xx1.c from (select c1, c, c2, 28 from tc) xx1 union select 'i','j','j',i from t2 union select c1, c2, c3, c from tc; 0 rows inserted/updated/deleted ij> -- For DERBY-1866. The problem for DERBY-1866 was that, -- when pushing predicates to subqueries beneath UNIONs, -- the predicates were always being pushed to the *first* -- table in the FROM list, regardless of whether or not -- that was actually the correct table. For the test -- query that uses this view (see below) the predicate -- is supposed to be pushed to TC, so in order to repro -- the DERBY-1866 failure we want to make sure that TC -- is *not* the first table in the FROM list. Thus we -- use the optimizer override to fix the join order so -- that TC is the second table. create view vz5a (z1, z2, z3, z4) as select distinct xx1.c1, xx1.c2, 'bokibob' bb, xx1.c from (select c1, c2, c3, c from --DERBY-PROPERTIES joinOrder=FIXED t2, tc where tc.c = t2.i) xx1 union select 'i','j','j',i from t2; 0 rows inserted/updated/deleted ij> -- Same as above but target FromTable in subquery is -- itself another subquery. create view vz5b (z1, z2, z3, z4) as select distinct xx1.c1, xx1.c2, 'bokibob' bb, xx1.c from (select c1, c2, c3, c from --DERBY-PROPERTIES joinOrder=FIXED t2, (select distinct * from tc) tc where tc.c = t2.i) xx1 union select 'i','j','j',i from t2; 0 rows inserted/updated/deleted ij> -- Same as above but target FromTable in subquery is -- another union node between two subqueries. create view vz5c (z1, z2, z3, z4) as select distinct xx1.c1, xx1.c2, 'bokibob' bb, xx1.c from (select c1, c2, c3, c from --DERBY-PROPERTIES joinOrder=FIXED t2, (select * from tc union select * from tc) tc where tc.c = t2.i) xx1 union select 'i','j','j',i from t2; 0 rows inserted/updated/deleted ij> -- Same as above but target FromTable in subquery is -- another full query with unions and subqueries. create view vz5d (z1, z2, z3, z4) as select distinct xx1.c1, xx1.c2, 'bokibob' bb, xx1.c from (select c1, c2, c3, c from --DERBY-PROPERTIES joinOrder=FIXED t2, (select * from tc union select z1 c1, z2 c2, z3 c3, z4 c from vz5b) tc where tc.c = t2.i) xx1 union select 'i','j','j',i from t2; 0 rows inserted/updated/deleted ij> -- Both sides of predicate reference aggregates. select x1.c1 from (select count(*) from t1 union select count(*) from t2) x1 (c1), (select count(*) from t3 union select count(*) from t4) x2 (c2) where x1.c1 = x2.c2; C1 ----------- ij> -- Both sides of predicate reference aggregates, and -- predicate is pushed through to non-flattenable nested -- subquery. select x1.c1 from (select count(*) from (select distinct j from t1) xx1 union select count(*) from t2 ) x1 (c1), (select count(*) from t3 union select count(*) from t4) x2 (c2) where x1.c1 = x2.c2; C1 ----------- ij> -- Both sides of predicate reference aggregates, and -- predicate is pushed through to non-flattenable nested -- subquery that is in turn part of a nested union. select x1.c1 from (select count(*) from (select distinct j from t1 union select distinct j from t2) xx1 union select count(*) from t2 ) x1 (c1), (select count(*) from t3 union select count(*) from t4) x2 (c2) where x1.c1 = x2.c2; C1 ----------- ij> -- Left side of predicate references base column, right side -- references aggregate; predicate is pushed through to non- -- flattenable nested subquery. select x1.c1 from (select xx1.c from (select distinct c, c1 from tc) xx1 union select count(*) from t2 ) x1 (c1), (select count(*) from t3 union select count(*) from t4) x2 (c2) where x1.c1 = x2.c2; C1 ----------- ij> -- Left side of predicate references base column, right side -- references aggregate; predicate is pushed through to non- -- flattenable nested subquery. select x1.c1 from (select xx1.c from (select c, c1 from tc) xx1 union select count(*) from t2 ) x1 (c1), (select count(*) from t3 union select count(*) from t4) x2 (c2) where x1.c1 = x2.c2; C1 ----------- ij> -- Left side of predicate references base column, right side -- side references aggregate; predicate is pushed through to -- a subquery in a nested union that has literals in its result -- column. select x1.z1 from (select xx1.c1, xx1.c2, xx1.c, xx1.c3 from (select c1, c2, c3, c from tc) xx1 union select 'i','j',j,'i' from t2 ) x1 (z1, z2, z3, z4), (select count(*) from t3 union select count (*) from t4) x2 (c2) where x1.z3 = x2.c2; Z1 ---- ij> -- Both sides of predicate reference base columns; predicate -- predicate is pushed through to a subquery in a nested union -- that has literals in its result column. select x1.z1 from (select xx1.c1, xx1.c2, xx1.c, xx1.c3 from (select c1, c2, c3, c from tc) xx1 union select 'i','j',j,'i' from t2 ) x1 (z1, z2, z3, z4), (select a from t3 union select count (*) from t4) x2 (c2) where x1.z3 = x2.c2; Z1 ---- i i i ij> -- Same as previous query, but with aggregate/base column -- in x2 switched. select x1.z1 from (select xx1.c1, xx1.c2, xx1.c, xx1.c3 from (select c1, c2, c3, c from tc) xx1 union select 'i','j',j,'i' from t2 ) x1 (z1, z2, z3, z4), (select count(*) from t3 union select a from t4) x2 (c2) where x1.z3 = x2.c2; Z1 ---- ij> -- Left side references aggregate, right side references base -- column; predicate is pushed to non-flattenable subquery -- that is part of a nested union for which one child references -- a base column and the other references an aggregate. select x1.c1 from (select count(*) from (select distinct j from t1) xx1 union select count(*) from t2 ) x1 (c1), (select a from t3 union select a from t4) x2 (c2) where x1.c1 = x2.c2; C1 ----------- ij> -- Same as previous query, but both children of inner-most -- union reference base columns. select x1.c1 from (select count(*) from (select distinct j from t1) xx1 union select i from t2 ) x1 (c1), (select a from t3 union select a from t4) x2 (c2) where x1.c1 = x2.c2; C1 ----------- 1 2 3 4 ij> -- Left side references aggregate, right side references base -- column; predicate is pushed to non-flattenable subquery -- that is part of a nested union for which one child references -- a base column and the other references an aggregate. select x1.c1 from (select count(*) from (select distinct j from t1) xx1 union select count(*) from t2 ) x1 (c1), (select i from t2 union select i from t1) x2 (c2) where x1.c1 = x2.c2; C1 ----------- 5 ij> -- Same as previous query, but one child of x2 references -- a literal. select x1.c1 from (select count(*) from (select distinct j from t1) xx1 union select count(*) from t2 ) x1 (c1), (select 1 from t2 union select i from t1) x2 (c2) where x1.c1 = x2.c2; C1 ----------- 5 ij> -- Left side of predicate references a base column that is -- deeply nested inside a subquery, a union, and a view, -- the latter of which itself has a union between two -- nested subqueries (whew). And finally, the position of -- the base column w.r.t the outer query (x1) is different -- than it is with respect to inner view (vz). select x1.z4 from (select z1, z4, z3 from vz union select '1', 4, '3' from t1 ) x1 (z1, z4, z3), (select distinct j from t2 union select j from t1) x2 (c2) where x1.z4 = x2.c2; Z4 ----------- 4 2 4 ij> -- Same as above but with an expression ("i+1") instead of -- a numeric literal. select x1.z4, x2.c2 from (select z1, z4, z3 from vz union select '1', i+1, '3' from t1 ) x1 (z1, z4, z3), (select distinct j from t2 union select j from t1) x2 (c2) where x1.z4 = x2.c2; Z4 |C2 ----------------------- 2 |2 4 |4 6 |6 2 |2 4 |4 ij> -- Same as previous query but with a different nested -- view (vz2) that is missing the nested union found -- in vz. select x1.z4 from (select z1, z4, z3 from vz2 union select '1', 4, '3' from t1 ) x1 (z1, z4, z3), (select distinct j from t2 union select j from t1) x2 (c2) where x1.z4 = x2.c2; Z4 ----------- 4 ij> -- Same as previous query but with a different nested -- view (vz4) that has double-nested unions in it. -- This is a test case for DERBY-1777. select x1.z4, x2.c2 from (select z1, z4, z3 from vz4 union select '1', i+1, '3' from t1 ) x1 (z1, z4, z3), (select distinct j from t2 union select j from t1) x2 (c2) where x1.z4 = x2.c2; Z4 |C2 ----------------------- 2 |2 4 |4 6 |6 2 |2 4 |4 ij> -- Push outer where predicate down into a UNION having a -- a Select child with more than one table in its FROM -- list. The predicate should be pushed to the correct -- table in the Select's FROM list. Prior to the fix for -- DERBY-1866 the predicate was always being pushed to -- the *first* table, regardless of whether or not that -- was actually the correct table. Thus the predicate -- "t1.i = vz5.z4" was getting pushed to table T2 even -- though it doesn't apply there. The result was an -- ASSERT failure in sane mode and an IndexOutOfBounds -- exception in insane mode. NOTE: Use of NESTEDLOOP -- join strategy ensures the predicate will be pushed -- (otherwise optimizer might choose to do a hash join -- and we wouldn't be testing what we want to test). select t1.i, vz5a.* from t1 left outer join vz5a --DERBY-PROPERTIES joinStrategy=NESTEDLOOP on t1.i = vz5a.z4; I |Z1 |Z2 |Z3 |Z4 ----------------------------------------- 1 |i |j |j |1 2 |i |j |j |2 3 |i |j |j |3 4 |i |j |j |4 5 |i |j |j |5 ij> -- Same query as above, but without the optimizer override. -- In this case there was another error where optimizer -- state involving the "joinOrder" override (see the -- definition of vz5a) was not properly reset, which could -- lead to an infinite loop. This problem was fixed as -- part of DERBY-1866, as well. select t1.i, vz5a.* from t1 left outer join vz5a on t1.i = vz5a.z4; I |Z1 |Z2 |Z3 |Z4 ----------------------------------------- 1 |i |j |j |1 2 |i |j |j |2 3 |i |j |j |3 4 |i |j |j |4 5 |i |j |j |5 ij> -- More tests for DERBY-1866 using more complicated views. select t1.i, vz5b.* from t1 left outer join vz5b --DERBY-PROPERTIES joinStrategy=NESTEDLOOP on t1.i = vz5b.z4; I |Z1 |Z2 |Z3 |Z4 ----------------------------------------- 1 |i |j |j |1 2 |i |j |j |2 3 |i |j |j |3 4 |i |j |j |4 5 |i |j |j |5 ij> select t1.i, vz5c.* from t1 left outer join vz5c --DERBY-PROPERTIES joinStrategy=NESTEDLOOP on t1.i = vz5c.z4; I |Z1 |Z2 |Z3 |Z4 ----------------------------------------- 1 |i |j |j |1 2 |i |j |j |2 3 |i |j |j |3 4 |i |j |j |4 5 |i |j |j |5 ij> select t1.i, vz5d.* from t1 left outer join vz5d --DERBY-PROPERTIES joinStrategy=NESTEDLOOP on t1.i = vz5d.z4; I |Z1 |Z2 |Z3 |Z4 ----------------------------------------- 1 |i |j |bokibob|1 1 |i |j |j |1 2 |i |j |bokibob|2 2 |i |j |j |2 3 |i |j |bokibob|3 3 |i |j |j |3 4 |i |j |bokibob|4 4 |i |j |j |4 5 |i |j |bokibob|5 5 |i |j |j |5 ij> -- Queries with Select->Union->Select chains having differently- -- ordered result column lists with some non-column reference -- expressions. In all of these queries we specify LEFT join -- and force NESTEDLOOP in order to coerce the optimizer to -- push predicates to a specific subquery. We do this to -- ensure that we test predicate pushdown during compilation -- AND during execution. It's the execution-time testing -- that is particular important for verifying DERBY-1633 -- functionality. -- Push predicate to union whose left child has a Select -- within a Select, both of which have the same result column -- ordering. select x1.z4, x2.c2 from (select z1, z4, z3 from vz union select '1', i+1, '3' from t1 ) x1 (z1, z4, z3) left join (select distinct i,j from (select distinct i,j from t2) x3 union select i, j from t1 ) x2 (c1, c2) --DERBY-PROPERTIES joinStrategy=NESTEDLOOP on x1.z4 = x2.c2; Z4 |C2 ----------------------- 2 |2 3 |NULL 4 |4 5 |NULL 6 |6 1 |NULL 2 |2 3 |NULL 4 |4 5 |NULL ij> -- Push predicate to union whose left child has a Select -- within a Select, where the result column lists for the -- two Selects are different ("i,j" vs "j,i"). select x1.z4, x2.c2 from (select z1, z4, z3 from vz union select '1', i+1, '3' from t1 ) x1 (z1, z4, z3) left join (select distinct i,j from (select distinct j,i from t2) x3 union select i, j from t1 ) x2 (c1, c2) --DERBY-PROPERTIES joinStrategy=NESTEDLOOP on x1.z4 = x2.c2; Z4 |C2 ----------------------- 2 |2 3 |NULL 4 |4 5 |NULL 6 |6 1 |NULL 2 |2 3 |NULL 4 |4 5 |NULL ij> -- Push predicate to union whose left child is itself a -- nested subquery (through use of the view "vz") and -- whose right child has an expression in its result -- column list. select x1.z4, x2.c2 from (select distinct i,j from (select distinct j,i from t2) x3 union select i, j from t1) x2 (c1, c2) left join (select z1, z4, z3 from vz union select '1', i+1, '3' from t1 ) x1 (z1, z4, z3) --DERBY-PROPERTIES joinStrategy=NESTEDLOOP on x1.z4 = x2.c2; Z4 |C2 ----------------------- 2 |2 2 |2 NULL |-4 4 |4 4 |4 6 |6 NULL |-8 NULL |8 NULL |10 ij> -- Same as previous but with a different expression. select x1.z4, x2.c2 from (select distinct i,j from (select distinct j,i from t2) x3 union select i, j from t1) x2 (c1, c2) left join (select z1, z4, z3 from vz union select '1', sin(i), '3' from t1 ) x1 (z1, z4, z3) --DERBY-PROPERTIES joinStrategy=NESTEDLOOP on x1.z4 = x2.c2; Z4 |C2 ---------------------------------- 2.0 |2 NULL |-4 4.0 |4 NULL |6 NULL |-8 NULL |8 NULL |10 ij> -- Same as previous but expression replaced with a regular -- column reference. select x1.z4, x2.c2 from (select distinct i,j from (select distinct j,i from t2) x3 union select i, j from t1) x2 (c1, c2) left join (select z1, z4, z3 from vz union select '1', i, '3' from t1 ) x1 (z1, z4, z3) --DERBY-PROPERTIES joinStrategy=NESTEDLOOP on x1.z4 = x2.c2; Z4 |C2 ----------------------- 2 |2 2 |2 NULL |-4 4 |4 4 |4 NULL |6 NULL |-8 NULL |8 NULL |10 ij> -- Same as previous but with a different expression and -- a different subquery (this time using view "vz3"). select x1.z4, x2.c2 from (select distinct i,j from (select distinct j,i from t2) x3 union select i, j from t1) x2 (c1, c2) left join (select z1, z4, z3 from vz3 union select '1', sin(i), '3' from t1 ) x1 (z1, z4, z3) --DERBY-PROPERTIES joinStrategy=NESTEDLOOP on x1.z4 = x2.c2; Z4 |C2 ---------------------------------- 2.0 |2 NULL |-4 4.0 |4 NULL |6 NULL |-8 NULL |8 NULL |10 ij> -- Push predicate to chain of unions whose left-most child -- is itself a nested subquery (through use of the view "vz") -- and in which the other unions have expressions in their -- result column lists. select x1.z4, x2.c2 from (select distinct i,j from (select distinct j,i from t2) x3 union select i, j from t1) x2 (c1, c2) left join (select z1, z4, z3 from vz union select '1', sin(i), '3' from t1 union select '1', 14, '3' from t1 ) x1 (z1, z4, z3) --DERBY-PROPERTIES joinStrategy=NESTEDLOOP on x1.z4 = x2.c2; Z4 |C2 ---------------------------------- 2.0 |2 NULL |-4 4.0 |4 NULL |6 NULL |-8 NULL |8 NULL |10 ij> -- Push predicate to chain of unions whose right-most child -- is itself a nested subquery (through use of the view "vz") -- and in which the other unions have expressions in their -- result column lists. select x1.z4, x2.c2 from (select distinct i,j from (select distinct j,i from t2) x3 union select i, j from t1) x2 (c1, c2) left join (select '1', sin(i), '3' from t1 union select '1', 14, '3' from t1 union select z1, z4, z3 from vz ) x1 (z1, z4, z3) --DERBY-PROPERTIES joinStrategy=NESTEDLOOP on x1.z4 = x2.c2; Z4 |C2 ---------------------------------- 2.0 |2 NULL |-4 4.0 |4 NULL |6 NULL |-8 NULL |8 NULL |10 ij> -- Cleanup from this set of tests. drop view vz; 0 rows inserted/updated/deleted ij> drop view vz2; 0 rows inserted/updated/deleted ij> drop view vz3; 0 rows inserted/updated/deleted ij> drop view vz4; 0 rows inserted/updated/deleted ij> drop view vz5a; 0 rows inserted/updated/deleted ij> drop view vz5d; 0 rows inserted/updated/deleted ij> drop view vz5b; 0 rows inserted/updated/deleted ij> drop view vz5c; 0 rows inserted/updated/deleted ij> drop table tc; 0 rows inserted/updated/deleted ij> -- Now bump up the size of tables T3 and T4 to the point where -- use of indexes will cause optimizer to choose nested loop join -- (and push predicates) instead of hash join. The following -- insertions put roughly 50,000 rows into T3 and into T4. -- These numbers are somewhat arbitrary, but please note that -- reducing the number of rows in these two tables could cause the -- optimizer to choose to skip pushing and instead use a hash join -- for some of the test queries. That's not 'wrong' per se, but -- it's not what we want to test here... autocommit off; ij> insert into t3 (a) values 21, 22, 23, 24, 25, 26, 27, 28, 29, 30; 10 rows inserted/updated/deleted ij> insert into t3 (a) values 31, 32, 33, 34, 35, 36, 37, 38, 39, 40; 10 rows inserted/updated/deleted ij> insert into t3 (a) values 41, 42, 43, 44, 45, 46, 47, 48, 49, 50; 10 rows inserted/updated/deleted ij> insert into t3 (a) values 51, 52, 53, 54, 55, 56, 57, 58, 59, 60; 10 rows inserted/updated/deleted ij> insert into t3 (a) values 61, 62, 63, 64, 65, 66, 67, 68, 69, 70; 10 rows inserted/updated/deleted ij> insert into t3 (a) values 71, 72, 73, 74, 75, 76, 77, 78, 79, 80; 10 rows inserted/updated/deleted ij> insert into t3 (a) values 81, 82, 83, 84, 85, 86, 87, 88, 89, 90; 10 rows inserted/updated/deleted ij> insert into t3 (a) values 91, 92, 93, 94, 95, 96, 97, 98, 99, 100; 10 rows inserted/updated/deleted ij> update t3 set b = 2 * a where a > 20; 80 rows inserted/updated/deleted ij> insert into t4 (a, b) (select a,b from t3 where a > 20); 80 rows inserted/updated/deleted ij> insert into t4 (a, b) (select a,b from t3 where a > 20); 80 rows inserted/updated/deleted ij> insert into t3 (a, b) (select a,b from t4 where a > 20); 160 rows inserted/updated/deleted ij> insert into t4 (a, b) (select a,b from t3 where a > 20); 240 rows inserted/updated/deleted ij> insert into t3 (a, b) (select a,b from t4 where a > 20); 400 rows inserted/updated/deleted ij> insert into t4 (a, b) (select a,b from t3 where a > 20); 640 rows inserted/updated/deleted ij> insert into t3 (a, b) (select a,b from t4 where a > 20); 1040 rows inserted/updated/deleted ij> insert into t4 (a, b) (select a,b from t3 where a > 20); 1680 rows inserted/updated/deleted ij> insert into t3 (a, b) (select a,b from t4 where a > 20); 2720 rows inserted/updated/deleted ij> insert into t4 (a, b) (select a,b from t3 where a > 20); 4400 rows inserted/updated/deleted ij> insert into t3 (a, b) (select a,b from t4 where a > 20); 7120 rows inserted/updated/deleted ij> insert into t4 (a, b) (select a,b from t3 where a > 20); 11520 rows inserted/updated/deleted ij> insert into t3 (a, b) (select a,b from t4 where a > 20); 18640 rows inserted/updated/deleted ij> insert into t4 (a, b) (select a,b from t3 where a > 20); 30160 rows inserted/updated/deleted ij> insert into t3 (a, b) (select a,b from t4 where a > 60); 24400 rows inserted/updated/deleted ij> commit; ij> autocommit on; ij> -- See exactly how many rows we inserted, for sanity. select count(*) from t3; 1 ----------- 54579 ij> select count(*) from t4; 1 ----------- 48812 ij> -- At this point we create the indexes. Note that we intentionally -- create the indexes AFTER loading the data, in order ensure that the -- index statistics are correct. We need the stats to be correct in -- order for the optimizer to choose the correct plan (i.e. to push the -- join predicates where possible). CREATE INDEX "APP"."T3_IX1" ON "APP"."T3" ("A"); 0 rows inserted/updated/deleted ij> CREATE INDEX "APP"."T3_IX2" ON "APP"."T3" ("B"); 0 rows inserted/updated/deleted ij> CREATE INDEX "APP"."T4_IX1" ON "APP"."T4" ("A"); 0 rows inserted/updated/deleted ij> CREATE INDEX "APP"."T4_IX2" ON "APP"."T4" ("B"); 0 rows inserted/updated/deleted ij> -- Create the rest of objects used in this test. CREATE TABLE "APP"."T5" ("I" INTEGER, "J" INTEGER); 0 rows inserted/updated/deleted ij> insert into t5 values (5, 10); 1 row inserted/updated/deleted ij> CREATE TABLE "APP"."T6" ("P" INTEGER, "Q" INTEGER); 0 rows inserted/updated/deleted ij> insert into t5 values (2, 4), (4, 8); 2 rows inserted/updated/deleted ij> CREATE TABLE "APP"."XX1" ("II" INTEGER NOT NULL, "JJ" CHAR(10), "MM" INTEGER, "OO" DOUBLE, "KK" BIGINT); 0 rows inserted/updated/deleted ij> CREATE TABLE "APP"."YY1" ("II" INTEGER NOT NULL, "JJ" CHAR(10), "AA" INTEGER, "OO" DOUBLE, "KK" BIGINT); 0 rows inserted/updated/deleted ij> ALTER TABLE "APP"."YY1" ADD CONSTRAINT "PK_YY1" PRIMARY KEY ("II"); 0 rows inserted/updated/deleted ij> ALTER TABLE "APP"."XX1" ADD CONSTRAINT "PK_XX1" PRIMARY KEY ("II"); 0 rows inserted/updated/deleted ij> create view xxunion as select all ii, jj, kk, mm from xx1 union all select ii, jj, kk, mm from xx1 union all select ii, jj, kk, mm from xx1 union all select ii, jj, kk, mm from xx1 union all select ii, jj, kk, mm from xx1 union all select ii, jj, kk, mm from xx1 union all select ii, jj, kk, mm from xx1 union all select ii, jj, kk, mm from xx1 union all select ii, jj, kk, mm from xx1 union all select ii, jj, kk, mm from xx1 union all select ii, jj, kk, mm from xx1 union all select ii, jj, kk, mm from xx1 union all select ii, jj, kk, mm from xx1 union all select ii, jj, kk, mm from xx1 union all select ii, jj, kk, mm from xx1 union all select ii, jj, kk, mm from xx1 union all select ii, jj, kk, mm from xx1 union all select ii, jj, kk, mm from xx1 union all select ii, jj, kk, mm from xx1 union all select ii, jj, kk, mm from xx1 union all select ii, jj, kk, mm from xx1 union all select ii, jj, kk, mm from xx1 union all select ii, jj, kk, mm from xx1 union all select ii, jj, kk, mm from xx1 union all select ii, jj, kk, mm from xx1; 0 rows inserted/updated/deleted ij> create view yyunion as select all ii, jj, kk, aa from yy1 union all select ii, jj, kk, aa from yy1 union all select ii, jj, kk, aa from yy1 union all select ii, jj, kk, aa from yy1 union all select ii, jj, kk, aa from yy1 union all select ii, jj, kk, aa from yy1 union all select ii, jj, kk, aa from yy1 union all select ii, jj, kk, aa from yy1 union all select ii, jj, kk, aa from yy1 union all select ii, jj, kk, aa from yy1 union all select ii, jj, kk, aa from yy1 union all select ii, jj, kk, aa from yy1 union all select ii, jj, kk, aa from yy1 union all select ii, jj, kk, aa from yy1 union all select ii, jj, kk, aa from yy1 union all select ii, jj, kk, aa from yy1 union all select ii, jj, kk, aa from yy1 union all select ii, jj, kk, aa from yy1 union all select ii, jj, kk, aa from yy1 union all select ii, jj, kk, aa from yy1 union all select ii, jj, kk, aa from yy1 union all select ii, jj, kk, aa from yy1 union all select ii, jj, kk, aa from yy1 union all select ii, jj, kk, aa from yy1 union all select ii, jj, kk, aa from yy1; 0 rows inserted/updated/deleted ij> -- Run compression on the test tables to try to get a consistent -- set of row count stats for the tables (DERBY-1902, DERBY-3479). call SYSCS_UTIL.SYSCS_COMPRESS_TABLE('APP', 'T1', 1); 0 rows inserted/updated/deleted ij> call SYSCS_UTIL.SYSCS_COMPRESS_TABLE('APP', 'T2', 1); 0 rows inserted/updated/deleted ij> call SYSCS_UTIL.SYSCS_COMPRESS_TABLE('APP', 'T3', 1); 0 rows inserted/updated/deleted ij> call SYSCS_UTIL.SYSCS_COMPRESS_TABLE('APP', 'T4', 1); 0 rows inserted/updated/deleted ij> call SYSCS_UTIL.SYSCS_COMPRESS_TABLE('APP', 'T5', 1); 0 rows inserted/updated/deleted ij> call SYSCS_UTIL.SYSCS_COMPRESS_TABLE('APP', 'T6', 1); 0 rows inserted/updated/deleted ij> -- And finally, run more extensive tests using the larger tables -- that have indexes. In these tests the optimizer should consider -- pushing predicates where possible. We can tell if a predicate -- has been "pushed" by looking at the query plan information for -- the tables in question: if the table has an index on a column that -- is used as part of the pushed predicate, then the optimizer will -- (for these tests) do an Index scan instead of a Table scan. If -- the table does not have such an index then the predicate will show -- up as a "qualifier" for a Table scan. In all of these tests T3 and -- T4 have appropriate indexes, so if we push a predicate to either -- of those tables we should see index scans. Neither T1 nor T2 -- has indexes, so if we push a predicate to either of those tables -- we should see a qualifier in the table scan information. -- Predicate push-down should occur for next two queries. Thus we -- we should see Index scans for T3 and T4--and this should be the -- case regardless of the order of the FROM list. select * from V1, V2 where V1.j = V2.b; I |J |A |B ----------------------------------------------- 1 |2 |2 |2 2 |4 |4 |4 ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); 1 ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Statement Name: null Statement Text: -- And finally, run more extensive tests using the larger tables -- that have indexes. In these tests the optimizer should consider -- pushing predicates where possible. We can tell if a predicate -- has been "pushed" by looking at the query plan information for -- the tables in question: if the table has an index on a column that -- is used as part of the pushed predicate, then the optimizer will -- (for these tests) do an Index scan instead of a Table scan. If -- the table does not have such an index then the predicate will show -- up as a "qualifier" for a Table scan. In all of these tests T3 and -- T4 have appropriate indexes, so if we push a predicate to either -- of those tables we should see index scans. Neither T1 nor T2 -- has indexes, so if we push a predicate to either of those tables -- we should see a qualifier in the table scan information. -- Predicate push-down should occur for next two queries. Thus we -- we should see Index scans for T3 and T4--and this should be the -- case regardless of the order of the FROM list. select * from V1, V2 where V1.j = V2.b 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: Nested Loop Join ResultSet: Number of opens = 1 Rows seen from the left = 7 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: Sort ResultSet: Number of opens = 1 Rows input = 10 Rows returned = 7 Eliminate duplicates = true In sorted order = false Sort information: Number of rows input=10 Number of rows output=7 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 Source result set: Union ResultSet: Number of opens = 1 Rows seen from the left = 5 Rows seen from the right = 5 Rows returned = 10 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 Left result set: Table Scan ResultSet for T1 at read committed isolation level using share row locking chosen by the optimizer Number of opens = 1 Rows seen = 5 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=All Number of columns fetched=2 Number of pages visited=2 Number of rows qualified=5 Number of rows visited=5 Scan type=heap start position: null stop position: null qualifiers: None Right result set: Table Scan ResultSet for T2 at read committed isolation level using share row locking chosen by the optimizer Number of opens = 1 Rows seen = 5 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=All Number of columns fetched=2 Number of pages visited=2 Number of rows qualified=5 Number of rows visited=5 Scan type=heap start position: null stop position: null qualifiers: None Right result set: Sort ResultSet: Number of opens = 7 Rows input = 2 Rows returned = 2 Eliminate duplicates = true In sorted order = false Sort information: Number of rows input=0 Number of rows output=0 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 Source result set: Union ResultSet: Number of opens = 7 Rows seen from the left = 2 Rows seen from the right = 0 Rows returned = 2 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 Left result set: Index Row to Base Row ResultSet for T3: Number of opens = 7 Rows seen = 2 Columns accessed from heap = {0} constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 Index Scan ResultSet for T3 using index T3_IX2 at read committed isolation level using share row locking chosen by the optimizer Number of opens = 7 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=All Number of columns fetched=2 Number of deleted rows visited=0 Number of pages visited=3 Number of rows qualified=0 Number of rows visited=1 Scan type=btree Tree height=3 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 Right result set: Index Row to Base Row ResultSet for T4: Number of opens = 7 Rows seen = 0 Columns accessed from heap = {0} constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 Index Scan ResultSet for T4 using index T4_IX2 at read committed isolation level using share row locking chosen by the optimizer Number of opens = 7 Rows seen = 0 Rows filtered = 0 Fetch Size = 1 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 scan information: Bit set of columns fetched=All Number of columns fetched=2 Number of deleted rows visited=0 Number of pages visited=3 Number of rows qualified=0 Number of rows visited=1 Scan type=btree Tree height=3 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> select * from V2, V1 where V1.j = V2.b; A |B |I |J ----------------------------------------------- 2 |2 |1 |2 4 |4 |2 |4 ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); 1 ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Statement Name: null Statement Text: select * from V2, V1 where V1.j = V2.b 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 (12): 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 = 7 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: Sort ResultSet: Number of opens = 1 Rows input = 10 Rows returned = 7 Eliminate duplicates = true In sorted order = false Sort information: Number of rows input=10 Number of rows output=7 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 Source result set: Union ResultSet: Number of opens = 1 Rows seen from the left = 5 Rows seen from the right = 5 Rows returned = 10 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 Left result set: Table Scan ResultSet for T1 at read committed isolation level using share row locking chosen by the optimizer Number of opens = 1 Rows seen = 5 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=All Number of columns fetched=2 Number of pages visited=2 Number of rows qualified=5 Number of rows visited=5 Scan type=heap start position: null stop position: null qualifiers: None Right result set: Table Scan ResultSet for T2 at read committed isolation level using share row locking chosen by the optimizer Number of opens = 1 Rows seen = 5 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=All Number of columns fetched=2 Number of pages visited=2 Number of rows qualified=5 Number of rows visited=5 Scan type=heap start position: null stop position: null qualifiers: None Right result set: Sort ResultSet: Number of opens = 7 Rows input = 2 Rows returned = 2 Eliminate duplicates = true In sorted order = false Sort information: Number of rows input=0 Number of rows output=0 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 Source result set: Union ResultSet: Number of opens = 7 Rows seen from the left = 2 Rows seen from the right = 0 Rows returned = 2 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 Left result set: Index Row to Base Row ResultSet for T3: Number of opens = 7 Rows seen = 2 Columns accessed from heap = {0} constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 Index Scan ResultSet for T3 using index T3_IX2 at read committed isolation level using share row locking chosen by the optimizer Number of opens = 7 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=All Number of columns fetched=2 Number of deleted rows visited=0 Number of pages visited=3 Number of rows qualified=0 Number of rows visited=1 Scan type=btree Tree height=3 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 Right result set: Index Row to Base Row ResultSet for T4: Number of opens = 7 Rows seen = 0 Columns accessed from heap = {0} constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 Index Scan ResultSet for T4 using index T4_IX2 at read committed isolation level using share row locking chosen by the optimizer Number of opens = 7 Rows seen = 0 Rows filtered = 0 Fetch Size = 1 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 scan information: Bit set of columns fetched=All Number of columns fetched=2 Number of deleted rows visited=0 Number of pages visited=3 Number of rows qualified=0 Number of rows visited=1 Scan type=btree Tree height=3 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> -- Changes for DERBY-805 don't affect non-join predicates (ex. "IN" or one- -- sided predicates), but make sure things still behave--i.e. these queries -- should still compile and execute without error. We don't expect to see -- any predicates pushed to T3 nor T4. select count(*) from V1, V2 where V1.i in (2,4); 1 ----------- 404 ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); 1 ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Statement Name: null Statement Text: -- Changes for DERBY-805 don't affect non-join predicates (ex. "IN" or one- -- sided predicates), but make sure things still behave--i.e. these queries -- should still compile and execute without error. We don't expect to see -- any predicates pushed to T3 nor T4. select count(*) from V1, V2 where V1.i in (2,4) 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 (15): 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: Scalar Aggregate ResultSet: Number of opens = 1 Rows input = 404 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 Index Key Optimization = false Source result set: Project-Restrict ResultSet (14): Number of opens = 1 Rows seen = 404 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 = 4 Rows seen from the right = 404 Rows filtered = 0 Rows returned = 404 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 Left result set: Project-Restrict ResultSet (9): Number of opens = 1 Rows seen = 4 Rows filtered = 0 restriction = true 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: Sort ResultSet: Number of opens = 1 Rows input = 4 Rows returned = 4 Eliminate duplicates = true In sorted order = false Sort information: Number of rows input=4 Number of rows output=4 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 Source result set: Union ResultSet: Number of opens = 1 Rows seen from the left = 2 Rows seen from the right = 2 Rows returned = 4 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 Left result set: Project-Restrict ResultSet (6): Number of opens = 1 Rows seen = 5 Rows filtered = 3 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 T1 at read committed isolation level using share row locking chosen by the optimizer Number of opens = 1 Rows seen = 5 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=All Number of columns fetched=2 Number of pages visited=2 Number of rows qualified=5 Number of rows visited=5 Scan type=heap start position: null stop position: null qualifiers: None Right result set: Project-Restrict ResultSet (8): Number of opens = 1 Rows seen = 5 Rows filtered = 3 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 T2 at read committed isolation level using share row locking chosen by the optimizer Number of opens = 1 Rows seen = 5 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=All Number of columns fetched=2 Number of pages visited=2 Number of rows qualified=5 Number of rows visited=5 Scan type=heap start position: null stop position: null qualifiers: None Right result set: Sort ResultSet: Number of opens = 4 Rows input = 413564 Rows returned = 404 Eliminate duplicates = true In sorted order = false Sort information: Number of rows input=103391 Number of rows output=101 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 Source result set: Union ResultSet: Number of opens = 4 Rows seen from the left = 218316 Rows seen from the right = 195248 Rows returned = 413564 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 Left result set: Table Scan ResultSet for T3 at read committed isolation level using share row locking chosen by the optimizer Number of opens = 4 Rows seen = 218316 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=All Number of columns fetched=2 Number of pages visited=435 Number of rows qualified=54579 Number of rows visited=54579 Scan type=heap start position: null stop position: null qualifiers: None Right result set: Table Scan ResultSet for T4 at read committed isolation level using share row locking chosen by the optimizer Number of opens = 4 Rows seen = 195248 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=All Number of columns fetched=2 Number of pages visited=389 Number of rows qualified=48812 Number of rows visited=48812 Scan type=heap start position: null stop position: null qualifiers: None ij> select count(*) from V1, V2 where V1.j > 0; 1 ----------- 505 ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); 1 ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Statement Name: null Statement Text: select count(*) from V1, V2 where V1.j > 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 (13): 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: Scalar Aggregate ResultSet: Number of opens = 1 Rows input = 505 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 Index Key Optimization = false Source result set: Project-Restrict ResultSet (12): Number of opens = 1 Rows seen = 505 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 = 5 Rows seen from the right = 505 Rows filtered = 0 Rows returned = 505 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 Left result set: Project-Restrict ResultSet (7): Number of opens = 1 Rows seen = 5 Rows filtered = 0 restriction = true 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: Sort ResultSet: Number of opens = 1 Rows input = 8 Rows returned = 5 Eliminate duplicates = true In sorted order = false Sort information: Number of rows input=8 Number of rows output=5 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 Source result set: Union ResultSet: Number of opens = 1 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: Table Scan ResultSet for T1 at read committed isolation level using share row locking chosen by the optimizer Number of opens = 1 Rows seen = 5 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=All Number of columns fetched=2 Number of pages visited=2 Number of rows qualified=5 Number of rows visited=5 Scan type=heap start position: null stop position: null qualifiers: Column[0][0] Id: 1 Operator: <= Ordered nulls: false Unknown return value: true Negate comparison result: true Right result set: Table Scan ResultSet for T2 at read committed isolation level using share row locking chosen by the optimizer Number of opens = 1 Rows seen = 3 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=All Number of columns fetched=2 Number of pages visited=2 Number of rows qualified=3 Number of rows visited=5 Scan type=heap start position: null stop position: null qualifiers: Column[0][0] Id: 1 Operator: <= Ordered nulls: false Unknown return value: true Negate comparison result: true Right result set: Sort ResultSet: Number of opens = 5 Rows input = 516955 Rows returned = 505 Eliminate duplicates = true In sorted order = false Sort information: Number of rows input=103391 Number of rows output=101 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 Source result set: Union ResultSet: Number of opens = 5 Rows seen from the left = 272895 Rows seen from the right = 244060 Rows returned = 516955 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 Left result set: Table Scan ResultSet for T3 at read committed isolation level using share row locking chosen by the optimizer Number of opens = 5 Rows seen = 272895 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=All Number of columns fetched=2 Number of pages visited=435 Number of rows qualified=54579 Number of rows visited=54579 Scan type=heap start position: null stop position: null qualifiers: None Right result set: Table Scan ResultSet for T4 at read committed isolation level using share row locking chosen by the optimizer Number of opens = 5 Rows seen = 244060 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=All Number of columns fetched=2 Number of pages visited=389 Number of rows qualified=48812 Number of rows visited=48812 Scan type=heap start position: null stop position: null qualifiers: None ij> -- Combination of join predicate and non-join predicate: the join predicate -- should be pushed to V2 (T3 and T4), the non-join predicate should operate -- as usual. select * from V1, V2 where V1.j = V2.b and V1.i in (2,4); I |J |A |B ----------------------------------------------- 2 |4 |4 |4 ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); 1 ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Statement Name: null Statement Text: -- Combination of join predicate and non-join predicate: the join predicate -- should be pushed to V2 (T3 and T4), the non-join predicate should operate -- as usual. select * from V1, V2 where V1.j = V2.b and V1.i in (2,4) 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: Nested Loop Join ResultSet: Number of opens = 1 Rows seen from the left = 4 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: Project-Restrict ResultSet (8): Number of opens = 1 Rows seen = 4 Rows filtered = 0 restriction = true 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: Sort ResultSet: Number of opens = 1 Rows input = 4 Rows returned = 4 Eliminate duplicates = true In sorted order = false Sort information: Number of rows input=4 Number of rows output=4 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 Source result set: Union ResultSet: Number of opens = 1 Rows seen from the left = 2 Rows seen from the right = 2 Rows returned = 4 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 Left result set: Project-Restrict ResultSet (5): Number of opens = 1 Rows seen = 5 Rows filtered = 3 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 T1 at read committed isolation level using share row locking chosen by the optimizer Number of opens = 1 Rows seen = 5 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=All Number of columns fetched=2 Number of pages visited=2 Number of rows qualified=5 Number of rows visited=5 Scan type=heap start position: null stop position: null qualifiers: None Right result set: Project-Restrict ResultSet (7): Number of opens = 1 Rows seen = 5 Rows filtered = 3 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 T2 at read committed isolation level using share row locking chosen by the optimizer Number of opens = 1 Rows seen = 5 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=All Number of columns fetched=2 Number of pages visited=2 Number of rows qualified=5 Number of rows visited=5 Scan type=heap start position: null stop position: null qualifiers: None Right result set: Sort ResultSet: Number of opens = 4 Rows input = 1 Rows returned = 1 Eliminate duplicates = true In sorted order = false Sort information: Number of rows input=0 Number of rows output=0 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 Source result set: Union ResultSet: Number of opens = 4 Rows seen from the left = 1 Rows seen from the right = 0 Rows returned = 1 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 Left result set: Index Row to Base Row ResultSet for T3: Number of opens = 4 Rows seen = 1 Columns accessed from heap = {0} constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 Index Scan ResultSet for T3 using index T3_IX2 at read committed isolation level using share row locking chosen by the optimizer Number of opens = 4 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=All Number of columns fetched=2 Number of deleted rows visited=0 Number of pages visited=3 Number of rows qualified=0 Number of rows visited=1 Scan type=btree Tree height=3 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 Right result set: Index Row to Base Row ResultSet for T4: Number of opens = 4 Rows seen = 0 Columns accessed from heap = {0} constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 Index Scan ResultSet for T4 using index T4_IX2 at read committed isolation level using share row locking chosen by the optimizer Number of opens = 4 Rows seen = 0 Rows filtered = 0 Fetch Size = 1 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 scan information: Bit set of columns fetched=All Number of columns fetched=2 Number of deleted rows visited=0 Number of pages visited=3 Number of rows qualified=0 Number of rows visited=1 Scan type=btree Tree height=3 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> -- Make sure predicates are pushed even if the subquery is explicit (as -- opposed to a view). Should see index scans on T3 and T4. select * from (select * from t1 union select * from t2) x1, (select * from t3 union select * from t4) x2 where x1.i = x2.a; I |J |A |B ----------------------------------------------- 1 |2 |1 |1 2 |-4 |2 |2 2 |4 |2 |2 3 |6 |3 |3 3 |6 |3 |12 4 |-8 |4 |4 4 |-8 |4 |16 4 |8 |4 |4 4 |8 |4 |16 ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); 1 ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Statement Name: null Statement Text: -- Make sure predicates are pushed even if the subquery is explicit (as -- opposed to a view). Should see index scans on T3 and T4. select * from (select * from t1 union select * from t2) x1, (select * from t3 union select * from t4) x2 where x1.i = x2.a 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: Nested Loop Join ResultSet: Number of opens = 1 Rows seen from the left = 7 Rows seen from the right = 9 Rows filtered = 0 Rows returned = 9 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 Left result set: Sort ResultSet: Number of opens = 1 Rows input = 10 Rows returned = 7 Eliminate duplicates = true In sorted order = false Sort information: Number of rows input=10 Number of rows output=7 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 Source result set: Union ResultSet: Number of opens = 1 Rows seen from the left = 5 Rows seen from the right = 5 Rows returned = 10 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 Left result set: Table Scan ResultSet for T1 at read committed isolation level using share row locking chosen by the optimizer Number of opens = 1 Rows seen = 5 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=All Number of columns fetched=2 Number of pages visited=2 Number of rows qualified=5 Number of rows visited=5 Scan type=heap start position: null stop position: null qualifiers: None Right result set: Table Scan ResultSet for T2 at read committed isolation level using share row locking chosen by the optimizer Number of opens = 1 Rows seen = 5 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=All Number of columns fetched=2 Number of pages visited=2 Number of rows qualified=5 Number of rows visited=5 Scan type=heap start position: null stop position: null qualifiers: None Right result set: Sort ResultSet: Number of opens = 7 Rows input = 9 Rows returned = 9 Eliminate duplicates = true In sorted order = false Sort information: Number of rows input=0 Number of rows output=0 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 Source result set: Union ResultSet: Number of opens = 7 Rows seen from the left = 6 Rows seen from the right = 3 Rows returned = 9 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 Left result set: Index Row to Base Row ResultSet for T3: Number of opens = 7 Rows seen = 6 Columns accessed from heap = {1} constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 Index Scan ResultSet for T3 using index T3_IX1 at read committed isolation level using share row locking chosen by the optimizer Number of opens = 7 Rows seen = 6 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=All Number of columns fetched=2 Number of deleted rows visited=0 Number of pages visited=3 Number of rows qualified=0 Number of rows visited=1 Scan type=btree Tree height=3 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 Right result set: Index Row to Base Row ResultSet for T4: Number of opens = 7 Rows seen = 3 Columns accessed from heap = {1} constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 Index Scan ResultSet for T4 using index T4_IX1 at read committed isolation level using share row locking chosen by the optimizer Number of opens = 7 Rows seen = 3 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=All Number of columns fetched=2 Number of deleted rows visited=0 Number of pages visited=3 Number of rows qualified=0 Number of rows visited=1 Scan type=btree Tree height=3 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> -- In this case optimizer will consider pushing predicate to X1 but will -- choose not to because it's cheaper to push the predicate to T3. -- So should see regular table scans on T1 and T2. select * from (select * from t1 union select * from t2) x1, t3 where x1.i = t3.a; I |J |A |B ----------------------------------------------- 1 |2 |1 |1 2 |-4 |2 |2 2 |4 |2 |2 3 |6 |3 |3 4 |-8 |4 |4 4 |8 |4 |4 ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); 1 ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Statement Name: null Statement Text: -- In this case optimizer will consider pushing predicate to X1 but will -- choose not to because it's cheaper to push the predicate to T3. -- So should see regular table scans on T1 and T2. select * from (select * from t1 union select * from t2) x1, t3 where x1.i = t3.a 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: Nested Loop Join ResultSet: Number of opens = 1 Rows seen from the left = 7 Rows seen from the right = 6 Rows filtered = 0 Rows returned = 6 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 Left result set: Sort ResultSet: Number of opens = 1 Rows input = 10 Rows returned = 7 Eliminate duplicates = true In sorted order = false Sort information: Number of rows input=10 Number of rows output=7 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 Source result set: Union ResultSet: Number of opens = 1 Rows seen from the left = 5 Rows seen from the right = 5 Rows returned = 10 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 Left result set: Table Scan ResultSet for T1 at read committed isolation level using share row locking chosen by the optimizer Number of opens = 1 Rows seen = 5 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=All Number of columns fetched=2 Number of pages visited=2 Number of rows qualified=5 Number of rows visited=5 Scan type=heap start position: null stop position: null qualifiers: None Right result set: Table Scan ResultSet for T2 at read committed isolation level using share row locking chosen by the optimizer Number of opens = 1 Rows seen = 5 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=All Number of columns fetched=2 Number of pages visited=2 Number of rows qualified=5 Number of rows visited=5 Scan type=heap start position: null stop position: null qualifiers: None Right result set: Index Row to Base Row ResultSet for T3: Number of opens = 7 Rows seen = 6 Columns accessed from heap = {0, 1} constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 Index Scan ResultSet for T3 using index T3_IX1 at read committed isolation level using instantaneous share row locking chosen by the optimizer Number of opens = 7 Rows seen = 6 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=2 Number of deleted rows visited=0 Number of pages visited=21 Number of rows qualified=6 Number of rows visited=13 Scan type=btree Tree height=3 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> -- UNION ALL should behave just like normal UNION. I.e. predicates should -- still be pushed to T3 and T4. select * from (select * from t1 union all select * from t2) x1, (select * from t3 union select * from t4) x2 where x1.i = x2.a; I |J |A |B ----------------------------------------------- 1 |2 |1 |1 2 |4 |2 |2 3 |6 |3 |3 3 |6 |3 |12 4 |8 |4 |4 4 |8 |4 |16 1 |2 |1 |1 2 |-4 |2 |2 3 |6 |3 |3 3 |6 |3 |12 4 |-8 |4 |4 4 |-8 |4 |16 ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); 1 ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Statement Name: null Statement Text: -- UNION ALL should behave just like normal UNION. I.e. predicates should -- still be pushed to T3 and T4. select * from (select * from t1 union all select * from t2) x1, (select * from t3 union select * from t4) x2 where x1.i = x2.a 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: Nested Loop Join ResultSet: Number of opens = 1 Rows seen from the left = 10 Rows seen from the right = 12 Rows filtered = 0 Rows returned = 12 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 Left result set: Union ResultSet: Number of opens = 1 Rows seen from the left = 5 Rows seen from the right = 5 Rows returned = 10 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 Left result set: Table Scan ResultSet for T1 at read committed isolation level using share row locking chosen by the optimizer Number of opens = 1 Rows seen = 5 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=All Number of columns fetched=2 Number of pages visited=2 Number of rows qualified=5 Number of rows visited=5 Scan type=heap start position: null stop position: null qualifiers: None Right result set: Table Scan ResultSet for T2 at read committed isolation level using share row locking chosen by the optimizer Number of opens = 1 Rows seen = 5 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=All Number of columns fetched=2 Number of pages visited=2 Number of rows qualified=5 Number of rows visited=5 Scan type=heap start position: null stop position: null qualifiers: None Right result set: Sort ResultSet: Number of opens = 10 Rows input = 12 Rows returned = 12 Eliminate duplicates = true In sorted order = false Sort information: Number of rows input=0 Number of rows output=0 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 Source result set: Union ResultSet: Number of opens = 10 Rows seen from the left = 8 Rows seen from the right = 4 Rows returned = 12 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 Left result set: Index Row to Base Row ResultSet for T3: Number of opens = 10 Rows seen = 8 Columns accessed from heap = {1} constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 Index Scan ResultSet for T3 using index T3_IX1 at read committed isolation level using share row locking chosen by the optimizer Number of opens = 10 Rows seen = 8 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=All Number of columns fetched=2 Number of deleted rows visited=0 Number of pages visited=3 Number of rows qualified=0 Number of rows visited=1 Scan type=btree Tree height=3 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 Right result set: Index Row to Base Row ResultSet for T4: Number of opens = 10 Rows seen = 4 Columns accessed from heap = {1} constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 Index Scan ResultSet for T4 using index T4_IX1 at read committed isolation level using share row locking chosen by the optimizer Number of opens = 10 Rows seen = 4 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=All Number of columns fetched=2 Number of deleted rows visited=0 Number of pages visited=3 Number of rows qualified=0 Number of rows visited=1 Scan type=btree Tree height=3 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> select * from (select * from t1 union all select * from t2) x1, (select * from t3 union all select * from t4) x2 where x1.i = x2.a; I |J |A |B ----------------------------------------------- 1 |2 |1 |1 2 |4 |2 |2 3 |6 |3 |3 3 |6 |3 |12 4 |8 |4 |4 4 |8 |4 |16 1 |2 |1 |1 2 |-4 |2 |2 3 |6 |3 |3 3 |6 |3 |12 4 |-8 |4 |4 4 |-8 |4 |16 ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); 1 ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Statement Name: null Statement Text: select * from (select * from t1 union all select * from t2) x1, (select * from t3 union all select * from t4) x2 where x1.i = x2.a 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: Nested Loop Join ResultSet: Number of opens = 1 Rows seen from the left = 10 Rows seen from the right = 12 Rows filtered = 0 Rows returned = 12 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 Left result set: Union ResultSet: Number of opens = 1 Rows seen from the left = 5 Rows seen from the right = 5 Rows returned = 10 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 Left result set: Table Scan ResultSet for T1 at read committed isolation level using share row locking chosen by the optimizer Number of opens = 1 Rows seen = 5 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=All Number of columns fetched=2 Number of pages visited=2 Number of rows qualified=5 Number of rows visited=5 Scan type=heap start position: null stop position: null qualifiers: None Right result set: Table Scan ResultSet for T2 at read committed isolation level using share row locking chosen by the optimizer Number of opens = 1 Rows seen = 5 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=All Number of columns fetched=2 Number of pages visited=2 Number of rows qualified=5 Number of rows visited=5 Scan type=heap start position: null stop position: null qualifiers: None Right result set: Union ResultSet: Number of opens = 10 Rows seen from the left = 8 Rows seen from the right = 4 Rows returned = 12 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 Left result set: Index Row to Base Row ResultSet for T3: Number of opens = 10 Rows seen = 8 Columns accessed from heap = {1} constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 Index Scan ResultSet for T3 using index T3_IX1 at read committed isolation level using share row locking chosen by the optimizer Number of opens = 10 Rows seen = 8 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=All Number of columns fetched=2 Number of deleted rows visited=0 Number of pages visited=3 Number of rows qualified=0 Number of rows visited=1 Scan type=btree Tree height=3 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 Right result set: Index Row to Base Row ResultSet for T4: Number of opens = 10 Rows seen = 4 Columns accessed from heap = {1} constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 Index Scan ResultSet for T4 using index T4_IX1 at read committed isolation level using share row locking chosen by the optimizer Number of opens = 10 Rows seen = 4 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=All Number of columns fetched=2 Number of deleted rows visited=0 Number of pages visited=3 Number of rows qualified=0 Number of rows visited=1 Scan type=btree Tree height=3 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> -- Predicate with both sides referencing same UNION isn't a join predicate, so -- no pushing should happen. So should see regular table scans on all tables. select * from v1, v2 where V1.i = V1.j; I |J |A |B ----------------------------------------------- ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); 1 ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Statement Name: null Statement Text: -- Predicate with both sides referencing same UNION isn't a join predicate, so -- no pushing should happen. So should see regular table scans on all tables. select * from v1, v2 where V1.i = V1.j 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: Nested Loop Join ResultSet: Number of opens = 1 Rows seen from the left = 0 Rows seen from the right = 0 Rows filtered = 0 Rows returned = 0 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 Left result set: Project-Restrict ResultSet (6): Number of opens = 1 Rows seen = 7 Rows filtered = 7 restriction = true 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: Sort ResultSet: Number of opens = 1 Rows input = 10 Rows returned = 7 Eliminate duplicates = true In sorted order = false Sort information: Number of rows input=10 Number of rows output=7 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 Source result set: Union ResultSet: Number of opens = 1 Rows seen from the left = 5 Rows seen from the right = 5 Rows returned = 10 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 Left result set: Table Scan ResultSet for T1 at read committed isolation level using share row locking chosen by the optimizer Number of opens = 1 Rows seen = 5 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=All Number of columns fetched=2 Number of pages visited=2 Number of rows qualified=5 Number of rows visited=5 Scan type=heap start position: null stop position: null qualifiers: None Right result set: Table Scan ResultSet for T2 at read committed isolation level using share row locking chosen by the optimizer Number of opens = 1 Rows seen = 5 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=All Number of columns fetched=2 Number of pages visited=2 Number of rows qualified=5 Number of rows visited=5 Scan type=heap start position: null stop position: null qualifiers: None Right result set: Sort ResultSet: Number of opens = 0 Rows input = 0 Rows returned = 0 Eliminate duplicates = true In sorted order = false Sort information: constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 Source result set: Union ResultSet: Number of opens = 0 Rows seen from the left = 0 Rows seen from the right = 0 Rows returned = 0 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 Left result set: Table Scan ResultSet for T3 at read committed isolation level using share row locking chosen by the optimizer Number of opens = 0 Rows seen = 0 Rows filtered = 0 Fetch Size = 1 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 scan information: start position: null stop position: null qualifiers: None Right result set: Table Scan ResultSet for T4 at read committed isolation level using share row locking chosen by the optimizer Number of opens = 0 Rows seen = 0 Rows filtered = 0 Fetch Size = 1 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 scan information: start position: null stop position: null qualifiers: None ij> -- Pushing predicates should still work even if user specifies explicit -- column names. In these two queries we push to X2 (T3 and T4). select * from (select * from t1 union select * from t2) x1 (c, d), (select * from t3 union select * from t4) x2 (e, f) where x1.c = x2.e; C |D |E |F ----------------------------------------------- 1 |2 |1 |1 2 |-4 |2 |2 2 |4 |2 |2 3 |6 |3 |3 3 |6 |3 |12 4 |-8 |4 |4 4 |-8 |4 |16 4 |8 |4 |4 4 |8 |4 |16 ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); 1 ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Statement Name: null Statement Text: -- Pushing predicates should still work even if user specifies explicit -- column names. In these two queries we push to X2 (T3 and T4). select * from (select * from t1 union select * from t2) x1 (c, d), (select * from t3 union select * from t4) x2 (e, f) where x1.c = x2.e 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: Nested Loop Join ResultSet: Number of opens = 1 Rows seen from the left = 7 Rows seen from the right = 9 Rows filtered = 0 Rows returned = 9 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 Left result set: Sort ResultSet: Number of opens = 1 Rows input = 10 Rows returned = 7 Eliminate duplicates = true In sorted order = false Sort information: Number of rows input=10 Number of rows output=7 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 Source result set: Union ResultSet: Number of opens = 1 Rows seen from the left = 5 Rows seen from the right = 5 Rows returned = 10 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 Left result set: Table Scan ResultSet for T1 at read committed isolation level using share row locking chosen by the optimizer Number of opens = 1 Rows seen = 5 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=All Number of columns fetched=2 Number of pages visited=2 Number of rows qualified=5 Number of rows visited=5 Scan type=heap start position: null stop position: null qualifiers: None Right result set: Table Scan ResultSet for T2 at read committed isolation level using share row locking chosen by the optimizer Number of opens = 1 Rows seen = 5 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=All Number of columns fetched=2 Number of pages visited=2 Number of rows qualified=5 Number of rows visited=5 Scan type=heap start position: null stop position: null qualifiers: None Right result set: Sort ResultSet: Number of opens = 7 Rows input = 9 Rows returned = 9 Eliminate duplicates = true In sorted order = false Sort information: Number of rows input=0 Number of rows output=0 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 Source result set: Union ResultSet: Number of opens = 7 Rows seen from the left = 6 Rows seen from the right = 3 Rows returned = 9 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 Left result set: Index Row to Base Row ResultSet for T3: Number of opens = 7 Rows seen = 6 Columns accessed from heap = {1} constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 Index Scan ResultSet for T3 using index T3_IX1 at read committed isolation level using share row locking chosen by the optimizer Number of opens = 7 Rows seen = 6 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=All Number of columns fetched=2 Number of deleted rows visited=0 Number of pages visited=3 Number of rows qualified=0 Number of rows visited=1 Scan type=btree Tree height=3 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 Right result set: Index Row to Base Row ResultSet for T4: Number of opens = 7 Rows seen = 3 Columns accessed from heap = {1} constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 Index Scan ResultSet for T4 using index T4_IX1 at read committed isolation level using share row locking chosen by the optimizer Number of opens = 7 Rows seen = 3 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=All Number of columns fetched=2 Number of deleted rows visited=0 Number of pages visited=3 Number of rows qualified=0 Number of rows visited=1 Scan type=btree Tree height=3 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> select * from (select * from t1 union select * from t2) x1 (a, b), (select * from t3 union select * from t4) x2 (i, j) where x1.a = x2.i; A |B |I |J ----------------------------------------------- 1 |2 |1 |1 2 |-4 |2 |2 2 |4 |2 |2 3 |6 |3 |3 3 |6 |3 |12 4 |-8 |4 |4 4 |-8 |4 |16 4 |8 |4 |4 4 |8 |4 |16 ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); 1 ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Statement Name: null Statement Text: select * from (select * from t1 union select * from t2) x1 (a, b), (select * from t3 union select * from t4) x2 (i, j) where x1.a = x2.i 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: Nested Loop Join ResultSet: Number of opens = 1 Rows seen from the left = 7 Rows seen from the right = 9 Rows filtered = 0 Rows returned = 9 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 Left result set: Sort ResultSet: Number of opens = 1 Rows input = 10 Rows returned = 7 Eliminate duplicates = true In sorted order = false Sort information: Number of rows input=10 Number of rows output=7 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 Source result set: Union ResultSet: Number of opens = 1 Rows seen from the left = 5 Rows seen from the right = 5 Rows returned = 10 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 Left result set: Table Scan ResultSet for T1 at read committed isolation level using share row locking chosen by the optimizer Number of opens = 1 Rows seen = 5 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=All Number of columns fetched=2 Number of pages visited=2 Number of rows qualified=5 Number of rows visited=5 Scan type=heap start position: null stop position: null qualifiers: None Right result set: Table Scan ResultSet for T2 at read committed isolation level using share row locking chosen by the optimizer Number of opens = 1 Rows seen = 5 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=All Number of columns fetched=2 Number of pages visited=2 Number of rows qualified=5 Number of rows visited=5 Scan type=heap start position: null stop position: null qualifiers: None Right result set: Sort ResultSet: Number of opens = 7 Rows input = 9 Rows returned = 9 Eliminate duplicates = true In sorted order = false Sort information: Number of rows input=0 Number of rows output=0 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 Source result set: Union ResultSet: Number of opens = 7 Rows seen from the left = 6 Rows seen from the right = 3 Rows returned = 9 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 Left result set: Index Row to Base Row ResultSet for T3: Number of opens = 7 Rows seen = 6 Columns accessed from heap = {1} constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 Index Scan ResultSet for T3 using index T3_IX1 at read committed isolation level using share row locking chosen by the optimizer Number of opens = 7 Rows seen = 6 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=All Number of columns fetched=2 Number of deleted rows visited=0 Number of pages visited=3 Number of rows qualified=0 Number of rows visited=1 Scan type=btree Tree height=3 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 Right result set: Index Row to Base Row ResultSet for T4: Number of opens = 7 Rows seen = 3 Columns accessed from heap = {1} constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 Index Scan ResultSet for T4 using index T4_IX1 at read committed isolation level using share row locking chosen by the optimizer Number of opens = 7 Rows seen = 3 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=All Number of columns fetched=2 Number of deleted rows visited=0 Number of pages visited=3 Number of rows qualified=0 Number of rows visited=1 Scan type=btree Tree height=3 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> -- In this query the optimizer will consider pushing, but will find -- that it's cheaper to do a hash join and thus will _not_ push. So -- we see hash join with table scan on T3. select count(*) from (select * from t1 union select * from t3) x1 (c, d), (select * from t2 union select * from t4) x2 (e, f) where x1.c = x2.e; 1 ----------- 103 ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); 1 ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Statement Name: null Statement Text: -- In this query the optimizer will consider pushing, but will find -- that it's cheaper to do a hash join and thus will _not_ push. So -- we see hash join with table scan on T3. select count(*) from (select * from t1 union select * from t3) x1 (c, d), (select * from t2 union select * from t4) x2 (e, f) where x1.c = x2.e 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 (13): 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: Scalar Aggregate ResultSet: Number of opens = 1 Rows input = 103 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 Index Key Optimization = false Source result set: Project-Restrict ResultSet (12): Number of opens = 1 Rows seen = 103 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: Hash Join ResultSet: Number of opens = 1 Rows seen from the left = 97 Rows seen from the right = 103 Rows filtered = 0 Rows returned = 103 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 Left result set: Sort ResultSet: Number of opens = 1 Rows input = 48817 Rows returned = 97 Eliminate duplicates = true In sorted order = false Sort information: Number of rows input=48817 Number of rows output=97 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 Source result set: Union ResultSet: Number of opens = 1 Rows seen from the left = 5 Rows seen from the right = 48812 Rows returned = 48817 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 Left result set: Table Scan ResultSet for T2 at read committed isolation level using share row locking chosen by the optimizer Number of opens = 1 Rows seen = 5 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=All Number of columns fetched=2 Number of pages visited=2 Number of rows qualified=5 Number of rows visited=5 Scan type=heap start position: null stop position: null qualifiers: None Right result set: Table Scan ResultSet for T4 at read committed isolation level using share row locking chosen by the optimizer Number of opens = 1 Rows seen = 48812 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=All Number of columns fetched=2 Number of pages visited=389 Number of rows qualified=48812 Number of rows visited=48812 Scan type=heap start position: null stop position: null qualifiers: None Right result set: Hash Table ResultSet (11): Number of opens = 97 Hash table size = 100 Hash key is column number 0 Rows seen = 104 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 next qualifiers: Column[0][0] Id: 0 Operator: = Ordered nulls: false Unknown return value: false Negate comparison result: false Source result set: Sort ResultSet: Number of opens = 1 Rows input = 54584 Rows returned = 104 Eliminate duplicates = true In sorted order = false Sort information: Number of rows input=54584 Number of rows output=104 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 Source result set: Union ResultSet: Number of opens = 1 Rows seen from the left = 5 Rows seen from the right = 54579 Rows returned = 54584 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 Left result set: Table Scan ResultSet for T1 at read committed isolation level using share row locking chosen by the optimizer Number of opens = 1 Rows seen = 5 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=All Number of columns fetched=2 Number of pages visited=2 Number of rows qualified=5 Number of rows visited=5 Scan type=heap start position: null stop position: null qualifiers: None Right result set: Table Scan ResultSet for T3 at read committed isolation level using share row locking chosen by the optimizer Number of opens = 1 Rows seen = 54579 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=All Number of columns fetched=2 Number of pages visited=435 Number of rows qualified=54579 Number of rows visited=54579 Scan type=heap start position: null stop position: null qualifiers: None ij> -- If we have nested unions, the predicate should get pushed all the way down -- to the base table(s) for every level of nesting. Should see index scans for -- T3 and for _both_ instances of T4. select * from (select * from t1 union select * from t2 union select * from t1 union select * from t2 ) x1, (select * from t3 union select * from t4 union select * from t4 ) x2 where x1.i = x2.a; I |J |A |B ----------------------------------------------- 1 |2 |1 |1 2 |-4 |2 |2 2 |4 |2 |2 3 |6 |3 |3 3 |6 |3 |12 4 |-8 |4 |4 4 |-8 |4 |16 4 |8 |4 |4 4 |8 |4 |16 ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); 1 ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Statement Name: null Statement Text: -- If we have nested unions, the predicate should get pushed all the way down -- to the base table(s) for every level of nesting. Should see index scans for -- T3 and for _both_ instances of T4. select * from (select * from t1 union select * from t2 union select * from t1 union select * from t2 ) x1, (select * from t3 union select * from t4 union select * from t4 ) x2 where x1.i = x2.a 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: Nested Loop Join ResultSet: Number of opens = 1 Rows seen from the left = 7 Rows seen from the right = 9 Rows filtered = 0 Rows returned = 9 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 Left result set: Sort ResultSet: Number of opens = 1 Rows input = 12 Rows returned = 7 Eliminate duplicates = true In sorted order = false Sort information: Number of rows input=12 Number of rows output=7 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 Source result set: Union ResultSet: Number of opens = 1 Rows seen from the left = 7 Rows seen from the right = 5 Rows returned = 12 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 Left result set: Sort ResultSet: Number of opens = 1 Rows input = 12 Rows returned = 7 Eliminate duplicates = true In sorted order = false Sort information: Number of rows input=12 Number of rows output=7 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 Source result set: Union ResultSet: Number of opens = 1 Rows seen from the left = 7 Rows seen from the right = 5 Rows returned = 12 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 Left result set: Sort ResultSet: Number of opens = 1 Rows input = 10 Rows returned = 7 Eliminate duplicates = true In sorted order = false Sort information: Number of rows input=10 Number of rows output=7 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 Source result set: Union ResultSet: Number of opens = 1 Rows seen from the left = 5 Rows seen from the right = 5 Rows returned = 10 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 Left result set: Table Scan ResultSet for T1 at read committed isolation level using share row locking chosen by the optimizer Number of opens = 1 Rows seen = 5 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=All Number of columns fetched=2 Number of pages visited=2 Number of rows qualified=5 Number of rows visited=5 Scan type=heap start position: null stop position: null qualifiers: None Right result set: Table Scan ResultSet for T2 at read committed isolation level using share row locking chosen by the optimizer Number of opens = 1 Rows seen = 5 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=All Number of columns fetched=2 Number of pages visited=2 Number of rows qualified=5 Number of rows visited=5 Scan type=heap start position: null stop position: null qualifiers: None Right result set: Table Scan ResultSet for T1 at read committed isolation level using share row locking chosen by the optimizer Number of opens = 1 Rows seen = 5 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=All Number of columns fetched=2 Number of pages visited=2 Number of rows qualified=5 Number of rows visited=5 Scan type=heap start position: null stop position: null qualifiers: None Right result set: Table Scan ResultSet for T2 at read committed isolation level using share row locking chosen by the optimizer Number of opens = 1 Rows seen = 5 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=All Number of columns fetched=2 Number of pages visited=2 Number of rows qualified=5 Number of rows visited=5 Scan type=heap start position: null stop position: null qualifiers: None Right result set: Sort ResultSet: Number of opens = 7 Rows input = 12 Rows returned = 9 Eliminate duplicates = true In sorted order = false Sort information: Number of rows input=0 Number of rows output=0 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 Source result set: Union ResultSet: Number of opens = 7 Rows seen from the left = 9 Rows seen from the right = 3 Rows returned = 12 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 Left result set: Sort ResultSet: Number of opens = 7 Rows input = 9 Rows returned = 9 Eliminate duplicates = true In sorted order = false Sort information: Number of rows input=0 Number of rows output=0 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 Source result set: Union ResultSet: Number of opens = 7 Rows seen from the left = 6 Rows seen from the right = 3 Rows returned = 9 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 Left result set: Index Row to Base Row ResultSet for T3: Number of opens = 7 Rows seen = 6 Columns accessed from heap = {1} constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 Index Scan ResultSet for T3 using index T3_IX1 at read committed isolation level using share row locking chosen by the optimizer Number of opens = 7 Rows seen = 6 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=All Number of columns fetched=2 Number of deleted rows visited=0 Number of pages visited=3 Number of rows qualified=0 Number of rows visited=1 Scan type=btree Tree height=3 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 Right result set: Index Row to Base Row ResultSet for T4: Number of opens = 7 Rows seen = 3 Columns accessed from heap = {1} constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 Index Scan ResultSet for T4 using index T4_IX1 at read committed isolation level using share row locking chosen by the optimizer Number of opens = 7 Rows seen = 3 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=All Number of columns fetched=2 Number of deleted rows visited=0 Number of pages visited=3 Number of rows qualified=0 Number of rows visited=1 Scan type=btree Tree height=3 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 Right result set: Index Row to Base Row ResultSet for T4: Number of opens = 7 Rows seen = 3 Columns accessed from heap = {1} constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 Index Scan ResultSet for T4 using index T4_IX1 at read committed isolation level using share row locking chosen by the optimizer Number of opens = 7 Rows seen = 3 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=All Number of columns fetched=2 Number of deleted rows visited=0 Number of pages visited=3 Number of rows qualified=0 Number of rows visited=1 Scan type=btree Tree height=3 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> -- Nested unions with non-join predicates should work as usual (no change -- with DERBY-805). So should see scalar qualifiers on scans for all -- instances of T1 and T2. select * from (select * from t1 union select * from t2 union select * from t1 union select * from t2 ) x1 where x1.i > 0; I |J ----------------------- 1 |2 2 |-4 2 |4 3 |6 4 |-8 4 |8 5 |10 ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); 1 ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Statement Name: null Statement Text: -- Nested unions with non-join predicates should work as usual (no change -- with DERBY-805). So should see scalar qualifiers on scans for all -- instances of T1 and T2. select * from (select * from t1 union select * from t2 union select * from t1 union select * from t2 ) x1 where x1.i > 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 (11): Number of opens = 1 Rows seen = 7 Rows filtered = 0 restriction = true 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: Sort ResultSet: Number of opens = 1 Rows input = 12 Rows returned = 7 Eliminate duplicates = true In sorted order = false Sort information: Number of rows input=12 Number of rows output=7 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 Source result set: Union ResultSet: Number of opens = 1 Rows seen from the left = 7 Rows seen from the right = 5 Rows returned = 12 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 Left result set: Sort ResultSet: Number of opens = 1 Rows input = 12 Rows returned = 7 Eliminate duplicates = true In sorted order = false Sort information: Number of rows input=12 Number of rows output=7 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 Source result set: Union ResultSet: Number of opens = 1 Rows seen from the left = 7 Rows seen from the right = 5 Rows returned = 12 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 Left result set: Sort ResultSet: Number of opens = 1 Rows input = 10 Rows returned = 7 Eliminate duplicates = true In sorted order = false Sort information: Number of rows input=10 Number of rows output=7 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 Source result set: Union ResultSet: Number of opens = 1 Rows seen from the left = 5 Rows seen from the right = 5 Rows returned = 10 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 Left result set: Table Scan ResultSet for T1 at read committed isolation level using share row locking chosen by the optimizer Number of opens = 1 Rows seen = 5 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=All Number of columns fetched=2 Number of pages visited=2 Number of rows qualified=5 Number of rows visited=5 Scan type=heap start position: null stop position: null qualifiers: Column[0][0] Id: 0 Operator: <= Ordered nulls: false Unknown return value: true Negate comparison result: true Right result set: Table Scan ResultSet for T2 at read committed isolation level using share row locking chosen by the optimizer Number of opens = 1 Rows seen = 5 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=All Number of columns fetched=2 Number of pages visited=2 Number of rows qualified=5 Number of rows visited=5 Scan type=heap start position: null stop position: null qualifiers: Column[0][0] Id: 0 Operator: <= Ordered nulls: false Unknown return value: true Negate comparison result: true Right result set: Table Scan ResultSet for T1 at read committed isolation level using share row locking chosen by the optimizer Number of opens = 1 Rows seen = 5 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=All Number of columns fetched=2 Number of pages visited=2 Number of rows qualified=5 Number of rows visited=5 Scan type=heap start position: null stop position: null qualifiers: Column[0][0] Id: 0 Operator: <= Ordered nulls: false Unknown return value: true Negate comparison result: true Right result set: Table Scan ResultSet for T2 at read committed isolation level using share row locking chosen by the optimizer Number of opens = 1 Rows seen = 5 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=All Number of columns fetched=2 Number of pages visited=2 Number of rows qualified=5 Number of rows visited=5 Scan type=heap start position: null stop position: null qualifiers: Column[0][0] Id: 0 Operator: <= Ordered nulls: false Unknown return value: true Negate comparison result: true ij> -- In this case there are no qualifiers, but the restriction is enforced -- at the ProjectRestrictNode level. That hasn't changed with DERBY-805. select count(*) from (select * from t1 union select * from t2 union select * from t3 union select * from t4 ) x1 (i, b) where x1.i > 0; 1 ----------- 108 ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); 1 ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Statement Name: null Statement Text: -- In this case there are no qualifiers, but the restriction is enforced -- at the ProjectRestrictNode level. That hasn't changed with DERBY-805. select count(*) from (select * from t1 union select * from t2 union select * from t3 union select * from t4 ) x1 (i, b) where x1.i > 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 (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: Scalar Aggregate ResultSet: Number of opens = 1 Rows input = 108 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 Index Key Optimization = false Source result set: Project-Restrict ResultSet (13): Number of opens = 1 Rows seen = 108 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: Project-Restrict ResultSet (12): Number of opens = 1 Rows seen = 108 Rows filtered = 0 restriction = true 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: Sort ResultSet: Number of opens = 1 Rows input = 48918 Rows returned = 108 Eliminate duplicates = true In sorted order = false Sort information: Number of rows input=48918 Number of rows output=108 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 Source result set: Union ResultSet: Number of opens = 1 Rows seen from the left = 106 Rows seen from the right = 48812 Rows returned = 48918 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 Left result set: Sort ResultSet: Number of opens = 1 Rows input = 54586 Rows returned = 106 Eliminate duplicates = true In sorted order = false Sort information: Number of rows input=54586 Number of rows output=106 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 Source result set: Union ResultSet: Number of opens = 1 Rows seen from the left = 7 Rows seen from the right = 54579 Rows returned = 54586 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 Left result set: Sort ResultSet: Number of opens = 1 Rows input = 10 Rows returned = 7 Eliminate duplicates = true In sorted order = false Sort information: Number of rows input=10 Number of rows output=7 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 Source result set: Union ResultSet: Number of opens = 1 Rows seen from the left = 5 Rows seen from the right = 5 Rows returned = 10 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 Left result set: Table Scan ResultSet for T1 at read committed isolation level using share row locking chosen by the optimizer Number of opens = 1 Rows seen = 5 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=All Number of columns fetched=2 Number of pages visited=2 Number of rows qualified=5 Number of rows visited=5 Scan type=heap start position: null stop position: null qualifiers: None Right result set: Table Scan ResultSet for T2 at read committed isolation level using share row locking chosen by the optimizer Number of opens = 1 Rows seen = 5 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=All Number of columns fetched=2 Number of pages visited=2 Number of rows qualified=5 Number of rows visited=5 Scan type=heap start position: null stop position: null qualifiers: None Right result set: Table Scan ResultSet for T3 at read committed isolation level using share row locking chosen by the optimizer Number of opens = 1 Rows seen = 54579 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=All Number of columns fetched=2 Number of pages visited=435 Number of rows qualified=54579 Number of rows visited=54579 Scan type=heap start position: null stop position: null qualifiers: None Right result set: Table Scan ResultSet for T4 at read committed isolation level using share row locking chosen by the optimizer Number of opens = 1 Rows seen = 48812 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=All Number of columns fetched=2 Number of pages visited=389 Number of rows qualified=48812 Number of rows visited=48812 Scan type=heap start position: null stop position: null qualifiers: None ij> -- Predicate pushdown should work with explicit use of "inner join" just like -- it does for implicit join. So should see index scans on T3 and T4. select * from (select * from t1 union select * from t2) x1 inner join (select * from t3 union select * from t4) x2 on x1.j = x2.b; I |J |A |B ----------------------------------------------- 1 |2 |2 |2 2 |4 |4 |4 ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); 1 ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Statement Name: null Statement Text: -- Predicate pushdown should work with explicit use of "inner join" just like -- it does for implicit join. So should see index scans on T3 and T4. select * from (select * from t1 union select * from t2) x1 inner join (select * from t3 union select * from t4) x2 on x1.j = x2.b 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: Nested Loop Join ResultSet: Number of opens = 1 Rows seen from the left = 7 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: Sort ResultSet: Number of opens = 1 Rows input = 10 Rows returned = 7 Eliminate duplicates = true In sorted order = false Sort information: Number of rows input=10 Number of rows output=7 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 Source result set: Union ResultSet: Number of opens = 1 Rows seen from the left = 5 Rows seen from the right = 5 Rows returned = 10 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 Left result set: Table Scan ResultSet for T1 at read committed isolation level using share row locking chosen by the optimizer Number of opens = 1 Rows seen = 5 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=All Number of columns fetched=2 Number of pages visited=2 Number of rows qualified=5 Number of rows visited=5 Scan type=heap start position: null stop position: null qualifiers: None Right result set: Table Scan ResultSet for T2 at read committed isolation level using share row locking chosen by the optimizer Number of opens = 1 Rows seen = 5 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=All Number of columns fetched=2 Number of pages visited=2 Number of rows qualified=5 Number of rows visited=5 Scan type=heap start position: null stop position: null qualifiers: None Right result set: Sort ResultSet: Number of opens = 7 Rows input = 2 Rows returned = 2 Eliminate duplicates = true In sorted order = false Sort information: Number of rows input=0 Number of rows output=0 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 Source result set: Union ResultSet: Number of opens = 7 Rows seen from the left = 2 Rows seen from the right = 0 Rows returned = 2 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 Left result set: Index Row to Base Row ResultSet for T3: Number of opens = 7 Rows seen = 2 Columns accessed from heap = {0} constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 Index Scan ResultSet for T3 using index T3_IX2 at read committed isolation level using share row locking chosen by the optimizer Number of opens = 7 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=All Number of columns fetched=2 Number of deleted rows visited=0 Number of pages visited=3 Number of rows qualified=0 Number of rows visited=1 Scan type=btree Tree height=3 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 Right result set: Index Row to Base Row ResultSet for T4: Number of opens = 7 Rows seen = 0 Columns accessed from heap = {0} constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 Index Scan ResultSet for T4 using index T4_IX2 at read committed isolation level using share row locking chosen by the optimizer Number of opens = 7 Rows seen = 0 Rows filtered = 0 Fetch Size = 1 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 scan information: Bit set of columns fetched=All Number of columns fetched=2 Number of deleted rows visited=0 Number of pages visited=3 Number of rows qualified=0 Number of rows visited=1 Scan type=btree Tree height=3 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> -- Can't push predicates into VALUES clauses. Predicate should end up -- at V2 (T3 and T4). select * from ( select i,j from t2 union values (1,1),(2,2),(3,3),(4,4) union select i,j from t1 ) x0 (i,j), v2 where x0.i = v2.a; I |J |A |B ----------------------------------------------- 1 |1 |1 |1 1 |2 |1 |1 2 |-4 |2 |2 2 |2 |2 |2 2 |4 |2 |2 3 |3 |3 |3 3 |3 |3 |12 3 |6 |3 |3 3 |6 |3 |12 4 |-8 |4 |4 4 |-8 |4 |16 4 |4 |4 |4 4 |4 |4 |16 4 |8 |4 |4 4 |8 |4 |16 ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); 1 ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Statement Name: null Statement Text: -- Can't push predicates into VALUES clauses. Predicate should end up -- at V2 (T3 and T4). select * from ( select i,j from t2 union values (1,1),(2,2),(3,3),(4,4) union select i,j from t1 ) x0 (i,j), v2 where x0.i = v2.a 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: Nested Loop Join ResultSet: Number of opens = 1 Rows seen from the left = 11 Rows seen from the right = 15 Rows filtered = 0 Rows returned = 15 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 Left result set: Sort ResultSet: Number of opens = 1 Rows input = 14 Rows returned = 11 Eliminate duplicates = true In sorted order = false Sort information: Number of rows input=14 Number of rows output=11 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 Source result set: Union ResultSet: Number of opens = 1 Rows seen from the left = 9 Rows seen from the right = 5 Rows returned = 14 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 Left result set: Sort ResultSet: Number of opens = 1 Rows input = 9 Rows returned = 9 Eliminate duplicates = true In sorted order = false Sort information: Number of rows input=9 Number of rows output=9 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 Source result set: Union ResultSet: Number of opens = 1 Rows seen from the left = 5 Rows seen from the right = 4 Rows returned = 9 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 Left result set: Table Scan ResultSet for T2 at read committed isolation level using share row locking chosen by the optimizer Number of opens = 1 Rows seen = 5 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=All Number of columns fetched=2 Number of pages visited=2 Number of rows qualified=5 Number of rows visited=5 Scan type=heap start position: null stop position: null qualifiers: None Right result set: Union ResultSet: Number of opens = 1 Rows seen from the left = 3 Rows seen from the right = 1 Rows returned = 4 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 Left result set: Union ResultSet: Number of opens = 1 Rows seen from the left = 2 Rows seen from the right = 1 Rows returned = 3 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 Left result set: Union ResultSet: Number of opens = 1 Rows seen from the left = 1 Rows seen from the right = 1 Rows returned = 2 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 Left 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 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 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 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 Right result set: Table Scan ResultSet for T1 at read committed isolation level using share row locking chosen by the optimizer Number of opens = 1 Rows seen = 5 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=All Number of columns fetched=2 Number of pages visited=2 Number of rows qualified=5 Number of rows visited=5 Scan type=heap start position: null stop position: null qualifiers: None Right result set: Sort ResultSet: Number of opens = 11 Rows input = 15 Rows returned = 15 Eliminate duplicates = true In sorted order = false Sort information: Number of rows input=0 Number of rows output=0 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 Source result set: Union ResultSet: Number of opens = 11 Rows seen from the left = 10 Rows seen from the right = 5 Rows returned = 15 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 Left result set: Index Row to Base Row ResultSet for T3: Number of opens = 11 Rows seen = 10 Columns accessed from heap = {1} constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 Index Scan ResultSet for T3 using index T3_IX1 at read committed isolation level using share row locking chosen by the optimizer Number of opens = 11 Rows seen = 10 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=All Number of columns fetched=2 Number of deleted rows visited=0 Number of pages visited=3 Number of rows qualified=0 Number of rows visited=1 Scan type=btree Tree height=3 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 Right result set: Index Row to Base Row ResultSet for T4: Number of opens = 11 Rows seen = 5 Columns accessed from heap = {1} constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 Index Scan ResultSet for T4 using index T4_IX1 at read committed isolation level using share row locking chosen by the optimizer Number of opens = 11 Rows seen = 5 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=All Number of columns fetched=2 Number of deleted rows visited=0 Number of pages visited=3 Number of rows qualified=0 Number of rows visited=1 Scan type=btree Tree height=3 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> -- Can't push predicates into VALUES clauses. Optimizer might consider pushing -- but shouldn't do it; in the end we'll do a hash join between X1 and T2. select * from t2, (select * from t1 union values (3,3), (4,4), (5,5), (6,6)) X1 (a,b) where X1.a = t2.i; I |J |A |B ----------------------------------------------- 1 |2 |1 |2 2 |-4 |2 |4 3 |6 |3 |3 3 |6 |3 |6 4 |-8 |4 |4 4 |-8 |4 |8 5 |10 |5 |5 5 |10 |5 |10 ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); 1 ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Statement Name: null Statement Text: -- Can't push predicates into VALUES clauses. Optimizer might consider pushing -- but shouldn't do it; in the end we'll do a hash join between X1 and T2. select * from t2, (select * from t1 union values (3,3), (4,4), (5,5), (6,6)) X1 (a,b) where X1.a = t2.i 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 (13): Number of opens = 1 Rows seen = 8 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: Hash Join ResultSet: Number of opens = 1 Rows seen from the left = 9 Rows seen from the right = 8 Rows filtered = 0 Rows returned = 8 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 Left result set: Sort ResultSet: Number of opens = 1 Rows input = 9 Rows returned = 9 Eliminate duplicates = true In sorted order = false Sort information: Number of rows input=9 Number of rows output=9 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 Source result set: Union ResultSet: Number of opens = 1 Rows seen from the left = 5 Rows seen from the right = 4 Rows returned = 9 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 Left result set: Table Scan ResultSet for T1 at read committed isolation level using share row locking chosen by the optimizer Number of opens = 1 Rows seen = 5 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=All Number of columns fetched=2 Number of pages visited=2 Number of rows qualified=5 Number of rows visited=5 Scan type=heap start position: null stop position: null qualifiers: None Right result set: Union ResultSet: Number of opens = 1 Rows seen from the left = 3 Rows seen from the right = 1 Rows returned = 4 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 Left result set: Union ResultSet: Number of opens = 1 Rows seen from the left = 2 Rows seen from the right = 1 Rows returned = 3 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 Left result set: Union ResultSet: Number of opens = 1 Rows seen from the left = 1 Rows seen from the right = 1 Rows returned = 2 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 Left 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 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 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 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 Right result set: Hash Scan ResultSet for T2 at read committed isolation level using instantaneous share row locking: Number of opens = 9 Hash table size = 5 Hash key is column number 0 Rows seen = 8 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=2 Number of pages visited=2 Number of rows qualified=5 Number of rows visited=5 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> -- Can't push predicates into VALUES clause. We'll try to push it to X1, but -- it will only make it to T4; it won't make it to T3 because the "other side" -- of the union with T3 is a VALUES clause. So we'll see an index scan on T4 -- and table scan on T3--but the predicate should still be applied to T3 at a -- higher level (through a ProjectRestrictNode), so we shouldn't get any extra -- rows. select * from (select i,j from t2 union values (1,1),(2,2),(3,3),(4,4) union select i,j from t1 ) x0 (i,j), (select a, b from t3 union values (4, 5), (5, 6), (6, 7) union select a, b from t4 ) x1 (a,b) where x0.i = x1.a; I |J |A |B ----------------------------------------------- 1 |1 |1 |1 1 |2 |1 |1 2 |-4 |2 |2 2 |2 |2 |2 2 |4 |2 |2 3 |3 |3 |3 3 |3 |3 |12 3 |6 |3 |3 3 |6 |3 |12 4 |-8 |4 |4 4 |-8 |4 |5 4 |-8 |4 |16 4 |4 |4 |4 4 |4 |4 |5 4 |4 |4 |16 4 |8 |4 |4 4 |8 |4 |5 4 |8 |4 |16 5 |10 |5 |6 ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); 1 ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Statement Name: null Statement Text: -- Can't push predicates into VALUES clause. We'll try to push it to X1, but -- it will only make it to T4; it won't make it to T3 because the "other side" -- of the union with T3 is a VALUES clause. So we'll see an index scan on T4 -- and table scan on T3--but the predicate should still be applied to T3 at a -- higher level (through a ProjectRestrictNode), so we shouldn't get any extra -- rows. select * from (select i,j from t2 union values (1,1),(2,2),(3,3),(4,4) union select i,j from t1 ) x0 (i,j), (select a, b from t3 union values (4, 5), (5, 6), (6, 7) union select a, b from t4 ) x1 (a,b) where x0.i = x1.a 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: Nested Loop Join ResultSet: Number of opens = 1 Rows seen from the left = 11 Rows seen from the right = 19 Rows filtered = 0 Rows returned = 19 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 Left result set: Sort ResultSet: Number of opens = 1 Rows input = 14 Rows returned = 11 Eliminate duplicates = true In sorted order = false Sort information: Number of rows input=14 Number of rows output=11 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 Source result set: Union ResultSet: Number of opens = 1 Rows seen from the left = 9 Rows seen from the right = 5 Rows returned = 14 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 Left result set: Sort ResultSet: Number of opens = 1 Rows input = 9 Rows returned = 9 Eliminate duplicates = true In sorted order = false Sort information: Number of rows input=9 Number of rows output=9 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 Source result set: Union ResultSet: Number of opens = 1 Rows seen from the left = 5 Rows seen from the right = 4 Rows returned = 9 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 Left result set: Table Scan ResultSet for T2 at read committed isolation level using share row locking chosen by the optimizer Number of opens = 1 Rows seen = 5 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=All Number of columns fetched=2 Number of pages visited=2 Number of rows qualified=5 Number of rows visited=5 Scan type=heap start position: null stop position: null qualifiers: None Right result set: Union ResultSet: Number of opens = 1 Rows seen from the left = 3 Rows seen from the right = 1 Rows returned = 4 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 Left result set: Union ResultSet: Number of opens = 1 Rows seen from the left = 2 Rows seen from the right = 1 Rows returned = 3 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 Left result set: Union ResultSet: Number of opens = 1 Rows seen from the left = 1 Rows seen from the right = 1 Rows returned = 2 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 Left 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 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 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 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 Right result set: Table Scan ResultSet for T1 at read committed isolation level using share row locking chosen by the optimizer Number of opens = 1 Rows seen = 5 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=All Number of columns fetched=2 Number of pages visited=2 Number of rows qualified=5 Number of rows visited=5 Scan type=heap start position: null stop position: null qualifiers: None Right result set: Project-Restrict ResultSet (27): Number of opens = 11 Rows seen = 1127 Rows filtered = 1108 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: Sort ResultSet: Number of opens = 11 Rows input = 1127 Rows returned = 1127 Eliminate duplicates = true In sorted order = false Sort information: Number of rows input=102 Number of rows output=102 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 Source result set: Union ResultSet: Number of opens = 11 Rows seen from the left = 1122 Rows seen from the right = 5 Rows returned = 1127 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 Left result set: Sort ResultSet: Number of opens = 11 Rows input = 600402 Rows returned = 1122 Eliminate duplicates = true In sorted order = false Sort information: Number of rows input=54582 Number of rows output=102 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 Source result set: Union ResultSet: Number of opens = 11 Rows seen from the left = 600369 Rows seen from the right = 33 Rows returned = 600402 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 Left result set: Table Scan ResultSet for T3 at read committed isolation level using share row locking chosen by the optimizer Number of opens = 11 Rows seen = 600369 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=All Number of columns fetched=2 Number of pages visited=435 Number of rows qualified=54579 Number of rows visited=54579 Scan type=heap start position: null stop position: null qualifiers: None Right result set: Union ResultSet: Number of opens = 11 Rows seen from the left = 22 Rows seen from the right = 11 Rows returned = 33 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 Left result set: Union ResultSet: Number of opens = 11 Rows seen from the left = 11 Rows seen from the right = 11 Rows returned = 22 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 Left result set: Row ResultSet: Number of opens = 11 Rows returned = 11 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 Right result set: Row ResultSet: Number of opens = 11 Rows returned = 11 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 Right result set: Row ResultSet: Number of opens = 11 Rows returned = 11 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 Right result set: Index Row to Base Row ResultSet for T4: Number of opens = 11 Rows seen = 5 Columns accessed from heap = {1} constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 Index Scan ResultSet for T4 using index T4_IX1 at read committed isolation level using share row locking chosen by the optimizer Number of opens = 11 Rows seen = 5 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=All Number of columns fetched=2 Number of deleted rows visited=0 Number of pages visited=3 Number of rows qualified=0 Number of rows visited=1 Scan type=btree Tree height=3 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> -- Make sure optimizer is still considering predicates for other, non-UNION -- nodes. Here we should use the predicate to do a hash join between X0 and -- T5 (i.e. we will not push it down to X0 because a) there are VALUES clauses -- to which we can't push, and b) it's cheaper to do the hash join). select * from t5, (values (2,2), (4,4) union values (1,1),(2,2),(3,3),(4,4) union select i,j from t1 ) x0 (i,j) where x0.i = t5.i; I |J |I |J ----------------------------------------------- 2 |4 |2 |2 2 |4 |2 |4 4 |8 |4 |4 4 |8 |4 |8 5 |10 |5 |10 ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); 1 ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Statement Name: null Statement Text: -- Make sure optimizer is still considering predicates for other, non-UNION -- nodes. Here we should use the predicate to do a hash join between X0 and -- T5 (i.e. we will not push it down to X0 because a) there are VALUES clauses -- to which we can't push, and b) it's cheaper to do the hash join). select * from t5, (values (2,2), (4,4) union values (1,1),(2,2),(3,3),(4,4) union select i,j from t1 ) x0 (i,j) where x0.i = t5.i 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 (18): Number of opens = 1 Rows seen = 5 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: Hash Join ResultSet: Number of opens = 1 Rows seen from the left = 9 Rows seen from the right = 5 Rows filtered = 0 Rows returned = 5 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 Left result set: Sort ResultSet: Number of opens = 1 Rows input = 9 Rows returned = 9 Eliminate duplicates = true In sorted order = false Sort information: Number of rows input=9 Number of rows output=9 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 Source result set: Union ResultSet: Number of opens = 1 Rows seen from the left = 4 Rows seen from the right = 5 Rows returned = 9 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 Left result set: Sort ResultSet: Number of opens = 1 Rows input = 6 Rows returned = 4 Eliminate duplicates = true In sorted order = false Sort information: Number of rows input=6 Number of rows output=4 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 Source result set: Union ResultSet: Number of opens = 1 Rows seen from the left = 2 Rows seen from the right = 4 Rows returned = 6 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 Left result set: Union ResultSet: Number of opens = 1 Rows seen from the left = 1 Rows seen from the right = 1 Rows returned = 2 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 Left 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 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 Right result set: Union ResultSet: Number of opens = 1 Rows seen from the left = 3 Rows seen from the right = 1 Rows returned = 4 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 Left result set: Union ResultSet: Number of opens = 1 Rows seen from the left = 2 Rows seen from the right = 1 Rows returned = 3 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 Left result set: Union ResultSet: Number of opens = 1 Rows seen from the left = 1 Rows seen from the right = 1 Rows returned = 2 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 Left 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 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 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 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 Right result set: Table Scan ResultSet for T1 at read committed isolation level using share row locking chosen by the optimizer Number of opens = 1 Rows seen = 5 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=All Number of columns fetched=2 Number of pages visited=2 Number of rows qualified=5 Number of rows visited=5 Scan type=heap start position: null stop position: null qualifiers: None Right result set: Hash Scan ResultSet for T5 at read committed isolation level using instantaneous share row locking: Number of opens = 9 Hash table size = 3 Hash key is column number 0 Rows seen = 5 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=2 Number of pages visited=2 Number of rows qualified=3 Number of rows visited=3 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> -- When we have very deeply nested union queries, make sure predicate push- -- down logic still works (esp. the scoping logic). These queries won't return -- any results, but the predicate should get pushed to EVERY instance of the -- base table all the way down. We're just checking to make sure these compile -- and execute without error. The query plan for these two queries alone would -- be several thousand lines so we don't print them out. We have other -- (smaller) tests to check that predicates are correctly pushed through nested -- unions. select distinct xx0.kk, xx0.ii, xx0.jj from xxunion xx0, yyunion yy0 where xx0.mm = yy0.ii; KK |II |JJ ------------------------------------------- ij> prepare sel1 as 'select distinct xx0.kk, xx0.ii, xx0.jj from xxunion xx0, yyunion yy0 where xx0.mm = yy0.ii and yy0.aa in (?) for fetch only'; ij> execute sel1 using 'values (1)'; IJ WARNING: Autocommit may close using result set KK |II |JJ ------------------------------------------- ij> -- Predicate push-down should only affect the UNIONs referenced; other UNIONs -- shouldn't interfere or be affected. Should see table scans for T1 and T2; -- then an index scan for the first instance of T3 and a table scan for second -- instance of T3; likewise for two instances of T4. select count(*) from (select * from t1 union select * from t2) x1, (select * from t3 union select * from t4) x2, (select * from t4 union select * from t3) x3 where x1.i = x3.a; 1 ----------- 909 ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); 1 ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Statement Name: null Statement Text: -- Predicate push-down should only affect the UNIONs referenced; other UNIONs -- shouldn't interfere or be affected. Should see table scans for T1 and T2; -- then an index scan for the first instance of T3 and a table scan for second -- instance of T3; likewise for two instances of T4. select count(*) from (select * from t1 union select * from t2) x1, (select * from t3 union select * from t4) x2, (select * from t4 union select * from t3) x3 where x1.i = x3.a 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 (19): 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: Scalar Aggregate ResultSet: Number of opens = 1 Rows input = 909 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 Index Key Optimization = false Source result set: Project-Restrict ResultSet (18): Number of opens = 1 Rows seen = 909 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 = 9 Rows seen from the right = 909 Rows filtered = 0 Rows returned = 909 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 = 7 Rows seen from the right = 9 Rows filtered = 0 Rows returned = 9 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 Left result set: Sort ResultSet: Number of opens = 1 Rows input = 10 Rows returned = 7 Eliminate duplicates = true In sorted order = false Sort information: Number of rows input=10 Number of rows output=7 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 Source result set: Union ResultSet: Number of opens = 1 Rows seen from the left = 5 Rows seen from the right = 5 Rows returned = 10 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 Left result set: Table Scan ResultSet for T1 at read committed isolation level using share row locking chosen by the optimizer Number of opens = 1 Rows seen = 5 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=All Number of columns fetched=2 Number of pages visited=2 Number of rows qualified=5 Number of rows visited=5 Scan type=heap start position: null stop position: null qualifiers: None Right result set: Table Scan ResultSet for T2 at read committed isolation level using share row locking chosen by the optimizer Number of opens = 1 Rows seen = 5 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=All Number of columns fetched=2 Number of pages visited=2 Number of rows qualified=5 Number of rows visited=5 Scan type=heap start position: null stop position: null qualifiers: None Right result set: Sort ResultSet: Number of opens = 7 Rows input = 9 Rows returned = 9 Eliminate duplicates = true In sorted order = false Sort information: Number of rows input=0 Number of rows output=0 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 Source result set: Union ResultSet: Number of opens = 7 Rows seen from the left = 3 Rows seen from the right = 6 Rows returned = 9 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 Left result set: Index Row to Base Row ResultSet for T4: Number of opens = 7 Rows seen = 3 Columns accessed from heap = {1} constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 Index Scan ResultSet for T4 using index T4_IX1 at read committed isolation level using share row locking chosen by the optimizer Number of opens = 7 Rows seen = 3 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=All Number of columns fetched=2 Number of deleted rows visited=0 Number of pages visited=3 Number of rows qualified=0 Number of rows visited=1 Scan type=btree Tree height=3 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 Right result set: Index Row to Base Row ResultSet for T3: Number of opens = 7 Rows seen = 6 Columns accessed from heap = {1} constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 Index Scan ResultSet for T3 using index T3_IX1 at read committed isolation level using share row locking chosen by the optimizer Number of opens = 7 Rows seen = 6 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=All Number of columns fetched=2 Number of deleted rows visited=0 Number of pages visited=3 Number of rows qualified=0 Number of rows visited=1 Scan type=btree Tree height=3 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 Right result set: Sort ResultSet: Number of opens = 9 Rows input = 930519 Rows returned = 909 Eliminate duplicates = true In sorted order = false Sort information: Number of rows input=103391 Number of rows output=101 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 Source result set: Union ResultSet: Number of opens = 9 Rows seen from the left = 491211 Rows seen from the right = 439308 Rows returned = 930519 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 Left result set: Table Scan ResultSet for T3 at read committed isolation level using share row locking chosen by the optimizer Number of opens = 9 Rows seen = 491211 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=All Number of columns fetched=2 Number of pages visited=435 Number of rows qualified=54579 Number of rows visited=54579 Scan type=heap start position: null stop position: null qualifiers: None Right result set: Table Scan ResultSet for T4 at read committed isolation level using share row locking chosen by the optimizer Number of opens = 9 Rows seen = 439308 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=All Number of columns fetched=2 Number of pages visited=389 Number of rows qualified=48812 Number of rows visited=48812 Scan type=heap start position: null stop position: null qualifiers: None ij> -- Here we should see index scans for both instances of T3 and for both -- instances of T4. select count(*) from (select * from t1 union select * from t2) x1, (select * from t3 union select * from t4) x2, (select * from t4 union select * from t3) x3 where x1.i = x3.a and x3.b = x2.b; 1 ----------- 9 ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); 1 ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Statement Name: null Statement Text: -- Here we should see index scans for both instances of T3 and for both -- instances of T4. select count(*) from (select * from t1 union select * from t2) x1, (select * from t3 union select * from t4) x2, (select * from t4 union select * from t3) x3 where x1.i = x3.a and x3.b = x2.b 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 (21): 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: Scalar Aggregate ResultSet: Number of opens = 1 Rows input = 9 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 Index Key Optimization = false Source result set: Project-Restrict ResultSet (20): Number of opens = 1 Rows seen = 9 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 = 9 Rows seen from the right = 9 Rows filtered = 0 Rows returned = 9 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 = 7 Rows seen from the right = 9 Rows filtered = 0 Rows returned = 9 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 Left result set: Sort ResultSet: Number of opens = 1 Rows input = 10 Rows returned = 7 Eliminate duplicates = true In sorted order = false Sort information: Number of rows input=10 Number of rows output=7 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 Source result set: Union ResultSet: Number of opens = 1 Rows seen from the left = 5 Rows seen from the right = 5 Rows returned = 10 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 Left result set: Table Scan ResultSet for T1 at read committed isolation level using share row locking chosen by the optimizer Number of opens = 1 Rows seen = 5 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=All Number of columns fetched=2 Number of pages visited=2 Number of rows qualified=5 Number of rows visited=5 Scan type=heap start position: null stop position: null qualifiers: None Right result set: Table Scan ResultSet for T2 at read committed isolation level using share row locking chosen by the optimizer Number of opens = 1 Rows seen = 5 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=All Number of columns fetched=2 Number of pages visited=2 Number of rows qualified=5 Number of rows visited=5 Scan type=heap start position: null stop position: null qualifiers: None Right result set: Sort ResultSet: Number of opens = 7 Rows input = 9 Rows returned = 9 Eliminate duplicates = true In sorted order = false Sort information: Number of rows input=0 Number of rows output=0 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 Source result set: Union ResultSet: Number of opens = 7 Rows seen from the left = 3 Rows seen from the right = 6 Rows returned = 9 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 Left result set: Index Row to Base Row ResultSet for T4: Number of opens = 7 Rows seen = 3 Columns accessed from heap = {1} constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 Index Scan ResultSet for T4 using index T4_IX1 at read committed isolation level using share row locking chosen by the optimizer Number of opens = 7 Rows seen = 3 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=All Number of columns fetched=2 Number of deleted rows visited=0 Number of pages visited=3 Number of rows qualified=0 Number of rows visited=1 Scan type=btree Tree height=3 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 Right result set: Index Row to Base Row ResultSet for T3: Number of opens = 7 Rows seen = 6 Columns accessed from heap = {1} constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 Index Scan ResultSet for T3 using index T3_IX1 at read committed isolation level using share row locking chosen by the optimizer Number of opens = 7 Rows seen = 6 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=All Number of columns fetched=2 Number of deleted rows visited=0 Number of pages visited=3 Number of rows qualified=0 Number of rows visited=1 Scan type=btree Tree height=3 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 Right result set: Sort ResultSet: Number of opens = 9 Rows input = 9 Rows returned = 9 Eliminate duplicates = true In sorted order = false Sort information: Number of rows input=1 Number of rows output=1 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 Source result set: Union ResultSet: Number of opens = 9 Rows seen from the left = 6 Rows seen from the right = 3 Rows returned = 9 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 Left result set: Index Row to Base Row ResultSet for T3: Number of opens = 9 Rows seen = 6 Columns accessed from heap = {0} constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 Index Scan ResultSet for T3 using index T3_IX2 at read committed isolation level using share row locking chosen by the optimizer Number of opens = 9 Rows seen = 6 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=All Number of columns fetched=2 Number of deleted rows visited=0 Number of pages visited=3 Number of rows qualified=0 Number of rows visited=1 Scan type=btree Tree height=3 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 Right result set: Index Row to Base Row ResultSet for T4: Number of opens = 9 Rows seen = 3 Columns accessed from heap = {0} constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 Index Scan ResultSet for T4 using index T4_IX2 at read committed isolation level using share row locking chosen by the optimizer Number of opens = 9 Rows seen = 3 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=All Number of columns fetched=2 Number of deleted rows visited=0 Number of pages visited=3 Number of rows qualified=1 Number of rows visited=2 Scan type=btree Tree height=3 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> -- Predicates pushed from outer queries shouldn't interfere with inner -- predicates for subqueries. Mostly checking for correct results here. select * from (select i, b j from t1, t4 where i = j union select * from t2) x1, t3 where x1.j = t3.a; I |J |A |B ----------------------------------------------- 1 |2 |2 |2 3 |6 |6 |24 5 |10 |10 |40 ij> -- Inner predicate should be handled as normal, outer predicate should -- either get pushed to V2 (T3 and T4) or else used for a hash join -- between x1 and v2. select * from (select i, b j from t1, t4 where i = j union select * from t2) x1, v2 where x1.j = v2.a; I |J |A |B ----------------------------------------------- 1 |2 |2 |2 3 |6 |6 |24 5 |10 |10 |40 ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); 1 ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Statement Name: null Statement Text: -- Inner predicate should be handled as normal, outer predicate should -- either get pushed to V2 (T3 and T4) or else used for a hash join -- between x1 and v2. select * from (select i, b j from t1, t4 where i = j union select * from t2) x1, v2 where x1.j = v2.a 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 (15): Number of opens = 1 Rows seen = 3 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: Hash Join ResultSet: Number of opens = 1 Rows seen from the left = 101 Rows seen from the right = 3 Rows filtered = 0 Rows returned = 3 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 Left result set: Sort ResultSet: Number of opens = 1 Rows input = 103391 Rows returned = 101 Eliminate duplicates = true In sorted order = false Sort information: Number of rows input=103391 Number of rows output=101 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 Source result set: Union ResultSet: Number of opens = 1 Rows seen from the left = 54579 Rows seen from the right = 48812 Rows returned = 103391 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 Left result set: Table Scan ResultSet for T3 at read committed isolation level using share row locking chosen by the optimizer Number of opens = 1 Rows seen = 54579 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=All Number of columns fetched=2 Number of pages visited=435 Number of rows qualified=54579 Number of rows visited=54579 Scan type=heap start position: null stop position: null qualifiers: None Right result set: Table Scan ResultSet for T4 at read committed isolation level using share row locking chosen by the optimizer Number of opens = 1 Rows seen = 48812 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=All Number of columns fetched=2 Number of pages visited=389 Number of rows qualified=48812 Number of rows visited=48812 Scan type=heap start position: null stop position: null qualifiers: None Right result set: Hash Table ResultSet (14): Number of opens = 101 Hash table size = 5 Hash key is column number 1 Rows seen = 5 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 next qualifiers: Column[0][0] Id: 1 Operator: = Ordered nulls: false Unknown return value: false Negate comparison result: false Source result set: Sort ResultSet: Number of opens = 1 Rows input = 5 Rows returned = 5 Eliminate duplicates = true In sorted order = false Sort information: Number of rows input=5 Number of rows output=5 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 Source result set: Union ResultSet: Number of opens = 1 Rows seen from the left = 0 Rows seen from the right = 5 Rows returned = 5 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 Left result set: Project-Restrict ResultSet (12): Number of opens = 1 Rows seen = 0 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 = 0 Rows seen from the right = 0 Rows filtered = 0 Rows returned = 0 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 Left result set: Project-Restrict ResultSet (10): Number of opens = 1 Rows seen = 5 Rows filtered = 5 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 T1 at read committed isolation level using share row locking chosen by the optimizer Number of opens = 1 Rows seen = 5 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=All Number of columns fetched=2 Number of pages visited=2 Number of rows qualified=5 Number of rows visited=5 Scan type=heap start position: null stop position: null qualifiers: None Right result set: Index Scan ResultSet for T4 using index T4_IX2 at read committed isolation level using share row locking chosen by the optimizer Number of opens = 0 Rows seen = 0 Rows filtered = 0 Fetch Size = 1 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 scan information: start position: None stop position: None qualifiers: None Right result set: Table Scan ResultSet for T2 at read committed isolation level using share row locking chosen by the optimizer Number of opens = 1 Rows seen = 5 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=All Number of columns fetched=2 Number of pages visited=2 Number of rows qualified=5 Number of rows visited=5 Scan type=heap start position: null stop position: null qualifiers: None ij> -- Outer predicate should either get pushed to V2 (T3 and T4) or -- else used for a hash join; similarly, inner predicate should -- either get pushed to T3 or else used for hash join between T1 -- and T3. select * from (select i, j from t1, t3 where i = a union select * from t2) x1, v2 where x1.i = v2.a; I |J |A |B ----------------------------------------------- 1 |2 |1 |1 2 |-4 |2 |2 2 |4 |2 |2 3 |6 |3 |3 3 |6 |3 |12 4 |-8 |4 |4 4 |8 |4 |4 4 |-8 |4 |16 4 |8 |4 |16 ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); 1 ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Statement Name: null Statement Text: -- Outer predicate should either get pushed to V2 (T3 and T4) or -- else used for a hash join; similarly, inner predicate should -- either get pushed to T3 or else used for hash join between T1 -- and T3. select * from (select i, j from t1, t3 where i = a union select * from t2) x1, v2 where x1.i = v2.a 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 (14): Number of opens = 1 Rows seen = 9 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: Hash Join ResultSet: Number of opens = 1 Rows seen from the left = 101 Rows seen from the right = 9 Rows filtered = 0 Rows returned = 9 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 Left result set: Sort ResultSet: Number of opens = 1 Rows input = 103391 Rows returned = 101 Eliminate duplicates = true In sorted order = false Sort information: Number of rows input=103391 Number of rows output=101 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 Source result set: Union ResultSet: Number of opens = 1 Rows seen from the left = 54579 Rows seen from the right = 48812 Rows returned = 103391 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 Left result set: Table Scan ResultSet for T3 at read committed isolation level using share row locking chosen by the optimizer Number of opens = 1 Rows seen = 54579 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=All Number of columns fetched=2 Number of pages visited=435 Number of rows qualified=54579 Number of rows visited=54579 Scan type=heap start position: null stop position: null qualifiers: None Right result set: Table Scan ResultSet for T4 at read committed isolation level using share row locking chosen by the optimizer Number of opens = 1 Rows seen = 48812 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=All Number of columns fetched=2 Number of pages visited=389 Number of rows qualified=48812 Number of rows visited=48812 Scan type=heap start position: null stop position: null qualifiers: None Right result set: Hash Table ResultSet (13): Number of opens = 101 Hash table size = 5 Hash key is column number 0 Rows seen = 7 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 next qualifiers: Column[0][0] Id: 0 Operator: = Ordered nulls: false Unknown return value: false Negate comparison result: false Source result set: Sort ResultSet: Number of opens = 1 Rows input = 9 Rows returned = 7 Eliminate duplicates = true In sorted order = false Sort information: Number of rows input=9 Number of rows output=7 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 Source result set: Union ResultSet: Number of opens = 1 Rows seen from the left = 4 Rows seen from the right = 5 Rows returned = 9 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 Left result set: Project-Restrict ResultSet (11): Number of opens = 1 Rows seen = 4 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: Hash Join ResultSet: Number of opens = 1 Rows seen from the left = 5 Rows seen from the right = 4 Rows filtered = 0 Rows returned = 4 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 Left result set: Table Scan ResultSet for T1 at read committed isolation level using share row locking chosen by the optimizer Number of opens = 1 Rows seen = 5 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=All Number of columns fetched=2 Number of pages visited=2 Number of rows qualified=5 Number of rows visited=5 Scan type=heap start position: null stop position: null qualifiers: None Right result set: Hash Scan ResultSet for T3 using index T3_IX1 at read committed isolation level using instantaneous share row locking: Number of opens = 5 Hash table size = 99 Hash key is column number 0 Rows seen = 4 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=297 Number of rows qualified=54579 Number of rows visited=54579 Scan type=btree Tree height=3 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: Table Scan ResultSet for T2 at read committed isolation level using share row locking chosen by the optimizer Number of opens = 1 Rows seen = 5 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=All Number of columns fetched=2 Number of pages visited=2 Number of rows qualified=5 Number of rows visited=5 Scan type=heap start position: null stop position: null qualifiers: None ij> -- Inner predicates treated as restrictions, outer predicate either -- pushed to X2 (T2 and T1) or used for hash join between X2 and X1. select * from (select i, b j from t1, t4 where i = j union select * from t2) x1, (select i, b j from t2, t3 where i = j union select * from t1) x2 where x1.j = x2.i; I |J |I |J ----------------------------------------------- 1 |2 |2 |4 ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); 1 ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Statement Name: null Statement Text: -- Inner predicates treated as restrictions, outer predicate either -- pushed to X2 (T2 and T1) or used for hash join between X2 and X1. select * from (select i, b j from t1, t4 where i = j union select * from t2) x1, (select i, b j from t2, t3 where i = j union select * from t1) x2 where x1.j = x2.i 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: Hash Join ResultSet: Number of opens = 1 Rows seen from the left = 5 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: Sort ResultSet: Number of opens = 1 Rows input = 5 Rows returned = 5 Eliminate duplicates = true In sorted order = false Sort information: Number of rows input=5 Number of rows output=5 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 Source result set: Union ResultSet: Number of opens = 1 Rows seen from the left = 0 Rows seen from the right = 5 Rows returned = 5 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 Left result set: Project-Restrict ResultSet (8): Number of opens = 1 Rows seen = 0 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 = 0 Rows seen from the right = 0 Rows filtered = 0 Rows returned = 0 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 Left result set: Project-Restrict ResultSet (6): Number of opens = 1 Rows seen = 5 Rows filtered = 5 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 T1 at read committed isolation level using share row locking chosen by the optimizer Number of opens = 1 Rows seen = 5 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=All Number of columns fetched=2 Number of pages visited=2 Number of rows qualified=5 Number of rows visited=5 Scan type=heap start position: null stop position: null qualifiers: None Right result set: Index Scan ResultSet for T4 using index T4_IX2 at read committed isolation level using share row locking chosen by the optimizer Number of opens = 0 Rows seen = 0 Rows filtered = 0 Fetch Size = 1 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 scan information: start position: None stop position: None qualifiers: None Right result set: Table Scan ResultSet for T2 at read committed isolation level using share row locking chosen by the optimizer Number of opens = 1 Rows seen = 5 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=All Number of columns fetched=2 Number of pages visited=2 Number of rows qualified=5 Number of rows visited=5 Scan type=heap start position: null stop position: null qualifiers: None Right result set: Hash Table ResultSet (18): Number of opens = 5 Hash table size = 5 Hash key is column number 0 Rows seen = 5 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 next qualifiers: Column[0][0] Id: 0 Operator: = Ordered nulls: false Unknown return value: false Negate comparison result: false Source result set: Sort ResultSet: Number of opens = 1 Rows input = 5 Rows returned = 5 Eliminate duplicates = true In sorted order = false Sort information: Number of rows input=5 Number of rows output=5 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 Source result set: Union ResultSet: Number of opens = 1 Rows seen from the left = 0 Rows seen from the right = 5 Rows returned = 5 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 Left result set: Project-Restrict ResultSet (16): Number of opens = 1 Rows seen = 0 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 = 0 Rows seen from the right = 0 Rows filtered = 0 Rows returned = 0 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 Left result set: Project-Restrict ResultSet (14): Number of opens = 1 Rows seen = 5 Rows filtered = 5 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 T2 at read committed isolation level using share row locking chosen by the optimizer Number of opens = 1 Rows seen = 5 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=All Number of columns fetched=2 Number of pages visited=2 Number of rows qualified=5 Number of rows visited=5 Scan type=heap start position: null stop position: null qualifiers: None Right result set: Index Scan ResultSet for T3 using index T3_IX2 at read committed isolation level using share row locking chosen by the optimizer Number of opens = 0 Rows seen = 0 Rows filtered = 0 Fetch Size = 1 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 scan information: start position: None stop position: None qualifiers: None Right result set: Table Scan ResultSet for T1 at read committed isolation level using share row locking chosen by the optimizer Number of opens = 1 Rows seen = 5 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=All Number of columns fetched=2 Number of pages visited=2 Number of rows qualified=5 Number of rows visited=5 Scan type=heap start position: null stop position: null qualifiers: None ij> -- Following queries deal with nested subqueries, which deserve extra -- testing because "best paths" for outer queries might not agree with -- "best paths" for inner queries, so we need to make sure the correct -- paths (based on predicates that are or are not pushed) are ultimately -- generated. -- Predicate should get pushed to V2 (T3 and T4). select count(*) from (select i,a,j,b from V1, V2 where V1.j = V2.b ) X3; 1 ----------- 2 ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); 1 ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Statement Name: null Statement Text: -- Following queries deal with nested subqueries, which deserve extra -- testing because "best paths" for outer queries might not agree with -- "best paths" for inner queries, so we need to make sure the correct -- paths (based on predicates that are or are not pushed) are ultimately -- generated. -- Predicate should get pushed to V2 (T3 and T4). select count(*) from (select i,a,j,b from V1, V2 where V1.j = V2.b ) X3 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 (15): 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: Scalar Aggregate ResultSet: Number of opens = 1 Rows input = 2 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 Index Key Optimization = false Source result set: Project-Restrict ResultSet (14): 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: Project-Restrict ResultSet (13): 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 = 7 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: Sort ResultSet: Number of opens = 1 Rows input = 10 Rows returned = 7 Eliminate duplicates = true In sorted order = false Sort information: Number of rows input=10 Number of rows output=7 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 Source result set: Union ResultSet: Number of opens = 1 Rows seen from the left = 5 Rows seen from the right = 5 Rows returned = 10 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 Left result set: Table Scan ResultSet for T1 at read committed isolation level using share row locking chosen by the optimizer Number of opens = 1 Rows seen = 5 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=All Number of columns fetched=2 Number of pages visited=2 Number of rows qualified=5 Number of rows visited=5 Scan type=heap start position: null stop position: null qualifiers: None Right result set: Table Scan ResultSet for T2 at read committed isolation level using share row locking chosen by the optimizer Number of opens = 1 Rows seen = 5 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=All Number of columns fetched=2 Number of pages visited=2 Number of rows qualified=5 Number of rows visited=5 Scan type=heap start position: null stop position: null qualifiers: None Right result set: Sort ResultSet: Number of opens = 7 Rows input = 2 Rows returned = 2 Eliminate duplicates = true In sorted order = false Sort information: Number of rows input=0 Number of rows output=0 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 Source result set: Union ResultSet: Number of opens = 7 Rows seen from the left = 2 Rows seen from the right = 0 Rows returned = 2 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 Left result set: Index Row to Base Row ResultSet for T3: Number of opens = 7 Rows seen = 2 Columns accessed from heap = {0} constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 Index Scan ResultSet for T3 using index T3_IX2 at read committed isolation level using share row locking chosen by the optimizer Number of opens = 7 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=All Number of columns fetched=2 Number of deleted rows visited=0 Number of pages visited=3 Number of rows qualified=0 Number of rows visited=1 Scan type=btree Tree height=3 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 Right result set: Index Row to Base Row ResultSet for T4: Number of opens = 7 Rows seen = 0 Columns accessed from heap = {0} constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 Index Scan ResultSet for T4 using index T4_IX2 at read committed isolation level using share row locking chosen by the optimizer Number of opens = 7 Rows seen = 0 Rows filtered = 0 Fetch Size = 1 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 scan information: Bit set of columns fetched=All Number of columns fetched=2 Number of deleted rows visited=0 Number of pages visited=3 Number of rows qualified=0 Number of rows visited=1 Scan type=btree Tree height=3 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> -- Multiple subqueries but NO UNIONs. All predicates are used for joins -- at their current level (no pushing). select t2.i,p from (select distinct i,p from (select distinct i,a from t1, t3 where t1.j = t3.b) X1, t6 where X1.a = t6.p) X2, t2 where t2.i = X2.i; I |P ----------------------- ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); 1 ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Statement Name: null Statement Text: -- Multiple subqueries but NO UNIONs. All predicates are used for joins -- at their current level (no pushing). select t2.i,p from (select distinct i,p from (select distinct i,a from t1, t3 where t1.j = t3.b) X1, t6 where X1.a = t6.p) X2, t2 where t2.i = X2.i 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 (13): Number of opens = 1 Rows seen = 0 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: Hash Join ResultSet: Number of opens = 1 Rows seen from the left = 0 Rows seen from the right = 0 Rows filtered = 0 Rows returned = 0 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 Left result set: Sort ResultSet: Number of opens = 1 Rows input = 0 Rows returned = 0 Eliminate duplicates = true In sorted order = false Sort information: Number of rows input=0 Number of rows output=0 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 Source result set: Project-Restrict ResultSet (11): Number of opens = 1 Rows seen = 0 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: Hash Join ResultSet: Number of opens = 1 Rows seen from the left = 2 Rows seen from the right = 0 Rows filtered = 0 Rows returned = 0 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 Left result set: Sort ResultSet: Number of opens = 1 Rows input = 2 Rows returned = 2 Eliminate duplicates = true In sorted order = false Sort information: Number of rows input=2 Number of rows output=2 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 Source result set: Project-Restrict ResultSet (9): 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 = 5 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: Table Scan ResultSet for T1 at read committed isolation level using share row locking chosen by the optimizer Number of opens = 1 Rows seen = 5 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=All Number of columns fetched=2 Number of pages visited=2 Number of rows qualified=5 Number of rows visited=5 Scan type=heap start position: null stop position: null qualifiers: None Right result set: Index Row to Base Row ResultSet for T3: Number of opens = 5 Rows seen = 2 Columns accessed from heap = {0} constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 Index Scan ResultSet for T3 using index T3_IX2 at read committed isolation level using share row locking chosen by the optimizer Number of opens = 5 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=All Number of columns fetched=2 Number of deleted rows visited=0 Number of pages visited=15 Number of rows qualified=2 Number of rows visited=7 Scan type=btree Tree height=3 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 Right result set: Hash Scan ResultSet for T6 at read committed isolation level using instantaneous share row locking: Number of opens = 2 Hash table size = 0 Hash key is column number 0 Rows seen = 0 Rows filtered = 0 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 scan information: Bit set of columns fetched={0} Number of columns fetched=1 Number of pages visited=2 Number of rows qualified=0 Number of rows visited=0 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 Right result set: Hash Scan ResultSet for T2 at read committed isolation level using instantaneous share row locking: Number of opens = 0 Hash table size = 0 Hash key is column number 0 Rows seen = 0 Rows filtered = 0 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 scan information: 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> -- Multiple, non-flattenable subqueries, but NO UNIONs. Shouldn't push -- anything. select x1.j, x2.b from (select distinct i,j from t1) x1, (select distinct a,b from t3) x2 where x1.i = x2.a order by x1.j, x2.b; J |B ----------------------- 2 |1 4 |2 6 |3 8 |4 ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); 1 ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Statement Name: null Statement Text: -- Multiple, non-flattenable subqueries, but NO UNIONs. Shouldn't push -- anything. select x1.j, x2.b from (select distinct i,j from t1) x1, (select distinct a,b from t3) x2 where x1.i = x2.a order by x1.j, x2.b 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: Sort ResultSet: Number of opens = 1 Rows input = 4 Rows returned = 4 Eliminate duplicates = false In sorted order = false Sort information: Number of rows input=4 Number of rows output=4 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 Source result set: Project-Restrict ResultSet (5): Number of opens = 1 Rows seen = 4 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 = 5 Rows seen from the right = 4 Rows filtered = 0 Rows returned = 4 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 Left result set: Distinct Scan ResultSet for T1 at read committed isolation level using instantaneous share row locking: Number of opens = 1 Hash table size = 5 Distinct columns are column numbers (0,1) Rows seen = 5 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=2 Number of pages visited=2 Number of rows qualified=5 Number of rows visited=5 Scan type=heap start position: null stop position: null scan qualifiers: None next qualifiers: None Right result set: Project-Restrict ResultSet (4): Number of opens = 5 Rows seen = 495 Rows filtered = 491 restriction = true 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: Distinct Scan ResultSet for T3 at read committed isolation level using instantaneous share row locking: Number of opens = 5 Hash table size = 99 Distinct columns are column numbers (0,1) Rows seen = 495 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=2 Number of pages visited=435 Number of rows qualified=54579 Number of rows visited=54579 Scan type=heap start position: null stop position: null scan qualifiers: None next qualifiers: None ij> select x1.j, x2.b from (select distinct i,j from t1) x1, (select distinct a,b from t3) x2, (select distinct i,j from t2) x3, (select distinct a,b from t4) x4 where x1.i = x2.a and x3.i = x4.a order by x1.j, x2.b; J |B ----------------------- 2 |1 2 |1 4 |2 4 |2 6 |3 6 |3 8 |4 8 |4 ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); 1 ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Statement Name: null Statement Text: select x1.j, x2.b from (select distinct i,j from t1) x1, (select distinct a,b from t3) x2, (select distinct i,j from t2) x3, (select distinct a,b from t4) x4 where x1.i = x2.a and x3.i = x4.a order by x1.j, x2.b 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: Sort ResultSet: Number of opens = 1 Rows input = 8 Rows returned = 8 Eliminate duplicates = false In sorted order = false Sort information: Number of rows input=8 Number of rows output=8 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 Source result set: Project-Restrict ResultSet (10): Number of opens = 1 Rows seen = 8 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 = 20 Rows seen from the right = 8 Rows filtered = 0 Rows returned = 8 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 = 25 Rows seen from the right = 20 Rows filtered = 0 Rows returned = 20 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 = 5 Rows seen from the right = 25 Rows filtered = 0 Rows returned = 25 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 Left result set: Distinct Scan ResultSet for T2 at read committed isolation level using instantaneous share row locking: Number of opens = 1 Hash table size = 5 Distinct columns are column numbers (0,1) Rows seen = 5 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=2 Number of pages visited=2 Number of rows qualified=5 Number of rows visited=5 Scan type=heap start position: null stop position: null scan qualifiers: None next qualifiers: None Right result set: Distinct Scan ResultSet for T1 at read committed isolation level using instantaneous share row locking: Number of opens = 5 Hash table size = 5 Distinct columns are column numbers (0,1) Rows seen = 25 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=2 Number of pages visited=2 Number of rows qualified=5 Number of rows visited=5 Scan type=heap start position: null stop position: null scan qualifiers: None next qualifiers: None Right result set: Project-Restrict ResultSet (7): Number of opens = 25 Rows seen = 2475 Rows filtered = 2455 restriction = true 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: Distinct Scan ResultSet for T3 at read committed isolation level using instantaneous share row locking: Number of opens = 25 Hash table size = 99 Distinct columns are column numbers (0,1) Rows seen = 2475 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=2 Number of pages visited=435 Number of rows qualified=54579 Number of rows visited=54579 Scan type=heap start position: null stop position: null scan qualifiers: None next qualifiers: None Right result set: Project-Restrict ResultSet (9): Number of opens = 20 Rows seen = 1840 Rows filtered = 1832 restriction = true 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: Distinct Scan ResultSet for T4 at read committed isolation level using instantaneous share row locking: Number of opens = 20 Hash table size = 92 Distinct columns are column numbers (0,1) Rows seen = 1840 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=2 Number of pages visited=389 Number of rows qualified=48812 Number of rows visited=48812 Scan type=heap start position: null stop position: null scan qualifiers: None next qualifiers: None ij> -- Multiple subqueries that are UNIONs. Outer-most predicate -- X0.b = X2.j can be pushed to union X0 but NOT to subquery X2. -- Inner predicate T6.p = X1.i is eligible for being pushed into -- union X1. In this case outer predicate is pushed to X0 -- (so we'll see index scans on T3 and T4) but inner predicate -- is used for a hash join between X1 and T6. select X0.a, X2.i from (select a,b from t4 union select a,b from t3) X0, (select i,j from (select i,j from t1 union select i,j from t2) X1, T6 where T6.p = X1.i) X2 where X0.b = X2.j ; A |I ----------------------- ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); 1 ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Statement Name: null Statement Text: -- Multiple subqueries that are UNIONs. Outer-most predicate -- X0.b = X2.j can be pushed to union X0 but NOT to subquery X2. -- Inner predicate T6.p = X1.i is eligible for being pushed into -- union X1. In this case outer predicate is pushed to X0 -- (so we'll see index scans on T3 and T4) but inner predicate -- is used for a hash join between X1 and T6. select X0.a, X2.i from (select a,b from t4 union select a,b from t3) X0, (select i,j from (select i,j from t1 union select i,j from t2) X1, T6 where T6.p = X1.i) X2 where X0.b = X2.j 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 (15): Number of opens = 1 Rows seen = 0 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 = 0 Rows seen from the right = 0 Rows filtered = 0 Rows returned = 0 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 Left result set: Project-Restrict ResultSet (8): Number of opens = 1 Rows seen = 0 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: Hash Join ResultSet: Number of opens = 1 Rows seen from the left = 7 Rows seen from the right = 0 Rows filtered = 0 Rows returned = 0 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 Left result set: Sort ResultSet: Number of opens = 1 Rows input = 10 Rows returned = 7 Eliminate duplicates = true In sorted order = false Sort information: Number of rows input=10 Number of rows output=7 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 Source result set: Union ResultSet: Number of opens = 1 Rows seen from the left = 5 Rows seen from the right = 5 Rows returned = 10 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 Left result set: Table Scan ResultSet for T1 at read committed isolation level using share row locking chosen by the optimizer Number of opens = 1 Rows seen = 5 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=All Number of columns fetched=2 Number of pages visited=2 Number of rows qualified=5 Number of rows visited=5 Scan type=heap start position: null stop position: null qualifiers: None Right result set: Table Scan ResultSet for T2 at read committed isolation level using share row locking chosen by the optimizer Number of opens = 1 Rows seen = 5 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=All Number of columns fetched=2 Number of pages visited=2 Number of rows qualified=5 Number of rows visited=5 Scan type=heap start position: null stop position: null qualifiers: None Right result set: Hash Scan ResultSet for T6 at read committed isolation level using instantaneous share row locking: Number of opens = 7 Hash table size = 0 Hash key is column number 0 Rows seen = 0 Rows filtered = 0 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 scan information: Bit set of columns fetched={0} Number of columns fetched=1 Number of pages visited=2 Number of rows qualified=0 Number of rows visited=0 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 Right result set: Sort ResultSet: Number of opens = 0 Rows input = 0 Rows returned = 0 Eliminate duplicates = true In sorted order = false Sort information: constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 Source result set: Union ResultSet: Number of opens = 0 Rows seen from the left = 0 Rows seen from the right = 0 Rows returned = 0 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 Left result set: Index Row to Base Row ResultSet for T4: Number of opens = 0 Rows seen = 0 Columns accessed from heap = {0} constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 Index Scan ResultSet for T4 using index T4_IX2 at read committed isolation level using share row locking chosen by the optimizer Number of opens = 0 Rows seen = 0 Rows filtered = 0 Fetch Size = 1 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 scan information: 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 Right result set: Index Row to Base Row ResultSet for T3: Number of opens = 0 Rows seen = 0 Columns accessed from heap = {0} constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 Index Scan ResultSet for T3 using index T3_IX2 at read committed isolation level using share row locking chosen by the optimizer Number of opens = 0 Rows seen = 0 Rows filtered = 0 Fetch Size = 1 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 scan information: 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> -- Same as above but without the inner predicate (so no -- hash on T6). select X0.a, X2.i from (select a,b from t4 union select a,b from t3) X0, (select i,j from (select i,j from t1 union select i,j from t2) X1, T6 ) X2 where X0.b = X2.j ; A |I ----------------------- ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); 1 ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Statement Name: null Statement Text: -- Same as above but without the inner predicate (so no -- hash on T6). select X0.a, X2.i from (select a,b from t4 union select a,b from t3) X0, (select i,j from (select i,j from t1 union select i,j from t2) X1, T6 ) X2 where X0.b = X2.j 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 (14): Number of opens = 1 Rows seen = 0 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 = 0 Rows seen from the right = 0 Rows filtered = 0 Rows returned = 0 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 = 7 Rows seen from the right = 0 Rows filtered = 0 Rows returned = 0 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 Left result set: Sort ResultSet: Number of opens = 1 Rows input = 10 Rows returned = 7 Eliminate duplicates = true In sorted order = false Sort information: Number of rows input=10 Number of rows output=7 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 Source result set: Union ResultSet: Number of opens = 1 Rows seen from the left = 5 Rows seen from the right = 5 Rows returned = 10 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 Left result set: Table Scan ResultSet for T1 at read committed isolation level using share row locking chosen by the optimizer Number of opens = 1 Rows seen = 5 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=All Number of columns fetched=2 Number of pages visited=2 Number of rows qualified=5 Number of rows visited=5 Scan type=heap start position: null stop position: null qualifiers: None Right result set: Table Scan ResultSet for T2 at read committed isolation level using share row locking chosen by the optimizer Number of opens = 1 Rows seen = 5 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=All Number of columns fetched=2 Number of pages visited=2 Number of rows qualified=5 Number of rows visited=5 Scan type=heap start position: null stop position: null qualifiers: None Right result set: Table Scan ResultSet for T6 at read committed isolation level using share row locking chosen by the optimizer Number of opens = 7 Rows seen = 0 Rows filtered = 0 Fetch Size = 1 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 scan information: Bit set of columns fetched={} Number of columns fetched=0 Number of pages visited=2 Number of rows qualified=0 Number of rows visited=0 Scan type=heap start position: null stop position: null qualifiers: None Right result set: Sort ResultSet: Number of opens = 0 Rows input = 0 Rows returned = 0 Eliminate duplicates = true In sorted order = false Sort information: constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 Source result set: Union ResultSet: Number of opens = 0 Rows seen from the left = 0 Rows seen from the right = 0 Rows returned = 0 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 Left result set: Index Row to Base Row ResultSet for T4: Number of opens = 0 Rows seen = 0 Columns accessed from heap = {0} constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 Index Scan ResultSet for T4 using index T4_IX2 at read committed isolation level using share row locking chosen by the optimizer Number of opens = 0 Rows seen = 0 Rows filtered = 0 Fetch Size = 1 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 scan information: 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 Right result set: Index Row to Base Row ResultSet for T3: Number of opens = 0 Rows seen = 0 Columns accessed from heap = {0} constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 Index Scan ResultSet for T3 using index T3_IX2 at read committed isolation level using share row locking chosen by the optimizer Number of opens = 0 Rows seen = 0 Rows filtered = 0 Fetch Size = 1 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 scan information: 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> -- Same as above, but without the outer predicate. Should see -- table scan on T3 and T4 (because nothing is pushed). select X0.a, X2.i from (select a,b from t4 union select a,b from t3) X0, (select i,j from (select i,j from t1 union select i,j from t2) X1, T6 where T6.p = X1.i) X2 ; A |I ----------------------- ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); 1 ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Statement Name: null Statement Text: -- Same as above, but without the outer predicate. Should see -- table scan on T3 and T4 (because nothing is pushed). select X0.a, X2.i from (select a,b from t4 union select a,b from t3) X0, (select i,j from (select i,j from t1 union select i,j from t2) X1, T6 where T6.p = X1.i) X2 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 (13): Number of opens = 1 Rows seen = 0 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 = 0 Rows seen from the right = 0 Rows filtered = 0 Rows returned = 0 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 Left result set: Project-Restrict ResultSet (8): Number of opens = 1 Rows seen = 0 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: Hash Join ResultSet: Number of opens = 1 Rows seen from the left = 7 Rows seen from the right = 0 Rows filtered = 0 Rows returned = 0 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 Left result set: Sort ResultSet: Number of opens = 1 Rows input = 10 Rows returned = 7 Eliminate duplicates = true In sorted order = false Sort information: Number of rows input=10 Number of rows output=7 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 Source result set: Union ResultSet: Number of opens = 1 Rows seen from the left = 5 Rows seen from the right = 5 Rows returned = 10 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 Left result set: Table Scan ResultSet for T1 at read committed isolation level using share row locking chosen by the optimizer Number of opens = 1 Rows seen = 5 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=All Number of columns fetched=2 Number of pages visited=2 Number of rows qualified=5 Number of rows visited=5 Scan type=heap start position: null stop position: null qualifiers: None Right result set: Table Scan ResultSet for T2 at read committed isolation level using share row locking chosen by the optimizer Number of opens = 1 Rows seen = 5 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=All Number of columns fetched=2 Number of pages visited=2 Number of rows qualified=5 Number of rows visited=5 Scan type=heap start position: null stop position: null qualifiers: None Right result set: Hash Scan ResultSet for T6 at read committed isolation level using instantaneous share row locking: Number of opens = 7 Hash table size = 0 Hash key is column number 0 Rows seen = 0 Rows filtered = 0 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 scan information: Bit set of columns fetched={0} Number of columns fetched=1 Number of pages visited=2 Number of rows qualified=0 Number of rows visited=0 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 Right result set: Sort ResultSet: Number of opens = 0 Rows input = 0 Rows returned = 0 Eliminate duplicates = true In sorted order = false Sort information: constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 Source result set: Union ResultSet: Number of opens = 0 Rows seen from the left = 0 Rows seen from the right = 0 Rows returned = 0 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 Left result set: Table Scan ResultSet for T4 at read committed isolation level using share row locking chosen by the optimizer Number of opens = 0 Rows seen = 0 Rows filtered = 0 Fetch Size = 1 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 scan information: start position: null stop position: null qualifiers: None Right result set: Table Scan ResultSet for T3 at read committed isolation level using share row locking chosen by the optimizer Number of opens = 0 Rows seen = 0 Rows filtered = 0 Fetch Size = 1 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 scan information: start position: null stop position: null qualifiers: None ij> -- Additional tests with VALUES clauses. Mostly just checking to make sure -- these queries compile and execute, and to ensure that all predicates are -- enforced even if they can't be pushed all the way down into a UNION. So -- we shouldn't get back any extra rows here. NOTE: Row order is not important -- in these queries, just so long as the correct rows are returned. call SYSCS_UTIL.SYSCS_SET_RUNTIMESTATISTICS(0); 0 rows inserted/updated/deleted ij> select * from (select * from t1 union select * from t2) x1, (values (2, 4), (3, 6), (4, 8)) x2 (a, b) where x1.i = x2.a; I |J |A |B ----------------------------------------------- 2 |-4 |2 |4 2 |4 |2 |4 3 |6 |3 |6 4 |-8 |4 |8 4 |8 |4 |8 ij> select * from (select * from t1 union (values (1, -1), (2, -2), (5, -5))) x1 (i, j), (values (2, 4), (3, 6), (4, 8)) x2 (a, b) where x1.i = x2.a; I |J |A |B ----------------------------------------------- 2 |-2 |2 |4 2 |4 |2 |4 3 |6 |3 |6 4 |8 |4 |8 ij> select * from (select * from t1 union all (values (1, -1), (2, -2), (5, -5))) x1 (i, j), (values (2, 4), (3, 6), (4, 8)) x2 (a, b) where x1.i = x2.a; I |J |A |B ----------------------------------------------- 2 |4 |2 |4 3 |6 |3 |6 4 |8 |4 |8 2 |-2 |2 |4 ij> select * from (select * from t1 union (values (1, -1), (2, -2), (5, -5))) x1 (i, j), (values (2, 4), (3, 6), (4, 8)) x2 (a, b) where x1.i = x2.a and x2.b = x1.j; I |J |A |B ----------------------------------------------- 2 |4 |2 |4 3 |6 |3 |6 4 |8 |4 |8 ij> select * from (values (2, -4), (3, -6), (4, -8) union values (1, -1), (2, -2), (5, -5) ) x1 (i, j), (values (2, 4), (3, 6), (4, 8)) x2 (a, b) where x1.i = x2.a; I |J |A |B ----------------------------------------------- 2 |-4 |2 |4 2 |-2 |2 |4 3 |-6 |3 |6 4 |-8 |4 |8 ij> select * from (values (2, -4), (3, -6), (4, -8) union values (1, -1), (2, -2), (5, -5) ) x1 (i, j), (values (2, 4), (3, 6), (4, 8)) x2 (a, b) where x1.i = x2.a and x2.b = x1.j; I |J |A |B ----------------------------------------------- ij> select * from (values (1, -1), (2, -2), (5, -5) union select * from t1) x1 (i,j), (values (2, 4), (3, 6), (4, 8)) x2 (a, b) where x1.i = x2.a; I |J |A |B ----------------------------------------------- 2 |-2 |2 |4 2 |4 |2 |4 3 |6 |3 |6 4 |8 |4 |8 ij> -- Clean up DERBY-805 objects. drop view v1; 0 rows inserted/updated/deleted ij> drop view v2; 0 rows inserted/updated/deleted ij> drop table t1; 0 rows inserted/updated/deleted ij> drop table t2; 0 rows inserted/updated/deleted ij> drop table t3; 0 rows inserted/updated/deleted ij> drop table t4; 0 rows inserted/updated/deleted ij> drop table t5; 0 rows inserted/updated/deleted ij> drop table t6; 0 rows inserted/updated/deleted ij> drop view xxunion; 0 rows inserted/updated/deleted ij> drop view yyunion; 0 rows inserted/updated/deleted ij> drop table xx1; 0 rows inserted/updated/deleted ij> drop table yy1; 0 rows inserted/updated/deleted ij> -- DERBY-1633: Nested UNIONs of views with different column orderings -- leads to incorrectly scoped predicates. We have a lot of different -- tables and views here to try to cover several different situations. -- Note that all of the views use DISTINCT because we don't want -- the views to be flattened and Derby doesn't flatten select -- queries with DISTINCT in them. CREATE TABLE "APP"."T1" ("I" INTEGER, "D" DOUBLE, "C" CHAR(10)); 0 rows inserted/updated/deleted ij> CREATE TABLE "APP"."T2" ("I2" INTEGER, "D2" DOUBLE, "C2" CHAR(10)); 0 rows inserted/updated/deleted ij> CREATE TABLE "APP"."T3" ("I3" INTEGER, "D3" DOUBLE, "C3" CHAR(10)); 0 rows inserted/updated/deleted ij> insert into t1 values (1, -1, '1'), (2, -2, '2'); 2 rows inserted/updated/deleted ij> insert into t2 values (2, -2, '2'), (4, -4, '4'), (8, -8, '8'); 3 rows inserted/updated/deleted ij> insert into t3 values (3, -3, '3'), (6, -6, '6'), (9, -9, '9'); 3 rows inserted/updated/deleted ij> CREATE TABLE "APP"."T4" ("C4" CHAR(10)); 0 rows inserted/updated/deleted ij> insert into t4 values '1', '2', '3', '4', '5', '6', '7', '8', '9'; 9 rows inserted/updated/deleted ij> insert into t4 select rtrim(c4) || rtrim(c4) from t4; 9 rows inserted/updated/deleted ij> CREATE TABLE "APP"."T5" ("I5" INTEGER, "D5" DOUBLE, "C5" CHAR(10)); 0 rows inserted/updated/deleted ij> CREATE TABLE "APP"."T6" ("I6" INTEGER, "D6" DOUBLE, "C6" CHAR(10)); 0 rows inserted/updated/deleted ij> insert into t5 values (100, 100.0, '100'), (200, 200.0, '200'), (300, 300.0, '300'); 3 rows inserted/updated/deleted ij> insert into t6 values (400, 400.0, '400'), (200, 200.0, '200'), (300, 300.0, '300'); 3 rows inserted/updated/deleted ij> create view v_keycol_at_pos_3 as select distinct i col1, d col2, c col3 from t1; 0 rows inserted/updated/deleted ij> create view v1_keycol_at_pos_2 as select distinct i2 col1, c2 col3, d2 col2 from t2; 0 rows inserted/updated/deleted ij> create view v2_keycol_at_pos_2 as select distinct i3 col1, c3 col3, d3 col2 from t3; 0 rows inserted/updated/deleted ij> create view v1_intersect as select distinct i5 col1, c5 col3, d5 col2 from t5; 0 rows inserted/updated/deleted ij> create view v2_intersect as select distinct i6 col1, c6 col3, d6 col2 from t6; 0 rows inserted/updated/deleted ij> create view v1_values as select distinct vals1 col1, vals2 col2, vals3 col3 from (values (321, 321.0, '321'), (432, 432.0, '432'), (654, 654.0, '654') ) VT(vals1, vals2, vals3); 0 rows inserted/updated/deleted ij> create view v_union as select distinct i col1, d col2, c col3 from t1 union select distinct i3 col1, d3 col2, c3 col3 from t3; 0 rows inserted/updated/deleted ij> -- Chain of UNIONs with left-most child as a view with a -- an RCL that is ordered differently than that of the -- UNIONs above it. The right child of the top-level -- node is a view that is a simple select from a table. create view topview as (select distinct 'other:' col0, vpos3.col3, vpos3.col1 from v_keycol_at_pos_3 vpos3 union select distinct 't2stuff:' col0, vpos2_1.col3, vpos2_1.col1 from v1_keycol_at_pos_2 vpos2_1 union select distinct 't3stuff:' col0, vpos2_2.col3, vpos2_2.col1 from v2_keycol_at_pos_2 vpos2_2 ); 0 rows inserted/updated/deleted ij> -- Chain of UNIONs with left-most child as a view with a -- an RCL that is ordered differently than that of the -- UNIONs above it. The right child of the top-level -- node is a view that is a select from yet another -- UNION node. create view topview2 as (select distinct 'other:' col0, vpos3.col3, vpos3.col1 from v_keycol_at_pos_3 vpos3 union select distinct 't2stuff:' col0, vpos2_1.col3, vpos2_1.col1 from v1_keycol_at_pos_2 vpos2_1 union select distinct 't3stuff:' col0, vpos2_2.col3, vpos2_2.col1 from v2_keycol_at_pos_2 vpos2_2 union select distinct 'morestuff:' col0, vu.col3, vu.col1 from v_union vu ); 0 rows inserted/updated/deleted ij> -- Chain of UNIONs with left-most child as a view with a -- an RCL that is ordered differently than that of the -- UNIONs above it. The left-most child of the last -- UNION in the chain is an INTERSECT node to which -- predicates cannot (currently) be pushed. In this -- case the intersect returns an empty result set. create view topview3 (col0, col3, col1) as (select distinct 'other:' col0, vpos3.col3, vpos3.col1 from v_keycol_at_pos_3 vpos3 intersect select distinct 't2stuff:' col0, vpos2_1.col3, vpos2_1.col1 from v1_keycol_at_pos_2 vpos2_1 union select distinct 't3stuff:' col0, vpos2_2.col3, vpos2_2.col1 from v2_keycol_at_pos_2 vpos2_2 union select distinct 'morestuff:' col0, vu.col3, vu.col1 from v_union vu ); 0 rows inserted/updated/deleted ij> -- Chain of UNIONs with left-most child as a view with a -- an RCL that is ordered differently than that of the -- UNIONs above it. The left-most child of the last -- UNION in the chain is an INTERSECT node to which -- predicates cannot (currently) be pushed. In this -- case the intersect returns a couple of rows. create view topview4 (col0, col3, col1) as (select distinct 'intersect:' col0, vi1.col3, vi1.col1 from v1_intersect vi1 intersect select distinct 'intersect:' col0, vi2.col3, vi2.col1 from v2_intersect vi2 union select distinct 't3stuff:' col0, vpos2_2.col3, vpos2_2.col1 from v2_keycol_at_pos_2 vpos2_2 union select distinct 'morestuff:' col0, vu.col3, vu.col1 from v_union vu ); 0 rows inserted/updated/deleted ij> -- Chain of UNIONs with left-most child as a view with a -- an RCL that is ordered differently than that of the -- UNIONs above it. The left-most child of the last -- UNION in the chain is a view that is a selet from -- a VALUES list (i.e. no base table). create view topview5 (col0, col3, col1) as (select distinct 'values:' col0, vv1.col3, vv1.col1 from v1_values vv1 union select distinct 'intersect:' col0, vi2.col3, vi2.col1 from v2_intersect vi2 union select distinct 't3stuff:' col0, vpos2_2.col3, vpos2_2.col1 from v2_keycol_at_pos_2 vpos2_2 union select distinct 'morestuff:' col0, vu.col3, vu.col1 from v_union vu ); 0 rows inserted/updated/deleted ij> -- All of the following queries failed at some point while finalizing -- the fix for DERBY-1633; some failed with error 42818, others -- failed with execution-time NPEs caused by incorrect (esp. double) -- remapping. The point here is to see how the top-level -- predicates are pushed through the nested unions to the -- bottom-most children. Use of LEFT JOINs with NESTEDLOOP -- effectively allows us to force the join order and thus to -- ensure the predicates are pushed to the desired top-level -- at execution time. All such queries are run once with -- NESTEDLOOP and once without, to make sure things work -- in both cases. select * from t4, topview where t4.c4 = topview.col3; C4 |COL0 |COL3 |COL1 ------------------------------------------ 1 |other: |1 |1 2 |other: |2 |2 2 |t2stuff:|2 |2 4 |t2stuff:|4 |4 8 |t2stuff:|8 |8 3 |t3stuff:|3 |3 6 |t3stuff:|6 |6 9 |t3stuff:|9 |9 ij> select * from t4, topview where topview.col3 = t4.c4; C4 |COL0 |COL3 |COL1 ------------------------------------------ 1 |other: |1 |1 2 |other: |2 |2 2 |t2stuff:|2 |2 4 |t2stuff:|4 |4 8 |t2stuff:|8 |8 3 |t3stuff:|3 |3 6 |t3stuff:|6 |6 9 |t3stuff:|9 |9 ij> select * from topview x1, topview where topview.col3 = x1.col3; COL0 |COL3 |COL1 |COL0 |COL3 |COL1 --------------------------------------------------------------- other: |1 |1 |other: |1 |1 other: |2 |2 |other: |2 |2 other: |2 |2 |t2stuff:|2 |2 t2stuff:|2 |2 |other: |2 |2 t2stuff:|2 |2 |t2stuff:|2 |2 t2stuff:|4 |4 |t2stuff:|4 |4 t2stuff:|8 |8 |t2stuff:|8 |8 t3stuff:|3 |3 |t3stuff:|3 |3 t3stuff:|6 |6 |t3stuff:|6 |6 t3stuff:|9 |9 |t3stuff:|9 |9 ij> select * from t4, topview2 where t4.c4 = topview2.col3; C4 |COL0 |COL3 |COL1 -------------------------------------------- 1 |morestuff:|1 |1 2 |morestuff:|2 |2 3 |morestuff:|3 |3 6 |morestuff:|6 |6 9 |morestuff:|9 |9 1 |other: |1 |1 2 |other: |2 |2 2 |t2stuff: |2 |2 4 |t2stuff: |4 |4 8 |t2stuff: |8 |8 3 |t3stuff: |3 |3 6 |t3stuff: |6 |6 9 |t3stuff: |9 |9 ij> select * from topview2 x1, topview where topview.col3 = x1.col3; COL0 |COL3 |COL1 |COL0 |COL3 |COL1 ----------------------------------------------------------------- morestuff:|1 |1 |other: |1 |1 morestuff:|2 |2 |other: |2 |2 morestuff:|2 |2 |t2stuff:|2 |2 morestuff:|3 |3 |t3stuff:|3 |3 morestuff:|6 |6 |t3stuff:|6 |6 morestuff:|9 |9 |t3stuff:|9 |9 other: |1 |1 |other: |1 |1 other: |2 |2 |other: |2 |2 other: |2 |2 |t2stuff:|2 |2 t2stuff: |2 |2 |other: |2 |2 t2stuff: |2 |2 |t2stuff:|2 |2 t2stuff: |4 |4 |t2stuff:|4 |4 t2stuff: |8 |8 |t2stuff:|8 |8 t3stuff: |3 |3 |t3stuff:|3 |3 t3stuff: |6 |6 |t3stuff:|6 |6 t3stuff: |9 |9 |t3stuff:|9 |9 ij> select * from t4 left join topview on t4.c4 = topview.col3; C4 |COL0 |COL3 |COL1 ------------------------------------------ 1 |other: |1 |1 2 |other: |2 |2 2 |t2stuff:|2 |2 3 |t3stuff:|3 |3 4 |t2stuff:|4 |4 5 |NULL |NULL |NULL 6 |t3stuff:|6 |6 7 |NULL |NULL |NULL 8 |t2stuff:|8 |8 9 |t3stuff:|9 |9 11 |NULL |NULL |NULL 22 |NULL |NULL |NULL 33 |NULL |NULL |NULL 44 |NULL |NULL |NULL 55 |NULL |NULL |NULL 66 |NULL |NULL |NULL 77 |NULL |NULL |NULL 88 |NULL |NULL |NULL 99 |NULL |NULL |NULL ij> select * from t4 left join topview --DERBY-PROPERTIES joinStrategy=NESTEDLOOP on t4.c4 = topview.col3; C4 |COL0 |COL3 |COL1 ------------------------------------------ 1 |other: |1 |1 2 |other: |2 |2 2 |t2stuff:|2 |2 3 |t3stuff:|3 |3 4 |t2stuff:|4 |4 5 |NULL |NULL |NULL 6 |t3stuff:|6 |6 7 |NULL |NULL |NULL 8 |t2stuff:|8 |8 9 |t3stuff:|9 |9 11 |NULL |NULL |NULL 22 |NULL |NULL |NULL 33 |NULL |NULL |NULL 44 |NULL |NULL |NULL 55 |NULL |NULL |NULL 66 |NULL |NULL |NULL 77 |NULL |NULL |NULL 88 |NULL |NULL |NULL 99 |NULL |NULL |NULL ij> select * from t4 left join topview on topview.col3 = t4.c4; C4 |COL0 |COL3 |COL1 ------------------------------------------ 1 |other: |1 |1 2 |other: |2 |2 2 |t2stuff:|2 |2 3 |t3stuff:|3 |3 4 |t2stuff:|4 |4 5 |NULL |NULL |NULL 6 |t3stuff:|6 |6 7 |NULL |NULL |NULL 8 |t2stuff:|8 |8 9 |t3stuff:|9 |9 11 |NULL |NULL |NULL 22 |NULL |NULL |NULL 33 |NULL |NULL |NULL 44 |NULL |NULL |NULL 55 |NULL |NULL |NULL 66 |NULL |NULL |NULL 77 |NULL |NULL |NULL 88 |NULL |NULL |NULL 99 |NULL |NULL |NULL ij> select * from t4 left join topview --DERBY-PROPERTIES joinStrategy=NESTEDLOOP on topview.col3 = t4.c4; C4 |COL0 |COL3 |COL1 ------------------------------------------ 1 |other: |1 |1 2 |other: |2 |2 2 |t2stuff:|2 |2 3 |t3stuff:|3 |3 4 |t2stuff:|4 |4 5 |NULL |NULL |NULL 6 |t3stuff:|6 |6 7 |NULL |NULL |NULL 8 |t2stuff:|8 |8 9 |t3stuff:|9 |9 11 |NULL |NULL |NULL 22 |NULL |NULL |NULL 33 |NULL |NULL |NULL 44 |NULL |NULL |NULL 55 |NULL |NULL |NULL 66 |NULL |NULL |NULL 77 |NULL |NULL |NULL 88 |NULL |NULL |NULL 99 |NULL |NULL |NULL ij> select * from topview x1 left join topview on topview.col3 = x1.col3; COL0 |COL3 |COL1 |COL0 |COL3 |COL1 --------------------------------------------------------------- other: |1 |1 |other: |1 |1 other: |2 |2 |other: |2 |2 other: |2 |2 |t2stuff:|2 |2 t2stuff:|2 |2 |other: |2 |2 t2stuff:|2 |2 |t2stuff:|2 |2 t2stuff:|4 |4 |t2stuff:|4 |4 t2stuff:|8 |8 |t2stuff:|8 |8 t3stuff:|3 |3 |t3stuff:|3 |3 t3stuff:|6 |6 |t3stuff:|6 |6 t3stuff:|9 |9 |t3stuff:|9 |9 ij> select * from topview x1 left join topview --DERBY-PROPERTIES joinStrategy=NESTEDLOOP on topview.col3 = x1.col3; COL0 |COL3 |COL1 |COL0 |COL3 |COL1 --------------------------------------------------------------- other: |1 |1 |other: |1 |1 other: |2 |2 |other: |2 |2 other: |2 |2 |t2stuff:|2 |2 t2stuff:|2 |2 |other: |2 |2 t2stuff:|2 |2 |t2stuff:|2 |2 t2stuff:|4 |4 |t2stuff:|4 |4 t2stuff:|8 |8 |t2stuff:|8 |8 t3stuff:|3 |3 |t3stuff:|3 |3 t3stuff:|6 |6 |t3stuff:|6 |6 t3stuff:|9 |9 |t3stuff:|9 |9 ij> select * from t4 left join topview2 on t4.c4 = topview2.col3; C4 |COL0 |COL3 |COL1 -------------------------------------------- 1 |morestuff:|1 |1 1 |other: |1 |1 2 |morestuff:|2 |2 2 |other: |2 |2 2 |t2stuff: |2 |2 3 |morestuff:|3 |3 3 |t3stuff: |3 |3 4 |t2stuff: |4 |4 5 |NULL |NULL |NULL 6 |morestuff:|6 |6 6 |t3stuff: |6 |6 7 |NULL |NULL |NULL 8 |t2stuff: |8 |8 9 |morestuff:|9 |9 9 |t3stuff: |9 |9 11 |NULL |NULL |NULL 22 |NULL |NULL |NULL 33 |NULL |NULL |NULL 44 |NULL |NULL |NULL 55 |NULL |NULL |NULL 66 |NULL |NULL |NULL 77 |NULL |NULL |NULL 88 |NULL |NULL |NULL 99 |NULL |NULL |NULL ij> select * from t4 left join topview2 --DERBY-PROPERTIES joinStrategy=NESTEDLOOP on t4.c4 = topview2.col3; C4 |COL0 |COL3 |COL1 -------------------------------------------- 1 |morestuff:|1 |1 1 |other: |1 |1 2 |morestuff:|2 |2 2 |other: |2 |2 2 |t2stuff: |2 |2 3 |morestuff:|3 |3 3 |t3stuff: |3 |3 4 |t2stuff: |4 |4 5 |NULL |NULL |NULL 6 |morestuff:|6 |6 6 |t3stuff: |6 |6 7 |NULL |NULL |NULL 8 |t2stuff: |8 |8 9 |morestuff:|9 |9 9 |t3stuff: |9 |9 11 |NULL |NULL |NULL 22 |NULL |NULL |NULL 33 |NULL |NULL |NULL 44 |NULL |NULL |NULL 55 |NULL |NULL |NULL 66 |NULL |NULL |NULL 77 |NULL |NULL |NULL 88 |NULL |NULL |NULL 99 |NULL |NULL |NULL ij> select * from topview2 x1 left join topview on topview.col3 = x1.col3; COL0 |COL3 |COL1 |COL0 |COL3 |COL1 ----------------------------------------------------------------- morestuff:|1 |1 |other: |1 |1 morestuff:|2 |2 |other: |2 |2 morestuff:|2 |2 |t2stuff:|2 |2 morestuff:|3 |3 |t3stuff:|3 |3 morestuff:|6 |6 |t3stuff:|6 |6 morestuff:|9 |9 |t3stuff:|9 |9 other: |1 |1 |other: |1 |1 other: |2 |2 |other: |2 |2 other: |2 |2 |t2stuff:|2 |2 t2stuff: |2 |2 |other: |2 |2 t2stuff: |2 |2 |t2stuff:|2 |2 t2stuff: |4 |4 |t2stuff:|4 |4 t2stuff: |8 |8 |t2stuff:|8 |8 t3stuff: |3 |3 |t3stuff:|3 |3 t3stuff: |6 |6 |t3stuff:|6 |6 t3stuff: |9 |9 |t3stuff:|9 |9 ij> select * from topview2 x1 left join topview --DERBY-PROPERTIES joinStrategy=NESTEDLOOP on topview.col3 = x1.col3; COL0 |COL3 |COL1 |COL0 |COL3 |COL1 ----------------------------------------------------------------- morestuff:|1 |1 |other: |1 |1 morestuff:|2 |2 |other: |2 |2 morestuff:|2 |2 |t2stuff:|2 |2 morestuff:|3 |3 |t3stuff:|3 |3 morestuff:|6 |6 |t3stuff:|6 |6 morestuff:|9 |9 |t3stuff:|9 |9 other: |1 |1 |other: |1 |1 other: |2 |2 |other: |2 |2 other: |2 |2 |t2stuff:|2 |2 t2stuff: |2 |2 |other: |2 |2 t2stuff: |2 |2 |t2stuff:|2 |2 t2stuff: |4 |4 |t2stuff:|4 |4 t2stuff: |8 |8 |t2stuff:|8 |8 t3stuff: |3 |3 |t3stuff:|3 |3 t3stuff: |6 |6 |t3stuff:|6 |6 t3stuff: |9 |9 |t3stuff:|9 |9 ij> select * from topview x1 left join topview2 on topview2.col3 = x1.col3; COL0 |COL3 |COL1 |COL0 |COL3 |COL1 ----------------------------------------------------------------- other: |1 |1 |morestuff:|1 |1 other: |1 |1 |other: |1 |1 other: |2 |2 |morestuff:|2 |2 other: |2 |2 |other: |2 |2 other: |2 |2 |t2stuff: |2 |2 t2stuff:|2 |2 |morestuff:|2 |2 t2stuff:|2 |2 |other: |2 |2 t2stuff:|2 |2 |t2stuff: |2 |2 t2stuff:|4 |4 |t2stuff: |4 |4 t2stuff:|8 |8 |t2stuff: |8 |8 t3stuff:|3 |3 |morestuff:|3 |3 t3stuff:|3 |3 |t3stuff: |3 |3 t3stuff:|6 |6 |morestuff:|6 |6 t3stuff:|6 |6 |t3stuff: |6 |6 t3stuff:|9 |9 |morestuff:|9 |9 t3stuff:|9 |9 |t3stuff: |9 |9 ij> select * from topview x1 left join topview2 --DERBY-PROPERTIES joinStrategy=NESTEDLOOP on topview2.col3 = x1.col3; COL0 |COL3 |COL1 |COL0 |COL3 |COL1 ----------------------------------------------------------------- other: |1 |1 |morestuff:|1 |1 other: |1 |1 |other: |1 |1 other: |2 |2 |morestuff:|2 |2 other: |2 |2 |other: |2 |2 other: |2 |2 |t2stuff: |2 |2 t2stuff:|2 |2 |morestuff:|2 |2 t2stuff:|2 |2 |other: |2 |2 t2stuff:|2 |2 |t2stuff: |2 |2 t2stuff:|4 |4 |t2stuff: |4 |4 t2stuff:|8 |8 |t2stuff: |8 |8 t3stuff:|3 |3 |morestuff:|3 |3 t3stuff:|3 |3 |t3stuff: |3 |3 t3stuff:|6 |6 |morestuff:|6 |6 t3stuff:|6 |6 |t3stuff: |6 |6 t3stuff:|9 |9 |morestuff:|9 |9 t3stuff:|9 |9 |t3stuff: |9 |9 ij> select * from topview x1 left join topview3 on topview3.col3 = x1.col3; COL0 |COL3 |COL1 |COL0 |COL3 |COL1 ----------------------------------------------------------------- other: |1 |1 |morestuff:|1 |1 other: |2 |2 |morestuff:|2 |2 t2stuff:|2 |2 |morestuff:|2 |2 t2stuff:|4 |4 |NULL |NULL |NULL t2stuff:|8 |8 |NULL |NULL |NULL t3stuff:|3 |3 |morestuff:|3 |3 t3stuff:|3 |3 |t3stuff: |3 |3 t3stuff:|6 |6 |morestuff:|6 |6 t3stuff:|6 |6 |t3stuff: |6 |6 t3stuff:|9 |9 |morestuff:|9 |9 t3stuff:|9 |9 |t3stuff: |9 |9 ij> select * from topview x1 left join topview3 --DERBY-PROPERTIES joinStrategy=NESTEDLOOP on topview3.col3 = x1.col3; COL0 |COL3 |COL1 |COL0 |COL3 |COL1 ----------------------------------------------------------------- other: |1 |1 |morestuff:|1 |1 other: |2 |2 |morestuff:|2 |2 t2stuff:|2 |2 |morestuff:|2 |2 t2stuff:|4 |4 |NULL |NULL |NULL t2stuff:|8 |8 |NULL |NULL |NULL t3stuff:|3 |3 |morestuff:|3 |3 t3stuff:|3 |3 |t3stuff: |3 |3 t3stuff:|6 |6 |morestuff:|6 |6 t3stuff:|6 |6 |t3stuff: |6 |6 t3stuff:|9 |9 |morestuff:|9 |9 t3stuff:|9 |9 |t3stuff: |9 |9 ij> select * from topview x1 left join topview4 on topview4.col3 = x1.col3; COL0 |COL3 |COL1 |COL0 |COL3 |COL1 ----------------------------------------------------------------- other: |1 |1 |morestuff:|1 |1 other: |2 |2 |morestuff:|2 |2 t2stuff:|2 |2 |morestuff:|2 |2 t2stuff:|4 |4 |NULL |NULL |NULL t2stuff:|8 |8 |NULL |NULL |NULL t3stuff:|3 |3 |morestuff:|3 |3 t3stuff:|3 |3 |t3stuff: |3 |3 t3stuff:|6 |6 |morestuff:|6 |6 t3stuff:|6 |6 |t3stuff: |6 |6 t3stuff:|9 |9 |morestuff:|9 |9 t3stuff:|9 |9 |t3stuff: |9 |9 ij> select * from topview x1 left join topview4 --DERBY-PROPERTIES joinStrategy=NESTEDLOOP on topview4.col3 = x1.col3; COL0 |COL3 |COL1 |COL0 |COL3 |COL1 ----------------------------------------------------------------- other: |1 |1 |morestuff:|1 |1 other: |2 |2 |morestuff:|2 |2 t2stuff:|2 |2 |morestuff:|2 |2 t2stuff:|4 |4 |NULL |NULL |NULL t2stuff:|8 |8 |NULL |NULL |NULL t3stuff:|3 |3 |morestuff:|3 |3 t3stuff:|3 |3 |t3stuff: |3 |3 t3stuff:|6 |6 |morestuff:|6 |6 t3stuff:|6 |6 |t3stuff: |6 |6 t3stuff:|9 |9 |morestuff:|9 |9 t3stuff:|9 |9 |t3stuff: |9 |9 ij> select * from topview x1 left join topview5 on topview5.col3 = x1.col3; COL0 |COL3 |COL1 |COL0 |COL3 |COL1 ----------------------------------------------------------------- other: |1 |1 |morestuff:|1 |1 other: |2 |2 |morestuff:|2 |2 t2stuff:|2 |2 |morestuff:|2 |2 t2stuff:|4 |4 |NULL |NULL |NULL t2stuff:|8 |8 |NULL |NULL |NULL t3stuff:|3 |3 |morestuff:|3 |3 t3stuff:|3 |3 |t3stuff: |3 |3 t3stuff:|6 |6 |morestuff:|6 |6 t3stuff:|6 |6 |t3stuff: |6 |6 t3stuff:|9 |9 |morestuff:|9 |9 t3stuff:|9 |9 |t3stuff: |9 |9 ij> select * from topview x1 left join topview5 --DERBY-PROPERTIES joinStrategy=NESTEDLOOP on topview5.col3 = x1.col3; COL0 |COL3 |COL1 |COL0 |COL3 |COL1 ----------------------------------------------------------------- other: |1 |1 |morestuff:|1 |1 other: |2 |2 |morestuff:|2 |2 t2stuff:|2 |2 |morestuff:|2 |2 t2stuff:|4 |4 |NULL |NULL |NULL t2stuff:|8 |8 |NULL |NULL |NULL t3stuff:|3 |3 |morestuff:|3 |3 t3stuff:|3 |3 |t3stuff: |3 |3 t3stuff:|6 |6 |morestuff:|6 |6 t3stuff:|6 |6 |t3stuff: |6 |6 t3stuff:|9 |9 |morestuff:|9 |9 t3stuff:|9 |9 |t3stuff: |9 |9 ij> -- DERBY-1681: -- In order to reproduce the issue described in DERBY-1681 -- we have to have a minimum amount of data in the tables; -- if we have too little, then somehow that affects the -- plan and we won't see the incorrect results. insert into t1 select * from t2; 3 rows inserted/updated/deleted ij> insert into t2 select * from t3; 3 rows inserted/updated/deleted ij> insert into t3 select * from t1; 5 rows inserted/updated/deleted ij> insert into t1 select * from t2; 6 rows inserted/updated/deleted ij> insert into t2 select * from t3; 8 rows inserted/updated/deleted ij> insert into t3 select * from t1; 11 rows inserted/updated/deleted ij> insert into t1 select * from t2; 14 rows inserted/updated/deleted ij> insert into t2 select * from t3; 19 rows inserted/updated/deleted ij> insert into t3 select * from t1; 25 rows inserted/updated/deleted ij> insert into t1 select * from t2; 33 rows inserted/updated/deleted ij> insert into t2 select * from t3; 44 rows inserted/updated/deleted ij> insert into t3 select * from t1; 58 rows inserted/updated/deleted ij> -- Now can just run one of the queries from DERBY-1633 to test -- the fix. Before DERBY-1681 this query would return 84 rows -- and it was clear that the predicate wasn't being enforced; -- after the fix, we should only see 42 rows and for every row -- the first and second column should be equal. select topview4.col3, x1.col3 from topview x1 left join topview4 on topview4.col3 = x1.col3; COL3 |COL3 --------------------- 1 |1 1 |1 2 |2 2 |2 3 |3 3 |3 4 |4 4 |4 6 |6 6 |6 8 |8 8 |8 9 |9 9 |9 1 |1 1 |1 2 |2 2 |2 3 |3 3 |3 4 |4 4 |4 6 |6 6 |6 8 |8 8 |8 9 |9 9 |9 1 |1 1 |1 2 |2 2 |2 3 |3 3 |3 4 |4 4 |4 6 |6 6 |6 8 |8 8 |8 9 |9 9 |9 ij> -- Clean-up from DERBY-1633 and DERBY-1681. drop view topview; 0 rows inserted/updated/deleted ij> drop view topview2; 0 rows inserted/updated/deleted ij> drop view topview3; 0 rows inserted/updated/deleted ij> drop view topview4; 0 rows inserted/updated/deleted ij> drop view topview5; 0 rows inserted/updated/deleted ij> drop view v_keycol_at_pos_3; 0 rows inserted/updated/deleted ij> drop view v1_keycol_at_pos_2; 0 rows inserted/updated/deleted ij> drop view v2_keycol_at_pos_2; 0 rows inserted/updated/deleted ij> drop view v1_intersect; 0 rows inserted/updated/deleted ij> drop view v2_intersect; 0 rows inserted/updated/deleted ij> drop view v1_values; 0 rows inserted/updated/deleted ij> drop view v_union; 0 rows inserted/updated/deleted ij> drop table t1; 0 rows inserted/updated/deleted ij> drop table t2; 0 rows inserted/updated/deleted ij> drop table t3; 0 rows inserted/updated/deleted ij> drop table t4; 0 rows inserted/updated/deleted ij> drop table t5; 0 rows inserted/updated/deleted ij> drop table t6; 0 rows inserted/updated/deleted ij>