SQLite

Check-in [a31d0bd90a]
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
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: a31d0bd90af7cc95f8e36ca8ece21515f872235e
User & Date: drh 2003-07-16 11:51:36.000
Context
2003-07-16
17:10
Fix typo on the homepage. (CVS 1054) (check-in: c74107d63a user: drh tags: trunk)
11:51
Make LIMIT 0 return no rows. LIMIT -1 still returns all rows. Ticket #346. (CVS 1053) (check-in: a31d0bd90a 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: e6a752bfef user: drh tags: trunk)
Changes
Side-by-Side Diff Ignore Whitespace Patch
Changes to src/select.c.
︙
8
9
10
11
12
13
14
15

16
17
18
19
20
21
22

8
9
10
11
12
13
14

15
16
17
18
19
20
21
22








-
+








**    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 $
** $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.
︙
2144
2145
2146
2147
2148
2149
2150






2151
2152

2153
2154
2155
2156
2157
2158
2159

2144
2145
2146
2147
2148
2149
2150
2151
2152
2153
2154
2155
2156
2157

2158
2159
2160
2161
2162
2163
2164
2165








+
+
+
+
+
+

-
+








  **
  ** 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 ){
  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

8
9
10
11
12
13
14

15
16
17
18
19
20
21
22








-
+








#    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 $
# $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]
︙
190
191
192
193
194
195
196
197

198
199
200
201
202

203
204



205








206

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








-
+




-
+


+
+
+
-
+
+
+
+
+
+
+
+


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
    SELECT * FROM t6 LIMIT -1
  }
} {1 2 3 4}
do_test limit-6.6 {
  execsql {
    SELECT * FROM t6 LIMIT 0 OFFSET 1
    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

1
2
3

4
5
6
7
8
9
10
11




-
+








#
# 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 $}
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>
︙
1546
1547
1548
1549
1550
1551
1552
1553

1554
1555
1556
1557
1558
1559
1560

1546
1547
1548
1549
1550
1551
1552

1553
1554
1555
1556
1557
1558
1559
1560








-
+








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.
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
︙