usr/src/lib/libsqlite/test/select6.test

   1
   2 #pragma ident   "%Z%%M% %I%     %E% SMI"
   3
   4 # 2001 September 15
   5 #
   6 # The author disclaims copyright to this source code.  In place of
   7 # a legal notice, here is a blessing:
   8 #
   9 #    May you do good and not evil.
  10 #    May you find forgiveness for yourself and forgive others.
  11 #    May you share freely, never taking more than you give.
  12 #
  13 #***********************************************************************
  14 # This file implements regression tests for SQLite library.  The
  15 # focus of this file is testing SELECT statements that contain
  16 # subqueries in their FROM clause.
  17 #
  18 # $Id: select6.test,v 1.11 2004/01/24 20:18:13 drh Exp $
  19
  20 set testdir [file dirname $argv0]
  21 source $testdir/tester.tcl
  22
  23 do_test select6-1.0 {
  24   execsql {
  25     BEGIN;
  26     CREATE TABLE t1(x, y);
  27     INSERT INTO t1 VALUES(1,1);
  28     INSERT INTO t1 VALUES(2,2);
  29     INSERT INTO t1 VALUES(3,2);
  30     INSERT INTO t1 VALUES(4,3);
  31     INSERT INTO t1 VALUES(5,3);
  32     INSERT INTO t1 VALUES(6,3);
  33     INSERT INTO t1 VALUES(7,3);
  34     INSERT INTO t1 VALUES(8,4);
  35     INSERT INTO t1 VALUES(9,4);
  36     INSERT INTO t1 VALUES(10,4);
  37     INSERT INTO t1 VALUES(11,4);
  38     INSERT INTO t1 VALUES(12,4);
  39     INSERT INTO t1 VALUES(13,4);
  40     INSERT INTO t1 VALUES(14,4);
  41     INSERT INTO t1 VALUES(15,4);
  42     INSERT INTO t1 VALUES(16,5);
  43     INSERT INTO t1 VALUES(17,5);
  44     INSERT INTO t1 VALUES(18,5);
  45     INSERT INTO t1 VALUES(19,5);
  46     INSERT INTO t1 VALUES(20,5);
  47     COMMIT;
  48     SELECT DISTINCT y FROM t1 ORDER BY y;
  49   }
  50 } {1 2 3 4 5}
  51
  52 do_test select6-1.1 {
  53   execsql2 {SELECT * FROM (SELECT x, y FROM t1 WHERE x<2)}
  54 } {x 1 y 1}
  55 do_test select6-1.2 {
  56   execsql {SELECT count(*) FROM (SELECT y FROM t1)}
  57 } {20}
  58 do_test select6-1.3 {
  59   execsql {SELECT count(*) FROM (SELECT DISTINCT y FROM t1)}
  60 } {5}
  61 do_test select6-1.4 {
  62   execsql {SELECT count(*) FROM (SELECT DISTINCT * FROM (SELECT y FROM t1))}
  63 } {5}
  64 do_test select6-1.5 {
  65   execsql {SELECT count(*) FROM (SELECT * FROM (SELECT DISTINCT y FROM t1))}
  66 } {5}
  67
  68 do_test select6-1.6 {
  69   execsql {
  70     SELECT *
  71     FROM (SELECT count(*),y FROM t1 GROUP BY y) AS a,
  72          (SELECT max(x),y FROM t1 GROUP BY y) as b
  73     WHERE a.y=b.y ORDER BY a.y
  74   }
  75 } {1 1 1 1 2 2 3 2 4 3 7 3 8 4 15 4 5 5 20 5}
  76 do_test select6-1.7 {
  77   execsql {
  78     SELECT a.y, a.[count(*)], [max(x)], [count(*)]
  79     FROM (SELECT count(*),y FROM t1 GROUP BY y) AS a,
  80          (SELECT max(x),y FROM t1 GROUP BY y) as b
  81     WHERE a.y=b.y ORDER BY a.y
  82   }
  83 } {1 1 1 1 2 2 3 2 3 4 7 4 4 8 15 8 5 5 20 5}
  84 do_test select6-1.8 {
  85   execsql {
  86     SELECT q, p, r
  87     FROM (SELECT count(*) as p , y as q FROM t1 GROUP BY y) AS a,
  88          (SELECT max(x) as r, y as s FROM t1 GROUP BY y) as b
  89     WHERE q=s ORDER BY s
  90   }
  91 } {1 1 1 2 2 3 3 4 7 4 8 15 5 5 20}
  92 do_test select6-1.9 {
  93   execsql {
  94     SELECT q, p, r, b.[min(x)+y]
  95     FROM (SELECT count(*) as p , y as q FROM t1 GROUP BY y) AS a,
  96          (SELECT max(x) as r, y as s, min(x)+y FROM t1 GROUP BY y) as b
  97     WHERE q=s ORDER BY s
  98   }
  99 } {1 1 1 2 2 2 3 4 3 4 7 7 4 8 15 12 5 5 20 21}
 100
 101 do_test select6-2.0 {
 102   execsql {
 103     CREATE TABLE t2(a INTEGER PRIMARY KEY, b);
 104     INSERT INTO t2 SELECT * FROM t1;
 105     SELECT DISTINCT b FROM t2 ORDER BY b;
 106   }
 107 } {1 2 3 4 5}
 108 do_test select6-2.1 {
 109   execsql2 {SELECT * FROM (SELECT a, b FROM t2 WHERE a<2)}
 110 } {a 1 b 1}
 111 do_test select6-2.2 {
 112   execsql {SELECT count(*) FROM (SELECT b FROM t2)}
 113 } {20}
 114 do_test select6-2.3 {
 115   execsql {SELECT count(*) FROM (SELECT DISTINCT b FROM t2)}
 116 } {5}
 117 do_test select6-2.4 {
 118   execsql {SELECT count(*) FROM (SELECT DISTINCT * FROM (SELECT b FROM t2))}
 119 } {5}
 120 do_test select6-2.5 {
 121   execsql {SELECT count(*) FROM (SELECT * FROM (SELECT DISTINCT b FROM t2))}
 122 } {5}
 123
 124 do_test select6-2.6 {
 125   execsql {
 126     SELECT *
 127     FROM (SELECT count(*),b FROM t2 GROUP BY b) AS a,
 128          (SELECT max(a),b FROM t2 GROUP BY b) as b
 129     WHERE a.b=b.b ORDER BY a.b
 130   }
 131 } {1 1 1 1 2 2 3 2 4 3 7 3 8 4 15 4 5 5 20 5}
 132 do_test select6-2.7 {
 133   execsql {
 134     SELECT a.b, a.[count(*)], [max(a)], [count(*)]
 135     FROM (SELECT count(*),b FROM t2 GROUP BY b) AS a,
 136          (SELECT max(a),b FROM t2 GROUP BY b) as b
 137     WHERE a.b=b.b ORDER BY a.b
 138   }
 139 } {1 1 1 1 2 2 3 2 3 4 7 4 4 8 15 8 5 5 20 5}
 140 do_test select6-2.8 {
 141   execsql {
 142     SELECT q, p, r
 143     FROM (SELECT count(*) as p , b as q FROM t2 GROUP BY b) AS a,
 144          (SELECT max(a) as r, b as s FROM t2 GROUP BY b) as b
 145     WHERE q=s ORDER BY s
 146   }
 147 } {1 1 1 2 2 3 3 4 7 4 8 15 5 5 20}
 148 do_test select6-2.9 {
 149   execsql {
 150     SELECT a.q, a.p, b.r
 151     FROM (SELECT count(*) as p , b as q FROM t2 GROUP BY q) AS a,
 152          (SELECT max(a) as r, b as s FROM t2 GROUP BY s) as b
 153     WHERE a.q=b.s ORDER BY a.q
 154   }
 155 } {1 1 1 2 2 3 3 4 7 4 8 15 5 5 20}
 156
 157 do_test sqlite6-3.1 {
 158   execsql2 {
 159     SELECT * FROM (SELECT * FROM (SELECT * FROM t1 WHERE x=3));
 160   }
 161 } {x 3 y 2}
 162 do_test sqlite6-3.2 {
 163   execsql {
 164     SELECT * FROM
 165       (SELECT a.q, a.p, b.r
 166        FROM (SELECT count(*) as p , b as q FROM t2 GROUP BY q) AS a,
 167             (SELECT max(a) as r, b as s FROM t2 GROUP BY s) as b
 168        WHERE a.q=b.s ORDER BY a.q)
 169     ORDER BY q
 170   }
 171 } {1 1 1 2 2 3 3 4 7 4 8 15 5 5 20}
 172 do_test select6-3.3 {
 173   execsql {
 174     SELECT a,b,a+b FROM (SELECT avg(x) as 'a', avg(y) as 'b' FROM t1)
 175   }
 176 } {10.5 3.7 14.2}
 177 do_test select6-3.4 {
 178   execsql {
 179     SELECT a,b,a+b FROM (SELECT avg(x) as 'a', avg(y) as 'b' FROM t1 WHERE y=4)
 180   }
 181 } {11.5 4 15.5}
 182 do_test select6-3.5 {
 183   execsql {
 184     SELECT x,y,x+y FROM (SELECT avg(a) as 'x', avg(b) as 'y' FROM t2 WHERE a=4)
 185   }
 186 } {4 3 7}
 187 do_test select6-3.6 {
 188   execsql {
 189     SELECT a,b,a+b FROM (SELECT avg(x) as 'a', avg(y) as 'b' FROM t1)
 190     WHERE a>10
 191   }
 192 } {10.5 3.7 14.2}
 193 do_test select6-3.7 {
 194   execsql {
 195     SELECT a,b,a+b FROM (SELECT avg(x) as 'a', avg(y) as 'b' FROM t1)
 196     WHERE a<10
 197   }
 198 } {}
 199 do_test select6-3.8 {
 200   execsql {
 201     SELECT a,b,a+b FROM (SELECT avg(x) as 'a', avg(y) as 'b' FROM t1 WHERE y=4)
 202     WHERE a>10
 203   }
 204 } {11.5 4 15.5}
 205 do_test select6-3.9 {
 206   execsql {
 207     SELECT a,b,a+b FROM (SELECT avg(x) as 'a', avg(y) as 'b' FROM t1 WHERE y=4)
 208     WHERE a<10
 209   }
 210 } {}
 211 do_test select6-3.10 {
 212   execsql {
 213     SELECT a,b,a+b FROM (SELECT avg(x) as 'a', y as 'b' FROM t1 GROUP BY b)
 214     ORDER BY a
 215   }
 216 } {1 1 2 2.5 2 4.5 5.5 3 8.5 11.5 4 15.5 18 5 23}
 217 do_test select6-3.11 {
 218   execsql {
 219     SELECT a,b,a+b FROM
 220        (SELECT avg(x) as 'a', y as 'b' FROM t1 GROUP BY b)
 221     WHERE b<4 ORDER BY a
 222   }
 223 } {1 1 2 2.5 2 4.5 5.5 3 8.5}
 224 do_test select6-3.12 {
 225   execsql {
 226     SELECT a,b,a+b FROM
 227        (SELECT avg(x) as 'a', y as 'b' FROM t1 GROUP BY b HAVING a>1)
 228     WHERE b<4 ORDER BY a
 229   }
 230 } {2.5 2 4.5 5.5 3 8.5}
 231 do_test select6-3.13 {
 232   execsql {
 233     SELECT a,b,a+b FROM
 234        (SELECT avg(x) as 'a', y as 'b' FROM t1 GROUP BY b HAVING a>1)
 235     ORDER BY a
 236   }
 237 } {2.5 2 4.5 5.5 3 8.5 11.5 4 15.5 18 5 23}
 238 do_test select6-3.14 {
 239   execsql {
 240     SELECT [count(*)],y FROM (SELECT count(*), y FROM t1 GROUP BY y)
 241     ORDER BY [count(*)]
 242   }
 243 } {1 1 2 2 4 3 5 5 8 4}
 244 do_test select6-3.15 {
 245   execsql {
 246     SELECT [count(*)],y FROM (SELECT count(*), y FROM t1 GROUP BY y)
 247     ORDER BY y
 248   }
 249 } {1 1 2 2 4 3 8 4 5 5}
 250
 251 do_test select6-4.1 {
 252   execsql {
 253     SELECT a,b,c FROM
 254       (SELECT x AS 'a', y AS 'b', x+y AS 'c' FROM t1 WHERE y=4)
 255     WHERE a<10 ORDER BY a;
 256   }
 257 } {8 4 12 9 4 13}
 258 do_test select6-4.2 {
 259   execsql {
 260     SELECT y FROM (SELECT DISTINCT y FROM t1) WHERE y<5 ORDER BY y
 261   }
 262 } {1 2 3 4}
 263 do_test select6-4.3 {
 264   execsql {
 265     SELECT DISTINCT y FROM (SELECT y FROM t1) WHERE y<5 ORDER BY y
 266   }
 267 } {1 2 3 4}
 268 do_test select6-4.4 {
 269   execsql {
 270     SELECT avg(y) FROM (SELECT DISTINCT y FROM t1) WHERE y<5 ORDER BY y
 271   }
 272 } {2.5}
 273 do_test select6-4.5 {
 274   execsql {
 275     SELECT avg(y) FROM (SELECT DISTINCT y FROM t1 WHERE y<5) ORDER BY y
 276   }
 277 } {2.5}
 278
 279 do_test select6-5.1 {
 280   execsql {
 281     SELECT a,x,b FROM
 282       (SELECT x+3 AS 'a', x FROM t1 WHERE y=3) AS 'p',
 283       (SELECT x AS 'b' FROM t1 WHERE y=4) AS 'q'
 284     WHERE a=b
 285     ORDER BY a
 286   }
 287 } {8 5 8 9 6 9 10 7 10}
 288 do_test select6-5.2 {
 289   execsql {
 290     SELECT a,x,b FROM
 291       (SELECT x+3 AS 'a', x FROM t1 WHERE y=3),
 292       (SELECT x AS 'b' FROM t1 WHERE y=4)
 293     WHERE a=b
 294     ORDER BY a
 295   }
 296 } {8 5 8 9 6 9 10 7 10}
 297
 298 # Tests of compound sub-selects
 299 #
 300 do_test select5-6.1 {
 301   execsql {
 302     DELETE FROM t1 WHERE x>4;
 303     SELECT * FROM t1
 304   }
 305 } {1 1 2 2 3 2 4 3}
 306 do_test select6-6.2 {
 307   execsql {
 308     SELECT * FROM (
 309       SELECT x AS 'a' FROM t1 UNION ALL SELECT x+10 AS 'a' FROM t1
 310     ) ORDER BY a;
 311   }
 312 } {1 2 3 4 11 12 13 14}
 313 do_test select6-6.3 {
 314   execsql {
 315     SELECT * FROM (
 316       SELECT x AS 'a' FROM t1 UNION ALL SELECT x+1 AS 'a' FROM t1
 317     ) ORDER BY a;
 318   }
 319 } {1 2 2 3 3 4 4 5}
 320 do_test select6-6.4 {
 321   execsql {
 322     SELECT * FROM (
 323       SELECT x AS 'a' FROM t1 UNION SELECT x+1 AS 'a' FROM t1
 324     ) ORDER BY a;
 325   }
 326 } {1 2 3 4 5}
 327 do_test select6-6.5 {
 328   execsql {
 329     SELECT * FROM (
 330       SELECT x AS 'a' FROM t1 INTERSECT SELECT x+1 AS 'a' FROM t1
 331     ) ORDER BY a;
 332   }
 333 } {2 3 4}
 334 do_test select6-6.6 {
 335   execsql {
 336     SELECT * FROM (
 337       SELECT x AS 'a' FROM t1 EXCEPT SELECT x*2 AS 'a' FROM t1
 338     ) ORDER BY a;
 339   }
 340 } {1 3}
 341
 342 # Subselects with no FROM clause
 343 #
 344 do_test select6-7.1 {
 345   execsql {
 346     SELECT * FROM (SELECT 1)
 347   }
 348 } {1}
 349 do_test select6-7.2 {
 350   execsql {
 351     SELECT c,b,a,* FROM (SELECT 1 AS 'a', 2 AS 'b', 'abc' AS 'c')
 352   }
 353 } {abc 2 1 1 2 abc}
 354 do_test select6-7.3 {
 355   execsql {
 356     SELECT c,b,a,* FROM (SELECT 1 AS 'a', 2 AS 'b', 'abc' AS 'c' WHERE 0)
 357   }
 358 } {}
 359 do_test select6-7.4 {
 360   execsql2 {
 361     SELECT c,b,a,* FROM (SELECT 1 AS 'a', 2 AS 'b', 'abc' AS 'c' WHERE 1)
 362   }
 363 } {c abc b 2 a 1 a 1 b 2 c abc}
 364
 365 # The following procedure compiles the SQL given as an argument and returns
 366 # TRUE if that SQL uses any transient tables and returns FALSE if no
 367 # transient tables are used.  This is used to make sure that the
 368 # sqliteFlattenSubquery() routine in select.c is doing its job.
 369 #
 370 proc is_flat {sql} {
 371   return [expr 0>[lsearch [execsql "EXPLAIN $sql"] OpenTemp]]
 372 }
 373
 374 # Check that the flattener works correctly for deeply nested subqueries
 375 # involving joins.
 376 #
 377 do_test select6-8.1 {
 378   execsql {
 379     BEGIN;
 380     CREATE TABLE t3(p,q);
 381     INSERT INTO t3 VALUES(1,11);
 382     INSERT INTO t3 VALUES(2,22);
 383     CREATE TABLE t4(q,r);
 384     INSERT INTO t4 VALUES(11,111);
 385     INSERT INTO t4 VALUES(22,222);
 386     COMMIT;
 387     SELECT * FROM t3 NATURAL JOIN t4;
 388   }
 389 } {1 11 111 2 22 222}
 390 do_test select6-8.2 {
 391   execsql {
 392     SELECT y, p, q, r FROM
 393        (SELECT t1.y AS y, t2.b AS b FROM t1, t2 WHERE t1.x=t2.a) AS m,
 394        (SELECT t3.p AS p, t3.q AS q, t4.r AS r FROM t3 NATURAL JOIN t4) as n
 395     WHERE  y=p
 396   }
 397 } {1 1 11 111 2 2 22 222 2 2 22 222}
 398 do_test select6-8.3 {
 399   is_flat {
 400     SELECT y, p, q, r FROM
 401        (SELECT t1.y AS y, t2.b AS b FROM t1, t2 WHERE t1.x=t2.a) AS m,
 402        (SELECT t3.p AS p, t3.q AS q, t4.r AS r FROM t3 NATURAL JOIN t4) as n
 403     WHERE  y=p
 404   }
 405 } {1}
 406 do_test select6-8.4 {
 407   execsql {
 408     SELECT DISTINCT y, p, q, r FROM
 409        (SELECT t1.y AS y, t2.b AS b FROM t1, t2 WHERE t1.x=t2.a) AS m,
 410        (SELECT t3.p AS p, t3.q AS q, t4.r AS r FROM t3 NATURAL JOIN t4) as n
 411     WHERE  y=p
 412   }
 413 } {1 1 11 111 2 2 22 222}
 414 do_test select6-8.5 {
 415   execsql {
 416     SELECT * FROM
 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) AS e,
 421       (SELECT r AS z FROM t4 WHERE q=11) AS f
 422     WHERE e.r=f.z
 423   }
 424 } {1 1 11 111 111}
 425 do_test select6-8.6 {
 426   is_flat {
 427     SELECT * FROM
 428       (SELECT y, p, q, r FROM
 429          (SELECT t1.y AS y, t2.b AS b FROM t1, t2 WHERE t1.x=t2.a) AS m,
 430          (SELECT t3.p AS p, t3.q AS q, t4.r AS r FROM t3 NATURAL JOIN t4) as n
 431       WHERE  y=p) AS e,
 432       (SELECT r AS z FROM t4 WHERE q=11) AS f
 433     WHERE e.r=f.z
 434   }
 435 } {1}
 436
 437
 438 finish_test