| OLD | NEW |
|---|
| (Empty) | |
| 1 # 2008 June 24 |
| 2 # |
| 3 # The author disclaims copyright to this source code. In place of |
| 4 # a legal notice, here is a blessing: |
| 5 # |
| 6 # May you do good and not evil. |
| 7 # May you find forgiveness for yourself and forgive others. |
| 8 # May you share freely, never taking more than you give. |
| 9 # |
| 10 #*********************************************************************** |
| 11 # This file implements regression tests for SQLite library. |
| 12 # |
| 13 # The focus of this file is testing the compound-SELECT merge |
| 14 # optimization. Or, in other words, making sure that all |
| 15 # possible combinations of UNION, UNION ALL, EXCEPT, and |
| 16 # INTERSECT work together with an ORDER BY clause (with or w/o |
| 17 # explicit sort order and explicit collating secquites) and |
| 18 # with and without optional LIMIT and OFFSET clauses. |
| 19 # |
| 20 # $Id: selectA.test,v 1.6 2008/08/21 14:24:29 drh Exp $ |
| 21 |
| 22 set testdir [file dirname $argv0] |
| 23 source $testdir/tester.tcl |
| 24 |
| 25 ifcapable !compound { |
| 26 finish_test |
| 27 return |
| 28 } |
| 29 |
| 30 do_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} |
| 41 do_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} |
| 52 do_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 |
| 65 do_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 h
are m M} |
| 71 do_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 h
are m M} |
| 77 do_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 h
are m M} |
| 83 do_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} |
| 89 do_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 h
are m M} |
| 95 do_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 h
are m M} |
| 101 do_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} |
| 107 do_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} |
| 113 do_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} |
| 119 do_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} |
| 125 do_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} |
| 131 do_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} |
| 137 do_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 h
are m M} |
| 143 do_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} |
| 149 do_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 h
are m M} |
| 155 do_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 h
are m M} |
| 161 do_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} |
| 167 do_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} |
| 173 do_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} |
| 179 do_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} |
| 185 do_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} |
| 191 do_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} |
| 197 do_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 h
are m M} |
| 203 do_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} |
| 209 do_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 h
are m M} |
| 215 do_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 h
are m M} |
| 221 do_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} |
| 227 do_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} |
| 233 do_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} |
| 239 do_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} |
| 245 do_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} |
| 251 do_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} |
| 257 do_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 h
are m M} |
| 263 do_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} |
| 269 do_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 h
are m M} |
| 275 do_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 h
are m M} |
| 281 do_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} |
| 287 do_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} |
| 293 do_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} |
| 299 do_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} |
| 305 do_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} |
| 311 do_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} |
| 317 do_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} |
| 323 do_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} |
| 329 do_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} |
| 335 do_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} |
| 341 do_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} |
| 347 do_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} |
| 353 do_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} |
| 359 do_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} |
| 365 do_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} |
| 371 do_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} |
| 377 do_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} |
| 383 do_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} |
| 389 do_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} |
| 395 do_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} |
| 401 do_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} |
| 407 do_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} |
| 413 do_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} |
| 419 do_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} |
| 425 do_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} |
| 431 do_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} |
| 437 do_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} |
| 443 do_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} |
| 449 do_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} |
| 455 do_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} |
| 461 do_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} |
| 467 do_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} |
| 473 do_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} |
| 479 do_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} |
| 487 do_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} |
| 495 do_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} |
| 503 do_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} |
| 518 do_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 h
are m M} |
| 524 do_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} |
| 530 do_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 h
are m M} |
| 536 do_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 h
are m M} |
| 542 do_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} |
| 548 do_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} |
| 554 do_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} |
| 560 do_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} |
| 566 do_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} |
| 572 do_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} |
| 578 do_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 h
are m M} |
| 584 do_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} |
| 590 do_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 h
are m M} |
| 596 do_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 h
are m M} |
| 602 do_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} |
| 608 do_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} |
| 614 do_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} |
| 620 do_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} |
| 626 do_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} |
| 632 do_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} |
| 638 do_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} |
| 650 do_test selectA-2.93 { |
| 651 execsql { |
| 652 SELECT upper((SELECT c FROM t1 UNION SELECT z FROM t2 ORDER BY 1)); |
| 653 } |
| 654 } {A} |
| 655 do_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} |
| 660 do_test selectA-2.95 { |
| 661 execsql { |
| 662 SELECT lower((SELECT c FROM t1 INTERSECT SELECT z FROM t2 ORDER BY 1)); |
| 663 } |
| 664 } {{}} |
| 665 do_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 |
| 672 do_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} |
| 683 do_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 h
are m M} |
| 689 do_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 h
are m M} |
| 695 do_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} |
| 701 do_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 h
are m M} |
| 707 do_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 h
are m M} |
| 713 do_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} |
| 719 do_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} |
| 725 do_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} |
| 731 do_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} |
| 737 do_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} |
| 743 do_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} |
| 749 do_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 h
are m M} |
| 755 do_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} |
| 761 do_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 h
are m M} |
| 767 do_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 h
are m M} |
| 773 do_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} |
| 779 do_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} |
| 785 do_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} |
| 791 do_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} |
| 797 do_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} |
| 803 do_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} |
| 809 do_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 h
are m M} |
| 815 do_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} |
| 821 do_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 h
are m M} |
| 827 do_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 h
are m M} |
| 833 do_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} |
| 839 do_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} |
| 845 do_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} |
| 851 do_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} |
| 857 do_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} |
| 863 do_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} |
| 869 do_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 h
are m M} |
| 875 do_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} |
| 881 do_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 h
are m M} |
| 887 do_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 h
are m M} |
| 893 do_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} |
| 899 do_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} |
| 905 do_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} |
| 911 do_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} |
| 917 do_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} |
| 923 do_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} |
| 929 do_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} |
| 935 do_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} |
| 941 do_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} |
| 947 do_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} |
| 953 do_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} |
| 959 do_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} |
| 965 do_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} |
| 971 do_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} |
| 977 do_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} |
| 983 do_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} |
| 989 do_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} |
| 995 do_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} |
| 1001 do_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} |
| 1007 do_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} |
| 1013 do_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} |
| 1019 do_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} |
| 1025 do_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} |
| 1031 do_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} |
| 1037 do_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} |
| 1043 do_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} |
| 1049 do_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} |
| 1055 do_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} |
| 1061 do_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} |
| 1067 do_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} |
| 1073 do_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} |
| 1079 do_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} |
| 1085 do_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} |
| 1091 do_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} |
| 1099 do_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} |
| 1107 do_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} |
| 1115 do_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} |
| 1130 do_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 h
are m M} |
| 1136 do_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} |
| 1142 do_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 h
are m M} |
| 1148 do_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 h
are m M} |
| 1154 do_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} |
| 1160 do_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} |
| 1166 do_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} |
| 1172 do_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} |
| 1178 do_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} |
| 1184 do_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} |
| 1190 do_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 h
are m M} |
| 1196 do_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} |
| 1202 do_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 h
are m M} |
| 1208 do_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 h
are m M} |
| 1214 do_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} |
| 1220 do_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} |
| 1226 do_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} |
| 1232 do_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} |
| 1238 do_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} |
| 1244 do_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} |
| 1250 do_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} |
| 1262 do_test selectA-3.93 { |
| 1263 execsql { |
| 1264 SELECT upper((SELECT c FROM t1 UNION SELECT z FROM t2 ORDER BY 1)); |
| 1265 } |
| 1266 } {A} |
| 1267 do_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} |
| 1272 do_test selectA-3.95 { |
| 1273 execsql { |
| 1274 SELECT lower((SELECT c FROM t1 INTERSECT SELECT z FROM t2 ORDER BY 1)); |
| 1275 } |
| 1276 } {{}} |
| 1277 do_test selectA-3.96 { |
| 1278 execsql { |
| 1279 SELECT lower((SELECT z FROM t2 EXCEPT SELECT c FROM t1 ORDER BY 1)); |
| 1280 } |
| 1281 } {m} |
| 1282 do_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 |
| 1296 finish_test |
| OLD | NEW |
|---|
Chromium Code Reviews
Issue 694353003:
Get `gn gen` to succeed on Windows (Closed)
Base URL: https://github.com/domokit/mojo.git@master