xref: /sqlite-3.40.0/test/selectD.test (revision 0dde4739) 
1# 2012 December 19
2#
3# The author disclaims copyright to this source code.  In place of
4# 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 name resolution in SELECT
12# statements that have parenthesized FROM clauses.
13#
14
15set testdir [file dirname $argv0]
16source $testdir/tester.tcl
17
18do_test selectD-1.1 {
19  db eval {
20    CREATE TABLE t1(a,b); INSERT INTO t1 VALUES(111,'x1');
21    CREATE TABLE t2(a,b); INSERT INTO t2 VALUES(222,'x2');
22    CREATE TABLE t3(a,b); INSERT INTO t3 VALUES(333,'x3');
23    CREATE TABLE t4(a,b); INSERT INTO t4 VALUES(444,'x4');
24
25    SELECT *
26      FROM (t1), (t2), (t3), (t4)
27     WHERE t4.a=t3.a+111
28       AND t3.a=t2.a+111
29       AND t2.a=t1.a+111;
30  }
31} {111 x1 222 x2 333 x3 444 x4}
32do_test selectD-1.2 {
33  db eval {
34    SELECT *
35      FROM t1 JOIN (t2 JOIN (t3 JOIN t4 ON t4.a=t3.a+111)
36                            ON t3.a=t2.a+111)
37                   ON t2.a=t1.a+111;
38  }
39} {111 x1 222 x2 333 x3 444 x4}
40do_test selectD-1.3 {
41  db eval {
42    UPDATE t2 SET a=111;
43    UPDATE t3 SET a=111;
44    UPDATE t4 SET a=111;
45    SELECT *
46      FROM t1 JOIN (t2 JOIN (t3 JOIN t4 USING(a)) USING (a)) USING (a);
47  }
48} {111 x1 x2 x3 x4}
49do_test selectD-1.4 {
50  db eval {
51    UPDATE t2 SET a=111;
52    UPDATE t3 SET a=111;
53    UPDATE t4 SET a=111;
54    SELECT *
55      FROM t1 LEFT JOIN (t2 LEFT JOIN (t3 LEFT JOIN t4 USING(a))
56                                      USING (a))
57                         USING (a);
58  }
59} {111 x1 x2 x3 x4}
60do_test selectD-1.5 {
61  db eval {
62    UPDATE t3 SET a=222;
63    UPDATE t4 SET a=222;
64    SELECT *
65      FROM (t1 LEFT JOIN t2 USING(a)) JOIN (t3 LEFT JOIN t4 USING(a))
66           ON t1.a=t3.a-111;
67  }
68} {111 x1 x2 222 x3 x4}
69do_test selectD-1.6 {
70  db eval {
71    UPDATE t4 SET a=333;
72    SELECT *
73      FROM (t1 LEFT JOIN t2 USING(a)) JOIN (t3 LEFT JOIN t4 USING(a))
74           ON t1.a=t3.a-111;
75  }
76} {111 x1 x2 222 x3 {}}
77
78finish_test
79