Annotation of embedaddon/sqlite3/test/selectA.test, revision 1.1
1.1 ! misho 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 hare 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 hare 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 hare 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 hare 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 hare 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 hare 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 hare 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 hare 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 hare 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 hare 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 hare 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 hare 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 hare 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 hare 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 hare 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 hare 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 hare 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 hare 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 hare 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 hare 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 hare 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 hare 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 hare 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 hare 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 hare 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 hare 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 hare 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 hare 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 hare 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 hare 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 hare 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 hare 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 hare 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 hare 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 hare 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 hare 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 hare 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 hare 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 hare 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
FreeBSD-CVSweb <freebsd-cvsweb@FreeBSD.org>
![[BACK]](/icons/cvsweb/back.gif){kind=icon}
Return to selectA.test CVS log ![[TXT]](/icons/cvsweb/text.gif){kind=icon}
![[DIR]](/icons/cvsweb/dir.gif){kind=icon}
Up to [ELWIX - Embedded LightWeight unIX -] / embedaddon / sqlite3 / test