db-derby-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From banda...@apache.org
Subject svn commit: r397682 [3/4] - in /db/derby/code/branches/10.1/java: engine/org/apache/derby/iapi/sql/compile/ engine/org/apache/derby/iapi/store/access/ engine/org/apache/derby/impl/sql/compile/ testing/org/apache/derbyTesting/functionTests/master/ testi...
Date Thu, 27 Apr 2006 23:42:04 GMT
Added: db/derby/code/branches/10.1/java/testing/org/apache/derbyTesting/functionTests/master/predicatePushdown.out
URL: http://svn.apache.org/viewcvs/db/derby/code/branches/10.1/java/testing/org/apache/derbyTesting/functionTests/master/predicatePushdown.out?rev=397682&view=auto
==============================================================================
--- db/derby/code/branches/10.1/java/testing/org/apache/derbyTesting/functionTests/master/predicatePushdown.out (added)
+++ db/derby/code/branches/10.1/java/testing/org/apache/derbyTesting/functionTests/master/predicatePushdown.out Thu Apr 27 16:42:02 2006
@@ -0,0 +1,10242 @@
+ij> -- 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> -- 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=1
+				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=1
+				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=1
+					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=1
+					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=1
+					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=1
+					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=1
+						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=1
+						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 = 20
+	Rows returned = 7
+	Eliminate duplicates = true
+	In sorted order = false
+	Sort information: 
+		Number of rows input=20
+		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 = 15
+		Rows seen from the right = 5
+		Rows returned = 20
+			constructor time (milliseconds) = 0
+			open time (milliseconds) = 0
+			next time (milliseconds) = 0
+			close time (milliseconds) = 0
+		Left result set:
+			Union ResultSet:
+			Number of opens = 1
+			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:
+				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=1
+						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=1
+						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=1
+					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=1
+				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 (16):
+	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 = 43
+		Rows returned = 21
+		Eliminate duplicates = true
+		In sorted order = false
+		Sort information: 
+			Number of rows input=43
+			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 = 31
+			Rows seen from the right = 12
+			Rows returned = 43
+				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 = 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=1
+						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=1
+						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=1
+					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=1
+			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=1
+			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=1
+					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=1
+					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=1
+				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=1
+				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=1
+				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=1
+				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=1
+			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=1
+			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=1
+				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=1
+				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> -- 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 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> -- 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 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> -- 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=1
+				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=1
+				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:
+			Project-Restrict ResultSet (10):
+			Number of opens = 7
+			Rows seen = 2
+			Rows filtered = 0
+			restriction = false
+			projection = false
+				constructor time (milliseconds) = 0
+				open time (milliseconds) = 0
+				next time (milliseconds) = 0
+				close time (milliseconds) = 0
+				restriction time (milliseconds) = 0
+				projection time (milliseconds) = 0
+			Source result set:
+				Index 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:
+			Project-Restrict ResultSet (13):
+			Number of opens = 7
+			Rows seen = 0
+			Rows filtered = 0
+			restriction = false
+			projection = false
+				constructor time (milliseconds) = 0
+				open time (milliseconds) = 0
+				next time (milliseconds) = 0
+				close time (milliseconds) = 0
+				restriction time (milliseconds) = 0
+				projection time (milliseconds) = 0
+			Source result set:
+				Index 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 (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:
+	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=1
+					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=1
+					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:
+				Project-Restrict ResultSet (10):
+				Number of opens = 7
+				Rows seen = 2
+				Rows filtered = 0
+				restriction = false
+				projection = false
+					constructor time (milliseconds) = 0
+					open time (milliseconds) = 0
+					next time (milliseconds) = 0
+					close time (milliseconds) = 0
+					restriction time (milliseconds) = 0
+					projection time (milliseconds) = 0
+				Source result set:
+					Index 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:
+				Project-Restrict ResultSet (13):
+				Number of opens = 7
+				Rows seen = 0
+				Rows filtered = 0
+				restriction = false
+				projection = false
+					constructor time (milliseconds) = 0
+					open time (milliseconds) = 0
+					next time (milliseconds) = 0
+					close time (milliseconds) = 0
+					restriction time (milliseconds) = 0
+					projection time (milliseconds) = 0
+				Source result set:
+					Index 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: 

[... 8128 lines stripped ...]


Mime
View raw message