db-derby-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From banda...@apache.org
Subject svn commit: r382940 [2/3] - in /db/derby/code/trunk/java: engine/org/apache/derby/impl/sql/compile/ testing/org/apache/derbyTesting/functionTests/master/ testing/org/apache/derbyTesting/functionTests/suites/ testing/org/apache/derbyTesting/functionTest...
Date Fri, 03 Mar 2006 21:56:11 GMT
Added: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/predicatePushdown.out
URL: http://svn.apache.org/viewcvs/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/predicatePushdown.out?rev=382940&view=auto
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/predicatePushdown.out (added)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/predicatePushdown.out Fri Mar  3 13:56:07 2006
@@ -0,0 +1,8720 @@
+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 tables/views for DERBY-805 testing.  For DERBY-805
+-- 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.
+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> 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> 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> 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 > 10;
+90 rows inserted/updated/deleted
+ij> CREATE TABLE "APP"."T4" ("A" INTEGER, "B" INTEGER);
+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> 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> insert into t4 (a) values 21, 22, 23, 24, 25, 26, 27, 28, 29, 30;
+10 rows inserted/updated/deleted
+ij> insert into t4 (a) values 31, 32, 33, 34, 35, 36, 37, 38, 39, 40;
+10 rows inserted/updated/deleted
+ij> insert into t4 (a) values 41, 42, 43, 44, 45, 46, 47, 48, 49, 50;
+10 rows inserted/updated/deleted
+ij> insert into t4 (a) values 51, 52, 53, 54, 55, 56, 57, 58, 59, 60;
+10 rows inserted/updated/deleted
+ij> insert into t4 (a) values 61, 62, 63, 64, 65, 66, 67, 68, 69, 70;
+10 rows inserted/updated/deleted
+ij> insert into t4 (a) values 71, 72, 73, 74, 75, 76, 77, 78, 79, 80;
+10 rows inserted/updated/deleted
+ij> insert into t4 (a) values 81, 82, 83, 84, 85, 86, 87, 88, 89, 90;
+10 rows inserted/updated/deleted
+ij> insert into t4 (a) values 91, 92, 93, 94, 95, 96, 97, 98, 99, 100;
+10 rows inserted/updated/deleted
+ij> update t4 set b = 2 * a where a > 10;
+90 rows inserted/updated/deleted
+ij> 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 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> 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> call SYSCS_UTIL.SYSCS_SET_RUNTIMESTATISTICS(1);
+0 rows inserted/updated/deleted
+ij> maximumdisplaywidth 15000;
+ij> -- 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: 
+	-- 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=1
+						Number of rows qualified=0
+						Number of rows visited=1
+						Scan type=btree
+						Tree height=1
+						start position: 
+	>= on first 1 column(s).
+	Ordered null semantics on the following columns: 
+						stop position: 
+	> on first 1 column(s).
+	Ordered null semantics on the following columns: 
+						qualifiers:
+None
+		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=1
+						Number of rows qualified=0
+						Number of rows visited=1
+						Scan type=btree
+						Tree height=1
+						start position: 
+	>= on first 1 column(s).
+	Ordered null semantics on the following columns: 
+						stop position: 
+	> on first 1 column(s).
+	Ordered null semantics on the following columns: 
+						qualifiers:
+None
+ij> 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=1
+							Number of rows qualified=0
+							Number of rows visited=1
+							Scan type=btree
+							Tree height=1
+							start position: 
+	>= on first 1 column(s).
+	Ordered null semantics on the following columns: 
+							stop position: 
+	> on first 1 column(s).
+	Ordered null semantics on the following columns: 
+							qualifiers:
+None
+			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=1
+							Number of rows qualified=0
+							Number of rows visited=1
+							Scan type=btree
+							Tree height=1
+							start position: 
+	>= on first 1 column(s).
+	Ordered null semantics on the following columns: 
+							stop position: 
+	> on first 1 column(s).
+	Ordered null semantics on the following columns: 
+							qualifiers:
+None
+ij> -- 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, and there should be a
+-- qualifier on T1 for the scalar predicate.
+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, and there should be a
+-- qualifier on T1 for the scalar predicate.
+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 = 3
+							Rows filtered = 1
+							restriction = true
+							projection = false
+								constructor time (milliseconds) = 0
+								open time (milliseconds) = 0
+								next time (milliseconds) = 0
+								close time (milliseconds) = 0
+								restriction time (milliseconds) = 0
+								projection time (milliseconds) = 0
+							Source result set:
+								Table Scan ResultSet for T1 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=1
+									Number of rows qualified=3
+									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
+Column[0][1] Id: 0
+Operator: <=
+Ordered nulls: false
+Unknown return value: false
+Negate comparison result: false
+						Right result set:
+							Project-Restrict ResultSet (8):
+							Number of opens = 1
+							Rows seen = 3
+							Rows filtered = 1
+							restriction = true
+							projection = false
+								constructor time (milliseconds) = 0
+								open time (milliseconds) = 0
+								next time (milliseconds) = 0
+								close time (milliseconds) = 0
+								restriction time (milliseconds) = 0
+								projection time (milliseconds) = 0
+							Source result set:
+								Table Scan ResultSet for 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=1
+									Number of rows qualified=3
+									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
+Column[0][1] Id: 0
+Operator: <=
+Ordered nulls: false
+Unknown return value: false
+Negate comparison result: false
+			Right result set:
+				Sort ResultSet:
+				Number of opens = 4
+				Rows input = 764
+				Rows returned = 404
+				Eliminate duplicates = true
+				In sorted order = false
+				Sort information: 
+					Number of rows input=191
+					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 = 396
+					Rows seen from the right = 368
+					Rows returned = 764
+						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 = 396
+						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=99
+							Number of rows visited=99
+							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 = 368
+						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=92
+							Number of rows visited=92
+							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=1
+								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=1
+								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 = 955
+				Rows returned = 505
+				Eliminate duplicates = true
+				In sorted order = false
+				Sort information: 
+					Number of rows input=191
+					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 = 495
+					Rows seen from the right = 460
+					Rows returned = 955
+						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 = 495
+						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=99
+							Number of rows visited=99
+							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 = 460
+						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=92
+							Number of rows visited=92
+							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 = 3
+				Rows filtered = 1
+				restriction = true
+				projection = false
+					constructor time (milliseconds) = 0
+					open time (milliseconds) = 0
+					next time (milliseconds) = 0
+					close time (milliseconds) = 0
+					restriction time (milliseconds) = 0
+					projection time (milliseconds) = 0
+				Source result set:
+					Table Scan ResultSet for T1 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=1
+						Number of rows qualified=3
+						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
+Column[0][1] Id: 0
+Operator: <=
+Ordered nulls: false
+Unknown return value: false
+Negate comparison result: false
+			Right result set:
+				Project-Restrict ResultSet (7):
+				Number of opens = 1
+				Rows seen = 3
+				Rows filtered = 1
+				restriction = true
+				projection = false
+					constructor time (milliseconds) = 0
+					open time (milliseconds) = 0
+					next time (milliseconds) = 0
+					close time (milliseconds) = 0
+					restriction time (milliseconds) = 0
+					projection time (milliseconds) = 0
+				Source result set:
+					Table Scan ResultSet for 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=1
+						Number of rows qualified=3
+						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
+Column[0][1] Id: 0
+Operator: <=
+Ordered nulls: false
+Unknown return value: false
+Negate comparison result: false
+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:
+			Project-Restrict ResultSet (13):
+			Number of opens = 4
+			Rows seen = 1
+			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 = 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=1
+						Number of rows qualified=0
+						Number of rows visited=1
+						Scan type=btree
+						Tree height=1
+						start position: 
+	>= on first 1 column(s).
+	Ordered null semantics on the following columns: 
+						stop position: 
+	> on first 1 column(s).
+	Ordered null semantics on the following columns: 
+						qualifiers:
+None
+		Right result set:
+			Project-Restrict ResultSet (16):
+			Number of opens = 4
+			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 = 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=1
+						Number of rows qualified=0
+						Number of rows visited=1
+						Scan type=btree
+						Tree height=1
+						start position: 
+	>= on first 1 column(s).
+	Ordered null semantics on the following columns: 
+						stop position: 
+	> on first 1 column(s).
+	Ordered null semantics on the following columns: 
+						qualifiers:
+None
+ij> -- 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=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 = 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:
+			Project-Restrict ResultSet (10):
+			Number of opens = 7
+			Rows seen = 6
+			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 = 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=1
+						Number of rows qualified=0
+						Number of rows visited=1
+						Scan type=btree
+						Tree height=1
+						start position: 
+	>= on first 1 column(s).
+	Ordered null semantics on the following columns: 
+						stop position: 
+	> on first 1 column(s).
+	Ordered null semantics on the following columns: 
+						qualifiers:
+None
+		Right result set:
+			Project-Restrict ResultSet (13):
+			Number of opens = 7
+			Rows seen = 3
+			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 = 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=1
+						Number of rows qualified=0
+						Number of rows visited=1
+						Scan type=btree
+						Tree height=1
+						start position: 
+	>= on first 1 column(s).
+	Ordered null semantics on the following columns: 
+						stop position: 
+	> on first 1 column(s).
+	Ordered null semantics on the following columns: 
+						qualifiers:
+None
+ij> -- In this case optimizer will consider pushing predicate to X1 but will
+-- choose not to because it's cheaper to do a hash join between X1 and T3.
+-- So should see regular table scans on T1 and T2 with hash scan on T3.
+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 do a hash join between X1 and T3.
+-- So should see regular table scans on T1 and T2 with hash scan on T3.
+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: 
+Hash 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=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 Scan ResultSet for T3 at read committed isolation level using instantaneous share row locking: 
+	Number of opens = 7
+	Hash table size = 99
+	Hash key is column number 0
+	Rows seen = 6
+	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=1
+		Number of rows qualified=99
+		Number of rows visited=99
+		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> -- 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=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 = 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:
+			Project-Restrict ResultSet (9):
+			Number of opens = 10
+			Rows seen = 8
+			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 = 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=1
+						Number of rows qualified=0
+						Number of rows visited=1
+						Scan type=btree
+						Tree height=1
+						start position: 
+	>= on first 1 column(s).
+	Ordered null semantics on the following columns: 
+						stop position: 
+	> on first 1 column(s).
+	Ordered null semantics on the following columns: 
+						qualifiers:
+None
+		Right result set:
+			Project-Restrict ResultSet (12):
+			Number of opens = 10
+			Rows seen = 4
+			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 = 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=1
+						Number of rows qualified=0
+						Number of rows visited=1
+						Scan type=btree
+						Tree height=1
+						start position: 
+	>= on first 1 column(s).
+	Ordered null semantics on the following columns: 
+						stop position: 
+	> on first 1 column(s).
+	Ordered null semantics on the following columns: 
+						qualifiers:
+None
+ij> 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=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:
+	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:
+		Project-Restrict ResultSet (8):
+		Number of opens = 10
+		Rows seen = 8
+		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 = 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=1
+					Number of rows qualified=0

[... 6602 lines stripped ...]


Mime
View raw message