AryaWu/sqlite
0
1# 2008 June 242#3# The author disclaims copyright to this source code. In place of4# a legal notice, here is a blessing:5#6# May you do good and not evil.7# May you find forgiveness for yourself and forgive others.8# May you share freely, never taking more than you give.9#10#***********************************************************************11# This file implements regression tests for SQLite library. 12#13# The focus of this file is testing the compound-SELECT merge14# optimization. Or, in other words, making sure that all15# possible combinations of UNION, UNION ALL, EXCEPT, and16# INTERSECT work together with an ORDER BY clause (with or w/o17# explicit sort order and explicit collating secquites) and18# 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.tcl24set testprefix selectA25 26ifcapable !compound {27 finish_test28 return29}30 31do_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}42do_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}53do_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 66do_test selectA-2.1 {67 execsql {68 SELECT a,b,c FROM t1 UNION ALL SELECT x,y,z FROM t269 ORDER BY a,b,c70 }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}72do_test selectA-2.1.1 { # Ticket #331473 execsql {74 SELECT t1.a, t1.b, t1.c FROM t1 UNION ALL SELECT x,y,z FROM t275 ORDER BY a,b,c76 }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}78do_test selectA-2.1.2 { # Ticket #331479 execsql {80 SELECT a,b,c FROM t1 UNION ALL SELECT x,y,z FROM t281 ORDER BY t1.a, t1.b, t1.c82 }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}84do_test selectA-2.2 {85 execsql {86 SELECT a,b,c FROM t1 UNION ALL SELECT x,y,z FROM t287 ORDER BY a DESC,b,c88 }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}90do_test selectA-2.3 {91 execsql {92 SELECT a,b,c FROM t1 UNION ALL SELECT x,y,z FROM t293 ORDER BY a,c,b94 }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}96do_test selectA-2.4 {97 execsql {98 SELECT a,b,c FROM t1 UNION ALL SELECT x,y,z FROM t299 ORDER BY b,a,c100 }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}102do_test selectA-2.5 {103 execsql {104 SELECT a,b,c FROM t1 UNION ALL SELECT x,y,z FROM t2105 ORDER BY b COLLATE NOCASE,a,c106 }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}108do_test selectA-2.6 {109 execsql {110 SELECT a,b,c FROM t1 UNION ALL SELECT x,y,z FROM t2111 ORDER BY b COLLATE NOCASE DESC,a,c112 }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}114do_test selectA-2.7 {115 execsql {116 SELECT a,b,c FROM t1 UNION ALL SELECT x,y,z FROM t2117 ORDER BY c,b,a118 }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}120do_test selectA-2.8 {121 execsql {122 SELECT a,b,c FROM t1 UNION ALL SELECT x,y,z FROM t2123 ORDER BY c,a,b124 }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}126do_test selectA-2.9 {127 execsql {128 SELECT a,b,c FROM t1 UNION ALL SELECT x,y,z FROM t2129 ORDER BY c DESC,a,b130 }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}132do_test selectA-2.10 {133 execsql {134 SELECT a,b,c FROM t1 UNION ALL SELECT x,y,z FROM t2135 ORDER BY c COLLATE BINARY DESC,a,b136 }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}138do_test selectA-2.11 {139 execsql {140 SELECT x,y,z FROM t2 UNION ALL SELECT a,b,c FROM t1141 ORDER BY a,b,c142 }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}144do_test selectA-2.12 {145 execsql {146 SELECT x,y,z FROM t2 UNION ALL SELECT a,b,c FROM t1147 ORDER BY a DESC,b,c148 }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}150do_test selectA-2.13 {151 execsql {152 SELECT x,y,z FROM t2 UNION ALL SELECT a,b,c FROM t1153 ORDER BY a,c,b154 }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}156do_test selectA-2.14 {157 execsql {158 SELECT x,y,z FROM t2 UNION ALL SELECT a,b,c FROM t1159 ORDER BY b,a,c160 }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}162do_test selectA-2.15 {163 execsql {164 SELECT x,y,z FROM t2 UNION ALL SELECT a,b,c FROM t1165 ORDER BY b COLLATE NOCASE,a,c166 }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}168do_test selectA-2.16 {169 execsql {170 SELECT x,y,z FROM t2 UNION ALL SELECT a,b,c FROM t1171 ORDER BY b COLLATE NOCASE DESC,a,c172 }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}174do_test selectA-2.17 {175 execsql {176 SELECT x,y,z FROM t2 UNION ALL SELECT a,b,c FROM t1177 ORDER BY c,b,a178 }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}180do_test selectA-2.18 {181 execsql {182 SELECT x,y,z FROM t2 UNION ALL SELECT a,b,c FROM t1183 ORDER BY c,a,b184 }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}186do_test selectA-2.19 {187 execsql {188 SELECT x,y,z FROM t2 UNION ALL SELECT a,b,c FROM t1189 ORDER BY c DESC,a,b190 }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}192do_test selectA-2.20 {193 execsql {194 SELECT x,y,z FROM t2 UNION ALL SELECT a,b,c FROM t1195 ORDER BY c COLLATE BINARY DESC,a,b196 }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}198do_test selectA-2.21 {199 execsql {200 SELECT a,b,c FROM t1 UNION SELECT x,y,z FROM t2201 ORDER BY a,b,c202 }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}204do_test selectA-2.22 {205 execsql {206 SELECT a,b,c FROM t1 UNION SELECT x,y,z FROM t2207 ORDER BY a DESC,b,c208 }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}210do_test selectA-2.23 {211 execsql {212 SELECT a,b,c FROM t1 UNION SELECT x,y,z FROM t2213 ORDER BY a,c,b214 }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}216do_test selectA-2.24 {217 execsql {218 SELECT a,b,c FROM t1 UNION SELECT x,y,z FROM t2219 ORDER BY b,a,c220 }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}222do_test selectA-2.25 {223 execsql {224 SELECT a,b,c FROM t1 UNION SELECT x,y,z FROM t2225 ORDER BY b COLLATE NOCASE,a,c226 }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}228do_test selectA-2.26 {229 execsql {230 SELECT a,b,c FROM t1 UNION SELECT x,y,z FROM t2231 ORDER BY b COLLATE NOCASE DESC,a,c232 }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}234do_test selectA-2.27 {235 execsql {236 SELECT a,b,c FROM t1 UNION SELECT x,y,z FROM t2237 ORDER BY c,b,a238 }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}240do_test selectA-2.28 {241 execsql {242 SELECT a,b,c FROM t1 UNION SELECT x,y,z FROM t2243 ORDER BY c,a,b244 }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}246do_test selectA-2.29 {247 execsql {248 SELECT a,b,c FROM t1 UNION SELECT x,y,z FROM t2249 ORDER BY c DESC,a,b250 }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}252do_test selectA-2.30 {253 execsql {254 SELECT a,b,c FROM t1 UNION SELECT x,y,z FROM t2255 ORDER BY c COLLATE BINARY DESC,a,b256 }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}258do_test selectA-2.31 {259 execsql {260 SELECT x,y,z FROM t2 UNION SELECT a,b,c FROM t1261 ORDER BY a,b,c262 }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}264do_test selectA-2.32 {265 execsql {266 SELECT x,y,z FROM t2 UNION SELECT a,b,c FROM t1267 ORDER BY a DESC,b,c268 }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}270do_test selectA-2.33 {271 execsql {272 SELECT x,y,z FROM t2 UNION SELECT a,b,c FROM t1273 ORDER BY a,c,b274 }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}276do_test selectA-2.34 {277 execsql {278 SELECT x,y,z FROM t2 UNION SELECT a,b,c FROM t1279 ORDER BY b,a,c280 }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}282do_test selectA-2.35 {283 execsql {284 SELECT x,y,z FROM t2 UNION SELECT a,b,c FROM t1285 ORDER BY y COLLATE NOCASE,x,z286 }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}288do_test selectA-2.36 {289 execsql {290 SELECT x,y,z FROM t2 UNION SELECT a,b,c FROM t1291 ORDER BY y COLLATE NOCASE DESC,x,z292 }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}294do_test selectA-2.37 {295 execsql {296 SELECT x,y,z FROM t2 UNION SELECT a,b,c FROM t1297 ORDER BY c,b,a298 }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}300do_test selectA-2.38 {301 execsql {302 SELECT x,y,z FROM t2 UNION SELECT a,b,c FROM t1303 ORDER BY c,a,b304 }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}306do_test selectA-2.39 {307 execsql {308 SELECT x,y,z FROM t2 UNION SELECT a,b,c FROM t1309 ORDER BY c DESC,a,b310 }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}312do_test selectA-2.40 {313 execsql {314 SELECT x,y,z FROM t2 UNION SELECT a,b,c FROM t1315 ORDER BY z COLLATE BINARY DESC,x,y316 }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}318do_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,c322 }323} {{} C c 1 a a 9.9 b B}324do_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,c328 }329} {hello d D abc e e}330do_test selectA-2.43 {331 execsql {332 SELECT a,b,c FROM t1 WHERE b>='d' INTERSECT SELECT a,b,c FROM t1333 ORDER BY a,b,c334 }335} {hello d D abc e e}336do_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,c340 }341} {hello d D abc e e}342do_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,c346 }347} {{} C c 1 a a 9.9 b B}348do_test selectA-2.46 {349 execsql {350 SELECT a,b,c FROM t1 WHERE b<'d' INTERSECT SELECT a,b,c FROM t1351 ORDER BY a,b,c352 }353} {{} C c 1 a a 9.9 b B}354do_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 DESC358 }359} {9.9 b B 1 a a {} C c}360do_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 DESC364 }365} {abc e e hello d D}366do_test selectA-2.49 {367 execsql {368 SELECT a,b,c FROM t1 WHERE b>='d' INTERSECT SELECT a,b,c FROM t1369 ORDER BY a DESC370 }371} {abc e e hello d D}372do_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 DESC376 }377} {abc e e hello d D}378do_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 DESC382 }383} {9.9 b B 1 a a {} C c}384do_test selectA-2.52 {385 execsql {386 SELECT a,b,c FROM t1 WHERE b<'d' INTERSECT SELECT a,b,c FROM t1387 ORDER BY a DESC388 }389} {9.9 b B 1 a a {} C c}390do_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 DESC394 }395} {{} C c 1 a a 9.9 b B}396do_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 b400 }401} {hello d D abc e e}402do_test selectA-2.55 {403 execsql {404 SELECT a,b,c FROM t1 WHERE b>='d' INTERSECT SELECT a,b,c FROM t1405 ORDER BY b DESC, c406 }407} {abc e e hello d D}408do_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, a412 }413} {hello d D abc e e}414do_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 NOCASE418 }419} {1 a a 9.9 b B {} C c}420do_test selectA-2.58 {421 execsql {422 SELECT a,b,c FROM t1 WHERE b<'d' INTERSECT SELECT a,b,c FROM t1423 ORDER BY b424 }425} {{} C c 1 a a 9.9 b B}426do_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 DESC430 }431} {1 a a 9.9 b B {} C c}432do_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 c436 }437} {hello d D abc e e}438do_test selectA-2.61 {439 execsql {440 SELECT a,b,c FROM t1 WHERE b>='d' INTERSECT SELECT a,b,c FROM t1441 ORDER BY c COLLATE BINARY, b DESC, c, a, b, c, a, b, c442 }443} {hello d D abc e e}444do_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, a448 }449} {abc e e hello d D}450do_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 NOCASE454 }455} {1 a a 9.9 b B {} C c}456do_test selectA-2.64 {457 execsql {458 SELECT a,b,c FROM t1 WHERE b<'d' INTERSECT SELECT a,b,c FROM t1459 ORDER BY c460 }461} {1 a a 9.9 b B {} C c}462do_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 NOCASE466 }467} {1 a a 9.9 b B {} C c}468do_test selectA-2.66 {469 execsql {470 SELECT a,b,c FROM t1 WHERE b<'d' INTERSECT SELECT a,b,c FROM t3471 ORDER BY c472 }473} {1 a a 9.9 b B {} C c}474do_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, a478 }479} {abc e e hello d D}480do_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 t3484 EXCEPT SELECT b,c,a FROM t3485 ORDER BY c DESC, a486 }487} {abc e e hello d D}488do_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 t3492 EXCEPT SELECT b,c,a FROM t3493 ORDER BY c COLLATE NOCASE494 }495} {1 a a 9.9 b B {} C c}496do_test selectA-2.70 {497 execsql {498 SELECT a,b,c FROM t1 WHERE b<'d' INTERSECT SELECT a,b,c FROM t1499 INTERSECT SELECT a,b,c FROM t3500 EXCEPT SELECT b,c,a FROM t3501 ORDER BY c502 }503} {1 a a 9.9 b B {} C c}504do_test selectA-2.71 {505 execsql {506 SELECT a,b,c FROM t1 WHERE b<'d'507 INTERSECT SELECT a,b,c FROM t1508 INTERSECT SELECT a,b,c FROM t3509 EXCEPT SELECT b,c,a FROM t3510 INTERSECT SELECT a,b,c FROM t1511 EXCEPT SELECT x,y,z FROM t2512 INTERSECT SELECT a,b,c FROM t3513 EXCEPT SELECT y,x,z FROM t2514 INTERSECT SELECT a,b,c FROM t1515 EXCEPT SELECT c,b,a FROM t3516 ORDER BY c517 }518} {1 a a 9.9 b B {} C c}519do_test selectA-2.72 {520 execsql {521 SELECT a,b,c FROM t3 UNION SELECT x,y,z FROM t2522 ORDER BY a,b,c523 }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}525do_test selectA-2.73 {526 execsql {527 SELECT a,b,c FROM t3 UNION SELECT x,y,z FROM t2528 ORDER BY a DESC,b,c529 }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}531do_test selectA-2.74 {532 execsql {533 SELECT a,b,c FROM t3 UNION SELECT x,y,z FROM t2534 ORDER BY a,c,b535 }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}537do_test selectA-2.75 {538 execsql {539 SELECT a,b,c FROM t3 UNION SELECT x,y,z FROM t2540 ORDER BY b,a,c541 }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}543do_test selectA-2.76 {544 execsql {545 SELECT a,b,c FROM t3 UNION SELECT x,y,z FROM t2546 ORDER BY b COLLATE NOCASE,a,c547 }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}549do_test selectA-2.77 {550 execsql {551 SELECT a,b,c FROM t3 UNION SELECT x,y,z FROM t2552 ORDER BY b COLLATE NOCASE DESC,a,c553 }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}555do_test selectA-2.78 {556 execsql {557 SELECT a,b,c FROM t3 UNION SELECT x,y,z FROM t2558 ORDER BY c,b,a559 }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}561do_test selectA-2.79 {562 execsql {563 SELECT a,b,c FROM t3 UNION SELECT x,y,z FROM t2564 ORDER BY c,a,b565 }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}567do_test selectA-2.80 {568 execsql {569 SELECT a,b,c FROM t3 UNION SELECT x,y,z FROM t2570 ORDER BY c DESC,a,b571 }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}573do_test selectA-2.81 {574 execsql {575 SELECT a,b,c FROM t3 UNION SELECT x,y,z FROM t2576 ORDER BY c COLLATE BINARY DESC,a,b577 }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}579do_test selectA-2.82 {580 execsql {581 SELECT x,y,z FROM t2 UNION SELECT a,b,c FROM t3582 ORDER BY a,b,c583 }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}585do_test selectA-2.83 {586 execsql {587 SELECT x,y,z FROM t2 UNION SELECT a,b,c FROM t3588 ORDER BY a DESC,b,c589 }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}591do_test selectA-2.84 {592 execsql {593 SELECT x,y,z FROM t2 UNION SELECT a,b,c FROM t3594 ORDER BY a,c,b595 }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}597do_test selectA-2.85 {598 execsql {599 SELECT x,y,z FROM t2 UNION SELECT a,b,c FROM t3600 ORDER BY b,a,c601 }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}603do_test selectA-2.86 {604 execsql {605 SELECT x,y,z FROM t2 UNION SELECT a,b,c FROM t3606 ORDER BY y COLLATE NOCASE,x,z607 }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}609do_test selectA-2.87 {610 execsql {611 SELECT x,y,z FROM t2 UNION SELECT a,b,c FROM t3612 ORDER BY y COLLATE NOCASE DESC,x,z613 }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}615do_test selectA-2.88 {616 execsql {617 SELECT x,y,z FROM t2 UNION SELECT a,b,c FROM t3618 ORDER BY c,b,a619 }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}621do_test selectA-2.89 {622 execsql {623 SELECT x,y,z FROM t2 UNION SELECT a,b,c FROM t3624 ORDER BY c,a,b625 }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}627do_test selectA-2.90 {628 execsql {629 SELECT x,y,z FROM t2 UNION SELECT a,b,c FROM t3630 ORDER BY c DESC,a,b631 }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}633do_test selectA-2.91 {634 execsql {635 SELECT x,y,z FROM t2 UNION SELECT a,b,c FROM t3636 ORDER BY z COLLATE BINARY DESC,x,y637 }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}639do_test selectA-2.92 {640 execsql {641 SELECT x,y,z FROM t2642 INTERSECT SELECT a,b,c FROM t3643 EXCEPT SELECT c,b,a FROM t1644 UNION SELECT a,b,c FROM t3645 INTERSECT SELECT a,b,c FROM t3646 EXCEPT SELECT c,b,a FROM t1647 UNION SELECT a,b,c FROM t3648 ORDER BY y COLLATE NOCASE DESC,x,z649 }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}651do_test selectA-2.93 {652 execsql {653 SELECT upper((SELECT c FROM t1 UNION SELECT z FROM t2 ORDER BY 1));654 }655} {A}656do_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}661do_test selectA-2.95 {662 execsql {663 SELECT lower((SELECT c FROM t1 INTERSECT SELECT z FROM t2 ORDER BY 1));664 }665} {{}}666do_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 673do_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}684do_test selectA-3.1 {685 execsql {686 SELECT a,b,c FROM t1 UNION ALL SELECT x,y,z FROM t2687 ORDER BY a,b,c688 }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}690do_test selectA-3.1.1 { # Ticket #3314691 execsql {692 SELECT t1.a,b,t1.c FROM t1 UNION ALL SELECT x,y,z FROM t2693 ORDER BY a,t1.b,t1.c694 }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}696do_test selectA-3.2 {697 execsql {698 SELECT a,b,c FROM t1 UNION ALL SELECT x,y,z FROM t2699 ORDER BY a DESC,b,c700 }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}702do_test selectA-3.3 {703 execsql {704 SELECT a,b,c FROM t1 UNION ALL SELECT x,y,z FROM t2705 ORDER BY a,c,b706 }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}708do_test selectA-3.4 {709 execsql {710 SELECT a,b,c FROM t1 UNION ALL SELECT x,y,z FROM t2711 ORDER BY b,a,c712 }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}714do_test selectA-3.5 {715 execsql {716 SELECT a,b,c FROM t1 UNION ALL SELECT x,y,z FROM t2717 ORDER BY b COLLATE NOCASE,a,c718 }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}720do_test selectA-3.6 {721 execsql {722 SELECT a,b,c FROM t1 UNION ALL SELECT x,y,z FROM t2723 ORDER BY b COLLATE NOCASE DESC,a,c724 }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}726do_test selectA-3.7 {727 execsql {728 SELECT a,b,c FROM t1 UNION ALL SELECT x,y,z FROM t2729 ORDER BY c,b,a730 }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}732do_test selectA-3.8 {733 execsql {734 SELECT a,b,c FROM t1 UNION ALL SELECT x,y,z FROM t2735 ORDER BY c,a,b736 }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}738do_test selectA-3.9 {739 execsql {740 SELECT a,b,c FROM t1 UNION ALL SELECT x,y,z FROM t2741 ORDER BY c DESC,a,b742 }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}744do_test selectA-3.10 {745 execsql {746 SELECT a,b,c FROM t1 UNION ALL SELECT x,y,z FROM t2747 ORDER BY c COLLATE BINARY DESC,a,b748 }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}750do_test selectA-3.11 {751 execsql {752 SELECT x,y,z FROM t2 UNION ALL SELECT a,b,c FROM t1753 ORDER BY a,b,c754 }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}756do_test selectA-3.12 {757 execsql {758 SELECT x,y,z FROM t2 UNION ALL SELECT a,b,c FROM t1759 ORDER BY a DESC,b,c760 }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}762do_test selectA-3.13 {763 execsql {764 SELECT x,y,z FROM t2 UNION ALL SELECT a,b,c FROM t1765 ORDER BY a,c,b766 }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}768do_test selectA-3.14 {769 execsql {770 SELECT x,y,z FROM t2 UNION ALL SELECT a,b,c FROM t1771 ORDER BY b,a,c772 }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}774do_test selectA-3.15 {775 execsql {776 SELECT x,y,z FROM t2 UNION ALL SELECT a,b,c FROM t1777 ORDER BY b COLLATE NOCASE,a,c778 }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}780do_test selectA-3.16 {781 execsql {782 SELECT x,y,z FROM t2 UNION ALL SELECT a,b,c FROM t1783 ORDER BY b COLLATE NOCASE DESC,a,c784 }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}786do_test selectA-3.17 {787 execsql {788 SELECT x,y,z FROM t2 UNION ALL SELECT a,b,c FROM t1789 ORDER BY c,b,a790 }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}792do_test selectA-3.18 {793 execsql {794 SELECT x,y,z FROM t2 UNION ALL SELECT a,b,c FROM t1795 ORDER BY c,a,b796 }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}798do_test selectA-3.19 {799 execsql {800 SELECT x,y,z FROM t2 UNION ALL SELECT a,b,c FROM t1801 ORDER BY c DESC,a,b802 }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}804do_test selectA-3.20 {805 execsql {806 SELECT x,y,z FROM t2 UNION ALL SELECT a,b,c FROM t1807 ORDER BY c COLLATE BINARY DESC,a,b808 }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}810do_test selectA-3.21 {811 execsql {812 SELECT a,b,c FROM t1 UNION SELECT x,y,z FROM t2813 ORDER BY a,b,c814 }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}816do_test selectA-3.22 {817 execsql {818 SELECT a,b,c FROM t1 UNION SELECT x,y,z FROM t2819 ORDER BY a DESC,b,c820 }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}822do_test selectA-3.23 {823 execsql {824 SELECT a,b,c FROM t1 UNION SELECT x,y,z FROM t2825 ORDER BY a,c,b826 }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}828do_test selectA-3.24 {829 execsql {830 SELECT a,b,c FROM t1 UNION SELECT x,y,z FROM t2831 ORDER BY b,a,c832 }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}834do_test selectA-3.25 {835 execsql {836 SELECT a,b,c FROM t1 UNION SELECT x,y,z FROM t2837 ORDER BY b COLLATE NOCASE,a,c838 }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}840do_test selectA-3.26 {841 execsql {842 SELECT a,b,c FROM t1 UNION SELECT x,y,z FROM t2843 ORDER BY b COLLATE NOCASE DESC,a,c844 }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}846do_test selectA-3.27 {847 execsql {848 SELECT a,b,c FROM t1 UNION SELECT x,y,z FROM t2849 ORDER BY c,b,a850 }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}852do_test selectA-3.28 {853 execsql {854 SELECT a,b,c FROM t1 UNION SELECT x,y,z FROM t2855 ORDER BY c,a,b856 }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}858do_test selectA-3.29 {859 execsql {860 SELECT a,b,c FROM t1 UNION SELECT x,y,z FROM t2861 ORDER BY c DESC,a,b862 }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}864do_test selectA-3.30 {865 execsql {866 SELECT a,b,c FROM t1 UNION SELECT x,y,z FROM t2867 ORDER BY c COLLATE BINARY DESC,a,b868 }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}870do_test selectA-3.31 {871 execsql {872 SELECT x,y,z FROM t2 UNION SELECT a,b,c FROM t1873 ORDER BY a,b,c874 }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}876do_test selectA-3.32 {877 execsql {878 SELECT x,y,z FROM t2 UNION SELECT a,b,c FROM t1879 ORDER BY a DESC,b,c880 }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}882do_test selectA-3.33 {883 execsql {884 SELECT x,y,z FROM t2 UNION SELECT a,b,c FROM t1885 ORDER BY a,c,b886 }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}888do_test selectA-3.34 {889 execsql {890 SELECT x,y,z FROM t2 UNION SELECT a,b,c FROM t1891 ORDER BY b,a,c892 }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}894do_test selectA-3.35 {895 execsql {896 SELECT x,y,z FROM t2 UNION SELECT a,b,c FROM t1897 ORDER BY y COLLATE NOCASE,x,z898 }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}900do_test selectA-3.36 {901 execsql {902 SELECT x,y,z FROM t2 UNION SELECT a,b,c FROM t1903 ORDER BY y COLLATE NOCASE DESC,x,z904 }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}906do_test selectA-3.37 {907 execsql {908 SELECT x,y,z FROM t2 UNION SELECT a,b,c FROM t1909 ORDER BY c,b,a910 }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}912do_test selectA-3.38 {913 execsql {914 SELECT x,y,z FROM t2 UNION SELECT a,b,c FROM t1915 ORDER BY c,a,b916 }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}918do_test selectA-3.39 {919 execsql {920 SELECT x,y,z FROM t2 UNION SELECT a,b,c FROM t1921 ORDER BY c DESC,a,b922 }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}924do_test selectA-3.40 {925 execsql {926 SELECT x,y,z FROM t2 UNION SELECT a,b,c FROM t1927 ORDER BY z COLLATE BINARY DESC,x,y928 }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}930do_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,c934 }935} {{} C c 1 a a 9.9 b B}936do_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,c940 }941} {hello d D abc e e}942do_test selectA-3.43 {943 execsql {944 SELECT a,b,c FROM t1 WHERE b>='d' INTERSECT SELECT a,b,c FROM t1945 ORDER BY a,b,c946 }947} {hello d D abc e e}948do_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,c952 }953} {hello d D abc e e}954do_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,c958 }959} {{} C c 1 a a 9.9 b B}960do_test selectA-3.46 {961 execsql {962 SELECT a,b,c FROM t1 WHERE b<'d' INTERSECT SELECT a,b,c FROM t1963 ORDER BY a,b,c964 }965} {{} C c 1 a a 9.9 b B}966do_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 DESC970 }971} {9.9 b B 1 a a {} C c}972do_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 DESC976 }977} {abc e e hello d D}978do_test selectA-3.49 {979 execsql {980 SELECT a,b,c FROM t1 WHERE b>='d' INTERSECT SELECT a,b,c FROM t1981 ORDER BY a DESC982 }983} {abc e e hello d D}984do_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 DESC988 }989} {abc e e hello d D}990do_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 DESC994 }995} {9.9 b B 1 a a {} C c}996do_test selectA-3.52 {997 execsql {998 SELECT a,b,c FROM t1 WHERE b<'d' INTERSECT SELECT a,b,c FROM t1999 ORDER BY a DESC1000 }1001} {9.9 b B 1 a a {} C c}1002do_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 DESC1006 }1007} {{} C c 1 a a 9.9 b B}1008do_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 b1012 }1013} {hello d D abc e e}1014do_test selectA-3.55 {1015 execsql {1016 SELECT a,b,c FROM t1 WHERE b>='d' INTERSECT SELECT a,b,c FROM t11017 ORDER BY b DESC, c1018 }1019} {abc e e hello d D}1020do_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, a1024 }1025} {hello d D abc e e}1026do_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 NOCASE1030 }1031} {1 a a 9.9 b B {} C c}1032do_test selectA-3.58 {1033 execsql {1034 SELECT a,b,c FROM t1 WHERE b<'d' INTERSECT SELECT a,b,c FROM t11035 ORDER BY b1036 }1037} {{} C c 1 a a 9.9 b B}1038do_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 DESC1042 }1043} {1 a a 9.9 b B {} C c}1044do_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 c1048 }1049} {hello d D abc e e}1050do_test selectA-3.61 {1051 execsql {1052 SELECT a,b,c FROM t1 WHERE b>='d' INTERSECT SELECT a,b,c FROM t11053 ORDER BY c COLLATE BINARY, b DESC, c, a, b, c, a, b, c1054 }1055} {hello d D abc e e}1056do_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, a1060 }1061} {abc e e hello d D}1062do_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 NOCASE1066 }1067} {1 a a 9.9 b B {} C c}1068do_test selectA-3.64 {1069 execsql {1070 SELECT a,b,c FROM t1 WHERE b<'d' INTERSECT SELECT a,b,c FROM t11071 ORDER BY c1072 }1073} {1 a a 9.9 b B {} C c}1074do_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 NOCASE1078 }1079} {1 a a 9.9 b B {} C c}1080do_test selectA-3.66 {1081 execsql {1082 SELECT a,b,c FROM t1 WHERE b<'d' INTERSECT SELECT a,b,c FROM t31083 ORDER BY c1084 }1085} {1 a a 9.9 b B {} C c}1086do_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, a1090 }1091} {abc e e hello d D}1092do_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 t31096 EXCEPT SELECT b,c,a FROM t31097 ORDER BY c DESC, a1098 }1099} {abc e e hello d D}1100do_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 t31104 EXCEPT SELECT b,c,a FROM t31105 ORDER BY c COLLATE NOCASE1106 }1107} {1 a a 9.9 b B {} C c}1108do_test selectA-3.70 {1109 execsql {1110 SELECT a,b,c FROM t1 WHERE b<'d' INTERSECT SELECT a,b,c FROM t11111 INTERSECT SELECT a,b,c FROM t31112 EXCEPT SELECT b,c,a FROM t31113 ORDER BY c1114 }1115} {1 a a 9.9 b B {} C c}1116do_test selectA-3.71 {1117 execsql {1118 SELECT a,b,c FROM t1 WHERE b<'d'1119 INTERSECT SELECT a,b,c FROM t11120 INTERSECT SELECT a,b,c FROM t31121 EXCEPT SELECT b,c,a FROM t31122 INTERSECT SELECT a,b,c FROM t11123 EXCEPT SELECT x,y,z FROM t21124 INTERSECT SELECT a,b,c FROM t31125 EXCEPT SELECT y,x,z FROM t21126 INTERSECT SELECT a,b,c FROM t11127 EXCEPT SELECT c,b,a FROM t31128 ORDER BY c1129 }1130} {1 a a 9.9 b B {} C c}1131do_test selectA-3.72 {1132 execsql {1133 SELECT a,b,c FROM t3 UNION SELECT x,y,z FROM t21134 ORDER BY a,b,c1135 }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}1137do_test selectA-3.73 {1138 execsql {1139 SELECT a,b,c FROM t3 UNION SELECT x,y,z FROM t21140 ORDER BY a DESC,b,c1141 }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}1143do_test selectA-3.74 {1144 execsql {1145 SELECT a,b,c FROM t3 UNION SELECT x,y,z FROM t21146 ORDER BY a,c,b1147 }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}1149do_test selectA-3.75 {1150 execsql {1151 SELECT a,b,c FROM t3 UNION SELECT x,y,z FROM t21152 ORDER BY b,a,c1153 }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}1155do_test selectA-3.76 {1156 execsql {1157 SELECT a,b,c FROM t3 UNION SELECT x,y,z FROM t21158 ORDER BY b COLLATE NOCASE,a,c1159 }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}1161do_test selectA-3.77 {1162 execsql {1163 SELECT a,b,c FROM t3 UNION SELECT x,y,z FROM t21164 ORDER BY b COLLATE NOCASE DESC,a,c1165 }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}1167do_test selectA-3.78 {1168 execsql {1169 SELECT a,b,c FROM t3 UNION SELECT x,y,z FROM t21170 ORDER BY c,b,a1171 }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}1173do_test selectA-3.79 {1174 execsql {1175 SELECT a,b,c FROM t3 UNION SELECT x,y,z FROM t21176 ORDER BY c,a,b1177 }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}1179do_test selectA-3.80 {1180 execsql {1181 SELECT a,b,c FROM t3 UNION SELECT x,y,z FROM t21182 ORDER BY c DESC,a,b1183 }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}1185do_test selectA-3.81 {1186 execsql {1187 SELECT a,b,c FROM t3 UNION SELECT x,y,z FROM t21188 ORDER BY c COLLATE BINARY DESC,a,b1189 }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}1191do_test selectA-3.82 {1192 execsql {1193 SELECT x,y,z FROM t2 UNION SELECT a,b,c FROM t31194 ORDER BY a,b,c1195 }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}1197do_test selectA-3.83 {1198 execsql {1199 SELECT x,y,z FROM t2 UNION SELECT a,b,c FROM t31200 ORDER BY a DESC,b,c