1: # 2001 September 15
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 file is testing SELECT statements that contain
13: # subqueries in their FROM clause.
14: #
15: # $Id: select6.test,v 1.1.1.1 2012/02/21 17:04:16 misho Exp $
16:
17: set testdir [file dirname $argv0]
18: source $testdir/tester.tcl
19:
20: # Omit this whole file if the library is build without subquery support.
21: ifcapable !subquery {
22: finish_test
23: return
24: }
25:
26: do_test select6-1.0 {
27: execsql {
28: BEGIN;
29: CREATE TABLE t1(x, y);
30: INSERT INTO t1 VALUES(1,1);
31: INSERT INTO t1 VALUES(2,2);
32: INSERT INTO t1 VALUES(3,2);
33: INSERT INTO t1 VALUES(4,3);
34: INSERT INTO t1 VALUES(5,3);
35: INSERT INTO t1 VALUES(6,3);
36: INSERT INTO t1 VALUES(7,3);
37: INSERT INTO t1 VALUES(8,4);
38: INSERT INTO t1 VALUES(9,4);
39: INSERT INTO t1 VALUES(10,4);
40: INSERT INTO t1 VALUES(11,4);
41: INSERT INTO t1 VALUES(12,4);
42: INSERT INTO t1 VALUES(13,4);
43: INSERT INTO t1 VALUES(14,4);
44: INSERT INTO t1 VALUES(15,4);
45: INSERT INTO t1 VALUES(16,5);
46: INSERT INTO t1 VALUES(17,5);
47: INSERT INTO t1 VALUES(18,5);
48: INSERT INTO t1 VALUES(19,5);
49: INSERT INTO t1 VALUES(20,5);
50: COMMIT;
51: SELECT DISTINCT y FROM t1 ORDER BY y;
52: }
53: } {1 2 3 4 5}
54:
55: do_test select6-1.1 {
56: execsql2 {SELECT * FROM (SELECT x, y FROM t1 WHERE x<2)}
57: } {x 1 y 1}
58: do_test select6-1.2 {
59: execsql {SELECT count(*) FROM (SELECT y FROM t1)}
60: } {20}
61: do_test select6-1.3 {
62: execsql {SELECT count(*) FROM (SELECT DISTINCT y FROM t1)}
63: } {5}
64: do_test select6-1.4 {
65: execsql {SELECT count(*) FROM (SELECT DISTINCT * FROM (SELECT y FROM t1))}
66: } {5}
67: do_test select6-1.5 {
68: execsql {SELECT count(*) FROM (SELECT * FROM (SELECT DISTINCT y FROM t1))}
69: } {5}
70:
71: do_test select6-1.6 {
72: execsql {
73: SELECT *
74: FROM (SELECT count(*),y FROM t1 GROUP BY y) AS a,
75: (SELECT max(x),y FROM t1 GROUP BY y) as b
76: WHERE a.y=b.y ORDER BY a.y
77: }
78: } {1 1 1 1 2 2 3 2 4 3 7 3 8 4 15 4 5 5 20 5}
79: do_test select6-1.7 {
80: execsql {
81: SELECT a.y, a.[count(*)], [max(x)], [count(*)]
82: FROM (SELECT count(*),y FROM t1 GROUP BY y) AS a,
83: (SELECT max(x),y FROM t1 GROUP BY y) as b
84: WHERE a.y=b.y ORDER BY a.y
85: }
86: } {1 1 1 1 2 2 3 2 3 4 7 4 4 8 15 8 5 5 20 5}
87: do_test select6-1.8 {
88: execsql {
89: SELECT q, p, r
90: FROM (SELECT count(*) as p , y as q FROM t1 GROUP BY y) AS a,
91: (SELECT max(x) as r, y as s FROM t1 GROUP BY y) as b
92: WHERE q=s ORDER BY s
93: }
94: } {1 1 1 2 2 3 3 4 7 4 8 15 5 5 20}
95: do_test select6-1.9 {
96: execsql {
97: SELECT q, p, r, b.[min(x)+y]
98: FROM (SELECT count(*) as p , y as q FROM t1 GROUP BY y) AS a,
99: (SELECT max(x) as r, y as s, min(x)+y FROM t1 GROUP BY y) as b
100: WHERE q=s ORDER BY s
101: }
102: } {1 1 1 2 2 2 3 4 3 4 7 7 4 8 15 12 5 5 20 21}
103:
104: do_test select6-2.0 {
105: execsql {
106: CREATE TABLE t2(a INTEGER PRIMARY KEY, b);
107: INSERT INTO t2 SELECT * FROM t1;
108: SELECT DISTINCT b FROM t2 ORDER BY b;
109: }
110: } {1 2 3 4 5}
111: do_test select6-2.1 {
112: execsql2 {SELECT * FROM (SELECT a, b FROM t2 WHERE a<2)}
113: } {a 1 b 1}
114: do_test select6-2.2 {
115: execsql {SELECT count(*) FROM (SELECT b FROM t2)}
116: } {20}
117: do_test select6-2.3 {
118: execsql {SELECT count(*) FROM (SELECT DISTINCT b FROM t2)}
119: } {5}
120: do_test select6-2.4 {
121: execsql {SELECT count(*) FROM (SELECT DISTINCT * FROM (SELECT b FROM t2))}
122: } {5}
123: do_test select6-2.5 {
124: execsql {SELECT count(*) FROM (SELECT * FROM (SELECT DISTINCT b FROM t2))}
125: } {5}
126:
127: do_test select6-2.6 {
128: execsql {
129: SELECT *
130: FROM (SELECT count(*),b FROM t2 GROUP BY b) AS a,
131: (SELECT max(a),b FROM t2 GROUP BY b) as b
132: WHERE a.b=b.b ORDER BY a.b
133: }
134: } {1 1 1 1 2 2 3 2 4 3 7 3 8 4 15 4 5 5 20 5}
135: do_test select6-2.7 {
136: execsql {
137: SELECT a.b, a.[count(*)], [max(a)], [count(*)]
138: FROM (SELECT count(*),b FROM t2 GROUP BY b) AS a,
139: (SELECT max(a),b FROM t2 GROUP BY b) as b
140: WHERE a.b=b.b ORDER BY a.b
141: }
142: } {1 1 1 1 2 2 3 2 3 4 7 4 4 8 15 8 5 5 20 5}
143: do_test select6-2.8 {
144: execsql {
145: SELECT q, p, r
146: FROM (SELECT count(*) as p , b as q FROM t2 GROUP BY b) AS a,
147: (SELECT max(a) as r, b as s FROM t2 GROUP BY b) as b
148: WHERE q=s ORDER BY s
149: }
150: } {1 1 1 2 2 3 3 4 7 4 8 15 5 5 20}
151: do_test select6-2.9 {
152: execsql {
153: SELECT a.q, a.p, b.r
154: FROM (SELECT count(*) as p , b as q FROM t2 GROUP BY q) AS a,
155: (SELECT max(a) as r, b as s FROM t2 GROUP BY s) as b
156: WHERE a.q=b.s ORDER BY a.q
157: }
158: } {1 1 1 2 2 3 3 4 7 4 8 15 5 5 20}
159:
160: do_test select6-3.1 {
161: execsql2 {
162: SELECT * FROM (SELECT * FROM (SELECT * FROM t1 WHERE x=3));
163: }
164: } {x 3 y 2}
165: do_test select6-3.2 {
166: execsql {
167: SELECT * FROM
168: (SELECT a.q, a.p, b.r
169: FROM (SELECT count(*) as p , b as q FROM t2 GROUP BY q) AS a,
170: (SELECT max(a) as r, b as s FROM t2 GROUP BY s) as b
171: WHERE a.q=b.s ORDER BY a.q)
172: ORDER BY "a.q"
173: }
174: } {1 1 1 2 2 3 3 4 7 4 8 15 5 5 20}
175: do_test select6-3.3 {
176: execsql {
177: SELECT a,b,a+b FROM (SELECT avg(x) as 'a', avg(y) as 'b' FROM t1)
178: }
179: } {10.5 3.7 14.2}
180: do_test select6-3.4 {
181: execsql {
182: SELECT a,b,a+b FROM (SELECT avg(x) as 'a', avg(y) as 'b' FROM t1 WHERE y=4)
183: }
184: } {11.5 4.0 15.5}
185: do_test select6-3.5 {
186: execsql {
187: SELECT x,y,x+y FROM (SELECT avg(a) as 'x', avg(b) as 'y' FROM t2 WHERE a=4)
188: }
189: } {4.0 3.0 7.0}
190: do_test select6-3.6 {
191: execsql {
192: SELECT a,b,a+b FROM (SELECT avg(x) as 'a', avg(y) as 'b' FROM t1)
193: WHERE a>10
194: }
195: } {10.5 3.7 14.2}
196: do_test select6-3.7 {
197: execsql {
198: SELECT a,b,a+b FROM (SELECT avg(x) as 'a', avg(y) as 'b' FROM t1)
199: WHERE a<10
200: }
201: } {}
202: do_test select6-3.8 {
203: execsql {
204: SELECT a,b,a+b FROM (SELECT avg(x) as 'a', avg(y) as 'b' FROM t1 WHERE y=4)
205: WHERE a>10
206: }
207: } {11.5 4.0 15.5}
208: do_test select6-3.9 {
209: execsql {
210: SELECT a,b,a+b FROM (SELECT avg(x) as 'a', avg(y) as 'b' FROM t1 WHERE y=4)
211: WHERE a<10
212: }
213: } {}
214: do_test select6-3.10 {
215: execsql {
216: SELECT a,b,a+b FROM (SELECT avg(x) as 'a', y as 'b' FROM t1 GROUP BY b)
217: ORDER BY a
218: }
219: } {1.0 1 2.0 2.5 2 4.5 5.5 3 8.5 11.5 4 15.5 18.0 5 23.0}
220: do_test select6-3.11 {
221: execsql {
222: SELECT a,b,a+b FROM
223: (SELECT avg(x) as 'a', y as 'b' FROM t1 GROUP BY b)
224: WHERE b<4 ORDER BY a
225: }
226: } {1.0 1 2.0 2.5 2 4.5 5.5 3 8.5}
227: do_test select6-3.12 {
228: execsql {
229: SELECT a,b,a+b FROM
230: (SELECT avg(x) as 'a', y as 'b' FROM t1 GROUP BY b HAVING a>1)
231: WHERE b<4 ORDER BY a
232: }
233: } {2.5 2 4.5 5.5 3 8.5}
234: do_test select6-3.13 {
235: execsql {
236: SELECT a,b,a+b FROM
237: (SELECT avg(x) as 'a', y as 'b' FROM t1 GROUP BY b HAVING a>1)
238: ORDER BY a
239: }
240: } {2.5 2 4.5 5.5 3 8.5 11.5 4 15.5 18.0 5 23.0}
241: do_test select6-3.14 {
242: execsql {
243: SELECT [count(*)],y FROM (SELECT count(*), y FROM t1 GROUP BY y)
244: ORDER BY [count(*)]
245: }
246: } {1 1 2 2 4 3 5 5 8 4}
247: do_test select6-3.15 {
248: execsql {
249: SELECT [count(*)],y FROM (SELECT count(*), y FROM t1 GROUP BY y)
250: ORDER BY y
251: }
252: } {1 1 2 2 4 3 8 4 5 5}
253:
254: do_test select6-4.1 {
255: execsql {
256: SELECT a,b,c FROM
257: (SELECT x AS 'a', y AS 'b', x+y AS 'c' FROM t1 WHERE y=4)
258: WHERE a<10 ORDER BY a;
259: }
260: } {8 4 12 9 4 13}
261: do_test select6-4.2 {
262: execsql {
263: SELECT y FROM (SELECT DISTINCT y FROM t1) WHERE y<5 ORDER BY y
264: }
265: } {1 2 3 4}
266: do_test select6-4.3 {
267: execsql {
268: SELECT DISTINCT y FROM (SELECT y FROM t1) WHERE y<5 ORDER BY y
269: }
270: } {1 2 3 4}
271: do_test select6-4.4 {
272: execsql {
273: SELECT avg(y) FROM (SELECT DISTINCT y FROM t1) WHERE y<5 ORDER BY y
274: }
275: } {2.5}
276: do_test select6-4.5 {
277: execsql {
278: SELECT avg(y) FROM (SELECT DISTINCT y FROM t1 WHERE y<5) ORDER BY y
279: }
280: } {2.5}
281:
282: do_test select6-5.1 {
283: execsql {
284: SELECT a,x,b FROM
285: (SELECT x+3 AS 'a', x FROM t1 WHERE y=3) AS 'p',
286: (SELECT x AS 'b' FROM t1 WHERE y=4) AS 'q'
287: WHERE a=b
288: ORDER BY a
289: }
290: } {8 5 8 9 6 9 10 7 10}
291: do_test select6-5.2 {
292: execsql {
293: SELECT a,x,b FROM
294: (SELECT x+3 AS 'a', x FROM t1 WHERE y=3),
295: (SELECT x AS 'b' FROM t1 WHERE y=4)
296: WHERE a=b
297: ORDER BY a
298: }
299: } {8 5 8 9 6 9 10 7 10}
300:
301: # Tests of compound sub-selects
302: #
303: do_test select6-6.1 {
304: execsql {
305: DELETE FROM t1 WHERE x>4;
306: SELECT * FROM t1
307: }
308: } {1 1 2 2 3 2 4 3}
309: ifcapable compound {
310: do_test select6-6.2 {
311: execsql {
312: SELECT * FROM (
313: SELECT x AS 'a' FROM t1 UNION ALL SELECT x+10 AS 'a' FROM t1
314: ) ORDER BY a;
315: }
316: } {1 2 3 4 11 12 13 14}
317: do_test select6-6.3 {
318: execsql {
319: SELECT * FROM (
320: SELECT x AS 'a' FROM t1 UNION ALL SELECT x+1 AS 'a' FROM t1
321: ) ORDER BY a;
322: }
323: } {1 2 2 3 3 4 4 5}
324: do_test select6-6.4 {
325: execsql {
326: SELECT * FROM (
327: SELECT x AS 'a' FROM t1 UNION SELECT x+1 AS 'a' FROM t1
328: ) ORDER BY a;
329: }
330: } {1 2 3 4 5}
331: do_test select6-6.5 {
332: execsql {
333: SELECT * FROM (
334: SELECT x AS 'a' FROM t1 INTERSECT SELECT x+1 AS 'a' FROM t1
335: ) ORDER BY a;
336: }
337: } {2 3 4}
338: do_test select6-6.6 {
339: execsql {
340: SELECT * FROM (
341: SELECT x AS 'a' FROM t1 EXCEPT SELECT x*2 AS 'a' FROM t1
342: ) ORDER BY a;
343: }
344: } {1 3}
345: } ;# ifcapable compound
346:
347: # Subselects with no FROM clause
348: #
349: do_test select6-7.1 {
350: execsql {
351: SELECT * FROM (SELECT 1)
352: }
353: } {1}
354: do_test select6-7.2 {
355: execsql {
356: SELECT c,b,a,* FROM (SELECT 1 AS 'a', 2 AS 'b', 'abc' AS 'c')
357: }
358: } {abc 2 1 1 2 abc}
359: do_test select6-7.3 {
360: execsql {
361: SELECT c,b,a,* FROM (SELECT 1 AS 'a', 2 AS 'b', 'abc' AS 'c' WHERE 0)
362: }
363: } {}
364: do_test select6-7.4 {
365: execsql2 {
366: SELECT c,b,a,* FROM (SELECT 1 AS 'a', 2 AS 'b', 'abc' AS 'c' WHERE 1)
367: }
368: } {c abc b 2 a 1 a 1 b 2 c abc}
369:
370: # The remaining tests in this file depend on the EXPLAIN keyword.
371: # Skip these tests if EXPLAIN is disabled in the current build.
372: #
373: ifcapable {!explain} {
374: finish_test
375: return
376: }
377:
378: # The following procedure compiles the SQL given as an argument and returns
379: # TRUE if that SQL uses any transient tables and returns FALSE if no
380: # transient tables are used. This is used to make sure that the
381: # sqliteFlattenSubquery() routine in select.c is doing its job.
382: #
383: proc is_flat {sql} {
384: return [expr 0>[lsearch [execsql "EXPLAIN $sql"] OpenEphemeral]]
385: }
386:
387: # Check that the flattener works correctly for deeply nested subqueries
388: # involving joins.
389: #
390: do_test select6-8.1 {
391: execsql {
392: BEGIN;
393: CREATE TABLE t3(p,q);
394: INSERT INTO t3 VALUES(1,11);
395: INSERT INTO t3 VALUES(2,22);
396: CREATE TABLE t4(q,r);
397: INSERT INTO t4 VALUES(11,111);
398: INSERT INTO t4 VALUES(22,222);
399: COMMIT;
400: SELECT * FROM t3 NATURAL JOIN t4;
401: }
402: } {1 11 111 2 22 222}
403: do_test select6-8.2 {
404: execsql {
405: SELECT y, p, q, r FROM
406: (SELECT t1.y AS y, t2.b AS b FROM t1, t2 WHERE t1.x=t2.a) AS m,
407: (SELECT t3.p AS p, t3.q AS q, t4.r AS r FROM t3 NATURAL JOIN t4) as n
408: WHERE y=p
409: }
410: } {1 1 11 111 2 2 22 222 2 2 22 222}
411: # If view support is omitted from the build, then so is the query
412: # "flattener". So omit this test and test select6-8.6 in that case.
413: ifcapable view {
414: do_test select6-8.3 {
415: is_flat {
416: SELECT y, p, q, r FROM
417: (SELECT t1.y AS y, t2.b AS b FROM t1, t2 WHERE t1.x=t2.a) AS m,
418: (SELECT t3.p AS p, t3.q AS q, t4.r AS r FROM t3 NATURAL JOIN t4) as n
419: WHERE y=p
420: }
421: } {1}
422: } ;# ifcapable view
423: do_test select6-8.4 {
424: execsql {
425: SELECT DISTINCT y, p, q, r FROM
426: (SELECT t1.y AS y, t2.b AS b FROM t1, t2 WHERE t1.x=t2.a) AS m,
427: (SELECT t3.p AS p, t3.q AS q, t4.r AS r FROM t3 NATURAL JOIN t4) as n
428: WHERE y=p
429: }
430: } {1 1 11 111 2 2 22 222}
431: do_test select6-8.5 {
432: execsql {
433: SELECT * FROM
434: (SELECT y, p, q, r FROM
435: (SELECT t1.y AS y, t2.b AS b FROM t1, t2 WHERE t1.x=t2.a) AS m,
436: (SELECT t3.p AS p, t3.q AS q, t4.r AS r FROM t3 NATURAL JOIN t4) as n
437: WHERE y=p) AS e,
438: (SELECT r AS z FROM t4 WHERE q=11) AS f
439: WHERE e.r=f.z
440: }
441: } {1 1 11 111 111}
442: ifcapable view {
443: do_test select6-8.6 {
444: is_flat {
445: SELECT * FROM
446: (SELECT y, p, q, r FROM
447: (SELECT t1.y AS y, t2.b AS b FROM t1, t2 WHERE t1.x=t2.a) AS m,
448: (SELECT t3.p AS p, t3.q AS q, t4.r AS r FROM t3 NATURAL JOIN t4) as n
449: WHERE y=p) AS e,
450: (SELECT r AS z FROM t4 WHERE q=11) AS f
451: WHERE e.r=f.z
452: }
453: } {1}
454: } ;# ifcapable view
455:
456: # Ticket #1634
457: #
458: do_test select6-9.1 {
459: execsql {
460: SELECT a.x, b.x FROM t1 AS a, (SELECT x FROM t1 LIMIT 2) AS b
461: ORDER BY 1, 2
462: }
463: } {1 1 1 2 2 1 2 2 3 1 3 2 4 1 4 2}
464: do_test select6-9.2 {
465: execsql {
466: SELECT x FROM (SELECT x FROM t1 LIMIT 2);
467: }
468: } {1 2}
469: do_test select6-9.3 {
470: execsql {
471: SELECT x FROM (SELECT x FROM t1 LIMIT 2 OFFSET 1);
472: }
473: } {2 3}
474: do_test select6-9.4 {
475: execsql {
476: SELECT x FROM (SELECT x FROM t1) LIMIT 2;
477: }
478: } {1 2}
479: do_test select6-9.5 {
480: execsql {
481: SELECT x FROM (SELECT x FROM t1) LIMIT 2 OFFSET 1;
482: }
483: } {2 3}
484: do_test select6-9.6 {
485: execsql {
486: SELECT x FROM (SELECT x FROM t1 LIMIT 2) LIMIT 3;
487: }
488: } {1 2}
489: do_test select6-9.7 {
490: execsql {
491: SELECT x FROM (SELECT x FROM t1 LIMIT -1) LIMIT 3;
492: }
493: } {1 2 3}
494: do_test select6-9.8 {
495: execsql {
496: SELECT x FROM (SELECT x FROM t1 LIMIT -1);
497: }
498: } {1 2 3 4}
499: do_test select6-9.9 {
500: execsql {
501: SELECT x FROM (SELECT x FROM t1 LIMIT -1 OFFSET 1);
502: }
503: } {2 3 4}
504: do_test select6-9.10 {
505: execsql {
506: SELECT x, y FROM (SELECT x, (SELECT 10+x) y FROM t1 LIMIT -1 OFFSET 1);
507: }
508: } {2 12 3 13 4 14}
509: do_test select6-9.11 {
510: execsql {
511: SELECT x, y FROM (SELECT x, (SELECT 10)+x y FROM t1 LIMIT -1 OFFSET 1);
512: }
513: } {2 12 3 13 4 14}
514:
515:
516:
517: finish_test
FreeBSD-CVSweb <freebsd-cvsweb@FreeBSD.org>
![[BACK]](/icons/cvsweb/back.gif){kind=icon}
Return to select6.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