/ 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 Unified Diffs Ignore Whitespace Patch

Changes to src/select.c.

8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
....
2144
2145
2146
2147
2148
2149
2150






2151
2152
2153
2154
2155
2156
2157
2158
2159
**    May you find forgiveness for yourself and forgive others.
**    May you share freely, never taking more than you give.
**
*************************************************************************
** This file contains C code routines that are called by the parser
** to handle SELECT statements in SQLite.
**
** $Id: select.c,v 1.142 2003/07/16 02:19:38 drh Exp $
*/
#include "sqliteInt.h"


/*
** Allocate a new Select structure and return a pointer to that
** structure.
................................................................................
  **
  ** The phrase "LIMIT 0" means all rows are shown, not zero rows.
  ** If the comparison is p->nLimit<=0 then "LIMIT 0" shows
  ** all rows.  It is the same as no limit. If the comparision is
  ** p->nLimit<0 then "LIMIT 0" show no rows at all.
  ** "LIMIT -1" always shows all rows.  There is some
  ** contraversy about what the correct behavior should be.






  */
  if( p->nLimit<=0 ){
    p->nLimit = -1;
  }else{
    int iMem = pParse->nMem++;
    sqliteVdbeAddOp(v, OP_Integer, -p->nLimit, 0);
    sqliteVdbeAddOp(v, OP_MemStore, iMem, 1);
    p->nLimit = iMem;
  }







|







 







>
>
>
>
>
>

|







8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
....
2144
2145
2146
2147
2148
2149
2150
2151
2152
2153
2154
2155
2156
2157
2158
2159
2160
2161
2162
2163
2164
2165
**    May you find forgiveness for yourself and forgive others.
**    May you share freely, never taking more than you give.
**
*************************************************************************
** This file contains C code routines that are called by the parser
** to handle SELECT statements in SQLite.
**
** $Id: select.c,v 1.143 2003/07/16 11:51:36 drh Exp $
*/
#include "sqliteInt.h"


/*
** Allocate a new Select structure and return a pointer to that
** structure.
................................................................................
  **
  ** The phrase "LIMIT 0" means all rows are shown, not zero rows.
  ** If the comparison is p->nLimit<=0 then "LIMIT 0" shows
  ** all rows.  It is the same as no limit. If the comparision is
  ** p->nLimit<0 then "LIMIT 0" show no rows at all.
  ** "LIMIT -1" always shows all rows.  There is some
  ** contraversy about what the correct behavior should be.
  **
  ** Note that up until this point, the nLimit and nOffset hold
  ** the numeric values of the limit and offset that appeared in
  ** the original SQL.  After this code, the nLimit and nOffset hold
  ** the register number of counters used to track the limit and
  ** offset.
  */
  if( p->nLimit<0 ){
    p->nLimit = -1;
  }else{
    int iMem = pParse->nMem++;
    sqliteVdbeAddOp(v, OP_Integer, -p->nLimit, 0);
    sqliteVdbeAddOp(v, OP_MemStore, iMem, 1);
    p->nLimit = iMem;
  }

Changes to test/limit.test.

8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
...
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204



205







206
#    May you share freely, never taking more than you give.
#
#***********************************************************************
# This file implements regression tests for SQLite library.  The
# focus of this file is testing the LIMIT ... OFFSET ... clause
#  of SELECT statements.
#
# $Id: limit.test,v 1.8 2003/07/16 02:19:38 drh Exp $

set testdir [file dirname $argv0]
source $testdir/tester.tcl

# Build some test data
#
set fd [open data1.txt w]
................................................................................
do_test limit-6.4 {
  execsql {
    SELECT * FROM t6 LIMIT -432 OFFSET 2;
  }
} {3 4}
do_test limit-6.5 {
  execsql {
    SELECT * FROM t6 LIMIT 0
  }
} {1 2 3 4}
do_test limit-6.6 {
  execsql {
    SELECT * FROM t6 LIMIT 0 OFFSET 1
  }
} {2 3 4}











finish_test







|







 







|




|


>
>
>
|
>
>
>
>
>
>
>

8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
...
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
#    May you share freely, never taking more than you give.
#
#***********************************************************************
# This file implements regression tests for SQLite library.  The
# focus of this file is testing the LIMIT ... OFFSET ... clause
#  of SELECT statements.
#
# $Id: limit.test,v 1.9 2003/07/16 11:51:36 drh Exp $

set testdir [file dirname $argv0]
source $testdir/tester.tcl

# Build some test data
#
set fd [open data1.txt w]
................................................................................
do_test limit-6.4 {
  execsql {
    SELECT * FROM t6 LIMIT -432 OFFSET 2;
  }
} {3 4}
do_test limit-6.5 {
  execsql {
    SELECT * FROM t6 LIMIT -1
  }
} {1 2 3 4}
do_test limit-6.6 {
  execsql {
    SELECT * FROM t6 LIMIT -1 OFFSET 1
  }
} {2 3 4}
do_test limit-6.7 {
  execsql {
    SELECT * FROM t6 LIMIT 0
  }
} {}
do_test limit-6.8 {
  execsql {
    SELECT * FROM t6 LIMIT 0 OFFSET 1
  }
} {}

finish_test

Changes to www/lang.tcl.

1
2
3
4
5
6
7
8
9
10
11
....
1546
1547
1548
1549
1550
1551
1552
1553
1554
1555
1556
1557
1558
1559
1560
#
# Run this Tcl script to generate the sqlite.html file.
#
set rcsid {$Id: lang.tcl,v 1.63 2003/06/15 23:49:39 drh Exp $}

puts {<html>
<head>
  <title>Query Language Understood By SQLite</title>
</head>
<body bgcolor=white>
<h1 align=center>
................................................................................
key for the sort.  The expressions do not have to be part of the
result for a simple SELECT, but in a compound SELECT each sort
expression must exactly match one of the result columns.  Each
sort expression may be optionally followed by ASC or DESC to specify
the sort order.</p>

<p>The LIMIT clause places an upper bound on the number of rows
returned in the result.  A LIMIT of 0 indicates no upper bound.
The optional OFFSET following LIMIT specifies how many
rows to skip at the beginning of the result set.</p>

<p>A compound SELECT is formed from two or more simple SELECTs connected
by one of the operators UNION, UNION ALL, INTERSECT, or EXCEPT.  In
a compound SELECT, all the constituent SELECTs must specify the
same number of result columns.  There may be only a single ORDER BY



|







 







|







1
2
3
4
5
6
7
8
9
10
11
....
1546
1547
1548
1549
1550
1551
1552
1553
1554
1555
1556
1557
1558
1559
1560
#
# Run this Tcl script to generate the sqlite.html file.
#
set rcsid {$Id: lang.tcl,v 1.64 2003/07/16 11:51:36 drh Exp $}

puts {<html>
<head>
  <title>Query Language Understood By SQLite</title>
</head>
<body bgcolor=white>
<h1 align=center>
................................................................................
key for the sort.  The expressions do not have to be part of the
result for a simple SELECT, but in a compound SELECT each sort
expression must exactly match one of the result columns.  Each
sort expression may be optionally followed by ASC or DESC to specify
the sort order.</p>

<p>The LIMIT clause places an upper bound on the number of rows
returned in the result.  A negative LIMIT indicates no upper bound.
The optional OFFSET following LIMIT specifies how many
rows to skip at the beginning of the result set.</p>

<p>A compound SELECT is formed from two or more simple SELECTs connected
by one of the operators UNION, UNION ALL, INTERSECT, or EXCEPT.  In
a compound SELECT, all the constituent SELECTs must specify the
same number of result columns.  There may be only a single ORDER BY