sqlite3/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 # $Id: select6.test,v 1.17 2005/01/29 08:32:46 danielk1977 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 ifcapable compound {
 304 do_test select5-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 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"] OpenTemp]]
 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
 457 finish_test