| blob | ad3b25863210d9512770b5d30e06e8181ffb2e68 |
1 #
2 # 2001 September 15
3 #
4 # The author disclaims copyright to this source code. In place of
5 # a legal notice, here is a blessing:
6 #
7 # May you do good and not evil.
8 # May you find forgiveness for yourself and forgive others.
9 # May you share freely, never taking more than you give.
10 #
11 #***********************************************************************
12 # This file implements regression tests for SQLite library. The
13 # focus of this file is testing SELECT statements that contain
14 # subqueries in their FROM clause.
15 #
16 # $Id: select6.test,v 1.11 2004/01/24 20:18:13 drh Exp $
18 set testdir [file dirname $argv0]
19 source $testdir/tester.tcl
21 do_test select6-1.0 {
22 execsql {
23 BEGIN;
24 CREATE TABLE t1(x, y);
25 INSERT INTO t1 VALUES(1,1);
26 INSERT INTO t1 VALUES(2,2);
27 INSERT INTO t1 VALUES(3,2);
28 INSERT INTO t1 VALUES(4,3);
29 INSERT INTO t1 VALUES(5,3);
30 INSERT INTO t1 VALUES(6,3);
31 INSERT INTO t1 VALUES(7,3);
32 INSERT INTO t1 VALUES(8,4);
33 INSERT INTO t1 VALUES(9,4);
34 INSERT INTO t1 VALUES(10,4);
35 INSERT INTO t1 VALUES(11,4);
36 INSERT INTO t1 VALUES(12,4);
37 INSERT INTO t1 VALUES(13,4);
38 INSERT INTO t1 VALUES(14,4);
39 INSERT INTO t1 VALUES(15,4);
40 INSERT INTO t1 VALUES(16,5);
41 INSERT INTO t1 VALUES(17,5);
42 INSERT INTO t1 VALUES(18,5);
43 INSERT INTO t1 VALUES(19,5);
44 INSERT INTO t1 VALUES(20,5);
45 COMMIT;
46 SELECT DISTINCT y FROM t1 ORDER BY y;
47 }
48 } {1 2 3 4 5}
50 do_test select6-1.1 {
51 execsql2 {SELECT * FROM (SELECT x, y FROM t1 WHERE x<2)}
52 } {x 1 y 1}
53 do_test select6-1.2 {
54 execsql {SELECT count(*) FROM (SELECT y FROM t1)}
55 } {20}
56 do_test select6-1.3 {
57 execsql {SELECT count(*) FROM (SELECT DISTINCT y FROM t1)}
58 } {5}
59 do_test select6-1.4 {
60 execsql {SELECT count(*) FROM (SELECT DISTINCT * FROM (SELECT y FROM t1))}
61 } {5}
62 do_test select6-1.5 {
63 execsql {SELECT count(*) FROM (SELECT * FROM (SELECT DISTINCT y FROM t1))}
64 } {5}
66 do_test select6-1.6 {
67 execsql {
68 SELECT *
69 FROM (SELECT count(*),y FROM t1 GROUP BY y) AS a,
70 (SELECT max(x),y FROM t1 GROUP BY y) as b
71 WHERE a.y=b.y ORDER BY a.y
72 }
73 } {1 1 1 1 2 2 3 2 4 3 7 3 8 4 15 4 5 5 20 5}
74 do_test select6-1.7 {
75 execsql {
76 SELECT a.y, a.[count(*)], [max(x)], [count(*)]
77 FROM (SELECT count(*),y FROM t1 GROUP BY y) AS a,
78 (SELECT max(x),y FROM t1 GROUP BY y) as b
79 WHERE a.y=b.y ORDER BY a.y
80 }
81 } {1 1 1 1 2 2 3 2 3 4 7 4 4 8 15 8 5 5 20 5}
82 do_test select6-1.8 {
83 execsql {
84 SELECT q, p, r
85 FROM (SELECT count(*) as p , y as q FROM t1 GROUP BY y) AS a,
86 (SELECT max(x) as r, y as s FROM t1 GROUP BY y) as b
87 WHERE q=s ORDER BY s
88 }
89 } {1 1 1 2 2 3 3 4 7 4 8 15 5 5 20}
90 do_test select6-1.9 {
91 execsql {
92 SELECT q, p, r, b.[min(x)+y]
93 FROM (SELECT count(*) as p , y as q FROM t1 GROUP BY y) AS a,
94 (SELECT max(x) as r, y as s, min(x)+y FROM t1 GROUP BY y) as b
95 WHERE q=s ORDER BY s
96 }
97 } {1 1 1 2 2 2 3 4 3 4 7 7 4 8 15 12 5 5 20 21}
99 do_test select6-2.0 {
100 execsql {
101 CREATE TABLE t2(a INTEGER PRIMARY KEY, b);
102 INSERT INTO t2 SELECT * FROM t1;
103 SELECT DISTINCT b FROM t2 ORDER BY b;
104 }
105 } {1 2 3 4 5}
106 do_test select6-2.1 {
107 execsql2 {SELECT * FROM (SELECT a, b FROM t2 WHERE a<2)}
108 } {a 1 b 1}
109 do_test select6-2.2 {
110 execsql {SELECT count(*) FROM (SELECT b FROM t2)}
111 } {20}
112 do_test select6-2.3 {
113 execsql {SELECT count(*) FROM (SELECT DISTINCT b FROM t2)}
114 } {5}
115 do_test select6-2.4 {
116 execsql {SELECT count(*) FROM (SELECT DISTINCT * FROM (SELECT b FROM t2))}
117 } {5}
118 do_test select6-2.5 {
119 execsql {SELECT count(*) FROM (SELECT * FROM (SELECT DISTINCT b FROM t2))}
120 } {5}
122 do_test select6-2.6 {
123 execsql {
124 SELECT *
125 FROM (SELECT count(*),b FROM t2 GROUP BY b) AS a,
126 (SELECT max(a),b FROM t2 GROUP BY b) as b
127 WHERE a.b=b.b ORDER BY a.b
128 }
129 } {1 1 1 1 2 2 3 2 4 3 7 3 8 4 15 4 5 5 20 5}
130 do_test select6-2.7 {
131 execsql {
132 SELECT a.b, a.[count(*)], [max(a)], [count(*)]
133 FROM (SELECT count(*),b FROM t2 GROUP BY b) AS a,
134 (SELECT max(a),b FROM t2 GROUP BY b) as b
135 WHERE a.b=b.b ORDER BY a.b
136 }
137 } {1 1 1 1 2 2 3 2 3 4 7 4 4 8 15 8 5 5 20 5}
138 do_test select6-2.8 {
139 execsql {
140 SELECT q, p, r
141 FROM (SELECT count(*) as p , b as q FROM t2 GROUP BY b) AS a,
142 (SELECT max(a) as r, b as s FROM t2 GROUP BY b) as b
143 WHERE q=s ORDER BY s
144 }
145 } {1 1 1 2 2 3 3 4 7 4 8 15 5 5 20}
146 do_test select6-2.9 {
147 execsql {
148 SELECT a.q, a.p, b.r
149 FROM (SELECT count(*) as p , b as q FROM t2 GROUP BY q) AS a,
150 (SELECT max(a) as r, b as s FROM t2 GROUP BY s) as b
151 WHERE a.q=b.s ORDER BY a.q
152 }
153 } {1 1 1 2 2 3 3 4 7 4 8 15 5 5 20}
155 do_test sqlite6-3.1 {
156 execsql2 {
157 SELECT * FROM (SELECT * FROM (SELECT * FROM t1 WHERE x=3));
158 }
159 } {x 3 y 2}
160 do_test sqlite6-3.2 {
161 execsql {
162 SELECT * FROM
163 (SELECT a.q, a.p, b.r
164 FROM (SELECT count(*) as p , b as q FROM t2 GROUP BY q) AS a,
165 (SELECT max(a) as r, b as s FROM t2 GROUP BY s) as b
166 WHERE a.q=b.s ORDER BY a.q)
167 ORDER BY q
168 }
169 } {1 1 1 2 2 3 3 4 7 4 8 15 5 5 20}
170 do_test select6-3.3 {
171 execsql {
172 SELECT a,b,a+b FROM (SELECT avg(x) as 'a', avg(y) as 'b' FROM t1)
173 }
174 } {10.5 3.7 14.2}
175 do_test select6-3.4 {
176 execsql {
177 SELECT a,b,a+b FROM (SELECT avg(x) as 'a', avg(y) as 'b' FROM t1 WHERE y=4)
178 }
179 } {11.5 4 15.5}
180 do_test select6-3.5 {
181 execsql {
182 SELECT x,y,x+y FROM (SELECT avg(a) as 'x', avg(b) as 'y' FROM t2 WHERE a=4)
183 }
184 } {4 3 7}
185 do_test select6-3.6 {
186 execsql {
187 SELECT a,b,a+b FROM (SELECT avg(x) as 'a', avg(y) as 'b' FROM t1)
188 WHERE a>10
189 }
190 } {10.5 3.7 14.2}
191 do_test select6-3.7 {
192 execsql {
193 SELECT a,b,a+b FROM (SELECT avg(x) as 'a', avg(y) as 'b' FROM t1)
194 WHERE a<10
195 }
196 } {}
197 do_test select6-3.8 {
198 execsql {
199 SELECT a,b,a+b FROM (SELECT avg(x) as 'a', avg(y) as 'b' FROM t1 WHERE y=4)
200 WHERE a>10
201 }
202 } {11.5 4 15.5}
203 do_test select6-3.9 {
204 execsql {
205 SELECT a,b,a+b FROM (SELECT avg(x) as 'a', avg(y) as 'b' FROM t1 WHERE y=4)
206 WHERE a<10
207 }
208 } {}
209 do_test select6-3.10 {
210 execsql {
211 SELECT a,b,a+b FROM (SELECT avg(x) as 'a', y as 'b' FROM t1 GROUP BY b)
212 ORDER BY a
213 }
214 } {1 1 2 2.5 2 4.5 5.5 3 8.5 11.5 4 15.5 18 5 23}
215 do_test select6-3.11 {
216 execsql {
217 SELECT a,b,a+b FROM
218 (SELECT avg(x) as 'a', y as 'b' FROM t1 GROUP BY b)
219 WHERE b<4 ORDER BY a
220 }
221 } {1 1 2 2.5 2 4.5 5.5 3 8.5}
222 do_test select6-3.12 {
223 execsql {
224 SELECT a,b,a+b FROM
225 (SELECT avg(x) as 'a', y as 'b' FROM t1 GROUP BY b HAVING a>1)
226 WHERE b<4 ORDER BY a
227 }
228 } {2.5 2 4.5 5.5 3 8.5}
229 do_test select6-3.13 {
230 execsql {
231 SELECT a,b,a+b FROM
232 (SELECT avg(x) as 'a', y as 'b' FROM t1 GROUP BY b HAVING a>1)
233 ORDER BY a
234 }
235 } {2.5 2 4.5 5.5 3 8.5 11.5 4 15.5 18 5 23}
236 do_test select6-3.14 {
237 execsql {
238 SELECT [count(*)],y FROM (SELECT count(*), y FROM t1 GROUP BY y)
239 ORDER BY [count(*)]
240 }
241 } {1 1 2 2 4 3 5 5 8 4}
242 do_test select6-3.15 {
243 execsql {
244 SELECT [count(*)],y FROM (SELECT count(*), y FROM t1 GROUP BY y)
245 ORDER BY y
246 }
247 } {1 1 2 2 4 3 8 4 5 5}
249 do_test select6-4.1 {
250 execsql {
251 SELECT a,b,c FROM
252 (SELECT x AS 'a', y AS 'b', x+y AS 'c' FROM t1 WHERE y=4)
253 WHERE a<10 ORDER BY a;
254 }
255 } {8 4 12 9 4 13}
256 do_test select6-4.2 {
257 execsql {
258 SELECT y FROM (SELECT DISTINCT y FROM t1) WHERE y<5 ORDER BY y
259 }
260 } {1 2 3 4}
261 do_test select6-4.3 {
262 execsql {
263 SELECT DISTINCT y FROM (SELECT y FROM t1) WHERE y<5 ORDER BY y
264 }
265 } {1 2 3 4}
266 do_test select6-4.4 {
267 execsql {
268 SELECT avg(y) FROM (SELECT DISTINCT y FROM t1) WHERE y<5 ORDER BY y
269 }
270 } {2.5}
271 do_test select6-4.5 {
272 execsql {
273 SELECT avg(y) FROM (SELECT DISTINCT y FROM t1 WHERE y<5) ORDER BY y
274 }
275 } {2.5}
277 do_test select6-5.1 {
278 execsql {
279 SELECT a,x,b FROM
280 (SELECT x+3 AS 'a', x FROM t1 WHERE y=3) AS 'p',
281 (SELECT x AS 'b' FROM t1 WHERE y=4) AS 'q'
282 WHERE a=b
283 ORDER BY a
284 }
285 } {8 5 8 9 6 9 10 7 10}
286 do_test select6-5.2 {
287 execsql {
288 SELECT a,x,b FROM
289 (SELECT x+3 AS 'a', x FROM t1 WHERE y=3),
290 (SELECT x AS 'b' FROM t1 WHERE y=4)
291 WHERE a=b
292 ORDER BY a
293 }
294 } {8 5 8 9 6 9 10 7 10}
296 # Tests of compound sub-selects
297 #
298 do_test select5-6.1 {
299 execsql {
300 DELETE FROM t1 WHERE x>4;
301 SELECT * FROM t1
302 }
303 } {1 1 2 2 3 2 4 3}
304 do_test select6-6.2 {
305 execsql {
306 SELECT * FROM (
307 SELECT x AS 'a' FROM t1 UNION ALL SELECT x+10 AS 'a' FROM t1
308 ) ORDER BY a;
309 }
310 } {1 2 3 4 11 12 13 14}
311 do_test select6-6.3 {
312 execsql {
313 SELECT * FROM (
314 SELECT x AS 'a' FROM t1 UNION ALL SELECT x+1 AS 'a' FROM t1
315 ) ORDER BY a;
316 }
317 } {1 2 2 3 3 4 4 5}
318 do_test select6-6.4 {
319 execsql {
320 SELECT * FROM (
321 SELECT x AS 'a' FROM t1 UNION SELECT x+1 AS 'a' FROM t1
322 ) ORDER BY a;
323 }
324 } {1 2 3 4 5}
325 do_test select6-6.5 {
326 execsql {
327 SELECT * FROM (
328 SELECT x AS 'a' FROM t1 INTERSECT SELECT x+1 AS 'a' FROM t1
329 ) ORDER BY a;
330 }
331 } {2 3 4}
332 do_test select6-6.6 {
333 execsql {
334 SELECT * FROM (
335 SELECT x AS 'a' FROM t1 EXCEPT SELECT x*2 AS 'a' FROM t1
336 ) ORDER BY a;
337 }
338 } {1 3}
340 # Subselects with no FROM clause
341 #
342 do_test select6-7.1 {
343 execsql {
344 SELECT * FROM (SELECT 1)
345 }
346 } {1}
347 do_test select6-7.2 {
348 execsql {
349 SELECT c,b,a,* FROM (SELECT 1 AS 'a', 2 AS 'b', 'abc' AS 'c')
350 }
351 } {abc 2 1 1 2 abc}
352 do_test select6-7.3 {
353 execsql {
354 SELECT c,b,a,* FROM (SELECT 1 AS 'a', 2 AS 'b', 'abc' AS 'c' WHERE 0)
355 }
356 } {}
357 do_test select6-7.4 {
358 execsql2 {
359 SELECT c,b,a,* FROM (SELECT 1 AS 'a', 2 AS 'b', 'abc' AS 'c' WHERE 1)
360 }
361 } {c abc b 2 a 1 a 1 b 2 c abc}
363 # The following procedure compiles the SQL given as an argument and returns
364 # TRUE if that SQL uses any transient tables and returns FALSE if no
365 # transient tables are used. This is used to make sure that the
366 # sqliteFlattenSubquery() routine in select.c is doing its job.
367 #
368 proc is_flat {sql} {
369 return [expr 0>[lsearch [execsql "EXPLAIN $sql"] OpenTemp]]
370 }
372 # Check that the flattener works correctly for deeply nested subqueries
373 # involving joins.
374 #
375 do_test select6-8.1 {
376 execsql {
377 BEGIN;
378 CREATE TABLE t3(p,q);
379 INSERT INTO t3 VALUES(1,11);
380 INSERT INTO t3 VALUES(2,22);
381 CREATE TABLE t4(q,r);
382 INSERT INTO t4 VALUES(11,111);
383 INSERT INTO t4 VALUES(22,222);
384 COMMIT;
385 SELECT * FROM t3 NATURAL JOIN t4;
386 }
387 } {1 11 111 2 22 222}
388 do_test select6-8.2 {
389 execsql {
390 SELECT y, p, q, r FROM
391 (SELECT t1.y AS y, t2.b AS b FROM t1, t2 WHERE t1.x=t2.a) AS m,
392 (SELECT t3.p AS p, t3.q AS q, t4.r AS r FROM t3 NATURAL JOIN t4) as n
393 WHERE y=p
394 }
395 } {1 1 11 111 2 2 22 222 2 2 22 222}
396 do_test select6-8.3 {
397 is_flat {
398 SELECT y, p, q, r FROM
399 (SELECT t1.y AS y, t2.b AS b FROM t1, t2 WHERE t1.x=t2.a) AS m,
400 (SELECT t3.p AS p, t3.q AS q, t4.r AS r FROM t3 NATURAL JOIN t4) as n
401 WHERE y=p
402 }
403 } {1}
404 do_test select6-8.4 {
405 execsql {
406 SELECT DISTINCT y, p, q, r FROM
407 (SELECT t1.y AS y, t2.b AS b FROM t1, t2 WHERE t1.x=t2.a) AS m,
408 (SELECT t3.p AS p, t3.q AS q, t4.r AS r FROM t3 NATURAL JOIN t4) as n
409 WHERE y=p
410 }
411 } {1 1 11 111 2 2 22 222}
412 do_test select6-8.5 {
413 execsql {
414 SELECT * FROM
415 (SELECT y, p, q, r FROM
416 (SELECT t1.y AS y, t2.b AS b FROM t1, t2 WHERE t1.x=t2.a) AS m,
417 (SELECT t3.p AS p, t3.q AS q, t4.r AS r FROM t3 NATURAL JOIN t4) as n
418 WHERE y=p) AS e,
419 (SELECT r AS z FROM t4 WHERE q=11) AS f
420 WHERE e.r=f.z
421 }
422 } {1 1 11 111 111}
423 do_test select6-8.6 {
424 is_flat {
425 SELECT * FROM
426 (SELECT y, p, q, r FROM
427 (SELECT t1.y AS y, t2.b AS b FROM t1, t2 WHERE t1.x=t2.a) AS m,
428 (SELECT t3.p AS p, t3.q AS q, t4.r AS r FROM t3 NATURAL JOIN t4) as n
429 WHERE y=p) AS e,
430 (SELECT r AS z FROM t4 WHERE q=11) AS f
431 WHERE e.r=f.z
432 }
433 } {1}
436 finish_test