xref: /sqlite-3.40.0/test/selectD.test (revision 3e3f1a5b) 
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
18
19for {set i 1} {$i<=2} {incr i} {
20  db close
21  forcedelete test$i.db
22  sqlite3 db test$i.db
23  if {$i==2} {
24    optimization_control db query-flattener off
25  }
26  do_test selectD-$i.0 {
27    db eval {
28      CREATE TABLE t1(a,b); INSERT INTO t1 VALUES(111,'x1');
29      CREATE TABLE t2(a,b); INSERT INTO t2 VALUES(222,'x2');
30      CREATE TEMP TABLE t3(a,b); INSERT INTO t3 VALUES(333,'x3');
31      CREATE TABLE t4(a,b); INSERT INTO t4 VALUES(444,'x4');
32    }
33  } {}
34  do_test selectD-$i.1 {
35    db eval {
36      SELECT *
37        FROM (t1), (t2), (t3), (t4)
38       WHERE t4.a=t3.a+111
39         AND t3.a=t2.a+111
40         AND t2.a=t1.a+111;
41    }
42  } {111 x1 222 x2 333 x3 444 x4}
43  do_test selectD-$i.2.1 {
44    db eval {
45      SELECT *
46        FROM t1 JOIN (t2 JOIN (t3 JOIN t4 ON t4.a=t3.a+111)
47                              ON t3.a=t2.a+111)
48                     ON t2.a=t1.a+111;
49    }
50  } {111 x1 222 x2 333 x3 444 x4}
51  do_test selectD-$i.2.2 {
52    db eval {
53      SELECT t3.a
54        FROM t1 JOIN (t2 JOIN (t3 JOIN t4 ON t4.a=t3.a+111)
55                              ON t3.a=t2.a+111)
56                     ON t2.a=t1.a+111;
57    }
58  } {333}
59  do_test selectD-$i.2.3 {
60    db eval {
61      SELECT t3.*
62        FROM t1 JOIN (t2 JOIN (t3 JOIN t4 ON t4.a=t3.a+111)
63                              ON t3.a=t2.a+111)
64                     ON t2.a=t1.a+111;
65    }
66  } {333 x3}
67  do_test selectD-$i.2.3 {
68    db eval {
69      SELECT t3.*, t2.*
70        FROM t1 JOIN (t2 JOIN (t3 JOIN t4 ON t4.a=t3.a+111)
71                              ON t3.a=t2.a+111)
72                     ON t2.a=t1.a+111;
73    }
74  } {333 x3 222 x2}
75  do_test selectD-$i.3 {
76    db eval {
77      UPDATE t2 SET a=111;
78      UPDATE t3 SET a=111;
79      UPDATE t4 SET a=111;
80      SELECT *
81        FROM t1 JOIN (t2 JOIN (t3 JOIN t4 USING(a)) USING (a)) USING (a);
82    }
83  } {111 x1 x2 x3 x4}
84  do_test selectD-$i.4 {
85    db eval {
86      UPDATE t2 SET a=111;
87      UPDATE t3 SET a=111;
88      UPDATE t4 SET a=111;
89      SELECT *
90        FROM t1 LEFT JOIN (t2 LEFT JOIN (t3 LEFT JOIN t4 USING(a))
91                                        USING (a))
92                           USING (a);
93    }
94  } {111 x1 x2 x3 x4}
95  do_test selectD-$i.5 {
96    db eval {
97      UPDATE t3 SET a=222;
98      UPDATE t4 SET a=222;
99      SELECT *
100        FROM (t1 LEFT JOIN t2 USING(a)) JOIN (t3 LEFT JOIN t4 USING(a))
101             ON t1.a=t3.a-111;
102    }
103  } {111 x1 x2 222 x3 x4}
104  do_test selectD-$i.6 {
105    db eval {
106      UPDATE t4 SET a=333;
107      SELECT *
108        FROM (t1 LEFT JOIN t2 USING(a)) JOIN (t3 LEFT JOIN t4 USING(a))
109             ON t1.a=t3.a-111;
110    }
111  } {111 x1 x2 222 x3 {}}
112  do_test selectD-$i.7 {
113    db eval {
114      SELECT t1.*, t2.*, t3.*, t4.b
115        FROM (t1 LEFT JOIN t2 USING(a)) JOIN (t3 LEFT JOIN t4 USING(a))
116             ON t1.a=t3.a-111;
117    }
118  } {111 x1 111 x2 222 x3 {}}
119}
120
121finish_test
122