Annotation of embedaddon/sqlite3/test/limit.test, revision 1.1
1.1 ! misho 1: # 2001 November 6
! 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 the LIMIT ... OFFSET ... clause
! 13: # of SELECT statements.
! 14: #
! 15: # $Id: limit.test,v 1.32 2008/08/02 03:50:39 drh Exp $
! 16:
! 17: set testdir [file dirname $argv0]
! 18: source $testdir/tester.tcl
! 19:
! 20: # Build some test data
! 21: #
! 22: execsql {
! 23: CREATE TABLE t1(x int, y int);
! 24: BEGIN;
! 25: }
! 26: for {set i 1} {$i<=32} {incr i} {
! 27: for {set j 0} {(1<<$j)<$i} {incr j} {}
! 28: execsql "INSERT INTO t1 VALUES([expr {32-$i}],[expr {10-$j}])"
! 29: }
! 30: execsql {
! 31: COMMIT;
! 32: }
! 33:
! 34: do_test limit-1.0 {
! 35: execsql {SELECT count(*) FROM t1}
! 36: } {32}
! 37: do_test limit-1.1 {
! 38: execsql {SELECT count(*) FROM t1 LIMIT 5}
! 39: } {32}
! 40: do_test limit-1.2.1 {
! 41: execsql {SELECT x FROM t1 ORDER BY x LIMIT 5}
! 42: } {0 1 2 3 4}
! 43: do_test limit-1.2.2 {
! 44: execsql {SELECT x FROM t1 ORDER BY x LIMIT 5 OFFSET 2}
! 45: } {2 3 4 5 6}
! 46: do_test limit-1.2.3 {
! 47: execsql {SELECT x FROM t1 ORDER BY x+1 LIMIT 5 OFFSET -2}
! 48: } {0 1 2 3 4}
! 49: do_test limit-1.2.4 {
! 50: execsql {SELECT x FROM t1 ORDER BY x+1 LIMIT 2, -5}
! 51: } {2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31}
! 52: do_test limit-1.2.5 {
! 53: execsql {SELECT x FROM t1 ORDER BY x+1 LIMIT -2, 5}
! 54: } {0 1 2 3 4}
! 55: do_test limit-1.2.6 {
! 56: execsql {SELECT x FROM t1 ORDER BY x+1 LIMIT -2, -5}
! 57: } {0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31}
! 58: do_test limit-1.2.7 {
! 59: execsql {SELECT x FROM t1 ORDER BY x LIMIT 2, 5}
! 60: } {2 3 4 5 6}
! 61: do_test limit-1.3 {
! 62: execsql {SELECT x FROM t1 ORDER BY x LIMIT 5 OFFSET 5}
! 63: } {5 6 7 8 9}
! 64: do_test limit-1.4.1 {
! 65: execsql {SELECT x FROM t1 ORDER BY x LIMIT 50 OFFSET 30}
! 66: } {30 31}
! 67: do_test limit-1.4.2 {
! 68: execsql {SELECT x FROM t1 ORDER BY x LIMIT 30, 50}
! 69: } {30 31}
! 70: do_test limit-1.5 {
! 71: execsql {SELECT x FROM t1 ORDER BY x LIMIT 50 OFFSET 50}
! 72: } {}
! 73: do_test limit-1.6 {
! 74: execsql {SELECT * FROM t1 AS a, t1 AS b ORDER BY a.x, b.x LIMIT 5}
! 75: } {0 5 0 5 0 5 1 5 0 5 2 5 0 5 3 5 0 5 4 5}
! 76: do_test limit-1.7 {
! 77: execsql {SELECT * FROM t1 AS a, t1 AS b ORDER BY a.x, b.x LIMIT 5 OFFSET 32}
! 78: } {1 5 0 5 1 5 1 5 1 5 2 5 1 5 3 5 1 5 4 5}
! 79:
! 80: ifcapable {view && subquery} {
! 81: do_test limit-2.1 {
! 82: execsql {
! 83: CREATE VIEW v1 AS SELECT * FROM t1 LIMIT 2;
! 84: SELECT count(*) FROM (SELECT * FROM v1);
! 85: }
! 86: } 2
! 87: } ;# ifcapable view
! 88: do_test limit-2.2 {
! 89: execsql {
! 90: CREATE TABLE t2 AS SELECT * FROM t1 LIMIT 2;
! 91: SELECT count(*) FROM t2;
! 92: }
! 93: } 2
! 94: ifcapable subquery {
! 95: do_test limit-2.3 {
! 96: execsql {
! 97: SELECT count(*) FROM t1 WHERE rowid IN (SELECT rowid FROM t1 LIMIT 2);
! 98: }
! 99: } 2
! 100: }
! 101:
! 102: ifcapable subquery {
! 103: do_test limit-3.1 {
! 104: execsql {
! 105: SELECT z FROM (SELECT y*10+x AS z FROM t1 ORDER BY x LIMIT 10)
! 106: ORDER BY z LIMIT 5;
! 107: }
! 108: } {50 51 52 53 54}
! 109: }
! 110:
! 111: do_test limit-4.1 {
! 112: ifcapable subquery {
! 113: execsql {
! 114: BEGIN;
! 115: CREATE TABLE t3(x);
! 116: INSERT INTO t3 SELECT x FROM t1 ORDER BY x LIMIT 10 OFFSET 1;
! 117: INSERT INTO t3 SELECT x+(SELECT max(x) FROM t3) FROM t3;
! 118: INSERT INTO t3 SELECT x+(SELECT max(x) FROM t3) FROM t3;
! 119: INSERT INTO t3 SELECT x+(SELECT max(x) FROM t3) FROM t3;
! 120: INSERT INTO t3 SELECT x+(SELECT max(x) FROM t3) FROM t3;
! 121: INSERT INTO t3 SELECT x+(SELECT max(x) FROM t3) FROM t3;
! 122: INSERT INTO t3 SELECT x+(SELECT max(x) FROM t3) FROM t3;
! 123: INSERT INTO t3 SELECT x+(SELECT max(x) FROM t3) FROM t3;
! 124: INSERT INTO t3 SELECT x+(SELECT max(x) FROM t3) FROM t3;
! 125: INSERT INTO t3 SELECT x+(SELECT max(x) FROM t3) FROM t3;
! 126: INSERT INTO t3 SELECT x+(SELECT max(x) FROM t3) FROM t3;
! 127: END;
! 128: SELECT count(*) FROM t3;
! 129: }
! 130: } else {
! 131: execsql {
! 132: BEGIN;
! 133: CREATE TABLE t3(x);
! 134: INSERT INTO t3 SELECT x FROM t1 ORDER BY x LIMIT 10 OFFSET 1;
! 135: }
! 136: for {set i 0} {$i<10} {incr i} {
! 137: set max_x_t3 [execsql {SELECT max(x) FROM t3}]
! 138: execsql "INSERT INTO t3 SELECT x+$max_x_t3 FROM t3;"
! 139: }
! 140: execsql {
! 141: END;
! 142: SELECT count(*) FROM t3;
! 143: }
! 144: }
! 145: } {10240}
! 146: do_test limit-4.2 {
! 147: execsql {
! 148: SELECT x FROM t3 LIMIT 2 OFFSET 10000
! 149: }
! 150: } {10001 10002}
! 151: do_test limit-4.3 {
! 152: execsql {
! 153: CREATE TABLE t4 AS SELECT x,
! 154: 'abcdefghijklmnopqrstuvwyxz ABCDEFGHIJKLMNOPQRSTUVWYXZ' || x ||
! 155: 'abcdefghijklmnopqrstuvwyxz ABCDEFGHIJKLMNOPQRSTUVWYXZ' || x ||
! 156: 'abcdefghijklmnopqrstuvwyxz ABCDEFGHIJKLMNOPQRSTUVWYXZ' || x ||
! 157: 'abcdefghijklmnopqrstuvwyxz ABCDEFGHIJKLMNOPQRSTUVWYXZ' || x ||
! 158: 'abcdefghijklmnopqrstuvwyxz ABCDEFGHIJKLMNOPQRSTUVWYXZ' || x AS y
! 159: FROM t3 LIMIT 1000;
! 160: SELECT x FROM t4 ORDER BY y DESC LIMIT 1 OFFSET 999;
! 161: }
! 162: } {1000}
! 163:
! 164: do_test limit-5.1 {
! 165: execsql {
! 166: CREATE TABLE t5(x,y);
! 167: INSERT INTO t5 SELECT x-y, x+y FROM t1 WHERE x BETWEEN 10 AND 15
! 168: ORDER BY x LIMIT 2;
! 169: SELECT * FROM t5 ORDER BY x;
! 170: }
! 171: } {5 15 6 16}
! 172: do_test limit-5.2 {
! 173: execsql {
! 174: DELETE FROM t5;
! 175: INSERT INTO t5 SELECT x-y, x+y FROM t1 WHERE x BETWEEN 10 AND 15
! 176: ORDER BY x DESC LIMIT 2;
! 177: SELECT * FROM t5 ORDER BY x;
! 178: }
! 179: } {9 19 10 20}
! 180: do_test limit-5.3 {
! 181: execsql {
! 182: DELETE FROM t5;
! 183: INSERT INTO t5 SELECT x-y, x+y FROM t1 WHERE x ORDER BY x DESC LIMIT 31;
! 184: SELECT * FROM t5 ORDER BY x LIMIT 2;
! 185: }
! 186: } {-4 6 -3 7}
! 187: do_test limit-5.4 {
! 188: execsql {
! 189: SELECT * FROM t5 ORDER BY x DESC, y DESC LIMIT 2;
! 190: }
! 191: } {21 41 21 39}
! 192: do_test limit-5.5 {
! 193: execsql {
! 194: DELETE FROM t5;
! 195: INSERT INTO t5 SELECT a.x*100+b.x, a.y*100+b.y FROM t1 AS a, t1 AS b
! 196: ORDER BY 1, 2 LIMIT 1000;
! 197: SELECT count(*), sum(x), sum(y), min(x), max(x), min(y), max(y) FROM t5;
! 198: }
! 199: } {1000 1528204 593161 0 3107 505 1005}
! 200:
! 201: # There is some contraversy about whether LIMIT 0 should be the same as
! 202: # no limit at all or if LIMIT 0 should result in zero output rows.
! 203: #
! 204: do_test limit-6.1 {
! 205: execsql {
! 206: BEGIN;
! 207: CREATE TABLE t6(a);
! 208: INSERT INTO t6 VALUES(1);
! 209: INSERT INTO t6 VALUES(2);
! 210: INSERT INTO t6 SELECT a+2 FROM t6;
! 211: COMMIT;
! 212: SELECT * FROM t6;
! 213: }
! 214: } {1 2 3 4}
! 215: do_test limit-6.2 {
! 216: execsql {
! 217: SELECT * FROM t6 LIMIT -1 OFFSET -1;
! 218: }
! 219: } {1 2 3 4}
! 220: do_test limit-6.3 {
! 221: execsql {
! 222: SELECT * FROM t6 LIMIT 2 OFFSET -123;
! 223: }
! 224: } {1 2}
! 225: do_test limit-6.4 {
! 226: execsql {
! 227: SELECT * FROM t6 LIMIT -432 OFFSET 2;
! 228: }
! 229: } {3 4}
! 230: do_test limit-6.5 {
! 231: execsql {
! 232: SELECT * FROM t6 LIMIT -1
! 233: }
! 234: } {1 2 3 4}
! 235: do_test limit-6.6 {
! 236: execsql {
! 237: SELECT * FROM t6 LIMIT -1 OFFSET 1
! 238: }
! 239: } {2 3 4}
! 240: do_test limit-6.7 {
! 241: execsql {
! 242: SELECT * FROM t6 LIMIT 0
! 243: }
! 244: } {}
! 245: do_test limit-6.8 {
! 246: execsql {
! 247: SELECT * FROM t6 LIMIT 0 OFFSET 1
! 248: }
! 249: } {}
! 250:
! 251: # Make sure LIMIT works well with compound SELECT statements.
! 252: # Ticket #393
! 253: #
! 254: ifcapable compound {
! 255: do_test limit-7.1.1 {
! 256: catchsql {
! 257: SELECT x FROM t2 LIMIT 5 UNION ALL SELECT a FROM t6;
! 258: }
! 259: } {1 {LIMIT clause should come after UNION ALL not before}}
! 260: do_test limit-7.1.2 {
! 261: catchsql {
! 262: SELECT x FROM t2 LIMIT 5 UNION SELECT a FROM t6;
! 263: }
! 264: } {1 {LIMIT clause should come after UNION not before}}
! 265: do_test limit-7.1.3 {
! 266: catchsql {
! 267: SELECT x FROM t2 LIMIT 5 EXCEPT SELECT a FROM t6 LIMIT 3;
! 268: }
! 269: } {1 {LIMIT clause should come after EXCEPT not before}}
! 270: do_test limit-7.1.4 {
! 271: catchsql {
! 272: SELECT x FROM t2 LIMIT 0,5 INTERSECT SELECT a FROM t6;
! 273: }
! 274: } {1 {LIMIT clause should come after INTERSECT not before}}
! 275: do_test limit-7.2 {
! 276: execsql {
! 277: SELECT x FROM t2 UNION ALL SELECT a FROM t6 LIMIT 5;
! 278: }
! 279: } {31 30 1 2 3}
! 280: do_test limit-7.3 {
! 281: execsql {
! 282: SELECT x FROM t2 UNION ALL SELECT a FROM t6 LIMIT 3 OFFSET 1;
! 283: }
! 284: } {30 1 2}
! 285: do_test limit-7.4 {
! 286: execsql {
! 287: SELECT x FROM t2 UNION ALL SELECT a FROM t6 ORDER BY 1 LIMIT 3 OFFSET 1;
! 288: }
! 289: } {2 3 4}
! 290: do_test limit-7.5 {
! 291: execsql {
! 292: SELECT x FROM t2 UNION SELECT x+2 FROM t2 LIMIT 2 OFFSET 1;
! 293: }
! 294: } {31 32}
! 295: do_test limit-7.6 {
! 296: execsql {
! 297: SELECT x FROM t2 UNION SELECT x+2 FROM t2 ORDER BY 1 DESC LIMIT 2 OFFSET 1;
! 298: }
! 299: } {32 31}
! 300: do_test limit-7.7 {
! 301: execsql {
! 302: SELECT a+9 FROM t6 EXCEPT SELECT y FROM t2 LIMIT 2;
! 303: }
! 304: } {11 12}
! 305: do_test limit-7.8 {
! 306: execsql {
! 307: SELECT a+9 FROM t6 EXCEPT SELECT y FROM t2 ORDER BY 1 DESC LIMIT 2;
! 308: }
! 309: } {13 12}
! 310: do_test limit-7.9 {
! 311: execsql {
! 312: SELECT a+26 FROM t6 INTERSECT SELECT x FROM t2 LIMIT 1;
! 313: }
! 314: } {30}
! 315: do_test limit-7.10 {
! 316: execsql {
! 317: SELECT a+27 FROM t6 INTERSECT SELECT x FROM t2 LIMIT 1;
! 318: }
! 319: } {30}
! 320: do_test limit-7.11 {
! 321: execsql {
! 322: SELECT a+27 FROM t6 INTERSECT SELECT x FROM t2 LIMIT 1 OFFSET 1;
! 323: }
! 324: } {31}
! 325: do_test limit-7.12 {
! 326: execsql {
! 327: SELECT a+27 FROM t6 INTERSECT SELECT x FROM t2
! 328: ORDER BY 1 DESC LIMIT 1 OFFSET 1;
! 329: }
! 330: } {30}
! 331: } ;# ifcapable compound
! 332:
! 333: # Tests for limit in conjunction with distinct. The distinct should
! 334: # occur before both the limit and the offset. Ticket #749.
! 335: #
! 336: do_test limit-8.1 {
! 337: execsql {
! 338: SELECT DISTINCT cast(round(x/100) as integer) FROM t3 LIMIT 5;
! 339: }
! 340: } {0 1 2 3 4}
! 341: do_test limit-8.2 {
! 342: execsql {
! 343: SELECT DISTINCT cast(round(x/100) as integer) FROM t3 LIMIT 5 OFFSET 5;
! 344: }
! 345: } {5 6 7 8 9}
! 346: do_test limit-8.3 {
! 347: execsql {
! 348: SELECT DISTINCT cast(round(x/100) as integer) FROM t3 LIMIT 5 OFFSET 25;
! 349: }
! 350: } {25 26 27 28 29}
! 351:
! 352: # Make sure limits on multiple subqueries work correctly.
! 353: # Ticket #1035
! 354: #
! 355: ifcapable subquery {
! 356: do_test limit-9.1 {
! 357: execsql {
! 358: SELECT * FROM (SELECT * FROM t6 LIMIT 3);
! 359: }
! 360: } {1 2 3}
! 361: }
! 362: do_test limit-9.2.1 {
! 363: execsql {
! 364: CREATE TABLE t7 AS SELECT * FROM t6;
! 365: }
! 366: } {}
! 367: ifcapable subquery {
! 368: do_test limit-9.2.2 {
! 369: execsql {
! 370: SELECT * FROM (SELECT * FROM t7 LIMIT 3);
! 371: }
! 372: } {1 2 3}
! 373: }
! 374: ifcapable compound {
! 375: ifcapable subquery {
! 376: do_test limit-9.3 {
! 377: execsql {
! 378: SELECT * FROM (SELECT * FROM t6 LIMIT 3)
! 379: UNION
! 380: SELECT * FROM (SELECT * FROM t7 LIMIT 3)
! 381: ORDER BY 1
! 382: }
! 383: } {1 2 3}
! 384: do_test limit-9.4 {
! 385: execsql {
! 386: SELECT * FROM (SELECT * FROM t6 LIMIT 3)
! 387: UNION
! 388: SELECT * FROM (SELECT * FROM t7 LIMIT 3)
! 389: ORDER BY 1
! 390: LIMIT 2
! 391: }
! 392: } {1 2}
! 393: }
! 394: do_test limit-9.5 {
! 395: catchsql {
! 396: SELECT * FROM t6 LIMIT 3
! 397: UNION
! 398: SELECT * FROM t7 LIMIT 3
! 399: }
! 400: } {1 {LIMIT clause should come after UNION not before}}
! 401: }
! 402:
! 403: # Test LIMIT and OFFSET using SQL variables.
! 404: do_test limit-10.1 {
! 405: set limit 10
! 406: db eval {
! 407: SELECT x FROM t1 LIMIT :limit;
! 408: }
! 409: } {31 30 29 28 27 26 25 24 23 22}
! 410: do_test limit-10.2 {
! 411: set limit 5
! 412: set offset 5
! 413: db eval {
! 414: SELECT x FROM t1 LIMIT :limit OFFSET :offset;
! 415: }
! 416: } {26 25 24 23 22}
! 417: do_test limit-10.3 {
! 418: set limit -1
! 419: db eval {
! 420: SELECT x FROM t1 WHERE x<10 LIMIT :limit;
! 421: }
! 422: } {9 8 7 6 5 4 3 2 1 0}
! 423: do_test limit-10.4 {
! 424: set limit 1.5
! 425: set rc [catch {
! 426: db eval {
! 427: SELECT x FROM t1 WHERE x<10 LIMIT :limit;
! 428: } } msg]
! 429: list $rc $msg
! 430: } {1 {datatype mismatch}}
! 431: do_test limit-10.5 {
! 432: set limit "hello world"
! 433: set rc [catch {
! 434: db eval {
! 435: SELECT x FROM t1 WHERE x<10 LIMIT :limit;
! 436: } } msg]
! 437: list $rc $msg
! 438: } {1 {datatype mismatch}}
! 439:
! 440: ifcapable subquery {
! 441: do_test limit-11.1 {
! 442: db eval {
! 443: SELECT x FROM (SELECT x FROM t1 ORDER BY x LIMIT 0) ORDER BY x
! 444: }
! 445: } {}
! 446: } ;# ifcapable subquery
! 447:
! 448: # Test error processing.
! 449: #
! 450: do_test limit-12.1 {
! 451: catchsql {
! 452: SELECT * FROM t1 LIMIT replace(1)
! 453: }
! 454: } {1 {wrong number of arguments to function replace()}}
! 455: do_test limit-12.2 {
! 456: catchsql {
! 457: SELECT * FROM t1 LIMIT 5 OFFSET replace(1)
! 458: }
! 459: } {1 {wrong number of arguments to function replace()}}
! 460: do_test limit-12.3 {
! 461: catchsql {
! 462: SELECT * FROM t1 LIMIT x
! 463: }
! 464: } {1 {no such column: x}}
! 465: do_test limit-12.4 {
! 466: catchsql {
! 467: SELECT * FROM t1 LIMIT 1 OFFSET x
! 468: }
! 469: } {1 {no such column: x}}
! 470:
! 471:
! 472: finish_test
FreeBSD-CVSweb <freebsd-cvsweb@FreeBSD.org>
![[BACK]](/icons/cvsweb/back.gif){kind=icon}
Return to limit.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