AryaWu/sqlite

1# 2022 May 172#3# The author disclaims copyright to this source code.  In place of4# a legal notice, here is a blessing:5#6#    May you do good and not evil.7#    May you find forgiveness for yourself and forgive others.8#    May you share freely, never taking more than you give.9#10#***********************************************************************11# This file implements regression tests for SQLite library.12#13 14set testdir [file dirname $argv0]15source $testdir/tester.tcl16set testprefix joinI17 18do_execsql_test 1.0 {19  CREATE TABLE t1(a INT);20  CREATE TABLE t2(b INT);21  CREATE TABLE t3(c INT);22}23 24foreach {tn sql} {25  1 "SELECT * FROM t1 RIGHT JOIN t2 ON t2.b=t3.c CROSS JOIN t3"26  2 "SELECT * FROM t1 RIGHT JOIN t2 ON t2.b=(SELECT t3.c) CROSS JOIN t3"27  3 "SELECT * FROM t1 RIGHT JOIN t2 ON CASE WHEN t2.b THEN t3.c ELSE 1 END CROSS JOIN t3"28} {29  do_catchsql_test 1.1.$tn $sql {1 {ON clause references tables to its right}}30}31 32 33#-------------------------------------------------------------------------34reset_db35 36do_execsql_test 2.0 {37  CREATE TABLE t0(c0 INT, c1 INT);38  CREATE TABLE t1 (c0 INT);39 40  CREATE VIEW v1(c0) AS SELECT t0.c0 FROM t0 NATURAL RIGHT JOIN t1;41  CREATE VIEW v2(c0) AS SELECT 0 FROM v1;42 43  INSERT INTO t0(c0, c1) VALUES (-1, 0);44  INSERT INTO t1(c0) VALUES (NULL);45 46  SELECT * FROM v1 INNER JOIN (v2 CROSS JOIN t0) ON (t0.c0 < t0.c1);47} {{} 0 -1 0}48 49#-------------------------------------------------------------------------50reset_db51do_execsql_test 3.0 {52  CREATE TABLE t0(c0, c1);53  CREATE TABLE t1(v);54  CREATE TABLE t2(w);55  CREATE TABLE t3(x);56  CREATE TABLE t4(y);57  CREATE TABLE t5(z);58}59 60do_execsql_test 3.1 {61  SELECT 1234 FROM t4 62  RIGHT JOIN t5 63  CROSS JOIN (t2 CROSS JOIN t0) AS a1 ON (a1.c0 < a1.c1);64}65 66do_execsql_test 3.2 {67  SELECT 1234 FROM t4 68  RIGHT JOIN t5 69  CROSS JOIN (t2 CROSS JOIN t1 CROSS JOIN t0) AS a1 ON (a1.c0 < a1.c1);70}71 72do_execsql_test 3.3 {73  SELECT 5678 FROM t0 RIGHT JOIN t1 ON (74    SELECT 1 FROM t2 RIGHT JOIN t3 ON t2.w75  ) CROSS JOIN t4;76}77 78do_catchsql_test 3.4 {79  SELECT 5678 FROM t0 RIGHT JOIN t1 ON (80    SELECT 1 FROM t2 RIGHT JOIN t3 ON t4.y81  ) CROSS JOIN t4;82} {1 {ON clause references tables to its right}}83 84do_execsql_test 3.5 {85  SELECT 5678 FROM t0 RIGHT JOIN t1 ON (86    SELECT 1 FROM t2 RIGHT JOIN t3 ON 0=t1.v87  ) CROSS JOIN t4;88}89 90do_catchsql_test 3.6 {91  SELECT 5678 FROM t0 RIGHT JOIN t1 ON (92    SELECT 1 FROM t2 RIGHT JOIN t3 ON max(0,t5.z) CROSS JOIN t593  ) CROSS JOIN t4;94} {1 {ON clause references tables to its right}}95 96#-------------------------------------------------------------------------97reset_db98do_execsql_test 4.0 {99  CREATE TABLE t1(a);100  CREATE TABLE t2(b);101  CREATE TABLE t3(c, d);102}103 104do_catchsql_test 4.1 {105  SELECT c+d AS cd FROM t1 LEFT JOIN t2 ON (cd=5) CROSS JOIN t3;106} {1 {ON clause references tables to its right}}107 108 109#-------------------------------------------------------------------------110reset_db111do_execsql_test 5.0 {112  CREATE TABLE parent1(parent1key, child1key, Child2key, child3key);113  CREATE TABLE child1 ( child1key NVARCHAR, value NVARCHAR );114  CREATE TABLE child2 ( child2key NVARCHAR, value NVARCHAR );115}116 117do_execsql_test 5.1 {118  SELECT parent1.parent1key, child1.value, child2.value119    FROM parent1120    LEFT OUTER JOIN child1 ON child1.child1key = parent1.child1key121    INNER JOIN child2 ON child2.child2key = parent1.child2key;122}123 124finish_test125 126