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