ij> -- -- Licensed to the Apache Software Foundation (ASF) under one or more -- contributor license agreements. See the NOTICE file distributed with -- this work for additional information regarding copyright ownership. -- The ASF licenses this file to You under the Apache License, Version 2.0 -- (the "License"); you may not use this file except in compliance with -- the License. You may obtain a copy of the License at -- -- http://www.apache.org/licenses/LICENSE-2.0 -- -- Unless required by applicable law or agreed to in writing, software -- distributed under the License is distributed on an "AS IS" BASIS, -- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. -- See the License for the specific language governing permissions and -- limitations under the License. -- -- test outer joins -- (NO NATURAL JOIN) autocommit off; ij> -- create some tables create table t1(c1 int); 0 rows inserted/updated/deleted ij> create table t2(c1 int); 0 rows inserted/updated/deleted ij> create table t3(c1 int); 0 rows inserted/updated/deleted ij> create table tt1(c1 int, c2 int, c3 int); 0 rows inserted/updated/deleted ij> create table tt2(c1 int, c2 int, c3 int); 0 rows inserted/updated/deleted ij> create table tt3(c1 int, c2 int, c3 int); 0 rows inserted/updated/deleted ij> create table empty_table(c1 int); 0 rows inserted/updated/deleted ij> create table insert_test(c1 int, c2 int, c3 int); 0 rows inserted/updated/deleted ij> -- following is verifying that oj is not a keyword create table oj(oj int); 0 rows inserted/updated/deleted ij> -- populate the tables insert into t1 values 1, 2, 2, 3, 4; 5 rows inserted/updated/deleted ij> insert into t2 values 1, 3, 3, 5, 6; 5 rows inserted/updated/deleted ij> insert into t3 values 2, 3, 5, 5, 7; 5 rows inserted/updated/deleted ij> insert into tt1 select c1, c1, c1 from t1; 5 rows inserted/updated/deleted ij> insert into tt2 select c1, c1, c1 from t2; 5 rows inserted/updated/deleted ij> insert into tt3 select c1, c1, c1 from t3; 5 rows inserted/updated/deleted ij> -- verifying that oj is not a keyword insert into oj(oj) values (1); 1 row inserted/updated/deleted ij> -- negative tests -- no outer join type select * from t1 outer join t2; ERROR 42X01: Syntax error: Encountered "outer" at line 4, column 18. Issue the 'help' command for general information on IJ command syntax. Any unrecognized commands are treated as potential SQL commands and executed directly. Consult your DBMS server reference documentation for details of the SQL syntax supported by your server. ij> -- no join clause select * from t1 left outer join t2; ERROR 42X01: Syntax error: Encountered "" at line 2, column 35. Issue the 'help' command for general information on IJ command syntax. Any unrecognized commands are treated as potential SQL commands and executed directly. Consult your DBMS server reference documentation for details of the SQL syntax supported by your server. ij> select * from t1 right outer join t2; ERROR 42X01: Syntax error: Encountered "" at line 1, column 36. Issue the 'help' command for general information on IJ command syntax. Any unrecognized commands are treated as potential SQL commands and executed directly. Consult your DBMS server reference documentation for details of the SQL syntax supported by your server. ij> -- positive tests select t1.c1 from t1 left outer join t2 on t1.c1 = t2.c1; C1 ----------- 1 2 2 3 3 4 ij> select t2.c1 from t1 right outer join t2 on t1.c1 = t2.c1; C1 ----------- 1 3 3 5 6 ij> select a.x from t1 a (x) left outer join t2 b (x) on a.x = b.x; X ----------- 1 2 2 3 3 4 ij> -- verify that selects from inner table work select b.* from (values 9) a left outer join t2 b on 1=1; C1 ----------- 1 3 3 5 6 ij> select b.* from (values 9) a left outer join t2 b on 1=0; C1 ----------- NULL ij> select b.* from (values 9) a right outer join t2 b on 1=0; C1 ----------- 1 3 3 5 6 ij> select a.* from (values 9) a right outer join t2 b on 1=1; 1 ----------- 9 9 9 9 9 ij> select a.* from (values 9) a right outer join t2 b on 1=0; 1 ----------- NULL NULL NULL NULL NULL ij> select a.* from ((values ('a', 'b')) a inner join (values ('c', 'd')) b on 1=1) left outer join (values ('e', 'f')) c on 1=1; 1 |2 --------- a |b ij> select b.* from ((values ('a', 'b')) a inner join (values ('c', 'd')) b on 1=1) left outer join (values ('e', 'f')) c on 1=1; 1 |2 --------- c |d ij> select c.* from ((values ('a', 'b')) a inner join (values ('c', 'd')) b on 1=1) left outer join (values ('e', 'f')) c on 1=1; 1 |2 --------- e |f ij> -- verifying that oj is not a keyword select * from oj where oj = 1; OJ ----------- 1 ij> --verifying both regular and {oj } in select * from t1 left outer join {oj t2 left outer join t3 on t2.c1=t3.c1} on t1.c1=t3.c1; C1 |C1 |C1 ----------------------------------- 1 |NULL |NULL 2 |NULL |NULL 2 |NULL |NULL 3 |3 |3 3 |3 |3 4 |NULL |NULL ij> -- left and right outer join with an empty table select t1.c1 from t1 left outer join empty_table et on t1.c1 = et.c1; C1 ----------- 1 2 2 3 4 ij> select t1.c1 from t1 right outer join empty_table et on t1.c1 = et.c1; C1 ----------- ij> select t1.c1 from empty_table et right outer join t1 on et.c1 = t1.c1; C1 ----------- 1 2 2 3 4 ij> -- this query may make no sense at all, but it's just trying to show that parser works -- fine with both regular tableexpression and tableexpression with {oj } select * from t1, {oj t2 join t3 on t2.c1=t3.c1}; C1 |C1 |C1 ----------------------------------- 1 |3 |3 1 |3 |3 1 |5 |5 1 |5 |5 2 |3 |3 2 |3 |3 2 |5 |5 2 |5 |5 2 |3 |3 2 |3 |3 2 |5 |5 2 |5 |5 3 |3 |3 3 |3 |3 3 |5 |5 3 |5 |5 4 |3 |3 4 |3 |3 4 |5 |5 4 |5 |5 ij> -- parameters and join clause prepare asdf as 'select * from t1 left outer join t2 on 1=? and t1.c1 = t2.c1'; ij> execute asdf using 'values 1'; C1 |C1 ----------------------- 1 |1 2 |NULL 2 |NULL 3 |3 3 |3 4 |NULL ij> remove asdf; ij> prepare asdf as 'select * from t1 left outer join t2 on t1.c1 = t2.c1 and t1.c1 = ?'; ij> execute asdf using 'values 1'; C1 |C1 ----------------------- 1 |1 2 |NULL 2 |NULL 3 |NULL 4 |NULL ij> remove asdf; ij> -- additional predicates outside of the join clause -- egs of using {oj --} syntax select * from t1 left outer join t2 on t1.c1 = t2.c1 where t1.c1 = 1; C1 |C1 ----------------------- 1 |1 ij> select * from {oj t1 left outer join t2 on t1.c1 = t2.c1} where t1.c1 = 1; C1 |C1 ----------------------- 1 |1 ij> select * from t1 right outer join t2 on t1.c1 = 1 where t2.c1 = t1.c1; C1 |C1 ----------------------- 1 |1 ij> select * from {oj t1 right outer join t2 on t1.c1 = 1} where t2.c1 = t1.c1; C1 |C1 ----------------------- 1 |1 ij> -- subquery in join clause. Not allowed in the DB2 compatibility mode. ERROR. -- egs of using {oj --} syntax select * from t1 a left outer join t2 b on a.c1 = b.c1 and a.c1 = (select c1 from t1 where a.c1 = t1.c1 and a.c1 = 1); ERROR 42972: An ON clause associated with a JOIN operator is not valid. ij> select * from {oj t1 a left outer join t2 b on a.c1 = b.c1 and a.c1 = (select c1 from t1 where a.c1 = t1.c1 and a.c1 = 1)}; ERROR 42972: An ON clause associated with a JOIN operator is not valid. ij> select * from t1 a left outer join t2 b on a.c1 = b.c1 and a.c1 = (select c1 from t1 where a.c1 = t1.c1 and a.c1 <> 2); ERROR 42972: An ON clause associated with a JOIN operator is not valid. ij> select * from {oj t1 a left outer join t2 b on a.c1 = b.c1 and a.c1 = (select c1 from t1 where a.c1 = t1.c1 and a.c1 <> 2)}; ERROR 42972: An ON clause associated with a JOIN operator is not valid. ij> select * from t1 a right outer join t2 b on a.c1 = b.c1 and a.c1 in (select c1 from t1 where a.c1 = t1.c1); ERROR 42972: An ON clause associated with a JOIN operator is not valid. ij> -- outer join in subquery -- egs of using {oj --} syntax select * from t1 a where exists (select * from t1 left outer join t2 on t1.c1 = t2.c1); C1 ----------- 1 2 2 3 4 ij> select * from t1 a where exists (select * from {oj t1 left outer join t2 on t1.c1 = t2.c1}); C1 ----------- 1 2 2 3 4 ij> select * from t1 a where exists (select * from t1 left outer join t2 on 1=0); C1 ----------- 1 2 2 3 4 ij> -- nested joins -- egs of using {oj --} syntax select * from t1 left outer join t2 on t1.c1 = t2.c1 left outer join t3 on t1.c1 = t3.c1; C1 |C1 |C1 ----------------------------------- 1 |1 |NULL 2 |NULL |2 2 |NULL |2 3 |3 |3 3 |3 |3 4 |NULL |NULL ij> select * from {oj t1 left outer join t2 on t1.c1 = t2.c1 left outer join t3 on t1.c1 = t3.c1}; C1 |C1 |C1 ----------------------------------- 1 |1 |NULL 2 |NULL |2 2 |NULL |2 3 |3 |3 3 |3 |3 4 |NULL |NULL ij> select * from t1 left outer join t2 on t1.c1 = t2.c1 left outer join t3 on t2.c1 = t3.c1; C1 |C1 |C1 ----------------------------------- 1 |1 |NULL 2 |NULL |NULL 2 |NULL |NULL 3 |3 |3 3 |3 |3 4 |NULL |NULL ij> select * from t3 right outer join t2 on t3.c1 = t2.c1 right outer join t1 on t1.c1 = t2.c1; C1 |C1 |C1 ----------------------------------- NULL |1 |1 NULL |NULL |2 NULL |NULL |2 3 |3 |3 3 |3 |3 NULL |NULL |4 ij> -- parens select * from (t1 left outer join t2 on t1.c1 = t2.c1) left outer join t3 on t1.c1 = t3.c1; C1 |C1 |C1 ----------------------------------- 1 |1 |NULL 2 |NULL |2 2 |NULL |2 3 |3 |3 3 |3 |3 4 |NULL |NULL ij> select * from t1 left outer join (t2 left outer join t3 on t2.c1 = t3.c1) on t1.c1 = t2.c1; C1 |C1 |C1 ----------------------------------- 1 |1 |NULL 2 |NULL |NULL 2 |NULL |NULL 3 |3 |3 3 |3 |3 4 |NULL |NULL ij> -- left/right outer join combinations select * from t1 a right outer join t2 b on a.c1 = b.c1 left outer join t3 c on a.c1 = b.c1 and b.c1 = c.c1; C1 |C1 |C1 ----------------------------------- 1 |1 |NULL 3 |3 |3 3 |3 |3 NULL |5 |NULL NULL |6 |NULL ij> select * from (t1 a right outer join t2 b on a.c1 = b.c1) left outer join t3 c on a.c1 = b.c1 and b.c1 = c.c1; C1 |C1 |C1 ----------------------------------- 1 |1 |NULL 3 |3 |3 3 |3 |3 NULL |5 |NULL NULL |6 |NULL ij> select * from t1 a left outer join t2 b on a.c1 = b.c1 right outer join t3 c on c.c1 = a.c1 where a.c1 is not null; C1 |C1 |C1 ----------------------------------- 2 |NULL |2 2 |NULL |2 3 |3 |3 3 |3 |3 ij> select * from (t1 a left outer join t2 b on a.c1 = b.c1) right outer join t3 c on c.c1 = a.c1 where a.c1 is not null; C1 |C1 |C1 ----------------------------------- 2 |NULL |2 2 |NULL |2 3 |3 |3 3 |3 |3 ij> select * from t1 a left outer join (t2 b right outer join t3 c on c.c1 = b.c1) on a.c1 = c.c1 where c.c1=b.c1; C1 |C1 |C1 ----------------------------------- 3 |3 |3 3 |3 |3 ij> -- test insert/update/delete insert into insert_test select * from t1 a left outer join t2 b on a.c1 = b.c1 left outer join t3 c on a.c1 <> c.c1; 26 rows inserted/updated/deleted ij> select * from insert_test; C1 |C2 |C3 ----------------------------------- 1 |1 |2 1 |1 |3 1 |1 |5 1 |1 |5 1 |1 |7 2 |NULL |3 2 |NULL |5 2 |NULL |5 2 |NULL |7 2 |NULL |3 2 |NULL |5 2 |NULL |5 2 |NULL |7 3 |3 |2 3 |3 |5 3 |3 |5 3 |3 |7 3 |3 |2 3 |3 |5 3 |3 |5 3 |3 |7 4 |NULL |2 4 |NULL |3 4 |NULL |5 4 |NULL |5 4 |NULL |7 ij> update insert_test set c1 = (select 9 from t1 a left outer join t1 b on a.c1 = b.c1 where a.c1 = 1) where c1 = 1; 5 rows inserted/updated/deleted ij> select * from insert_test; C1 |C2 |C3 ----------------------------------- 9 |1 |2 9 |1 |3 9 |1 |5 9 |1 |5 9 |1 |7 2 |NULL |3 2 |NULL |5 2 |NULL |5 2 |NULL |7 2 |NULL |3 2 |NULL |5 2 |NULL |5 2 |NULL |7 3 |3 |2 3 |3 |5 3 |3 |5 3 |3 |7 3 |3 |2 3 |3 |5 3 |3 |5 3 |3 |7 4 |NULL |2 4 |NULL |3 4 |NULL |5 4 |NULL |5 4 |NULL |7 ij> delete from insert_test where c1 = (select 9 from t1 a left outer join t1 b on a.c1 = b.c1 where a.c1 = 1); 5 rows inserted/updated/deleted ij> select * from insert_test; C1 |C2 |C3 ----------------------------------- 2 |NULL |3 2 |NULL |5 2 |NULL |5 2 |NULL |7 2 |NULL |3 2 |NULL |5 2 |NULL |5 2 |NULL |7 3 |3 |2 3 |3 |5 3 |3 |5 3 |3 |7 3 |3 |2 3 |3 |5 3 |3 |5 3 |3 |7 4 |NULL |2 4 |NULL |3 4 |NULL |5 4 |NULL |5 4 |NULL |7 ij> delete from insert_test; 21 rows inserted/updated/deleted ij> insert into insert_test select * from (select * from t1 a left outer join t2 b on a.c1 = b.c1 left outer join t3 c on a.c1 <> c.c1) d (c1, c2, c3); 26 rows inserted/updated/deleted ij> select * from insert_test; C1 |C2 |C3 ----------------------------------- 1 |1 |2 1 |1 |3 1 |1 |5 1 |1 |5 1 |1 |7 2 |NULL |3 2 |NULL |5 2 |NULL |5 2 |NULL |7 2 |NULL |3 2 |NULL |5 2 |NULL |5 2 |NULL |7 3 |3 |2 3 |3 |5 3 |3 |5 3 |3 |7 3 |3 |2 3 |3 |5 3 |3 |5 3 |3 |7 4 |NULL |2 4 |NULL |3 4 |NULL |5 4 |NULL |5 4 |NULL |7 ij> delete from insert_test; 26 rows inserted/updated/deleted ij> -- verify that right outer join xforms don't get result columns -- confused create table a (c1 int); 0 rows inserted/updated/deleted ij> create table b (c2 float); 0 rows inserted/updated/deleted ij> create table c (c3 char(30)); 0 rows inserted/updated/deleted ij> insert into a values 1; 1 row inserted/updated/deleted ij> insert into b values 3.3; 1 row inserted/updated/deleted ij> insert into c values 'asdf'; 1 row inserted/updated/deleted ij> select * from a left outer join b on 1=1 left outer join c on 1=1; C1 |C2 |C3 ----------------------------------------------------------------- 1 |3.3 |asdf ij> select * from a left outer join b on 1=1 left outer join c on 1=0; C1 |C2 |C3 ----------------------------------------------------------------- 1 |3.3 |NULL ij> select * from a left outer join b on 1=0 left outer join c on 1=1; C1 |C2 |C3 ----------------------------------------------------------------- 1 |NULL |asdf ij> select * from a left outer join b on 1=0 left outer join c on 1=0; C1 |C2 |C3 ----------------------------------------------------------------- 1 |NULL |NULL ij> select * from c right outer join b on 1=1 right outer join a on 1=1; C3 |C2 |C1 ----------------------------------------------------------------- asdf |3.3 |1 ij> select * from c right outer join b on 1=1 right outer join a on 1=0; C3 |C2 |C1 ----------------------------------------------------------------- NULL |NULL |1 ij> select * from c right outer join b on 1=0 right outer join a on 1=1; C3 |C2 |C1 ----------------------------------------------------------------- NULL |3.3 |1 ij> select * from c right outer join b on 1=0 right outer join a on 1=0; C3 |C2 |C1 ----------------------------------------------------------------- NULL |NULL |1 ij> -- multicolumn tests -- c1, c2, and c3 all have the same values select tt1.c1, tt1.c2, tt1.c3, tt2.c2, tt2.c3 from tt1 left outer join tt2 on tt1.c1 = tt2.c1; C1 |C2 |C3 |C2 |C3 ----------------------------------------------------------- 1 |1 |1 |1 |1 2 |2 |2 |NULL |NULL 2 |2 |2 |NULL |NULL 3 |3 |3 |3 |3 3 |3 |3 |3 |3 4 |4 |4 |NULL |NULL ij> select tt1.c1, tt1.c2, tt1.c3, tt2.c3 from tt1 left outer join tt2 on tt1.c1 = tt2.c1; C1 |C2 |C3 |C3 ----------------------------------------------- 1 |1 |1 |1 2 |2 |2 |NULL 2 |2 |2 |NULL 3 |3 |3 |3 3 |3 |3 |3 4 |4 |4 |NULL ij> select tt1.c1, tt1.c2, tt1.c3 from tt1 left outer join tt2 on tt1.c1 = tt2.c1; C1 |C2 |C3 ----------------------------------- 1 |1 |1 2 |2 |2 2 |2 |2 3 |3 |3 3 |3 |3 4 |4 |4 ij> -- nested outer joins select tt1.c2, tt1.c1, tt1.c3, tt2.c1, tt2.c3 from t1 left outer join tt1 on t1.c1 = tt1.c1 left outer join tt2 on tt1.c2 = tt2.c2; C2 |C1 |C3 |C1 |C3 ----------------------------------------------------------- 1 |1 |1 |1 |1 2 |2 |2 |NULL |NULL 2 |2 |2 |NULL |NULL 2 |2 |2 |NULL |NULL 2 |2 |2 |NULL |NULL 3 |3 |3 |3 |3 3 |3 |3 |3 |3 4 |4 |4 |NULL |NULL ij> -- make sure that column reordering is working correctly -- when there's an ON clause create table x (c1 int, c2 int, c3 int); 0 rows inserted/updated/deleted ij> create table y (c3 int, c4 int, c5 int); 0 rows inserted/updated/deleted ij> insert into x values (1, 2, 3), (4, 5, 6); 2 rows inserted/updated/deleted ij> insert into y values (3, 4, 5), (666, 7, 8); 2 rows inserted/updated/deleted ij> -- qualfied * will return all of the columns of the qualified table -- including join columns select x.* from x join y on x.c3 = y.c3; C1 |C2 |C3 ----------------------------------- 1 |2 |3 ij> select x.* from x left outer join y on x.c3 = y.c3; C1 |C2 |C3 ----------------------------------- 1 |2 |3 4 |5 |6 ij> select x.* from x right outer join y on x.c3 = y.c3; C1 |C2 |C3 ----------------------------------- 1 |2 |3 NULL |NULL |NULL ij> select y.* from x join y on x.c3 = y.c3; C3 |C4 |C5 ----------------------------------- 3 |4 |5 ij> select y.* from x left outer join y on x.c3 = y.c3; C3 |C4 |C5 ----------------------------------- 3 |4 |5 NULL |NULL |NULL ij> select y.* from x right outer join y on x.c3 = y.c3; C3 |C4 |C5 ----------------------------------- 3 |4 |5 666 |7 |8 ij> -- * will return all of the columns of all joined tables select * from x join y on x.c3 = y.c3; C1 |C2 |C3 |C3 |C4 |C5 ----------------------------------------------------------------------- 1 |2 |3 |3 |4 |5 ij> select * from x left outer join y on x.c3 = y.c3; C1 |C2 |C3 |C3 |C4 |C5 ----------------------------------------------------------------------- 1 |2 |3 |3 |4 |5 4 |5 |6 |NULL |NULL |NULL ij> select * from x right outer join y on x.c3 = y.c3; C1 |C2 |C3 |C3 |C4 |C5 ----------------------------------------------------------------------- 1 |2 |3 |3 |4 |5 NULL |NULL |NULL |666 |7 |8 ij> commit; ij> -- test outer join -> inner join xform delete from tt1; 5 rows inserted/updated/deleted ij> delete from tt2; 5 rows inserted/updated/deleted ij> delete from tt3; 5 rows inserted/updated/deleted ij> insert into tt1 values (1, 2, 3), (2, 3, 4), (3, 4, 5); 3 rows inserted/updated/deleted ij> insert into tt2 values (1, 2, 3), (2, 3, 4), (3, 4, 5); 3 rows inserted/updated/deleted ij> insert into tt3 values (1, 2, 3), (2, 3, 4), (3, 4, 5); 3 rows inserted/updated/deleted ij> call SYSCS_UTIL.SYSCS_SET_RUNTIMESTATISTICS(1); 0 rows inserted/updated/deleted ij> maximumdisplaywidth 4500; ij> -- no xform, predicate on outer table select * from tt1 left outer join tt2 on tt1.c1 = tt2.c2 where tt1.c1 = 3; C1 |C2 |C3 |C1 |C2 |C3 ----------------------------------------------------------------------- 3 |4 |5 |2 |3 |4 ij> -- various predicates on inner table select * from tt1 left outer join tt2 on tt1.c1 = tt2.c2 where tt2.c2 = 3; C1 |C2 |C3 |C1 |C2 |C3 ----------------------------------------------------------------------- 3 |4 |5 |2 |3 |4 ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); 1 ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Statement Name: null Statement Text: -- various predicates on inner table select * from tt1 left outer join tt2 on tt1.c1 = tt2.c2 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 Join ResultSet: Number of opens = 1 Rows seen from the left = 1 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 = 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: 0 Operator: = Ordered nulls: false Unknown return value: false Negate comparison result: false Right result set: Table Scan ResultSet for TT2 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: 1 Operator: = Ordered nulls: false Unknown return value: false Negate comparison result: false ij> select * from tt1 left outer join tt2 on tt1.c1 = tt2.c2 where tt2.c1 + 1= tt2.c2; C1 |C2 |C3 |C1 |C2 |C3 ----------------------------------------------------------------------- 2 |3 |4 |1 |2 |3 3 |4 |5 |2 |3 |4 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 where tt2.c1 + 1= tt2.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: Nested Loop Join ResultSet: Number of opens = 1 Rows seen from the left = 3 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: Project-Restrict ResultSet (3): Number of opens = 1 Rows seen = 3 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: Table Scan ResultSet for TT2 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: Table Scan ResultSet for TT1 at read committed isolation level using instantaneous share row locking chosen by the optimizer Number of opens = 3 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=24 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 * from tt1 left outer join tt2 on tt1.c1 = tt2.c2 where tt2.c1 + 1= 3; C1 |C2 |C3 |C1 |C2 |C3 ----------------------------------------------------------------------- 3 |4 |5 |2 |3 |4 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 where tt2.c1 + 1= 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: Project-Restrict ResultSet (5): 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 Join ResultSet: Number of opens = 1 Rows seen from the left = 1 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: Project-Restrict ResultSet (3): 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: Table Scan ResultSet for TT2 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: 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 = 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: 0 Operator: = Ordered nulls: false Unknown return value: false Negate comparison result: false ij> select * from tt2 right outer join tt1 on tt1.c1 = tt2.c2 where tt2.c1 + 1= 3; C1 |C2 |C3 |C1 |C2 |C3 ----------------------------------------------------------------------- 2 |3 |4 |3 |4 |5 ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); 1 ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Statement Name: null Statement Text: select * from tt2 right outer join tt1 on tt1.c1 = tt2.c2 where tt2.c1 + 1= 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 Join ResultSet: Number of opens = 1 Rows seen from the left = 1 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: Project-Restrict ResultSet (3): 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: Table Scan ResultSet for TT2 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: 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 = 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: 0 Operator: = Ordered nulls: false Unknown return value: false Negate comparison result: false ij> select * from tt1 left outer join tt2 on tt1.c1 = tt2.c2 left outer join tt3 on tt2.c2 = tt3.c3 where tt3.c3 = 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 tt3.c3 = 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: Project-Restrict ResultSet (7): 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 Join ResultSet: Number of opens = 1 Rows seen from the left = 1 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 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 Right result set: Project-Restrict ResultSet (6): 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: 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 incorrect results -- when name is null was the 2nd predicate -- due to a bug in OJ->IJ xform code. select timeslots.slotno, inventory.itemno, capacity, name from inventory left outer join timeslots on inventory.capacity = timeslots.slotno left outer join reservations on timeslots.slotno = reservations.slotno where capacity > 3 and name is null; SLOTNO |ITEMNO |CAPACITY |NAME ---------------------------------------------------------------------------------------------------------------------------------------- NULL |1 |4 |NULL ij> select timeslots.slotno, inventory.itemno, capacity, name from inventory left outer join timeslots on inventory.capacity = timeslots.slotno left outer join reservations on timeslots.slotno = reservations.slotno where name is null and capacity > 3; SLOTNO |ITEMNO |CAPACITY |NAME ---------------------------------------------------------------------------------------------------------------------------------------- NULL |1 |4 |NULL ij> rollback; ij> -- bug 2930, cross join under outer join CREATE TABLE properties ( name VARCHAR(50), value VARCHAR(200)); 0 rows inserted/updated/deleted ij> INSERT INTO properties VALUES ('businessName', 'Cloud 9 Cafe'); 1 row inserted/updated/deleted ij> INSERT INTO properties VALUES ('lastReservationDate', '2001-12-31'); 1 row inserted/updated/deleted ij> CREATE TABLE inventory ( itemno INT NOT NULL PRIMARY KEY, capacity INT ); 0 rows inserted/updated/deleted ij> INSERT INTO inventory VALUES (1, 2); 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> INSERT INTO inventory VALUES (4, 2); 1 row inserted/updated/deleted ij> INSERT INTO inventory VALUES (5, 2); 1 row inserted/updated/deleted ij> INSERT INTO inventory VALUES (6, 4); 1 row inserted/updated/deleted ij> INSERT INTO inventory VALUES (7, 4); 1 row inserted/updated/deleted ij> INSERT INTO inventory VALUES (8, 4); 1 row inserted/updated/deleted ij> INSERT INTO inventory VALUES (9, 4); 1 row inserted/updated/deleted ij> INSERT INTO inventory VALUES (10, 4); 1 row inserted/updated/deleted ij> CREATE TABLE timeslots ( slot TIME NOT NULL PRIMARY KEY ); 0 rows inserted/updated/deleted ij> INSERT INTO timeslots VALUES('17:00:00'); 1 row inserted/updated/deleted ij> INSERT INTO timeslots VALUES('17:30:00'); 1 row inserted/updated/deleted ij> INSERT INTO timeslots VALUES('18:00:00'); 1 row inserted/updated/deleted ij> INSERT INTO timeslots VALUES('18:30:00'); 1 row inserted/updated/deleted ij> INSERT INTO timeslots VALUES('19:00:00'); 1 row inserted/updated/deleted ij> INSERT INTO timeslots VALUES('19:30:00'); 1 row inserted/updated/deleted ij> INSERT INTO timeslots VALUES('20:00:00'); 1 row inserted/updated/deleted ij> INSERT INTO timeslots VALUES('20:30:00'); 1 row inserted/updated/deleted ij> INSERT INTO timeslots VALUES('21:00:00'); 1 row inserted/updated/deleted ij> INSERT INTO timeslots VALUES('21:30:00'); 1 row inserted/updated/deleted ij> INSERT INTO timeslots VALUES('22:00:00'); 1 row inserted/updated/deleted ij> CREATE TABLE reservations ( itemno INT CONSTRAINT inventory_fk REFERENCES inventory, slot TIME CONSTRAINT timeslots_fk REFERENCES timeslots, resdate DATE NOT NULL, name VARCHAR(100) NOT NULL, quantity INT, CONSTRAINT reservations_u UNIQUE(name, resdate)); 0 rows inserted/updated/deleted ij> INSERT INTO reservations VALUES(6, '17:00:00', '2000-07-13', 'Williams', 4); 1 row inserted/updated/deleted ij> INSERT INTO reservations VALUES(7, '17:00:00', '2000-07-13', 'Johnson', 4); 1 row inserted/updated/deleted ij> INSERT INTO reservations VALUES(8, '17:00:00', '2000-07-13', 'Allen', 3); 1 row inserted/updated/deleted ij> INSERT INTO reservations VALUES(9, '17:00:00', '2000-07-13', 'Dexmier', 4); 1 row inserted/updated/deleted ij> INSERT INTO reservations VALUES(1, '17:30:00', '2000-07-13', 'Gates', 2); 1 row inserted/updated/deleted ij> INSERT INTO reservations VALUES(2, '17:30:00', '2000-07-13', 'McNealy', 2); 1 row inserted/updated/deleted ij> INSERT INTO reservations VALUES(3, '17:30:00', '2000-07-13', 'Hoffman', 1); 1 row inserted/updated/deleted ij> INSERT INTO reservations VALUES(4, '17:30:00', '2000-07-13', 'Sippl', 2); 1 row inserted/updated/deleted ij> INSERT INTO reservations VALUES(6, '17:30:00', '2000-07-13', 'Yang', 4); 1 row inserted/updated/deleted ij> INSERT INTO reservations VALUES(7, '17:30:00', '2000-07-13', 'Meyers', 4); 1 row inserted/updated/deleted ij> select max(name), max(resdate) from inventory join timeslots on inventory.capacity is not null left outer join reservations on inventory.itemno = reservations.itemno and reservations.slot = timeslots.slot; 1 |2 --------------------------------------------------------------------------------------------------------------- Yang |2000-07-13 WARNING 01003: Null values were eliminated from the argument of a column function. ij> rollback; ij> -- bug 2931, cross join under outer join CREATE TABLE properties ( name VARCHAR(50), value VARCHAR(200)); 0 rows inserted/updated/deleted ij> INSERT INTO properties VALUES ('businessName', 'Cloud 9 Cafe'); 1 row inserted/updated/deleted ij> INSERT INTO properties VALUES ('lastReservationDate', '2001-12-31'); 1 row inserted/updated/deleted ij> CREATE TABLE inventory ( itemno INT NOT NULL PRIMARY KEY, capacity INT ); 0 rows inserted/updated/deleted ij> INSERT INTO inventory VALUES (1, 2); 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> INSERT INTO inventory VALUES (4, 2); 1 row inserted/updated/deleted ij> INSERT INTO inventory VALUES (5, 2); 1 row inserted/updated/deleted ij> INSERT INTO inventory VALUES (6, 4); 1 row inserted/updated/deleted ij> INSERT INTO inventory VALUES (7, 4); 1 row inserted/updated/deleted ij> INSERT INTO inventory VALUES (8, 4); 1 row inserted/updated/deleted ij> INSERT INTO inventory VALUES (9, 4); 1 row inserted/updated/deleted ij> INSERT INTO inventory VALUES (10, 4); 1 row inserted/updated/deleted ij> CREATE TABLE timeslots ( slot TIME NOT NULL PRIMARY KEY ); 0 rows inserted/updated/deleted ij> INSERT INTO timeslots VALUES('17:00:00'); 1 row inserted/updated/deleted ij> INSERT INTO timeslots VALUES('17:30:00'); 1 row inserted/updated/deleted ij> INSERT INTO timeslots VALUES('18:00:00'); 1 row inserted/updated/deleted ij> INSERT INTO timeslots VALUES('18:30:00'); 1 row inserted/updated/deleted ij> INSERT INTO timeslots VALUES('19:00:00'); 1 row inserted/updated/deleted ij> INSERT INTO timeslots VALUES('19:30:00'); 1 row inserted/updated/deleted ij> INSERT INTO timeslots VALUES('20:00:00'); 1 row inserted/updated/deleted ij> INSERT INTO timeslots VALUES('20:30:00'); 1 row inserted/updated/deleted ij> INSERT INTO timeslots VALUES('21:00:00'); 1 row inserted/updated/deleted ij> INSERT INTO timeslots VALUES('21:30:00'); 1 row inserted/updated/deleted ij> INSERT INTO timeslots VALUES('22:00:00'); 1 row inserted/updated/deleted ij> CREATE TABLE reservations ( itemno INT CONSTRAINT inventory_fk REFERENCES inventory, slot TIME CONSTRAINT timeslots_fk REFERENCES timeslots, resdate DATE NOT NULL, name VARCHAR(100) NOT NULL, quantity INT, CONSTRAINT reservations_u UNIQUE(name, resdate)); 0 rows inserted/updated/deleted ij> INSERT INTO reservations VALUES(6, '17:00:00', '2000-07-13', 'Williams', 4); 1 row inserted/updated/deleted ij> INSERT INTO reservations VALUES(7, '17:00:00', '2000-07-13', 'Johnson', 4); 1 row inserted/updated/deleted ij> INSERT INTO reservations VALUES(8, '17:00:00', '2000-07-13', 'Allen', 3); 1 row inserted/updated/deleted ij> INSERT INTO reservations VALUES(9, '17:00:00', '2000-07-13', 'Dexmier', 4); 1 row inserted/updated/deleted ij> INSERT INTO reservations VALUES(1, '17:30:00', '2000-07-13', 'Gates', 2); 1 row inserted/updated/deleted ij> INSERT INTO reservations VALUES(2, '17:30:00', '2000-07-13', 'McNealy', 2); 1 row inserted/updated/deleted ij> INSERT INTO reservations VALUES(3, '17:30:00', '2000-07-13', 'Hoffman', 1); 1 row inserted/updated/deleted ij> INSERT INTO reservations VALUES(4, '17:30:00', '2000-07-13', 'Sippl', 2); 1 row inserted/updated/deleted ij> INSERT INTO reservations VALUES(6, '17:30:00', '2000-07-13', 'Yang', 4); 1 row inserted/updated/deleted ij> INSERT INTO reservations VALUES(7, '17:30:00', '2000-07-13', 'Meyers', 4); 1 row inserted/updated/deleted ij> -- this query should return values from the 'slot' column (type date) -- but it seems to be returning integers! select max(timeslots.slot) from inventory inner join timeslots on inventory.capacity is not null left outer join reservations on inventory.capacity = reservations.itemno and reservations.slot = timeslots.slot; 1 -------- 22:00:00 ij> rollback; ij> -- bug 2897 Push join predicates from where clause -- to right select * from t1 inner join t2 on 1=1 left outer join t3 on t1.c1 = t3.c1 where t1.c1 = t2.c1; C1 |C1 |C1 ----------------------------------- 1 |1 |NULL 3 |3 |3 3 |3 |3 ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); 1 ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Statement Name: null Statement Text: -- bug 2897 Push join predicates from where clause -- to right select * from t1 inner join t2 on 1=1 left outer join t3 on t1.c1 = t3.c1 where t1.c1 = t2.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: Nested Loop Left Outer Join ResultSet: Number of opens = 1 Rows seen from the left = 3 Rows seen from the right = 2 Empty right rows returned = 1 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: Hash Join ResultSet: Number of opens = 1 Rows seen from the left = 5 Rows seen from the right = 3 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 T1 at read committed isolation level using instantaneous share row locking chosen by the optimizer Number of opens = 1 Rows seen = 5 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=1 Number of pages visited=1 Number of rows qualified=5 Number of rows visited=5 Scan type=heap start position: null stop position: null qualifiers: None Right result set: Project-Restrict ResultSet (5): Number of opens = 5 Rows seen = 3 Rows filtered = 0 restriction = false 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 T2 at read committed isolation level using instantaneous share row locking: Number of opens = 5 Hash table size = 4 Hash key is column number 0 Rows seen = 3 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=1 Number of pages visited=1 Number of rows qualified=5 Number of rows visited=5 Scan type=heap start position: null stop position: null scan qualifiers: None next qualifiers: Column[0][0] Id: 0 Operator: = Ordered nulls: false Unknown return value: false Negate comparison result: false Right result set: Table Scan ResultSet for T3 at read committed isolation level using instantaneous share row locking chosen by the optimizer Number of opens = 3 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=1 Number of pages visited=1 Number of rows qualified=2 Number of rows visited=15 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> -- Test fix for bug 5659 create table xxx (a int not null); 0 rows inserted/updated/deleted ij> create table yyy (a int not null); 0 rows inserted/updated/deleted ij> insert into xxx values (1); 1 row inserted/updated/deleted ij> select * from xxx left join yyy on (xxx.a=yyy.a); A |A ----------------------- 1 |NULL ij> insert into xxx values (null); ERROR 23502: Column 'A' cannot accept a NULL value. ij> select * from xxx; A ----------- 1 ij> drop table xxx; 0 rows inserted/updated/deleted ij> drop table yyy; 0 rows inserted/updated/deleted ij> -- Defect 5658. Disable querries with ambiguous references. create table ttab1 (a int, b int); 0 rows inserted/updated/deleted ij> insert into ttab1 values (1,1),(2,2); 2 rows inserted/updated/deleted ij> create table ttab2 (c int, d int); 0 rows inserted/updated/deleted ij> insert into ttab2 values (1,1),(2,2); 2 rows inserted/updated/deleted ij> -- this statement should raise an error because -- more than one object table includes column "b" select cor1.*, cor2.* from ttab1 cor1 left outer join ttab2 on (b = d), ttab1 left outer join ttab2 cor2 on (b = d); ERROR 42X03: Column name 'B' is in more than one table in the FROM list. ij> select cor1.*, cor2.* from ttab1 cor1 left outer join ttab2 on (b = d), ttab1 left outer join ttab2 cor2 on (b = cor2.d); ERROR 42X03: Column name 'B' is in more than one table in the FROM list. ij> -- This should pass select cor1.*, cor2.* from ttab1 left outer join ttab2 on (b = d), ttab1 cor1 left outer join ttab2 cor2 on (cor1.b = cor2.d); A |B |C |D ----------------------------------------------- 1 |1 |1 |1 2 |2 |2 |2 1 |1 |1 |1 2 |2 |2 |2 ij> -- These should fail too select * from ttab1, ttab1 left outer join ttab2 on (a=c); ERROR 42X03: Column name 'TTAB1.A' is in more than one table in the FROM list. ij> select * from ttab1 cor1, ttab1 left outer join ttab2 on (cor1.a=c); ERROR 42972: An ON clause associated with a JOIN operator is not valid. ij> -- This should pass select * from ttab1, ttab1 cor1 left outer join ttab2 on (cor1.a=c); A |B |A |B |C |D ----------------------------------------------------------------------- 1 |1 |1 |1 |1 |1 2 |2 |1 |1 |1 |1 1 |1 |2 |2 |2 |2 2 |2 |2 |2 |2 |2 ij> drop table ttab1; 0 rows inserted/updated/deleted ij> drop table ttab2; 0 rows inserted/updated/deleted ij> -- Test 5164 CREATE TABLE "APP"."GOVT_AGCY" ("GVA_ID" NUMERIC(20,0) NOT NULL, "GVA_ORL_ID" NUMERIC(20,0) NOT NULL, "GVA_GAC_ID" NUMERIC(20,0)); 0 rows inserted/updated/deleted ij> CREATE TABLE "APP"."GEO_STRC_ELMT" ("GSE_ID" NUMERIC(20,0) NOT NULL, "GSE_GSET_ID" NUMERIC(20,0) NOT NULL, "GSE_GA_ID_PRNT" NUMERIC(20,0) NOT NULL, "GSE_GA_ID_CHLD" NUMERIC(20,0) NOT NULL); 0 rows inserted/updated/deleted ij> CREATE TABLE "APP"."GEO_AREA" ("GA_ID" NUMERIC(20,0) NOT NULL, "GA_GAT_ID" NUMERIC(20,0) NOT NULL, "GA_NM" VARCHAR(30) NOT NULL, "GA_ABRV_NM" VARCHAR(5)); 0 rows inserted/updated/deleted ij> CREATE TABLE "APP"."REG" ("REG_ID" NUMERIC(20,0) NOT NULL, "REG_NM" VARCHAR(60) NOT NULL, "REG_DESC" VARCHAR(240), "REG_ABRV_NM" VARCHAR(15), "REG_CD" NUMERIC(8,0) NOT NULL, "REG_STRT_DT" TIMESTAMP NOT NULL, "REG_END_DT" TIMESTAMP NOT NULL DEFAULT 'xxxxxxFILTERED-TIMESTAMPxxxxx, "REG_EMPR_LIAB_IND" CHAR(1) NOT NULL DEFAULT 'N', "REG_PAYR_TAX_SURG_CRTF_IND" CHAR(1) NOT NULL DEFAULT 'N', "REG_PYT_ID" NUMERIC(20,0), "REG_GA_ID" NUMERIC(20,0) NOT NULL, "REG_GVA_ID" NUMERIC(20,0) NOT NULL, "REG_REGT_ID" NUMERIC(20,0) NOT NULL, "REG_PRNT_ID" NUMERIC(20,0)); 0 rows inserted/updated/deleted ij> -- This should not get ArrayIndexOutofBound exception SELECT 1 FROM reg JOIN geo_area jrsd ON (jrsd.ga_id = reg.reg_ga_id) LEFT OUTER JOIN geo_strc_elmt gse ON (gse.gse_ga_id_chld = reg.reg_ga_id) LEFT OUTER JOIN geo_area prnt ON (prnt.ga_id = reg.reg_ga_id) JOIN govt_agcy gva ON (reg.reg_gva_id = gva.gva_id); 1 ----------- ij> DROP TABLE "APP"."GOVT_AGCY"; 0 rows inserted/updated/deleted ij> DROP TABLE "APP"."GEO_STRC_ELMT"; 0 rows inserted/updated/deleted ij> DROP TABLE "APP"."GEO_AREA"; 0 rows inserted/updated/deleted ij> DROP TABLE "APP"."REG"; 0 rows inserted/updated/deleted ij> -- reset autocommit autocommit on; ij> -- drop the tables drop table t1; 0 rows inserted/updated/deleted ij> drop table t2; 0 rows inserted/updated/deleted ij> drop table t3; 0 rows inserted/updated/deleted ij> drop table tt1; 0 rows inserted/updated/deleted ij> drop table tt2; 0 rows inserted/updated/deleted ij> drop table tt3; 0 rows inserted/updated/deleted ij> drop table insert_test; 0 rows inserted/updated/deleted ij> drop table empty_table; 0 rows inserted/updated/deleted ij> drop table a; 0 rows inserted/updated/deleted ij> drop table b; 0 rows inserted/updated/deleted ij> drop table c; 0 rows inserted/updated/deleted ij> drop table oj; 0 rows inserted/updated/deleted ij> drop table x; 0 rows inserted/updated/deleted ij> drop table y; 0 rows inserted/updated/deleted ij>