Annotation of embedaddon/sqlite3/test/select4.test, revision 1.1
1.1 ! misho 1: # 2001 September 15
! 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. The
! 12: # focus of this file is testing UNION, INTERSECT and EXCEPT operators
! 13: # in SELECT statements.
! 14: #
! 15: # $Id: select4.test,v 1.30 2009/04/16 00:24:24 drh Exp $
! 16:
! 17: set testdir [file dirname $argv0]
! 18: source $testdir/tester.tcl
! 19:
! 20: # Most tests in this file depend on compound-select. But there are a couple
! 21: # right at the end that test DISTINCT, so we cannot omit the entire file.
! 22: #
! 23: ifcapable compound {
! 24:
! 25: # Build some test data
! 26: #
! 27: execsql {
! 28: CREATE TABLE t1(n int, log int);
! 29: BEGIN;
! 30: }
! 31: for {set i 1} {$i<32} {incr i} {
! 32: for {set j 0} {(1<<$j)<$i} {incr j} {}
! 33: execsql "INSERT INTO t1 VALUES($i,$j)"
! 34: }
! 35: execsql {
! 36: COMMIT;
! 37: }
! 38:
! 39: do_test select4-1.0 {
! 40: execsql {SELECT DISTINCT log FROM t1 ORDER BY log}
! 41: } {0 1 2 3 4 5}
! 42:
! 43: # Union All operator
! 44: #
! 45: do_test select4-1.1a {
! 46: lsort [execsql {SELECT DISTINCT log FROM t1}]
! 47: } {0 1 2 3 4 5}
! 48: do_test select4-1.1b {
! 49: lsort [execsql {SELECT n FROM t1 WHERE log=3}]
! 50: } {5 6 7 8}
! 51: do_test select4-1.1c {
! 52: execsql {
! 53: SELECT DISTINCT log FROM t1
! 54: UNION ALL
! 55: SELECT n FROM t1 WHERE log=3
! 56: ORDER BY log;
! 57: }
! 58: } {0 1 2 3 4 5 5 6 7 8}
! 59: do_test select4-1.1d {
! 60: execsql {
! 61: CREATE TABLE t2 AS
! 62: SELECT DISTINCT log FROM t1
! 63: UNION ALL
! 64: SELECT n FROM t1 WHERE log=3
! 65: ORDER BY log;
! 66: SELECT * FROM t2;
! 67: }
! 68: } {0 1 2 3 4 5 5 6 7 8}
! 69: execsql {DROP TABLE t2}
! 70: do_test select4-1.1e {
! 71: execsql {
! 72: CREATE TABLE t2 AS
! 73: SELECT DISTINCT log FROM t1
! 74: UNION ALL
! 75: SELECT n FROM t1 WHERE log=3
! 76: ORDER BY log DESC;
! 77: SELECT * FROM t2;
! 78: }
! 79: } {8 7 6 5 5 4 3 2 1 0}
! 80: execsql {DROP TABLE t2}
! 81: do_test select4-1.1f {
! 82: execsql {
! 83: SELECT DISTINCT log FROM t1
! 84: UNION ALL
! 85: SELECT n FROM t1 WHERE log=2
! 86: }
! 87: } {0 1 2 3 4 5 3 4}
! 88: do_test select4-1.1g {
! 89: execsql {
! 90: CREATE TABLE t2 AS
! 91: SELECT DISTINCT log FROM t1
! 92: UNION ALL
! 93: SELECT n FROM t1 WHERE log=2;
! 94: SELECT * FROM t2;
! 95: }
! 96: } {0 1 2 3 4 5 3 4}
! 97: execsql {DROP TABLE t2}
! 98: ifcapable subquery {
! 99: do_test select4-1.2 {
! 100: execsql {
! 101: SELECT log FROM t1 WHERE n IN
! 102: (SELECT DISTINCT log FROM t1 UNION ALL
! 103: SELECT n FROM t1 WHERE log=3)
! 104: ORDER BY log;
! 105: }
! 106: } {0 1 2 2 3 3 3 3}
! 107: }
! 108: do_test select4-1.3 {
! 109: set v [catch {execsql {
! 110: SELECT DISTINCT log FROM t1 ORDER BY log
! 111: UNION ALL
! 112: SELECT n FROM t1 WHERE log=3
! 113: ORDER BY log;
! 114: }} msg]
! 115: lappend v $msg
! 116: } {1 {ORDER BY clause should come after UNION ALL not before}}
! 117:
! 118: # Union operator
! 119: #
! 120: do_test select4-2.1 {
! 121: execsql {
! 122: SELECT DISTINCT log FROM t1
! 123: UNION
! 124: SELECT n FROM t1 WHERE log=3
! 125: ORDER BY log;
! 126: }
! 127: } {0 1 2 3 4 5 6 7 8}
! 128: ifcapable subquery {
! 129: do_test select4-2.2 {
! 130: execsql {
! 131: SELECT log FROM t1 WHERE n IN
! 132: (SELECT DISTINCT log FROM t1 UNION
! 133: SELECT n FROM t1 WHERE log=3)
! 134: ORDER BY log;
! 135: }
! 136: } {0 1 2 2 3 3 3 3}
! 137: }
! 138: do_test select4-2.3 {
! 139: set v [catch {execsql {
! 140: SELECT DISTINCT log FROM t1 ORDER BY log
! 141: UNION
! 142: SELECT n FROM t1 WHERE log=3
! 143: ORDER BY log;
! 144: }} msg]
! 145: lappend v $msg
! 146: } {1 {ORDER BY clause should come after UNION not before}}
! 147:
! 148: # Except operator
! 149: #
! 150: do_test select4-3.1.1 {
! 151: execsql {
! 152: SELECT DISTINCT log FROM t1
! 153: EXCEPT
! 154: SELECT n FROM t1 WHERE log=3
! 155: ORDER BY log;
! 156: }
! 157: } {0 1 2 3 4}
! 158: do_test select4-3.1.2 {
! 159: execsql {
! 160: CREATE TABLE t2 AS
! 161: SELECT DISTINCT log FROM t1
! 162: EXCEPT
! 163: SELECT n FROM t1 WHERE log=3
! 164: ORDER BY log;
! 165: SELECT * FROM t2;
! 166: }
! 167: } {0 1 2 3 4}
! 168: execsql {DROP TABLE t2}
! 169: do_test select4-3.1.3 {
! 170: execsql {
! 171: CREATE TABLE t2 AS
! 172: SELECT DISTINCT log FROM t1
! 173: EXCEPT
! 174: SELECT n FROM t1 WHERE log=3
! 175: ORDER BY log DESC;
! 176: SELECT * FROM t2;
! 177: }
! 178: } {4 3 2 1 0}
! 179: execsql {DROP TABLE t2}
! 180: ifcapable subquery {
! 181: do_test select4-3.2 {
! 182: execsql {
! 183: SELECT log FROM t1 WHERE n IN
! 184: (SELECT DISTINCT log FROM t1 EXCEPT
! 185: SELECT n FROM t1 WHERE log=3)
! 186: ORDER BY log;
! 187: }
! 188: } {0 1 2 2}
! 189: }
! 190: do_test select4-3.3 {
! 191: set v [catch {execsql {
! 192: SELECT DISTINCT log FROM t1 ORDER BY log
! 193: EXCEPT
! 194: SELECT n FROM t1 WHERE log=3
! 195: ORDER BY log;
! 196: }} msg]
! 197: lappend v $msg
! 198: } {1 {ORDER BY clause should come after EXCEPT not before}}
! 199:
! 200: # Intersect operator
! 201: #
! 202: do_test select4-4.1.1 {
! 203: execsql {
! 204: SELECT DISTINCT log FROM t1
! 205: INTERSECT
! 206: SELECT n FROM t1 WHERE log=3
! 207: ORDER BY log;
! 208: }
! 209: } {5}
! 210:
! 211: do_test select4-4.1.2 {
! 212: execsql {
! 213: SELECT DISTINCT log FROM t1
! 214: UNION ALL
! 215: SELECT 6
! 216: INTERSECT
! 217: SELECT n FROM t1 WHERE log=3
! 218: ORDER BY t1.log;
! 219: }
! 220: } {5 6}
! 221:
! 222: do_test select4-4.1.3 {
! 223: execsql {
! 224: CREATE TABLE t2 AS
! 225: SELECT DISTINCT log FROM t1 UNION ALL SELECT 6
! 226: INTERSECT
! 227: SELECT n FROM t1 WHERE log=3
! 228: ORDER BY log;
! 229: SELECT * FROM t2;
! 230: }
! 231: } {5 6}
! 232: execsql {DROP TABLE t2}
! 233: do_test select4-4.1.4 {
! 234: execsql {
! 235: CREATE TABLE t2 AS
! 236: SELECT DISTINCT log FROM t1 UNION ALL SELECT 6
! 237: INTERSECT
! 238: SELECT n FROM t1 WHERE log=3
! 239: ORDER BY log DESC;
! 240: SELECT * FROM t2;
! 241: }
! 242: } {6 5}
! 243: execsql {DROP TABLE t2}
! 244: ifcapable subquery {
! 245: do_test select4-4.2 {
! 246: execsql {
! 247: SELECT log FROM t1 WHERE n IN
! 248: (SELECT DISTINCT log FROM t1 INTERSECT
! 249: SELECT n FROM t1 WHERE log=3)
! 250: ORDER BY log;
! 251: }
! 252: } {3}
! 253: }
! 254: do_test select4-4.3 {
! 255: set v [catch {execsql {
! 256: SELECT DISTINCT log FROM t1 ORDER BY log
! 257: INTERSECT
! 258: SELECT n FROM t1 WHERE log=3
! 259: ORDER BY log;
! 260: }} msg]
! 261: lappend v $msg
! 262: } {1 {ORDER BY clause should come after INTERSECT not before}}
! 263:
! 264: # Various error messages while processing UNION or INTERSECT
! 265: #
! 266: do_test select4-5.1 {
! 267: set v [catch {execsql {
! 268: SELECT DISTINCT log FROM t2
! 269: UNION ALL
! 270: SELECT n FROM t1 WHERE log=3
! 271: ORDER BY log;
! 272: }} msg]
! 273: lappend v $msg
! 274: } {1 {no such table: t2}}
! 275: do_test select4-5.2 {
! 276: set v [catch {execsql {
! 277: SELECT DISTINCT log AS "xyzzy" FROM t1
! 278: UNION ALL
! 279: SELECT n FROM t1 WHERE log=3
! 280: ORDER BY xyzzy;
! 281: }} msg]
! 282: lappend v $msg
! 283: } {0 {0 1 2 3 4 5 5 6 7 8}}
! 284: do_test select4-5.2b {
! 285: set v [catch {execsql {
! 286: SELECT DISTINCT log AS xyzzy FROM t1
! 287: UNION ALL
! 288: SELECT n FROM t1 WHERE log=3
! 289: ORDER BY "xyzzy";
! 290: }} msg]
! 291: lappend v $msg
! 292: } {0 {0 1 2 3 4 5 5 6 7 8}}
! 293: do_test select4-5.2c {
! 294: set v [catch {execsql {
! 295: SELECT DISTINCT log FROM t1
! 296: UNION ALL
! 297: SELECT n FROM t1 WHERE log=3
! 298: ORDER BY "xyzzy";
! 299: }} msg]
! 300: lappend v $msg
! 301: } {1 {1st ORDER BY term does not match any column in the result set}}
! 302: do_test select4-5.2d {
! 303: set v [catch {execsql {
! 304: SELECT DISTINCT log FROM t1
! 305: INTERSECT
! 306: SELECT n FROM t1 WHERE log=3
! 307: ORDER BY "xyzzy";
! 308: }} msg]
! 309: lappend v $msg
! 310: } {1 {1st ORDER BY term does not match any column in the result set}}
! 311: do_test select4-5.2e {
! 312: set v [catch {execsql {
! 313: SELECT DISTINCT log FROM t1
! 314: UNION ALL
! 315: SELECT n FROM t1 WHERE log=3
! 316: ORDER BY n;
! 317: }} msg]
! 318: lappend v $msg
! 319: } {0 {0 1 2 3 4 5 5 6 7 8}}
! 320: do_test select4-5.2f {
! 321: catchsql {
! 322: SELECT DISTINCT log FROM t1
! 323: UNION ALL
! 324: SELECT n FROM t1 WHERE log=3
! 325: ORDER BY log;
! 326: }
! 327: } {0 {0 1 2 3 4 5 5 6 7 8}}
! 328: do_test select4-5.2g {
! 329: catchsql {
! 330: SELECT DISTINCT log FROM t1
! 331: UNION ALL
! 332: SELECT n FROM t1 WHERE log=3
! 333: ORDER BY 1;
! 334: }
! 335: } {0 {0 1 2 3 4 5 5 6 7 8}}
! 336: do_test select4-5.2h {
! 337: catchsql {
! 338: SELECT DISTINCT log FROM t1
! 339: UNION ALL
! 340: SELECT n FROM t1 WHERE log=3
! 341: ORDER BY 2;
! 342: }
! 343: } {1 {1st ORDER BY term out of range - should be between 1 and 1}}
! 344: do_test select4-5.2i {
! 345: catchsql {
! 346: SELECT DISTINCT 1, log FROM t1
! 347: UNION ALL
! 348: SELECT 2, n FROM t1 WHERE log=3
! 349: ORDER BY 2, 1;
! 350: }
! 351: } {0 {1 0 1 1 1 2 1 3 1 4 1 5 2 5 2 6 2 7 2 8}}
! 352: do_test select4-5.2j {
! 353: catchsql {
! 354: SELECT DISTINCT 1, log FROM t1
! 355: UNION ALL
! 356: SELECT 2, n FROM t1 WHERE log=3
! 357: ORDER BY 1, 2 DESC;
! 358: }
! 359: } {0 {1 5 1 4 1 3 1 2 1 1 1 0 2 8 2 7 2 6 2 5}}
! 360: do_test select4-5.2k {
! 361: catchsql {
! 362: SELECT DISTINCT 1, log FROM t1
! 363: UNION ALL
! 364: SELECT 2, n FROM t1 WHERE log=3
! 365: ORDER BY n, 1;
! 366: }
! 367: } {0 {1 0 1 1 1 2 1 3 1 4 1 5 2 5 2 6 2 7 2 8}}
! 368: do_test select4-5.3 {
! 369: set v [catch {execsql {
! 370: SELECT DISTINCT log, n FROM t1
! 371: UNION ALL
! 372: SELECT n FROM t1 WHERE log=3
! 373: ORDER BY log;
! 374: }} msg]
! 375: lappend v $msg
! 376: } {1 {SELECTs to the left and right of UNION ALL do not have the same number of result columns}}
! 377: do_test select4-5.3-3807-1 {
! 378: catchsql {
! 379: SELECT 1 UNION SELECT 2, 3 UNION SELECT 4, 5 ORDER BY 1;
! 380: }
! 381: } {1 {SELECTs to the left and right of UNION do not have the same number of result columns}}
! 382: do_test select4-5.4 {
! 383: set v [catch {execsql {
! 384: SELECT log FROM t1 WHERE n=2
! 385: UNION ALL
! 386: SELECT log FROM t1 WHERE n=3
! 387: UNION ALL
! 388: SELECT log FROM t1 WHERE n=4
! 389: UNION ALL
! 390: SELECT log FROM t1 WHERE n=5
! 391: ORDER BY log;
! 392: }} msg]
! 393: lappend v $msg
! 394: } {0 {1 2 2 3}}
! 395:
! 396: do_test select4-6.1 {
! 397: execsql {
! 398: SELECT log, count(*) as cnt FROM t1 GROUP BY log
! 399: UNION
! 400: SELECT log, n FROM t1 WHERE n=7
! 401: ORDER BY cnt, log;
! 402: }
! 403: } {0 1 1 1 2 2 3 4 3 7 4 8 5 15}
! 404: do_test select4-6.2 {
! 405: execsql {
! 406: SELECT log, count(*) FROM t1 GROUP BY log
! 407: UNION
! 408: SELECT log, n FROM t1 WHERE n=7
! 409: ORDER BY count(*), log;
! 410: }
! 411: } {0 1 1 1 2 2 3 4 3 7 4 8 5 15}
! 412:
! 413: # NULLs are indistinct for the UNION operator.
! 414: # Make sure the UNION operator recognizes this
! 415: #
! 416: do_test select4-6.3 {
! 417: execsql {
! 418: SELECT NULL UNION SELECT NULL UNION
! 419: SELECT 1 UNION SELECT 2 AS 'x'
! 420: ORDER BY x;
! 421: }
! 422: } {{} 1 2}
! 423: do_test select4-6.3.1 {
! 424: execsql {
! 425: SELECT NULL UNION ALL SELECT NULL UNION ALL
! 426: SELECT 1 UNION ALL SELECT 2 AS 'x'
! 427: ORDER BY x;
! 428: }
! 429: } {{} {} 1 2}
! 430:
! 431: # Make sure the DISTINCT keyword treats NULLs as indistinct.
! 432: #
! 433: ifcapable subquery {
! 434: do_test select4-6.4 {
! 435: execsql {
! 436: SELECT * FROM (
! 437: SELECT NULL, 1 UNION ALL SELECT NULL, 1
! 438: );
! 439: }
! 440: } {{} 1 {} 1}
! 441: do_test select4-6.5 {
! 442: execsql {
! 443: SELECT DISTINCT * FROM (
! 444: SELECT NULL, 1 UNION ALL SELECT NULL, 1
! 445: );
! 446: }
! 447: } {{} 1}
! 448: do_test select4-6.6 {
! 449: execsql {
! 450: SELECT DISTINCT * FROM (
! 451: SELECT 1,2 UNION ALL SELECT 1,2
! 452: );
! 453: }
! 454: } {1 2}
! 455: }
! 456:
! 457: # Test distinctness of NULL in other ways.
! 458: #
! 459: do_test select4-6.7 {
! 460: execsql {
! 461: SELECT NULL EXCEPT SELECT NULL
! 462: }
! 463: } {}
! 464:
! 465:
! 466: # Make sure column names are correct when a compound select appears as
! 467: # an expression in the WHERE clause.
! 468: #
! 469: do_test select4-7.1 {
! 470: execsql {
! 471: CREATE TABLE t2 AS SELECT log AS 'x', count(*) AS 'y' FROM t1 GROUP BY log;
! 472: SELECT * FROM t2 ORDER BY x;
! 473: }
! 474: } {0 1 1 1 2 2 3 4 4 8 5 15}
! 475: ifcapable subquery {
! 476: do_test select4-7.2 {
! 477: execsql2 {
! 478: SELECT * FROM t1 WHERE n IN (SELECT n FROM t1 INTERSECT SELECT x FROM t2)
! 479: ORDER BY n
! 480: }
! 481: } {n 1 log 0 n 2 log 1 n 3 log 2 n 4 log 2 n 5 log 3}
! 482: do_test select4-7.3 {
! 483: execsql2 {
! 484: SELECT * FROM t1 WHERE n IN (SELECT n FROM t1 EXCEPT SELECT x FROM t2)
! 485: ORDER BY n LIMIT 2
! 486: }
! 487: } {n 6 log 3 n 7 log 3}
! 488: do_test select4-7.4 {
! 489: execsql2 {
! 490: SELECT * FROM t1 WHERE n IN (SELECT n FROM t1 UNION SELECT x FROM t2)
! 491: ORDER BY n LIMIT 2
! 492: }
! 493: } {n 1 log 0 n 2 log 1}
! 494: } ;# ifcapable subquery
! 495:
! 496: } ;# ifcapable compound
! 497:
! 498: # Make sure DISTINCT works appropriately on TEXT and NUMERIC columns.
! 499: do_test select4-8.1 {
! 500: execsql {
! 501: BEGIN;
! 502: CREATE TABLE t3(a text, b float, c text);
! 503: INSERT INTO t3 VALUES(1, 1.1, '1.1');
! 504: INSERT INTO t3 VALUES(2, 1.10, '1.10');
! 505: INSERT INTO t3 VALUES(3, 1.10, '1.1');
! 506: INSERT INTO t3 VALUES(4, 1.1, '1.10');
! 507: INSERT INTO t3 VALUES(5, 1.2, '1.2');
! 508: INSERT INTO t3 VALUES(6, 1.3, '1.3');
! 509: COMMIT;
! 510: }
! 511: execsql {
! 512: SELECT DISTINCT b FROM t3 ORDER BY c;
! 513: }
! 514: } {1.1 1.2 1.3}
! 515: do_test select4-8.2 {
! 516: execsql {
! 517: SELECT DISTINCT c FROM t3 ORDER BY c;
! 518: }
! 519: } {1.1 1.10 1.2 1.3}
! 520:
! 521: # Make sure the names of columns are taken from the right-most subquery
! 522: # right in a compound query. Ticket #1721
! 523: #
! 524: ifcapable compound {
! 525:
! 526: do_test select4-9.1 {
! 527: execsql2 {
! 528: SELECT x, y FROM t2 UNION SELECT a, b FROM t3 ORDER BY x LIMIT 1
! 529: }
! 530: } {x 0 y 1}
! 531: do_test select4-9.2 {
! 532: execsql2 {
! 533: SELECT x, y FROM t2 UNION ALL SELECT a, b FROM t3 ORDER BY x LIMIT 1
! 534: }
! 535: } {x 0 y 1}
! 536: do_test select4-9.3 {
! 537: execsql2 {
! 538: SELECT x, y FROM t2 EXCEPT SELECT a, b FROM t3 ORDER BY x LIMIT 1
! 539: }
! 540: } {x 0 y 1}
! 541: do_test select4-9.4 {
! 542: execsql2 {
! 543: SELECT x, y FROM t2 INTERSECT SELECT 0 AS a, 1 AS b;
! 544: }
! 545: } {x 0 y 1}
! 546: do_test select4-9.5 {
! 547: execsql2 {
! 548: SELECT 0 AS x, 1 AS y
! 549: UNION
! 550: SELECT 2 AS p, 3 AS q
! 551: UNION
! 552: SELECT 4 AS a, 5 AS b
! 553: ORDER BY x LIMIT 1
! 554: }
! 555: } {x 0 y 1}
! 556:
! 557: ifcapable subquery {
! 558: do_test select4-9.6 {
! 559: execsql2 {
! 560: SELECT * FROM (
! 561: SELECT 0 AS x, 1 AS y
! 562: UNION
! 563: SELECT 2 AS p, 3 AS q
! 564: UNION
! 565: SELECT 4 AS a, 5 AS b
! 566: ) ORDER BY 1 LIMIT 1;
! 567: }
! 568: } {x 0 y 1}
! 569: do_test select4-9.7 {
! 570: execsql2 {
! 571: SELECT * FROM (
! 572: SELECT 0 AS x, 1 AS y
! 573: UNION
! 574: SELECT 2 AS p, 3 AS q
! 575: UNION
! 576: SELECT 4 AS a, 5 AS b
! 577: ) ORDER BY x LIMIT 1;
! 578: }
! 579: } {x 0 y 1}
! 580: } ;# ifcapable subquery
! 581:
! 582: do_test select4-9.8 {
! 583: execsql {
! 584: SELECT 0 AS x, 1 AS y
! 585: UNION
! 586: SELECT 2 AS y, -3 AS x
! 587: ORDER BY x LIMIT 1;
! 588: }
! 589: } {0 1}
! 590:
! 591: do_test select4-9.9.1 {
! 592: execsql2 {
! 593: SELECT 1 AS a, 2 AS b UNION ALL SELECT 3 AS b, 4 AS a
! 594: }
! 595: } {a 1 b 2 a 3 b 4}
! 596:
! 597: ifcapable subquery {
! 598: do_test select4-9.9.2 {
! 599: execsql2 {
! 600: SELECT * FROM (SELECT 1 AS a, 2 AS b UNION ALL SELECT 3 AS b, 4 AS a)
! 601: WHERE b=3
! 602: }
! 603: } {}
! 604: do_test select4-9.10 {
! 605: execsql2 {
! 606: SELECT * FROM (SELECT 1 AS a, 2 AS b UNION ALL SELECT 3 AS b, 4 AS a)
! 607: WHERE b=2
! 608: }
! 609: } {a 1 b 2}
! 610: do_test select4-9.11 {
! 611: execsql2 {
! 612: SELECT * FROM (SELECT 1 AS a, 2 AS b UNION ALL SELECT 3 AS e, 4 AS b)
! 613: WHERE b=2
! 614: }
! 615: } {a 1 b 2}
! 616: do_test select4-9.12 {
! 617: execsql2 {
! 618: SELECT * FROM (SELECT 1 AS a, 2 AS b UNION ALL SELECT 3 AS e, 4 AS b)
! 619: WHERE b>0
! 620: }
! 621: } {a 1 b 2 a 3 b 4}
! 622: } ;# ifcapable subquery
! 623:
! 624: # Try combining DISTINCT, LIMIT, and OFFSET. Make sure they all work
! 625: # together.
! 626: #
! 627: do_test select4-10.1 {
! 628: execsql {
! 629: SELECT DISTINCT log FROM t1 ORDER BY log
! 630: }
! 631: } {0 1 2 3 4 5}
! 632: do_test select4-10.2 {
! 633: execsql {
! 634: SELECT DISTINCT log FROM t1 ORDER BY log LIMIT 4
! 635: }
! 636: } {0 1 2 3}
! 637: do_test select4-10.3 {
! 638: execsql {
! 639: SELECT DISTINCT log FROM t1 ORDER BY log LIMIT 0
! 640: }
! 641: } {}
! 642: do_test select4-10.4 {
! 643: execsql {
! 644: SELECT DISTINCT log FROM t1 ORDER BY log LIMIT -1
! 645: }
! 646: } {0 1 2 3 4 5}
! 647: do_test select4-10.5 {
! 648: execsql {
! 649: SELECT DISTINCT log FROM t1 ORDER BY log LIMIT -1 OFFSET 2
! 650: }
! 651: } {2 3 4 5}
! 652: do_test select4-10.6 {
! 653: execsql {
! 654: SELECT DISTINCT log FROM t1 ORDER BY log LIMIT 3 OFFSET 2
! 655: }
! 656: } {2 3 4}
! 657: do_test select4-10.7 {
! 658: execsql {
! 659: SELECT DISTINCT log FROM t1 ORDER BY +log LIMIT 3 OFFSET 20
! 660: }
! 661: } {}
! 662: do_test select4-10.8 {
! 663: execsql {
! 664: SELECT DISTINCT log FROM t1 ORDER BY log LIMIT 0 OFFSET 3
! 665: }
! 666: } {}
! 667: do_test select4-10.9 {
! 668: execsql {
! 669: SELECT DISTINCT max(n), log FROM t1 ORDER BY +log; -- LIMIT 2 OFFSET 1
! 670: }
! 671: } {31 5}
! 672:
! 673: # Make sure compound SELECTs with wildly different numbers of columns
! 674: # do not cause assertion faults due to register allocation issues.
! 675: #
! 676: do_test select4-11.1 {
! 677: catchsql {
! 678: SELECT x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x FROM t2
! 679: UNION
! 680: SELECT x FROM t2
! 681: }
! 682: } {1 {SELECTs to the left and right of UNION do not have the same number of result columns}}
! 683: do_test select4-11.2 {
! 684: catchsql {
! 685: SELECT x FROM t2
! 686: UNION
! 687: SELECT x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x FROM t2
! 688: }
! 689: } {1 {SELECTs to the left and right of UNION do not have the same number of result columns}}
! 690: do_test select4-11.3 {
! 691: catchsql {
! 692: SELECT x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x FROM t2
! 693: UNION ALL
! 694: SELECT x FROM t2
! 695: }
! 696: } {1 {SELECTs to the left and right of UNION ALL do not have the same number of result columns}}
! 697: do_test select4-11.4 {
! 698: catchsql {
! 699: SELECT x FROM t2
! 700: UNION ALL
! 701: SELECT x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x FROM t2
! 702: }
! 703: } {1 {SELECTs to the left and right of UNION ALL do not have the same number of result columns}}
! 704: do_test select4-11.5 {
! 705: catchsql {
! 706: SELECT x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x FROM t2
! 707: EXCEPT
! 708: SELECT x FROM t2
! 709: }
! 710: } {1 {SELECTs to the left and right of EXCEPT do not have the same number of result columns}}
! 711: do_test select4-11.6 {
! 712: catchsql {
! 713: SELECT x FROM t2
! 714: EXCEPT
! 715: SELECT x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x FROM t2
! 716: }
! 717: } {1 {SELECTs to the left and right of EXCEPT do not have the same number of result columns}}
! 718: do_test select4-11.7 {
! 719: catchsql {
! 720: SELECT x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x FROM t2
! 721: INTERSECT
! 722: SELECT x FROM t2
! 723: }
! 724: } {1 {SELECTs to the left and right of INTERSECT do not have the same number of result columns}}
! 725: do_test select4-11.8 {
! 726: catchsql {
! 727: SELECT x FROM t2
! 728: INTERSECT
! 729: SELECT x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x FROM t2
! 730: }
! 731: } {1 {SELECTs to the left and right of INTERSECT do not have the same number of result columns}}
! 732:
! 733: do_test select4-11.11 {
! 734: catchsql {
! 735: SELECT x FROM t2
! 736: UNION
! 737: SELECT x FROM t2
! 738: UNION ALL
! 739: SELECT x FROM t2
! 740: EXCEPT
! 741: SELECT x FROM t2
! 742: INTERSECT
! 743: SELECT x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x FROM t2
! 744: }
! 745: } {1 {SELECTs to the left and right of INTERSECT do not have the same number of result columns}}
! 746: do_test select4-11.12 {
! 747: catchsql {
! 748: SELECT x FROM t2
! 749: UNION
! 750: SELECT x FROM t2
! 751: UNION ALL
! 752: SELECT x FROM t2
! 753: EXCEPT
! 754: SELECT x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x FROM t2
! 755: EXCEPT
! 756: SELECT x FROM t2
! 757: }
! 758: } {1 {SELECTs to the left and right of EXCEPT do not have the same number of result columns}}
! 759: do_test select4-11.13 {
! 760: catchsql {
! 761: SELECT x FROM t2
! 762: UNION
! 763: SELECT x FROM t2
! 764: UNION ALL
! 765: SELECT x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x FROM t2
! 766: UNION ALL
! 767: SELECT x FROM t2
! 768: EXCEPT
! 769: SELECT x FROM t2
! 770: }
! 771: } {1 {SELECTs to the left and right of UNION ALL do not have the same number of result columns}}
! 772: do_test select4-11.14 {
! 773: catchsql {
! 774: SELECT x FROM t2
! 775: UNION
! 776: SELECT x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x FROM t2
! 777: UNION
! 778: SELECT x FROM t2
! 779: UNION ALL
! 780: SELECT x FROM t2
! 781: EXCEPT
! 782: SELECT x FROM t2
! 783: }
! 784: } {1 {SELECTs to the left and right of UNION do not have the same number of result columns}}
! 785: do_test select4-11.15 {
! 786: catchsql {
! 787: SELECT x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x FROM t2
! 788: UNION
! 789: SELECT x FROM t2
! 790: INTERSECT
! 791: SELECT x FROM t2
! 792: UNION ALL
! 793: SELECT x FROM t2
! 794: EXCEPT
! 795: SELECT x FROM t2
! 796: }
! 797: } {1 {SELECTs to the left and right of UNION do not have the same number of result columns}}
! 798:
! 799: do_test select4-12.1 {
! 800: sqlite3 db2 :memory:
! 801: catchsql {
! 802: SELECT 1 UNION SELECT 2,3 UNION SELECT 4,5 ORDER BY 1;
! 803: } db2
! 804: } {1 {SELECTs to the left and right of UNION do not have the same number of result columns}}
! 805:
! 806: } ;# ifcapable compound
! 807:
! 808: finish_test
FreeBSD-CVSweb <freebsd-cvsweb@FreeBSD.org>
![[BACK]](/icons/cvsweb/back.gif){kind=icon}
Return to select4.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