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 20set testdir [file dirname $argv0] 21source $testdir/tester.tcl 22 23do_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 52do_test select6-1.1 { 53 execsql2 {SELECT * FROM (SELECT x, y FROM t1 WHERE x<2)} 54} {x 1 y 1} 55do_test select6-1.2 { 56 execsql {SELECT count(*) FROM (SELECT y FROM t1)} 57} {20} 58do_test select6-1.3 { 59 execsql {SELECT count(*) FROM (SELECT DISTINCT y FROM t1)} 60} {5} 61do_test select6-1.4 { 62 execsql {SELECT count(*) FROM (SELECT DISTINCT * FROM (SELECT y FROM t1))} 63} {5} 64do_test select6-1.5 { 65 execsql {SELECT count(*) FROM (SELECT * FROM (SELECT DISTINCT y FROM t1))} 66} {5} 67 68do_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} 76do_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} 84do_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} 92do_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 101do_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} 108do_test select6-2.1 { 109 execsql2 {SELECT * FROM (SELECT a, b FROM t2 WHERE a<2)} 110} {a 1 b 1} 111do_test select6-2.2 { 112 execsql {SELECT count(*) FROM (SELECT b FROM t2)} 113} {20} 114do_test select6-2.3 { 115 execsql {SELECT count(*) FROM (SELECT DISTINCT b FROM t2)} 116} {5} 117do_test select6-2.4 { 118 execsql {SELECT count(*) FROM (SELECT DISTINCT * FROM (SELECT b FROM t2))} 119} {5} 120do_test select6-2.5 { 121 execsql {SELECT count(*) FROM (SELECT * FROM (SELECT DISTINCT b FROM t2))} 122} {5} 123 124do_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} 132do_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} 140do_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} 148do_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 157do_test sqlite6-3.1 { 158 execsql2 { 159 SELECT * FROM (SELECT * FROM (SELECT * FROM t1 WHERE x=3)); 160 } 161} {x 3 y 2} 162do_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} 172do_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} 177do_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} 182do_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} 187do_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} 193do_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} {} 199do_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} 205do_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} {} 211do_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} 217do_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} 224do_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} 231do_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} 238do_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} 244do_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 251do_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} 258do_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} 263do_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} 268do_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} 273do_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 279do_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} 288do_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# 300do_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} 306do_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} 313do_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} 320do_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} 327do_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} 334do_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# 344do_test select6-7.1 { 345 execsql { 346 SELECT * FROM (SELECT 1) 347 } 348} {1} 349do_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} 354do_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} {} 359do_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# 370proc 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# 377do_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} 390do_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} 398do_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} 406do_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} 414do_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} 425do_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 438finish_test 439