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.9 2008/08/04 03:51:24 danielk1977 Exp $ 14 15set testdir [file dirname $argv0] 16source $testdir/tester.tcl 17 18ifcapable !compound { 19 finish_test 20 return 21} 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 ::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 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 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 test_transform selectB-$ii.13 { 160 SELECT * FROM (SELECT a FROM t1 UNION ALL SELECT d FROM t2 ORDER BY a ASC) 161 } { 162 SELECT a FROM t1 UNION ALL SELECT d FROM t2 ORDER BY 1 ASC 163 } {2 3 8 12 14 21} 164 165 test_transform selectB-$ii.14 { 166 SELECT * FROM (SELECT a FROM t1 UNION ALL SELECT d FROM t2 ORDER BY a DESC) 167 } { 168 SELECT a FROM t1 UNION ALL SELECT d FROM t2 ORDER BY 1 DESC 169 } {21 14 12 8 3 2} 170 171 test_transform selectB-$ii.14 { 172 SELECT * FROM ( 173 SELECT a FROM t1 UNION ALL SELECT d FROM t2 ORDER BY a DESC 174 ) LIMIT 2 OFFSET 2 175 } { 176 SELECT a FROM t1 UNION ALL SELECT d FROM t2 ORDER BY 1 DESC LIMIT 2 OFFSET 2 177 } {12 8} 178 179 test_transform selectB-$ii.15 { 180 SELECT * FROM ( 181 SELECT a, b FROM t1 UNION ALL SELECT d, e FROM t2 ORDER BY a ASC, e DESC 182 ) 183 } { 184 SELECT a, b FROM t1 UNION ALL SELECT d, e FROM t2 ORDER BY a ASC, e DESC 185 } {2 4 3 6 8 10 12 15 14 16 21 24} 186} 187 188do_test selectB-3.0 { 189 execsql { 190 DROP INDEX i1; 191 DROP INDEX i2; 192 } 193} {} 194 195for {set ii 3} {$ii <= 4} {incr ii} { 196 197 if {$ii == 4} { 198 do_test selectB-4.0 { 199 execsql { 200 CREATE INDEX i1 ON t1(a); 201 CREATE INDEX i2 ON t1(b); 202 CREATE INDEX i3 ON t1(c); 203 CREATE INDEX i4 ON t2(d); 204 CREATE INDEX i5 ON t2(e); 205 CREATE INDEX i6 ON t2(f); 206 } 207 } {} 208 } 209 210 do_test selectB-$ii.1 { 211 execsql { 212 SELECT DISTINCT * FROM 213 (SELECT c FROM t1 UNION ALL SELECT e FROM t2) 214 ORDER BY 1; 215 } 216 } {6 12 15 18 24} 217 218 do_test selectB-$ii.2 { 219 execsql { 220 SELECT c, count(*) FROM 221 (SELECT c FROM t1 UNION ALL SELECT e FROM t2) 222 GROUP BY c ORDER BY 1; 223 } 224 } {6 2 12 1 15 1 18 1 24 1} 225 do_test selectB-$ii.3 { 226 execsql { 227 SELECT c, count(*) FROM 228 (SELECT c FROM t1 UNION ALL SELECT e FROM t2) 229 GROUP BY c HAVING count(*)>1; 230 } 231 } {6 2} 232 do_test selectB-$ii.4 { 233 execsql { 234 SELECT t4.c, t3.a FROM 235 (SELECT c FROM t1 UNION ALL SELECT e FROM t2) AS t4, t1 AS t3 236 WHERE t3.a=14 237 ORDER BY 1 238 } 239 } {6 14 6 14 12 14 15 14 18 14 24 14} 240 241 do_test selectB-$ii.5 { 242 execsql { 243 SELECT d FROM t2 244 EXCEPT 245 SELECT a FROM (SELECT a FROM t1 UNION ALL SELECT d FROM t2) 246 } 247 } {} 248 do_test selectB-$ii.6 { 249 execsql { 250 SELECT * FROM (SELECT a FROM t1 UNION ALL SELECT d FROM t2) 251 EXCEPT 252 SELECT * FROM (SELECT a FROM t1 UNION ALL SELECT d FROM t2) 253 } 254 } {} 255 do_test selectB-$ii.7 { 256 execsql { 257 SELECT c FROM t1 258 EXCEPT 259 SELECT * FROM (SELECT e FROM t2 UNION ALL SELECT f FROM t2) 260 } 261 } {12} 262 do_test selectB-$ii.8 { 263 execsql { 264 SELECT * FROM (SELECT e FROM t2 UNION ALL SELECT f FROM t2) 265 EXCEPT 266 SELECT c FROM t1 267 } 268 } {9 15 24 27} 269 do_test selectB-$ii.9 { 270 execsql { 271 SELECT * FROM (SELECT e FROM t2 UNION ALL SELECT f FROM t2) 272 EXCEPT 273 SELECT c FROM t1 274 ORDER BY c DESC 275 } 276 } {27 24 15 9} 277 278 do_test selectB-$ii.10 { 279 execsql { 280 SELECT * FROM (SELECT e FROM t2 UNION ALL SELECT f FROM t2) 281 UNION 282 SELECT c FROM t1 283 ORDER BY c DESC 284 } 285 } {27 24 18 15 12 9 6} 286 do_test selectB-$ii.11 { 287 execsql { 288 SELECT c FROM t1 289 UNION 290 SELECT * FROM (SELECT e FROM t2 UNION ALL SELECT f FROM t2) 291 ORDER BY c 292 } 293 } {6 9 12 15 18 24 27} 294 do_test selectB-$ii.12 { 295 execsql { 296 SELECT c FROM t1 UNION SELECT e FROM t2 UNION ALL SELECT f FROM t2 297 ORDER BY c 298 } 299 } {6 9 12 15 18 18 24 27} 300 do_test selectB-$ii.13 { 301 execsql { 302 SELECT * FROM (SELECT e FROM t2 UNION ALL SELECT f FROM t2) 303 UNION 304 SELECT * FROM (SELECT e FROM t2 UNION ALL SELECT f FROM t2) 305 ORDER BY 1 306 } 307 } {6 9 15 18 24 27} 308 309 do_test selectB-$ii.14 { 310 execsql { 311 SELECT c FROM t1 312 INTERSECT 313 SELECT * FROM (SELECT e FROM t2 UNION ALL SELECT f FROM t2) 314 ORDER BY 1 315 } 316 } {6 18} 317 do_test selectB-$ii.15 { 318 execsql { 319 SELECT * FROM (SELECT e FROM t2 UNION ALL SELECT f FROM t2) 320 INTERSECT 321 SELECT c FROM t1 322 ORDER BY 1 323 } 324 } {6 18} 325 do_test selectB-$ii.16 { 326 execsql { 327 SELECT * FROM (SELECT e FROM t2 UNION ALL SELECT f FROM t2) 328 INTERSECT 329 SELECT * FROM (SELECT e FROM t2 UNION ALL SELECT f FROM t2) 330 ORDER BY 1 331 } 332 } {6 9 15 18 24 27} 333 334 do_test selectB-$ii.17 { 335 execsql { 336 SELECT * FROM ( 337 SELECT a FROM t1 UNION ALL SELECT d FROM t2 LIMIT 4 338 ) LIMIT 2 339 } 340 } {2 8} 341 342 do_test selectB-$ii.18 { 343 execsql { 344 SELECT * FROM ( 345 SELECT a FROM t1 UNION ALL SELECT d FROM t2 LIMIT 4 OFFSET 2 346 ) LIMIT 2 347 } 348 } {14 3} 349 350 do_test selectB-$ii.19 { 351 execsql { 352 SELECT * FROM ( 353 SELECT DISTINCT (a/10) FROM t1 UNION ALL SELECT DISTINCT(d%2) FROM t2 354 ) 355 } 356 } {0 1 0 1} 357 358 do_test selectB-$ii.20 { 359 execsql { 360 SELECT DISTINCT * FROM ( 361 SELECT DISTINCT (a/10) FROM t1 UNION ALL SELECT DISTINCT(d%2) FROM t2 362 ) 363 } 364 } {0 1} 365 366 do_test selectB-$ii.21 { 367 execsql { 368 SELECT * FROM (SELECT * FROM t1 UNION ALL SELECT * FROM t2) ORDER BY a+b 369 } 370 } {2 4 6 3 6 9 8 10 12 12 15 18 14 16 18 21 24 27} 371 372 do_test selectB-$ii.21 { 373 execsql { 374 SELECT * FROM (SELECT 345 UNION ALL SELECT d FROM t2) ORDER BY 1; 375 } 376 } {3 12 21 345} 377} 378 379finish_test 380