/ Check-in [17701224]
Login

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

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 Side-by-Side Diffs Ignore Whitespace Patch

Changes to VERSION.

     1         -2.0.8
            1  +2.1.0

Changes to src/build.c.

    21     21   **     COPY
    22     22   **     VACUUM
    23     23   **     BEGIN TRANSACTION
    24     24   **     COMMIT
    25     25   **     ROLLBACK
    26     26   **     PRAGMA
    27     27   **
    28         -** $Id: build.c,v 1.52 2001/10/22 02:58:10 drh Exp $
           28  +** $Id: build.c,v 1.53 2001/11/06 14:10:42 drh Exp $
    29     29   */
    30     30   #include "sqliteInt.h"
    31     31   #include <ctype.h>
    32     32   
    33     33   /*
    34     34   ** This routine is called after a single SQL statement has been
    35     35   ** parsed and we want to execute the VDBE code to implement 
................................................................................
  1102   1102       a = sqliteRealloc(pList->a, n*sizeof(pList->a[0]));
  1103   1103       if( a==0 ){
  1104   1104         sqliteExprDelete(pExpr);
  1105   1105         return pList;
  1106   1106       }
  1107   1107       pList->a = a;
  1108   1108     }
  1109         -  if( pExpr ){
         1109  +  if( pExpr || pName ){
  1110   1110       i = pList->nExpr++;
  1111   1111       pList->a[i].pExpr = pExpr;
  1112   1112       pList->a[i].zName = 0;
  1113   1113       if( pName ){
  1114   1114         sqliteSetNString(&pList->a[i].zName, pName->z, pName->n, 0);
  1115   1115         sqliteDequote(pList->a[i].zName);
  1116   1116       }

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.46 2001/11/06 04:00:19 drh Exp $
           15  +** $Id: select.c,v 1.47 2001/11/06 14:10:42 drh Exp $
    16     16   */
    17     17   #include "sqliteInt.h"
    18     18   
    19     19   /*
    20     20   ** Allocate a new Select structure and return a pointer to that
    21     21   ** structure.
    22     22   */
................................................................................
   360    360       for(i=0; i<pTabList->nId; i++){
   361    361         Table *pTab = pTabList->a[i].pTab;
   362    362         for(j=0; j<pTab->nCol; j++){
   363    363           Expr *pExpr = sqliteExpr(TK_DOT, 0, 0, 0);
   364    364           if( pExpr==0 ) break;
   365    365           pExpr->pLeft = sqliteExpr(TK_ID, 0, 0, 0);
   366    366           if( pExpr->pLeft==0 ){ sqliteExprDelete(pExpr); break; }
   367         -        pExpr->pLeft->token.z = pTab->zName;
   368         -        pExpr->pLeft->token.n = strlen(pTab->zName);
          367  +        if( pTabList->a[i].zAlias && pTabList->a[i].zAlias[0] ){
          368  +          pExpr->pLeft->token.z = pTabList->a[i].zAlias;
          369  +          pExpr->pLeft->token.n = strlen(pTabList->a[i].zAlias);
          370  +        }else{
          371  +          pExpr->pLeft->token.z = pTab->zName;
          372  +          pExpr->pLeft->token.n = strlen(pTab->zName);
          373  +        }
   369    374           pExpr->pRight = sqliteExpr(TK_ID, 0, 0, 0);
   370    375           if( pExpr->pRight==0 ){ sqliteExprDelete(pExpr); break; }
   371    376           pExpr->pRight->token.z = pTab->aCol[j].zName;
   372    377           pExpr->pRight->token.n = strlen(pTab->aCol[j].zName);
   373    378           pExpr->span.z = "";
   374    379           pExpr->span.n = 0;
   375    380           pEList = sqliteExprListAppend(pEList, pExpr, 0);

Added test/limit.test.

            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.1 2001/11/06 14:10:42 drh Exp $
           16  +
           17  +set testdir [file dirname $argv0]
           18  +source $testdir/tester.tcl
           19  +
           20  +# Build some test data
           21  +#
           22  +set fd [open data1.txt w]
           23  +for {set i 1} {$i<=32} {incr i} {
           24  +  for {set j 0} {pow(2,$j)<$i} {incr j} {}
           25  +  puts $fd "[expr {32-$i}]\t[expr {10-$j}]"
           26  +}
           27  +close $fd
           28  +execsql {
           29  +  CREATE TABLE t1(x int, y int);
           30  +  COPY t1 FROM 'data1.txt'
           31  +}
           32  +file delete data1.txt
           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 {
           41  +  execsql {SELECT x FROM t1 ORDER BY x LIMIT 5}
           42  +} {0 1 2 3 4}
           43  +do_test limit-1.3 {
           44  +  execsql {SELECT x FROM t1 ORDER BY x LIMIT 5 OFFSET 5}
           45  +} {5 6 7 8 9}
           46  +do_test limit-1.4 {
           47  +  execsql {SELECT x FROM t1 ORDER BY x LIMIT 50 OFFSET 30}
           48  +} {30 31}
           49  +do_test limit-1.5 {
           50  +  execsql {SELECT x FROM t1 ORDER BY x LIMIT 50 OFFSET 50}
           51  +} {}
           52  +do_test limit-1.6 {
           53  +  execsql {SELECT * FROM t1 a, t1 b ORDER BY a.x, b.x LIMIT 5}
           54  +} {0 5 0 5 0 5 1 5 0 5 2 5 0 5 3 5 0 5 4 5}
           55  +do_test limit-1.7 {
           56  +  execsql {SELECT * FROM t1 a, t1 b ORDER BY a.x, b.x LIMIT 5 OFFSET 32}
           57  +} {1 5 0 5 1 5 1 5 1 5 2 5 1 5 3 5 1 5 4 5}
           58  +
           59  +
           60  +finish_test

Changes to test/select1.test.

     7      7   #    May you find forgiveness for yourself and forgive others.
     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 SELECT statement.
    13     13   #
    14         -# $Id: select1.test,v 1.14 2001/10/19 16:44:58 drh Exp $
           14  +# $Id: select1.test,v 1.15 2001/11/06 14:10:42 drh Exp $
    15     15   
    16     16   set testdir [file dirname $argv0]
    17     17   source $testdir/tester.tcl
    18     18   
    19     19   # Try to select on a non-existant table.
    20     20   #
    21     21   do_test select1-1.1 {
................................................................................
    63     63   } {11 22 1.1 2.2}
    64     64   do_test select1-1.10 {
    65     65     execsql {SELECT test1.f1, test2.r1 FROM test1, test2}
    66     66   } {11 1.1}
    67     67   do_test select1-1.11 {
    68     68     execsql {SELECT test1.f1, test2.r1 FROM test2, test1}
    69     69   } {11 1.1}
           70  +do_test select1-1.11.1 {
           71  +  execsql {SELECT * FROM test2, test1}
           72  +} {1.1 2.2 11 22}
           73  +do_test select1-1.11.2 {
           74  +  execsql {SELECT * FROM test1 AS a, test1 AS b}
           75  +} {11 22 11 22}
    70     76   do_test select1-1.12 {
    71     77     execsql {SELECT max(test1.f1,test2.r1), min(test1.f2,test2.r2)
    72     78              FROM test2, test1}
    73     79   } {11 2.2}
    74     80   do_test select1-1.13 {
    75     81     execsql {SELECT min(test1.f1,test2.r1), max(test1.f2,test2.r2)
    76     82              FROM test1, test2}

Changes to www/changes.tcl.

    16     16     puts "<DT><B>$date</B></DT>"
    17     17     puts "<DD><P><UL>$desc</UL></P></DD>"
    18     18   }
    19     19   
    20     20   chng {2001 ??? ?? (2.1.0)} {
    21     21   <li>Change the format of data records so that records up to 16MB in size
    22     22       can be stored.</li>
           23  +<li>Change the format of indices to allow for better query optimization.</li>
           24  +<li>Implement the "LIMIT ... OFFSET ..." clause on SELECT statements.</li>
    23     25   }
    24     26   
    25     27   chng {2001 Nov 3 (2.0.8)} {
    26     28   <li>Made selected parameters in API functions <b>const</b>. This should
    27     29       be fully backwards compatible.</li>
    28     30   <li>Documentation updates</li>
    29     31   <li>Simplify the design of the VDBE by restricting the number of sorters

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.14 2001/10/19 16:44:58 drh Exp $}
            4  +set rcsid {$Id: lang.tcl,v 1.15 2001/11/06 14:10:42 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>
................................................................................
   505    505   Syntax {sql-statement} {
   506    506   SELECT <result> FROM <table-list> 
   507    507   [WHERE <expression>]
   508    508   [GROUP BY <expr-list>]
   509    509   [HAVING <expression>]
   510    510   [<compound-op> <select>]*
   511    511   [ORDER BY <sort-expr-list>]
          512  +[LIMIT <integer> [OFFSET <integer>]]
   512    513   } {result} {
   513    514   STAR | <result-column> [, <result-column>]*
   514    515   } {result-column} {
   515    516   <expression> [ [AS] <string> ]
   516    517   } {table-list} {
   517    518   <table-name> [, <table-name>]*
   518    519   } {sort-expr-list} {
................................................................................
   554    555   <p>The ORDER BY clause causes the output rows to be sorted.  
   555    556   The argument to ORDER BY is a list of expressions that are used as the
   556    557   key for the sort.  The expressions do not have to be part of the
   557    558   result for a simple SELECT, but in a compound SELECT each sort
   558    559   expression must exactly match one of the result columns.  Each
   559    560   sort expression may be optionally followed by ASC or DESC to specify
   560    561   the sort order.</p>
          562  +
          563  +<p>The LIMIT clause places an upper bound on the number of rows
          564  +returned in the result.  A LIMIT of 0 indicates no upper bound.
          565  +The optional OFFSET following LIMIT specifies how many
          566  +rows to skip at the beginning of the result set.</p>
   561    567   
   562    568   <p>A compound SELECT is formed from two or more simple SELECTs connected
   563    569   by one of the operators UNION, UNION ALL, INTERSECT, or EXCEPT.  In
   564    570   a compound SELECT, all the constituent SELECTs must specify the
   565    571   same number of result columns.  There may be only a single ORDER BY
   566    572   clause at the end of the compound SELECT.  The UNION and UNION ALL
   567    573   operators combine the results of the SELECTs to the right and left into