| 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. |
| -- |
| -- |
| -- this test shows the current supported join functionality |
| -- |
| |
| -- create some tables |
| create table t1 (t1_c1 int, t1_c2 char(10)); |
| 0 rows inserted/updated/deleted |
| ij> create table t2 (t2_c1 int, t2_c2 char(10)); |
| 0 rows inserted/updated/deleted |
| ij> create table t3 (t3_c1 int, t3_c2 char(10)); |
| 0 rows inserted/updated/deleted |
| ij> create table t4 (t4_c1 int, t4_c2 char(10)); |
| 0 rows inserted/updated/deleted |
| ij> -- populate the tables |
| insert into t1 values (1, 't1-row1'); |
| 1 row inserted/updated/deleted |
| ij> insert into t1 values (2, 't1-row2'); |
| 1 row inserted/updated/deleted |
| ij> insert into t2 values (1, 't2-row1'); |
| 1 row inserted/updated/deleted |
| ij> insert into t2 values (2, 't2-row2'); |
| 1 row inserted/updated/deleted |
| ij> insert into t3 values (1, 't3-row1'); |
| 1 row inserted/updated/deleted |
| ij> insert into t3 values (2, 't3-row2'); |
| 1 row inserted/updated/deleted |
| ij> insert into t4 values (1, 't4-row1'); |
| 1 row inserted/updated/deleted |
| ij> insert into t4 values (2, 't4-row2'); |
| 1 row inserted/updated/deleted |
| ij> -- negative test, same exposed name |
| select * from t1, t1; |
| ERROR 42X09: The table or alias name 'T1' is used more than once in the FROM list. |
| ij> -- cartesian products |
| -- full projection |
| select * from t1, t2; |
| T1_C1 |T1_C2 |T2_C1 |T2_C2 |
| --------------------------------------------- |
| 1 |t1-row1 |1 |t2-row1 |
| 1 |t1-row1 |2 |t2-row2 |
| 2 |t1-row2 |1 |t2-row1 |
| 2 |t1-row2 |2 |t2-row2 |
| ij> select * from t1 a, t2 b, t3 cc, t4 d order by 1,2,3,4,5,6; |
| T1_C1 |T1_C2 |T2_C1 |T2_C2 |T3_C1 |T3_C2 |T4_C1 |T4_C2 |
| ------------------------------------------------------------------------------------------- |
| 1 |t1-row1 |1 |t2-row1 |1 |t3-row1 |2 |t4-row2 |
| 1 |t1-row1 |1 |t2-row1 |1 |t3-row1 |1 |t4-row1 |
| 1 |t1-row1 |1 |t2-row1 |2 |t3-row2 |2 |t4-row2 |
| 1 |t1-row1 |1 |t2-row1 |2 |t3-row2 |1 |t4-row1 |
| 1 |t1-row1 |2 |t2-row2 |1 |t3-row1 |2 |t4-row2 |
| 1 |t1-row1 |2 |t2-row2 |1 |t3-row1 |1 |t4-row1 |
| 1 |t1-row1 |2 |t2-row2 |2 |t3-row2 |2 |t4-row2 |
| 1 |t1-row1 |2 |t2-row2 |2 |t3-row2 |1 |t4-row1 |
| 2 |t1-row2 |1 |t2-row1 |1 |t3-row1 |2 |t4-row2 |
| 2 |t1-row2 |1 |t2-row1 |1 |t3-row1 |1 |t4-row1 |
| 2 |t1-row2 |1 |t2-row1 |2 |t3-row2 |2 |t4-row2 |
| 2 |t1-row2 |1 |t2-row1 |2 |t3-row2 |1 |t4-row1 |
| 2 |t1-row2 |2 |t2-row2 |1 |t3-row1 |2 |t4-row2 |
| 2 |t1-row2 |2 |t2-row2 |1 |t3-row1 |1 |t4-row1 |
| 2 |t1-row2 |2 |t2-row2 |2 |t3-row2 |2 |t4-row2 |
| 2 |t1-row2 |2 |t2-row2 |2 |t3-row2 |1 |t4-row1 |
| ij> -- reorder columns |
| select t2.*, t1.* from t1, t2; |
| T2_C1 |T2_C2 |T1_C1 |T1_C2 |
| --------------------------------------------- |
| 1 |t2-row1 |1 |t1-row1 |
| 2 |t2-row2 |1 |t1-row1 |
| 1 |t2-row1 |2 |t1-row2 |
| 2 |t2-row2 |2 |t1-row2 |
| ij> select t2_c2, t1_c2, t1_c1, t2_c1 from t1, t2; |
| T2_C2 |T1_C2 |T1_C1 |T2_C1 |
| --------------------------------------------- |
| t2-row1 |t1-row1 |1 |1 |
| t2-row2 |t1-row1 |1 |2 |
| t2-row1 |t1-row2 |2 |1 |
| t2-row2 |t1-row2 |2 |2 |
| ij> -- project out columns |
| select t2_c2, t1_c1 from t1, t2; |
| T2_C2 |T1_C1 |
| ---------------------- |
| t2-row1 |1 |
| t2-row2 |1 |
| t2-row1 |2 |
| t2-row2 |2 |
| ij> select a.t1_c1, cc.t1_c1, e.t1_c1, g.t1_c1, i.t1_c1 from t1 a, t1 cc, t1 e, t1 g, t1 i; |
| T1_C1 |T1_C1 |T1_C1 |T1_C1 |T1_C1 |
| ----------------------------------------------------------- |
| 1 |1 |1 |1 |1 |
| 1 |1 |1 |1 |2 |
| 1 |1 |1 |2 |1 |
| 1 |1 |1 |2 |2 |
| 1 |1 |2 |1 |1 |
| 1 |1 |2 |1 |2 |
| 1 |1 |2 |2 |1 |
| 1 |1 |2 |2 |2 |
| 1 |2 |1 |1 |1 |
| 1 |2 |1 |1 |2 |
| 1 |2 |1 |2 |1 |
| 1 |2 |1 |2 |2 |
| 1 |2 |2 |1 |1 |
| 1 |2 |2 |1 |2 |
| 1 |2 |2 |2 |1 |
| 1 |2 |2 |2 |2 |
| 2 |1 |1 |1 |1 |
| 2 |1 |1 |1 |2 |
| 2 |1 |1 |2 |1 |
| 2 |1 |1 |2 |2 |
| 2 |1 |2 |1 |1 |
| 2 |1 |2 |1 |2 |
| 2 |1 |2 |2 |1 |
| 2 |1 |2 |2 |2 |
| 2 |2 |1 |1 |1 |
| 2 |2 |1 |1 |2 |
| 2 |2 |1 |2 |1 |
| 2 |2 |1 |2 |2 |
| 2 |2 |2 |1 |1 |
| 2 |2 |2 |1 |2 |
| 2 |2 |2 |2 |1 |
| 2 |2 |2 |2 |2 |
| ij> -- project/restricts |
| select a.t1_c1, b.t1_c1, cc.t1_c1, d.t1_c1, e.t1_c1, f.t1_c1, g.t1_c1, h.t1_c1, i.t1_c1, j.t1_c1 |
| from t1 a, t1 b, t1 cc, t1 d, t1 e, t1 f, t1 g, t1 h, t1 i, t1 j |
| where a.t1_c2 = b.t1_c2 and b.t1_c2 = cc.t1_c2 and cc.t1_c2 = d.t1_c2 and |
| d.t1_c2 = e.t1_c2 and e.t1_c2 = f.t1_c2 and f.t1_c2 = g.t1_c2 and |
| g.t1_c2 = h.t1_c2 and h.t1_c2 = i.t1_c2 and i.t1_c2 = j.t1_c2; |
| T1_C1 |T1_C1 |T1_C1 |T1_C1 |T1_C1 |T1_C1 |T1_C1 |T1_C1 |T1_C1 |T1_C1 |
| ----------------------------------------------------------------------------------------------------------------------- |
| 1 |1 |1 |1 |1 |1 |1 |1 |1 |1 |
| 2 |2 |2 |2 |2 |2 |2 |2 |2 |2 |
| ij> select a.t1_c1, b.t1_c1, cc.t1_c1, d.t1_c1, e.t1_c1, f.t1_c1, g.t1_c1, h.t1_c1, i.t1_c1, j.t1_c1 |
| from t1 a, t1 b, t1 cc, t1 d, t1 e, t1 f, t1 g, t1 h, t1 i, t1 j |
| where a.t1_c1 = 1 and b.t1_c1 = 1 and cc.t1_c1 = 1 and d.t1_c1 = 1 and e.t1_c1 = 1 and |
| f.t1_c1 = 1 and g.t1_c1 = 1 and h.t1_c1 = 1 and i.t1_c1 = 1 and |
| a.t1_c2 = b.t1_c2 and b.t1_c2 = cc.t1_c2 and cc.t1_c2 = d.t1_c2 and |
| d.t1_c2 = e.t1_c2 and e.t1_c2 = f.t1_c2 and f.t1_c2 = g.t1_c2 and |
| g.t1_c2 = h.t1_c2 and h.t1_c2 = i.t1_c2 and i.t1_c2 = j.t1_c2; |
| T1_C1 |T1_C1 |T1_C1 |T1_C1 |T1_C1 |T1_C1 |T1_C1 |T1_C1 |T1_C1 |T1_C1 |
| ----------------------------------------------------------------------------------------------------------------------- |
| 1 |1 |1 |1 |1 |1 |1 |1 |1 |1 |
| ij> -- project out entire tables |
| select 1, 2 from t1, t2; |
| 1 |2 |
| ----------------------- |
| 1 |2 |
| 1 |2 |
| 1 |2 |
| 1 |2 |
| ij> select 1, t1.t1_c1 from t1, t2; |
| 1 |T1_C1 |
| ----------------------- |
| 1 |1 |
| 1 |1 |
| 1 |2 |
| 1 |2 |
| ij> select t2.t2_c2,1 from t1, t2; |
| T2_C2 |2 |
| ---------------------- |
| t2-row1 |1 |
| t2-row2 |1 |
| t2-row1 |1 |
| t2-row2 |1 |
| ij> -- bug #306 |
| select c.t1_c1 from (select a.t1_c1 from t1 a, t1 b) c, t1 d where c.t1_c1 = d.t1_c1; |
| T1_C1 |
| ----------- |
| 1 |
| 1 |
| 2 |
| 2 |
| ij> -- create a table for testing inserts |
| create table instab (instab_c1 int, instab_c2 char(10), instab_c3 int, |
| instab_c4 char(10)); |
| 0 rows inserted/updated/deleted |
| ij> -- insert select with joins |
| -- cartesian product |
| insert into instab select * from t1, t2; |
| 4 rows inserted/updated/deleted |
| ij> select * from instab; |
| INSTAB_C1 |INSTAB_C2 |INSTAB_C3 |INSTAB_C4 |
| --------------------------------------------- |
| 1 |t1-row1 |1 |t2-row1 |
| 1 |t1-row1 |2 |t2-row2 |
| 2 |t1-row2 |1 |t2-row1 |
| 2 |t1-row2 |2 |t2-row2 |
| ij> delete from instab; |
| 4 rows inserted/updated/deleted |
| ij> insert into instab (instab_c1, instab_c2, instab_c3, instab_c4) |
| select * from t1, t2; |
| 4 rows inserted/updated/deleted |
| ij> select * from instab; |
| INSTAB_C1 |INSTAB_C2 |INSTAB_C3 |INSTAB_C4 |
| --------------------------------------------- |
| 1 |t1-row1 |1 |t2-row1 |
| 1 |t1-row1 |2 |t2-row2 |
| 2 |t1-row2 |1 |t2-row1 |
| 2 |t1-row2 |2 |t2-row2 |
| ij> delete from instab; |
| 4 rows inserted/updated/deleted |
| ij> insert into instab (instab_c1, instab_c2, instab_c3, instab_c4) |
| select t2_c1, t2_c2, t1_c1, t1_c2 from t1, t2; |
| 4 rows inserted/updated/deleted |
| ij> select * from instab; |
| INSTAB_C1 |INSTAB_C2 |INSTAB_C3 |INSTAB_C4 |
| --------------------------------------------- |
| 1 |t2-row1 |1 |t1-row1 |
| 2 |t2-row2 |1 |t1-row1 |
| 1 |t2-row1 |2 |t1-row2 |
| 2 |t2-row2 |2 |t1-row2 |
| ij> delete from instab; |
| 4 rows inserted/updated/deleted |
| ij> insert into instab (instab_c3, instab_c1, instab_c2, instab_c4) |
| select t2_c1, t1_c1, t1_c2, t2_c2 from t1, t2; |
| 4 rows inserted/updated/deleted |
| ij> select * from instab; |
| INSTAB_C1 |INSTAB_C2 |INSTAB_C3 |INSTAB_C4 |
| --------------------------------------------- |
| 1 |t1-row1 |1 |t2-row1 |
| 1 |t1-row1 |2 |t2-row2 |
| 2 |t1-row2 |1 |t2-row1 |
| 2 |t1-row2 |2 |t2-row2 |
| ij> delete from instab; |
| 4 rows inserted/updated/deleted |
| ij> -- projection |
| insert into instab (instab_c1, instab_c3) |
| select t1_c1, t2_c1 from t1, t2; |
| 4 rows inserted/updated/deleted |
| ij> select * from instab; |
| INSTAB_C1 |INSTAB_C2 |INSTAB_C3 |INSTAB_C4 |
| --------------------------------------------- |
| 1 |NULL |1 |NULL |
| 1 |NULL |2 |NULL |
| 2 |NULL |1 |NULL |
| 2 |NULL |2 |NULL |
| ij> delete from instab; |
| 4 rows inserted/updated/deleted |
| ij> -- project out 1 or more tables from join |
| insert into instab select 1, '2', 3, '4' from t1, t2; |
| 4 rows inserted/updated/deleted |
| ij> select * from instab; |
| INSTAB_C1 |INSTAB_C2 |INSTAB_C3 |INSTAB_C4 |
| --------------------------------------------- |
| 1 |2 |3 |4 |
| 1 |2 |3 |4 |
| 1 |2 |3 |4 |
| 1 |2 |3 |4 |
| ij> delete from instab; |
| 4 rows inserted/updated/deleted |
| ij> insert into instab select 1, t1.t1_c2, 3, t1.t1_c2 from t1, t2; |
| 4 rows inserted/updated/deleted |
| ij> select * from instab; |
| INSTAB_C1 |INSTAB_C2 |INSTAB_C3 |INSTAB_C4 |
| --------------------------------------------- |
| 1 |t1-row1 |3 |t1-row1 |
| 1 |t1-row1 |3 |t1-row1 |
| 1 |t1-row2 |3 |t1-row2 |
| 1 |t1-row2 |3 |t1-row2 |
| ij> delete from instab; |
| 4 rows inserted/updated/deleted |
| ij> insert into instab select t2.t2_c1, '2', t2.t2_c1, '4' from t1, t2; |
| 4 rows inserted/updated/deleted |
| ij> select * from instab; |
| INSTAB_C1 |INSTAB_C2 |INSTAB_C3 |INSTAB_C4 |
| --------------------------------------------- |
| 1 |2 |1 |4 |
| 2 |2 |2 |4 |
| 1 |2 |1 |4 |
| 2 |2 |2 |4 |
| ij> delete from instab; |
| 4 rows inserted/updated/deleted |
| ij> ------------------------------------------ |
| -- test optimizations where we push around |
| -- predicates (remapColumnReferences) |
| ------------------------------------------ |
| -- case |
| select t1_c1 from t1, t2 where (case when t1_c1 = 1 then t2_c2 end) = t2_c2; |
| T1_C1 |
| ----------- |
| 1 |
| 1 |
| ij> -- CHAR built-in function |
| select t1_c1 from t1, t2 where CHAR(t1_c1) = t2_c2; |
| T1_C1 |
| ----------- |
| ij> -- logical operator OR |
| select t1_c1 from t1, t2 where t1_c1 = 1 or t2_c1 = 2; |
| T1_C1 |
| ----------- |
| 1 |
| 1 |
| 2 |
| ij> -- logical operator AND |
| select t1_c1 from t1, t2 where t1_c1 = 2147483647 and 2147483647 = t2_c1; |
| T1_C1 |
| ----------- |
| ij> -- beetle 5421 |
| -- INT built-in function |
| select t1_c1 from t1, t2 where INT(t1_c1) = t2_c1; |
| T1_C1 |
| ----------- |
| 1 |
| 2 |
| ij> select t1_c1 from t1, t2 where t1_c1 = INT(2147483647) and INT(2147483647) = t2_c1; |
| T1_C1 |
| ----------- |
| ij> -- transitive closure - verify join condition doesn't get dropped |
| create table x(c1 int); |
| 0 rows inserted/updated/deleted |
| ij> create table y(c1 int); |
| 0 rows inserted/updated/deleted |
| ij> insert into x values 1, 2, null; |
| 3 rows inserted/updated/deleted |
| ij> insert into y values 1, 2, null; |
| 3 rows inserted/updated/deleted |
| ij> select * from x,y where x.c1 = y.c1 and x.c1 = 1 and y.c1 = 2; |
| C1 |C1 |
| ----------------------- |
| ij> select * from x,y where x.c1 = y.c1 and x.c1 is null; |
| C1 |C1 |
| ----------------------- |
| ij> select * from x,y where x.c1 = y.c1 and x.c1 is null and y.c1 = 2; |
| C1 |C1 |
| ----------------------- |
| ij> select * from x,y where x.c1 = y.c1 and x.c1 is null and y.c1 is null; |
| C1 |C1 |
| ----------------------- |
| ij> -- DERBY-2526: join node flattening leads to incorrect transitive closure, |
| -- which in turn results in incorrect results. |
| |
| -- Ex. 1: As posted to DERBY-2526: |
| |
| create table b2 (c1 int, c2 int, c3 char(1), c4 int, c5 int, c6 int); |
| 0 rows inserted/updated/deleted |
| ij> create table b4 (c7 int, c4 int, c6 int); |
| 0 rows inserted/updated/deleted |
| ij> create table b3 (c8 int, c9 int, c5 int, c6 int); |
| 0 rows inserted/updated/deleted |
| ij> create table b (c1 int, c2 int, c3 char(1), c4 int, c5 int, c6 int); |
| 0 rows inserted/updated/deleted |
| ij> create view bvw (c5, c1 ,c2 ,c3 ,c4) as |
| select c5, c1 ,c2 ,c3 ,c4 from b2 union |
| select c5, c1 ,c2 ,c3 ,c4 from b; |
| 0 rows inserted/updated/deleted |
| ij> create view bvw2 (c1 ,c2 ,c3 ,c4 ,c5) as |
| select c1 ,c2 ,c3 ,c4 ,c5 from b2 union |
| select c1 ,c2 ,c3 ,c4 ,c5 from b; |
| 0 rows inserted/updated/deleted |
| ij> insert into b4 (c7,c4,c6) values (4, 42, 31); |
| 1 row inserted/updated/deleted |
| ij> insert into b2 (c5,c1,c3,c4,c6) values (3,4, 'F',43,23); |
| 1 row inserted/updated/deleted |
| ij> insert into b3 (c5,c8,c9,c6) values (2,3,19,28); |
| 1 row inserted/updated/deleted |
| ij> -- Should see 1 row for *both* of these queries. |
| select b3.* from b3 join bvw on (b3.c8 = bvw.c5) join b4 on (bvw.c1 = b4.c7) where b4.c4 = 42; |
| C8 |C9 |C5 |C6 |
| ----------------------------------------------- |
| 3 |19 |2 |28 |
| ij> select b3.* from b3 join bvw2 on (b3.c8 = bvw2.c5) join b4 on (bvw2.c1 = b4.c7) where b4.c4 = 42; |
| C8 |C9 |C5 |C6 |
| ----------------------------------------------- |
| 3 |19 |2 |28 |
| ij> -- Cleanup. |
| drop view bvw; |
| 0 rows inserted/updated/deleted |
| ij> drop view bvw2; |
| 0 rows inserted/updated/deleted |
| ij> drop table b; |
| 0 rows inserted/updated/deleted |
| ij> drop table b2; |
| 0 rows inserted/updated/deleted |
| ij> drop table b3; |
| 0 rows inserted/updated/deleted |
| ij> drop table b4; |
| 0 rows inserted/updated/deleted |
| ij> -- Ex. 2: Simplified repro. |
| |
| create table b1 (c0 int); |
| 0 rows inserted/updated/deleted |
| ij> create table xx (c1 int, c2 int); |
| 0 rows inserted/updated/deleted |
| ij> create table b2 (c3 int, c4 int); |
| 0 rows inserted/updated/deleted |
| ij> insert into b1 values 1; |
| 1 row inserted/updated/deleted |
| ij> insert into xx values (0, 1); |
| 1 row inserted/updated/deleted |
| ij> insert into b2 values (0, 2); |
| 1 row inserted/updated/deleted |
| ij> -- Following should return 1 row. |
| select b1.* from |
| b1 JOIN (select * from xx) VW(c1,c2) on (b1.c0 = vw.c2) |
| JOIN b2 on (vw.c1 = b2.c3); |
| C0 |
| ----------- |
| 1 |
| ij> -- Try out various correlation name combinations to make sure that |
| -- correct column remapping occurs regardless of correlation name. |
| |
| select b1.* from |
| b1 JOIN (select * from xx) VW(ccx1,ccx2) on (b1.c0 = vw.ccx2) |
| JOIN b2 on (vw.ccx1 = b2.c3); |
| C0 |
| ----------- |
| 1 |
| ij> select b1.* from |
| b1 JOIN (select c1 as ccx1, c2 as ccx2 from xx) VW(ccx1,ccx2) on (b1.c0 = vw.ccx2) |
| JOIN b2 on (vw.ccx1 = b2.c3); |
| C0 |
| ----------- |
| 1 |
| ij> select b1.* from |
| b1 JOIN (select c1 as ccx1, c2 as ccx2 from xx) VW(x1,x2) on (b1.c0 = vw.x2) |
| JOIN b2 on (vw.x1 = b2.c3); |
| C0 |
| ----------- |
| 1 |
| ij> select b1.* from |
| b1 JOIN (select c1 as ccx1, c2 as ccx2 from xx) VW(c1,c2) on (b1.c0 = vw.c2) |
| JOIN b2 on (vw.c1 = b2.c3); |
| C0 |
| ----------- |
| 1 |
| ij> -- Cleanup. |
| drop table b1; |
| 0 rows inserted/updated/deleted |
| ij> drop table b2; |
| 0 rows inserted/updated/deleted |
| ij> drop table xx; |
| 0 rows inserted/updated/deleted |
| ij> -- DERBY-3023: join node flattening leads to incorrect search transitive |
| -- closure, which in turn leads to incorrect results. |
| |
| CREATE TABLE d3023_t1 (A INTEGER, B INTEGER); |
| 0 rows inserted/updated/deleted |
| ij> insert into d3023_t1 values (1, 1), (-2, 2), (3, 3); |
| 3 rows inserted/updated/deleted |
| ij> CREATE TABLE d3023_t2 (C INTEGER, D INTEGER); |
| 0 rows inserted/updated/deleted |
| ij> insert into d3023_t2 values (1, -1), (2, -2), (3, -3); |
| 3 rows inserted/updated/deleted |
| ij> CREATE TABLE d3023_t3 (I INTEGER, J INTEGER); |
| 0 rows inserted/updated/deleted |
| ij> insert into d3023_t3 values (-2, 1), (-3, -2); |
| 2 rows inserted/updated/deleted |
| ij> CREATE TABLE d3023_t4 (X INTEGER, Y INTEGER); |
| 0 rows inserted/updated/deleted |
| ij> insert into d3023_t4 values (1, 1), (2, 2), (3, 3); |
| 3 rows inserted/updated/deleted |
| ij> -- Incremental queries building up to the query in question... |
| |
| select distinct * from |
| d3023_t1 left outer join d3023_t2 on d3023_t1.a = d3023_t2.d; |
| A |B |C |D |
| ----------------------------------------------- |
| -2 |2 |2 |-2 |
| 1 |1 |NULL |NULL |
| 3 |3 |NULL |NULL |
| ij> select distinct * from |
| d3023_t1 left outer join d3023_t2 on d3023_t1.a = d3023_t2.d |
| where d3023_t1.a = -2; |
| A |B |C |D |
| ----------------------------------------------- |
| -2 |2 |2 |-2 |
| ij> select distinct * from |
| d3023_t1 left outer join d3023_t2 on d3023_t1.a = d3023_t2.d |
| inner join d3023_t3 on d3023_t1.a = d3023_t3.j; |
| A |B |C |D |I |J |
| ----------------------------------------------------------------------- |
| -2 |2 |2 |-2 |-3 |-2 |
| 1 |1 |NULL |NULL |-2 |1 |
| ij> select distinct * from |
| d3023_t1 left outer join d3023_t2 on d3023_t1.a = d3023_t2.d |
| inner join d3023_t3 on d3023_t1.a = d3023_t3.j |
| where d3023_t1.a = -2; |
| A |B |C |D |I |J |
| ----------------------------------------------------------------------- |
| -2 |2 |2 |-2 |-3 |-2 |
| ij> -- This query only returns a single row, even without the |
| -- explicit search predicate. |
| select distinct * from |
| d3023_t1 left outer join d3023_t2 on d3023_t1.a = d3023_t2.d |
| inner join d3023_t3 on d3023_t1.a = d3023_t3.j |
| inner join d3023_t4 on d3023_t2.c = d3023_t4.x; |
| A |B |C |D |I |J |X |Y |
| ----------------------------------------------------------------------------------------------- |
| -2 |2 |2 |-2 |-3 |-2 |2 |2 |
| ij> -- Slight variation of the same query. Add a search predicate |
| -- enforcing "d3023_t1.a = -2" to the join condition. Since the |
| -- row we saw in the previous query satisifies that predicate, |
| -- we should see the same row again. |
| select distinct * from |
| d3023_t1 left outer join d3023_t2 |
| on d3023_t1.a = d3023_t2.d AND d3023_t1.a = -2 |
| inner join d3023_t3 on d3023_t1.a = d3023_t3.j |
| inner join d3023_t4 on d3023_t2.c = d3023_t4.x; |
| A |B |C |D |I |J |X |Y |
| ----------------------------------------------------------------------------------------------- |
| -2 |2 |2 |-2 |-3 |-2 |2 |2 |
| ij> -- Same query as above, but with the predicate "d3023_t1.a = -2" |
| -- sitting at the top-most (outer) SELECT. That makes the predicate |
| -- available for inclusion in the "search transitive closure" logic |
| -- for the outer SELECT. That said, prior to the fix for DERBY-3023, |
| -- search transitive closure was incorrectly adding a new predicate, |
| -- d3023_t4.x = -2, to the query. This was because two different |
| -- column references were incorrectly mapped to the same column |
| -- position w.r.t. the outer join: i.e. "d3023_t1.a" in the search |
| -- predicate "d3023_t1.a = -2" AND "d3023_t2.c" in the join predicate |
| -- "d3023_t2.c = d3023_t4.x" were BOTH referencing the first column |
| -- in the HalfOuterJoinNode. As a result, the search transitive |
| -- closure logic thought that there was transitive equality between |
| -- the two predicates, which was incorrect. That in turn caused the |
| -- query to return incorrect results (no rows). With the fix for |
| -- DERBY-3023, this query should now return a single row. |
| |
| select distinct * from |
| d3023_t1 left outer join d3023_t2 on d3023_t1.a = d3023_t2.d |
| inner join d3023_t3 on d3023_t1.a = d3023_t3.j |
| inner join d3023_t4 on d3023_t2.c = d3023_t4.x |
| where d3023_t1.a = -2; |
| A |B |C |D |I |J |X |Y |
| ----------------------------------------------------------------------------------------------- |
| -2 |2 |2 |-2 |-3 |-2 |2 |2 |
| ij> -- Cleanup. |
| drop table d3023_t1; |
| 0 rows inserted/updated/deleted |
| ij> drop table d3023_t2; |
| 0 rows inserted/updated/deleted |
| ij> drop table d3023_t3; |
| 0 rows inserted/updated/deleted |
| ij> drop table d3023_t4; |
| 0 rows inserted/updated/deleted |
| ij> -- Beetle task 5000. Bug found by Websphere. Should not return any rows. |
| select t1_c1, t1_c2, t2_c1, t2_c2 |
| from t1, t2 |
| where t1_c1 = t2_c1 |
| and t1_c1 = 1 |
| and t2_c1 <> 1; |
| T1_C1 |T1_C2 |T2_C1 |T2_C2 |
| --------------------------------------------- |
| ij> -- Beetle task 4736 |
| create table a (a1 int not null primary key, a2 int, a3 int, a4 int, a5 int, a6 int); |
| 0 rows inserted/updated/deleted |
| ij> create table b (b1 int not null primary key, b2 int, b3 int, b4 int, b5 int, b6 int); |
| 0 rows inserted/updated/deleted |
| ij> create table c (c1 int not null, c2 int, c3 int not null, c4 int, c5 int, c6 int); |
| 0 rows inserted/updated/deleted |
| ij> create table d (d1 int not null, d2 int, d3 int not null, d4 int, d5 int, d6 int); |
| 0 rows inserted/updated/deleted |
| ij> alter table c add primary key (c1,c3); |
| 0 rows inserted/updated/deleted |
| ij> alter table d add primary key (d1,d3); |
| 0 rows inserted/updated/deleted |
| ij> insert into a values (1,1,3,6,NULL,2),(2,3,2,4,2,2),(3,4,2,NULL,NULL,NULL), |
| (4,NULL,4,2,5,2),(5,2,3,5,7,4),(7,1,4,2,3,4), |
| (8,8,8,8,8,8),(6,7,3,2,3,4); |
| 8 rows inserted/updated/deleted |
| ij> insert into b values (6,7,2,3,NULL,1),(4,5,9,6,3,2),(1,4,2,NULL,NULL,NULL), |
| (5,NULL,2,2,5,2),(3,2,3,3,1,4),(7,3,3,3,3,3),(9,3,3,3,3,3); |
| 7 rows inserted/updated/deleted |
| ij> insert into c values (3,7,7,3,NULL,1),(8,3,9,1,3,2),(1,4,1,NULL,NULL,NULL), |
| (3,NULL,1,2,4,2),(2,2,5,3,2,4),(1,7,2,3,1,1),(3,8,4,2,4,6); |
| 7 rows inserted/updated/deleted |
| ij> insert into d values (1,7,2,3,NULL,3),(2,3,9,1,1,2),(2,2,2,NULL,3,2), |
| (1,NULL,3,2,2,1),(2,2,5,3,2,3),(2,5,6,3,7,2); |
| 6 rows inserted/updated/deleted |
| ij> select a1,b1,c1,c3,d1,d3 |
| from D join (A left outer join (B join C on b2=c2) on a1=b1) |
| on d3=b3 and d1=a2; |
| A1 |B1 |C1 |C3 |D1 |D3 |
| ----------------------------------------------------------------------- |
| 1 |1 |1 |1 |1 |2 |
| 7 |7 |8 |9 |1 |3 |
| ij> select a1,b1,c1,c3,d1,d3 |
| from D join ((B join C on b2=c2) right outer join A on a1=b1) |
| on d3=b3 and d1=a2; |
| A1 |B1 |C1 |C3 |D1 |D3 |
| ----------------------------------------------------------------------- |
| 1 |1 |1 |1 |1 |2 |
| 7 |7 |8 |9 |1 |3 |
| ij> -- JIRA 1089: demonstrate that a table with an identity column generated |
| -- always can be used as the target of an insert-as-select join: |
| create table j1089_source (source_id int); |
| 0 rows inserted/updated/deleted |
| ij> insert into j1089_source values (0); |
| 1 row inserted/updated/deleted |
| ij> create table j1089_dest ( |
| dest_id int not null primary key generated always as identity, |
| source_id_1 int not null, |
| source_id_2 int not null); |
| 0 rows inserted/updated/deleted |
| ij> insert into j1089_dest (source_id_1, source_id_2) |
| select s1.source_id, s2.source_id |
| from j1089_source as s1 |
| join j1089_source as s2 on 1 = 1; |
| 1 row inserted/updated/deleted |
| ij> select * from j1089_dest; |
| DEST_ID |SOURCE_ID_1|SOURCE_ID_2 |
| ----------------------------------- |
| 1 |0 |0 |
| ij> -- DERBY-3538 NullPointerException during execution for query with LEFT |
| -- OUTER JOIN whose inner table selects all constants. |
| create table t3538 (i int, j int); |
| 0 rows inserted/updated/deleted |
| ij> insert into t3538 values (-1, -2), (-2, -4), (-3, -9); |
| 3 rows inserted/updated/deleted |
| ij> select * from |
| t3538 left outer join |
| (select -1 a, 1 b from t3538) x0 --DERBY-PROPERTIES joinStrategy=NESTEDLOOP |
| on x0.a = t3538.i; |
| I |J |A |B |
| ----------------------------------------------- |
| -1 |-2 |-1 |1 |
| -1 |-2 |-1 |1 |
| -1 |-2 |-1 |1 |
| -2 |-4 |NULL |NULL |
| -3 |-9 |NULL |NULL |
| ij> --- regression test for an old optimizer problem; when the bug occurred, |
| -- hash join queries with 'or' would return incorrect results. |
| create table t5929_1 ( i int, j int, k int); |
| 0 rows inserted/updated/deleted |
| ij> create table t5929_2 ( i int, j int, k int); |
| 0 rows inserted/updated/deleted |
| ij> insert into t5929_1 values (1, 1, 1), (2, 1, 2); |
| 2 rows inserted/updated/deleted |
| ij> insert into t5929_1 select i+2, j, k from t5929_1; |
| 2 rows inserted/updated/deleted |
| ij> insert into t5929_1 select i+4, j, k from t5929_1; |
| 4 rows inserted/updated/deleted |
| ij> insert into t5929_2 select * from t5929_1; |
| 8 rows inserted/updated/deleted |
| ij> -- This query should return 32 rows, but it returned 64 before fix |
| select * from --DERBY-PROPERTIES joinOrder=fixed |
| t5929_1, t5929_2 --DERBY-PROPERTIES joinStrategy=nestedLoop |
| where t5929_1.j=t5929_2.j and (t5929_2.j=1 and (t5929_2.k=1 or t5929_2.j=4)); |
| I |J |K |I |J |K |
| ----------------------------------------------------------------------- |
| 1 |1 |1 |1 |1 |1 |
| 1 |1 |1 |3 |1 |1 |
| 1 |1 |1 |5 |1 |1 |
| 1 |1 |1 |7 |1 |1 |
| 2 |1 |2 |1 |1 |1 |
| 2 |1 |2 |3 |1 |1 |
| 2 |1 |2 |5 |1 |1 |
| 2 |1 |2 |7 |1 |1 |
| 3 |1 |1 |1 |1 |1 |
| 3 |1 |1 |3 |1 |1 |
| 3 |1 |1 |5 |1 |1 |
| 3 |1 |1 |7 |1 |1 |
| 4 |1 |2 |1 |1 |1 |
| 4 |1 |2 |3 |1 |1 |
| 4 |1 |2 |5 |1 |1 |
| 4 |1 |2 |7 |1 |1 |
| 5 |1 |1 |1 |1 |1 |
| 5 |1 |1 |3 |1 |1 |
| 5 |1 |1 |5 |1 |1 |
| 5 |1 |1 |7 |1 |1 |
| 6 |1 |2 |1 |1 |1 |
| 6 |1 |2 |3 |1 |1 |
| 6 |1 |2 |5 |1 |1 |
| 6 |1 |2 |7 |1 |1 |
| 7 |1 |1 |1 |1 |1 |
| 7 |1 |1 |3 |1 |1 |
| 7 |1 |1 |5 |1 |1 |
| 7 |1 |1 |7 |1 |1 |
| 8 |1 |2 |1 |1 |1 |
| 8 |1 |2 |3 |1 |1 |
| 8 |1 |2 |5 |1 |1 |
| 8 |1 |2 |7 |1 |1 |
| ij> -- This query should return identical to the above query |
| select * from --DERBY-PROPERTIES joinOrder=fixed |
| t5929_1, t5929_2 --DERBY-PROPERTIES joinStrategy=hash |
| where t5929_1.j=t5929_2.j and (t5929_2.j=1 and t5929_2.k=1); |
| I |J |K |I |J |K |
| ----------------------------------------------------------------------- |
| 1 |1 |1 |1 |1 |1 |
| 1 |1 |1 |3 |1 |1 |
| 1 |1 |1 |5 |1 |1 |
| 1 |1 |1 |7 |1 |1 |
| 2 |1 |2 |1 |1 |1 |
| 2 |1 |2 |3 |1 |1 |
| 2 |1 |2 |5 |1 |1 |
| 2 |1 |2 |7 |1 |1 |
| 3 |1 |1 |1 |1 |1 |
| 3 |1 |1 |3 |1 |1 |
| 3 |1 |1 |5 |1 |1 |
| 3 |1 |1 |7 |1 |1 |
| 4 |1 |2 |1 |1 |1 |
| 4 |1 |2 |3 |1 |1 |
| 4 |1 |2 |5 |1 |1 |
| 4 |1 |2 |7 |1 |1 |
| 5 |1 |1 |1 |1 |1 |
| 5 |1 |1 |3 |1 |1 |
| 5 |1 |1 |5 |1 |1 |
| 5 |1 |1 |7 |1 |1 |
| 6 |1 |2 |1 |1 |1 |
| 6 |1 |2 |3 |1 |1 |
| 6 |1 |2 |5 |1 |1 |
| 6 |1 |2 |7 |1 |1 |
| 7 |1 |1 |1 |1 |1 |
| 7 |1 |1 |3 |1 |1 |
| 7 |1 |1 |5 |1 |1 |
| 7 |1 |1 |7 |1 |1 |
| 8 |1 |2 |1 |1 |1 |
| 8 |1 |2 |3 |1 |1 |
| 8 |1 |2 |5 |1 |1 |
| 8 |1 |2 |7 |1 |1 |
| ij> select * from --DERBY-PROPERTIES joinOrder=fixed |
| t5929_1, t5929_2 --DERBY-PROPERTIES joinStrategy=hash |
| where t5929_1.j=t5929_2.j and (t5929_2.j=1 and (t5929_2.k=1 or t5929_2.j+1=5)); |
| I |J |K |I |J |K |
| ----------------------------------------------------------------------- |
| 1 |1 |1 |1 |1 |1 |
| 1 |1 |1 |3 |1 |1 |
| 1 |1 |1 |5 |1 |1 |
| 1 |1 |1 |7 |1 |1 |
| 2 |1 |2 |1 |1 |1 |
| 2 |1 |2 |3 |1 |1 |
| 2 |1 |2 |5 |1 |1 |
| 2 |1 |2 |7 |1 |1 |
| 3 |1 |1 |1 |1 |1 |
| 3 |1 |1 |3 |1 |1 |
| 3 |1 |1 |5 |1 |1 |
| 3 |1 |1 |7 |1 |1 |
| 4 |1 |2 |1 |1 |1 |
| 4 |1 |2 |3 |1 |1 |
| 4 |1 |2 |5 |1 |1 |
| 4 |1 |2 |7 |1 |1 |
| 5 |1 |1 |1 |1 |1 |
| 5 |1 |1 |3 |1 |1 |
| 5 |1 |1 |5 |1 |1 |
| 5 |1 |1 |7 |1 |1 |
| 6 |1 |2 |1 |1 |1 |
| 6 |1 |2 |3 |1 |1 |
| 6 |1 |2 |5 |1 |1 |
| 6 |1 |2 |7 |1 |1 |
| 7 |1 |1 |1 |1 |1 |
| 7 |1 |1 |3 |1 |1 |
| 7 |1 |1 |5 |1 |1 |
| 7 |1 |1 |7 |1 |1 |
| 8 |1 |2 |1 |1 |1 |
| 8 |1 |2 |3 |1 |1 |
| 8 |1 |2 |5 |1 |1 |
| 8 |1 |2 |7 |1 |1 |
| ij> select * from --DERBY-PROPERTIES joinOrder=fixed |
| t5929_2, t5929_1 --DERBY-PROPERTIES joinStrategy=hash |
| where t5929_1.j=t5929_2.j and (t5929_2.j=1 and (t5929_2.k=1 or t5929_2.j=4)); |
| I |J |K |I |J |K |
| ----------------------------------------------------------------------- |
| 1 |1 |1 |1 |1 |1 |
| 1 |1 |1 |2 |1 |2 |
| 1 |1 |1 |3 |1 |1 |
| 1 |1 |1 |4 |1 |2 |
| 1 |1 |1 |5 |1 |1 |
| 1 |1 |1 |6 |1 |2 |
| 1 |1 |1 |7 |1 |1 |
| 1 |1 |1 |8 |1 |2 |
| 3 |1 |1 |1 |1 |1 |
| 3 |1 |1 |2 |1 |2 |
| 3 |1 |1 |3 |1 |1 |
| 3 |1 |1 |4 |1 |2 |
| 3 |1 |1 |5 |1 |1 |
| 3 |1 |1 |6 |1 |2 |
| 3 |1 |1 |7 |1 |1 |
| 3 |1 |1 |8 |1 |2 |
| 5 |1 |1 |1 |1 |1 |
| 5 |1 |1 |2 |1 |2 |
| 5 |1 |1 |3 |1 |1 |
| 5 |1 |1 |4 |1 |2 |
| 5 |1 |1 |5 |1 |1 |
| 5 |1 |1 |6 |1 |2 |
| 5 |1 |1 |7 |1 |1 |
| 5 |1 |1 |8 |1 |2 |
| 7 |1 |1 |1 |1 |1 |
| 7 |1 |1 |2 |1 |2 |
| 7 |1 |1 |3 |1 |1 |
| 7 |1 |1 |4 |1 |2 |
| 7 |1 |1 |5 |1 |1 |
| 7 |1 |1 |6 |1 |2 |
| 7 |1 |1 |7 |1 |1 |
| 7 |1 |1 |8 |1 |2 |
| ij> ----------------------------------- |
| -- clean up |
| ---------------------------------- |
| 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 d; |
| 0 rows inserted/updated/deleted |
| ij> 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 t4; |
| 0 rows inserted/updated/deleted |
| ij> drop table instab; |
| 0 rows inserted/updated/deleted |
| ij> drop table x; |
| 0 rows inserted/updated/deleted |
| ij> drop table y; |
| 0 rows inserted/updated/deleted |
| ij> drop table j1089_source; |
| 0 rows inserted/updated/deleted |
| ij> drop table j1089_dest; |
| 0 rows inserted/updated/deleted |
| ij> drop table t3538; |
| 0 rows inserted/updated/deleted |
| ij> drop table t5929_1; |
| 0 rows inserted/updated/deleted |
| ij> drop table t5929_2; |
| 0 rows inserted/updated/deleted |
| ij> |
