Debian Codegraph @

test/select6.test

raw
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
16 set testdir [file dirname $argv0]
17 source $testdir/tester.tcl
18
19 # Omit this whole file if the library is build without subquery support.
20 ifcapable !subquery {
21 finish_test
22 return
23 }
24 set ::testprefix select6
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 }
173 } {1 1 1 2 2 3 3 4 7 4 8 15 5 5 20}
174 do_test select6-3.3 {
175 execsql {
176 SELECT a,b,a+b FROM (SELECT avg(x) as 'a', avg(y) as 'b' FROM t1)
177 }
178 } {10.5 3.7 14.2}
179 do_test select6-3.4 {
180 execsql {
181 SELECT a,b,a+b FROM (SELECT avg(x) as 'a', avg(y) as 'b' FROM t1 WHERE y=4)
182 }
183 } {11.5 4.0 15.5}
184 do_test select6-3.5 {
185 execsql {
186 SELECT x,y,x+y FROM (SELECT avg(a) as 'x', avg(b) as 'y' FROM t2 WHERE a=4)
187 }
188 } {4.0 3.0 7.0}
189 do_test select6-3.6 {
190 execsql {
191 SELECT a,b,a+b FROM (SELECT avg(x) as 'a', avg(y) as 'b' FROM t1)
192 WHERE a>10
193 }
194 } {10.5 3.7 14.2}
195 do_test select6-3.7 {
196 execsql {
197 SELECT a,b,a+b FROM (SELECT avg(x) as 'a', avg(y) as 'b' FROM t1)
198 WHERE a<10
199 }
200 } {}
201 do_test select6-3.8 {
202 execsql {
203 SELECT a,b,a+b FROM (SELECT avg(x) as 'a', avg(y) as 'b' FROM t1 WHERE y=4)
204 WHERE a>10
205 }
206 } {11.5 4.0 15.5}
207 do_test select6-3.9 {
208 execsql {
209 SELECT a,b,a+b FROM (SELECT avg(x) as 'a', avg(y) as 'b' FROM t1 WHERE y=4)
210 WHERE a<10
211 }
212 } {}
213 do_test select6-3.10 {
214 execsql {
215 SELECT a,b,a+b FROM (SELECT avg(x) as 'a', y as 'b' FROM t1 GROUP BY b)
216 ORDER BY a
217 }
218 } {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}
219 do_test select6-3.11 {
220 execsql {
221 SELECT a,b,a+b FROM
222 (SELECT avg(x) as 'a', y as 'b' FROM t1 GROUP BY b)
223 WHERE b<4 ORDER BY a
224 }
225 } {1.0 1 2.0 2.5 2 4.5 5.5 3 8.5}
226 do_test select6-3.12 {
227 execsql {
228 SELECT a,b,a+b FROM
229 (SELECT avg(x) as 'a', y as 'b' FROM t1 GROUP BY b HAVING a>1)
230 WHERE b<4 ORDER BY a
231 }
232 } {2.5 2 4.5 5.5 3 8.5}
233 do_test select6-3.13 {
234 execsql {
235 SELECT a,b,a+b FROM
236 (SELECT avg(x) as 'a', y as 'b' FROM t1 GROUP BY b HAVING a>1)
237 ORDER BY a
238 }
239 } {2.5 2 4.5 5.5 3 8.5 11.5 4 15.5 18.0 5 23.0}
240 do_test select6-3.14 {
241 execsql {
242 SELECT [count(*)],y FROM (SELECT count(*), y FROM t1 GROUP BY y)
243 ORDER BY [count(*)]
244 }
245 } {1 1 2 2 4 3 5 5 8 4}
246 do_test select6-3.15 {
247 execsql {
248 SELECT [count(*)],y FROM (SELECT count(*), y FROM t1 GROUP BY y)
249 ORDER BY y
250 }
251 } {1 1 2 2 4 3 8 4 5 5}
252
253 do_test select6-4.1 {
254 execsql {
255 SELECT a,b,c FROM
256 (SELECT x AS 'a', y AS 'b', x+y AS 'c' FROM t1 WHERE y=4)
257 WHERE a<10 ORDER BY a;
258 }
259 } {8 4 12 9 4 13}
260 do_test select6-4.2 {
261 execsql {
262 SELECT y FROM (SELECT DISTINCT y FROM t1) WHERE y<5 ORDER BY y
263 }
264 } {1 2 3 4}
265 do_test select6-4.3 {
266 execsql {
267 SELECT DISTINCT y FROM (SELECT y FROM t1) WHERE y<5 ORDER BY y
268 }
269 } {1 2 3 4}
270 do_test select6-4.4 {
271 execsql {
272 SELECT avg(y) FROM (SELECT DISTINCT y FROM t1) WHERE y<5 ORDER BY y
273 }
274 } {2.5}
275 do_test select6-4.5 {
276 execsql {
277 SELECT avg(y) FROM (SELECT DISTINCT y FROM t1 WHERE y<5) ORDER BY y
278 }
279 } {2.5}
280
281 do_test select6-5.1 {
282 execsql {
283 SELECT a,x,b FROM
284 (SELECT x+3 AS 'a', x FROM t1 WHERE y=3) AS 'p',
285 (SELECT x AS 'b' FROM t1 WHERE y=4) AS 'q'
286 WHERE a=b
287 ORDER BY a
288 }
289 } {8 5 8 9 6 9 10 7 10}
290 do_test select6-5.2 {
291 execsql {
292 SELECT a,x,b FROM
293 (SELECT x+3 AS 'a', x FROM t1 WHERE y=3),
294 (SELECT x AS 'b' FROM t1 WHERE y=4)
295 WHERE a=b
296 ORDER BY a
297 }
298 } {8 5 8 9 6 9 10 7 10}
299
300 # Tests of compound sub-selects
301 #
302 do_test select6-6.1 {
303 execsql {
304 DELETE FROM t1 WHERE x>4;
305 SELECT * FROM t1
306 }
307 } {1 1 2 2 3 2 4 3}
308 ifcapable compound {
309 do_test select6-6.2 {
310 execsql {
311 SELECT * FROM (
312 SELECT x AS 'a' FROM t1 UNION ALL SELECT x+10 AS 'a' FROM t1
313 ) ORDER BY a;
314 }
315 } {1 2 3 4 11 12 13 14}
316 do_test select6-6.3 {
317 execsql {
318 SELECT * FROM (
319 SELECT x AS 'a' FROM t1 UNION ALL SELECT x+1 AS 'a' FROM t1
320 ) ORDER BY a;
321 }
322 } {1 2 2 3 3 4 4 5}
323 do_test select6-6.4 {
324 execsql {
325 SELECT * FROM (
326 SELECT x AS 'a' FROM t1 UNION SELECT x+1 AS 'a' FROM t1
327 ) ORDER BY a;
328 }
329 } {1 2 3 4 5}
330 do_test select6-6.5 {
331 execsql {
332 SELECT * FROM (
333 SELECT x AS 'a' FROM t1 INTERSECT SELECT x+1 AS 'a' FROM t1
334 ) ORDER BY a;
335 }
336 } {2 3 4}
337 do_test select6-6.6 {
338 execsql {
339 SELECT * FROM (
340 SELECT x AS 'a' FROM t1 EXCEPT SELECT x*2 AS 'a' FROM t1
341 ) ORDER BY a;
342 }
343 } {1 3}
344 } ;# ifcapable compound
345
346 # Subselects with no FROM clause
347 #
348 do_test select6-7.1 {
349 execsql {
350 SELECT * FROM (SELECT 1)
351 }
352 } {1}
353 do_test select6-7.2 {
354 execsql {
355 SELECT c,b,a,* FROM (SELECT 1 AS 'a', 2 AS 'b', 'abc' AS 'c')
356 }
357 } {abc 2 1 1 2 abc}
358 do_test select6-7.3 {
359 execsql {
360 SELECT c,b,a,* FROM (SELECT 1 AS 'a', 2 AS 'b', 'abc' AS 'c' WHERE 0)
361 }
362 } {}
363 do_test select6-7.4 {
364 execsql2 {
365 SELECT c,b,a,* FROM (SELECT 1 AS 'a', 2 AS 'b', 'abc' AS 'c' WHERE 1)
366 }
367 } {c abc b 2 a 1 a 1 b 2 c abc}
368
369 # The remaining tests in this file depend on the EXPLAIN keyword.
370 # Skip these tests if EXPLAIN is disabled in the current build.
371 #
372 ifcapable {!explain} {
373 finish_test
374 return
375 }
376
377 # The following procedure compiles the SQL given as an argument and returns
378 # TRUE if that SQL uses any transient tables and returns FALSE if no
379 # transient tables are used. This is used to make sure that the
380 # sqliteFlattenSubquery() routine in select.c is doing its job.
381 #
382 proc is_flat {sql} {
383 return [expr 0>[lsearch [execsql "EXPLAIN $sql"] OpenEphemeral]]
384 }
385
386 # Check that the flattener works correctly for deeply nested subqueries
387 # involving joins.
388 #
389 do_test select6-8.1 {
390 execsql {
391 BEGIN;
392 CREATE TABLE t3(p,q);
393 INSERT INTO t3 VALUES(1,11);
394 INSERT INTO t3 VALUES(2,22);
395 CREATE TABLE t4(q,r);
396 INSERT INTO t4 VALUES(11,111);
397 INSERT INTO t4 VALUES(22,222);
398 COMMIT;
399 SELECT * FROM t3 NATURAL JOIN t4;
400 }
401 } {1 11 111 2 22 222}
402 do_test select6-8.2 {
403 execsql {
404 SELECT y, p, q, r FROM
405 (SELECT t1.y AS y, t2.b AS b FROM t1, t2 WHERE t1.x=t2.a) AS m,
406 (SELECT t3.p AS p, t3.q AS q, t4.r AS r FROM t3 NATURAL JOIN t4) as n
407 WHERE y=p
408 }
409 } {1 1 11 111 2 2 22 222 2 2 22 222}
410 # If view support is omitted from the build, then so is the query
411 # "flattener". So omit this test and test select6-8.6 in that case.
412 ifcapable view {
413 do_test select6-8.3 {
414 is_flat {
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
419 }
420 } {1}
421 } ;# ifcapable view
422 do_test select6-8.4 {
423 execsql {
424 SELECT DISTINCT y, p, q, r FROM
425 (SELECT t1.y AS y, t2.b AS b FROM t1, t2 WHERE t1.x=t2.a) AS m,
426 (SELECT t3.p AS p, t3.q AS q, t4.r AS r FROM t3 NATURAL JOIN t4) as n
427 WHERE y=p
428 }
429 } {1 1 11 111 2 2 22 222}
430 do_test select6-8.5 {
431 execsql {
432 SELECT * FROM
433 (SELECT y, p, q, r FROM
434 (SELECT t1.y AS y, t2.b AS b FROM t1, t2 WHERE t1.x=t2.a) AS m,
435 (SELECT t3.p AS p, t3.q AS q, t4.r AS r FROM t3 NATURAL JOIN t4) as n
436 WHERE y=p) AS e,
437 (SELECT r AS z FROM t4 WHERE q=11) AS f
438 WHERE e.r=f.z
439 }
440 } {1 1 11 111 111}
441 ifcapable view {
442 do_test select6-8.6 {
443 is_flat {
444 SELECT * FROM
445 (SELECT y, p, q, r FROM
446 (SELECT t1.y AS y, t2.b AS b FROM t1, t2 WHERE t1.x=t2.a) AS m,
447 (SELECT t3.p AS p, t3.q AS q, t4.r AS r FROM t3 NATURAL JOIN t4) as n
448 WHERE y=p) AS e,
449 (SELECT r AS z FROM t4 WHERE q=11) AS f
450 WHERE e.r=f.z
451 }
452 } {1}
453 } ;# ifcapable view
454
455 # Ticket #1634
456 #
457 do_test select6-9.1 {
458 execsql {
459 SELECT a.x, b.x FROM t1 AS a, (SELECT x FROM t1 LIMIT 2) AS b
460 ORDER BY 1, 2
461 }
462 } {1 1 1 2 2 1 2 2 3 1 3 2 4 1 4 2}
463 do_test select6-9.2 {
464 execsql {
465 SELECT x FROM (SELECT x FROM t1 LIMIT 2);
466 }
467 } {1 2}
468 do_test select6-9.3 {
469 execsql {
470 SELECT x FROM (SELECT x FROM t1 LIMIT 2 OFFSET 1);
471 }
472 } {2 3}
473 do_test select6-9.4 {
474 execsql {
475 SELECT x FROM (SELECT x FROM t1) LIMIT 2;
476 }
477 } {1 2}
478 do_test select6-9.5 {
479 execsql {
480 SELECT x FROM (SELECT x FROM t1) LIMIT 2 OFFSET 1;
481 }
482 } {2 3}
483 do_test select6-9.6 {
484 execsql {
485 SELECT x FROM (SELECT x FROM t1 LIMIT 2) LIMIT 3;
486 }
487 } {1 2}
488 do_test select6-9.7 {
489 execsql {
490 SELECT x FROM (SELECT x FROM t1 LIMIT -1) LIMIT 3;
491 }
492 } {1 2 3}
493 do_test select6-9.8 {
494 execsql {
495 SELECT x FROM (SELECT x FROM t1 LIMIT -1);
496 }
497 } {1 2 3 4}
498 do_test select6-9.9 {
499 execsql {
500 SELECT x FROM (SELECT x FROM t1 LIMIT -1 OFFSET 1);
501 }
502 } {2 3 4}
503 do_test select6-9.10 {
504 execsql {
505 SELECT x, y FROM (SELECT x, (SELECT 10+x) y FROM t1 LIMIT -1 OFFSET 1);
506 }
507 } {2 12 3 13 4 14}
508 do_test select6-9.11 {
509 execsql {
510 SELECT x, y FROM (SELECT x, (SELECT 10)+x y FROM t1 LIMIT -1 OFFSET 1);
511 }
512 } {2 12 3 13 4 14}
513
514
515 #-------------------------------------------------------------------------
516 # Test that if a UNION ALL sub-query that would otherwise be eligible for
517 # flattening consists of two or more SELECT statements that do not all
518 # return the same number of result columns, the error is detected.
519 #
520 do_execsql_test 10.1 {
521 CREATE TABLE t(i,j,k);
522 CREATE TABLE j(l,m);
523 CREATE TABLE k(o);
524 }
525
526 set err [list 1 {SELECTs to the left and right of UNION ALL do not have the same number of result columns}]
527
528 do_execsql_test 10.2 {
529 SELECT * FROM (SELECT * FROM t), j;
530 }
531 do_catchsql_test 10.3 {
532 SELECT * FROM t UNION ALL SELECT * FROM j
533 } $err
534 do_catchsql_test 10.4 {
535 SELECT * FROM (SELECT i FROM t UNION ALL SELECT l, m FROM j)
536 } $err
537 do_catchsql_test 10.5 {
538 SELECT * FROM (SELECT j FROM t UNION ALL SELECT * FROM j)
539 } $err
540 do_catchsql_test 10.6 {
541 SELECT * FROM (SELECT * FROM t UNION ALL SELECT * FROM j)
542 } $err
543 do_catchsql_test 10.7 {
544 SELECT * FROM (
545 SELECT * FROM t UNION ALL
546 SELECT l,m,l FROM j UNION ALL
547 SELECT * FROM k
548 )
549 } $err
550 do_catchsql_test 10.8 {
551 SELECT * FROM (
552 SELECT * FROM k UNION ALL
553 SELECT * FROM t UNION ALL
554 SELECT l,m,l FROM j
555 )
556 } $err
557
558 # 2015-02-09 Ticket [2f7170d73bf9abf80339187aa3677dce3dbcd5ca]
559 # "misuse of aggregate" error if aggregate column from FROM
560 # subquery is used in correlated subquery
561 #
562 do_execsql_test 11.1 {
563 DROP TABLE IF EXISTS t1;
564 CREATE TABLE t1(w INT, x INT);
565 INSERT INTO t1(w,x)
566 VALUES(1,10),(2,20),(3,30),
567 (2,21),(3,31),
568 (3,32);
569 CREATE INDEX t1wx ON t1(w,x);
570
571 DROP TABLE IF EXISTS t2;
572 CREATE TABLE t2(w INT, y VARCHAR(8));
573 INSERT INTO t2(w,y) VALUES(1,'one'),(2,'two'),(3,'three'),(4,'four');
574 CREATE INDEX t2wy ON t2(w,y);
575
576 SELECT cnt, xyz, (SELECT y FROM t2 WHERE w=cnt), '|'
577 FROM (SELECT count(*) AS cnt, w AS xyz FROM t1 GROUP BY 2)
578 ORDER BY cnt, xyz;
579 } {1 1 one | 2 2 two | 3 3 three |}
580 do_execsql_test 11.2 {
581 SELECT cnt, xyz, lower((SELECT y FROM t2 WHERE w=cnt)), '|'
582 FROM (SELECT count(*) AS cnt, w AS xyz FROM t1 GROUP BY 2)
583 ORDER BY cnt, xyz;
584 } {1 1 one | 2 2 two | 3 3 three |}
585 do_execsql_test 11.3 {
586 SELECT cnt, xyz, '|'
587 FROM (SELECT count(*) AS cnt, w AS xyz FROM t1 GROUP BY 2)
588 WHERE (SELECT y FROM t2 WHERE w=cnt)!='two'
589 ORDER BY cnt, xyz;
590 } {1 1 | 3 3 |}
591 do_execsql_test 11.4 {
592 SELECT cnt, xyz, '|'
593 FROM (SELECT count(*) AS cnt, w AS xyz FROM t1 GROUP BY 2)
594 ORDER BY lower((SELECT y FROM t2 WHERE w=cnt));
595 } {1 1 | 3 3 | 2 2 |}
596 do_execsql_test 11.5 {
597 SELECT cnt, xyz,
598 CASE WHEN (SELECT y FROM t2 WHERE w=cnt)=='two'
599 THEN 'aaa' ELSE 'bbb'
600 END, '|'
601 FROM (SELECT count(*) AS cnt, w AS xyz FROM t1 GROUP BY 2)
602 ORDER BY +cnt;
603 } {1 1 bbb | 2 2 aaa | 3 3 bbb |}
604
605 do_execsql_test 11.100 {
606 DROP TABLE t1;
607 DROP TABLE t2;
608 CREATE TABLE t1(x);
609 CREATE TABLE t2(y, z);
610 SELECT ( SELECT y FROM t2 WHERE z = cnt )
611 FROM ( SELECT count(*) AS cnt FROM t1 );
612 } {{}}
613
614 # 2019-05-29 ticket https://sqlite.org/src/info/c41afac34f15781f
615 # A LIMIT clause in a subquery is incorrectly applied to a subquery.
616 #
617 do_execsql_test 12.100 {
618 DROP TABLE t1;
619 DROP TABLE t2;
620 CREATE TABLE t1(a);
621 INSERT INTO t1 VALUES(1);
622 INSERT INTO t1 VALUES(2);
623 CREATE TABLE t2(b);
624 INSERT INTO t2 VALUES(3);
625 SELECT * FROM (
626 SELECT * FROM (SELECT * FROM t1 LIMIT 1)
627 UNION ALL
628 SELECT * from t2);
629 } {1 3}
630
631 #-------------------------------------------------------------------------
632 reset_db
633 do_execsql_test 13.100 {
634
635 CREATE TABLE t1(y INT);
636 INSERT INTO t1 (y) VALUES (1);
637
638 CREATE TABLE t2(x INTEGER);
639 INSERT INTO t2 VALUES(0);
640
641 CREATE TABLE empty1(z);
642 }
643
644 do_execsql_test 13.110 {
645 SELECT t1.y
646 FROM ( SELECT 'AAA' )
647 INNER JOIN (
648 SELECT 1 AS abc FROM (
649 SELECT 1 FROM t2 LEFT JOIN empty1
650 )
651 ) AS sub0 ON sub0.abc
652 , t1
653 RIGHT JOIN (SELECT 'BBB' FROM ( SELECT 'CCC' ))
654 } {1}
655
656 do_execsql_test 13.120 {
657 SELECT t1.y
658 FROM ( SELECT 'AAA' )
659 INNER JOIN (
660 SELECT 1 AS abc FROM (
661 SELECT 1 FROM t2 LEFT JOIN empty1
662 )
663 ) AS sub0 ON sub0.abc
664 , t1
665 RIGHT JOIN (SELECT 'BBB' FROM ( SELECT 'CCC' ))
666 WHERE t1.y
667 } {1}
668
669
670 finish_test