wasCSharpSQLite – Blame information for rev
?pathlinks?
Rev | Author | Line No. | Line |
---|---|---|---|
1 | office | 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.17 2009/01/09 01:12:28 drh Exp $ |
||
15 | # |
||
16 | |||
17 | set testdir [file dirname $argv0] |
||
18 | source $testdir/tester.tcl |
||
19 | |||
20 | ifcapable !subquery { |
||
21 | finish_test |
||
22 | return |
||
23 | } |
||
24 | |||
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} |
||
50 | |||
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} |
||
61 | |||
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} |
||
80 | |||
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} |
||
87 | |||
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} |
||
99 | |||
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} |
||
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'); |
||
129 | |||
130 | SELECT 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 2002-3 25 2002-4 15} |
||
138 | do_test subquery-1.10.5 { |
||
139 | execsql { |
||
140 | SELECT 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 2002-3 25 2002-4 15} |
||
147 | do_test subquery-1.10.6 { |
||
148 | execsql { |
||
149 | DROP TABLE t5; |
||
150 | } |
||
151 | } {} |
||
152 | |||
153 | |||
154 | |||
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); |
||
218 | |||
219 | CREATE TABLE t4(x TEXT); |
||
220 | INSERT INTO t4 VALUES('10.0'); |
||
221 | } |
||
222 | } {} |
||
223 | do_test subquery-2.5.2 { |
||
224 | # In the expr "x IN (SELECT a FROM t3)" the RHS of the IN operator |
||
225 | # has text affinity and the LHS has integer affinity. The rule is |
||
226 | # that we try to convert both sides to an integer before doing the |
||
227 | # comparision. Hence, the integer value 10 in t3 will compare equal |
||
228 | # to the string value '10.0' in t4 because the t4 value will be |
||
229 | # converted into an integer. |
||
230 | execsql { |
||
231 | SELECT * FROM t4 WHERE x IN (SELECT a FROM t3); |
||
232 | } |
||
233 | } {10.0} |
||
234 | do_test subquery-2.5.3.1 { |
||
235 | # The t4i index cannot be used to resolve the "x IN (...)" constraint |
||
236 | # because the constraint has integer affinity but t4i has text affinity. |
||
237 | execsql { |
||
238 | CREATE INDEX t4i ON t4(x); |
||
239 | SELECT * FROM t4 WHERE x IN (SELECT a FROM t3); |
||
240 | } |
||
241 | } {10.0} |
||
242 | do_test subquery-2.5.3.2 { |
||
243 | # Verify that the t4i index was not used in the previous query |
||
244 | set ::sqlite_query_plan |
||
245 | } {t4 {}} |
||
246 | do_test subquery-2.5.4 { |
||
247 | execsql { |
||
248 | DROP TABLE t3; |
||
249 | DROP TABLE t4; |
||
250 | } |
||
251 | } {} |
||
252 | |||
253 | #------------------------------------------------------------------ |
||
254 | # The following test cases - subquery-3.* - test tickets that |
||
255 | # were raised during development of correlated subqueries. |
||
256 | # |
||
257 | |||
258 | # Ticket 1083 |
||
259 | ifcapable view { |
||
260 | do_test subquery-3.1 { |
||
261 | catchsql { DROP TABLE t1; } |
||
262 | catchsql { DROP TABLE t2; } |
||
263 | execsql { |
||
264 | CREATE TABLE t1(a,b); |
||
265 | INSERT INTO t1 VALUES(1,2); |
||
266 | CREATE VIEW v1 AS SELECT b FROM t1 WHERE a>0; |
||
267 | CREATE TABLE t2(p,q); |
||
268 | INSERT INTO t2 VALUES(2,9); |
||
269 | SELECT * FROM v1 WHERE EXISTS(SELECT * FROM t2 WHERE p=v1.b); |
||
270 | } |
||
271 | } {2} |
||
272 | do_test subquery-3.1.1 { |
||
273 | execsql { |
||
274 | SELECT * FROM v1 WHERE EXISTS(SELECT 1); |
||
275 | } |
||
276 | } {2} |
||
277 | } else { |
||
278 | catchsql { DROP TABLE t1; } |
||
279 | catchsql { DROP TABLE t2; } |
||
280 | execsql { |
||
281 | CREATE TABLE t1(a,b); |
||
282 | INSERT INTO t1 VALUES(1,2); |
||
283 | CREATE TABLE t2(p,q); |
||
284 | INSERT INTO t2 VALUES(2,9); |
||
285 | } |
||
286 | } |
||
287 | |||
288 | # Ticket 1084 |
||
289 | do_test subquery-3.2 { |
||
290 | catchsql { |
||
291 | CREATE TABLE t1(a,b); |
||
292 | INSERT INTO t1 VALUES(1,2); |
||
293 | } |
||
294 | execsql { |
||
295 | SELECT (SELECT t1.a) FROM t1; |
||
296 | } |
||
297 | } {1} |
||
298 | |||
299 | # Test Cases subquery-3.3.* test correlated subqueries where the |
||
300 | # parent query is an aggregate query. Ticket #1105 is an example |
||
301 | # of such a query. |
||
302 | # |
||
303 | do_test subquery-3.3.1 { |
||
304 | execsql { |
||
305 | SELECT a, (SELECT b) FROM t1 GROUP BY a; |
||
306 | } |
||
307 | } {1 2} |
||
308 | do_test subquery-3.3.2 { |
||
309 | catchsql {DROP TABLE t2} |
||
310 | execsql { |
||
311 | CREATE TABLE t2(c, d); |
||
312 | INSERT INTO t2 VALUES(1, 'one'); |
||
313 | INSERT INTO t2 VALUES(2, 'two'); |
||
314 | SELECT a, (SELECT d FROM t2 WHERE a=c) FROM t1 GROUP BY a; |
||
315 | } |
||
316 | } {1 one} |
||
317 | do_test subquery-3.3.3 { |
||
318 | execsql { |
||
319 | INSERT INTO t1 VALUES(2, 4); |
||
320 | SELECT max(a), (SELECT d FROM t2 WHERE a=c) FROM t1; |
||
321 | } |
||
322 | } {2 two} |
||
323 | do_test subquery-3.3.4 { |
||
324 | execsql { |
||
325 | SELECT a, (SELECT (SELECT d FROM t2 WHERE a=c)) FROM t1 GROUP BY a; |
||
326 | } |
||
327 | } {1 one 2 two} |
||
328 | do_test subquery-3.3.5 { |
||
329 | execsql { |
||
330 | SELECT a, (SELECT count(*) FROM t2 WHERE a=c) FROM t1; |
||
331 | } |
||
332 | } {1 1 2 1} |
||
333 | |||
334 | #------------------------------------------------------------------ |
||
335 | # These tests - subquery-4.* - use the TCL statement cache to try |
||
336 | # and expose bugs to do with re-using statements that have been |
||
337 | # passed to sqlite3_reset(). |
||
338 | # |
||
339 | # One problem was that VDBE memory cells were not being initialised |
||
340 | # to NULL on the second and subsequent executions. |
||
341 | # |
||
342 | do_test subquery-4.1.1 { |
||
343 | execsql { |
||
344 | SELECT (SELECT a FROM t1); |
||
345 | } |
||
346 | } {1} |
||
347 | do_test subquery-4.2 { |
||
348 | execsql { |
||
349 | DELETE FROM t1; |
||
350 | SELECT (SELECT a FROM t1); |
||
351 | } |
||
352 | } {{}} |
||
353 | do_test subquery-4.2.1 { |
||
354 | execsql { |
||
355 | CREATE TABLE t3(a PRIMARY KEY); |
||
356 | INSERT INTO t3 VALUES(10); |
||
357 | } |
||
358 | execsql {INSERT INTO t3 VALUES((SELECT max(a) FROM t3)+1)} |
||
359 | } {} |
||
360 | do_test subquery-4.2.2 { |
||
361 | execsql {INSERT INTO t3 VALUES((SELECT max(a) FROM t3)+1)} |
||
362 | } {} |
||
363 | |||
364 | #------------------------------------------------------------------ |
||
365 | # The subquery-5.* tests make sure string literals in double-quotes |
||
366 | # are handled efficiently. Double-quote literals are first checked |
||
367 | # to see if they match any column names. If there is not column name |
||
368 | # match then those literals are used a string constants. When a |
||
369 | # double-quoted string appears, we want to make sure that the search |
||
370 | # for a matching column name did not cause an otherwise static subquery |
||
371 | # to become a dynamic (correlated) subquery. |
||
372 | # |
||
373 | do_test subquery-5.1 { |
||
374 | proc callcntproc {n} { |
||
375 | incr ::callcnt |
||
376 | return $n |
||
377 | } |
||
378 | set callcnt 0 |
||
379 | db function callcnt callcntproc |
||
380 | execsql { |
||
381 | CREATE TABLE t4(x,y); |
||
382 | INSERT INTO t4 VALUES('one',1); |
||
383 | INSERT INTO t4 VALUES('two',2); |
||
384 | INSERT INTO t4 VALUES('three',3); |
||
385 | INSERT INTO t4 VALUES('four',4); |
||
386 | CREATE TABLE t5(a,b); |
||
387 | INSERT INTO t5 VALUES(1,11); |
||
388 | INSERT INTO t5 VALUES(2,22); |
||
389 | INSERT INTO t5 VALUES(3,33); |
||
390 | INSERT INTO t5 VALUES(4,44); |
||
391 | SELECT b FROM t5 WHERE a IN |
||
392 | (SELECT callcnt(y)+0 FROM t4 WHERE x="two") |
||
393 | } |
||
394 | } {22} |
||
395 | do_test subquery-5.2 { |
||
396 | # This is the key test. The subquery should have only run once. If |
||
397 | # The double-quoted identifier "two" were causing the subquery to be |
||
398 | # processed as a correlated subquery, then it would have run 4 times. |
||
399 | set callcnt |
||
400 | } {1} |
||
401 | |||
402 | |||
403 | # Ticket #1380. Make sure correlated subqueries on an IN clause work |
||
404 | # correctly when the left-hand side of the IN operator is constant. |
||
405 | # |
||
406 | do_test subquery-6.1 { |
||
407 | set callcnt 0 |
||
408 | execsql { |
||
409 | SELECT x FROM t4 WHERE 1 IN (SELECT callcnt(count(*)) FROM t5 WHERE a=y) |
||
410 | } |
||
411 | } {one two three four} |
||
412 | do_test subquery-6.2 { |
||
413 | set callcnt |
||
414 | } {4} |
||
415 | do_test subquery-6.3 { |
||
416 | set callcnt 0 |
||
417 | execsql { |
||
418 | SELECT x FROM t4 WHERE 1 IN (SELECT callcnt(count(*)) FROM t5 WHERE a=1) |
||
419 | } |
||
420 | } {one two three four} |
||
421 | do_test subquery-6.4 { |
||
422 | set callcnt |
||
423 | } {1} |
||
424 | |||
425 | if 0 { ############# disable until we get #2652 fixed |
||
426 | # Ticket #2652. Allow aggregate functions of outer queries inside |
||
427 | # a non-aggregate subquery. |
||
428 | # |
||
429 | do_test subquery-7.1 { |
||
430 | execsql { |
||
431 | CREATE TABLE t7(c7); |
||
432 | INSERT INTO t7 VALUES(1); |
||
433 | INSERT INTO t7 VALUES(2); |
||
434 | INSERT INTO t7 VALUES(3); |
||
435 | CREATE TABLE t8(c8); |
||
436 | INSERT INTO t8 VALUES(100); |
||
437 | INSERT INTO t8 VALUES(200); |
||
438 | INSERT INTO t8 VALUES(300); |
||
439 | CREATE TABLE t9(c9); |
||
440 | INSERT INTO t9 VALUES(10000); |
||
441 | INSERT INTO t9 VALUES(20000); |
||
442 | INSERT INTO t9 VALUES(30000); |
||
443 | |||
444 | SELECT (SELECT c7+c8 FROM t7) FROM t8; |
||
445 | } |
||
446 | } {101 201 301} |
||
447 | do_test subquery-7.2 { |
||
448 | execsql { |
||
449 | SELECT (SELECT max(c7)+c8 FROM t7) FROM t8; |
||
450 | } |
||
451 | } {103 203 303} |
||
452 | do_test subquery-7.3 { |
||
453 | execsql { |
||
454 | SELECT (SELECT c7+max(c8) FROM t8) FROM t7 |
||
455 | } |
||
456 | } {301} |
||
457 | do_test subquery-7.4 { |
||
458 | execsql { |
||
459 | SELECT (SELECT max(c7)+max(c8) FROM t8) FROM t7 |
||
460 | } |
||
461 | } {303} |
||
462 | do_test subquery-7.5 { |
||
463 | execsql { |
||
464 | SELECT (SELECT c8 FROM t8 WHERE rowid=max(c7)) FROM t7 |
||
465 | } |
||
466 | } {300} |
||
467 | do_test subquery-7.6 { |
||
468 | execsql { |
||
469 | SELECT (SELECT (SELECT max(c7+c8+c9) FROM t9) FROM t8) FROM t7 |
||
470 | } |
||
471 | } {30101 30102 30103} |
||
472 | do_test subquery-7.7 { |
||
473 | execsql { |
||
474 | SELECT (SELECT (SELECT c7+max(c8+c9) FROM t9) FROM t8) FROM t7 |
||
475 | } |
||
476 | } {30101 30102 30103} |
||
477 | do_test subquery-7.8 { |
||
478 | execsql { |
||
479 | SELECT (SELECT (SELECT max(c7)+c8+c9 FROM t9) FROM t8) FROM t7 |
||
480 | } |
||
481 | } {10103} |
||
482 | do_test subquery-7.9 { |
||
483 | execsql { |
||
484 | SELECT (SELECT (SELECT c7+max(c8)+c9 FROM t9) FROM t8) FROM t7 |
||
485 | } |
||
486 | } {10301 10302 10303} |
||
487 | do_test subquery-7.10 { |
||
488 | execsql { |
||
489 | SELECT (SELECT (SELECT c7+c8+max(c9) FROM t9) FROM t8) FROM t7 |
||
490 | } |
||
491 | } {30101 30102 30103} |
||
492 | do_test subquery-7.11 { |
||
493 | execsql { |
||
494 | SELECT (SELECT (SELECT max(c7)+max(c8)+max(c9) FROM t9) FROM t8) FROM t7 |
||
495 | } |
||
496 | } {30303} |
||
497 | } ;############# Disabled |
||
498 | |||
499 | finish_test |