outerjoin.out

derby database source code.good for you.

					Bit set of columns fetched=All
					Number of columns fetched=3
					Number of pages visited=1
					Number of rows qualified=3
					Number of rows visited=8
					Scan type=heap
					start position: 
null					stop position: 
null					scan qualifiers:
Non&
ij> select * from tt1 left outer join tt2 on tt1.c1 = tt2.c2 left outer join tt3 on tt2.c2 = tt3.c3 where tt2.c2 = 3;
C1         |C2         |C3         |C1         |C2         |C3         |C1         |C2         |C3         
-----------------------------------------------------------------------------------------------------------
3          |4          |5          |2          |3          |4          |1          |2          |3          
ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
1                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Statement Name: 
	null
Statement Text: 
	select * from tt1 left outer join tt2 on tt1.c1 = tt2.c2 left outer join tt3 on tt2.c2 = tt3.c3 where tt2.c2 = 3
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: 
Nested Loop Left Outer Join ResultSet:
Number of opens = 1
Rows seen from the left = 1
Rows seen from the right = 1
Empty right rows returned = 0
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:
	Hash Join ResultSet:
	Number of opens = 1
	Rows seen from the left = 3
	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 TT1 at read committed isolation level using instantaneous share row locking chosen by the optimizer
		Number of opens = 1
		Rows seen = 3
		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=3
			Number of rows visited=8
			Scan type=heap
			start position: 
null			stop position: 
null			qualifiers:
None
	Right result set:
		Hash Scan ResultSet for TT2 at read committed isolation level using instantaneous share row locking: 
		Number of opens = 3
		Hash table size = 1
		Hash key is column number 1
		Rows seen = 1
		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=All
			Number of columns fetched=3
			Number of pages visited=1
			Number of rows qualified=1
			Number of rows visited=8
			Scan type=heap
			start position: 
null			stop position: 
null			scan qualifiers:
Column[0][0] Id: 1
Operator: =
Ordered nulls: false
Unknown return value: false
Negate comparison result: false
			next qualifiers:
Column[0][0] Id: 1
Operator: =
Ordered nulls: false
Unknown return value: false
Negate comparison result: false
Right result set:
	Table Scan ResultSet for TT3 at read committed isolation level using instantaneous share row locking chosen by the optimizer
	Number of opens = 1
	Rows seen = 1
	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=1
		Number of rows visited=8
		Scan type=heap
		start position: 
null		stop position: 
null		qualifiers:
Column[0][0] Id: 2
Operator: =
Ordered nulls: false
Unknown return value: false
Negate comparison result: false
ij> -- make sure predicates are null tolerant 
select * from tt1 left outer join tt2 on tt1.c1 = tt2.c2 
where char(tt2.c2) is null;
C1         |C2         |C3         |C1         |C2         |C3         
-----------------------------------------------------------------------
1          |2          |3          |NULL       |NULL       |NULL       
ij> -- where java.lang.Integer::toString(tt2.c2) = '2';
values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
1                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Statement Name: 
	null
Statement Text: 
	-- make sure predicates are null tolerant 
select * from tt1 left outer join tt2 on tt1.c1 = tt2.c2 
where char(tt2.c2) is null
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 = 3
Rows filtered = 2
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 Left Outer Join ResultSet:
	Number of opens = 1
	Rows seen from the left = 3
	Rows seen from the right = 2
	Empty right rows returned = 0
	Rows filtered = 0
	Rows returned = 3
		constructor time (milliseconds) = 0
		open time (milliseconds) = 0
		next time (milliseconds) = 0
		close time (milliseconds) = 0
	Left result set:
		Table Scan ResultSet for TT1 at read committed isolation level using instantaneous share row locking chosen by the optimizer
		Number of opens = 1
		Rows seen = 3
		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=3
			Number of rows visited=8
			Scan type=heap
			start position: 
null			stop position: 
null			qualifiers:
None
	Right result set:
		Hash Scan ResultSet for TT2 at read committed isolation level using instantaneous share row locking: 
		Number of opens = 3
		Hash table size = 3
		Hash key is column number 1
		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=All
			Number of columns fetched=3
			Number of pages visited=1
			Number of rows qualified=3
			Number of rows visited=8
			Scan type=heap
			start position: 
null			stop position: 
null			scan qualifiers:
None
			next qualifiers:
Column[0][0] Id: 1
Operator: =
Ordered nulls: false
Unknown return value: false
Negate comparison result: false
ij> commit;
ij> -- bug 2924, cross join under an outer join
CREATE TABLE inventory(itemno INT NOT NULL PRIMARY KEY, capacity INT);
0 rows inserted/updated/deleted
ij> INSERT INTO inventory VALUES (1, 4);
1 row inserted/updated/deleted
ij> INSERT INTO inventory VALUES (2, 2);
1 row inserted/updated/deleted
ij> INSERT INTO inventory VALUES (3, 2);
1 row inserted/updated/deleted
ij> CREATE TABLE timeslots (slotno INT NOT NULL PRIMARY KEY);
0 rows inserted/updated/deleted
ij> INSERT INTO timeslots VALUES(1);
1 row inserted/updated/deleted
ij> INSERT INTO timeslots VALUES(2);
1 row inserted/updated/deleted
ij> create table reservations(slotno INT CONSTRAINT timeslots_fk REFERENCES timeslots, 
itemno INT CONSTRAINT inventory_fk REFERENCES inventory, 
name VARCHAR(100), resdate DATE);
0 rows inserted/updated/deleted
ij> INSERT INTO reservations VALUES(1, 1, 'Joe', '2000-04-14');
1 row inserted/updated/deleted
ij> INSERT INTO reservations VALUES(1, 1, 'Fred', '2000-04-13');
1 row inserted/updated/deleted
ij> -- This query used to cause a null pointer exception
select name, resdate 
from reservations left outer join (inventory join timeslots on inventory.itemno = timeslots.slotno)
on inventory.itemno = reservations.itemno and timeslots.slotno = reservations.slotno
where resdate = '2000-04-14';
NAME                                                                                                |RESDATE   
---------------------------------------------------------------------------------------------------------------
Joe                                                                                                 |2000-04-14
ij> rollback;
ij> -- bug 2923, cross join under an outer join
create table inventory(itemno INT NOT NULL PRIMARY KEY, capacity INT);
0 rows inserted/updated/deleted
ij> INSERT into inventory values (1, 4);
1 row inserted/updated/deleted
ij> INSERT into inventory values (2, 2);
1 row inserted/updated/deleted
ij> INSERT into inventory values (3, 2);
1 row inserted/updated/deleted
ij> CREATE TABLE timeslots (slotno INT NOT NULL PRIMARY KEY);
0 rows inserted/updated/deleted
ij> INSERT INTO timeslots VALUES(1);
1 row inserted/updated/deleted
ij> INSERT INTO timeslots VALUES(2);
1 row inserted/updated/deleted
ij> create table reservations(slotno INT CONSTRAINT timeslots_fk REFERENCES timeslots,
itemno INT CONSTRAINT inventory_fk REFERENCES inventory,
name VARCHAR(100));
0 rows inserted/updated/deleted
ij> INSERT INTO reservations VALUES(1, 1, 'Joe');
1 row inserted/updated/deleted
ij> INSERT INTO reservations VALUES(2, 2, 'Fred');
1 row inserted/updated/deleted
ij> -- This query used to get inco