subqueryflattening.out

derby database source code.good for you.

ij> -- test subquery flattening into outer query block
set isolation to rr;
0 rows inserted/updated/deleted
ij> -- tests for flattening a subquery based on a
-- uniqueness condition
-- by default, holdability of ResultSet objects created using this Connection object is true. Following will set it to false for this connection.
NoHoldForConnection;
ij> -- create some tables
create table outer1 (c1 int, c2 int, c3 int);
0 rows inserted/updated/deleted
ij> create table outer2 (c1 int, c2 int, c3 int);
0 rows inserted/updated/deleted
ij> create table noidx (c1 int);
0 rows inserted/updated/deleted
ij> create table idx1 (c1 int);
0 rows inserted/updated/deleted
ij> create unique index idx1_1 on idx1(c1);
0 rows inserted/updated/deleted
ij> create table idx2 (c1 int, c2 int);
0 rows inserted/updated/deleted
ij> create unique index idx2_1 on idx2(c1, c2);
0 rows inserted/updated/deleted
ij> create table nonunique_idx1 (c1 int);
0 rows inserted/updated/deleted
ij> create index nonunique_idx1_1 on nonunique_idx1(c1);
0 rows inserted/updated/deleted
ij> insert into outer1 values (1, 2, 3);
1 row inserted/updated/deleted
ij> insert into outer1 values (4, 5, 6);
1 row inserted/updated/deleted
ij> insert into outer2 values (1, 2, 3);
1 row inserted/updated/deleted
ij> insert into outer2 values (4, 5, 6);
1 row inserted/updated/deleted
ij> insert into noidx values 1, 1;
2 rows inserted/updated/deleted
ij> insert into idx1 values 1, 2;
2 rows inserted/updated/deleted
ij> insert into idx2 values (1, 1), (1, 2);
2 rows inserted/updated/deleted
ij> insert into nonunique_idx1 values 1, 1;
2 rows inserted/updated/deleted
ij> -- cases where subqueries don't get flattened
-- (we would get incorrect results with 
-- incorrect flattening)
-- one of tables in subquery doesn't have index
select * from outer1 where c1 in (select idx1.c1 from noidx, idx1 where idx1.c1 = noidx.c1);
C1         |C2         |C3         
-----------------------------------
1          |2          |3          
ij> -- group by in subquery
select * from outer1 o where c1 <= (select c1 from idx1 i group by c1);
C1         |C2         |C3         
-----------------------------------
ERROR 21000: Scalar subquery is only allowed to return a single row.
ij> -- otherwise flattenable subquery under an or 
-- subquery returns no rows
select * from outer1 o where c1 + 0 = 1 or c1 in (select c1 from idx1 i where i.c1 = 0);
C1         |C2         |C3         
-----------------------------------
1          |2          |3          
ij> select * from outer1 o where c1 in (select c1 from idx1 i where i.c1 = 0) or c1 + 0 = 1;
C1         |C2         |C3         
-----------------------------------
1          |2          |3          
ij> -- empty subquery in select list which is otherwise flattenable
select (select c1 from idx1 where c1 = 0) from outer1;
1          
-----------
NULL       
NULL       
ij> -- multiple tables in subquery
-- no one table's equality condition based
-- solely on constants and correlation columns
select * from outer1 o where exists (select * from idx2 i, idx1 where o.c1 = i.c1 and i.c2 = idx1.c1);
C1         |C2         |C3         
-----------------------------------
1          |2          |3          
ij> -- subqueries that should get flattened
call SYSCS_UTIL.SYSCS_SET_RUNTIMESTATISTICS(1);
0 rows inserted/updated/deleted
ij> maximumdisplaywidth 40000;
ij> -- simple IN
select * from outer1 o where o.c1 in (select c1 from idx1);
C1         |C2         |C3         
-----------------------------------
1          |2          |3          
ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
1                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Statement Name: 
	null
Statement Text: 
	-- simple IN
select * from outer1 o where o.c1 in (select c1 from idx1)
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 (4):
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:
	Nested Loop Exists Join ResultSet:
	Number of opens = 1
	Rows seen from the left = 2
	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:
		Table Scan ResultSet for OUTER1 at serializable isolation level using share table locking chosen by the optimizer
		Number of opens = 1
		Rows seen = 2
		Rows filtered = 0
		Fetch Size = 16
			constructor time (milliseconds) = 0
			open time (milliseconds) = 0
			next time (milliseconds) = 0
			close time (milliseconds) = 0
			next time in milliseconds/row = 0
		scan information: 
			Bit set of columns fetched=All
			Number of columns fetched=3
			Number of pages visited=1
			Number of rows qualified=2
			Number of rows visited=2
			Scan type=heap
			start position: 
null			stop position: 
null			qualifiers:
None
	Right result set:
		Index Scan ResultSet for IDX1 using index IDX1_1 at serializable isolation level using share row locking chosen by the optimizer
		Number of opens = 2
		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={0}
			Number of columns fetched=1
			Number of deleted rows visited=0
			Number of pages visited=2
			Number of rows qualified=1
			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> -- simple EXISTS
select * from outer1 o where exists (select * from idx1 i where o.c1 = i.c1);
C1         |C2         |C3         
-----------------------------------
1          |2          |3          
ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
1                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Statement Name: 
	null
Statement Text: 
	-- simple EXISTS
select * from outer1 o where exists (select * from idx1 i where o.c1 = i.c1)
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 (4):
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:
	Nested Loop Exists Join ResultSet:
	Number of opens = 1
	Rows seen from the left = 2
	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:
		Table Scan ResultSet for OUTER1 at serializable isolation level using share table locking chosen by the optimizer
		Number of opens = 1
		Rows seen = 2
		Rows filtered = 0
		Fetch Size = 16
			constructor time (milliseconds) = 0
			open time (milliseconds) = 0
			next time (milliseconds) = 0
			close time (milliseconds) = 0
			next time in milliseconds/row = 0
		scan information: 
			Bit set of columns fetched=All
			Number of columns fetched=3
			Number of pages visited=1
			Number of rows qualified=2
			Number of rows visited=2
			Scan type=heap
			start position: 
null			stop position: 
null			qualifiers:
None
	Right result set:
		Index Scan ResultSet for IDX1 using index IDX1_1 at serializable isolation level using share row locking chosen by the optimizer
		Number of opens = 2
		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={0}
			Number of columns fetched=1
			Number of deleted rows visited=0
			Number of pages visited=2
			Number of rows qualified=1
			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> -- simple ANY