| 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 |
|
| 15 |
set testdir [file dirname $argv0] |
| 16 |
source $testdir/tester.tcl |
| 17 |
|
| 18 |
|
| 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 |
} |
| 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 |
# |
| 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 x2 USING AUTOMATIC/} |
| 173 |
|
| 174 |
finish_test |