Debian Codegraph @

test/subquery2.test

raw
1 # 2011 September 16
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 SQLite library. The
12 # focus of this script is testing correlated subqueries
13 #
14 #
15
16 set testdir [file dirname $argv0]
17 source $testdir/tester.tcl
18 set ::testprefix subquery2
19
20 ifcapable !subquery {
21 finish_test
22 return
23 }
24
25 do_test subquery2-1.1 {
26 execsql {
27 BEGIN;
28 CREATE TABLE t1(a,b);
29 INSERT INTO t1 VALUES(1,2);
30 INSERT INTO t1 VALUES(3,4);
31 INSERT INTO t1 VALUES(5,6);
32 INSERT INTO t1 VALUES(7,8);
33 CREATE TABLE t2(c,d);
34 INSERT INTO t2 VALUES(1,1);
35 INSERT INTO t2 VALUES(3,9);
36 INSERT INTO t2 VALUES(5,25);
37 INSERT INTO t2 VALUES(7,49);
38 CREATE TABLE t3(e,f);
39 INSERT INTO t3 VALUES(1,1);
40 INSERT INTO t3 VALUES(3,27);
41 INSERT INTO t3 VALUES(5,125);
42 INSERT INTO t3 VALUES(7,343);
43 COMMIT;
44 }
45 execsql {
46 SELECT a FROM t1
47 WHERE b IN (SELECT x+1 FROM (SELECT DISTINCT f/(a*a) AS x FROM t3));
48 }
49 } {1 3 5 7}
50 do_test subquery2-1.2 {
51 execsql {
52 CREATE INDEX t1b ON t1(b);
53 SELECT a FROM t1
54 WHERE b IN (SELECT x+1 FROM (SELECT DISTINCT f/(a*a) AS x FROM t3));
55 }
56 } {1 3 5 7}
57
58 do_test subquery2-1.11 {
59 execsql {
60 SELECT a FROM t1
61 WHERE +b=(SELECT x+1 FROM (SELECT DISTINCT f/(a*a) AS x FROM t3));
62 }
63 } {1}
64 do_test subquery2-1.12 {
65 execsql {
66 SELECT a FROM t1
67 WHERE b=(SELECT x+1 FROM (SELECT DISTINCT f/(a*a) AS x FROM t3));
68 }
69 } {1}
70
71 do_test subquery2-1.21 {
72 execsql {
73 SELECT a FROM t1
74 WHERE +b=(SELECT x+1 FROM
75 (SELECT DISTINCT f/d AS x FROM t2 JOIN t3 ON d*a=f))
76 }
77 } {1 3 5 7}
78 do_test subquery2-1.22 {
79 execsql {
80 SELECT a FROM t1
81 WHERE b=(SELECT x+1 FROM
82 (SELECT DISTINCT f/d AS x FROM t2 JOIN t3 ON d*a=f))
83 }
84 } {1 3 5 7}
85
86 #-------------------------------------------------------------------------
87 # Test that ticket d6b36be38a has been fixed.
88 do_execsql_test 2.1 {
89 CREATE TABLE t4(a, b);
90 CREATE TABLE t5(a, b);
91 INSERT INTO t5 VALUES(3, 5);
92
93 INSERT INTO t4 VALUES(1, 1);
94 INSERT INTO t4 VALUES(2, 3);
95 INSERT INTO t4 VALUES(3, 6);
96 INSERT INTO t4 VALUES(4, 10);
97 INSERT INTO t4 VALUES(5, 15);
98 }
99
100 do_execsql_test 2.2 {
101 SELECT *
102 FROM (SELECT * FROM t4 ORDER BY a LIMIT -1 OFFSET 1)
103 LIMIT (SELECT a FROM t5)
104 } {2 3 3 6 4 10}
105
106 ############################################################################
107 # Ticket http://sqlite.org/src/info/d11a6e908f (2014-09-20)
108 # Query planner fault on three-way nested join with compound inner SELECT
109 #
110 do_execsql_test 3.0 {
111 DROP TABLE IF EXISTS t1;
112 DROP TABLE IF EXISTS t2;
113 CREATE TABLE t1 (id INTEGER PRIMARY KEY, data TEXT);
114 INSERT INTO t1(id,data) VALUES(9,'nine-a');
115 INSERT INTO t1(id,data) VALUES(10,'ten-a');
116 INSERT INTO t1(id,data) VALUES(11,'eleven-a');
117 CREATE TABLE t2 (id INTEGER PRIMARY KEY, data TEXT);
118 INSERT INTO t2(id,data) VALUES(9,'nine-b');
119 INSERT INTO t2(id,data) VALUES(10,'ten-b');
120 INSERT INTO t2(id,data) VALUES(11,'eleven-b');
121
122 SELECT id FROM (
123 SELECT id,data FROM (
124 SELECT * FROM t1 UNION ALL SELECT * FROM t2
125 )
126 WHERE id=10 ORDER BY data
127 );
128 } {10 10}
129 do_execsql_test 3.1 {
130 SELECT data FROM (
131 SELECT 'dummy', data FROM (
132 SELECT data FROM t1 UNION ALL SELECT data FROM t1
133 ) ORDER BY data
134 );
135 } {eleven-a eleven-a nine-a nine-a ten-a ten-a}
136 do_execsql_test 3.2 {
137 DROP TABLE IF EXISTS t3;
138 DROP TABLE IF EXISTS t4;
139 CREATE TABLE t3(id INTEGER, data TEXT);
140 CREATE TABLE t4(id INTEGER, data TEXT);
141 INSERT INTO t3 VALUES(4, 'a'),(2,'c');
142 INSERT INTO t4 VALUES(3, 'b'),(1,'d');
143
144 SELECT data, id FROM (
145 SELECT id, data FROM (
146 SELECT * FROM t3 UNION ALL SELECT * FROM t4
147 ) ORDER BY data
148 );
149 } {a 4 b 3 c 2 d 1}
150
151 #-------------------------------------------------------------------------
152
153 do_execsql_test 4.0 {
154 CREATE TABLE t6(x);
155 }
156
157 foreach {tn sql} {
158 1 {
159 SELECT 'abc' FROM (
160 SELECT x FROM t6 ORDER BY 1
161 UNION ALL
162 SELECT x FROM t6
163 )
164 }
165 2 {
166 SELECT 'abc' FROM (
167 SELECT x FROM t6
168 UNION ALL
169 SELECT x FROM t6 ORDER BY 1
170 UNION ALL
171 SELECT x FROM t6
172 )
173 }
174 3 {
175 SELECT 'abc' FROM (
176 SELECT x FROM t6 ORDER BY 1
177 UNION ALL
178 SELECT x FROM t6 ORDER BY 1
179 UNION ALL
180 SELECT x FROM t6
181 )
182 }
183 4 {
184 SELECT 'abc' FROM (
185 SELECT x FROM t6
186 UNION ALL
187 SELECT x FROM t6 ORDER BY 1
188 UNION ALL
189 SELECT x FROM t6 ORDER BY 1
190 UNION ALL
191 SELECT x FROM t6
192 )
193 }
194 } {
195 do_catchsql_test 4.$tn $sql [list {*}{
196 1 {ORDER BY clause should come after UNION ALL not before}
197 }]
198 }
199
200 #-------------------------------------------------------------------------
201 # Test that ticket [9cdc5c46] is fixed.
202 #
203 reset_db
204 do_execsql_test 5.0 {
205 CREATE TABLE t1(x);
206 INSERT INTO t1 VALUES('ALFKI');
207 INSERT INTO t1 VALUES('ANATR');
208
209 CREATE TABLE t2(y, z);
210 CREATE INDEX t2y ON t2 (y);
211 INSERT INTO t2 VALUES('ANATR', '1997-08-08 00:00:00');
212 INSERT INTO t2 VALUES('ALFKI', '1997-08-25 00:00:00');
213 }
214 do_execsql_test 5.1 {
215 SELECT ( SELECT y FROM t2 WHERE x = y ORDER BY y, z) FROM t1;
216 } {ALFKI ANATR}
217
218 #-------------------------------------------------------------------------
219 reset_db
220 do_execsql_test 6.0 {
221 CREATE TABLE t1(x);
222 INSERT INTO t1 VALUES(1234);
223 }
224
225 do_execsql_test 6.1 {
226 SELECT DISTINCT 'string' FROM t1 LIMIT 1 OFFSET 5;
227 }
228
229 do_execsql_test 6.2 {
230 SELECT (
231 SELECT 'string' FROM t1 LIMIT 1 OFFSET 5
232 );
233 } {{}}
234
235 do_execsql_test 6.3 {
236 SELECT (
237 SELECT DISTINCT 'string' FROM t1 LIMIT 1 OFFSET 5
238 );
239 } {{}}
240
241 do_execsql_test 6.4 {
242 SELECT (
243 SELECT DISTINCT 'string' FROM t1 ORDER BY 1 LIMIT 1 OFFSET 5
244 );
245 } {{}}
246
247 do_execsql_test 6.5 {
248 SELECT (
249 SELECT 'string' FROM t1 ORDER BY 1 LIMIT 1 OFFSET 5
250 );
251 } {{}}
252
253 do_execsql_test 6.6 {
254 SELECT (SELECT DISTINCT x, x FROM t1 LIMIT 1 OFFSET 5)==(1234, 1234)
255 } {{}}
256
257 #-------------------------------------------------------------------------
258 reset_db
259 do_execsql_test 7.0 {
260 CREATE TABLE t1(x);
261 CREATE INDEX i1 ON t1(x);
262 INSERT INTO t1 VALUES(1234);
263 }
264
265 do_execsql_test 7.1 {
266 SELECT (
267 SELECT DISTINCT 'string' FROM t1 ORDER BY x LIMIT 1 OFFSET 5
268 );
269 } {{}}
270
271 do_execsql_test 7.2 {
272 DROP INDEX i1;
273 CREATE UNIQUE INDEX i1 ON t1(x);
274 }
275
276 do_execsql_test 7.3 {
277 SELECT (
278 SELECT DISTINCT x FROM t1 ORDER BY 1 LIMIT 1 OFFSET 5
279 );
280 } {{}}
281
282 do_execsql_test 7.4 {
283 SELECT (SELECT DISTINCT x FROM t1 ORDER BY +x LIMIT 1 OFFSET 0);
284 } {1234}
285
286 finish_test