gitlab.com/CoiaPrant/sqlite3@v1.19.1/testdata/tcl/selectA.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 # The focus of this file is testing the compound-SELECT merge 14 # optimization. Or, in other words, making sure that all 15 # possible combinations of UNION, UNION ALL, EXCEPT, and 16 # INTERSECT work together with an ORDER BY clause (with or w/o 17 # explicit sort order and explicit collating secquites) and 18 # with and without optional LIMIT and OFFSET clauses. 19 # 20 # $Id: selectA.test,v 1.6 2008/08/21 14:24:29 drh Exp $ 21 22 set testdir [file dirname $argv0] 23 source $testdir/tester.tcl 24 set testprefix selectA 25 26 ifcapable !compound { 27 finish_test 28 return 29 } 30 31 do_test selectA-1.0 { 32 execsql { 33 CREATE TABLE t1(a,b,c COLLATE NOCASE); 34 INSERT INTO t1 VALUES(1,'a','a'); 35 INSERT INTO t1 VALUES(9.9, 'b', 'B'); 36 INSERT INTO t1 VALUES(NULL, 'C', 'c'); 37 INSERT INTO t1 VALUES('hello', 'd', 'D'); 38 INSERT INTO t1 VALUES(x'616263', 'e', 'e'); 39 SELECT * FROM t1; 40 } 41 } {1 a a 9.9 b B {} C c hello d D abc e e} 42 do_test selectA-1.1 { 43 execsql { 44 CREATE TABLE t2(x,y,z COLLATE NOCASE); 45 INSERT INTO t2 VALUES(NULL,'U','u'); 46 INSERT INTO t2 VALUES('mad', 'Z', 'z'); 47 INSERT INTO t2 VALUES(x'68617265', 'm', 'M'); 48 INSERT INTO t2 VALUES(5.2e6, 'X', 'x'); 49 INSERT INTO t2 VALUES(-23, 'Y', 'y'); 50 SELECT * FROM t2; 51 } 52 } {{} U u mad Z z hare m M 5200000.0 X x -23 Y y} 53 do_test selectA-1.2 { 54 execsql { 55 CREATE TABLE t3(a,b,c COLLATE NOCASE); 56 INSERT INTO t3 SELECT * FROM t1; 57 INSERT INTO t3 SELECT * FROM t2; 58 INSERT INTO t3 SELECT * FROM t1; 59 INSERT INTO t3 SELECT * FROM t2; 60 INSERT INTO t3 SELECT * FROM t1; 61 INSERT INTO t3 SELECT * FROM t2; 62 SELECT count(*) FROM t3; 63 } 64 } {30} 65 66 do_test selectA-2.1 { 67 execsql { 68 SELECT a,b,c FROM t1 UNION ALL SELECT x,y,z FROM t2 69 ORDER BY a,b,c 70 } 71 } {{} C c {} U u -23 Y y 1 a a 9.9 b B 5200000.0 X x hello d D mad Z z abc e e hare m M} 72 do_test selectA-2.1.1 { # Ticket #3314 73 execsql { 74 SELECT t1.a, t1.b, t1.c FROM t1 UNION ALL SELECT x,y,z FROM t2 75 ORDER BY a,b,c 76 } 77 } {{} C c {} U u -23 Y y 1 a a 9.9 b B 5200000.0 X x hello d D mad Z z abc e e hare m M} 78 do_test selectA-2.1.2 { # Ticket #3314 79 execsql { 80 SELECT a,b,c FROM t1 UNION ALL SELECT x,y,z FROM t2 81 ORDER BY t1.a, t1.b, t1.c 82 } 83 } {{} C c {} U u -23 Y y 1 a a 9.9 b B 5200000.0 X x hello d D mad Z z abc e e hare m M} 84 do_test selectA-2.2 { 85 execsql { 86 SELECT a,b,c FROM t1 UNION ALL SELECT x,y,z FROM t2 87 ORDER BY a DESC,b,c 88 } 89 } {hare m M abc e e mad Z z hello d D 5200000.0 X x 9.9 b B 1 a a -23 Y y {} C c {} U u} 90 do_test selectA-2.3 { 91 execsql { 92 SELECT a,b,c FROM t1 UNION ALL SELECT x,y,z FROM t2 93 ORDER BY a,c,b 94 } 95 } {{} C c {} U u -23 Y y 1 a a 9.9 b B 5200000.0 X x hello d D mad Z z abc e e hare m M} 96 do_test selectA-2.4 { 97 execsql { 98 SELECT a,b,c FROM t1 UNION ALL SELECT x,y,z FROM t2 99 ORDER BY b,a,c 100 } 101 } {{} C c {} U u 5200000.0 X x -23 Y y mad Z z 1 a a 9.9 b B hello d D abc e e hare m M} 102 do_test selectA-2.5 { 103 execsql { 104 SELECT a,b,c FROM t1 UNION ALL SELECT x,y,z FROM t2 105 ORDER BY b COLLATE NOCASE,a,c 106 } 107 } {1 a a 9.9 b B {} C c hello d D abc e e hare m M {} U u 5200000.0 X x -23 Y y mad Z z} 108 do_test selectA-2.6 { 109 execsql { 110 SELECT a,b,c FROM t1 UNION ALL SELECT x,y,z FROM t2 111 ORDER BY b COLLATE NOCASE DESC,a,c 112 } 113 } {mad Z z -23 Y y 5200000.0 X x {} U u hare m M abc e e hello d D {} C c 9.9 b B 1 a a} 114 do_test selectA-2.7 { 115 execsql { 116 SELECT a,b,c FROM t1 UNION ALL SELECT x,y,z FROM t2 117 ORDER BY c,b,a 118 } 119 } {1 a a 9.9 b B {} C c hello d D abc e e hare m M {} U u 5200000.0 X x -23 Y y mad Z z} 120 do_test selectA-2.8 { 121 execsql { 122 SELECT a,b,c FROM t1 UNION ALL SELECT x,y,z FROM t2 123 ORDER BY c,a,b 124 } 125 } {1 a a 9.9 b B {} C c hello d D abc e e hare m M {} U u 5200000.0 X x -23 Y y mad Z z} 126 do_test selectA-2.9 { 127 execsql { 128 SELECT a,b,c FROM t1 UNION ALL SELECT x,y,z FROM t2 129 ORDER BY c DESC,a,b 130 } 131 } {mad Z z -23 Y y 5200000.0 X x {} U u hare m M abc e e hello d D {} C c 9.9 b B 1 a a} 132 do_test selectA-2.10 { 133 execsql { 134 SELECT a,b,c FROM t1 UNION ALL SELECT x,y,z FROM t2 135 ORDER BY c COLLATE BINARY DESC,a,b 136 } 137 } {mad Z z -23 Y y 5200000.0 X x {} U u abc e e {} C c 1 a a hare m M hello d D 9.9 b B} 138 do_test selectA-2.11 { 139 execsql { 140 SELECT x,y,z FROM t2 UNION ALL SELECT a,b,c FROM t1 141 ORDER BY a,b,c 142 } 143 } {{} C c {} U u -23 Y y 1 a a 9.9 b B 5200000.0 X x hello d D mad Z z abc e e hare m M} 144 do_test selectA-2.12 { 145 execsql { 146 SELECT x,y,z FROM t2 UNION ALL SELECT a,b,c FROM t1 147 ORDER BY a DESC,b,c 148 } 149 } {hare m M abc e e mad Z z hello d D 5200000.0 X x 9.9 b B 1 a a -23 Y y {} C c {} U u} 150 do_test selectA-2.13 { 151 execsql { 152 SELECT x,y,z FROM t2 UNION ALL SELECT a,b,c FROM t1 153 ORDER BY a,c,b 154 } 155 } {{} C c {} U u -23 Y y 1 a a 9.9 b B 5200000.0 X x hello d D mad Z z abc e e hare m M} 156 do_test selectA-2.14 { 157 execsql { 158 SELECT x,y,z FROM t2 UNION ALL SELECT a,b,c FROM t1 159 ORDER BY b,a,c 160 } 161 } {{} C c {} U u 5200000.0 X x -23 Y y mad Z z 1 a a 9.9 b B hello d D abc e e hare m M} 162 do_test selectA-2.15 { 163 execsql { 164 SELECT x,y,z FROM t2 UNION ALL SELECT a,b,c FROM t1 165 ORDER BY b COLLATE NOCASE,a,c 166 } 167 } {1 a a 9.9 b B {} C c hello d D abc e e hare m M {} U u 5200000.0 X x -23 Y y mad Z z} 168 do_test selectA-2.16 { 169 execsql { 170 SELECT x,y,z FROM t2 UNION ALL SELECT a,b,c FROM t1 171 ORDER BY b COLLATE NOCASE DESC,a,c 172 } 173 } {mad Z z -23 Y y 5200000.0 X x {} U u hare m M abc e e hello d D {} C c 9.9 b B 1 a a} 174 do_test selectA-2.17 { 175 execsql { 176 SELECT x,y,z FROM t2 UNION ALL SELECT a,b,c FROM t1 177 ORDER BY c,b,a 178 } 179 } {1 a a 9.9 b B {} C c hello d D abc e e hare m M {} U u 5200000.0 X x -23 Y y mad Z z} 180 do_test selectA-2.18 { 181 execsql { 182 SELECT x,y,z FROM t2 UNION ALL SELECT a,b,c FROM t1 183 ORDER BY c,a,b 184 } 185 } {1 a a 9.9 b B {} C c hello d D abc e e hare m M {} U u 5200000.0 X x -23 Y y mad Z z} 186 do_test selectA-2.19 { 187 execsql { 188 SELECT x,y,z FROM t2 UNION ALL SELECT a,b,c FROM t1 189 ORDER BY c DESC,a,b 190 } 191 } {mad Z z -23 Y y 5200000.0 X x {} U u hare m M abc e e hello d D {} C c 9.9 b B 1 a a} 192 do_test selectA-2.20 { 193 execsql { 194 SELECT x,y,z FROM t2 UNION ALL SELECT a,b,c FROM t1 195 ORDER BY c COLLATE BINARY DESC,a,b 196 } 197 } {mad Z z -23 Y y 5200000.0 X x {} U u abc e e {} C c 1 a a hare m M hello d D 9.9 b B} 198 do_test selectA-2.21 { 199 execsql { 200 SELECT a,b,c FROM t1 UNION SELECT x,y,z FROM t2 201 ORDER BY a,b,c 202 } 203 } {{} C c {} U u -23 Y y 1 a a 9.9 b B 5200000.0 X x hello d D mad Z z abc e e hare m M} 204 do_test selectA-2.22 { 205 execsql { 206 SELECT a,b,c FROM t1 UNION SELECT x,y,z FROM t2 207 ORDER BY a DESC,b,c 208 } 209 } {hare m M abc e e mad Z z hello d D 5200000.0 X x 9.9 b B 1 a a -23 Y y {} C c {} U u} 210 do_test selectA-2.23 { 211 execsql { 212 SELECT a,b,c FROM t1 UNION SELECT x,y,z FROM t2 213 ORDER BY a,c,b 214 } 215 } {{} C c {} U u -23 Y y 1 a a 9.9 b B 5200000.0 X x hello d D mad Z z abc e e hare m M} 216 do_test selectA-2.24 { 217 execsql { 218 SELECT a,b,c FROM t1 UNION SELECT x,y,z FROM t2 219 ORDER BY b,a,c 220 } 221 } {{} C c {} U u 5200000.0 X x -23 Y y mad Z z 1 a a 9.9 b B hello d D abc e e hare m M} 222 do_test selectA-2.25 { 223 execsql { 224 SELECT a,b,c FROM t1 UNION SELECT x,y,z FROM t2 225 ORDER BY b COLLATE NOCASE,a,c 226 } 227 } {1 a a 9.9 b B {} C c hello d D abc e e hare m M {} U u 5200000.0 X x -23 Y y mad Z z} 228 do_test selectA-2.26 { 229 execsql { 230 SELECT a,b,c FROM t1 UNION SELECT x,y,z FROM t2 231 ORDER BY b COLLATE NOCASE DESC,a,c 232 } 233 } {mad Z z -23 Y y 5200000.0 X x {} U u hare m M abc e e hello d D {} C c 9.9 b B 1 a a} 234 do_test selectA-2.27 { 235 execsql { 236 SELECT a,b,c FROM t1 UNION SELECT x,y,z FROM t2 237 ORDER BY c,b,a 238 } 239 } {1 a a 9.9 b B {} C c hello d D abc e e hare m M {} U u 5200000.0 X x -23 Y y mad Z z} 240 do_test selectA-2.28 { 241 execsql { 242 SELECT a,b,c FROM t1 UNION SELECT x,y,z FROM t2 243 ORDER BY c,a,b 244 } 245 } {1 a a 9.9 b B {} C c hello d D abc e e hare m M {} U u 5200000.0 X x -23 Y y mad Z z} 246 do_test selectA-2.29 { 247 execsql { 248 SELECT a,b,c FROM t1 UNION SELECT x,y,z FROM t2 249 ORDER BY c DESC,a,b 250 } 251 } {mad Z z -23 Y y 5200000.0 X x {} U u hare m M abc e e hello d D {} C c 9.9 b B 1 a a} 252 do_test selectA-2.30 { 253 execsql { 254 SELECT a,b,c FROM t1 UNION SELECT x,y,z FROM t2 255 ORDER BY c COLLATE BINARY DESC,a,b 256 } 257 } {mad Z z -23 Y y 5200000.0 X x {} U u abc e e {} C c 1 a a hare m M hello d D 9.9 b B} 258 do_test selectA-2.31 { 259 execsql { 260 SELECT x,y,z FROM t2 UNION SELECT a,b,c FROM t1 261 ORDER BY a,b,c 262 } 263 } {{} C c {} U u -23 Y y 1 a a 9.9 b B 5200000.0 X x hello d D mad Z z abc e e hare m M} 264 do_test selectA-2.32 { 265 execsql { 266 SELECT x,y,z FROM t2 UNION SELECT a,b,c FROM t1 267 ORDER BY a DESC,b,c 268 } 269 } {hare m M abc e e mad Z z hello d D 5200000.0 X x 9.9 b B 1 a a -23 Y y {} C c {} U u} 270 do_test selectA-2.33 { 271 execsql { 272 SELECT x,y,z FROM t2 UNION SELECT a,b,c FROM t1 273 ORDER BY a,c,b 274 } 275 } {{} C c {} U u -23 Y y 1 a a 9.9 b B 5200000.0 X x hello d D mad Z z abc e e hare m M} 276 do_test selectA-2.34 { 277 execsql { 278 SELECT x,y,z FROM t2 UNION SELECT a,b,c FROM t1 279 ORDER BY b,a,c 280 } 281 } {{} C c {} U u 5200000.0 X x -23 Y y mad Z z 1 a a 9.9 b B hello d D abc e e hare m M} 282 do_test selectA-2.35 { 283 execsql { 284 SELECT x,y,z FROM t2 UNION SELECT a,b,c FROM t1 285 ORDER BY y COLLATE NOCASE,x,z 286 } 287 } {1 a a 9.9 b B {} C c hello d D abc e e hare m M {} U u 5200000.0 X x -23 Y y mad Z z} 288 do_test selectA-2.36 { 289 execsql { 290 SELECT x,y,z FROM t2 UNION SELECT a,b,c FROM t1 291 ORDER BY y COLLATE NOCASE DESC,x,z 292 } 293 } {mad Z z -23 Y y 5200000.0 X x {} U u hare m M abc e e hello d D {} C c 9.9 b B 1 a a} 294 do_test selectA-2.37 { 295 execsql { 296 SELECT x,y,z FROM t2 UNION SELECT a,b,c FROM t1 297 ORDER BY c,b,a 298 } 299 } {1 a a 9.9 b B {} C c hello d D abc e e hare m M {} U u 5200000.0 X x -23 Y y mad Z z} 300 do_test selectA-2.38 { 301 execsql { 302 SELECT x,y,z FROM t2 UNION SELECT a,b,c FROM t1 303 ORDER BY c,a,b 304 } 305 } {1 a a 9.9 b B {} C c hello d D abc e e hare m M {} U u 5200000.0 X x -23 Y y mad Z z} 306 do_test selectA-2.39 { 307 execsql { 308 SELECT x,y,z FROM t2 UNION SELECT a,b,c FROM t1 309 ORDER BY c DESC,a,b 310 } 311 } {mad Z z -23 Y y 5200000.0 X x {} U u hare m M abc e e hello d D {} C c 9.9 b B 1 a a} 312 do_test selectA-2.40 { 313 execsql { 314 SELECT x,y,z FROM t2 UNION SELECT a,b,c FROM t1 315 ORDER BY z COLLATE BINARY DESC,x,y 316 } 317 } {mad Z z -23 Y y 5200000.0 X x {} U u abc e e {} C c 1 a a hare m M hello d D 9.9 b B} 318 do_test selectA-2.41 { 319 execsql { 320 SELECT a,b,c FROM t1 EXCEPT SELECT a,b,c FROM t1 WHERE b>='d' 321 ORDER BY a,b,c 322 } 323 } {{} C c 1 a a 9.9 b B} 324 do_test selectA-2.42 { 325 execsql { 326 SELECT a,b,c FROM t1 INTERSECT SELECT a,b,c FROM t1 WHERE b>='d' 327 ORDER BY a,b,c 328 } 329 } {hello d D abc e e} 330 do_test selectA-2.43 { 331 execsql { 332 SELECT a,b,c FROM t1 WHERE b>='d' INTERSECT SELECT a,b,c FROM t1 333 ORDER BY a,b,c 334 } 335 } {hello d D abc e e} 336 do_test selectA-2.44 { 337 execsql { 338 SELECT a,b,c FROM t1 EXCEPT SELECT a,b,c FROM t1 WHERE b<'d' 339 ORDER BY a,b,c 340 } 341 } {hello d D abc e e} 342 do_test selectA-2.45 { 343 execsql { 344 SELECT a,b,c FROM t1 INTERSECT SELECT a,b,c FROM t1 WHERE b<'d' 345 ORDER BY a,b,c 346 } 347 } {{} C c 1 a a 9.9 b B} 348 do_test selectA-2.46 { 349 execsql { 350 SELECT a,b,c FROM t1 WHERE b<'d' INTERSECT SELECT a,b,c FROM t1 351 ORDER BY a,b,c 352 } 353 } {{} C c 1 a a 9.9 b B} 354 do_test selectA-2.47 { 355 execsql { 356 SELECT a,b,c FROM t1 EXCEPT SELECT a,b,c FROM t1 WHERE b>='d' 357 ORDER BY a DESC 358 } 359 } {9.9 b B 1 a a {} C c} 360 do_test selectA-2.48 { 361 execsql { 362 SELECT a,b,c FROM t1 INTERSECT SELECT a,b,c FROM t1 WHERE b>='d' 363 ORDER BY a DESC 364 } 365 } {abc e e hello d D} 366 do_test selectA-2.49 { 367 execsql { 368 SELECT a,b,c FROM t1 WHERE b>='d' INTERSECT SELECT a,b,c FROM t1 369 ORDER BY a DESC 370 } 371 } {abc e e hello d D} 372 do_test selectA-2.50 { 373 execsql { 374 SELECT a,b,c FROM t1 EXCEPT SELECT a,b,c FROM t1 WHERE b<'d' 375 ORDER BY a DESC 376 } 377 } {abc e e hello d D} 378 do_test selectA-2.51 { 379 execsql { 380 SELECT a,b,c FROM t1 INTERSECT SELECT a,b,c FROM t1 WHERE b<'d' 381 ORDER BY a DESC 382 } 383 } {9.9 b B 1 a a {} C c} 384 do_test selectA-2.52 { 385 execsql { 386 SELECT a,b,c FROM t1 WHERE b<'d' INTERSECT SELECT a,b,c FROM t1 387 ORDER BY a DESC 388 } 389 } {9.9 b B 1 a a {} C c} 390 do_test selectA-2.53 { 391 execsql { 392 SELECT a,b,c FROM t1 EXCEPT SELECT a,b,c FROM t1 WHERE b>='d' 393 ORDER BY b, a DESC 394 } 395 } {{} C c 1 a a 9.9 b B} 396 do_test selectA-2.54 { 397 execsql { 398 SELECT a,b,c FROM t1 INTERSECT SELECT a,b,c FROM t1 WHERE b>='d' 399 ORDER BY b 400 } 401 } {hello d D abc e e} 402 do_test selectA-2.55 { 403 execsql { 404 SELECT a,b,c FROM t1 WHERE b>='d' INTERSECT SELECT a,b,c FROM t1 405 ORDER BY b DESC, c 406 } 407 } {abc e e hello d D} 408 do_test selectA-2.56 { 409 execsql { 410 SELECT a,b,c FROM t1 EXCEPT SELECT a,b,c FROM t1 WHERE b<'d' 411 ORDER BY b, c DESC, a 412 } 413 } {hello d D abc e e} 414 do_test selectA-2.57 { 415 execsql { 416 SELECT a,b,c FROM t1 INTERSECT SELECT a,b,c FROM t1 WHERE b<'d' 417 ORDER BY b COLLATE NOCASE 418 } 419 } {1 a a 9.9 b B {} C c} 420 do_test selectA-2.58 { 421 execsql { 422 SELECT a,b,c FROM t1 WHERE b<'d' INTERSECT SELECT a,b,c FROM t1 423 ORDER BY b 424 } 425 } {{} C c 1 a a 9.9 b B} 426 do_test selectA-2.59 { 427 execsql { 428 SELECT a,b,c FROM t1 EXCEPT SELECT a,b,c FROM t1 WHERE b>='d' 429 ORDER BY c, a DESC 430 } 431 } {1 a a 9.9 b B {} C c} 432 do_test selectA-2.60 { 433 execsql { 434 SELECT a,b,c FROM t1 INTERSECT SELECT a,b,c FROM t1 WHERE b>='d' 435 ORDER BY c 436 } 437 } {hello d D abc e e} 438 do_test selectA-2.61 { 439 execsql { 440 SELECT a,b,c FROM t1 WHERE b>='d' INTERSECT SELECT a,b,c FROM t1 441 ORDER BY c COLLATE BINARY, b DESC, c, a, b, c, a, b, c 442 } 443 } {hello d D abc e e} 444 do_test selectA-2.62 { 445 execsql { 446 SELECT a,b,c FROM t1 EXCEPT SELECT a,b,c FROM t1 WHERE b<'d' 447 ORDER BY c DESC, a 448 } 449 } {abc e e hello d D} 450 do_test selectA-2.63 { 451 execsql { 452 SELECT a,b,c FROM t1 INTERSECT SELECT a,b,c FROM t1 WHERE b<'d' 453 ORDER BY c COLLATE NOCASE 454 } 455 } {1 a a 9.9 b B {} C c} 456 do_test selectA-2.64 { 457 execsql { 458 SELECT a,b,c FROM t1 WHERE b<'d' INTERSECT SELECT a,b,c FROM t1 459 ORDER BY c 460 } 461 } {1 a a 9.9 b B {} C c} 462 do_test selectA-2.65 { 463 execsql { 464 SELECT a,b,c FROM t3 INTERSECT SELECT a,b,c FROM t1 WHERE b<'d' 465 ORDER BY c COLLATE NOCASE 466 } 467 } {1 a a 9.9 b B {} C c} 468 do_test selectA-2.66 { 469 execsql { 470 SELECT a,b,c FROM t1 WHERE b<'d' INTERSECT SELECT a,b,c FROM t3 471 ORDER BY c 472 } 473 } {1 a a 9.9 b B {} C c} 474 do_test selectA-2.67 { 475 execsql { 476 SELECT a,b,c FROM t1 EXCEPT SELECT a,b,c FROM t3 WHERE b<'d' 477 ORDER BY c DESC, a 478 } 479 } {abc e e hello d D} 480 do_test selectA-2.68 { 481 execsql { 482 SELECT a,b,c FROM t1 EXCEPT SELECT a,b,c FROM t1 WHERE b<'d' 483 INTERSECT SELECT a,b,c FROM t3 484 EXCEPT SELECT b,c,a FROM t3 485 ORDER BY c DESC, a 486 } 487 } {abc e e hello d D} 488 do_test selectA-2.69 { 489 execsql { 490 SELECT a,b,c FROM t1 INTERSECT SELECT a,b,c FROM t1 WHERE b<'d' 491 INTERSECT SELECT a,b,c FROM t3 492 EXCEPT SELECT b,c,a FROM t3 493 ORDER BY c COLLATE NOCASE 494 } 495 } {1 a a 9.9 b B {} C c} 496 do_test selectA-2.70 { 497 execsql { 498 SELECT a,b,c FROM t1 WHERE b<'d' INTERSECT SELECT a,b,c FROM t1 499 INTERSECT SELECT a,b,c FROM t3 500 EXCEPT SELECT b,c,a FROM t3 501 ORDER BY c 502 } 503 } {1 a a 9.9 b B {} C c} 504 do_test selectA-2.71 { 505 execsql { 506 SELECT a,b,c FROM t1 WHERE b<'d' 507 INTERSECT SELECT a,b,c FROM t1 508 INTERSECT SELECT a,b,c FROM t3 509 EXCEPT SELECT b,c,a FROM t3 510 INTERSECT SELECT a,b,c FROM t1 511 EXCEPT SELECT x,y,z FROM t2 512 INTERSECT SELECT a,b,c FROM t3 513 EXCEPT SELECT y,x,z FROM t2 514 INTERSECT SELECT a,b,c FROM t1 515 EXCEPT SELECT c,b,a FROM t3 516 ORDER BY c 517 } 518 } {1 a a 9.9 b B {} C c} 519 do_test selectA-2.72 { 520 execsql { 521 SELECT a,b,c FROM t3 UNION SELECT x,y,z FROM t2 522 ORDER BY a,b,c 523 } 524 } {{} C c {} U u -23 Y y 1 a a 9.9 b B 5200000.0 X x hello d D mad Z z abc e e hare m M} 525 do_test selectA-2.73 { 526 execsql { 527 SELECT a,b,c FROM t3 UNION SELECT x,y,z FROM t2 528 ORDER BY a DESC,b,c 529 } 530 } {hare m M abc e e mad Z z hello d D 5200000.0 X x 9.9 b B 1 a a -23 Y y {} C c {} U u} 531 do_test selectA-2.74 { 532 execsql { 533 SELECT a,b,c FROM t3 UNION SELECT x,y,z FROM t2 534 ORDER BY a,c,b 535 } 536 } {{} C c {} U u -23 Y y 1 a a 9.9 b B 5200000.0 X x hello d D mad Z z abc e e hare m M} 537 do_test selectA-2.75 { 538 execsql { 539 SELECT a,b,c FROM t3 UNION SELECT x,y,z FROM t2 540 ORDER BY b,a,c 541 } 542 } {{} C c {} U u 5200000.0 X x -23 Y y mad Z z 1 a a 9.9 b B hello d D abc e e hare m M} 543 do_test selectA-2.76 { 544 execsql { 545 SELECT a,b,c FROM t3 UNION SELECT x,y,z FROM t2 546 ORDER BY b COLLATE NOCASE,a,c 547 } 548 } {1 a a 9.9 b B {} C c hello d D abc e e hare m M {} U u 5200000.0 X x -23 Y y mad Z z} 549 do_test selectA-2.77 { 550 execsql { 551 SELECT a,b,c FROM t3 UNION SELECT x,y,z FROM t2 552 ORDER BY b COLLATE NOCASE DESC,a,c 553 } 554 } {mad Z z -23 Y y 5200000.0 X x {} U u hare m M abc e e hello d D {} C c 9.9 b B 1 a a} 555 do_test selectA-2.78 { 556 execsql { 557 SELECT a,b,c FROM t3 UNION SELECT x,y,z FROM t2 558 ORDER BY c,b,a 559 } 560 } {1 a a 9.9 b B {} C c hello d D abc e e hare m M {} U u 5200000.0 X x -23 Y y mad Z z} 561 do_test selectA-2.79 { 562 execsql { 563 SELECT a,b,c FROM t3 UNION SELECT x,y,z FROM t2 564 ORDER BY c,a,b 565 } 566 } {1 a a 9.9 b B {} C c hello d D abc e e hare m M {} U u 5200000.0 X x -23 Y y mad Z z} 567 do_test selectA-2.80 { 568 execsql { 569 SELECT a,b,c FROM t3 UNION SELECT x,y,z FROM t2 570 ORDER BY c DESC,a,b 571 } 572 } {mad Z z -23 Y y 5200000.0 X x {} U u hare m M abc e e hello d D {} C c 9.9 b B 1 a a} 573 do_test selectA-2.81 { 574 execsql { 575 SELECT a,b,c FROM t3 UNION SELECT x,y,z FROM t2 576 ORDER BY c COLLATE BINARY DESC,a,b 577 } 578 } {mad Z z -23 Y y 5200000.0 X x {} U u abc e e {} C c 1 a a hare m M hello d D 9.9 b B} 579 do_test selectA-2.82 { 580 execsql { 581 SELECT x,y,z FROM t2 UNION SELECT a,b,c FROM t3 582 ORDER BY a,b,c 583 } 584 } {{} C c {} U u -23 Y y 1 a a 9.9 b B 5200000.0 X x hello d D mad Z z abc e e hare m M} 585 do_test selectA-2.83 { 586 execsql { 587 SELECT x,y,z FROM t2 UNION SELECT a,b,c FROM t3 588 ORDER BY a DESC,b,c 589 } 590 } {hare m M abc e e mad Z z hello d D 5200000.0 X x 9.9 b B 1 a a -23 Y y {} C c {} U u} 591 do_test selectA-2.84 { 592 execsql { 593 SELECT x,y,z FROM t2 UNION SELECT a,b,c FROM t3 594 ORDER BY a,c,b 595 } 596 } {{} C c {} U u -23 Y y 1 a a 9.9 b B 5200000.0 X x hello d D mad Z z abc e e hare m M} 597 do_test selectA-2.85 { 598 execsql { 599 SELECT x,y,z FROM t2 UNION SELECT a,b,c FROM t3 600 ORDER BY b,a,c 601 } 602 } {{} C c {} U u 5200000.0 X x -23 Y y mad Z z 1 a a 9.9 b B hello d D abc e e hare m M} 603 do_test selectA-2.86 { 604 execsql { 605 SELECT x,y,z FROM t2 UNION SELECT a,b,c FROM t3 606 ORDER BY y COLLATE NOCASE,x,z 607 } 608 } {1 a a 9.9 b B {} C c hello d D abc e e hare m M {} U u 5200000.0 X x -23 Y y mad Z z} 609 do_test selectA-2.87 { 610 execsql { 611 SELECT x,y,z FROM t2 UNION SELECT a,b,c FROM t3 612 ORDER BY y COLLATE NOCASE DESC,x,z 613 } 614 } {mad Z z -23 Y y 5200000.0 X x {} U u hare m M abc e e hello d D {} C c 9.9 b B 1 a a} 615 do_test selectA-2.88 { 616 execsql { 617 SELECT x,y,z FROM t2 UNION SELECT a,b,c FROM t3 618 ORDER BY c,b,a 619 } 620 } {1 a a 9.9 b B {} C c hello d D abc e e hare m M {} U u 5200000.0 X x -23 Y y mad Z z} 621 do_test selectA-2.89 { 622 execsql { 623 SELECT x,y,z FROM t2 UNION SELECT a,b,c FROM t3 624 ORDER BY c,a,b 625 } 626 } {1 a a 9.9 b B {} C c hello d D abc e e hare m M {} U u 5200000.0 X x -23 Y y mad Z z} 627 do_test selectA-2.90 { 628 execsql { 629 SELECT x,y,z FROM t2 UNION SELECT a,b,c FROM t3 630 ORDER BY c DESC,a,b 631 } 632 } {mad Z z -23 Y y 5200000.0 X x {} U u hare m M abc e e hello d D {} C c 9.9 b B 1 a a} 633 do_test selectA-2.91 { 634 execsql { 635 SELECT x,y,z FROM t2 UNION SELECT a,b,c FROM t3 636 ORDER BY z COLLATE BINARY DESC,x,y 637 } 638 } {mad Z z -23 Y y 5200000.0 X x {} U u abc e e {} C c 1 a a hare m M hello d D 9.9 b B} 639 do_test selectA-2.92 { 640 execsql { 641 SELECT x,y,z FROM t2 642 INTERSECT SELECT a,b,c FROM t3 643 EXCEPT SELECT c,b,a FROM t1 644 UNION SELECT a,b,c FROM t3 645 INTERSECT SELECT a,b,c FROM t3 646 EXCEPT SELECT c,b,a FROM t1 647 UNION SELECT a,b,c FROM t3 648 ORDER BY y COLLATE NOCASE DESC,x,z 649 } 650 } {mad Z z -23 Y y 5200000.0 X x {} U u hare m M abc e e hello d D {} C c 9.9 b B 1 a a} 651 do_test selectA-2.93 { 652 execsql { 653 SELECT upper((SELECT c FROM t1 UNION SELECT z FROM t2 ORDER BY 1)); 654 } 655 } {A} 656 do_test selectA-2.94 { 657 execsql { 658 SELECT lower((SELECT c FROM t1 UNION ALL SELECT z FROM t2 ORDER BY 1)); 659 } 660 } {a} 661 do_test selectA-2.95 { 662 execsql { 663 SELECT lower((SELECT c FROM t1 INTERSECT SELECT z FROM t2 ORDER BY 1)); 664 } 665 } {{}} 666 do_test selectA-2.96 { 667 execsql { 668 SELECT lower((SELECT z FROM t2 EXCEPT SELECT c FROM t1 ORDER BY 1)); 669 } 670 } {m} 671 672 673 do_test selectA-3.0 { 674 execsql { 675 CREATE UNIQUE INDEX t1a ON t1(a); 676 CREATE UNIQUE INDEX t1b ON t1(b); 677 CREATE UNIQUE INDEX t1c ON t1(c); 678 CREATE UNIQUE INDEX t2x ON t2(x); 679 CREATE UNIQUE INDEX t2y ON t2(y); 680 CREATE UNIQUE INDEX t2z ON t2(z); 681 SELECT name FROM sqlite_master WHERE type='index' 682 } 683 } {t1a t1b t1c t2x t2y t2z} 684 do_test selectA-3.1 { 685 execsql { 686 SELECT a,b,c FROM t1 UNION ALL SELECT x,y,z FROM t2 687 ORDER BY a,b,c 688 } 689 } {{} C c {} U u -23 Y y 1 a a 9.9 b B 5200000.0 X x hello d D mad Z z abc e e hare m M} 690 do_test selectA-3.1.1 { # Ticket #3314 691 execsql { 692 SELECT t1.a,b,t1.c FROM t1 UNION ALL SELECT x,y,z FROM t2 693 ORDER BY a,t1.b,t1.c 694 } 695 } {{} C c {} U u -23 Y y 1 a a 9.9 b B 5200000.0 X x hello d D mad Z z abc e e hare m M} 696 do_test selectA-3.2 { 697 execsql { 698 SELECT a,b,c FROM t1 UNION ALL SELECT x,y,z FROM t2 699 ORDER BY a DESC,b,c 700 } 701 } {hare m M abc e e mad Z z hello d D 5200000.0 X x 9.9 b B 1 a a -23 Y y {} C c {} U u} 702 do_test selectA-3.3 { 703 execsql { 704 SELECT a,b,c FROM t1 UNION ALL SELECT x,y,z FROM t2 705 ORDER BY a,c,b 706 } 707 } {{} C c {} U u -23 Y y 1 a a 9.9 b B 5200000.0 X x hello d D mad Z z abc e e hare m M} 708 do_test selectA-3.4 { 709 execsql { 710 SELECT a,b,c FROM t1 UNION ALL SELECT x,y,z FROM t2 711 ORDER BY b,a,c 712 } 713 } {{} C c {} U u 5200000.0 X x -23 Y y mad Z z 1 a a 9.9 b B hello d D abc e e hare m M} 714 do_test selectA-3.5 { 715 execsql { 716 SELECT a,b,c FROM t1 UNION ALL SELECT x,y,z FROM t2 717 ORDER BY b COLLATE NOCASE,a,c 718 } 719 } {1 a a 9.9 b B {} C c hello d D abc e e hare m M {} U u 5200000.0 X x -23 Y y mad Z z} 720 do_test selectA-3.6 { 721 execsql { 722 SELECT a,b,c FROM t1 UNION ALL SELECT x,y,z FROM t2 723 ORDER BY b COLLATE NOCASE DESC,a,c 724 } 725 } {mad Z z -23 Y y 5200000.0 X x {} U u hare m M abc e e hello d D {} C c 9.9 b B 1 a a} 726 do_test selectA-3.7 { 727 execsql { 728 SELECT a,b,c FROM t1 UNION ALL SELECT x,y,z FROM t2 729 ORDER BY c,b,a 730 } 731 } {1 a a 9.9 b B {} C c hello d D abc e e hare m M {} U u 5200000.0 X x -23 Y y mad Z z} 732 do_test selectA-3.8 { 733 execsql { 734 SELECT a,b,c FROM t1 UNION ALL SELECT x,y,z FROM t2 735 ORDER BY c,a,b 736 } 737 } {1 a a 9.9 b B {} C c hello d D abc e e hare m M {} U u 5200000.0 X x -23 Y y mad Z z} 738 do_test selectA-3.9 { 739 execsql { 740 SELECT a,b,c FROM t1 UNION ALL SELECT x,y,z FROM t2 741 ORDER BY c DESC,a,b 742 } 743 } {mad Z z -23 Y y 5200000.0 X x {} U u hare m M abc e e hello d D {} C c 9.9 b B 1 a a} 744 do_test selectA-3.10 { 745 execsql { 746 SELECT a,b,c FROM t1 UNION ALL SELECT x,y,z FROM t2 747 ORDER BY c COLLATE BINARY DESC,a,b 748 } 749 } {mad Z z -23 Y y 5200000.0 X x {} U u abc e e {} C c 1 a a hare m M hello d D 9.9 b B} 750 do_test selectA-3.11 { 751 execsql { 752 SELECT x,y,z FROM t2 UNION ALL SELECT a,b,c FROM t1 753 ORDER BY a,b,c 754 } 755 } {{} C c {} U u -23 Y y 1 a a 9.9 b B 5200000.0 X x hello d D mad Z z abc e e hare m M} 756 do_test selectA-3.12 { 757 execsql { 758 SELECT x,y,z FROM t2 UNION ALL SELECT a,b,c FROM t1 759 ORDER BY a DESC,b,c 760 } 761 } {hare m M abc e e mad Z z hello d D 5200000.0 X x 9.9 b B 1 a a -23 Y y {} C c {} U u} 762 do_test selectA-3.13 { 763 execsql { 764 SELECT x,y,z FROM t2 UNION ALL SELECT a,b,c FROM t1 765 ORDER BY a,c,b 766 } 767 } {{} C c {} U u -23 Y y 1 a a 9.9 b B 5200000.0 X x hello d D mad Z z abc e e hare m M} 768 do_test selectA-3.14 { 769 execsql { 770 SELECT x,y,z FROM t2 UNION ALL SELECT a,b,c FROM t1 771 ORDER BY b,a,c 772 } 773 } {{} C c {} U u 5200000.0 X x -23 Y y mad Z z 1 a a 9.9 b B hello d D abc e e hare m M} 774 do_test selectA-3.15 { 775 execsql { 776 SELECT x,y,z FROM t2 UNION ALL SELECT a,b,c FROM t1 777 ORDER BY b COLLATE NOCASE,a,c 778 } 779 } {1 a a 9.9 b B {} C c hello d D abc e e hare m M {} U u 5200000.0 X x -23 Y y mad Z z} 780 do_test selectA-3.16 { 781 execsql { 782 SELECT x,y,z FROM t2 UNION ALL SELECT a,b,c FROM t1 783 ORDER BY b COLLATE NOCASE DESC,a,c 784 } 785 } {mad Z z -23 Y y 5200000.0 X x {} U u hare m M abc e e hello d D {} C c 9.9 b B 1 a a} 786 do_test selectA-3.17 { 787 execsql { 788 SELECT x,y,z FROM t2 UNION ALL SELECT a,b,c FROM t1 789 ORDER BY c,b,a 790 } 791 } {1 a a 9.9 b B {} C c hello d D abc e e hare m M {} U u 5200000.0 X x -23 Y y mad Z z} 792 do_test selectA-3.18 { 793 execsql { 794 SELECT x,y,z FROM t2 UNION ALL SELECT a,b,c FROM t1 795 ORDER BY c,a,b 796 } 797 } {1 a a 9.9 b B {} C c hello d D abc e e hare m M {} U u 5200000.0 X x -23 Y y mad Z z} 798 do_test selectA-3.19 { 799 execsql { 800 SELECT x,y,z FROM t2 UNION ALL SELECT a,b,c FROM t1 801 ORDER BY c DESC,a,b 802 } 803 } {mad Z z -23 Y y 5200000.0 X x {} U u hare m M abc e e hello d D {} C c 9.9 b B 1 a a} 804 do_test selectA-3.20 { 805 execsql { 806 SELECT x,y,z FROM t2 UNION ALL SELECT a,b,c FROM t1 807 ORDER BY c COLLATE BINARY DESC,a,b 808 } 809 } {mad Z z -23 Y y 5200000.0 X x {} U u abc e e {} C c 1 a a hare m M hello d D 9.9 b B} 810 do_test selectA-3.21 { 811 execsql { 812 SELECT a,b,c FROM t1 UNION SELECT x,y,z FROM t2 813 ORDER BY a,b,c 814 } 815 } {{} C c {} U u -23 Y y 1 a a 9.9 b B 5200000.0 X x hello d D mad Z z abc e e hare m M} 816 do_test selectA-3.22 { 817 execsql { 818 SELECT a,b,c FROM t1 UNION SELECT x,y,z FROM t2 819 ORDER BY a DESC,b,c 820 } 821 } {hare m M abc e e mad Z z hello d D 5200000.0 X x 9.9 b B 1 a a -23 Y y {} C c {} U u} 822 do_test selectA-3.23 { 823 execsql { 824 SELECT a,b,c FROM t1 UNION SELECT x,y,z FROM t2 825 ORDER BY a,c,b 826 } 827 } {{} C c {} U u -23 Y y 1 a a 9.9 b B 5200000.0 X x hello d D mad Z z abc e e hare m M} 828 do_test selectA-3.24 { 829 execsql { 830 SELECT a,b,c FROM t1 UNION SELECT x,y,z FROM t2 831 ORDER BY b,a,c 832 } 833 } {{} C c {} U u 5200000.0 X x -23 Y y mad Z z 1 a a 9.9 b B hello d D abc e e hare m M} 834 do_test selectA-3.25 { 835 execsql { 836 SELECT a,b,c FROM t1 UNION SELECT x,y,z FROM t2 837 ORDER BY b COLLATE NOCASE,a,c 838 } 839 } {1 a a 9.9 b B {} C c hello d D abc e e hare m M {} U u 5200000.0 X x -23 Y y mad Z z} 840 do_test selectA-3.26 { 841 execsql { 842 SELECT a,b,c FROM t1 UNION SELECT x,y,z FROM t2 843 ORDER BY b COLLATE NOCASE DESC,a,c 844 } 845 } {mad Z z -23 Y y 5200000.0 X x {} U u hare m M abc e e hello d D {} C c 9.9 b B 1 a a} 846 do_test selectA-3.27 { 847 execsql { 848 SELECT a,b,c FROM t1 UNION SELECT x,y,z FROM t2 849 ORDER BY c,b,a 850 } 851 } {1 a a 9.9 b B {} C c hello d D abc e e hare m M {} U u 5200000.0 X x -23 Y y mad Z z} 852 do_test selectA-3.28 { 853 execsql { 854 SELECT a,b,c FROM t1 UNION SELECT x,y,z FROM t2 855 ORDER BY c,a,b 856 } 857 } {1 a a 9.9 b B {} C c hello d D abc e e hare m M {} U u 5200000.0 X x -23 Y y mad Z z} 858 do_test selectA-3.29 { 859 execsql { 860 SELECT a,b,c FROM t1 UNION SELECT x,y,z FROM t2 861 ORDER BY c DESC,a,b 862 } 863 } {mad Z z -23 Y y 5200000.0 X x {} U u hare m M abc e e hello d D {} C c 9.9 b B 1 a a} 864 do_test selectA-3.30 { 865 execsql { 866 SELECT a,b,c FROM t1 UNION SELECT x,y,z FROM t2 867 ORDER BY c COLLATE BINARY DESC,a,b 868 } 869 } {mad Z z -23 Y y 5200000.0 X x {} U u abc e e {} C c 1 a a hare m M hello d D 9.9 b B} 870 do_test selectA-3.31 { 871 execsql { 872 SELECT x,y,z FROM t2 UNION SELECT a,b,c FROM t1 873 ORDER BY a,b,c 874 } 875 } {{} C c {} U u -23 Y y 1 a a 9.9 b B 5200000.0 X x hello d D mad Z z abc e e hare m M} 876 do_test selectA-3.32 { 877 execsql { 878 SELECT x,y,z FROM t2 UNION SELECT a,b,c FROM t1 879 ORDER BY a DESC,b,c 880 } 881 } {hare m M abc e e mad Z z hello d D 5200000.0 X x 9.9 b B 1 a a -23 Y y {} C c {} U u} 882 do_test selectA-3.33 { 883 execsql { 884 SELECT x,y,z FROM t2 UNION SELECT a,b,c FROM t1 885 ORDER BY a,c,b 886 } 887 } {{} C c {} U u -23 Y y 1 a a 9.9 b B 5200000.0 X x hello d D mad Z z abc e e hare m M} 888 do_test selectA-3.34 { 889 execsql { 890 SELECT x,y,z FROM t2 UNION SELECT a,b,c FROM t1 891 ORDER BY b,a,c 892 } 893 } {{} C c {} U u 5200000.0 X x -23 Y y mad Z z 1 a a 9.9 b B hello d D abc e e hare m M} 894 do_test selectA-3.35 { 895 execsql { 896 SELECT x,y,z FROM t2 UNION SELECT a,b,c FROM t1 897 ORDER BY y COLLATE NOCASE,x,z 898 } 899 } {1 a a 9.9 b B {} C c hello d D abc e e hare m M {} U u 5200000.0 X x -23 Y y mad Z z} 900 do_test selectA-3.36 { 901 execsql { 902 SELECT x,y,z FROM t2 UNION SELECT a,b,c FROM t1 903 ORDER BY y COLLATE NOCASE DESC,x,z 904 } 905 } {mad Z z -23 Y y 5200000.0 X x {} U u hare m M abc e e hello d D {} C c 9.9 b B 1 a a} 906 do_test selectA-3.37 { 907 execsql { 908 SELECT x,y,z FROM t2 UNION SELECT a,b,c FROM t1 909 ORDER BY c,b,a 910 } 911 } {1 a a 9.9 b B {} C c hello d D abc e e hare m M {} U u 5200000.0 X x -23 Y y mad Z z} 912 do_test selectA-3.38 { 913 execsql { 914 SELECT x,y,z FROM t2 UNION SELECT a,b,c FROM t1 915 ORDER BY c,a,b 916 } 917 } {1 a a 9.9 b B {} C c hello d D abc e e hare m M {} U u 5200000.0 X x -23 Y y mad Z z} 918 do_test selectA-3.39 { 919 execsql { 920 SELECT x,y,z FROM t2 UNION SELECT a,b,c FROM t1 921 ORDER BY c DESC,a,b 922 } 923 } {mad Z z -23 Y y 5200000.0 X x {} U u hare m M abc e e hello d D {} C c 9.9 b B 1 a a} 924 do_test selectA-3.40 { 925 execsql { 926 SELECT x,y,z FROM t2 UNION SELECT a,b,c FROM t1 927 ORDER BY z COLLATE BINARY DESC,x,y 928 } 929 } {mad Z z -23 Y y 5200000.0 X x {} U u abc e e {} C c 1 a a hare m M hello d D 9.9 b B} 930 do_test selectA-3.41 { 931 execsql { 932 SELECT a,b,c FROM t1 EXCEPT SELECT a,b,c FROM t1 WHERE b>='d' 933 ORDER BY a,b,c 934 } 935 } {{} C c 1 a a 9.9 b B} 936 do_test selectA-3.42 { 937 execsql { 938 SELECT a,b,c FROM t1 INTERSECT SELECT a,b,c FROM t1 WHERE b>='d' 939 ORDER BY a,b,c 940 } 941 } {hello d D abc e e} 942 do_test selectA-3.43 { 943 execsql { 944 SELECT a,b,c FROM t1 WHERE b>='d' INTERSECT SELECT a,b,c FROM t1 945 ORDER BY a,b,c 946 } 947 } {hello d D abc e e} 948 do_test selectA-3.44 { 949 execsql { 950 SELECT a,b,c FROM t1 EXCEPT SELECT a,b,c FROM t1 WHERE b<'d' 951 ORDER BY a,b,c 952 } 953 } {hello d D abc e e} 954 do_test selectA-3.45 { 955 execsql { 956 SELECT a,b,c FROM t1 INTERSECT SELECT a,b,c FROM t1 WHERE b<'d' 957 ORDER BY a,b,c 958 } 959 } {{} C c 1 a a 9.9 b B} 960 do_test selectA-3.46 { 961 execsql { 962 SELECT a,b,c FROM t1 WHERE b<'d' INTERSECT SELECT a,b,c FROM t1 963 ORDER BY a,b,c 964 } 965 } {{} C c 1 a a 9.9 b B} 966 do_test selectA-3.47 { 967 execsql { 968 SELECT a,b,c FROM t1 EXCEPT SELECT a,b,c FROM t1 WHERE b>='d' 969 ORDER BY a DESC 970 } 971 } {9.9 b B 1 a a {} C c} 972 do_test selectA-3.48 { 973 execsql { 974 SELECT a,b,c FROM t1 INTERSECT SELECT a,b,c FROM t1 WHERE b>='d' 975 ORDER BY a DESC 976 } 977 } {abc e e hello d D} 978 do_test selectA-3.49 { 979 execsql { 980 SELECT a,b,c FROM t1 WHERE b>='d' INTERSECT SELECT a,b,c FROM t1 981 ORDER BY a DESC 982 } 983 } {abc e e hello d D} 984 do_test selectA-3.50 { 985 execsql { 986 SELECT a,b,c FROM t1 EXCEPT SELECT a,b,c FROM t1 WHERE b<'d' 987 ORDER BY a DESC 988 } 989 } {abc e e hello d D} 990 do_test selectA-3.51 { 991 execsql { 992 SELECT a,b,c FROM t1 INTERSECT SELECT a,b,c FROM t1 WHERE b<'d' 993 ORDER BY a DESC 994 } 995 } {9.9 b B 1 a a {} C c} 996 do_test selectA-3.52 { 997 execsql { 998 SELECT a,b,c FROM t1 WHERE b<'d' INTERSECT SELECT a,b,c FROM t1 999 ORDER BY a DESC 1000 } 1001 } {9.9 b B 1 a a {} C c} 1002 do_test selectA-3.53 { 1003 execsql { 1004 SELECT a,b,c FROM t1 EXCEPT SELECT a,b,c FROM t1 WHERE b>='d' 1005 ORDER BY b, a DESC 1006 } 1007 } {{} C c 1 a a 9.9 b B} 1008 do_test selectA-3.54 { 1009 execsql { 1010 SELECT a,b,c FROM t1 INTERSECT SELECT a,b,c FROM t1 WHERE b>='d' 1011 ORDER BY b 1012 } 1013 } {hello d D abc e e} 1014 do_test selectA-3.55 { 1015 execsql { 1016 SELECT a,b,c FROM t1 WHERE b>='d' INTERSECT SELECT a,b,c FROM t1 1017 ORDER BY b DESC, c 1018 } 1019 } {abc e e hello d D} 1020 do_test selectA-3.56 { 1021 execsql { 1022 SELECT a,b,c FROM t1 EXCEPT SELECT a,b,c FROM t1 WHERE b<'d' 1023 ORDER BY b, c DESC, a 1024 } 1025 } {hello d D abc e e} 1026 do_test selectA-3.57 { 1027 execsql { 1028 SELECT a,b,c FROM t1 INTERSECT SELECT a,b,c FROM t1 WHERE b<'d' 1029 ORDER BY b COLLATE NOCASE 1030 } 1031 } {1 a a 9.9 b B {} C c} 1032 do_test selectA-3.58 { 1033 execsql { 1034 SELECT a,b,c FROM t1 WHERE b<'d' INTERSECT SELECT a,b,c FROM t1 1035 ORDER BY b 1036 } 1037 } {{} C c 1 a a 9.9 b B} 1038 do_test selectA-3.59 { 1039 execsql { 1040 SELECT a,b,c FROM t1 EXCEPT SELECT a,b,c FROM t1 WHERE b>='d' 1041 ORDER BY c, a DESC 1042 } 1043 } {1 a a 9.9 b B {} C c} 1044 do_test selectA-3.60 { 1045 execsql { 1046 SELECT a,b,c FROM t1 INTERSECT SELECT a,b,c FROM t1 WHERE b>='d' 1047 ORDER BY c 1048 } 1049 } {hello d D abc e e} 1050 do_test selectA-3.61 { 1051 execsql { 1052 SELECT a,b,c FROM t1 WHERE b>='d' INTERSECT SELECT a,b,c FROM t1 1053 ORDER BY c COLLATE BINARY, b DESC, c, a, b, c, a, b, c 1054 } 1055 } {hello d D abc e e} 1056 do_test selectA-3.62 { 1057 execsql { 1058 SELECT a,b,c FROM t1 EXCEPT SELECT a,b,c FROM t1 WHERE b<'d' 1059 ORDER BY c DESC, a 1060 } 1061 } {abc e e hello d D} 1062 do_test selectA-3.63 { 1063 execsql { 1064 SELECT a,b,c FROM t1 INTERSECT SELECT a,b,c FROM t1 WHERE b<'d' 1065 ORDER BY c COLLATE NOCASE 1066 } 1067 } {1 a a 9.9 b B {} C c} 1068 do_test selectA-3.64 { 1069 execsql { 1070 SELECT a,b,c FROM t1 WHERE b<'d' INTERSECT SELECT a,b,c FROM t1 1071 ORDER BY c 1072 } 1073 } {1 a a 9.9 b B {} C c} 1074 do_test selectA-3.65 { 1075 execsql { 1076 SELECT a,b,c FROM t3 INTERSECT SELECT a,b,c FROM t1 WHERE b<'d' 1077 ORDER BY c COLLATE NOCASE 1078 } 1079 } {1 a a 9.9 b B {} C c} 1080 do_test selectA-3.66 { 1081 execsql { 1082 SELECT a,b,c FROM t1 WHERE b<'d' INTERSECT SELECT a,b,c FROM t3 1083 ORDER BY c 1084 } 1085 } {1 a a 9.9 b B {} C c} 1086 do_test selectA-3.67 { 1087 execsql { 1088 SELECT a,b,c FROM t1 EXCEPT SELECT a,b,c FROM t3 WHERE b<'d' 1089 ORDER BY c DESC, a 1090 } 1091 } {abc e e hello d D} 1092 do_test selectA-3.68 { 1093 execsql { 1094 SELECT a,b,c FROM t1 EXCEPT SELECT a,b,c FROM t1 WHERE b<'d' 1095 INTERSECT SELECT a,b,c FROM t3 1096 EXCEPT SELECT b,c,a FROM t3 1097 ORDER BY c DESC, a 1098 } 1099 } {abc e e hello d D} 1100 do_test selectA-3.69 { 1101 execsql { 1102 SELECT a,b,c FROM t1 INTERSECT SELECT a,b,c FROM t1 WHERE b<'d' 1103 INTERSECT SELECT a,b,c FROM t3 1104 EXCEPT SELECT b,c,a FROM t3 1105 ORDER BY c COLLATE NOCASE 1106 } 1107 } {1 a a 9.9 b B {} C c} 1108 do_test selectA-3.70 { 1109 execsql { 1110 SELECT a,b,c FROM t1 WHERE b<'d' INTERSECT SELECT a,b,c FROM t1 1111 INTERSECT SELECT a,b,c FROM t3 1112 EXCEPT SELECT b,c,a FROM t3 1113 ORDER BY c 1114 } 1115 } {1 a a 9.9 b B {} C c} 1116 do_test selectA-3.71 { 1117 execsql { 1118 SELECT a,b,c FROM t1 WHERE b<'d' 1119 INTERSECT SELECT a,b,c FROM t1 1120 INTERSECT SELECT a,b,c FROM t3 1121 EXCEPT SELECT b,c,a FROM t3 1122 INTERSECT SELECT a,b,c FROM t1 1123 EXCEPT SELECT x,y,z FROM t2 1124 INTERSECT SELECT a,b,c FROM t3 1125 EXCEPT SELECT y,x,z FROM t2 1126 INTERSECT SELECT a,b,c FROM t1 1127 EXCEPT SELECT c,b,a FROM t3 1128 ORDER BY c 1129 } 1130 } {1 a a 9.9 b B {} C c} 1131 do_test selectA-3.72 { 1132 execsql { 1133 SELECT a,b,c FROM t3 UNION SELECT x,y,z FROM t2 1134 ORDER BY a,b,c 1135 } 1136 } {{} C c {} U u -23 Y y 1 a a 9.9 b B 5200000.0 X x hello d D mad Z z abc e e hare m M} 1137 do_test selectA-3.73 { 1138 execsql { 1139 SELECT a,b,c FROM t3 UNION SELECT x,y,z FROM t2 1140 ORDER BY a DESC,b,c 1141 } 1142 } {hare m M abc e e mad Z z hello d D 5200000.0 X x 9.9 b B 1 a a -23 Y y {} C c {} U u} 1143 do_test selectA-3.74 { 1144 execsql { 1145 SELECT a,b,c FROM t3 UNION SELECT x,y,z FROM t2 1146 ORDER BY a,c,b 1147 } 1148 } {{} C c {} U u -23 Y y 1 a a 9.9 b B 5200000.0 X x hello d D mad Z z abc e e hare m M} 1149 do_test selectA-3.75 { 1150 execsql { 1151 SELECT a,b,c FROM t3 UNION SELECT x,y,z FROM t2 1152 ORDER BY b,a,c 1153 } 1154 } {{} C c {} U u 5200000.0 X x -23 Y y mad Z z 1 a a 9.9 b B hello d D abc e e hare m M} 1155 do_test selectA-3.76 { 1156 execsql { 1157 SELECT a,b,c FROM t3 UNION SELECT x,y,z FROM t2 1158 ORDER BY b COLLATE NOCASE,a,c 1159 } 1160 } {1 a a 9.9 b B {} C c hello d D abc e e hare m M {} U u 5200000.0 X x -23 Y y mad Z z} 1161 do_test selectA-3.77 { 1162 execsql { 1163 SELECT a,b,c FROM t3 UNION SELECT x,y,z FROM t2 1164 ORDER BY b COLLATE NOCASE DESC,a,c 1165 } 1166 } {mad Z z -23 Y y 5200000.0 X x {} U u hare m M abc e e hello d D {} C c 9.9 b B 1 a a} 1167 do_test selectA-3.78 { 1168 execsql { 1169 SELECT a,b,c FROM t3 UNION SELECT x,y,z FROM t2 1170 ORDER BY c,b,a 1171 } 1172 } {1 a a 9.9 b B {} C c hello d D abc e e hare m M {} U u 5200000.0 X x -23 Y y mad Z z} 1173 do_test selectA-3.79 { 1174 execsql { 1175 SELECT a,b,c FROM t3 UNION SELECT x,y,z FROM t2 1176 ORDER BY c,a,b 1177 } 1178 } {1 a a 9.9 b B {} C c hello d D abc e e hare m M {} U u 5200000.0 X x -23 Y y mad Z z} 1179 do_test selectA-3.80 { 1180 execsql { 1181 SELECT a,b,c FROM t3 UNION SELECT x,y,z FROM t2 1182 ORDER BY c DESC,a,b 1183 } 1184 } {mad Z z -23 Y y 5200000.0 X x {} U u hare m M abc e e hello d D {} C c 9.9 b B 1 a a} 1185 do_test selectA-3.81 { 1186 execsql { 1187 SELECT a,b,c FROM t3 UNION SELECT x,y,z FROM t2 1188 ORDER BY c COLLATE BINARY DESC,a,b 1189 } 1190 } {mad Z z -23 Y y 5200000.0 X x {} U u abc e e {} C c 1 a a hare m M hello d D 9.9 b B} 1191 do_test selectA-3.82 { 1192 execsql { 1193 SELECT x,y,z FROM t2 UNION SELECT a,b,c FROM t3 1194 ORDER BY a,b,c 1195 } 1196 } {{} C c {} U u -23 Y y 1 a a 9.9 b B 5200000.0 X x hello d D mad Z z abc e e hare m M} 1197 do_test selectA-3.83 { 1198 execsql { 1199 SELECT x,y,z FROM t2 UNION SELECT a,b,c FROM t3 1200 ORDER BY a DESC,b,c 1201 } 1202 } {hare m M abc e e mad Z z hello d D 5200000.0 X x 9.9 b B 1 a a -23 Y y {} C c {} U u} 1203 do_test selectA-3.84 { 1204 execsql { 1205 SELECT x,y,z FROM t2 UNION SELECT a,b,c FROM t3 1206 ORDER BY a,c,b 1207 } 1208 } {{} C c {} U u -23 Y y 1 a a 9.9 b B 5200000.0 X x hello d D mad Z z abc e e hare m M} 1209 do_test selectA-3.85 { 1210 execsql { 1211 SELECT x,y,z FROM t2 UNION SELECT a,b,c FROM t3 1212 ORDER BY b,a,c 1213 } 1214 } {{} C c {} U u 5200000.0 X x -23 Y y mad Z z 1 a a 9.9 b B hello d D abc e e hare m M} 1215 do_test selectA-3.86 { 1216 execsql { 1217 SELECT x,y,z FROM t2 UNION SELECT a,b,c FROM t3 1218 ORDER BY y COLLATE NOCASE,x,z 1219 } 1220 } {1 a a 9.9 b B {} C c hello d D abc e e hare m M {} U u 5200000.0 X x -23 Y y mad Z z} 1221 do_test selectA-3.87 { 1222 execsql { 1223 SELECT x,y,z FROM t2 UNION SELECT a,b,c FROM t3 1224 ORDER BY y COLLATE NOCASE DESC,x,z 1225 } 1226 } {mad Z z -23 Y y 5200000.0 X x {} U u hare m M abc e e hello d D {} C c 9.9 b B 1 a a} 1227 do_test selectA-3.88 { 1228 execsql { 1229 SELECT x,y,z FROM t2 UNION SELECT a,b,c FROM t3 1230 ORDER BY c,b,a 1231 } 1232 } {1 a a 9.9 b B {} C c hello d D abc e e hare m M {} U u 5200000.0 X x -23 Y y mad Z z} 1233 do_test selectA-3.89 { 1234 execsql { 1235 SELECT x,y,z FROM t2 UNION SELECT a,b,c FROM t3 1236 ORDER BY c,a,b 1237 } 1238 } {1 a a 9.9 b B {} C c hello d D abc e e hare m M {} U u 5200000.0 X x -23 Y y mad Z z} 1239 do_test selectA-3.90 { 1240 execsql { 1241 SELECT x,y,z FROM t2 UNION SELECT a,b,c FROM t3 1242 ORDER BY c DESC,a,b 1243 } 1244 } {mad Z z -23 Y y 5200000.0 X x {} U u hare m M abc e e hello d D {} C c 9.9 b B 1 a a} 1245 do_test selectA-3.91 { 1246 execsql { 1247 SELECT x,y,z FROM t2 UNION SELECT a,b,c FROM t3 1248 ORDER BY z COLLATE BINARY DESC,x,y 1249 } 1250 } {mad Z z -23 Y y 5200000.0 X x {} U u abc e e {} C c 1 a a hare m M hello d D 9.9 b B} 1251 do_test selectA-3.92 { 1252 execsql { 1253 SELECT x,y,z FROM t2 1254 INTERSECT SELECT a,b,c FROM t3 1255 EXCEPT SELECT c,b,a FROM t1 1256 UNION SELECT a,b,c FROM t3 1257 INTERSECT SELECT a,b,c FROM t3 1258 EXCEPT SELECT c,b,a FROM t1 1259 UNION SELECT a,b,c FROM t3 1260 ORDER BY y COLLATE NOCASE DESC,x,z 1261 } 1262 } {mad Z z -23 Y y 5200000.0 X x {} U u hare m M abc e e hello d D {} C c 9.9 b B 1 a a} 1263 do_test selectA-3.93 { 1264 execsql { 1265 SELECT upper((SELECT c FROM t1 UNION SELECT z FROM t2 ORDER BY 1)); 1266 } 1267 } {A} 1268 do_test selectA-3.94 { 1269 execsql { 1270 SELECT lower((SELECT c FROM t1 UNION ALL SELECT z FROM t2 ORDER BY 1)); 1271 } 1272 } {a} 1273 do_test selectA-3.95 { 1274 execsql { 1275 SELECT lower((SELECT c FROM t1 INTERSECT SELECT z FROM t2 ORDER BY 1)); 1276 } 1277 } {{}} 1278 do_test selectA-3.96 { 1279 execsql { 1280 SELECT lower((SELECT z FROM t2 EXCEPT SELECT c FROM t1 ORDER BY 1)); 1281 } 1282 } {m} 1283 do_test selectA-3.97 { 1284 execsql { 1285 SELECT upper((SELECT x FROM ( 1286 SELECT x,y,z FROM t2 1287 INTERSECT SELECT a,b,c FROM t3 1288 EXCEPT SELECT c,b,a FROM t1 1289 UNION SELECT a,b,c FROM t3 1290 INTERSECT SELECT a,b,c FROM t3 1291 EXCEPT SELECT c,b,a FROM t1 1292 UNION SELECT a,b,c FROM t3 1293 ORDER BY y COLLATE NOCASE DESC,x,z))) 1294 } 1295 } {MAD} 1296 do_execsql_test selectA-3.98 { 1297 WITH RECURSIVE 1298 xyz(n) AS ( 1299 SELECT upper((SELECT x FROM ( 1300 SELECT x,y,z FROM t2 1301 INTERSECT SELECT a,b,c FROM t3 1302 EXCEPT SELECT c,b,a FROM t1 1303 UNION SELECT a,b,c FROM t3 1304 INTERSECT SELECT a,b,c FROM t3 1305 EXCEPT SELECT c,b,a FROM t1 1306 UNION SELECT a,b,c FROM t3 1307 ORDER BY y COLLATE NOCASE DESC,x,z))) 1308 UNION ALL 1309 SELECT n || '+' FROM xyz WHERE length(n)<5 1310 ) 1311 SELECT n FROM xyz ORDER BY +n; 1312 } {MAD MAD+ MAD++} 1313 1314 #------------------------------------------------------------------------- 1315 # At one point the following code exposed a temp register reuse problem. 1316 # 1317 proc f {args} { return 1 } 1318 db func f f 1319 1320 do_execsql_test 4.1.1 { 1321 CREATE TABLE t4(a, b); 1322 CREATE TABLE t5(c, d); 1323 1324 INSERT INTO t5 VALUES(1, 'x'); 1325 INSERT INTO t5 VALUES(2, 'x'); 1326 INSERT INTO t4 VALUES(3, 'x'); 1327 INSERT INTO t4 VALUES(4, 'x'); 1328 1329 CREATE INDEX i1 ON t4(a); 1330 CREATE INDEX i2 ON t5(c); 1331 } 1332 1333 do_eqp_test 4.1.2 { 1334 SELECT c, d FROM t5 1335 UNION ALL 1336 SELECT a, b FROM t4 WHERE f()==f() 1337 ORDER BY 1,2 1338 } { 1339 QUERY PLAN 1340 `--MERGE (UNION ALL) 1341 |--LEFT 1342 | |--SCAN t5 USING INDEX i2 1343 | `--USE TEMP B-TREE FOR RIGHT PART OF ORDER BY 1344 `--RIGHT 1345 |--SCAN t4 USING INDEX i1 1346 `--USE TEMP B-TREE FOR RIGHT PART OF ORDER BY 1347 } 1348 1349 do_execsql_test 4.1.3 { 1350 SELECT c, d FROM t5 1351 UNION ALL 1352 SELECT a, b FROM t4 WHERE f()==f() 1353 ORDER BY 1,2 1354 } { 1355 1 x 2 x 3 x 4 x 1356 } 1357 1358 do_execsql_test 4.2.1 { 1359 CREATE TABLE t6(a, b); 1360 CREATE TABLE t7(c, d); 1361 1362 INSERT INTO t7 VALUES(2, 9); 1363 INSERT INTO t6 VALUES(3, 0); 1364 INSERT INTO t6 VALUES(4, 1); 1365 INSERT INTO t7 VALUES(5, 6); 1366 INSERT INTO t6 VALUES(6, 0); 1367 INSERT INTO t7 VALUES(7, 6); 1368 1369 CREATE INDEX i6 ON t6(a); 1370 CREATE INDEX i7 ON t7(c); 1371 } 1372 1373 do_execsql_test 4.2.2 { 1374 SELECT c, f(d,c,d,c,d) FROM t7 1375 UNION ALL 1376 SELECT a, b FROM t6 1377 ORDER BY 1,2 1378 } {/2 . 3 . 4 . 5 . 6 . 7 ./} 1379 1380 1381 proc strip_rnd {explain} { 1382 regexp -all {sqlite_sq_[0123456789ABCDEF]*} $explain sqlite_sq 1383 } 1384 1385 proc do_same_test {tn q1 args} { 1386 set r2 [strip_rnd [db eval "EXPLAIN $q1"]] 1387 set i 1 1388 foreach q $args { 1389 set tst [subst -nocommands {strip_rnd [db eval "EXPLAIN $q"]}] 1390 uplevel do_test $tn.$i [list $tst] [list $r2] 1391 incr i 1392 } 1393 } 1394 1395 do_execsql_test 5.0 { 1396 CREATE TABLE t8(a, b); 1397 CREATE TABLE t9(c, d); 1398 } {} 1399 1400 do_same_test 5.1 { 1401 SELECT a, b FROM t8 INTERSECT SELECT c, d FROM t9 ORDER BY a; 1402 } { 1403 SELECT a, b FROM t8 INTERSECT SELECT c, d FROM t9 ORDER BY t8.a; 1404 } { 1405 SELECT a, b FROM t8 INTERSECT SELECT c, d FROM t9 ORDER BY 1; 1406 } { 1407 SELECT a, b FROM t8 INTERSECT SELECT c, d FROM t9 ORDER BY c; 1408 } { 1409 SELECT a, b FROM t8 INTERSECT SELECT c, d FROM t9 ORDER BY t9.c; 1410 } 1411 1412 do_same_test 5.2 { 1413 SELECT a, b FROM t8 UNION SELECT c, d FROM t9 ORDER BY a COLLATE NOCASE 1414 } { 1415 SELECT a, b FROM t8 UNION SELECT c, d FROM t9 ORDER BY t8.a COLLATE NOCASE 1416 } { 1417 SELECT a, b FROM t8 UNION SELECT c, d FROM t9 ORDER BY 1 COLLATE NOCASE 1418 } { 1419 SELECT a, b FROM t8 UNION SELECT c, d FROM t9 ORDER BY c COLLATE NOCASE 1420 } { 1421 SELECT a, b FROM t8 UNION SELECT c, d FROM t9 ORDER BY t9.c COLLATE NOCASE 1422 } 1423 1424 do_same_test 5.3 { 1425 SELECT a, b FROM t8 EXCEPT SELECT c, d FROM t9 ORDER BY b, c COLLATE NOCASE 1426 } { 1427 SELECT a, b FROM t8 EXCEPT SELECT c, d FROM t9 ORDER BY 2, 1 COLLATE NOCASE 1428 } { 1429 SELECT a, b FROM t8 EXCEPT SELECT c, d FROM t9 ORDER BY d, a COLLATE NOCASE 1430 } { 1431 SELECT a, b FROM t8 EXCEPT SELECT * FROM t9 ORDER BY t9.d, c COLLATE NOCASE 1432 } { 1433 SELECT * FROM t8 EXCEPT SELECT c, d FROM t9 ORDER BY d, t8.a COLLATE NOCASE 1434 } 1435 1436 do_catchsql_test 5.4 { 1437 SELECT * FROM t8 UNION SELECT * FROM t9 ORDER BY a+b COLLATE NOCASE 1438 } {1 {1st ORDER BY term does not match any column in the result set}} 1439 1440 do_execsql_test 6.1 { 1441 DROP TABLE IF EXISTS t1; 1442 DROP TABLE IF EXISTS t2; 1443 CREATE TABLE t1(a INTEGER); 1444 CREATE TABLE t2(b TEXT); 1445 INSERT INTO t2(b) VALUES('12345'); 1446 SELECT * FROM (SELECT a FROM t1 UNION SELECT b FROM t2) WHERE a=a; 1447 } {12345} 1448 1449 # 2020-06-15 ticket 8f157e8010b22af0 1450 # 1451 reset_db 1452 do_execsql_test 7.1 { 1453 CREATE TABLE t1(c1); INSERT INTO t1 VALUES(12),(123),(1234),(NULL),('abc'); 1454 CREATE TABLE t2(c2); INSERT INTO t2 VALUES(44),(55),(123); 1455 CREATE TABLE t3(c3,c4); INSERT INTO t3 VALUES(66,1),(123,2),(77,3); 1456 CREATE VIEW t4 AS SELECT c3 FROM t3; 1457 CREATE VIEW t5 AS SELECT c3 FROM t3 ORDER BY c4; 1458 } 1459 do_execsql_test 7.2 { 1460 SELECT * FROM t1, t2 WHERE c1=(SELECT 123 INTERSECT SELECT c2 FROM t4) AND c1=123; 1461 } {123 123} 1462 do_execsql_test 7.3 { 1463 SELECT * FROM t1, t2 WHERE c1=(SELECT 123 INTERSECT SELECT c2 FROM t5) AND c1=123; 1464 } {123 123} 1465 do_execsql_test 7.4 { 1466 CREATE TABLE a(b); 1467 CREATE VIEW c(d) AS SELECT b FROM a ORDER BY b; 1468 SELECT sum(d) OVER( PARTITION BY(SELECT 0 FROM c JOIN a WHERE b =(SELECT b INTERSECT SELECT d FROM c) AND b = 123)) FROM c; 1469 } {} 1470 1471 #------------------------------------------------------------------------- 1472 reset_db 1473 do_execsql_test 8.0 { 1474 CREATE TABLE x1(x); 1475 CREATE TABLE t1(a, b, c, d); 1476 CREATE INDEX t1a ON t1(a); 1477 CREATE INDEX t1b ON t1(b); 1478 } 1479 1480 do_execsql_test 8.1 { 1481 SELECT 'ABCD' FROM t1 1482 WHERE (a=? OR b=?) 1483 AND (0 OR (SELECT 'xyz' INTERSECT SELECT a ORDER BY 1)) 1484 } {} 1485 1486 finish_test