distinctelimination.out

derby database source code.good for you.

1          |1          |1          
1          |2          |1          
1          |3          |1          
2          |1          |1          
2          |2          |1          
2          |3          |1          
3          |1          |1          
3          |2          |1          
3          |3          |1          
ij> -- Following runtime statistics output should have Eliminate duplicates = true
values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
1                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Statement Name: 
	null
Statement Text: 
	-- no single table will yield at most 1 row
select distinct a.c1, a.c3, a.c2 from two a, two b where a.c1 = b.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: 
Sort ResultSet:
Number of opens = 1
Rows input = 27
Rows returned = 9
Eliminate duplicates = true
In sorted order = false
Sort information: 
	Number of rows input=27
	Number of rows output=9
	constructor time (milliseconds) = 0
	open time (milliseconds) = 0
	next time (milliseconds) = 0
	close time (milliseconds) = 0
Source result set:
	Project-Restrict ResultSet (5):
	Number of opens = 1
	Rows seen = 27
	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 = 9
		Rows seen from the right = 27
		Rows filtered = 0
		Rows returned = 27
			constructor time (milliseconds) = 0
			open time (milliseconds) = 0
			next time (milliseconds) = 0
			close time (milliseconds) = 0
		Left result set:
			Table Scan ResultSet for TWO at serializable isolation level using share table locking chosen by the optimizer
			Number of opens = 1
			Rows seen = 9
			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={0, 1, 2}
				Number of columns fetched=3
				Number of pages visited=1
				Number of rows qualified=9
				Number of rows visited=9
				Scan type=heap
				start position: 
null				stop position: 
null				qualifiers:
None
		Right result set:
			Hash Scan ResultSet for TWO using index TWO_C1C3 at serializable isolation level using share table locking: 
			Number of opens = 9
			Hash table size = 3
			Hash key is column number 0
			Rows seen = 27
			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=9
				Number of rows visited=9
				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> select distinct a.c1, a.c3, a.c2 from two a, two b where a.c1 = b.c1 and a.c2 = 1;
C1         |C3         |C2         
-----------------------------------
1          |1          |1          
1          |2          |1          
1          |3          |1          
2          |1          |1          
2          |2          |1          
2          |3          |1          
3          |1          |1          
3          |2          |1          
3          |3          |1          
ij> -- Following runtime statistics output should have Eliminate duplicates = true
values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
1                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Statement Name: 
	null
Statement Text: 
	select distinct a.c1, a.c3, a.c2 from two a, two b where a.c1 = b.c1 and a.c2 = 1
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 = 27
Rows returned = 9
Eliminate duplicates = true
In sorted order = false
Sort information: 
	Number of rows input=27
	Number of rows output=9
	constructor time (milliseconds) = 0
	open time (milliseconds) = 0
	next time (milliseconds) = 0
	close time (milliseconds) = 0
Source result set:
	Project-Restrict ResultSet (5):
	Number of opens = 1
	Rows seen = 27
	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 = 9
		Rows seen from the right = 27
		Rows filtered = 0
		Rows returned = 27
			constructor time (milliseconds) = 0
			open time (milliseconds) = 0
			next time (milliseconds) = 0
			close time (milliseconds) = 0
		Left result set:
			Table Scan ResultSet for TWO at serializable isolation level using share table locking chosen by the optimizer
			Number of opens = 1
			Rows seen = 9
			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={0, 1, 2}
				Number of columns fetched=3
				Number of pages visited=1
				Number of rows qualified=9
				Number of rows visited=9
				Scan type=heap
				start position: 
null				stop position: 
null				qualifiers:
Column[0][0] Id: 1
Operator: =
Ordered nulls: false
Unknown return value: false
Negate comparison result: false
		Right result set:
			Index Scan ResultSet for TWO using index TWO_C1C3 at serializable isolation level using share row locking chosen by the optimizer
			Number of opens = 9
			Rows seen = 27
			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={0}
				Number of columns fetched=1
				Number of deleted rows visited=0
				Number of pages visited=9
				Number of rows qualified=27
				Number of rows visited=33
				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> -- both keys from unique index in where clause but joined to different tables
select distinct a.c1 from one a, two b, three c where a.c1 = b.c1 and c.c1 = b.c3
and a.c1 = 1;
C1         
-----------
1          
ij> -- Following runtime statistics output should have Eliminate duplicates = true
values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
1                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Statement Name: 
	null
Statement Text: 
	-- both keys from unique index in where clause but joined to different tables
select distinct a.c1 from one a, two b, three c where a.c1 = b.c1 and c.c1 = b.c3
and a.c1 = 1
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 = 3