000001 # 2001 November 6
000002 #
000003 # The author disclaims copyright to this source code. In place of
000004 # a legal notice, here is a blessing:
000005 #
000006 # May you do good and not evil.
000007 # May you find forgiveness for yourself and forgive others.
000008 # May you share freely, never taking more than you give.
000009 #
000010 #***********************************************************************
000011 # This file implements regression tests for SQLite library. The
000012 # focus of this file is testing the LIMIT ... OFFSET ... clause
000013 # of SELECT statements.
000014 #
000015 # $Id: limit.test,v 1.32 2008/08/02 03:50:39 drh Exp $
000016
000017 set testdir [file dirname $argv0]
000018 source $testdir/tester.tcl
000019
000020 # Build some test data
000021 #
000022 execsql {
000023 CREATE TABLE t1(x int, y int);
000024 BEGIN;
000025 }
000026 for {set i 1} {$i<=32} {incr i} {
000027 for {set j 0} {(1<<$j)<$i} {incr j} {}
000028 execsql "INSERT INTO t1 VALUES([expr {32-$i}],[expr {10-$j}])"
000029 }
000030 execsql {
000031 COMMIT;
000032 }
000033
000034 do_test limit-1.0 {
000035 execsql {SELECT count(*) FROM t1}
000036 } {32}
000037 do_test limit-1.1 {
000038 execsql {SELECT count(*) FROM t1 LIMIT 5}
000039 } {32}
000040 do_test limit-1.2.1 {
000041 execsql {SELECT x FROM t1 ORDER BY x LIMIT 5}
000042 } {0 1 2 3 4}
000043 do_test limit-1.2.2 {
000044 execsql {SELECT x FROM t1 ORDER BY x LIMIT 5 OFFSET 2}
000045 } {2 3 4 5 6}
000046 do_test limit-1.2.3 {
000047 execsql {SELECT x FROM t1 ORDER BY x+1 LIMIT 5 OFFSET -2}
000048 } {0 1 2 3 4}
000049 do_test limit-1.2.4 {
000050 execsql {SELECT x FROM t1 ORDER BY x+1 LIMIT 2, -5}
000051 } {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}
000052 do_test limit-1.2.5 {
000053 execsql {SELECT x FROM t1 ORDER BY x+1 LIMIT -2, 5}
000054 } {0 1 2 3 4}
000055 do_test limit-1.2.6 {
000056 execsql {SELECT x FROM t1 ORDER BY x+1 LIMIT -2, -5}
000057 } {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}
000058 do_test limit-1.2.7 {
000059 execsql {SELECT x FROM t1 ORDER BY x LIMIT 2, 5}
000060 } {2 3 4 5 6}
000061 do_test limit-1.3 {
000062 execsql {SELECT x FROM t1 ORDER BY x LIMIT 5 OFFSET 5}
000063 } {5 6 7 8 9}
000064 do_test limit-1.4.1 {
000065 execsql {SELECT x FROM t1 ORDER BY x LIMIT 50 OFFSET 30}
000066 } {30 31}
000067 do_test limit-1.4.2 {
000068 execsql {SELECT x FROM t1 ORDER BY x LIMIT 30, 50}
000069 } {30 31}
000070 do_test limit-1.5 {
000071 execsql {SELECT x FROM t1 ORDER BY x LIMIT 50 OFFSET 50}
000072 } {}
000073 do_test limit-1.6 {
000074 execsql {SELECT * FROM t1 AS a, t1 AS b ORDER BY a.x, b.x LIMIT 5}
000075 } {0 5 0 5 0 5 1 5 0 5 2 5 0 5 3 5 0 5 4 5}
000076 do_test limit-1.7 {
000077 execsql {SELECT * FROM t1 AS a, t1 AS b ORDER BY a.x, b.x LIMIT 5 OFFSET 32}
000078 } {1 5 0 5 1 5 1 5 1 5 2 5 1 5 3 5 1 5 4 5}
000079
000080 ifcapable {view && subquery} {
000081 do_test limit-2.1 {
000082 execsql {
000083 CREATE VIEW v1 AS SELECT * FROM t1 LIMIT 2;
000084 SELECT count(*) FROM (SELECT * FROM v1);
000085 }
000086 } 2
000087 } ;# ifcapable view
000088 do_test limit-2.2 {
000089 execsql {
000090 CREATE TABLE t2 AS SELECT * FROM t1 LIMIT 2;
000091 SELECT count(*) FROM t2;
000092 }
000093 } 2
000094 ifcapable subquery {
000095 do_test limit-2.3 {
000096 execsql {
000097 SELECT count(*) FROM t1 WHERE rowid IN (SELECT rowid FROM t1 LIMIT 2);
000098 }
000099 } 2
000100 }
000101
000102 ifcapable subquery {
000103 do_test limit-3.1 {
000104 execsql {
000105 SELECT z FROM (SELECT y*10+x AS z FROM t1 ORDER BY x LIMIT 10)
000106 ORDER BY z LIMIT 5;
000107 }
000108 } {50 51 52 53 54}
000109 }
000110
000111 do_test limit-4.1 {
000112 ifcapable subquery {
000113 execsql {
000114 BEGIN;
000115 CREATE TABLE t3(x);
000116 INSERT INTO t3 SELECT x FROM t1 ORDER BY x LIMIT 10 OFFSET 1;
000117 INSERT INTO t3 SELECT x+(SELECT max(x) FROM t3) FROM t3;
000118 INSERT INTO t3 SELECT x+(SELECT max(x) FROM t3) FROM t3;
000119 INSERT INTO t3 SELECT x+(SELECT max(x) FROM t3) FROM t3;
000120 INSERT INTO t3 SELECT x+(SELECT max(x) FROM t3) FROM t3;
000121 INSERT INTO t3 SELECT x+(SELECT max(x) FROM t3) FROM t3;
000122 INSERT INTO t3 SELECT x+(SELECT max(x) FROM t3) FROM t3;
000123 INSERT INTO t3 SELECT x+(SELECT max(x) FROM t3) FROM t3;
000124 INSERT INTO t3 SELECT x+(SELECT max(x) FROM t3) FROM t3;
000125 INSERT INTO t3 SELECT x+(SELECT max(x) FROM t3) FROM t3;
000126 INSERT INTO t3 SELECT x+(SELECT max(x) FROM t3) FROM t3;
000127 END;
000128 SELECT count(*) FROM t3;
000129 }
000130 } else {
000131 execsql {
000132 BEGIN;
000133 CREATE TABLE t3(x);
000134 INSERT INTO t3 SELECT x FROM t1 ORDER BY x LIMIT 10 OFFSET 1;
000135 }
000136 for {set i 0} {$i<10} {incr i} {
000137 set max_x_t3 [execsql {SELECT max(x) FROM t3}]
000138 execsql "INSERT INTO t3 SELECT x+$max_x_t3 FROM t3;"
000139 }
000140 execsql {
000141 END;
000142 SELECT count(*) FROM t3;
000143 }
000144 }
000145 } {10240}
000146 do_test limit-4.2 {
000147 execsql {
000148 SELECT x FROM t3 LIMIT 2 OFFSET 10000
000149 }
000150 } {10001 10002}
000151 do_test limit-4.3 {
000152 execsql {
000153 CREATE TABLE t4 AS SELECT x,
000154 'abcdefghijklmnopqrstuvwyxz ABCDEFGHIJKLMNOPQRSTUVWYXZ' || x ||
000155 'abcdefghijklmnopqrstuvwyxz ABCDEFGHIJKLMNOPQRSTUVWYXZ' || x ||
000156 'abcdefghijklmnopqrstuvwyxz ABCDEFGHIJKLMNOPQRSTUVWYXZ' || x ||
000157 'abcdefghijklmnopqrstuvwyxz ABCDEFGHIJKLMNOPQRSTUVWYXZ' || x ||
000158 'abcdefghijklmnopqrstuvwyxz ABCDEFGHIJKLMNOPQRSTUVWYXZ' || x AS y
000159 FROM t3 LIMIT 1000;
000160 SELECT x FROM t4 ORDER BY y DESC LIMIT 1 OFFSET 999;
000161 }
000162 } {1000}
000163
000164 do_test limit-5.1 {
000165 execsql {
000166 CREATE TABLE t5(x,y);
000167 INSERT INTO t5 SELECT x-y, x+y FROM t1 WHERE x BETWEEN 10 AND 15
000168 ORDER BY x LIMIT 2;
000169 SELECT * FROM t5 ORDER BY x;
000170 }
000171 } {5 15 6 16}
000172 do_test limit-5.2 {
000173 execsql {
000174 DELETE FROM t5;
000175 INSERT INTO t5 SELECT x-y, x+y FROM t1 WHERE x BETWEEN 10 AND 15
000176 ORDER BY x DESC LIMIT 2;
000177 SELECT * FROM t5 ORDER BY x;
000178 }
000179 } {9 19 10 20}
000180 do_test limit-5.3 {
000181 execsql {
000182 DELETE FROM t5;
000183 INSERT INTO t5 SELECT x-y, x+y FROM t1 WHERE x ORDER BY x DESC LIMIT 31;
000184 SELECT * FROM t5 ORDER BY x LIMIT 2;
000185 }
000186 } {-4 6 -3 7}
000187 do_test limit-5.4 {
000188 execsql {
000189 SELECT * FROM t5 ORDER BY x DESC, y DESC LIMIT 2;
000190 }
000191 } {21 41 21 39}
000192 do_test limit-5.5 {
000193 execsql {
000194 DELETE FROM t5;
000195 INSERT INTO t5 SELECT a.x*100+b.x, a.y*100+b.y FROM t1 AS a, t1 AS b
000196 ORDER BY 1, 2 LIMIT 1000;
000197 SELECT count(*), sum(x), sum(y), min(x), max(x), min(y), max(y) FROM t5;
000198 }
000199 } {1000 1528204 593161 0 3107 505 1005}
000200
000201 # There is some contraversy about whether LIMIT 0 should be the same as
000202 # no limit at all or if LIMIT 0 should result in zero output rows.
000203 #
000204 do_test limit-6.1 {
000205 execsql {
000206 BEGIN;
000207 CREATE TABLE t6(a);
000208 INSERT INTO t6 VALUES(1);
000209 INSERT INTO t6 VALUES(2);
000210 INSERT INTO t6 SELECT a+2 FROM t6;
000211 COMMIT;
000212 SELECT * FROM t6;
000213 }
000214 } {1 2 3 4}
000215 do_test limit-6.2 {
000216 execsql {
000217 SELECT * FROM t6 LIMIT -1 OFFSET -1;
000218 }
000219 } {1 2 3 4}
000220 do_test limit-6.3 {
000221 execsql {
000222 SELECT * FROM t6 LIMIT 2 OFFSET -123;
000223 }
000224 } {1 2}
000225 do_test limit-6.4 {
000226 execsql {
000227 SELECT * FROM t6 LIMIT -432 OFFSET 2;
000228 }
000229 } {3 4}
000230 do_test limit-6.5 {
000231 execsql {
000232 SELECT * FROM t6 LIMIT -1
000233 }
000234 } {1 2 3 4}
000235 do_test limit-6.6 {
000236 execsql {
000237 SELECT * FROM t6 LIMIT -1 OFFSET 1
000238 }
000239 } {2 3 4}
000240 do_test limit-6.7 {
000241 execsql {
000242 SELECT * FROM t6 LIMIT 0
000243 }
000244 } {}
000245 do_test limit-6.8 {
000246 execsql {
000247 SELECT * FROM t6 LIMIT 0 OFFSET 1
000248 }
000249 } {}
000250
000251 # Make sure LIMIT works well with compound SELECT statements.
000252 # Ticket #393
000253 #
000254 # EVIDENCE-OF: R-13512-64012 In a compound SELECT, only the last or
000255 # right-most simple SELECT may contain a LIMIT clause.
000256 #
000257 # EVIDENCE-OF: R-03782-50113 In a compound SELECT, the LIMIT clause
000258 # applies to the entire compound, not just the final SELECT.
000259 #
000260 ifcapable compound {
000261 do_test limit-7.1.1 {
000262 catchsql {
000263 SELECT x FROM t2 LIMIT 5 UNION ALL SELECT a FROM t6;
000264 }
000265 } {1 {LIMIT clause should come after UNION ALL not before}}
000266 do_test limit-7.1.2 {
000267 catchsql {
000268 SELECT x FROM t2 LIMIT 5 UNION SELECT a FROM t6;
000269 }
000270 } {1 {LIMIT clause should come after UNION not before}}
000271 do_test limit-7.1.3 {
000272 catchsql {
000273 SELECT x FROM t2 LIMIT 5 EXCEPT SELECT a FROM t6 LIMIT 3;
000274 }
000275 } {1 {LIMIT clause should come after EXCEPT not before}}
000276 do_test limit-7.1.4 {
000277 catchsql {
000278 SELECT x FROM t2 LIMIT 0,5 INTERSECT SELECT a FROM t6;
000279 }
000280 } {1 {LIMIT clause should come after INTERSECT not before}}
000281 do_test limit-7.2 {
000282 execsql {
000283 SELECT x FROM t2 UNION ALL SELECT a FROM t6 LIMIT 5;
000284 }
000285 } {31 30 1 2 3}
000286 do_test limit-7.3 {
000287 execsql {
000288 SELECT x FROM t2 UNION ALL SELECT a FROM t6 LIMIT 3 OFFSET 1;
000289 }
000290 } {30 1 2}
000291 do_test limit-7.4 {
000292 execsql {
000293 SELECT x FROM t2 UNION ALL SELECT a FROM t6 ORDER BY 1 LIMIT 3 OFFSET 1;
000294 }
000295 } {2 3 4}
000296 do_test limit-7.5 {
000297 execsql {
000298 SELECT x FROM t2 UNION SELECT x+2 FROM t2 LIMIT 2 OFFSET 1;
000299 }
000300 } {31 32}
000301 do_test limit-7.6 {
000302 execsql {
000303 SELECT x FROM t2 UNION SELECT x+2 FROM t2 ORDER BY 1 DESC LIMIT 2 OFFSET 1;
000304 }
000305 } {32 31}
000306 do_test limit-7.7 {
000307 execsql {
000308 SELECT a+9 FROM t6 EXCEPT SELECT y FROM t2 LIMIT 2;
000309 }
000310 } {11 12}
000311 do_test limit-7.8 {
000312 execsql {
000313 SELECT a+9 FROM t6 EXCEPT SELECT y FROM t2 ORDER BY 1 DESC LIMIT 2;
000314 }
000315 } {13 12}
000316 do_test limit-7.9 {
000317 execsql {
000318 SELECT a+26 FROM t6 INTERSECT SELECT x FROM t2 LIMIT 1;
000319 }
000320 } {30}
000321 do_test limit-7.10 {
000322 execsql {
000323 SELECT a+27 FROM t6 INTERSECT SELECT x FROM t2 LIMIT 1;
000324 }
000325 } {30}
000326 do_test limit-7.11 {
000327 execsql {
000328 SELECT a+27 FROM t6 INTERSECT SELECT x FROM t2 LIMIT 1 OFFSET 1;
000329 }
000330 } {31}
000331 do_test limit-7.12 {
000332 execsql {
000333 SELECT a+27 FROM t6 INTERSECT SELECT x FROM t2
000334 ORDER BY 1 DESC LIMIT 1 OFFSET 1;
000335 }
000336 } {30}
000337 } ;# ifcapable compound
000338
000339 # Tests for limit in conjunction with distinct. The distinct should
000340 # occur before both the limit and the offset. Ticket #749.
000341 #
000342 do_test limit-8.1 {
000343 execsql {
000344 SELECT DISTINCT cast(round(x/100) as integer) FROM t3 LIMIT 5;
000345 }
000346 } {0 1 2 3 4}
000347 do_test limit-8.2 {
000348 execsql {
000349 SELECT DISTINCT cast(round(x/100) as integer) FROM t3 LIMIT 5 OFFSET 5;
000350 }
000351 } {5 6 7 8 9}
000352 do_test limit-8.3 {
000353 execsql {
000354 SELECT DISTINCT cast(round(x/100) as integer) FROM t3 LIMIT 5 OFFSET 25;
000355 }
000356 } {25 26 27 28 29}
000357
000358 # Make sure limits on multiple subqueries work correctly.
000359 # Ticket #1035
000360 #
000361 ifcapable subquery {
000362 do_test limit-9.1 {
000363 execsql {
000364 SELECT * FROM (SELECT * FROM t6 LIMIT 3);
000365 }
000366 } {1 2 3}
000367 }
000368 do_test limit-9.2.1 {
000369 execsql {
000370 CREATE TABLE t7 AS SELECT * FROM t6;
000371 }
000372 } {}
000373 ifcapable subquery {
000374 do_test limit-9.2.2 {
000375 execsql {
000376 SELECT * FROM (SELECT * FROM t7 LIMIT 3);
000377 }
000378 } {1 2 3}
000379 }
000380 ifcapable compound {
000381 ifcapable subquery {
000382 do_test limit-9.3 {
000383 execsql {
000384 SELECT * FROM (SELECT * FROM t6 LIMIT 3)
000385 UNION
000386 SELECT * FROM (SELECT * FROM t7 LIMIT 3)
000387 ORDER BY 1
000388 }
000389 } {1 2 3}
000390 do_test limit-9.4 {
000391 execsql {
000392 SELECT * FROM (SELECT * FROM t6 LIMIT 3)
000393 UNION
000394 SELECT * FROM (SELECT * FROM t7 LIMIT 3)
000395 ORDER BY 1
000396 LIMIT 2
000397 }
000398 } {1 2}
000399 }
000400 do_test limit-9.5 {
000401 catchsql {
000402 SELECT * FROM t6 LIMIT 3
000403 UNION
000404 SELECT * FROM t7 LIMIT 3
000405 }
000406 } {1 {LIMIT clause should come after UNION not before}}
000407 }
000408
000409 # Test LIMIT and OFFSET using SQL variables.
000410 do_test limit-10.1 {
000411 set limit 10
000412 db eval {
000413 SELECT x FROM t1 LIMIT :limit;
000414 }
000415 } {31 30 29 28 27 26 25 24 23 22}
000416 do_test limit-10.2 {
000417 set limit 5
000418 set offset 5
000419 db eval {
000420 SELECT x FROM t1 LIMIT :limit OFFSET :offset;
000421 }
000422 } {26 25 24 23 22}
000423 do_test limit-10.3 {
000424 set limit -1
000425 db eval {
000426 SELECT x FROM t1 WHERE x<10 LIMIT :limit;
000427 }
000428 } {9 8 7 6 5 4 3 2 1 0}
000429 do_test limit-10.4 {
000430 set limit 1.5
000431 set rc [catch {
000432 db eval {
000433 SELECT x FROM t1 WHERE x<10 LIMIT :limit;
000434 } } msg]
000435 list $rc $msg
000436 } {1 {datatype mismatch}}
000437 do_test limit-10.5 {
000438 set limit "hello world"
000439 set rc [catch {
000440 db eval {
000441 SELECT x FROM t1 WHERE x<10 LIMIT :limit;
000442 } } msg]
000443 list $rc $msg
000444 } {1 {datatype mismatch}}
000445
000446 ifcapable subquery {
000447 do_test limit-11.1 {
000448 db eval {
000449 SELECT x FROM (SELECT x FROM t1 ORDER BY x LIMIT 0) ORDER BY x
000450 }
000451 } {}
000452 } ;# ifcapable subquery
000453
000454 # Test error processing.
000455 #
000456 do_test limit-12.1 {
000457 catchsql {
000458 SELECT * FROM t1 LIMIT replace(1)
000459 }
000460 } {1 {wrong number of arguments to function replace()}}
000461 do_test limit-12.2 {
000462 catchsql {
000463 SELECT * FROM t1 LIMIT 5 OFFSET replace(1)
000464 }
000465 } {1 {wrong number of arguments to function replace()}}
000466 do_test limit-12.3 {
000467 catchsql {
000468 SELECT * FROM t1 LIMIT x
000469 }
000470 } {1 {no such column: x}}
000471 do_test limit-12.4 {
000472 catchsql {
000473 SELECT * FROM t1 LIMIT 1 OFFSET x
000474 }
000475 } {1 {no such column: x}}
000476
000477 # Ticket [db4d96798da8b]
000478 # LIMIT does not work with nested views containing UNION ALL
000479 #
000480 do_test limit-13.1 {
000481 db eval {
000482 CREATE TABLE t13(x);
000483 INSERT INTO t13 VALUES(1),(2);
000484 CREATE VIEW v13a AS SELECT x AS y FROM t13;
000485 CREATE VIEW v13b AS SELECT y AS z FROM v13a UNION ALL SELECT y+10 FROM v13a;
000486 CREATE VIEW v13c AS SELECT z FROM v13b UNION ALL SELECT z+20 FROM v13b;
000487 }
000488 } {}
000489 do_test limit-13.2 {
000490 db eval {SELECT z FROM v13c LIMIT 1}
000491 } {1}
000492 do_test limit-13.3 {
000493 db eval {SELECT z FROM v13c LIMIT 2}
000494 } {1 2}
000495 do_test limit-13.4 {
000496 db eval {SELECT z FROM v13c LIMIT 3}
000497 } {1 2 11}
000498 do_test limit-13.5 {
000499 db eval {SELECT z FROM v13c LIMIT 4}
000500 } {1 2 11 12}
000501 do_test limit-13.6 {
000502 db eval {SELECT z FROM v13c LIMIT 5}
000503 } {1 2 11 12 21}
000504 do_test limit-13.7 {
000505 db eval {SELECT z FROM v13c LIMIT 6}
000506 } {1 2 11 12 21 22}
000507 do_test limit-13.8 {
000508 db eval {SELECT z FROM v13c LIMIT 7}
000509 } {1 2 11 12 21 22 31}
000510 do_test limit-13.9 {
000511 db eval {SELECT z FROM v13c LIMIT 8}
000512 } {1 2 11 12 21 22 31 32}
000513 do_test limit-13.10 {
000514 db eval {SELECT z FROM v13c LIMIT 9}
000515 } {1 2 11 12 21 22 31 32}
000516 do_test limit-13.11 {
000517 db eval {SELECT z FROM v13c LIMIT 1 OFFSET 1}
000518 } {2}
000519 do_test limit-13.12 {
000520 db eval {SELECT z FROM v13c LIMIT 2 OFFSET 1}
000521 } {2 11}
000522 do_test limit-13.13 {
000523 db eval {SELECT z FROM v13c LIMIT 3 OFFSET 1}
000524 } {2 11 12}
000525 do_test limit-13.14 {
000526 db eval {SELECT z FROM v13c LIMIT 4 OFFSET 1}
000527 } {2 11 12 21}
000528 do_test limit-13.15 {
000529 db eval {SELECT z FROM v13c LIMIT 5 OFFSET 1}
000530 } {2 11 12 21 22}
000531 do_test limit-13.16 {
000532 db eval {SELECT z FROM v13c LIMIT 6 OFFSET 1}
000533 } {2 11 12 21 22 31}
000534 do_test limit-13.17 {
000535 db eval {SELECT z FROM v13c LIMIT 7 OFFSET 1}
000536 } {2 11 12 21 22 31 32}
000537 do_test limit-13.18 {
000538 db eval {SELECT z FROM v13c LIMIT 8 OFFSET 1}
000539 } {2 11 12 21 22 31 32}
000540 do_test limit-13.21 {
000541 db eval {SELECT z FROM v13c LIMIT 1 OFFSET 2}
000542 } {11}
000543 do_test limit-13.22 {
000544 db eval {SELECT z FROM v13c LIMIT 2 OFFSET 2}
000545 } {11 12}
000546 do_test limit-13.23 {
000547 db eval {SELECT z FROM v13c LIMIT 3 OFFSET 2}
000548 } {11 12 21}
000549 do_test limit-13.24 {
000550 db eval {SELECT z FROM v13c LIMIT 4 OFFSET 2}
000551 } {11 12 21 22}
000552 do_test limit-13.25 {
000553 db eval {SELECT z FROM v13c LIMIT 5 OFFSET 2}
000554 } {11 12 21 22 31}
000555 do_test limit-13.26 {
000556 db eval {SELECT z FROM v13c LIMIT 6 OFFSET 2}
000557 } {11 12 21 22 31 32}
000558 do_test limit-13.27 {
000559 db eval {SELECT z FROM v13c LIMIT 7 OFFSET 2}
000560 } {11 12 21 22 31 32}
000561 do_test limit-13.31 {
000562 db eval {SELECT z FROM v13c LIMIT 1 OFFSET 3}
000563 } {12}
000564 do_test limit-13.32 {
000565 db eval {SELECT z FROM v13c LIMIT 2 OFFSET 3}
000566 } {12 21}
000567 do_test limit-13.33 {
000568 db eval {SELECT z FROM v13c LIMIT 3 OFFSET 3}
000569 } {12 21 22}
000570 do_test limit-13.34 {
000571 db eval {SELECT z FROM v13c LIMIT 4 OFFSET 3}
000572 } {12 21 22 31}
000573 do_test limit-13.35 {
000574 db eval {SELECT z FROM v13c LIMIT 5 OFFSET 3}
000575 } {12 21 22 31 32}
000576 do_test limit-13.36 {
000577 db eval {SELECT z FROM v13c LIMIT 6 OFFSET 3}
000578 } {12 21 22 31 32}
000579 do_test limit-13.41 {
000580 db eval {SELECT z FROM v13c LIMIT 1 OFFSET 4}
000581 } {21}
000582 do_test limit-13.42 {
000583 db eval {SELECT z FROM v13c LIMIT 2 OFFSET 4}
000584 } {21 22}
000585 do_test limit-13.43 {
000586 db eval {SELECT z FROM v13c LIMIT 3 OFFSET 4}
000587 } {21 22 31}
000588 do_test limit-13.44 {
000589 db eval {SELECT z FROM v13c LIMIT 4 OFFSET 4}
000590 } {21 22 31 32}
000591 do_test limit-13.45 {
000592 db eval {SELECT z FROM v13c LIMIT 5 OFFSET 4}
000593 } {21 22 31 32}
000594 do_test limit-13.51 {
000595 db eval {SELECT z FROM v13c LIMIT 1 OFFSET 5}
000596 } {22}
000597 do_test limit-13.52 {
000598 db eval {SELECT z FROM v13c LIMIT 2 OFFSET 5}
000599 } {22 31}
000600 do_test limit-13.53 {
000601 db eval {SELECT z FROM v13c LIMIT 3 OFFSET 5}
000602 } {22 31 32}
000603 do_test limit-13.54 {
000604 db eval {SELECT z FROM v13c LIMIT 4 OFFSET 5}
000605 } {22 31 32}
000606 do_test limit-13.61 {
000607 db eval {SELECT z FROM v13c LIMIT 1 OFFSET 6}
000608 } {31}
000609 do_test limit-13.62 {
000610 db eval {SELECT z FROM v13c LIMIT 2 OFFSET 6}
000611 } {31 32}
000612 do_test limit-13.63 {
000613 db eval {SELECT z FROM v13c LIMIT 3 OFFSET 6}
000614 } {31 32}
000615 do_test limit-13.71 {
000616 db eval {SELECT z FROM v13c LIMIT 1 OFFSET 7}
000617 } {32}
000618 do_test limit-13.72 {
000619 db eval {SELECT z FROM v13c LIMIT 2 OFFSET 7}
000620 } {32}
000621 do_test limit-13.81 {
000622 db eval {SELECT z FROM v13c LIMIT 1 OFFSET 8}
000623 } {}
000624
000625 do_execsql_test limit-14.1 {
000626 SELECT 123 LIMIT 1 OFFSET 0
000627 } {123}
000628 do_execsql_test limit-14.2 {
000629 SELECT 123 LIMIT 1 OFFSET 1
000630 } {}
000631 do_execsql_test limit-14.3 {
000632 SELECT 123 LIMIT 0 OFFSET 0
000633 } {}
000634 do_execsql_test limit-14.4 {
000635 SELECT 123 LIMIT 0 OFFSET 1
000636 } {}
000637 do_execsql_test limit-14.6 {
000638 SELECT 123 LIMIT -1 OFFSET 0
000639 } {123}
000640 do_execsql_test limit-14.7 {
000641 SELECT 123 LIMIT -1 OFFSET 1
000642 } {}
000643
000644 # 2021-03-05 dbsqlfuzz crash-d811039c9f44f2d43199d5889fcf4085ef6221b9
000645 #
000646 reset_db
000647 do_execsql_test limit-15.1 {
000648 CREATE TABLE t1(a PRIMARY KEY, b TEXT);
000649 CREATE TABLE t4(c PRIMARY KEY, d);
000650 CREATE TABLE t5(e PRIMARY KEY, f);
000651 CREATE TABLE t6(g, h);
000652 CREATE TABLE t3_a(k, v);
000653 CREATE TABLE t3_b(k, v);
000654 CREATE VIEW t3 AS SELECT * FROM t3_a UNION ALL SELECT * FROM t3_b;
000655 INSERT INTO t5(e,f) VALUES(500000,'orange');
000656 INSERT INTO t4(c,d) VALUES(300000,'blue'),(400,'green'),(8000,'grey');
000657 INSERT INTO t1(a,b) VALUES(300000,'purple');
000658 INSERT INTO t3_a VALUES(300000,'yellow'),(500,'pink'),(8000,'red');
000659 INSERT INTO t6 default values;
000660 SELECT (
000661 SELECT 100000 FROM
000662 (SELECT 200000 FROM t6 WHERE a = ( SELECT 300000 FROM t3 WHERE a ) ),
000663 (SELECT 400000 FROM t5 WHERE e=500000),
000664 (SELECT 600000 FROM t4 WHERE c=a)
000665 ) FROM t1;
000666 } {100000}
000667
000668 finish_test