/ Check-in [17701224]
Login
Overview
Comment:Added support for LIMIT. (CVS 302)
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1:177012249ae93dbea4a11fb50faaae7912848bd0
User & Date: drh 2001-11-06 14:10:42
Context
2001-11-07
14:22
Incremental update. We are in the middle of modifying the index system to support range queries without doing a complete table scan. (CVS 303) check-in: e6ca23fa user: drh tags: trunk
2001-11-06
14:10
Added support for LIMIT. (CVS 302) check-in: 17701224 user: drh tags: trunk
04:00
Implement indices that occur in sort order and the LIMIT...OFFSET clause of SELECT statements. (CVS 301) check-in: eb07768a user: drh tags: trunk
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to VERSION.

1
2.0.8
|
1
2.1.0

Changes to src/build.c.

21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
....
1102
1103
1104
1105
1106
1107
1108
1109
1110
1111
1112
1113
1114
1115
1116
**     COPY
**     VACUUM
**     BEGIN TRANSACTION
**     COMMIT
**     ROLLBACK
**     PRAGMA
**
** $Id: build.c,v 1.52 2001/10/22 02:58:10 drh Exp $
*/
#include "sqliteInt.h"
#include <ctype.h>

/*
** This routine is called after a single SQL statement has been
** parsed and we want to execute the VDBE code to implement 
................................................................................
    a = sqliteRealloc(pList->a, n*sizeof(pList->a[0]));
    if( a==0 ){
      sqliteExprDelete(pExpr);
      return pList;
    }
    pList->a = a;
  }
  if( pExpr ){
    i = pList->nExpr++;
    pList->a[i].pExpr = pExpr;
    pList->a[i].zName = 0;
    if( pName ){
      sqliteSetNString(&pList->a[i].zName, pName->z, pName->n, 0);
      sqliteDequote(pList->a[i].zName);
    }







|







 







|







21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
....
1102
1103
1104
1105
1106
1107
1108
1109
1110
1111
1112
1113
1114
1115
1116
**     COPY
**     VACUUM
**     BEGIN TRANSACTION
**     COMMIT
**     ROLLBACK
**     PRAGMA
**
** $Id: build.c,v 1.53 2001/11/06 14:10:42 drh Exp $
*/
#include "sqliteInt.h"
#include <ctype.h>

/*
** This routine is called after a single SQL statement has been
** parsed and we want to execute the VDBE code to implement 
................................................................................
    a = sqliteRealloc(pList->a, n*sizeof(pList->a[0]));
    if( a==0 ){
      sqliteExprDelete(pExpr);
      return pList;
    }
    pList->a = a;
  }
  if( pExpr || pName ){
    i = pList->nExpr++;
    pList->a[i].pExpr = pExpr;
    pList->a[i].zName = 0;
    if( pName ){
      sqliteSetNString(&pList->a[i].zName, pName->z, pName->n, 0);
      sqliteDequote(pList->a[i].zName);
    }

Changes to src/select.c.

8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
...
360
361
362
363
364
365
366




367
368

369
370
371
372
373
374
375
**    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.46 2001/11/06 04:00:19 drh Exp $
*/
#include "sqliteInt.h"

/*
** Allocate a new Select structure and return a pointer to that
** structure.
*/
................................................................................
    for(i=0; i<pTabList->nId; i++){
      Table *pTab = pTabList->a[i].pTab;
      for(j=0; j<pTab->nCol; j++){
        Expr *pExpr = sqliteExpr(TK_DOT, 0, 0, 0);
        if( pExpr==0 ) break;
        pExpr->pLeft = sqliteExpr(TK_ID, 0, 0, 0);
        if( pExpr->pLeft==0 ){ sqliteExprDelete(pExpr); break; }




        pExpr->pLeft->token.z = pTab->zName;
        pExpr->pLeft->token.n = strlen(pTab->zName);

        pExpr->pRight = sqliteExpr(TK_ID, 0, 0, 0);
        if( pExpr->pRight==0 ){ sqliteExprDelete(pExpr); break; }
        pExpr->pRight->token.z = pTab->aCol[j].zName;
        pExpr->pRight->token.n = strlen(pTab->aCol[j].zName);
        pExpr->span.z = "";
        pExpr->span.n = 0;
        pEList = sqliteExprListAppend(pEList, pExpr, 0);







|







 







>
>
>
>
|
|
>







8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
...
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
**    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.47 2001/11/06 14:10:42 drh Exp $
*/
#include "sqliteInt.h"

/*
** Allocate a new Select structure and return a pointer to that
** structure.
*/
................................................................................
    for(i=0; i<pTabList->nId; i++){
      Table *pTab = pTabList->a[i].pTab;
      for(j=0; j<pTab->nCol; j++){
        Expr *pExpr = sqliteExpr(TK_DOT, 0, 0, 0);
        if( pExpr==0 ) break;
        pExpr->pLeft = sqliteExpr(TK_ID, 0, 0, 0);
        if( pExpr->pLeft==0 ){ sqliteExprDelete(pExpr); break; }
        if( pTabList->a[i].zAlias && pTabList->a[i].zAlias[0] ){
          pExpr->pLeft->token.z = pTabList->a[i].zAlias;
          pExpr->pLeft->token.n = strlen(pTabList->a[i].zAlias);
        }else{
          pExpr->pLeft->token.z = pTab->zName;
          pExpr->pLeft->token.n = strlen(pTab->zName);
        }
        pExpr->pRight = sqliteExpr(TK_ID, 0, 0, 0);
        if( pExpr->pRight==0 ){ sqliteExprDelete(pExpr); break; }
        pExpr->pRight->token.z = pTab->aCol[j].zName;
        pExpr->pRight->token.n = strlen(pTab->aCol[j].zName);
        pExpr->span.z = "";
        pExpr->span.n = 0;
        pEList = sqliteExprListAppend(pEList, pExpr, 0);

Added test/limit.test.

























































































































>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
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
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
# 2001 November 6
#
# The author disclaims copyright to this source code.  In place of
# a legal notice, here is a blessing:
#
#    May you do good and not evil.
#    May you find forgiveness for yourself and forgive others.
#    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.1 2001/11/06 14:10:42 drh Exp $

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

# Build some test data
#
set fd [open data1.txt w]
for {set i 1} {$i<=32} {incr i} {
  for {set j 0} {pow(2,$j)<$i} {incr j} {}
  puts $fd "[expr {32-$i}]\t[expr {10-$j}]"
}
close $fd
execsql {
  CREATE TABLE t1(x int, y int);
  COPY t1 FROM 'data1.txt'
}
file delete data1.txt

do_test limit-1.0 {
  execsql {SELECT count(*) FROM t1}
} {32}
do_test limit-1.1 {
  execsql {SELECT count(*) FROM t1 LIMIT  5}
} {32}
do_test limit-1.2 {
  execsql {SELECT x FROM t1 ORDER BY x LIMIT 5}
} {0 1 2 3 4}
do_test limit-1.3 {
  execsql {SELECT x FROM t1 ORDER BY x LIMIT 5 OFFSET 5}
} {5 6 7 8 9}
do_test limit-1.4 {
  execsql {SELECT x FROM t1 ORDER BY x LIMIT 50 OFFSET 30}
} {30 31}
do_test limit-1.5 {
  execsql {SELECT x FROM t1 ORDER BY x LIMIT 50 OFFSET 50}
} {}
do_test limit-1.6 {
  execsql {SELECT * FROM t1 a, t1 b ORDER BY a.x, b.x LIMIT 5}
} {0 5 0 5 0 5 1 5 0 5 2 5 0 5 3 5 0 5 4 5}
do_test limit-1.7 {
  execsql {SELECT * FROM t1 a, t1 b ORDER BY a.x, b.x LIMIT 5 OFFSET 32}
} {1 5 0 5 1 5 1 5 1 5 2 5 1 5 3 5 1 5 4 5}


finish_test

Changes to test/select1.test.

7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
..
63
64
65
66
67
68
69






70
71
72
73
74
75
76
#    May you find forgiveness for yourself and forgive others.
#    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 SELECT statement.
#
# $Id: select1.test,v 1.14 2001/10/19 16:44:58 drh Exp $

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

# Try to select on a non-existant table.
#
do_test select1-1.1 {
................................................................................
} {11 22 1.1 2.2}
do_test select1-1.10 {
  execsql {SELECT test1.f1, test2.r1 FROM test1, test2}
} {11 1.1}
do_test select1-1.11 {
  execsql {SELECT test1.f1, test2.r1 FROM test2, test1}
} {11 1.1}






do_test select1-1.12 {
  execsql {SELECT max(test1.f1,test2.r1), min(test1.f2,test2.r2)
           FROM test2, test1}
} {11 2.2}
do_test select1-1.13 {
  execsql {SELECT min(test1.f1,test2.r1), max(test1.f2,test2.r2)
           FROM test1, test2}







|







 







>
>
>
>
>
>







7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
..
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
#    May you find forgiveness for yourself and forgive others.
#    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 SELECT statement.
#
# $Id: select1.test,v 1.15 2001/11/06 14:10:42 drh Exp $

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

# Try to select on a non-existant table.
#
do_test select1-1.1 {
................................................................................
} {11 22 1.1 2.2}
do_test select1-1.10 {
  execsql {SELECT test1.f1, test2.r1 FROM test1, test2}
} {11 1.1}
do_test select1-1.11 {
  execsql {SELECT test1.f1, test2.r1 FROM test2, test1}
} {11 1.1}
do_test select1-1.11.1 {
  execsql {SELECT * FROM test2, test1}
} {1.1 2.2 11 22}
do_test select1-1.11.2 {
  execsql {SELECT * FROM test1 AS a, test1 AS b}
} {11 22 11 22}
do_test select1-1.12 {
  execsql {SELECT max(test1.f1,test2.r1), min(test1.f2,test2.r2)
           FROM test2, test1}
} {11 2.2}
do_test select1-1.13 {
  execsql {SELECT min(test1.f1,test2.r1), max(test1.f2,test2.r2)
           FROM test1, test2}

Changes to www/changes.tcl.

16
17
18
19
20
21
22


23
24
25
26
27
28
29
  puts "<DT><B>$date</B></DT>"
  puts "<DD><P><UL>$desc</UL></P></DD>"
}

chng {2001 ??? ?? (2.1.0)} {
<li>Change the format of data records so that records up to 16MB in size
    can be stored.</li>


}

chng {2001 Nov 3 (2.0.8)} {
<li>Made selected parameters in API functions <b>const</b>. This should
    be fully backwards compatible.</li>
<li>Documentation updates</li>
<li>Simplify the design of the VDBE by restricting the number of sorters







>
>







16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
  puts "<DT><B>$date</B></DT>"
  puts "<DD><P><UL>$desc</UL></P></DD>"
}

chng {2001 ??? ?? (2.1.0)} {
<li>Change the format of data records so that records up to 16MB in size
    can be stored.</li>
<li>Change the format of indices to allow for better query optimization.</li>
<li>Implement the "LIMIT ... OFFSET ..." clause on SELECT statements.</li>
}

chng {2001 Nov 3 (2.0.8)} {
<li>Made selected parameters in API functions <b>const</b>. This should
    be fully backwards compatible.</li>
<li>Documentation updates</li>
<li>Simplify the design of the VDBE by restricting the number of sorters

Changes to www/lang.tcl.

1
2
3
4
5
6
7
8
9
10
11
...
505
506
507
508
509
510
511

512
513
514
515
516
517
518
...
554
555
556
557
558
559
560





561
562
563
564
565
566
567
#
# Run this Tcl script to generate the sqlite.html file.
#
set rcsid {$Id: lang.tcl,v 1.14 2001/10/19 16:44:58 drh Exp $}

puts {<html>
<head>
  <title>Query Language Understood By SQLite</title>
</head>
<body bgcolor=white>
<h1 align=center>
................................................................................
Syntax {sql-statement} {
SELECT <result> FROM <table-list> 
[WHERE <expression>]
[GROUP BY <expr-list>]
[HAVING <expression>]
[<compound-op> <select>]*
[ORDER BY <sort-expr-list>]

} {result} {
STAR | <result-column> [, <result-column>]*
} {result-column} {
<expression> [ [AS] <string> ]
} {table-list} {
<table-name> [, <table-name>]*
} {sort-expr-list} {
................................................................................
<p>The ORDER BY clause causes the output rows to be sorted.  
The argument to ORDER BY is a list of expressions that are used as the
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>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
clause at the end of the compound SELECT.  The UNION and UNION ALL
operators combine the results of the SELECTs to the right and left into



|







 







>







 







>
>
>
>
>







1
2
3
4
5
6
7
8
9
10
11
...
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
...
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
#
# Run this Tcl script to generate the sqlite.html file.
#
set rcsid {$Id: lang.tcl,v 1.15 2001/11/06 14:10:42 drh Exp $}

puts {<html>
<head>
  <title>Query Language Understood By SQLite</title>
</head>
<body bgcolor=white>
<h1 align=center>
................................................................................
Syntax {sql-statement} {
SELECT <result> FROM <table-list> 
[WHERE <expression>]
[GROUP BY <expr-list>]
[HAVING <expression>]
[<compound-op> <select>]*
[ORDER BY <sort-expr-list>]
[LIMIT <integer> [OFFSET <integer>]]
} {result} {
STAR | <result-column> [, <result-column>]*
} {result-column} {
<expression> [ [AS] <string> ]
} {table-list} {
<table-name> [, <table-name>]*
} {sort-expr-list} {
................................................................................
<p>The ORDER BY clause causes the output rows to be sorted.  
The argument to ORDER BY is a list of expressions that are used as the
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
clause at the end of the compound SELECT.  The UNION and UNION ALL
operators combine the results of the SELECTs to the right and left into