predicatesintoviews.out

derby database source code.good for you.

ij> -- test the pushing of predicates into unflattened views
-- and derived tables
set isolation to rr;
0 rows inserted/updated/deleted
ij> -- by default, holdability of ResultSet objects created using this Connection object is true. Following will set it to false for this connection.
NoHoldForConnection;
ij> -- set up
create table t1(c1 int, c2 int, c3 varchar(2000));
0 rows inserted/updated/deleted
ij> -- simple views
create view sv1 (c1, c2, c3) as select c1, c2, c3 || '$' from t1;
0 rows inserted/updated/deleted
ij> create view sv2 (x1, x2, x3) as select c1, c2, c3  || '$' from t1;
0 rows inserted/updated/deleted
ij> create view sv3 (y1, y2, y3) as select x1, x2, x3  || '$' from sv2;
0 rows inserted/updated/deleted
ij> create view sv4 (z1, z2, z3, z4, z5, z6) as
select a.c1, a.c2, a.c3 || '$', b.c1, b.c2, b.c3 || '$' from t1 a, t1 b;
0 rows inserted/updated/deleted
ij> -- more complex views
create view av1 (c1, c2) as select c1, max(c2) from t1 group by c1;
0 rows inserted/updated/deleted
ij> create view av2 (x1, x2) as select c1, max(c2) from av1 group by c1;
0 rows inserted/updated/deleted
ij> create view av3 (y1, y2, y3, y4) as
select a.c1, b.c1, max(a.c2), max(b.c2) from t1 a, t1 b group by a.c1, b.c1;
0 rows inserted/updated/deleted
ij> -- non-flattenable derived table in a non-flattenable view
create view cv1 (c1, c2) as
select c1, max(c2) from (select c1, c2 + 1 from t1) t(c1, c2) group by c1;
0 rows inserted/updated/deleted
ij> -- populate the tables
insert into t1 values (1, 1, ''), (1, 1, ''), (1, 2, ''), (1, 2, ''),
		      (2, 2, ''), (2, 2, ''), (2, 3, ''), (2, 3, '');
8 rows inserted/updated/deleted
ij> call SYSCS_UTIL.SYSCS_SET_RUNTIMESTATISTICS(1);
0 rows inserted/updated/deleted
ij> maximumdisplaywidth 20000;
ij> -- predicate should get pushed into scan
select c1, c2 from sv1 where c1 = 1 order by c1, c2;
C1         |C2         
-----------------------
1          |1          
1          |1          
1          |2          
1          |2          
ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
1                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Statement Name: 
	null
Statement Text: 
	-- predicate should get pushed into scan
select c1, c2 from sv1 where c1 = 1 order by c1, c2
Parse Time: 0
Bind Time: 0
Optimize Time: 0
Generate Time: 0
Compile Time: 0
Execute Time: 0
Begin Compilation Timestamp : null
End Compilation Timestamp : null
Begin Execution Timestamp : null
End Execution Timestamp : null
Statement Execution Plan Text: 
Sort ResultSet:
Number of opens = 1
Rows input = 4
Rows returned = 4
Eliminate duplicates = false
In sorted order = false
Sort information: 
	Number of rows input=4
	Number of rows output=4
	constructor time (milliseconds) = 0
	open time (milliseconds) = 0
	next time (milliseconds) = 0
	close time (milliseconds) = 0
Source result set:
	Project-Restrict ResultSet (3):
	Number of opens = 1
	Rows seen = 4
	Rows filtered = 0
	restriction = false
	projection = true
		constructor time (milliseconds) = 0
		open time (milliseconds) = 0
		next time (milliseconds) = 0
		close time (milliseconds) = 0
		restriction time (milliseconds) = 0
		projection time (milliseconds) = 0
	Source result set:
		Project-Restrict ResultSet (2):
		Number of opens = 1
		Rows seen = 4
		Rows filtered = 0
		restriction = false
		projection = true
			constructor time (milliseconds) = 0
			open time (milliseconds) = 0
			next time (milliseconds) = 0
			close time (milliseconds) = 0
			restriction time (milliseconds) = 0
			projection time (milliseconds) = 0
		Source result set:
			Table Scan ResultSet for T1 at serializable isolation level using share table locking chosen by the optimizer
			Number of opens = 1
			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=3
				Number of pages visited=1
				Number of rows qualified=4
				Number of rows visited=8
				Scan type=heap
				start position: 
null				stop position: 
null				qualifiers:
Column[0][0] Id: 0
Operator: =
Ordered nulls: false
Unknown return value: false
Negate comparison result: false
ij> select c1, c2 from sv1 where c1 = 1  + 1 order by c1, c2;
C1         |C2         
-----------------------
2          |2          
2          |2          
2          |3          
2          |3          
ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
1                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Statement Name: 
	null
Statement Text: 
	select c1, c2 from sv1 where c1 = 1  + 1 order by c1, c2
Parse Time: 0
Bind Time: 0
Optimize Time: 0
Generate Time: 0
Compile Time: 0
Execute Time: 0
Begin Compilation Timestamp : null
End Compilation Timestamp : null
Begin Execution Timestamp : null
End Execution Timestamp : null
Statement Execution Plan Text: 
Sort ResultSet:
Number of opens = 1
Rows input = 4
Rows returned = 4
Eliminate duplicates = false
In sorted order = false
Sort information: 
	Number of rows input=4
	Number of rows output=4
	constructor time (milliseconds) = 0
	open time (milliseconds) = 0
	next time (milliseconds) = 0
	close time (milliseconds) = 0
Source result set:
	Project-Restrict ResultSet (3):
	Number of opens = 1
	Rows seen = 4
	Rows filtered = 0
	restriction = false
	projection = true
		constructor time (milliseconds) = 0
		open time (milliseconds) = 0
		next time (milliseconds) = 0
		close time (milliseconds) = 0
		restriction time (milliseconds) = 0
		projection time (milliseconds) = 0
	Source result set:
		Project-Restrict ResultSet (2):
		Number of opens = 1
		Rows seen = 4
		Rows filtered = 0
		restriction = false
		projection = true
			constructor time (milliseconds) = 0
			open time (milliseconds) = 0
			next time (milliseconds) = 0
			close time (milliseconds) = 0
			restriction time (milliseconds) = 0
			projection time (milliseconds) = 0
		Source result set:
			Table Scan ResultSet for T1 at serializable isolation level using share table locking chosen by the optimizer
			Number of opens = 1
			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=3
				Number of pages visited=1
				Number of rows qualified=4
				Number of rows visited=8
				Scan type=heap
				start position: 
null				stop position: 
null				qualifiers:
Column[0][0] Id: 0
Operator: =
Ordered nulls: false
Unknown return value: false
Negate comparison result: false
ij> select x1, x2 from sv2 where x1 = 1 order by x1, x2;
X1         |X2         
-----------------------
1          |1          
1          |1          
1          |2          
1          |2          
ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
1                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Statement Name: 
	null
Statement Text: 
	select x1, x2 from sv2 where x1 = 1 order by x1, x2
Parse Time: 0
Bind Time: 0
Optimize Time: 0
Generate Time: 0
Compile Time: 0
Execute Time: 0
Begin Compilation Timestamp : null
End Compilation Timestamp : null
Begin Execution Timestamp : null
End Execution Timestamp : null
Statement Execution Plan Text: 
Sort ResultSet:
Number of opens = 1
Rows input = 4
Rows returned = 4
Eliminate duplicates = false
In sorted order = false
Sort information: 
	Number of rows input=4
	Number of rows output=4
	constructor time (milliseconds) = 0
	open time (milliseconds) = 0
	next time (milliseconds) = 0
	close time (milliseconds) = 0
Source result set:
	Project-Restrict ResultSet (3):
	Number of opens = 1
	Rows seen = 4
	Rows filtered = 0
	restriction = false
	projection = true
		constructor time (milliseconds) = 0
		open time (milliseconds) = 0
		next time (milliseconds) = 0
		close time (milliseconds) = 0
		restriction time (milliseconds) = 0
		projection time (milliseconds) = 0