predicatepushdown.out

derby database source code.good for you.

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