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 473
