sql-tests/inputs/cross-join.sql

0001 -- Cross join detection and error checking is done in JoinSuite since explain output is
0002 -- used in the error message and the ids are not stable. Only positive cases are checked here.
0003
0004 create temporary view nt1 as select * from values
0005   ("one", 1),
0006   ("two", 2),
0007   ("three", 3)
0008   as nt1(k, v1);
0009
0010 create temporary view nt2 as select * from values
0011   ("one", 1),
0012   ("two", 22),
0013   ("one", 5)
0014   as nt2(k, v2);
0015
0016 -- Cross joins with and without predicates
0017 SELECT * FROM nt1 cross join nt2;
0018 SELECT * FROM nt1 cross join nt2 where nt1.k = nt2.k;
0019 SELECT * FROM nt1 cross join nt2 on (nt1.k = nt2.k);
0020 SELECT * FROM nt1 cross join nt2 where nt1.v1 = 1 and nt2.v2 = 22;
0021
0022 SELECT a.key, b.key FROM
0023 (SELECT k key FROM nt1 WHERE v1 < 2) a
0024 CROSS JOIN
0025 (SELECT k key FROM nt2 WHERE v2 = 22) b;
0026
0027 -- Join reordering
0028 create temporary view A(a, va) as select * from nt1;
0029 create temporary view B(b, vb) as select * from nt1;
0030 create temporary view C(c, vc) as select * from nt1;
0031 create temporary view D(d, vd) as select * from nt1;
0032
0033 -- Allowed since cross join with C is explicit
0034 select * from ((A join B on (a = b)) cross join C) join D on (a = d);
0035 -- Cross joins with non-equal predicates
0036 SELECT * FROM nt1 CROSS JOIN nt2 ON (nt1.k > nt2.k);