AryaWu/sqlite
0
1# 2008 June 242#3# The author disclaims copyright to this source code. In place of4# 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. 12#13# $Id: selectB.test,v 1.10 2009/04/02 16:59:47 drh Exp $14 15set testdir [file dirname $argv0]16source $testdir/tester.tcl17 18ifcapable !compound {19 finish_test20 return21}22 23proc test_transform {testname sql1 sql2 results} {24 set ::vdbe1 [list]25 set ::vdbe2 [list]26 db eval "explain $sql1" { lappend ::vdbe1 $opcode }27 db eval "explain $sql2" { lappend ::vdbe2 $opcode }28 29 do_test $testname.transform {30 set ::vdbe131 } $::vdbe232 33 set ::sql1 $sql134 do_test $testname.sql1 {35 execsql $::sql136 } $results37 38 set ::sql2 $sql239 do_test $testname.sql2 {40 execsql $::sql241 } $results42}43 44do_test selectB-1.1 {45 execsql {46 CREATE TABLE t1(a, b, c);47 CREATE TABLE t2(d, e, f);48 49 INSERT INTO t1 VALUES( 2, 4, 6);50 INSERT INTO t1 VALUES( 8, 10, 12);51 INSERT INTO t1 VALUES(14, 16, 18);52 53 INSERT INTO t2 VALUES(3, 6, 9);54 INSERT INTO t2 VALUES(12, 15, 18);55 INSERT INTO t2 VALUES(21, 24, 27);56 }57} {}58 59for {set ii 1} {$ii <= 2} {incr ii} {60 61 if {$ii == 2} {62 do_test selectB-2.1 {63 execsql {64 CREATE INDEX i1 ON t1(a);65 CREATE INDEX i2 ON t2(d);66 }67 } {}68 }69 70 test_transform selectB-$ii.2 {71 SELECT * FROM (SELECT a FROM t1 UNION ALL SELECT d FROM t2)72 } {73 SELECT a FROM t1 UNION ALL SELECT d FROM t274 } {2 8 14 3 12 21}75 76 test_transform selectB-$ii.3 {77 SELECT * FROM (SELECT a FROM t1 UNION ALL SELECT d FROM t2) ORDER BY 178 } {79 SELECT a FROM t1 UNION ALL SELECT d FROM t2 ORDER BY 180 } {2 3 8 12 14 21}81 82 test_transform selectB-$ii.4 {83 SELECT * FROM 84 (SELECT a FROM t1 UNION ALL SELECT d FROM t2) 85 WHERE a>10 ORDER BY 186 } {87 SELECT a FROM t1 WHERE a>10 UNION ALL SELECT d FROM t2 WHERE d>10 ORDER BY 188 } {12 14 21}89 90 test_transform selectB-$ii.5 {91 SELECT * FROM 92 (SELECT a FROM t1 UNION ALL SELECT d FROM t2) 93 WHERE a>10 ORDER BY a94 } {95 SELECT a FROM t1 WHERE a>10 96 UNION ALL 97 SELECT d FROM t2 WHERE d>10 98 ORDER BY a99 } {12 14 21}100 101 test_transform selectB-$ii.6 {102 SELECT * FROM 103 (SELECT a FROM t1 UNION ALL SELECT d FROM t2 WHERE d > 12) 104 WHERE a>10 ORDER BY a105 } {106 SELECT a FROM t1 WHERE a>10107 UNION ALL 108 SELECT d FROM t2 WHERE d>12 AND d>10109 ORDER BY a110 } {14 21}111 112 test_transform selectB-$ii.7 {113 SELECT * FROM (SELECT a FROM t1 UNION ALL SELECT d FROM t2) ORDER BY 1 114 LIMIT 2115 } {116 SELECT a FROM t1 UNION ALL SELECT d FROM t2 ORDER BY 1 LIMIT 2117 } {2 3}118 119 test_transform selectB-$ii.8 {120 SELECT * FROM (SELECT a FROM t1 UNION ALL SELECT d FROM t2) ORDER BY 1 121 LIMIT 2 OFFSET 3122 } {123 SELECT a FROM t1 UNION ALL SELECT d FROM t2 ORDER BY 1 LIMIT 2 OFFSET 3124 } {12 14}125 126 test_transform selectB-$ii.9 {127 SELECT * FROM (128 SELECT a FROM t1 UNION ALL SELECT d FROM t2 UNION ALL SELECT c FROM t1129 ) 130 } {131 SELECT a FROM t1 UNION ALL SELECT d FROM t2 UNION ALL SELECT c FROM t1132 } {2 8 14 3 12 21 6 12 18}133 134 test_transform selectB-$ii.10 {135 SELECT * FROM (136 SELECT a FROM t1 UNION ALL SELECT d FROM t2 UNION ALL SELECT c FROM t1137 ) ORDER BY 1138 } {139 SELECT a FROM t1 UNION ALL SELECT d FROM t2 UNION ALL SELECT c FROM t1140 ORDER BY 1141 } {2 3 6 8 12 12 14 18 21}142 143 test_transform selectB-$ii.11 {144 SELECT * FROM (145 SELECT a FROM t1 UNION ALL SELECT d FROM t2 UNION ALL SELECT c FROM t1146 ) WHERE a>=10 ORDER BY 1 LIMIT 3147 } {148 SELECT a FROM t1 WHERE a>=10 UNION ALL SELECT d FROM t2 WHERE d>=10149 UNION ALL SELECT c FROM t1 WHERE c>=10150 ORDER BY 1 LIMIT 3151 } {12 12 14}152 153 test_transform selectB-$ii.12 {154 SELECT * FROM (SELECT a FROM t1 UNION ALL SELECT d FROM t2 LIMIT 2)155 } {156 SELECT a FROM t1 UNION ALL SELECT d FROM t2 LIMIT 2157 } {2 8}158 159 # An ORDER BY in a compound subqueries defeats flattening. Ticket #3773160 # test_transform selectB-$ii.13 {161 # SELECT * FROM (SELECT a FROM t1 UNION ALL SELECT d FROM t2 ORDER BY a ASC)162 # } {163 # SELECT a FROM t1 UNION ALL SELECT d FROM t2 ORDER BY 1 ASC164 # } {2 3 8 12 14 21}165 # 166 # test_transform selectB-$ii.14 {167 # SELECT * FROM (SELECT a FROM t1 UNION ALL SELECT d FROM t2 ORDER BY a DESC)168 # } {169 # SELECT a FROM t1 UNION ALL SELECT d FROM t2 ORDER BY 1 DESC170 # } {21 14 12 8 3 2}171 #172 # test_transform selectB-$ii.14 {173 # SELECT * FROM (174 # SELECT a FROM t1 UNION ALL SELECT d FROM t2 ORDER BY a DESC175 # ) LIMIT 2 OFFSET 2176 # } {177 # SELECT a FROM t1 UNION ALL SELECT d FROM t2 ORDER BY 1 DESC178 # LIMIT 2 OFFSET 2179 # } {12 8}180 #181 # test_transform selectB-$ii.15 {182 # SELECT * FROM (183 # SELECT a, b FROM t1 UNION ALL SELECT d, e FROM t2 ORDER BY a ASC, e DESC184 # )185 # } {186 # SELECT a, b FROM t1 UNION ALL SELECT d, e FROM t2 ORDER BY a ASC, e DESC187 # } {2 4 3 6 8 10 12 15 14 16 21 24}188}189 190do_test selectB-3.0 {191 execsql {192 DROP INDEX i1;193 DROP INDEX i2;194 }195} {}196 197for {set ii 3} {$ii <= 6} {incr ii} {198 199 switch $ii {200 4 {201 optimization_control db query-flattener off202 }203 5 {204 optimization_control db query-flattener on205 do_test selectB-5.0 {206 execsql {207 CREATE INDEX i1 ON t1(a);208 CREATE INDEX i2 ON t1(b);209 CREATE INDEX i3 ON t1(c);210 CREATE INDEX i4 ON t2(d);211 CREATE INDEX i5 ON t2(e);212 CREATE INDEX i6 ON t2(f);213 }214 } {}215 }216 6 {217 optimization_control db query-flattener off218 }219 }220 221 do_test selectB-$ii.1 {222 execsql {223 SELECT DISTINCT * FROM 224 (SELECT c FROM t1 UNION ALL SELECT e FROM t2) 225 ORDER BY 1;226 }227 } {6 12 15 18 24}228 229 do_test selectB-$ii.2 {230 execsql {231 SELECT c, count(*) FROM 232 (SELECT c FROM t1 UNION ALL SELECT e FROM t2) 233 GROUP BY c ORDER BY 1;234 }235 } {6 2 12 1 15 1 18 1 24 1}236 do_test selectB-$ii.3 {237 execsql {238 SELECT c, count(*) FROM 239 (SELECT c FROM t1 UNION ALL SELECT e FROM t2) 240 GROUP BY c HAVING count(*)>1;241 }242 } {6 2}243 do_test selectB-$ii.4 {244 execsql {245 SELECT t4.c, t3.a FROM 246 (SELECT c FROM t1 UNION ALL SELECT e FROM t2) AS t4, t1 AS t3247 WHERE t3.a=14248 ORDER BY 1249 }250 } {6 14 6 14 12 14 15 14 18 14 24 14}251 252 do_test selectB-$ii.5 {253 execsql {254 SELECT d FROM t2 255 EXCEPT 256 SELECT a FROM (SELECT a FROM t1 UNION ALL SELECT d FROM t2)257 }258 } {}259 do_test selectB-$ii.6 {260 execsql {261 SELECT * FROM (SELECT a FROM t1 UNION ALL SELECT d FROM t2)262 EXCEPT 263 SELECT * FROM (SELECT a FROM t1 UNION ALL SELECT d FROM t2)264 }265 } {}266 do_test selectB-$ii.7 {267 execsql {268 SELECT c FROM t1269 EXCEPT 270 SELECT * FROM (SELECT e FROM t2 UNION ALL SELECT f FROM t2)271 }272 } {12}273 do_test selectB-$ii.8 {274 execsql {275 SELECT * FROM (SELECT e FROM t2 UNION ALL SELECT f FROM t2)276 EXCEPT 277 SELECT c FROM t1278 }279 } {9 15 24 27}280 do_test selectB-$ii.9 {281 execsql {282 SELECT * FROM (SELECT e FROM t2 UNION ALL SELECT f FROM t2)283 EXCEPT 284 SELECT c FROM t1285 ORDER BY c DESC286 }287 } {27 24 15 9}288 289 do_test selectB-$ii.10 {290 execsql {291 SELECT * FROM (SELECT e FROM t2 UNION ALL SELECT f FROM t2)292 UNION 293 SELECT c FROM t1294 ORDER BY c DESC295 }296 } {27 24 18 15 12 9 6}297 do_test selectB-$ii.11 {298 execsql {299 SELECT c FROM t1300 UNION 301 SELECT * FROM (SELECT e FROM t2 UNION ALL SELECT f FROM t2)302 ORDER BY c303 }304 } {6 9 12 15 18 24 27}305 do_test selectB-$ii.12 {306 execsql {307 SELECT c FROM t1 UNION SELECT e FROM t2 UNION ALL SELECT f FROM t2308 ORDER BY c309 }310 } {6 9 12 15 18 18 24 27}311 do_test selectB-$ii.13 {312 execsql {313 SELECT * FROM (SELECT e FROM t2 UNION ALL SELECT f FROM t2)314 UNION 315 SELECT * FROM (SELECT e FROM t2 UNION ALL SELECT f FROM t2)316 ORDER BY 1317 }318 } {6 9 15 18 24 27}319 320 do_test selectB-$ii.14 {321 execsql {322 SELECT c FROM t1323 INTERSECT 324 SELECT * FROM (SELECT e FROM t2 UNION ALL SELECT f FROM t2)325 ORDER BY 1326 }327 } {6 18}328 do_test selectB-$ii.15 {329 execsql {330 SELECT * FROM (SELECT e FROM t2 UNION ALL SELECT f FROM t2)331 INTERSECT 332 SELECT c FROM t1333 ORDER BY 1334 }335 } {6 18}336 do_test selectB-$ii.16 {337 execsql {338 SELECT * FROM (SELECT e FROM t2 UNION ALL SELECT f FROM t2)339 INTERSECT 340 SELECT * FROM (SELECT e FROM t2 UNION ALL SELECT f FROM t2)341 ORDER BY 1342 }343 } {6 9 15 18 24 27}344 345 do_test selectB-$ii.17 {346 execsql {347 SELECT * FROM (348 SELECT a FROM t1 UNION ALL SELECT d FROM t2 LIMIT 4349 ) LIMIT 2350 }351 } {2 8}352 353 do_test selectB-$ii.18 {354 execsql {355 SELECT * FROM (356 SELECT a FROM t1 UNION ALL SELECT d FROM t2 LIMIT 4 OFFSET 2357 ) LIMIT 2358 }359 } {14 3}360 361 do_test selectB-$ii.19 {362 execsql {363 SELECT * FROM (364 SELECT DISTINCT (a/10) FROM t1 UNION ALL SELECT DISTINCT(d%2) FROM t2365 )366 }367 } {0 1 1 0}368 369 do_test selectB-$ii.20 {370 execsql {371 SELECT DISTINCT * FROM (372 SELECT DISTINCT (a/10) FROM t1 UNION ALL SELECT DISTINCT(d%2) FROM t2373 )374 }375 } {0 1}376 377 do_test selectB-$ii.21 {378 execsql {379 SELECT * FROM (SELECT * FROM t1 UNION ALL SELECT * FROM t2) ORDER BY a+b380 }381 } {2 4 6 3 6 9 8 10 12 12 15 18 14 16 18 21 24 27}382 383 do_test selectB-$ii.22 {384 execsql {385 SELECT * FROM (SELECT 345 UNION ALL SELECT d FROM t2) ORDER BY 1;386 }387 } {3 12 21 345}388 389 do_test selectB-$ii.23 {390 execsql {391 SELECT x, y FROM (392 SELECT a AS x, b AS y FROM t1393 UNION ALL394 SELECT a*10 + 0.1, f*10 + 0.1 FROM t1 JOIN t2 ON (c=d)395 UNION ALL396 SELECT a*100, b*100 FROM t1397 ) ORDER BY 1;398 }399 } {2 4 8 10 14 16 80.1 180.1 200 400 800 1000 1400 1600}400 401 do_test selectB-$ii.24 {402 execsql {403 SELECT x, y FROM (404 SELECT a AS x, b AS y FROM t1405 UNION ALL406 SELECT a*10 + 0.1, f*10 + 0.1 FROM t1 LEFT JOIN t2 ON (c=d)407 UNION ALL408 SELECT a*100, b*100 FROM t1409 ) ORDER BY 1;410 }411 } {2 4 8 10 14 16 20.1 {} 80.1 180.1 140.1 {} 200 400 800 1000 1400 1600}412 413 do_test selectB-$ii.25 {414 execsql {415 SELECT x+y FROM (416 SELECT a AS x, b AS y FROM t1417 UNION ALL418 SELECT a*10 + 0.1, f*10 + 0.1 FROM t1 LEFT JOIN t2 ON (c=d)419 UNION ALL420 SELECT a*100, b*100 FROM t1421 ) WHERE y+x NOT NULL ORDER BY 1;422 }423 } {6 18 30 260.2 600 1800 3000}424}425 426finish_test427