inbetween.out

derby database source code.good for you.

End Execution Timestamp : null
Statement Execution Plan Text: 
Project-Restrict ResultSet (5):
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 Exists Join ResultSet:
	Number of opens = 1
	Rows seen from the left = 4
	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:
		Table Scan ResultSet for T2 at serializable isolation level using share table locking chosen by the optimizer
		Number of opens = 1
		Rows seen = 4
		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=2
			Number of pages visited=1
			Number of rows qualified=4
			Number of rows visited=4
			Scan type=heap
			start position: 
null			stop position: 
null			qualifiers:
None
	Right result set:
		Project-Restrict ResultSet (4):
		Number of opens = 4
		Rows seen = 2
		Rows filtered = 0
		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:
			Hash Scan ResultSet for T1 using index I11 at serializable isolation level using share table locking: 
			Number of opens = 4
			Hash table size = 5
			Hash key is column number 0
			Rows seen = 2
			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={0}
				Number of columns fetched=1
				Number of deleted rows visited=0
				Number of pages visited=1
				Number of rows qualified=5
				Number of rows visited=5
				Scan type=btree
				Tree height=1
				start position: 
	None
				stop position: 
	None
				scan qualifiers:
None
				next qualifiers:
Column[0][0] Id: 0
Operator: =
Ordered nulls: false
Unknown return value: false
Negate comparison result: false
ij> -- nested loop exists join, right side should be ProjectRestrict on index scan with start and stop keys
select c1 from t2 where c1 in (select c1 from t1 where c1 in (5, t2.c2) and c1 in ('5', '7'));
ERROR 42818: Comparisons between 'REAL' and 'CHAR' are not supported.
ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
1                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Statement Name: 
	null
Statement Text: 
	values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS()
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: 
Row ResultSet:
Number of opens = 1
Rows returned = 1
	constructor time (milliseconds) = 0
	open time (milliseconds) = 0
	next time (milliseconds) = 0
	close time (milliseconds) = 0
ij> -- hash exists join, right side PR on hash index scan, no start/stop key, next qualifier "=".
select c1 from t2 where c1 in (select c1 from t1 where c1 in (5, t2.c2));
C1           
-------------
8.0          
5.0          
ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
1                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Statement Name: 
	null
Statement Text: 
	-- hash exists join, right side PR on hash index scan, no start/stop key, next qualifier "=".
select c1 from t2 where c1 in (select c1 from t1 where c1 in (5, t2.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: 
Project-Restrict ResultSet (5):
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 Exists Join ResultSet:
	Number of opens = 1
	Rows seen from the left = 4
	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:
		Table Scan ResultSet for T2 at serializable isolation level using share table locking chosen by the optimizer
		Number of opens = 1
		Rows seen = 4
		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=2
			Number of pages visited=1
			Number of rows qualified=4
			Number of rows visited=4
			Scan type=heap
			start position: 
null			stop position: 
null			qualifiers:
None
	Right result set:
		Project-Restrict ResultSet (4):
		Number of opens = 4
		Rows seen = 2
		Rows filtered = 0
		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:
			Hash Scan ResultSet for T1 using index I11 at serializable isolation level using share table locking: 
			Number of opens = 4
			Hash table size = 5
			Hash key is column number 0
			Rows seen = 2
			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={0}
				Number of columns fetched=1
				Number of deleted rows visited=0
				Number of pages visited=1
				Number of rows qualified=5
				Number of rows visited=5
				Scan type=btree
				Tree height=1
				start position: 
	None
				stop position: 
	None
				scan qualifiers:
None
				next qualifiers:
Column[0][0] Id: 0
Operator: =
Ordered nulls: false
Unknown return value: false
Negate comparison result: false
ij> -- hash exists join, right side PR on hash index scan, still no start/stop key, next qualifier "=".
-- It still doesn't have start/stop key because c1 in ('5', '7') is blocked out by 2 others.
select c1 from t2 where c1 in (select c1 from t1 where c1 in (5, t2.c2) and c1 in ('5', '7'));
ERROR 42818: Comparisons between 'REAL' and 'CHAR' are not supported.
ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
1                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Statement Name: 
	null
Statement Text: 
	values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS()
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: 
Row ResultSet:
Number of opens = 1
Rows returned = 1
	constructor time (milliseconds) = 0
	open time (milliseconds) = 0
	next time (milliseconds) = 0
	close time (milliseconds) = 0
ij> create index i12 on t1 (c1, c2);
0 rows inserted/updated/deleted
ij> -- at push "in" time, we determined that it is key and we can push; but at hash time we determined
-- it's not key.  Now the key is it should be filtered out, otherwise we get exception.
select c1 from t2 where c1 in (select c1 from t1 where c2 in (5, t2.c2));
C1           
-------------
ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
1                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Statement Name: 
	null
Statement Text: 
	-- at push "in" time, we determined that it is key and we can push; but at hash time we determined