| blob | 4b626ed83acb70512f4f94fcd0f45c824160e904 |
1 # 2005 January 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 SQLite library. The
12 # focus of this script is testing correlated subqueries
13 #
14 # $Id: subquery.test,v 1.9 2005/05/23 15:06:39 drh Exp $
15 #
17 set testdir [file dirname $argv0]
18 source $testdir/tester.tcl
20 ifcapable !subquery {
21 finish_test
22 return
23 }
25 do_test subquery-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(x,y);
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 COMMIT;
39 }
40 execsql {
41 SELECT a, (SELECT y FROM t2 WHERE x=a) FROM t1 WHERE b<8
42 }
43 } {1 1 3 9 5 25}
44 do_test subquery-1.2 {
45 execsql {
46 UPDATE t1 SET b=b+(SELECT y FROM t2 WHERE x=a);
47 SELECT * FROM t1;
48 }
49 } {1 3 3 13 5 31 7 57}
51 do_test subquery-1.3 {
52 execsql {
53 SELECT b FROM t1 WHERE EXISTS(SELECT * FROM t2 WHERE y=a)
54 }
55 } {3}
56 do_test subquery-1.4 {
57 execsql {
58 SELECT b FROM t1 WHERE NOT EXISTS(SELECT * FROM t2 WHERE y=a)
59 }
60 } {13 31 57}
62 # Simple tests to make sure correlated subqueries in WHERE clauses
63 # are used by the query optimizer correctly.
64 do_test subquery-1.5 {
65 execsql {
66 SELECT a, x FROM t1, t2 WHERE t1.a = (SELECT x);
67 }
68 } {1 1 3 3 5 5 7 7}
69 do_test subquery-1.6 {
70 execsql {
71 CREATE INDEX i1 ON t1(a);
72 SELECT a, x FROM t1, t2 WHERE t1.a = (SELECT x);
73 }
74 } {1 1 3 3 5 5 7 7}
75 do_test subquery-1.7 {
76 execsql {
77 SELECT a, x FROM t2, t1 WHERE t1.a = (SELECT x);
78 }
79 } {1 1 3 3 5 5 7 7}
81 # Try an aggregate in both the subquery and the parent query.
82 do_test subquery-1.8 {
83 execsql {
84 SELECT count(*) FROM t1 WHERE a > (SELECT count(*) FROM t2);
85 }
86 } {2}
88 # Test a correlated subquery disables the "only open the index" optimization.
89 do_test subquery-1.9.1 {
90 execsql {
91 SELECT (y*2)>b FROM t1, t2 WHERE a=x;
92 }
93 } {0 1 1 1}
94 do_test subquery-1.9.2 {
95 execsql {
96 SELECT a FROM t1 WHERE (SELECT (y*2)>b FROM t2 WHERE a=x);
97 }
98 } {3 5 7}
100 # Test that the flattening optimization works with subquery expressions.
101 do_test subquery-1.10.1 {
102 execsql {
103 SELECT (SELECT a), b FROM t1;
104 }
105 } {1 3 3 13 5 31 7 57}
106 do_test subquery-1.10.2 {
107 execsql {
108 SELECT * FROM (SELECT (SELECT a), b FROM t1);
109 }
110 } {1 3 3 13 5 31 7 57}
111 do_test subquery-1.10.3 {
112 execsql {
113 SELECT * FROM (SELECT (SELECT sum(a) FROM t1));
114 }
115 } {16.0}
116 do_test subquery-1.10.4 {
117 execsql {
118 CREATE TABLE t5 (val int, period text PRIMARY KEY);
119 INSERT INTO t5 VALUES(5, '2001-3');
120 INSERT INTO t5 VALUES(10, '2001-4');
121 INSERT INTO t5 VALUES(15, '2002-1');
122 INSERT INTO t5 VALUES(5, '2002-2');
123 INSERT INTO t5 VALUES(10, '2002-3');
124 INSERT INTO t5 VALUES(15, '2002-4');
125 INSERT INTO t5 VALUES(10, '2003-1');
126 INSERT INTO t5 VALUES(5, '2003-2');
127 INSERT INTO t5 VALUES(25, '2003-3');
128 INSERT INTO t5 VALUES(5, '2003-4');
130 SELECT "a.period", vsum
131 FROM (SELECT
132 a.period,
133 (select sum(val) from t5 where period between a.period and '2002-4') vsum
134 FROM t5 a where a.period between '2002-1' and '2002-4')
135 WHERE vsum < 45 ;
136 }
137 } {2002-2 30.0 2002-3 25.0 2002-4 15.0}
138 do_test subquery-1.10.5 {
139 execsql {
140 SELECT "a.period", vsum from
141 (select a.period,
142 (select sum(val) from t5 where period between a.period and '2002-4') vsum
143 FROM t5 a where a.period between '2002-1' and '2002-4')
144 WHERE vsum < 45 ;
145 }
146 } {2002-2 30.0 2002-3 25.0 2002-4 15.0}
147 do_test subquery-1.10.6 {
148 execsql {
149 DROP TABLE t5;
150 }
151 } {}
155 #------------------------------------------------------------------
156 # The following test cases - subquery-2.* - are not logically
157 # organized. They're here largely because they were failing during
158 # one stage of development of sub-queries.
159 #
160 do_test subquery-2.1 {
161 execsql {
162 SELECT (SELECT 10);
163 }
164 } {10}
165 do_test subquery-2.2.1 {
166 execsql {
167 CREATE TABLE t3(a PRIMARY KEY, b);
168 INSERT INTO t3 VALUES(1, 2);
169 INSERT INTO t3 VALUES(3, 1);
170 }
171 } {}
172 do_test subquery-2.2.2 {
173 execsql {
174 SELECT * FROM t3 WHERE a IN (SELECT b FROM t3);
175 }
176 } {1 2}
177 do_test subquery-2.2.3 {
178 execsql {
179 DROP TABLE t3;
180 }
181 } {}
182 do_test subquery-2.3.1 {
183 execsql {
184 CREATE TABLE t3(a TEXT);
185 INSERT INTO t3 VALUES('10');
186 }
187 } {}
188 do_test subquery-2.3.2 {
189 execsql {
190 SELECT a IN (10.0, 20) FROM t3;
191 }
192 } {0}
193 do_test subquery-2.3.3 {
194 execsql {
195 DROP TABLE t3;
196 }
197 } {}
198 do_test subquery-2.4.1 {
199 execsql {
200 CREATE TABLE t3(a TEXT);
201 INSERT INTO t3 VALUES('XX');
202 }
203 } {}
204 do_test subquery-2.4.2 {
205 execsql {
206 SELECT count(*) FROM t3 WHERE a IN (SELECT 'XX')
207 }
208 } {1}
209 do_test subquery-2.4.3 {
210 execsql {
211 DROP TABLE t3;
212 }
213 } {}
214 do_test subquery-2.5.1 {
215 execsql {
216 CREATE TABLE t3(a INTEGER);
217 INSERT INTO t3 VALUES(10);
219 CREATE TABLE t4(x TEXT);
220 INSERT INTO t4 VALUES('10.0');
221 }
222 } {}
223 do_test subquery-2.5.2 {
224 execsql {
225 SELECT * FROM t4 WHERE x IN (SELECT a FROM t3);
226 }
227 } {10.0}
228 do_test subquery-2.5.3 {
229 execsql {
230 CREATE INDEX t4i ON t4(x);
231 SELECT * FROM t4 WHERE x IN (SELECT a FROM t3);
232 }
233 } {10.0}
234 do_test subquery-2.5.4 {
235 execsql {
236 DROP TABLE t3;
237 DROP TABLE t4;
238 }
239 } {}
241 #------------------------------------------------------------------
242 # The following test cases - subquery-3.* - test tickets that
243 # were raised during development of correlated subqueries.
244 #
246 # Ticket 1083
247 ifcapable view {
248 do_test subquery-3.1 {
249 catchsql { DROP TABLE t1; }
250 catchsql { DROP TABLE t2; }
251 execsql {
252 CREATE TABLE t1(a,b);
253 INSERT INTO t1 VALUES(1,2);
254 CREATE VIEW v1 AS SELECT b FROM t1 WHERE a>0;
255 CREATE TABLE t2(p,q);
256 INSERT INTO t2 VALUES(2,9);
257 SELECT * FROM v1 WHERE EXISTS(SELECT * FROM t2 WHERE p=v1.b);
258 }
259 } {2}
260 }
262 # Ticket 1084
263 do_test subquery-3.2 {
264 catchsql {
265 CREATE TABLE t1(a,b);
266 INSERT INTO t1 VALUES(1,2);
267 }
268 execsql {
269 SELECT (SELECT t1.a) FROM t1;
270 }
271 } {1}
273 # Test Cases subquery-3.3.* test correlated subqueries where the
274 # parent query is an aggregate query. Ticket #1105 is an example
275 # of such a query.
276 #
277 do_test subquery-3.3.1 {
278 execsql {
279 SELECT a, (SELECT b) FROM t1 GROUP BY a;
280 }
281 } {1 2}
282 do_test subquery-3.3.2 {
283 catchsql {DROP TABLE t2}
284 execsql {
285 CREATE TABLE t2(c, d);
286 INSERT INTO t2 VALUES(1, 'one');
287 INSERT INTO t2 VALUES(2, 'two');
288 SELECT a, (SELECT d FROM t2 WHERE a=c) FROM t1 GROUP BY a;
289 }
290 } {1 one}
291 do_test subquery-3.3.3 {
292 execsql {
293 INSERT INTO t1 VALUES(2, 4);
294 SELECT max(a), (SELECT d FROM t2 WHERE a=c) FROM t1;
295 }
296 } {2 two}
297 do_test subquery-3.3.4 {
298 execsql {
299 SELECT a, (SELECT (SELECT d FROM t2 WHERE a=c)) FROM t1 GROUP BY a;
300 }
301 } {1 one 2 two}
302 do_test subquery-3.3.5 {
303 execsql {
304 SELECT a, (SELECT count(*) FROM t2 WHERE a=c) FROM t1;
305 }
306 } {1 1 2 1}
308 #------------------------------------------------------------------
309 # These tests - subquery-4.* - use the TCL statement cache to try
310 # and expose bugs to do with re-using statements that have been
311 # passed to sqlite3_reset().
312 #
313 # One problem was that VDBE memory cells were not being initialised
314 # to NULL on the second and subsequent executions.
315 #
316 do_test subquery-4.1.1 {
317 execsql {
318 SELECT (SELECT a FROM t1);
319 }
320 } {1}
321 do_test subquery-4.2 {
322 execsql {
323 DELETE FROM t1;
324 SELECT (SELECT a FROM t1);
325 }
326 } {{}}
327 do_test subquery-4.2.1 {
328 execsql {
329 CREATE TABLE t3(a PRIMARY KEY);
330 INSERT INTO t3 VALUES(10);
331 }
332 execsql {INSERT INTO t3 VALUES((SELECT max(a) FROM t3)+1)}
333 } {}
334 do_test subquery-4.2.2 {
335 execsql {INSERT INTO t3 VALUES((SELECT max(a) FROM t3)+1)}
336 } {}
338 #------------------------------------------------------------------
339 # The subquery-5.* tests make sure string literals in double-quotes
340 # are handled efficiently. Double-quote literals are first checked
341 # to see if they match any column names. If there is not column name
342 # match then those literals are used a string constants. When a
343 # double-quoted string appears, we want to make sure that the search
344 # for a matching column name did not cause an otherwise static subquery
345 # to become a dynamic (correlated) subquery.
346 #
347 do_test subquery-5.1 {
348 proc callcntproc {n} {
349 incr ::callcnt
350 return $n
351 }
352 set callcnt 0
353 db function callcnt callcntproc
354 execsql {
355 CREATE TABLE t4(x,y);
356 INSERT INTO t4 VALUES('one',1);
357 INSERT INTO t4 VALUES('two',2);
358 INSERT INTO t4 VALUES('three',3);
359 INSERT INTO t4 VALUES('four',4);
360 CREATE TABLE t5(a,b);
361 INSERT INTO t5 VALUES(1,11);
362 INSERT INTO t5 VALUES(2,22);
363 INSERT INTO t5 VALUES(3,33);
364 INSERT INTO t5 VALUES(4,44);
365 SELECT b FROM t5 WHERE a IN
366 (SELECT callcnt(y)+0 FROM t4 WHERE x="two")
367 }
368 } {22}
369 do_test subquery-5.2 {
370 # This is the key test. The subquery should have only run once. If
371 # The double-quoted identifier "two" were causing the subquery to be
372 # processed as a correlated subquery, then it would have run 4 times.
373 set callcnt
374 } {1}
380 finish_test