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.1.1.1 2012/02/21 17:04:16 misho 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
FreeBSD-CVSweb <freebsd-cvsweb@FreeBSD.org>
![[BACK]](/icons/cvsweb/back.gif){kind=icon}
Return to subquery.test CVS log ![[TXT]](/icons/cvsweb/text.gif){kind=icon}
![[DIR]](/icons/cvsweb/dir.gif){kind=icon}
Up to [ELWIX - Embedded LightWeight unIX -] / embedaddon / sqlite3 / test