gitlab.com/CoiaPrant/sqlite3@v1.19.1/testdata/tcl/limit.test (about) 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 # EVIDENCE-OF: R-13512-64012 In a compound SELECT, only the last or 255 # right-most simple SELECT may contain a LIMIT clause. 256 # 257 # EVIDENCE-OF: R-03782-50113 In a compound SELECT, the LIMIT clause 258 # applies to the entire compound, not just the final SELECT. 259 # 260 ifcapable compound { 261 do_test limit-7.1.1 { 262 catchsql { 263 SELECT x FROM t2 LIMIT 5 UNION ALL SELECT a FROM t6; 264 } 265 } {1 {LIMIT clause should come after UNION ALL not before}} 266 do_test limit-7.1.2 { 267 catchsql { 268 SELECT x FROM t2 LIMIT 5 UNION SELECT a FROM t6; 269 } 270 } {1 {LIMIT clause should come after UNION not before}} 271 do_test limit-7.1.3 { 272 catchsql { 273 SELECT x FROM t2 LIMIT 5 EXCEPT SELECT a FROM t6 LIMIT 3; 274 } 275 } {1 {LIMIT clause should come after EXCEPT not before}} 276 do_test limit-7.1.4 { 277 catchsql { 278 SELECT x FROM t2 LIMIT 0,5 INTERSECT SELECT a FROM t6; 279 } 280 } {1 {LIMIT clause should come after INTERSECT not before}} 281 do_test limit-7.2 { 282 execsql { 283 SELECT x FROM t2 UNION ALL SELECT a FROM t6 LIMIT 5; 284 } 285 } {31 30 1 2 3} 286 do_test limit-7.3 { 287 execsql { 288 SELECT x FROM t2 UNION ALL SELECT a FROM t6 LIMIT 3 OFFSET 1; 289 } 290 } {30 1 2} 291 do_test limit-7.4 { 292 execsql { 293 SELECT x FROM t2 UNION ALL SELECT a FROM t6 ORDER BY 1 LIMIT 3 OFFSET 1; 294 } 295 } {2 3 4} 296 do_test limit-7.5 { 297 execsql { 298 SELECT x FROM t2 UNION SELECT x+2 FROM t2 LIMIT 2 OFFSET 1; 299 } 300 } {31 32} 301 do_test limit-7.6 { 302 execsql { 303 SELECT x FROM t2 UNION SELECT x+2 FROM t2 ORDER BY 1 DESC LIMIT 2 OFFSET 1; 304 } 305 } {32 31} 306 do_test limit-7.7 { 307 execsql { 308 SELECT a+9 FROM t6 EXCEPT SELECT y FROM t2 LIMIT 2; 309 } 310 } {11 12} 311 do_test limit-7.8 { 312 execsql { 313 SELECT a+9 FROM t6 EXCEPT SELECT y FROM t2 ORDER BY 1 DESC LIMIT 2; 314 } 315 } {13 12} 316 do_test limit-7.9 { 317 execsql { 318 SELECT a+26 FROM t6 INTERSECT SELECT x FROM t2 LIMIT 1; 319 } 320 } {30} 321 do_test limit-7.10 { 322 execsql { 323 SELECT a+27 FROM t6 INTERSECT SELECT x FROM t2 LIMIT 1; 324 } 325 } {30} 326 do_test limit-7.11 { 327 execsql { 328 SELECT a+27 FROM t6 INTERSECT SELECT x FROM t2 LIMIT 1 OFFSET 1; 329 } 330 } {31} 331 do_test limit-7.12 { 332 execsql { 333 SELECT a+27 FROM t6 INTERSECT SELECT x FROM t2 334 ORDER BY 1 DESC LIMIT 1 OFFSET 1; 335 } 336 } {30} 337 } ;# ifcapable compound 338 339 # Tests for limit in conjunction with distinct. The distinct should 340 # occur before both the limit and the offset. Ticket #749. 341 # 342 do_test limit-8.1 { 343 execsql { 344 SELECT DISTINCT cast(round(x/100) as integer) FROM t3 LIMIT 5; 345 } 346 } {0 1 2 3 4} 347 do_test limit-8.2 { 348 execsql { 349 SELECT DISTINCT cast(round(x/100) as integer) FROM t3 LIMIT 5 OFFSET 5; 350 } 351 } {5 6 7 8 9} 352 do_test limit-8.3 { 353 execsql { 354 SELECT DISTINCT cast(round(x/100) as integer) FROM t3 LIMIT 5 OFFSET 25; 355 } 356 } {25 26 27 28 29} 357 358 # Make sure limits on multiple subqueries work correctly. 359 # Ticket #1035 360 # 361 ifcapable subquery { 362 do_test limit-9.1 { 363 execsql { 364 SELECT * FROM (SELECT * FROM t6 LIMIT 3); 365 } 366 } {1 2 3} 367 } 368 do_test limit-9.2.1 { 369 execsql { 370 CREATE TABLE t7 AS SELECT * FROM t6; 371 } 372 } {} 373 ifcapable subquery { 374 do_test limit-9.2.2 { 375 execsql { 376 SELECT * FROM (SELECT * FROM t7 LIMIT 3); 377 } 378 } {1 2 3} 379 } 380 ifcapable compound { 381 ifcapable subquery { 382 do_test limit-9.3 { 383 execsql { 384 SELECT * FROM (SELECT * FROM t6 LIMIT 3) 385 UNION 386 SELECT * FROM (SELECT * FROM t7 LIMIT 3) 387 ORDER BY 1 388 } 389 } {1 2 3} 390 do_test limit-9.4 { 391 execsql { 392 SELECT * FROM (SELECT * FROM t6 LIMIT 3) 393 UNION 394 SELECT * FROM (SELECT * FROM t7 LIMIT 3) 395 ORDER BY 1 396 LIMIT 2 397 } 398 } {1 2} 399 } 400 do_test limit-9.5 { 401 catchsql { 402 SELECT * FROM t6 LIMIT 3 403 UNION 404 SELECT * FROM t7 LIMIT 3 405 } 406 } {1 {LIMIT clause should come after UNION not before}} 407 } 408 409 # Test LIMIT and OFFSET using SQL variables. 410 do_test limit-10.1 { 411 set limit 10 412 db eval { 413 SELECT x FROM t1 LIMIT :limit; 414 } 415 } {31 30 29 28 27 26 25 24 23 22} 416 do_test limit-10.2 { 417 set limit 5 418 set offset 5 419 db eval { 420 SELECT x FROM t1 LIMIT :limit OFFSET :offset; 421 } 422 } {26 25 24 23 22} 423 do_test limit-10.3 { 424 set limit -1 425 db eval { 426 SELECT x FROM t1 WHERE x<10 LIMIT :limit; 427 } 428 } {9 8 7 6 5 4 3 2 1 0} 429 do_test limit-10.4 { 430 set limit 1.5 431 set rc [catch { 432 db eval { 433 SELECT x FROM t1 WHERE x<10 LIMIT :limit; 434 } } msg] 435 list $rc $msg 436 } {1 {datatype mismatch}} 437 do_test limit-10.5 { 438 set limit "hello world" 439 set rc [catch { 440 db eval { 441 SELECT x FROM t1 WHERE x<10 LIMIT :limit; 442 } } msg] 443 list $rc $msg 444 } {1 {datatype mismatch}} 445 446 ifcapable subquery { 447 do_test limit-11.1 { 448 db eval { 449 SELECT x FROM (SELECT x FROM t1 ORDER BY x LIMIT 0) ORDER BY x 450 } 451 } {} 452 } ;# ifcapable subquery 453 454 # Test error processing. 455 # 456 do_test limit-12.1 { 457 catchsql { 458 SELECT * FROM t1 LIMIT replace(1) 459 } 460 } {1 {wrong number of arguments to function replace()}} 461 do_test limit-12.2 { 462 catchsql { 463 SELECT * FROM t1 LIMIT 5 OFFSET replace(1) 464 } 465 } {1 {wrong number of arguments to function replace()}} 466 do_test limit-12.3 { 467 catchsql { 468 SELECT * FROM t1 LIMIT x 469 } 470 } {1 {no such column: x}} 471 do_test limit-12.4 { 472 catchsql { 473 SELECT * FROM t1 LIMIT 1 OFFSET x 474 } 475 } {1 {no such column: x}} 476 477 # Ticket [db4d96798da8b] 478 # LIMIT does not work with nested views containing UNION ALL 479 # 480 do_test limit-13.1 { 481 db eval { 482 CREATE TABLE t13(x); 483 INSERT INTO t13 VALUES(1),(2); 484 CREATE VIEW v13a AS SELECT x AS y FROM t13; 485 CREATE VIEW v13b AS SELECT y AS z FROM v13a UNION ALL SELECT y+10 FROM v13a; 486 CREATE VIEW v13c AS SELECT z FROM v13b UNION ALL SELECT z+20 FROM v13b; 487 } 488 } {} 489 do_test limit-13.2 { 490 db eval {SELECT z FROM v13c LIMIT 1} 491 } {1} 492 do_test limit-13.3 { 493 db eval {SELECT z FROM v13c LIMIT 2} 494 } {1 2} 495 do_test limit-13.4 { 496 db eval {SELECT z FROM v13c LIMIT 3} 497 } {1 2 11} 498 do_test limit-13.5 { 499 db eval {SELECT z FROM v13c LIMIT 4} 500 } {1 2 11 12} 501 do_test limit-13.6 { 502 db eval {SELECT z FROM v13c LIMIT 5} 503 } {1 2 11 12 21} 504 do_test limit-13.7 { 505 db eval {SELECT z FROM v13c LIMIT 6} 506 } {1 2 11 12 21 22} 507 do_test limit-13.8 { 508 db eval {SELECT z FROM v13c LIMIT 7} 509 } {1 2 11 12 21 22 31} 510 do_test limit-13.9 { 511 db eval {SELECT z FROM v13c LIMIT 8} 512 } {1 2 11 12 21 22 31 32} 513 do_test limit-13.10 { 514 db eval {SELECT z FROM v13c LIMIT 9} 515 } {1 2 11 12 21 22 31 32} 516 do_test limit-13.11 { 517 db eval {SELECT z FROM v13c LIMIT 1 OFFSET 1} 518 } {2} 519 do_test limit-13.12 { 520 db eval {SELECT z FROM v13c LIMIT 2 OFFSET 1} 521 } {2 11} 522 do_test limit-13.13 { 523 db eval {SELECT z FROM v13c LIMIT 3 OFFSET 1} 524 } {2 11 12} 525 do_test limit-13.14 { 526 db eval {SELECT z FROM v13c LIMIT 4 OFFSET 1} 527 } {2 11 12 21} 528 do_test limit-13.15 { 529 db eval {SELECT z FROM v13c LIMIT 5 OFFSET 1} 530 } {2 11 12 21 22} 531 do_test limit-13.16 { 532 db eval {SELECT z FROM v13c LIMIT 6 OFFSET 1} 533 } {2 11 12 21 22 31} 534 do_test limit-13.17 { 535 db eval {SELECT z FROM v13c LIMIT 7 OFFSET 1} 536 } {2 11 12 21 22 31 32} 537 do_test limit-13.18 { 538 db eval {SELECT z FROM v13c LIMIT 8 OFFSET 1} 539 } {2 11 12 21 22 31 32} 540 do_test limit-13.21 { 541 db eval {SELECT z FROM v13c LIMIT 1 OFFSET 2} 542 } {11} 543 do_test limit-13.22 { 544 db eval {SELECT z FROM v13c LIMIT 2 OFFSET 2} 545 } {11 12} 546 do_test limit-13.23 { 547 db eval {SELECT z FROM v13c LIMIT 3 OFFSET 2} 548 } {11 12 21} 549 do_test limit-13.24 { 550 db eval {SELECT z FROM v13c LIMIT 4 OFFSET 2} 551 } {11 12 21 22} 552 do_test limit-13.25 { 553 db eval {SELECT z FROM v13c LIMIT 5 OFFSET 2} 554 } {11 12 21 22 31} 555 do_test limit-13.26 { 556 db eval {SELECT z FROM v13c LIMIT 6 OFFSET 2} 557 } {11 12 21 22 31 32} 558 do_test limit-13.27 { 559 db eval {SELECT z FROM v13c LIMIT 7 OFFSET 2} 560 } {11 12 21 22 31 32} 561 do_test limit-13.31 { 562 db eval {SELECT z FROM v13c LIMIT 1 OFFSET 3} 563 } {12} 564 do_test limit-13.32 { 565 db eval {SELECT z FROM v13c LIMIT 2 OFFSET 3} 566 } {12 21} 567 do_test limit-13.33 { 568 db eval {SELECT z FROM v13c LIMIT 3 OFFSET 3} 569 } {12 21 22} 570 do_test limit-13.34 { 571 db eval {SELECT z FROM v13c LIMIT 4 OFFSET 3} 572 } {12 21 22 31} 573 do_test limit-13.35 { 574 db eval {SELECT z FROM v13c LIMIT 5 OFFSET 3} 575 } {12 21 22 31 32} 576 do_test limit-13.36 { 577 db eval {SELECT z FROM v13c LIMIT 6 OFFSET 3} 578 } {12 21 22 31 32} 579 do_test limit-13.41 { 580 db eval {SELECT z FROM v13c LIMIT 1 OFFSET 4} 581 } {21} 582 do_test limit-13.42 { 583 db eval {SELECT z FROM v13c LIMIT 2 OFFSET 4} 584 } {21 22} 585 do_test limit-13.43 { 586 db eval {SELECT z FROM v13c LIMIT 3 OFFSET 4} 587 } {21 22 31} 588 do_test limit-13.44 { 589 db eval {SELECT z FROM v13c LIMIT 4 OFFSET 4} 590 } {21 22 31 32} 591 do_test limit-13.45 { 592 db eval {SELECT z FROM v13c LIMIT 5 OFFSET 4} 593 } {21 22 31 32} 594 do_test limit-13.51 { 595 db eval {SELECT z FROM v13c LIMIT 1 OFFSET 5} 596 } {22} 597 do_test limit-13.52 { 598 db eval {SELECT z FROM v13c LIMIT 2 OFFSET 5} 599 } {22 31} 600 do_test limit-13.53 { 601 db eval {SELECT z FROM v13c LIMIT 3 OFFSET 5} 602 } {22 31 32} 603 do_test limit-13.54 { 604 db eval {SELECT z FROM v13c LIMIT 4 OFFSET 5} 605 } {22 31 32} 606 do_test limit-13.61 { 607 db eval {SELECT z FROM v13c LIMIT 1 OFFSET 6} 608 } {31} 609 do_test limit-13.62 { 610 db eval {SELECT z FROM v13c LIMIT 2 OFFSET 6} 611 } {31 32} 612 do_test limit-13.63 { 613 db eval {SELECT z FROM v13c LIMIT 3 OFFSET 6} 614 } {31 32} 615 do_test limit-13.71 { 616 db eval {SELECT z FROM v13c LIMIT 1 OFFSET 7} 617 } {32} 618 do_test limit-13.72 { 619 db eval {SELECT z FROM v13c LIMIT 2 OFFSET 7} 620 } {32} 621 do_test limit-13.81 { 622 db eval {SELECT z FROM v13c LIMIT 1 OFFSET 8} 623 } {} 624 625 do_execsql_test limit-14.1 { 626 SELECT 123 LIMIT 1 OFFSET 0 627 } {123} 628 do_execsql_test limit-14.2 { 629 SELECT 123 LIMIT 1 OFFSET 1 630 } {} 631 do_execsql_test limit-14.3 { 632 SELECT 123 LIMIT 0 OFFSET 0 633 } {} 634 do_execsql_test limit-14.4 { 635 SELECT 123 LIMIT 0 OFFSET 1 636 } {} 637 do_execsql_test limit-14.6 { 638 SELECT 123 LIMIT -1 OFFSET 0 639 } {123} 640 do_execsql_test limit-14.7 { 641 SELECT 123 LIMIT -1 OFFSET 1 642 } {} 643 644 # 2021-03-05 dbsqlfuzz crash-d811039c9f44f2d43199d5889fcf4085ef6221b9 645 # 646 reset_db 647 do_execsql_test limit-15.1 { 648 CREATE TABLE t1(a PRIMARY KEY, b TEXT); 649 CREATE TABLE t4(c PRIMARY KEY, d); 650 CREATE TABLE t5(e PRIMARY KEY, f); 651 CREATE TABLE t6(g, h); 652 CREATE TABLE t3_a(k, v); 653 CREATE TABLE t3_b(k, v); 654 CREATE VIEW t3 AS SELECT * FROM t3_a UNION ALL SELECT * FROM t3_b; 655 INSERT INTO t5(e,f) VALUES(500000,'orange'); 656 INSERT INTO t4(c,d) VALUES(300000,'blue'),(400,'green'),(8000,'grey'); 657 INSERT INTO t1(a,b) VALUES(300000,'purple'); 658 INSERT INTO t3_a VALUES(300000,'yellow'),(500,'pink'),(8000,'red'); 659 INSERT INTO t6 default values; 660 SELECT ( 661 SELECT 100000 FROM 662 (SELECT 200000 FROM t6 WHERE a = ( SELECT 300000 FROM t3 WHERE a ) ), 663 (SELECT 400000 FROM t5 WHERE e=500000), 664 (SELECT 600000 FROM t4 WHERE c=a) 665 ) FROM t1; 666 } {100000} 667 668 finish_test