gitlab.com/CoiaPrant/sqlite3@v1.19.1/testdata/tcl/selectB.test (about) 1 # 2008 June 24 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. 12 # 13 # $Id: selectB.test,v 1.10 2009/04/02 16:59:47 drh Exp $ 14 15 set testdir [file dirname $argv0] 16 source $testdir/tester.tcl 17 18 ifcapable !compound { 19 finish_test 20 return 21 } 22 23 proc 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 ::vdbe1 31 } $::vdbe2 32 33 set ::sql1 $sql1 34 do_test $testname.sql1 { 35 execsql $::sql1 36 } $results 37 38 set ::sql2 $sql2 39 do_test $testname.sql2 { 40 execsql $::sql2 41 } $results 42 } 43 44 do_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 59 for {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 t2 74 } {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 1 78 } { 79 SELECT a FROM t1 UNION ALL SELECT d FROM t2 ORDER BY 1 80 } {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 1 86 } { 87 SELECT a FROM t1 WHERE a>10 UNION ALL SELECT d FROM t2 WHERE d>10 ORDER BY 1 88 } {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 a 94 } { 95 SELECT a FROM t1 WHERE a>10 96 UNION ALL 97 SELECT d FROM t2 WHERE d>10 98 ORDER BY a 99 } {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 a 105 } { 106 SELECT a FROM t1 WHERE a>10 107 UNION ALL 108 SELECT d FROM t2 WHERE d>12 AND d>10 109 ORDER BY a 110 } {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 2 115 } { 116 SELECT a FROM t1 UNION ALL SELECT d FROM t2 ORDER BY 1 LIMIT 2 117 } {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 3 122 } { 123 SELECT a FROM t1 UNION ALL SELECT d FROM t2 ORDER BY 1 LIMIT 2 OFFSET 3 124 } {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 t1 129 ) 130 } { 131 SELECT a FROM t1 UNION ALL SELECT d FROM t2 UNION ALL SELECT c FROM t1 132 } {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 t1 137 ) ORDER BY 1 138 } { 139 SELECT a FROM t1 UNION ALL SELECT d FROM t2 UNION ALL SELECT c FROM t1 140 ORDER BY 1 141 } {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 t1 146 ) WHERE a>=10 ORDER BY 1 LIMIT 3 147 } { 148 SELECT a FROM t1 WHERE a>=10 UNION ALL SELECT d FROM t2 WHERE d>=10 149 UNION ALL SELECT c FROM t1 WHERE c>=10 150 ORDER BY 1 LIMIT 3 151 } {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 2 157 } {2 8} 158 159 # An ORDER BY in a compound subqueries defeats flattening. Ticket #3773 160 # 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 ASC 164 # } {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 DESC 170 # } {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 DESC 175 # ) LIMIT 2 OFFSET 2 176 # } { 177 # SELECT a FROM t1 UNION ALL SELECT d FROM t2 ORDER BY 1 DESC 178 # LIMIT 2 OFFSET 2 179 # } {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 DESC 184 # ) 185 # } { 186 # SELECT a, b FROM t1 UNION ALL SELECT d, e FROM t2 ORDER BY a ASC, e DESC 187 # } {2 4 3 6 8 10 12 15 14 16 21 24} 188 } 189 190 do_test selectB-3.0 { 191 execsql { 192 DROP INDEX i1; 193 DROP INDEX i2; 194 } 195 } {} 196 197 for {set ii 3} {$ii <= 6} {incr ii} { 198 199 switch $ii { 200 4 { 201 optimization_control db query-flattener off 202 } 203 5 { 204 optimization_control db query-flattener on 205 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 off 218 } 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 t3 247 WHERE t3.a=14 248 ORDER BY 1 249 } 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 t1 269 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 t1 278 } 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 t1 285 ORDER BY c DESC 286 } 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 t1 294 ORDER BY c DESC 295 } 296 } {27 24 18 15 12 9 6} 297 do_test selectB-$ii.11 { 298 execsql { 299 SELECT c FROM t1 300 UNION 301 SELECT * FROM (SELECT e FROM t2 UNION ALL SELECT f FROM t2) 302 ORDER BY c 303 } 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 t2 308 ORDER BY c 309 } 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 1 317 } 318 } {6 9 15 18 24 27} 319 320 do_test selectB-$ii.14 { 321 execsql { 322 SELECT c FROM t1 323 INTERSECT 324 SELECT * FROM (SELECT e FROM t2 UNION ALL SELECT f FROM t2) 325 ORDER BY 1 326 } 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 t1 333 ORDER BY 1 334 } 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 1 342 } 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 4 349 ) LIMIT 2 350 } 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 2 357 ) LIMIT 2 358 } 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 t2 365 ) 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 t2 373 ) 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+b 380 } 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 t1 393 UNION ALL 394 SELECT a*10 + 0.1, f*10 + 0.1 FROM t1 JOIN t2 ON (c=d) 395 UNION ALL 396 SELECT a*100, b*100 FROM t1 397 ) 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 t1 405 UNION ALL 406 SELECT a*10 + 0.1, f*10 + 0.1 FROM t1 LEFT JOIN t2 ON (c=d) 407 UNION ALL 408 SELECT a*100, b*100 FROM t1 409 ) 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 t1 417 UNION ALL 418 SELECT a*10 + 0.1, f*10 + 0.1 FROM t1 LEFT JOIN t2 ON (c=d) 419 UNION ALL 420 SELECT a*100, b*100 FROM t1 421 ) WHERE y+x NOT NULL ORDER BY 1; 422 } 423 } {6 18 30 260.2 600 1800 3000} 424 } 425 426 finish_test