| blob | 89f999eb6db97d6411df3f2afdae4e012909cc62 |
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 #
15 set testdir [file dirname $argv0]
16 source $testdir/tester.tcl
19 for {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 }
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 #
160 do_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.*/}
174 finish_test