third_party/sqlite/src/test/limit.test

   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