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 inner 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 insert_test(c1 int, c2 int, c3 int); 0 rows inserted/updated/deleted ij> -- populate the tables insert into t1 values 1, 2, 3, 4; 4 rows inserted/updated/deleted ij> insert into t2 values 1, 3, 5, 6; 4 rows inserted/updated/deleted ij> insert into t3 values 2, 3, 5, 7; 4 rows inserted/updated/deleted ij> -- negative tests -- no join clause select * from t1 join t2; ERROR 42X01: Syntax error: Encountered "" at line 4, column 24. ij> select * from t1 inner join t2; ERROR 42X01: Syntax error: Encountered "" at line 1, column 30. ij> -- empty column list select * from t1 join t2 using (); ERROR 42X01: Syntax error: Encountered "using" at line 2, column 26. ij> -- non-boolean join clause select * from t1 join t2 on 1; ERROR 42Y12: The ON clause of a JOIN is a 'INTEGER' expression. It must be a BOOLEAN expression. ij> -- duplicate exposed names, DB2 extension -- DB2 UDB: PASS -- DB2 CS: FAIL select * from t1 join t1 on 1=1; ERROR 42X03: Column name 'T1.C1' is in more than one table in the FROM list. ij> -- duplicate exposed names select * from t1 join t1 on c1 = 1; ERROR 42X03: Column name 'T1.C1' is in more than one table in the FROM list. ij> select * from t1 join t1 on (c1); ERROR 42X03: Column name 'T1.C1' is in more than one table in the FROM list. ij> -- join clause only allowed to contain column references from tables being -- joined. DB2 doesn't allow references to correlated columns select * from t1, t2 join t3 on t1.c1 = t2.c1; ERROR 42972: An ON clause associated with a JOIN operator is not valid. ij> -- should match db2's behavior by raising an error select * from t2 b inner join t3 c on a.c1 = b.c1 and b.c1 = c.c1; ERROR 42X04: Column 'A.C1' is either not in any table in the FROM list or appears within a join specification and is outside the scope of the join specification or appears in a HAVING clause and is not in the GROUP BY list. If this is a CREATE or ALTER TABLE statement then 'A.C1' is not a column in the target table. ij> select * from t3 b where exists (select * from t1 a inner join t2 on b.c1 = t2.c1); ERROR 42972: An ON clause associated with a JOIN operator is not valid. ij> select * from t3 where exists (select * from t1 inner join t2 on t3.c1 = t2.c1); ERROR 42972: An ON clause associated with a JOIN operator is not valid. ij> -- positive tests select a.c1 from t1 a join t2 b on a.c1 = b.c1; C1 ----------- 1 3 ij> select a.x from t1 a (x) join t2 b (x) on a.x = b.x; X ----------- 1 3 ij> -- ANSI "extension" - duplicate exposed names allowed when no column references -- this may go away if we can figure out how to detect this error and -- get bored enough to prioritize the fix get cursor c as 'select 1 from t1 join t1 on 1=1'; ij> next c; 1 ----------- 1 ij> close c; ij> -- parameters and join clause prepare asdf as 'select * from t1 join t2 on ?=1 and t1.c1 = t2.c1'; ij> execute asdf using 'values 1'; C1 |C1 ----------------------- 1 |1 3 |3 ij> remove asdf; ij> prepare asdf as 'select * from t1 join t2 on t1.c1 = t2.c1 and t1.c1 = ?'; ij> execute asdf using 'values 1'; C1 |C1 ----------------------- 1 |1 ij> remove asdf; ij> -- additional predicates outside of the join clause select * from t1 join t2 on t1.c1 = t2.c1 where t1.c1 = 1; C1 |C1 ----------------------- 1 |1 ij> select * from t1 join t2 on t1.c1 = 1 where t2.c1 = t1.c1; C1 |C1 ----------------------- 1 |1 ij> -- subquery in join clause, not allowed in DB2 compatibility mode select * from t1 a join t2 b on a.c1 = b.c1 and a.c1 = (select c1 from t1 where a.c1 = t1.c1); ERROR 42972: An ON clause associated with a JOIN operator is not valid. ij> select * from t1 a 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> -- correlated columns select * from t1 a where exists (select * from t1 inner join t2 on a.c1 = t2.c1); ERROR 42972: An ON clause associated with a JOIN operator is not valid. ij> -- nested joins select * from t1 join t2 on t1.c1 = t2.c1 inner join t3 on t1.c1 = t3.c1; C1 |C1 |C1 ----------------------------------- 3 |3 |3 ij> -- parens select * from (t1 join t2 on t1.c1 = t2.c1) inner join t3 on t1.c1 = t3.c1; C1 |C1 |C1 ----------------------------------- 3 |3 |3 ij> select * from t1 join (t2 inner join t3 on t2.c1 = t3.c1) on t1.c1 = t2.c1; C1 |C1 |C1 ----------------------------------- 3 |3 |3 ij> -- [inner] joins select * from t1 a left outer join t2 b on a.c1 = b.c1 inner join t3 c on b.c1 = c.c1; C1 |C1 |C1 ----------------------------------- 3 |3 |3 ij> select * from (t1 a left outer join t2 b on a.c1 = b.c1) inner join t3 c on b.c1 = c.c1; C1 |C1 |C1 ----------------------------------- 3 |3 |3 ij> select * from t1 a join t2 b on a.c1 = b.c1 inner join t3 c on c.c1 = a.c1 where c.c1 > 2 and a.c1 > 2; C1 |C1 |C1 ----------------------------------- 3 |3 |3 ij> select * from (t1 a join t2 b on a.c1 = b.c1) inner join t3 c on c.c1 = a.c1 where c.c1 > 2 and a.c1 > 2; C1 |C1 |C1 ----------------------------------- 3 |3 |3 ij> select * from t1 a join (t2 b inner join t3 c on c.c1 = b.c1) on a.c1 = b.c1 where c.c1 > 2 and b.c1 > 2; C1 |C1 |C1 ----------------------------------- 3 |3 |3 ij> -- test insert/update/delete insert into insert_test select * from t1 a join t2 b on a.c1 = b.c1 inner join t3 c on a.c1 <> c.c1; 7 rows inserted/updated/deleted ij> select * from insert_test; C1 |C2 |C3 ----------------------------------- 1 |1 |2 1 |1 |3 1 |1 |5 1 |1 |7 3 |3 |2 3 |3 |5 3 |3 |7 ij> update insert_test set c1 = (select 9 from t1 a join t1 b on a.c1 = b.c1 where a.c1 = 1) where c1 = 1; 4 rows inserted/updated/deleted ij> select * from insert_test; C1 |C2 |C3 ----------------------------------- 9 |1 |2 9 |1 |3 9 |1 |5 9 |1 |7 3 |3 |2 3 |3 |5 3 |3 |7 ij> delete from insert_test where c1 = (select 9 from t1 a join t1 b on a.c1 = b.c1 where a.c1 = 1); 4 rows inserted/updated/deleted ij> select * from insert_test; C1 |C2 |C3 ----------------------------------- 3 |3 |2 3 |3 |5 3 |3 |7 ij> -- multicolumn join select * from insert_test a join insert_test b on a.c1 = b.c1 and a.c2 = b.c2 and a.c3 = b.c3; C1 |C2 |C3 |C1 |C2 |C3 ----------------------------------------------------------------------- 3 |3 |2 |3 |3 |2 3 |3 |5 |3 |3 |5 3 |3 |7 |3 |3 |7 ij> -- continue with insert tests delete from insert_test; 3 rows inserted/updated/deleted ij> insert into insert_test select * from (select * from t1 a join t2 b on a.c1 = b.c1 inner join t3 c on a.c1 <> c.c1) d (c1, c2, c3); 7 rows inserted/updated/deleted ij> select * from insert_test; C1 |C2 |C3 ----------------------------------- 1 |1 |2 1 |1 |3 1 |1 |5 1 |1 |7 3 |3 |2 3 |3 |5 3 |3 |7 ij> delete from insert_test; 7 rows inserted/updated/deleted ij> -- reset autocomiit 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 insert_test; 0 rows inserted/updated/deleted ij>