wasCSharpSQLite – Blame information for rev 4

Subversion Repositories:
Rev:
Rev Author Line No. Line
1 office 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.29 2009/01/09 01:12:28 drh 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