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