xref: /sqlite-3.40.0/test/selectD.test (revision 7aa3ebee) 
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      ATTACH ':memory:' AS aux1;
29      CREATE TABLE t1(a,b); INSERT INTO t1 VALUES(111,'x1');
30      CREATE TABLE t2(a,b); INSERT INTO t2 VALUES(222,'x2');
31      CREATE TEMP TABLE t3(a,b); INSERT INTO t3 VALUES(333,'x3');
32      CREATE TABLE main.t4(a,b); INSERT INTO main.t4 VALUES(444,'x4');
33      CREATE TABLE aux1.t4(a,b); INSERT INTO aux1.t4 VALUES(555,'x5');
34    }
35  } {}
36  do_test selectD-$i.1 {
37    db eval {
38      SELECT *
39        FROM (t1), (t2), (t3), (t4)
40       WHERE t4.a=t3.a+111
41         AND t3.a=t2.a+111
42         AND t2.a=t1.a+111;
43    }
44  } {111 x1 222 x2 333 x3 444 x4}
45  do_test selectD-$i.2.1 {
46    db eval {
47      SELECT *
48        FROM t1 JOIN (t2 JOIN (t3 JOIN t4 ON t4.a=t3.a+111)
49                              ON t3.a=t2.a+111)
50                     ON t2.a=t1.a+111;
51    }
52  } {111 x1 222 x2 333 x3 444 x4}
53  do_test selectD-$i.2.2 {
54    db eval {
55      SELECT t3.a
56        FROM t1 JOIN (t2 JOIN (t3 JOIN t4 ON t4.a=t3.a+111)
57                              ON t3.a=t2.a+111)
58                     ON t2.a=t1.a+111;
59    }
60  } {333}
61  do_test selectD-$i.2.3 {
62    db eval {
63      SELECT t3.*
64        FROM t1 JOIN (t2 JOIN (t3 JOIN t4 ON t4.a=t3.a+111)
65                              ON t3.a=t2.a+111)
66                     ON t2.a=t1.a+111;
67    }
68  } {333 x3}
69  do_test selectD-$i.2.3 {
70    db eval {
71      SELECT t3.*, t2.*
72        FROM t1 JOIN (t2 JOIN (t3 JOIN t4 ON t4.a=t3.a+111)
73                              ON t3.a=t2.a+111)
74                     ON t2.a=t1.a+111;
75    }
76  } {333 x3 222 x2}
77  do_test selectD-$i.2.4 {
78    db eval {
79      SELECT *
80        FROM t1 JOIN (t2 JOIN (main.t4 JOIN aux1.t4 ON aux1.t4.a=main.t4.a+111)
81                              ON main.t4.a=t2.a+222)
82                     ON t2.a=t1.a+111;
83    }
84  } {111 x1 222 x2 444 x4 555 x5}
85  do_test selectD-$i.2.5 {
86    db eval {
87      SELECT *
88        FROM t1 JOIN (t2 JOIN (main.t4 AS x JOIN aux1.t4 ON aux1.t4.a=x.a+111)
89                              ON x.a=t2.a+222)
90                     ON t2.a=t1.a+111;
91    }
92  } {111 x1 222 x2 444 x4 555 x5}
93  do_test selectD-$i.2.6 {
94    catchsql {
95      SELECT *
96        FROM t1 JOIN (t2 JOIN (main.t4 JOIN aux.t4 ON aux.t4.a=main.t4.a+111)
97                              ON main.t4.a=t2.a+222)
98                     ON t2.a=t1.a+111;
99    }
100  } {1 {no such table: aux.t4}}
101  do_test selectD-$i.2.7 {
102    db eval {
103      SELECT x.a, y.b
104        FROM t1 JOIN (t2 JOIN (main.t4 x JOIN aux1.t4 y ON y.a=x.a+111)
105                              ON x.a=t2.a+222)
106                     ON t2.a=t1.a+111;
107    }
108  } {444 x5}
109  do_test selectD-$i.3 {
110    db eval {
111      UPDATE t2 SET a=111;
112      UPDATE t3 SET a=111;
113      UPDATE t4 SET a=111;
114      SELECT *
115        FROM t1 JOIN (t2 JOIN (t3 JOIN t4 USING(a)) USING (a)) USING (a);
116    }
117  } {111 x1 x2 x3 x4}
118  do_test selectD-$i.4 {
119    db eval {
120      UPDATE t2 SET a=111;
121      UPDATE t3 SET a=111;
122      UPDATE t4 SET a=111;
123      SELECT *
124        FROM t1 LEFT JOIN (t2 LEFT JOIN (t3 LEFT JOIN t4 USING(a))
125                                        USING (a))
126                           USING (a);
127    }
128  } {111 x1 x2 x3 x4}
129  do_test selectD-$i.5 {
130    db eval {
131      UPDATE t3 SET a=222;
132      UPDATE t4 SET a=222;
133      SELECT *
134        FROM (t1 LEFT JOIN t2 USING(a)) JOIN (t3 LEFT JOIN t4 USING(a))
135             ON t1.a=t3.a-111;
136    }
137  } {111 x1 x2 222 x3 x4}
138  do_test selectD-$i.6 {
139    db eval {
140      UPDATE t4 SET a=333;
141      SELECT *
142        FROM (t1 LEFT JOIN t2 USING(a)) JOIN (t3 LEFT JOIN t4 USING(a))
143             ON t1.a=t3.a-111;
144    }
145  } {111 x1 x2 222 x3 {}}
146  do_test selectD-$i.7 {
147    db eval {
148      SELECT t1.*, t2.*, t3.*, t4.b
149        FROM (t1 LEFT JOIN t2 USING(a)) JOIN (t3 LEFT JOIN t4 USING(a))
150             ON t1.a=t3.a-111;
151    }
152  } {111 x1 111 x2 222 x3 {}}
153}
154
155# The following test was added on 2013-04-24 in order to verify that
156# the datatypes and affinities of sub-sub-queries are set prior to computing
157# the datatypes and affinities of the parent sub-queries because the
158# latter computation depends on the former.
159#
160do_execsql_test selectD-4.1 {
161  CREATE TABLE t41(a INTEGER PRIMARY KEY, b INTEGER);
162  CREATE TABLE t42(d INTEGER PRIMARY KEY, e INTEGER);
163  CREATE TABLE t43(f INTEGER PRIMARY KEY, g INTEGER);
164  EXPLAIN QUERY PLAN
165  SELECT *
166   FROM t41
167   LEFT JOIN (SELECT count(*) AS cnt, x1.d
168                FROM (t42 INNER JOIN t43 ON d=g) AS x1
169               WHERE x1.d>5
170               GROUP BY x1.d) AS x2
171                  ON t41.b=x2.d;
172} {/.*SEARCH SUBQUERY 1 AS x2 USING AUTOMATIC.*/}
173
174finish_test
175