/ Check-in [a31d0bd9]
Login

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
Comment:Make LIMIT 0 return no rows. LIMIT -1 still returns all rows. Ticket #346. (CVS 1053)
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: a31d0bd90af7cc95f8e36ca8ece21515f872235e
User & Date: drh 2003-07-16 11:51:36
Context
2003-07-16
17:10
Fix typo on the homepage. (CVS 1054) check-in: c74107d6 user: drh tags: trunk
11:51
Make LIMIT 0 return no rows. LIMIT -1 still returns all rows. Ticket #346. (CVS 1053) check-in: a31d0bd9 user: drh tags: trunk
02:19
Allow negative values for LIMIT and OFFSET. Add tests for negative LIMITs and OFFSETs. Make the OFFSET work even if LIMIT is 0 or negative. (CVS 1052) check-in: e6a752bf user: drh tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/select.c.

     8      8   **    May you find forgiveness for yourself and forgive others.
     9      9   **    May you share freely, never taking more than you give.
    10     10   **
    11     11   *************************************************************************
    12     12   ** This file contains C code routines that are called by the parser
    13     13   ** to handle SELECT statements in SQLite.
    14     14   **
    15         -** $Id: select.c,v 1.142 2003/07/16 02:19:38 drh Exp $
           15  +** $Id: select.c,v 1.143 2003/07/16 11:51:36 drh Exp $
    16     16   */
    17     17   #include "sqliteInt.h"
    18     18   
    19     19   
    20     20   /*
    21     21   ** Allocate a new Select structure and return a pointer to that
    22     22   ** structure.
................................................................................
  2144   2144     **
  2145   2145     ** The phrase "LIMIT 0" means all rows are shown, not zero rows.
  2146   2146     ** If the comparison is p->nLimit<=0 then "LIMIT 0" shows
  2147   2147     ** all rows.  It is the same as no limit. If the comparision is
  2148   2148     ** p->nLimit<0 then "LIMIT 0" show no rows at all.
  2149   2149     ** "LIMIT -1" always shows all rows.  There is some
  2150   2150     ** contraversy about what the correct behavior should be.
         2151  +  **
         2152  +  ** Note that up until this point, the nLimit and nOffset hold
         2153  +  ** the numeric values of the limit and offset that appeared in
         2154  +  ** the original SQL.  After this code, the nLimit and nOffset hold
         2155  +  ** the register number of counters used to track the limit and
         2156  +  ** offset.
  2151   2157     */
  2152         -  if( p->nLimit<=0 ){
         2158  +  if( p->nLimit<0 ){
  2153   2159       p->nLimit = -1;
  2154   2160     }else{
  2155   2161       int iMem = pParse->nMem++;
  2156   2162       sqliteVdbeAddOp(v, OP_Integer, -p->nLimit, 0);
  2157   2163       sqliteVdbeAddOp(v, OP_MemStore, iMem, 1);
  2158   2164       p->nLimit = iMem;
  2159   2165     }

Changes to test/limit.test.

     8      8   #    May you share freely, never taking more than you give.
     9      9   #
    10     10   #***********************************************************************
    11     11   # This file implements regression tests for SQLite library.  The
    12     12   # focus of this file is testing the LIMIT ... OFFSET ... clause
    13     13   #  of SELECT statements.
    14     14   #
    15         -# $Id: limit.test,v 1.8 2003/07/16 02:19:38 drh Exp $
           15  +# $Id: limit.test,v 1.9 2003/07/16 11:51:36 drh Exp $
    16     16   
    17     17   set testdir [file dirname $argv0]
    18     18   source $testdir/tester.tcl
    19     19   
    20     20   # Build some test data
    21     21   #
    22     22   set fd [open data1.txt w]
................................................................................
   190    190   do_test limit-6.4 {
   191    191     execsql {
   192    192       SELECT * FROM t6 LIMIT -432 OFFSET 2;
   193    193     }
   194    194   } {3 4}
   195    195   do_test limit-6.5 {
   196    196     execsql {
   197         -    SELECT * FROM t6 LIMIT 0
          197  +    SELECT * FROM t6 LIMIT -1
   198    198     }
   199    199   } {1 2 3 4}
   200    200   do_test limit-6.6 {
   201    201     execsql {
   202         -    SELECT * FROM t6 LIMIT 0 OFFSET 1
          202  +    SELECT * FROM t6 LIMIT -1 OFFSET 1
   203    203     }
   204    204   } {2 3 4}
          205  +do_test limit-6.7 {
          206  +  execsql {
          207  +    SELECT * FROM t6 LIMIT 0
          208  +  }
          209  +} {}
          210  +do_test limit-6.8 {
          211  +  execsql {
          212  +    SELECT * FROM t6 LIMIT 0 OFFSET 1
          213  +  }
          214  +} {}
   205    215   
   206    216   finish_test

Changes to www/lang.tcl.

     1      1   #
     2      2   # Run this Tcl script to generate the sqlite.html file.
     3      3   #
     4         -set rcsid {$Id: lang.tcl,v 1.63 2003/06/15 23:49:39 drh Exp $}
            4  +set rcsid {$Id: lang.tcl,v 1.64 2003/07/16 11:51:36 drh Exp $}
     5      5   
     6      6   puts {<html>
     7      7   <head>
     8      8     <title>Query Language Understood By SQLite</title>
     9      9   </head>
    10     10   <body bgcolor=white>
    11     11   <h1 align=center>
................................................................................
  1546   1546   key for the sort.  The expressions do not have to be part of the
  1547   1547   result for a simple SELECT, but in a compound SELECT each sort
  1548   1548   expression must exactly match one of the result columns.  Each
  1549   1549   sort expression may be optionally followed by ASC or DESC to specify
  1550   1550   the sort order.</p>
  1551   1551   
  1552   1552   <p>The LIMIT clause places an upper bound on the number of rows
  1553         -returned in the result.  A LIMIT of 0 indicates no upper bound.
         1553  +returned in the result.  A negative LIMIT indicates no upper bound.
  1554   1554   The optional OFFSET following LIMIT specifies how many
  1555   1555   rows to skip at the beginning of the result set.</p>
  1556   1556   
  1557   1557   <p>A compound SELECT is formed from two or more simple SELECTs connected
  1558   1558   by one of the operators UNION, UNION ALL, INTERSECT, or EXCEPT.  In
  1559   1559   a compound SELECT, all the constituent SELECTs must specify the
  1560   1560   same number of result columns.  There may be only a single ORDER BY