test/select6.test

   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