gitlab.com/CoiaPrant/sqlite3@v1.19.1/testdata/tcl/select6.test (about) 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://www.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 finish_test