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