1.1 --- /dev/null Thu Jan 01 00:00:00 1970 +0000
1.2 +++ b/os/persistentdata/persistentstorage/sqlite3api/TEST/TclScript/selectA.test Fri Jun 15 03:10:57 2012 +0200
1.3 @@ -0,0 +1,1296 @@
1.4 +# 2008 June 24
1.5 +#
1.6 +# The author disclaims copyright to this source code. In place of
1.7 +# a legal notice, here is a blessing:
1.8 +#
1.9 +# May you do good and not evil.
1.10 +# May you find forgiveness for yourself and forgive others.
1.11 +# May you share freely, never taking more than you give.
1.12 +#
1.13 +#***********************************************************************
1.14 +# This file implements regression tests for SQLite library.
1.15 +#
1.16 +# The focus of this file is testing the compound-SELECT merge
1.17 +# optimization. Or, in other words, making sure that all
1.18 +# possible combinations of UNION, UNION ALL, EXCEPT, and
1.19 +# INTERSECT work together with an ORDER BY clause (with or w/o
1.20 +# explicit sort order and explicit collating secquites) and
1.21 +# with and without optional LIMIT and OFFSET clauses.
1.22 +#
1.23 +# $Id: selectA.test,v 1.6 2008/08/21 14:24:29 drh Exp $
1.24 +
1.25 +set testdir [file dirname $argv0]
1.26 +source $testdir/tester.tcl
1.27 +
1.28 +ifcapable !compound {
1.29 + finish_test
1.30 + return
1.31 +}
1.32 +
1.33 +do_test selectA-1.0 {
1.34 + execsql {
1.35 + CREATE TABLE t1(a,b,c COLLATE NOCASE);
1.36 + INSERT INTO t1 VALUES(1,'a','a');
1.37 + INSERT INTO t1 VALUES(9.9, 'b', 'B');
1.38 + INSERT INTO t1 VALUES(NULL, 'C', 'c');
1.39 + INSERT INTO t1 VALUES('hello', 'd', 'D');
1.40 + INSERT INTO t1 VALUES(x'616263', 'e', 'e');
1.41 + SELECT * FROM t1;
1.42 + }
1.43 +} {1 a a 9.9 b B {} C c hello d D abc e e}
1.44 +do_test selectA-1.1 {
1.45 + execsql {
1.46 + CREATE TABLE t2(x,y,z COLLATE NOCASE);
1.47 + INSERT INTO t2 VALUES(NULL,'U','u');
1.48 + INSERT INTO t2 VALUES('mad', 'Z', 'z');
1.49 + INSERT INTO t2 VALUES(x'68617265', 'm', 'M');
1.50 + INSERT INTO t2 VALUES(5.2e6, 'X', 'x');
1.51 + INSERT INTO t2 VALUES(-23, 'Y', 'y');
1.52 + SELECT * FROM t2;
1.53 + }
1.54 +} {{} U u mad Z z hare m M 5200000.0 X x -23 Y y}
1.55 +do_test selectA-1.2 {
1.56 + execsql {
1.57 + CREATE TABLE t3(a,b,c COLLATE NOCASE);
1.58 + INSERT INTO t3 SELECT * FROM t1;
1.59 + INSERT INTO t3 SELECT * FROM t2;
1.60 + INSERT INTO t3 SELECT * FROM t1;
1.61 + INSERT INTO t3 SELECT * FROM t2;
1.62 + INSERT INTO t3 SELECT * FROM t1;
1.63 + INSERT INTO t3 SELECT * FROM t2;
1.64 + SELECT count(*) FROM t3;
1.65 + }
1.66 +} {30}
1.67 +
1.68 +do_test selectA-2.1 {
1.69 + execsql {
1.70 + SELECT a,b,c FROM t1 UNION ALL SELECT x,y,z FROM t2
1.71 + ORDER BY a,b,c
1.72 + }
1.73 +} {{} 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}
1.74 +do_test selectA-2.1.1 { # Ticket #3314
1.75 + execsql {
1.76 + SELECT t1.a, t1.b, t1.c FROM t1 UNION ALL SELECT x,y,z FROM t2
1.77 + ORDER BY a,b,c
1.78 + }
1.79 +} {{} 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}
1.80 +do_test selectA-2.1.2 { # Ticket #3314
1.81 + execsql {
1.82 + SELECT a,b,c FROM t1 UNION ALL SELECT x,y,z FROM t2
1.83 + ORDER BY t1.a, t1.b, t1.c
1.84 + }
1.85 +} {{} 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}
1.86 +do_test selectA-2.2 {
1.87 + execsql {
1.88 + SELECT a,b,c FROM t1 UNION ALL SELECT x,y,z FROM t2
1.89 + ORDER BY a DESC,b,c
1.90 + }
1.91 +} {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}
1.92 +do_test selectA-2.3 {
1.93 + execsql {
1.94 + SELECT a,b,c FROM t1 UNION ALL SELECT x,y,z FROM t2
1.95 + ORDER BY a,c,b
1.96 + }
1.97 +} {{} 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}
1.98 +do_test selectA-2.4 {
1.99 + execsql {
1.100 + SELECT a,b,c FROM t1 UNION ALL SELECT x,y,z FROM t2
1.101 + ORDER BY b,a,c
1.102 + }
1.103 +} {{} 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}
1.104 +do_test selectA-2.5 {
1.105 + execsql {
1.106 + SELECT a,b,c FROM t1 UNION ALL SELECT x,y,z FROM t2
1.107 + ORDER BY b COLLATE NOCASE,a,c
1.108 + }
1.109 +} {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}
1.110 +do_test selectA-2.6 {
1.111 + execsql {
1.112 + SELECT a,b,c FROM t1 UNION ALL SELECT x,y,z FROM t2
1.113 + ORDER BY b COLLATE NOCASE DESC,a,c
1.114 + }
1.115 +} {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}
1.116 +do_test selectA-2.7 {
1.117 + execsql {
1.118 + SELECT a,b,c FROM t1 UNION ALL SELECT x,y,z FROM t2
1.119 + ORDER BY c,b,a
1.120 + }
1.121 +} {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}
1.122 +do_test selectA-2.8 {
1.123 + execsql {
1.124 + SELECT a,b,c FROM t1 UNION ALL SELECT x,y,z FROM t2
1.125 + ORDER BY c,a,b
1.126 + }
1.127 +} {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}
1.128 +do_test selectA-2.9 {
1.129 + execsql {
1.130 + SELECT a,b,c FROM t1 UNION ALL SELECT x,y,z FROM t2
1.131 + ORDER BY c DESC,a,b
1.132 + }
1.133 +} {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}
1.134 +do_test selectA-2.10 {
1.135 + execsql {
1.136 + SELECT a,b,c FROM t1 UNION ALL SELECT x,y,z FROM t2
1.137 + ORDER BY c COLLATE BINARY DESC,a,b
1.138 + }
1.139 +} {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}
1.140 +do_test selectA-2.11 {
1.141 + execsql {
1.142 + SELECT x,y,z FROM t2 UNION ALL SELECT a,b,c FROM t1
1.143 + ORDER BY a,b,c
1.144 + }
1.145 +} {{} 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}
1.146 +do_test selectA-2.12 {
1.147 + execsql {
1.148 + SELECT x,y,z FROM t2 UNION ALL SELECT a,b,c FROM t1
1.149 + ORDER BY a DESC,b,c
1.150 + }
1.151 +} {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}
1.152 +do_test selectA-2.13 {
1.153 + execsql {
1.154 + SELECT x,y,z FROM t2 UNION ALL SELECT a,b,c FROM t1
1.155 + ORDER BY a,c,b
1.156 + }
1.157 +} {{} 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}
1.158 +do_test selectA-2.14 {
1.159 + execsql {
1.160 + SELECT x,y,z FROM t2 UNION ALL SELECT a,b,c FROM t1
1.161 + ORDER BY b,a,c
1.162 + }
1.163 +} {{} 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}
1.164 +do_test selectA-2.15 {
1.165 + execsql {
1.166 + SELECT x,y,z FROM t2 UNION ALL SELECT a,b,c FROM t1
1.167 + ORDER BY b COLLATE NOCASE,a,c
1.168 + }
1.169 +} {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}
1.170 +do_test selectA-2.16 {
1.171 + execsql {
1.172 + SELECT x,y,z FROM t2 UNION ALL SELECT a,b,c FROM t1
1.173 + ORDER BY b COLLATE NOCASE DESC,a,c
1.174 + }
1.175 +} {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}
1.176 +do_test selectA-2.17 {
1.177 + execsql {
1.178 + SELECT x,y,z FROM t2 UNION ALL SELECT a,b,c FROM t1
1.179 + ORDER BY c,b,a
1.180 + }
1.181 +} {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}
1.182 +do_test selectA-2.18 {
1.183 + execsql {
1.184 + SELECT x,y,z FROM t2 UNION ALL SELECT a,b,c FROM t1
1.185 + ORDER BY c,a,b
1.186 + }
1.187 +} {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}
1.188 +do_test selectA-2.19 {
1.189 + execsql {
1.190 + SELECT x,y,z FROM t2 UNION ALL SELECT a,b,c FROM t1
1.191 + ORDER BY c DESC,a,b
1.192 + }
1.193 +} {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}
1.194 +do_test selectA-2.20 {
1.195 + execsql {
1.196 + SELECT x,y,z FROM t2 UNION ALL SELECT a,b,c FROM t1
1.197 + ORDER BY c COLLATE BINARY DESC,a,b
1.198 + }
1.199 +} {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}
1.200 +do_test selectA-2.21 {
1.201 + execsql {
1.202 + SELECT a,b,c FROM t1 UNION SELECT x,y,z FROM t2
1.203 + ORDER BY a,b,c
1.204 + }
1.205 +} {{} 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}
1.206 +do_test selectA-2.22 {
1.207 + execsql {
1.208 + SELECT a,b,c FROM t1 UNION SELECT x,y,z FROM t2
1.209 + ORDER BY a DESC,b,c
1.210 + }
1.211 +} {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}
1.212 +do_test selectA-2.23 {
1.213 + execsql {
1.214 + SELECT a,b,c FROM t1 UNION SELECT x,y,z FROM t2
1.215 + ORDER BY a,c,b
1.216 + }
1.217 +} {{} 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}
1.218 +do_test selectA-2.24 {
1.219 + execsql {
1.220 + SELECT a,b,c FROM t1 UNION SELECT x,y,z FROM t2
1.221 + ORDER BY b,a,c
1.222 + }
1.223 +} {{} 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}
1.224 +do_test selectA-2.25 {
1.225 + execsql {
1.226 + SELECT a,b,c FROM t1 UNION SELECT x,y,z FROM t2
1.227 + ORDER BY b COLLATE NOCASE,a,c
1.228 + }
1.229 +} {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}
1.230 +do_test selectA-2.26 {
1.231 + execsql {
1.232 + SELECT a,b,c FROM t1 UNION SELECT x,y,z FROM t2
1.233 + ORDER BY b COLLATE NOCASE DESC,a,c
1.234 + }
1.235 +} {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}
1.236 +do_test selectA-2.27 {
1.237 + execsql {
1.238 + SELECT a,b,c FROM t1 UNION SELECT x,y,z FROM t2
1.239 + ORDER BY c,b,a
1.240 + }
1.241 +} {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}
1.242 +do_test selectA-2.28 {
1.243 + execsql {
1.244 + SELECT a,b,c FROM t1 UNION SELECT x,y,z FROM t2
1.245 + ORDER BY c,a,b
1.246 + }
1.247 +} {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}
1.248 +do_test selectA-2.29 {
1.249 + execsql {
1.250 + SELECT a,b,c FROM t1 UNION SELECT x,y,z FROM t2
1.251 + ORDER BY c DESC,a,b
1.252 + }
1.253 +} {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}
1.254 +do_test selectA-2.30 {
1.255 + execsql {
1.256 + SELECT a,b,c FROM t1 UNION SELECT x,y,z FROM t2
1.257 + ORDER BY c COLLATE BINARY DESC,a,b
1.258 + }
1.259 +} {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}
1.260 +do_test selectA-2.31 {
1.261 + execsql {
1.262 + SELECT x,y,z FROM t2 UNION SELECT a,b,c FROM t1
1.263 + ORDER BY a,b,c
1.264 + }
1.265 +} {{} 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}
1.266 +do_test selectA-2.32 {
1.267 + execsql {
1.268 + SELECT x,y,z FROM t2 UNION SELECT a,b,c FROM t1
1.269 + ORDER BY a DESC,b,c
1.270 + }
1.271 +} {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}
1.272 +do_test selectA-2.33 {
1.273 + execsql {
1.274 + SELECT x,y,z FROM t2 UNION SELECT a,b,c FROM t1
1.275 + ORDER BY a,c,b
1.276 + }
1.277 +} {{} 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}
1.278 +do_test selectA-2.34 {
1.279 + execsql {
1.280 + SELECT x,y,z FROM t2 UNION SELECT a,b,c FROM t1
1.281 + ORDER BY b,a,c
1.282 + }
1.283 +} {{} 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}
1.284 +do_test selectA-2.35 {
1.285 + execsql {
1.286 + SELECT x,y,z FROM t2 UNION SELECT a,b,c FROM t1
1.287 + ORDER BY b COLLATE NOCASE,a,c
1.288 + }
1.289 +} {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}
1.290 +do_test selectA-2.36 {
1.291 + execsql {
1.292 + SELECT x,y,z FROM t2 UNION SELECT a,b,c FROM t1
1.293 + ORDER BY b COLLATE NOCASE DESC,a,c
1.294 + }
1.295 +} {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}
1.296 +do_test selectA-2.37 {
1.297 + execsql {
1.298 + SELECT x,y,z FROM t2 UNION SELECT a,b,c FROM t1
1.299 + ORDER BY c,b,a
1.300 + }
1.301 +} {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}
1.302 +do_test selectA-2.38 {
1.303 + execsql {
1.304 + SELECT x,y,z FROM t2 UNION SELECT a,b,c FROM t1
1.305 + ORDER BY c,a,b
1.306 + }
1.307 +} {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}
1.308 +do_test selectA-2.39 {
1.309 + execsql {
1.310 + SELECT x,y,z FROM t2 UNION SELECT a,b,c FROM t1
1.311 + ORDER BY c DESC,a,b
1.312 + }
1.313 +} {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}
1.314 +do_test selectA-2.40 {
1.315 + execsql {
1.316 + SELECT x,y,z FROM t2 UNION SELECT a,b,c FROM t1
1.317 + ORDER BY c COLLATE BINARY DESC,a,b
1.318 + }
1.319 +} {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}
1.320 +do_test selectA-2.41 {
1.321 + execsql {
1.322 + SELECT a,b,c FROM t1 EXCEPT SELECT a,b,c FROM t1 WHERE b>='d'
1.323 + ORDER BY a,b,c
1.324 + }
1.325 +} {{} C c 1 a a 9.9 b B}
1.326 +do_test selectA-2.42 {
1.327 + execsql {
1.328 + SELECT a,b,c FROM t1 INTERSECT SELECT a,b,c FROM t1 WHERE b>='d'
1.329 + ORDER BY a,b,c
1.330 + }
1.331 +} {hello d D abc e e}
1.332 +do_test selectA-2.43 {
1.333 + execsql {
1.334 + SELECT a,b,c FROM t1 WHERE b>='d' INTERSECT SELECT a,b,c FROM t1
1.335 + ORDER BY a,b,c
1.336 + }
1.337 +} {hello d D abc e e}
1.338 +do_test selectA-2.44 {
1.339 + execsql {
1.340 + SELECT a,b,c FROM t1 EXCEPT SELECT a,b,c FROM t1 WHERE b<'d'
1.341 + ORDER BY a,b,c
1.342 + }
1.343 +} {hello d D abc e e}
1.344 +do_test selectA-2.45 {
1.345 + execsql {
1.346 + SELECT a,b,c FROM t1 INTERSECT SELECT a,b,c FROM t1 WHERE b<'d'
1.347 + ORDER BY a,b,c
1.348 + }
1.349 +} {{} C c 1 a a 9.9 b B}
1.350 +do_test selectA-2.46 {
1.351 + execsql {
1.352 + SELECT a,b,c FROM t1 WHERE b<'d' INTERSECT SELECT a,b,c FROM t1
1.353 + ORDER BY a,b,c
1.354 + }
1.355 +} {{} C c 1 a a 9.9 b B}
1.356 +do_test selectA-2.47 {
1.357 + execsql {
1.358 + SELECT a,b,c FROM t1 EXCEPT SELECT a,b,c FROM t1 WHERE b>='d'
1.359 + ORDER BY a DESC
1.360 + }
1.361 +} {9.9 b B 1 a a {} C c}
1.362 +do_test selectA-2.48 {
1.363 + execsql {
1.364 + SELECT a,b,c FROM t1 INTERSECT SELECT a,b,c FROM t1 WHERE b>='d'
1.365 + ORDER BY a DESC
1.366 + }
1.367 +} {abc e e hello d D}
1.368 +do_test selectA-2.49 {
1.369 + execsql {
1.370 + SELECT a,b,c FROM t1 WHERE b>='d' INTERSECT SELECT a,b,c FROM t1
1.371 + ORDER BY a DESC
1.372 + }
1.373 +} {abc e e hello d D}
1.374 +do_test selectA-2.50 {
1.375 + execsql {
1.376 + SELECT a,b,c FROM t1 EXCEPT SELECT a,b,c FROM t1 WHERE b<'d'
1.377 + ORDER BY a DESC
1.378 + }
1.379 +} {abc e e hello d D}
1.380 +do_test selectA-2.51 {
1.381 + execsql {
1.382 + SELECT a,b,c FROM t1 INTERSECT SELECT a,b,c FROM t1 WHERE b<'d'
1.383 + ORDER BY a DESC
1.384 + }
1.385 +} {9.9 b B 1 a a {} C c}
1.386 +do_test selectA-2.52 {
1.387 + execsql {
1.388 + SELECT a,b,c FROM t1 WHERE b<'d' INTERSECT SELECT a,b,c FROM t1
1.389 + ORDER BY a DESC
1.390 + }
1.391 +} {9.9 b B 1 a a {} C c}
1.392 +do_test selectA-2.53 {
1.393 + execsql {
1.394 + SELECT a,b,c FROM t1 EXCEPT SELECT a,b,c FROM t1 WHERE b>='d'
1.395 + ORDER BY b, a DESC
1.396 + }
1.397 +} {{} C c 1 a a 9.9 b B}
1.398 +do_test selectA-2.54 {
1.399 + execsql {
1.400 + SELECT a,b,c FROM t1 INTERSECT SELECT a,b,c FROM t1 WHERE b>='d'
1.401 + ORDER BY b
1.402 + }
1.403 +} {hello d D abc e e}
1.404 +do_test selectA-2.55 {
1.405 + execsql {
1.406 + SELECT a,b,c FROM t1 WHERE b>='d' INTERSECT SELECT a,b,c FROM t1
1.407 + ORDER BY b DESC, c
1.408 + }
1.409 +} {abc e e hello d D}
1.410 +do_test selectA-2.56 {
1.411 + execsql {
1.412 + SELECT a,b,c FROM t1 EXCEPT SELECT a,b,c FROM t1 WHERE b<'d'
1.413 + ORDER BY b, c DESC, a
1.414 + }
1.415 +} {hello d D abc e e}
1.416 +do_test selectA-2.57 {
1.417 + execsql {
1.418 + SELECT a,b,c FROM t1 INTERSECT SELECT a,b,c FROM t1 WHERE b<'d'
1.419 + ORDER BY b COLLATE NOCASE
1.420 + }
1.421 +} {1 a a 9.9 b B {} C c}
1.422 +do_test selectA-2.58 {
1.423 + execsql {
1.424 + SELECT a,b,c FROM t1 WHERE b<'d' INTERSECT SELECT a,b,c FROM t1
1.425 + ORDER BY b
1.426 + }
1.427 +} {{} C c 1 a a 9.9 b B}
1.428 +do_test selectA-2.59 {
1.429 + execsql {
1.430 + SELECT a,b,c FROM t1 EXCEPT SELECT a,b,c FROM t1 WHERE b>='d'
1.431 + ORDER BY c, a DESC
1.432 + }
1.433 +} {1 a a 9.9 b B {} C c}
1.434 +do_test selectA-2.60 {
1.435 + execsql {
1.436 + SELECT a,b,c FROM t1 INTERSECT SELECT a,b,c FROM t1 WHERE b>='d'
1.437 + ORDER BY c
1.438 + }
1.439 +} {hello d D abc e e}
1.440 +do_test selectA-2.61 {
1.441 + execsql {
1.442 + SELECT a,b,c FROM t1 WHERE b>='d' INTERSECT SELECT a,b,c FROM t1
1.443 + ORDER BY c COLLATE BINARY, b DESC, c, a, b, c, a, b, c
1.444 + }
1.445 +} {hello d D abc e e}
1.446 +do_test selectA-2.62 {
1.447 + execsql {
1.448 + SELECT a,b,c FROM t1 EXCEPT SELECT a,b,c FROM t1 WHERE b<'d'
1.449 + ORDER BY c DESC, a
1.450 + }
1.451 +} {abc e e hello d D}
1.452 +do_test selectA-2.63 {
1.453 + execsql {
1.454 + SELECT a,b,c FROM t1 INTERSECT SELECT a,b,c FROM t1 WHERE b<'d'
1.455 + ORDER BY c COLLATE NOCASE
1.456 + }
1.457 +} {1 a a 9.9 b B {} C c}
1.458 +do_test selectA-2.64 {
1.459 + execsql {
1.460 + SELECT a,b,c FROM t1 WHERE b<'d' INTERSECT SELECT a,b,c FROM t1
1.461 + ORDER BY c
1.462 + }
1.463 +} {1 a a 9.9 b B {} C c}
1.464 +do_test selectA-2.65 {
1.465 + execsql {
1.466 + SELECT a,b,c FROM t3 INTERSECT SELECT a,b,c FROM t1 WHERE b<'d'
1.467 + ORDER BY c COLLATE NOCASE
1.468 + }
1.469 +} {1 a a 9.9 b B {} C c}
1.470 +do_test selectA-2.66 {
1.471 + execsql {
1.472 + SELECT a,b,c FROM t1 WHERE b<'d' INTERSECT SELECT a,b,c FROM t3
1.473 + ORDER BY c
1.474 + }
1.475 +} {1 a a 9.9 b B {} C c}
1.476 +do_test selectA-2.67 {
1.477 + execsql {
1.478 + SELECT a,b,c FROM t1 EXCEPT SELECT a,b,c FROM t3 WHERE b<'d'
1.479 + ORDER BY c DESC, a
1.480 + }
1.481 +} {abc e e hello d D}
1.482 +do_test selectA-2.68 {
1.483 + execsql {
1.484 + SELECT a,b,c FROM t1 EXCEPT SELECT a,b,c FROM t1 WHERE b<'d'
1.485 + INTERSECT SELECT a,b,c FROM t3
1.486 + EXCEPT SELECT b,c,a FROM t3
1.487 + ORDER BY c DESC, a
1.488 + }
1.489 +} {abc e e hello d D}
1.490 +do_test selectA-2.69 {
1.491 + execsql {
1.492 + SELECT a,b,c FROM t1 INTERSECT SELECT a,b,c FROM t1 WHERE b<'d'
1.493 + INTERSECT SELECT a,b,c FROM t3
1.494 + EXCEPT SELECT b,c,a FROM t3
1.495 + ORDER BY c COLLATE NOCASE
1.496 + }
1.497 +} {1 a a 9.9 b B {} C c}
1.498 +do_test selectA-2.70 {
1.499 + execsql {
1.500 + SELECT a,b,c FROM t1 WHERE b<'d' INTERSECT SELECT a,b,c FROM t1
1.501 + INTERSECT SELECT a,b,c FROM t3
1.502 + EXCEPT SELECT b,c,a FROM t3
1.503 + ORDER BY c
1.504 + }
1.505 +} {1 a a 9.9 b B {} C c}
1.506 +do_test selectA-2.71 {
1.507 + execsql {
1.508 + SELECT a,b,c FROM t1 WHERE b<'d'
1.509 + INTERSECT SELECT a,b,c FROM t1
1.510 + INTERSECT SELECT a,b,c FROM t3
1.511 + EXCEPT SELECT b,c,a FROM t3
1.512 + INTERSECT SELECT a,b,c FROM t1
1.513 + EXCEPT SELECT x,y,z FROM t2
1.514 + INTERSECT SELECT a,b,c FROM t3
1.515 + EXCEPT SELECT y,x,z FROM t2
1.516 + INTERSECT SELECT a,b,c FROM t1
1.517 + EXCEPT SELECT c,b,a FROM t3
1.518 + ORDER BY c
1.519 + }
1.520 +} {1 a a 9.9 b B {} C c}
1.521 +do_test selectA-2.72 {
1.522 + execsql {
1.523 + SELECT a,b,c FROM t3 UNION SELECT x,y,z FROM t2
1.524 + ORDER BY a,b,c
1.525 + }
1.526 +} {{} 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}
1.527 +do_test selectA-2.73 {
1.528 + execsql {
1.529 + SELECT a,b,c FROM t3 UNION SELECT x,y,z FROM t2
1.530 + ORDER BY a DESC,b,c
1.531 + }
1.532 +} {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}
1.533 +do_test selectA-2.74 {
1.534 + execsql {
1.535 + SELECT a,b,c FROM t3 UNION SELECT x,y,z FROM t2
1.536 + ORDER BY a,c,b
1.537 + }
1.538 +} {{} 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}
1.539 +do_test selectA-2.75 {
1.540 + execsql {
1.541 + SELECT a,b,c FROM t3 UNION SELECT x,y,z FROM t2
1.542 + ORDER BY b,a,c
1.543 + }
1.544 +} {{} 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}
1.545 +do_test selectA-2.76 {
1.546 + execsql {
1.547 + SELECT a,b,c FROM t3 UNION SELECT x,y,z FROM t2
1.548 + ORDER BY b COLLATE NOCASE,a,c
1.549 + }
1.550 +} {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}
1.551 +do_test selectA-2.77 {
1.552 + execsql {
1.553 + SELECT a,b,c FROM t3 UNION SELECT x,y,z FROM t2
1.554 + ORDER BY b COLLATE NOCASE DESC,a,c
1.555 + }
1.556 +} {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}
1.557 +do_test selectA-2.78 {
1.558 + execsql {
1.559 + SELECT a,b,c FROM t3 UNION SELECT x,y,z FROM t2
1.560 + ORDER BY c,b,a
1.561 + }
1.562 +} {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}
1.563 +do_test selectA-2.79 {
1.564 + execsql {
1.565 + SELECT a,b,c FROM t3 UNION SELECT x,y,z FROM t2
1.566 + ORDER BY c,a,b
1.567 + }
1.568 +} {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}
1.569 +do_test selectA-2.80 {
1.570 + execsql {
1.571 + SELECT a,b,c FROM t3 UNION SELECT x,y,z FROM t2
1.572 + ORDER BY c DESC,a,b
1.573 + }
1.574 +} {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}
1.575 +do_test selectA-2.81 {
1.576 + execsql {
1.577 + SELECT a,b,c FROM t3 UNION SELECT x,y,z FROM t2
1.578 + ORDER BY c COLLATE BINARY DESC,a,b
1.579 + }
1.580 +} {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}
1.581 +do_test selectA-2.82 {
1.582 + execsql {
1.583 + SELECT x,y,z FROM t2 UNION SELECT a,b,c FROM t3
1.584 + ORDER BY a,b,c
1.585 + }
1.586 +} {{} 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}
1.587 +do_test selectA-2.83 {
1.588 + execsql {
1.589 + SELECT x,y,z FROM t2 UNION SELECT a,b,c FROM t3
1.590 + ORDER BY a DESC,b,c
1.591 + }
1.592 +} {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}
1.593 +do_test selectA-2.84 {
1.594 + execsql {
1.595 + SELECT x,y,z FROM t2 UNION SELECT a,b,c FROM t3
1.596 + ORDER BY a,c,b
1.597 + }
1.598 +} {{} 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}
1.599 +do_test selectA-2.85 {
1.600 + execsql {
1.601 + SELECT x,y,z FROM t2 UNION SELECT a,b,c FROM t3
1.602 + ORDER BY b,a,c
1.603 + }
1.604 +} {{} 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}
1.605 +do_test selectA-2.86 {
1.606 + execsql {
1.607 + SELECT x,y,z FROM t2 UNION SELECT a,b,c FROM t3
1.608 + ORDER BY b COLLATE NOCASE,a,c
1.609 + }
1.610 +} {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}
1.611 +do_test selectA-2.87 {
1.612 + execsql {
1.613 + SELECT x,y,z FROM t2 UNION SELECT a,b,c FROM t3
1.614 + ORDER BY y COLLATE NOCASE DESC,x,z
1.615 + }
1.616 +} {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}
1.617 +do_test selectA-2.88 {
1.618 + execsql {
1.619 + SELECT x,y,z FROM t2 UNION SELECT a,b,c FROM t3
1.620 + ORDER BY c,b,a
1.621 + }
1.622 +} {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}
1.623 +do_test selectA-2.89 {
1.624 + execsql {
1.625 + SELECT x,y,z FROM t2 UNION SELECT a,b,c FROM t3
1.626 + ORDER BY c,a,b
1.627 + }
1.628 +} {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}
1.629 +do_test selectA-2.90 {
1.630 + execsql {
1.631 + SELECT x,y,z FROM t2 UNION SELECT a,b,c FROM t3
1.632 + ORDER BY c DESC,a,b
1.633 + }
1.634 +} {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}
1.635 +do_test selectA-2.91 {
1.636 + execsql {
1.637 + SELECT x,y,z FROM t2 UNION SELECT a,b,c FROM t3
1.638 + ORDER BY c COLLATE BINARY DESC,a,b
1.639 + }
1.640 +} {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}
1.641 +do_test selectA-2.92 {
1.642 + execsql {
1.643 + SELECT x,y,z FROM t2
1.644 + INTERSECT SELECT a,b,c FROM t3
1.645 + EXCEPT SELECT c,b,a FROM t1
1.646 + UNION SELECT a,b,c FROM t3
1.647 + INTERSECT SELECT a,b,c FROM t3
1.648 + EXCEPT SELECT c,b,a FROM t1
1.649 + UNION SELECT a,b,c FROM t3
1.650 + ORDER BY y COLLATE NOCASE DESC,x,z
1.651 + }
1.652 +} {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}
1.653 +do_test selectA-2.93 {
1.654 + execsql {
1.655 + SELECT upper((SELECT c FROM t1 UNION SELECT z FROM t2 ORDER BY 1));
1.656 + }
1.657 +} {A}
1.658 +do_test selectA-2.94 {
1.659 + execsql {
1.660 + SELECT lower((SELECT c FROM t1 UNION ALL SELECT z FROM t2 ORDER BY 1));
1.661 + }
1.662 +} {a}
1.663 +do_test selectA-2.95 {
1.664 + execsql {
1.665 + SELECT lower((SELECT c FROM t1 INTERSECT SELECT z FROM t2 ORDER BY 1));
1.666 + }
1.667 +} {{}}
1.668 +do_test selectA-2.96 {
1.669 + execsql {
1.670 + SELECT lower((SELECT z FROM t2 EXCEPT SELECT c FROM t1 ORDER BY 1));
1.671 + }
1.672 +} {m}
1.673 +
1.674 +
1.675 +do_test selectA-3.0 {
1.676 + execsql {
1.677 + CREATE UNIQUE INDEX t1a ON t1(a);
1.678 + CREATE UNIQUE INDEX t1b ON t1(b);
1.679 + CREATE UNIQUE INDEX t1c ON t1(c);
1.680 + CREATE UNIQUE INDEX t2x ON t2(x);
1.681 + CREATE UNIQUE INDEX t2y ON t2(y);
1.682 + CREATE UNIQUE INDEX t2z ON t2(z);
1.683 + SELECT name FROM sqlite_master WHERE type='index'
1.684 + }
1.685 +} {t1a t1b t1c t2x t2y t2z}
1.686 +do_test selectA-3.1 {
1.687 + execsql {
1.688 + SELECT a,b,c FROM t1 UNION ALL SELECT x,y,z FROM t2
1.689 + ORDER BY a,b,c
1.690 + }
1.691 +} {{} 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}
1.692 +do_test selectA-3.1.1 { # Ticket #3314
1.693 + execsql {
1.694 + SELECT t1.a,b,t1.c FROM t1 UNION ALL SELECT x,y,z FROM t2
1.695 + ORDER BY a,t1.b,t1.c
1.696 + }
1.697 +} {{} 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}
1.698 +do_test selectA-3.2 {
1.699 + execsql {
1.700 + SELECT a,b,c FROM t1 UNION ALL SELECT x,y,z FROM t2
1.701 + ORDER BY a DESC,b,c
1.702 + }
1.703 +} {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}
1.704 +do_test selectA-3.3 {
1.705 + execsql {
1.706 + SELECT a,b,c FROM t1 UNION ALL SELECT x,y,z FROM t2
1.707 + ORDER BY a,c,b
1.708 + }
1.709 +} {{} 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}
1.710 +do_test selectA-3.4 {
1.711 + execsql {
1.712 + SELECT a,b,c FROM t1 UNION ALL SELECT x,y,z FROM t2
1.713 + ORDER BY b,a,c
1.714 + }
1.715 +} {{} 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}
1.716 +do_test selectA-3.5 {
1.717 + execsql {
1.718 + SELECT a,b,c FROM t1 UNION ALL SELECT x,y,z FROM t2
1.719 + ORDER BY b COLLATE NOCASE,a,c
1.720 + }
1.721 +} {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}
1.722 +do_test selectA-3.6 {
1.723 + execsql {
1.724 + SELECT a,b,c FROM t1 UNION ALL SELECT x,y,z FROM t2
1.725 + ORDER BY b COLLATE NOCASE DESC,a,c
1.726 + }
1.727 +} {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}
1.728 +do_test selectA-3.7 {
1.729 + execsql {
1.730 + SELECT a,b,c FROM t1 UNION ALL SELECT x,y,z FROM t2
1.731 + ORDER BY c,b,a
1.732 + }
1.733 +} {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}
1.734 +do_test selectA-3.8 {
1.735 + execsql {
1.736 + SELECT a,b,c FROM t1 UNION ALL SELECT x,y,z FROM t2
1.737 + ORDER BY c,a,b
1.738 + }
1.739 +} {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}
1.740 +do_test selectA-3.9 {
1.741 + execsql {
1.742 + SELECT a,b,c FROM t1 UNION ALL SELECT x,y,z FROM t2
1.743 + ORDER BY c DESC,a,b
1.744 + }
1.745 +} {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}
1.746 +do_test selectA-3.10 {
1.747 + execsql {
1.748 + SELECT a,b,c FROM t1 UNION ALL SELECT x,y,z FROM t2
1.749 + ORDER BY c COLLATE BINARY DESC,a,b
1.750 + }
1.751 +} {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}
1.752 +do_test selectA-3.11 {
1.753 + execsql {
1.754 + SELECT x,y,z FROM t2 UNION ALL SELECT a,b,c FROM t1
1.755 + ORDER BY a,b,c
1.756 + }
1.757 +} {{} 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}
1.758 +do_test selectA-3.12 {
1.759 + execsql {
1.760 + SELECT x,y,z FROM t2 UNION ALL SELECT a,b,c FROM t1
1.761 + ORDER BY a DESC,b,c
1.762 + }
1.763 +} {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}
1.764 +do_test selectA-3.13 {
1.765 + execsql {
1.766 + SELECT x,y,z FROM t2 UNION ALL SELECT a,b,c FROM t1
1.767 + ORDER BY a,c,b
1.768 + }
1.769 +} {{} 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}
1.770 +do_test selectA-3.14 {
1.771 + execsql {
1.772 + SELECT x,y,z FROM t2 UNION ALL SELECT a,b,c FROM t1
1.773 + ORDER BY b,a,c
1.774 + }
1.775 +} {{} 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}
1.776 +do_test selectA-3.15 {
1.777 + execsql {
1.778 + SELECT x,y,z FROM t2 UNION ALL SELECT a,b,c FROM t1
1.779 + ORDER BY b COLLATE NOCASE,a,c
1.780 + }
1.781 +} {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}
1.782 +do_test selectA-3.16 {
1.783 + execsql {
1.784 + SELECT x,y,z FROM t2 UNION ALL SELECT a,b,c FROM t1
1.785 + ORDER BY b COLLATE NOCASE DESC,a,c
1.786 + }
1.787 +} {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}
1.788 +do_test selectA-3.17 {
1.789 + execsql {
1.790 + SELECT x,y,z FROM t2 UNION ALL SELECT a,b,c FROM t1
1.791 + ORDER BY c,b,a
1.792 + }
1.793 +} {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}
1.794 +do_test selectA-3.18 {
1.795 + execsql {
1.796 + SELECT x,y,z FROM t2 UNION ALL SELECT a,b,c FROM t1
1.797 + ORDER BY c,a,b
1.798 + }
1.799 +} {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}
1.800 +do_test selectA-3.19 {
1.801 + execsql {
1.802 + SELECT x,y,z FROM t2 UNION ALL SELECT a,b,c FROM t1
1.803 + ORDER BY c DESC,a,b
1.804 + }
1.805 +} {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}
1.806 +do_test selectA-3.20 {
1.807 + execsql {
1.808 + SELECT x,y,z FROM t2 UNION ALL SELECT a,b,c FROM t1
1.809 + ORDER BY c COLLATE BINARY DESC,a,b
1.810 + }
1.811 +} {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}
1.812 +do_test selectA-3.21 {
1.813 + execsql {
1.814 + SELECT a,b,c FROM t1 UNION SELECT x,y,z FROM t2
1.815 + ORDER BY a,b,c
1.816 + }
1.817 +} {{} 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}
1.818 +do_test selectA-3.22 {
1.819 + execsql {
1.820 + SELECT a,b,c FROM t1 UNION SELECT x,y,z FROM t2
1.821 + ORDER BY a DESC,b,c
1.822 + }
1.823 +} {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}
1.824 +do_test selectA-3.23 {
1.825 + execsql {
1.826 + SELECT a,b,c FROM t1 UNION SELECT x,y,z FROM t2
1.827 + ORDER BY a,c,b
1.828 + }
1.829 +} {{} 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}
1.830 +do_test selectA-3.24 {
1.831 + execsql {
1.832 + SELECT a,b,c FROM t1 UNION SELECT x,y,z FROM t2
1.833 + ORDER BY b,a,c
1.834 + }
1.835 +} {{} 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}
1.836 +do_test selectA-3.25 {
1.837 + execsql {
1.838 + SELECT a,b,c FROM t1 UNION SELECT x,y,z FROM t2
1.839 + ORDER BY b COLLATE NOCASE,a,c
1.840 + }
1.841 +} {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}
1.842 +do_test selectA-3.26 {
1.843 + execsql {
1.844 + SELECT a,b,c FROM t1 UNION SELECT x,y,z FROM t2
1.845 + ORDER BY b COLLATE NOCASE DESC,a,c
1.846 + }
1.847 +} {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}
1.848 +do_test selectA-3.27 {
1.849 + execsql {
1.850 + SELECT a,b,c FROM t1 UNION SELECT x,y,z FROM t2
1.851 + ORDER BY c,b,a
1.852 + }
1.853 +} {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}
1.854 +do_test selectA-3.28 {
1.855 + execsql {
1.856 + SELECT a,b,c FROM t1 UNION SELECT x,y,z FROM t2
1.857 + ORDER BY c,a,b
1.858 + }
1.859 +} {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}
1.860 +do_test selectA-3.29 {
1.861 + execsql {
1.862 + SELECT a,b,c FROM t1 UNION SELECT x,y,z FROM t2
1.863 + ORDER BY c DESC,a,b
1.864 + }
1.865 +} {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}
1.866 +do_test selectA-3.30 {
1.867 + execsql {
1.868 + SELECT a,b,c FROM t1 UNION SELECT x,y,z FROM t2
1.869 + ORDER BY c COLLATE BINARY DESC,a,b
1.870 + }
1.871 +} {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}
1.872 +do_test selectA-3.31 {
1.873 + execsql {
1.874 + SELECT x,y,z FROM t2 UNION SELECT a,b,c FROM t1
1.875 + ORDER BY a,b,c
1.876 + }
1.877 +} {{} 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}
1.878 +do_test selectA-3.32 {
1.879 + execsql {
1.880 + SELECT x,y,z FROM t2 UNION SELECT a,b,c FROM t1
1.881 + ORDER BY a DESC,b,c
1.882 + }
1.883 +} {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}
1.884 +do_test selectA-3.33 {
1.885 + execsql {
1.886 + SELECT x,y,z FROM t2 UNION SELECT a,b,c FROM t1
1.887 + ORDER BY a,c,b
1.888 + }
1.889 +} {{} 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}
1.890 +do_test selectA-3.34 {
1.891 + execsql {
1.892 + SELECT x,y,z FROM t2 UNION SELECT a,b,c FROM t1
1.893 + ORDER BY b,a,c
1.894 + }
1.895 +} {{} 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}
1.896 +do_test selectA-3.35 {
1.897 + execsql {
1.898 + SELECT x,y,z FROM t2 UNION SELECT a,b,c FROM t1
1.899 + ORDER BY b COLLATE NOCASE,a,c
1.900 + }
1.901 +} {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}
1.902 +do_test selectA-3.36 {
1.903 + execsql {
1.904 + SELECT x,y,z FROM t2 UNION SELECT a,b,c FROM t1
1.905 + ORDER BY b COLLATE NOCASE DESC,a,c
1.906 + }
1.907 +} {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}
1.908 +do_test selectA-3.37 {
1.909 + execsql {
1.910 + SELECT x,y,z FROM t2 UNION SELECT a,b,c FROM t1
1.911 + ORDER BY c,b,a
1.912 + }
1.913 +} {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}
1.914 +do_test selectA-3.38 {
1.915 + execsql {
1.916 + SELECT x,y,z FROM t2 UNION SELECT a,b,c FROM t1
1.917 + ORDER BY c,a,b
1.918 + }
1.919 +} {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}
1.920 +do_test selectA-3.39 {
1.921 + execsql {
1.922 + SELECT x,y,z FROM t2 UNION SELECT a,b,c FROM t1
1.923 + ORDER BY c DESC,a,b
1.924 + }
1.925 +} {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}
1.926 +do_test selectA-3.40 {
1.927 + execsql {
1.928 + SELECT x,y,z FROM t2 UNION SELECT a,b,c FROM t1
1.929 + ORDER BY c COLLATE BINARY DESC,a,b
1.930 + }
1.931 +} {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}
1.932 +do_test selectA-3.41 {
1.933 + execsql {
1.934 + SELECT a,b,c FROM t1 EXCEPT SELECT a,b,c FROM t1 WHERE b>='d'
1.935 + ORDER BY a,b,c
1.936 + }
1.937 +} {{} C c 1 a a 9.9 b B}
1.938 +do_test selectA-3.42 {
1.939 + execsql {
1.940 + SELECT a,b,c FROM t1 INTERSECT SELECT a,b,c FROM t1 WHERE b>='d'
1.941 + ORDER BY a,b,c
1.942 + }
1.943 +} {hello d D abc e e}
1.944 +do_test selectA-3.43 {
1.945 + execsql {
1.946 + SELECT a,b,c FROM t1 WHERE b>='d' INTERSECT SELECT a,b,c FROM t1
1.947 + ORDER BY a,b,c
1.948 + }
1.949 +} {hello d D abc e e}
1.950 +do_test selectA-3.44 {
1.951 + execsql {
1.952 + SELECT a,b,c FROM t1 EXCEPT SELECT a,b,c FROM t1 WHERE b<'d'
1.953 + ORDER BY a,b,c
1.954 + }
1.955 +} {hello d D abc e e}
1.956 +do_test selectA-3.45 {
1.957 + execsql {
1.958 + SELECT a,b,c FROM t1 INTERSECT SELECT a,b,c FROM t1 WHERE b<'d'
1.959 + ORDER BY a,b,c
1.960 + }
1.961 +} {{} C c 1 a a 9.9 b B}
1.962 +do_test selectA-3.46 {
1.963 + execsql {
1.964 + SELECT a,b,c FROM t1 WHERE b<'d' INTERSECT SELECT a,b,c FROM t1
1.965 + ORDER BY a,b,c
1.966 + }
1.967 +} {{} C c 1 a a 9.9 b B}
1.968 +do_test selectA-3.47 {
1.969 + execsql {
1.970 + SELECT a,b,c FROM t1 EXCEPT SELECT a,b,c FROM t1 WHERE b>='d'
1.971 + ORDER BY a DESC
1.972 + }
1.973 +} {9.9 b B 1 a a {} C c}
1.974 +do_test selectA-3.48 {
1.975 + execsql {
1.976 + SELECT a,b,c FROM t1 INTERSECT SELECT a,b,c FROM t1 WHERE b>='d'
1.977 + ORDER BY a DESC
1.978 + }
1.979 +} {abc e e hello d D}
1.980 +do_test selectA-3.49 {
1.981 + execsql {
1.982 + SELECT a,b,c FROM t1 WHERE b>='d' INTERSECT SELECT a,b,c FROM t1
1.983 + ORDER BY a DESC
1.984 + }
1.985 +} {abc e e hello d D}
1.986 +do_test selectA-3.50 {
1.987 + execsql {
1.988 + SELECT a,b,c FROM t1 EXCEPT SELECT a,b,c FROM t1 WHERE b<'d'
1.989 + ORDER BY a DESC
1.990 + }
1.991 +} {abc e e hello d D}
1.992 +do_test selectA-3.51 {
1.993 + execsql {
1.994 + SELECT a,b,c FROM t1 INTERSECT SELECT a,b,c FROM t1 WHERE b<'d'
1.995 + ORDER BY a DESC
1.996 + }
1.997 +} {9.9 b B 1 a a {} C c}
1.998 +do_test selectA-3.52 {
1.999 + execsql {
1.1000 + SELECT a,b,c FROM t1 WHERE b<'d' INTERSECT SELECT a,b,c FROM t1
1.1001 + ORDER BY a DESC
1.1002 + }
1.1003 +} {9.9 b B 1 a a {} C c}
1.1004 +do_test selectA-3.53 {
1.1005 + execsql {
1.1006 + SELECT a,b,c FROM t1 EXCEPT SELECT a,b,c FROM t1 WHERE b>='d'
1.1007 + ORDER BY b, a DESC
1.1008 + }
1.1009 +} {{} C c 1 a a 9.9 b B}
1.1010 +do_test selectA-3.54 {
1.1011 + execsql {
1.1012 + SELECT a,b,c FROM t1 INTERSECT SELECT a,b,c FROM t1 WHERE b>='d'
1.1013 + ORDER BY b
1.1014 + }
1.1015 +} {hello d D abc e e}
1.1016 +do_test selectA-3.55 {
1.1017 + execsql {
1.1018 + SELECT a,b,c FROM t1 WHERE b>='d' INTERSECT SELECT a,b,c FROM t1
1.1019 + ORDER BY b DESC, c
1.1020 + }
1.1021 +} {abc e e hello d D}
1.1022 +do_test selectA-3.56 {
1.1023 + execsql {
1.1024 + SELECT a,b,c FROM t1 EXCEPT SELECT a,b,c FROM t1 WHERE b<'d'
1.1025 + ORDER BY b, c DESC, a
1.1026 + }
1.1027 +} {hello d D abc e e}
1.1028 +do_test selectA-3.57 {
1.1029 + execsql {
1.1030 + SELECT a,b,c FROM t1 INTERSECT SELECT a,b,c FROM t1 WHERE b<'d'
1.1031 + ORDER BY b COLLATE NOCASE
1.1032 + }
1.1033 +} {1 a a 9.9 b B {} C c}
1.1034 +do_test selectA-3.58 {
1.1035 + execsql {
1.1036 + SELECT a,b,c FROM t1 WHERE b<'d' INTERSECT SELECT a,b,c FROM t1
1.1037 + ORDER BY b
1.1038 + }
1.1039 +} {{} C c 1 a a 9.9 b B}
1.1040 +do_test selectA-3.59 {
1.1041 + execsql {
1.1042 + SELECT a,b,c FROM t1 EXCEPT SELECT a,b,c FROM t1 WHERE b>='d'
1.1043 + ORDER BY c, a DESC
1.1044 + }
1.1045 +} {1 a a 9.9 b B {} C c}
1.1046 +do_test selectA-3.60 {
1.1047 + execsql {
1.1048 + SELECT a,b,c FROM t1 INTERSECT SELECT a,b,c FROM t1 WHERE b>='d'
1.1049 + ORDER BY c
1.1050 + }
1.1051 +} {hello d D abc e e}
1.1052 +do_test selectA-3.61 {
1.1053 + execsql {
1.1054 + SELECT a,b,c FROM t1 WHERE b>='d' INTERSECT SELECT a,b,c FROM t1
1.1055 + ORDER BY c COLLATE BINARY, b DESC, c, a, b, c, a, b, c
1.1056 + }
1.1057 +} {hello d D abc e e}
1.1058 +do_test selectA-3.62 {
1.1059 + execsql {
1.1060 + SELECT a,b,c FROM t1 EXCEPT SELECT a,b,c FROM t1 WHERE b<'d'
1.1061 + ORDER BY c DESC, a
1.1062 + }
1.1063 +} {abc e e hello d D}
1.1064 +do_test selectA-3.63 {
1.1065 + execsql {
1.1066 + SELECT a,b,c FROM t1 INTERSECT SELECT a,b,c FROM t1 WHERE b<'d'
1.1067 + ORDER BY c COLLATE NOCASE
1.1068 + }
1.1069 +} {1 a a 9.9 b B {} C c}
1.1070 +do_test selectA-3.64 {
1.1071 + execsql {
1.1072 + SELECT a,b,c FROM t1 WHERE b<'d' INTERSECT SELECT a,b,c FROM t1
1.1073 + ORDER BY c
1.1074 + }
1.1075 +} {1 a a 9.9 b B {} C c}
1.1076 +do_test selectA-3.65 {
1.1077 + execsql {
1.1078 + SELECT a,b,c FROM t3 INTERSECT SELECT a,b,c FROM t1 WHERE b<'d'
1.1079 + ORDER BY c COLLATE NOCASE
1.1080 + }
1.1081 +} {1 a a 9.9 b B {} C c}
1.1082 +do_test selectA-3.66 {
1.1083 + execsql {
1.1084 + SELECT a,b,c FROM t1 WHERE b<'d' INTERSECT SELECT a,b,c FROM t3
1.1085 + ORDER BY c
1.1086 + }
1.1087 +} {1 a a 9.9 b B {} C c}
1.1088 +do_test selectA-3.67 {
1.1089 + execsql {
1.1090 + SELECT a,b,c FROM t1 EXCEPT SELECT a,b,c FROM t3 WHERE b<'d'
1.1091 + ORDER BY c DESC, a
1.1092 + }
1.1093 +} {abc e e hello d D}
1.1094 +do_test selectA-3.68 {
1.1095 + execsql {
1.1096 + SELECT a,b,c FROM t1 EXCEPT SELECT a,b,c FROM t1 WHERE b<'d'
1.1097 + INTERSECT SELECT a,b,c FROM t3
1.1098 + EXCEPT SELECT b,c,a FROM t3
1.1099 + ORDER BY c DESC, a
1.1100 + }
1.1101 +} {abc e e hello d D}
1.1102 +do_test selectA-3.69 {
1.1103 + execsql {
1.1104 + SELECT a,b,c FROM t1 INTERSECT SELECT a,b,c FROM t1 WHERE b<'d'
1.1105 + INTERSECT SELECT a,b,c FROM t3
1.1106 + EXCEPT SELECT b,c,a FROM t3
1.1107 + ORDER BY c COLLATE NOCASE
1.1108 + }
1.1109 +} {1 a a 9.9 b B {} C c}
1.1110 +do_test selectA-3.70 {
1.1111 + execsql {
1.1112 + SELECT a,b,c FROM t1 WHERE b<'d' INTERSECT SELECT a,b,c FROM t1
1.1113 + INTERSECT SELECT a,b,c FROM t3
1.1114 + EXCEPT SELECT b,c,a FROM t3
1.1115 + ORDER BY c
1.1116 + }
1.1117 +} {1 a a 9.9 b B {} C c}
1.1118 +do_test selectA-3.71 {
1.1119 + execsql {
1.1120 + SELECT a,b,c FROM t1 WHERE b<'d'
1.1121 + INTERSECT SELECT a,b,c FROM t1
1.1122 + INTERSECT SELECT a,b,c FROM t3
1.1123 + EXCEPT SELECT b,c,a FROM t3
1.1124 + INTERSECT SELECT a,b,c FROM t1
1.1125 + EXCEPT SELECT x,y,z FROM t2
1.1126 + INTERSECT SELECT a,b,c FROM t3
1.1127 + EXCEPT SELECT y,x,z FROM t2
1.1128 + INTERSECT SELECT a,b,c FROM t1
1.1129 + EXCEPT SELECT c,b,a FROM t3
1.1130 + ORDER BY c
1.1131 + }
1.1132 +} {1 a a 9.9 b B {} C c}
1.1133 +do_test selectA-3.72 {
1.1134 + execsql {
1.1135 + SELECT a,b,c FROM t3 UNION SELECT x,y,z FROM t2
1.1136 + ORDER BY a,b,c
1.1137 + }
1.1138 +} {{} 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}
1.1139 +do_test selectA-3.73 {
1.1140 + execsql {
1.1141 + SELECT a,b,c FROM t3 UNION SELECT x,y,z FROM t2
1.1142 + ORDER BY a DESC,b,c
1.1143 + }
1.1144 +} {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}
1.1145 +do_test selectA-3.74 {
1.1146 + execsql {
1.1147 + SELECT a,b,c FROM t3 UNION SELECT x,y,z FROM t2
1.1148 + ORDER BY a,c,b
1.1149 + }
1.1150 +} {{} 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}
1.1151 +do_test selectA-3.75 {
1.1152 + execsql {
1.1153 + SELECT a,b,c FROM t3 UNION SELECT x,y,z FROM t2
1.1154 + ORDER BY b,a,c
1.1155 + }
1.1156 +} {{} 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}
1.1157 +do_test selectA-3.76 {
1.1158 + execsql {
1.1159 + SELECT a,b,c FROM t3 UNION SELECT x,y,z FROM t2
1.1160 + ORDER BY b COLLATE NOCASE,a,c
1.1161 + }
1.1162 +} {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}
1.1163 +do_test selectA-3.77 {
1.1164 + execsql {
1.1165 + SELECT a,b,c FROM t3 UNION SELECT x,y,z FROM t2
1.1166 + ORDER BY b COLLATE NOCASE DESC,a,c
1.1167 + }
1.1168 +} {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}
1.1169 +do_test selectA-3.78 {
1.1170 + execsql {
1.1171 + SELECT a,b,c FROM t3 UNION SELECT x,y,z FROM t2
1.1172 + ORDER BY c,b,a
1.1173 + }
1.1174 +} {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}
1.1175 +do_test selectA-3.79 {
1.1176 + execsql {
1.1177 + SELECT a,b,c FROM t3 UNION SELECT x,y,z FROM t2
1.1178 + ORDER BY c,a,b
1.1179 + }
1.1180 +} {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}
1.1181 +do_test selectA-3.80 {
1.1182 + execsql {
1.1183 + SELECT a,b,c FROM t3 UNION SELECT x,y,z FROM t2
1.1184 + ORDER BY c DESC,a,b
1.1185 + }
1.1186 +} {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}
1.1187 +do_test selectA-3.81 {
1.1188 + execsql {
1.1189 + SELECT a,b,c FROM t3 UNION SELECT x,y,z FROM t2
1.1190 + ORDER BY c COLLATE BINARY DESC,a,b
1.1191 + }
1.1192 +} {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}
1.1193 +do_test selectA-3.82 {
1.1194 + execsql {
1.1195 + SELECT x,y,z FROM t2 UNION SELECT a,b,c FROM t3
1.1196 + ORDER BY a,b,c
1.1197 + }
1.1198 +} {{} 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}
1.1199 +do_test selectA-3.83 {
1.1200 + execsql {
1.1201 + SELECT x,y,z FROM t2 UNION SELECT a,b,c FROM t3
1.1202 + ORDER BY a DESC,b,c
1.1203 + }
1.1204 +} {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}
1.1205 +do_test selectA-3.84 {
1.1206 + execsql {
1.1207 + SELECT x,y,z FROM t2 UNION SELECT a,b,c FROM t3
1.1208 + ORDER BY a,c,b
1.1209 + }
1.1210 +} {{} 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}
1.1211 +do_test selectA-3.85 {
1.1212 + execsql {
1.1213 + SELECT x,y,z FROM t2 UNION SELECT a,b,c FROM t3
1.1214 + ORDER BY b,a,c
1.1215 + }
1.1216 +} {{} 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}
1.1217 +do_test selectA-3.86 {
1.1218 + execsql {
1.1219 + SELECT x,y,z FROM t2 UNION SELECT a,b,c FROM t3
1.1220 + ORDER BY b COLLATE NOCASE,a,c
1.1221 + }
1.1222 +} {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}
1.1223 +do_test selectA-3.87 {
1.1224 + execsql {
1.1225 + SELECT x,y,z FROM t2 UNION SELECT a,b,c FROM t3
1.1226 + ORDER BY y COLLATE NOCASE DESC,x,z
1.1227 + }
1.1228 +} {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}
1.1229 +do_test selectA-3.88 {
1.1230 + execsql {
1.1231 + SELECT x,y,z FROM t2 UNION SELECT a,b,c FROM t3
1.1232 + ORDER BY c,b,a
1.1233 + }
1.1234 +} {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}
1.1235 +do_test selectA-3.89 {
1.1236 + execsql {
1.1237 + SELECT x,y,z FROM t2 UNION SELECT a,b,c FROM t3
1.1238 + ORDER BY c,a,b
1.1239 + }
1.1240 +} {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}
1.1241 +do_test selectA-3.90 {
1.1242 + execsql {
1.1243 + SELECT x,y,z FROM t2 UNION SELECT a,b,c FROM t3
1.1244 + ORDER BY c DESC,a,b
1.1245 + }
1.1246 +} {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}
1.1247 +do_test selectA-3.91 {
1.1248 + execsql {
1.1249 + SELECT x,y,z FROM t2 UNION SELECT a,b,c FROM t3
1.1250 + ORDER BY c COLLATE BINARY DESC,a,b
1.1251 + }
1.1252 +} {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}
1.1253 +do_test selectA-3.92 {
1.1254 + execsql {
1.1255 + SELECT x,y,z FROM t2
1.1256 + INTERSECT SELECT a,b,c FROM t3
1.1257 + EXCEPT SELECT c,b,a FROM t1
1.1258 + UNION SELECT a,b,c FROM t3
1.1259 + INTERSECT SELECT a,b,c FROM t3
1.1260 + EXCEPT SELECT c,b,a FROM t1
1.1261 + UNION SELECT a,b,c FROM t3
1.1262 + ORDER BY y COLLATE NOCASE DESC,x,z
1.1263 + }
1.1264 +} {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}
1.1265 +do_test selectA-3.93 {
1.1266 + execsql {
1.1267 + SELECT upper((SELECT c FROM t1 UNION SELECT z FROM t2 ORDER BY 1));
1.1268 + }
1.1269 +} {A}
1.1270 +do_test selectA-3.94 {
1.1271 + execsql {
1.1272 + SELECT lower((SELECT c FROM t1 UNION ALL SELECT z FROM t2 ORDER BY 1));
1.1273 + }
1.1274 +} {a}
1.1275 +do_test selectA-3.95 {
1.1276 + execsql {
1.1277 + SELECT lower((SELECT c FROM t1 INTERSECT SELECT z FROM t2 ORDER BY 1));
1.1278 + }
1.1279 +} {{}}
1.1280 +do_test selectA-3.96 {
1.1281 + execsql {
1.1282 + SELECT lower((SELECT z FROM t2 EXCEPT SELECT c FROM t1 ORDER BY 1));
1.1283 + }
1.1284 +} {m}
1.1285 +do_test selectA-3.97 {
1.1286 + execsql {
1.1287 + SELECT upper((SELECT x FROM (
1.1288 + SELECT x,y,z FROM t2
1.1289 + INTERSECT SELECT a,b,c FROM t3
1.1290 + EXCEPT SELECT c,b,a FROM t1
1.1291 + UNION SELECT a,b,c FROM t3
1.1292 + INTERSECT SELECT a,b,c FROM t3
1.1293 + EXCEPT SELECT c,b,a FROM t1
1.1294 + UNION SELECT a,b,c FROM t3
1.1295 + ORDER BY y COLLATE NOCASE DESC,x,z)))
1.1296 + }
1.1297 +} {MAD}
1.1298 +
1.1299 +finish_test