SQLite

Check-in [515e5033a5]
Login

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

Overview
Comment:Allow expressions (including variables) in LIMIT and OFFSET clauses. Ticket #1096. (CVS 2316)
Downloads: Tarball | ZIP archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 515e5033a5482f55e7edb66d69ff3da7e234ff2e
User & Date: danielk1977 2005-02-05 12:48:48.000
Context
2005-02-06
02:45
Add the (highly experimental) omit_readlock pragma that disables the use of readlocks on read-only databases that are connected using ATTACH. (CVS 2317) (check-in: 2155448d21 user: drh tags: trunk)
2005-02-05
12:48
Allow expressions (including variables) in LIMIT and OFFSET clauses. Ticket #1096. (CVS 2316) (check-in: 515e5033a5 user: danielk1977 tags: trunk)
07:33
Add a numeric version number. Ticket #1097. (CVS 2315) (check-in: a9c33a804d user: danielk1977 tags: trunk)
Changes
Unified Diff Ignore Whitespace Patch
Changes to src/expr.c.
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 routines used for analyzing expressions and
** for generating VDBE code that evaluates expressions in SQLite.
**
** $Id: expr.c,v 1.191 2005/02/04 04:07:17 danielk1977 Exp $
*/
#include "sqliteInt.h"
#include <ctype.h>

/*
** Return the 'affinity' of the expression pExpr if any.
**







|







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 routines used for analyzing expressions and
** for generating VDBE code that evaluates expressions in SQLite.
**
** $Id: expr.c,v 1.192 2005/02/05 12:48:48 danielk1977 Exp $
*/
#include "sqliteInt.h"
#include <ctype.h>

/*
** Return the 'affinity' of the expression pExpr if any.
**
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
  pNew->pSrc = sqlite3SrcListDup(p->pSrc);
  pNew->pWhere = sqlite3ExprDup(p->pWhere);
  pNew->pGroupBy = sqlite3ExprListDup(p->pGroupBy);
  pNew->pHaving = sqlite3ExprDup(p->pHaving);
  pNew->pOrderBy = sqlite3ExprListDup(p->pOrderBy);
  pNew->op = p->op;
  pNew->pPrior = sqlite3SelectDup(p->pPrior);
  pNew->nLimit = p->nLimit;
  pNew->nOffset = p->nOffset;
  pNew->iLimit = -1;
  pNew->iOffset = -1;
  pNew->ppOpenTemp = 0;
  pNew->pFetch = 0;
  pNew->isResolved = 0;
  pNew->isAgg = 0;
  return pNew;







|
|







507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
  pNew->pSrc = sqlite3SrcListDup(p->pSrc);
  pNew->pWhere = sqlite3ExprDup(p->pWhere);
  pNew->pGroupBy = sqlite3ExprListDup(p->pGroupBy);
  pNew->pHaving = sqlite3ExprDup(p->pHaving);
  pNew->pOrderBy = sqlite3ExprListDup(p->pOrderBy);
  pNew->op = p->op;
  pNew->pPrior = sqlite3SelectDup(p->pPrior);
  pNew->pLimit = sqlite3ExprDup(p->pLimit);
  pNew->pOffset = sqlite3ExprDup(p->pOffset);
  pNew->iLimit = -1;
  pNew->iOffset = -1;
  pNew->ppOpenTemp = 0;
  pNew->pFetch = 0;
  pNew->isResolved = 0;
  pNew->isAgg = 0;
  return pNew;
Changes to src/parse.y.
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
**
*************************************************************************
** This file contains SQLite's grammar for SQL.  Process this file
** using the lemon parser generator to generate C code that runs
** the parser.  Lemon will also generate a header file containing
** numeric codes for all of the tokens.
**
** @(#) $Id: parse.y,v 1.164 2005/02/04 04:07:17 danielk1977 Exp $
*/
%token_prefix TK_
%token_type {Token}
%default_type {Token}
%extra_argument {Parse *pParse}
%syntax_error {
  if( pParse->zErrMsg==0 ){







|







10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
**
*************************************************************************
** This file contains SQLite's grammar for SQL.  Process this file
** using the lemon parser generator to generate C code that runs
** the parser.  Lemon will also generate a header file containing
** numeric codes for all of the tokens.
**
** @(#) $Id: parse.y,v 1.165 2005/02/05 12:48:48 danielk1977 Exp $
*/
%token_prefix TK_
%token_type {Token}
%default_type {Token}
%extra_argument {Parse *pParse}
%syntax_error {
  if( pParse->zErrMsg==0 ){
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
#include "parse.h"

/*
** An instance of this structure holds information about the
** LIMIT clause of a SELECT statement.
*/
struct LimitVal {
  int limit;    /* The LIMIT value.  -1 if there is no limit */
  int offset;   /* The OFFSET.  0 if there is none */
};

/*
** An instance of this structure is used to store the LIKE,
** GLOB, NOT LIKE, and NOT GLOB operators.
*/
struct LikeOp {







|
|







35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
#include "parse.h"

/*
** An instance of this structure holds information about the
** LIMIT clause of a SELECT statement.
*/
struct LimitVal {
  Expr *pLimit;    /* The LIMIT expression.  NULL if there is no limit */
  Expr *pOffset;   /* The OFFSET expression.  NULL if there is none */
};

/*
** An instance of this structure is used to store the LIKE,
** GLOB, NOT LIKE, and NOT GLOB operators.
*/
struct LikeOp {
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
multiselect_op(A) ::= UNION(OP).      {A = @OP;}
multiselect_op(A) ::= UNION ALL.      {A = TK_ALL;}
multiselect_op(A) ::= INTERSECT(OP).  {A = @OP;}
multiselect_op(A) ::= EXCEPT(OP).     {A = @OP;}
%endif // SQLITE_OMIT_COMPOUND_SELECT
oneselect(A) ::= SELECT distinct(D) selcollist(W) from(X) where_opt(Y)
                 groupby_opt(P) having_opt(Q) orderby_opt(Z) limit_opt(L). {
  A = sqlite3SelectNew(W,X,Y,P,Q,Z,D,L.limit,L.offset);
}

// The "distinct" nonterminal is true (1) if the DISTINCT keyword is
// present and false (0) if it is not.
//
%type distinct {int}
distinct(A) ::= DISTINCT.   {A = 1;}







|







339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
multiselect_op(A) ::= UNION(OP).      {A = @OP;}
multiselect_op(A) ::= UNION ALL.      {A = TK_ALL;}
multiselect_op(A) ::= INTERSECT(OP).  {A = @OP;}
multiselect_op(A) ::= EXCEPT(OP).     {A = @OP;}
%endif // SQLITE_OMIT_COMPOUND_SELECT
oneselect(A) ::= SELECT distinct(D) selcollist(W) from(X) where_opt(Y)
                 groupby_opt(P) having_opt(Q) orderby_opt(Z) limit_opt(L). {
  A = sqlite3SelectNew(W,X,Y,P,Q,Z,D,L.pLimit,L.pOffset);
}

// The "distinct" nonterminal is true (1) if the DISTINCT keyword is
// present and false (0) if it is not.
//
%type distinct {int}
distinct(A) ::= DISTINCT.   {A = 1;}
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
  // is contained inside parentheses.  This can be either a subquery or
  // a grouping of table and subqueries.
  //
  %type seltablist_paren {Select*}
  %destructor seltablist_paren {sqlite3SelectDelete($$);}
  seltablist_paren(A) ::= select(S).      {A = S;}
  seltablist_paren(A) ::= seltablist(F).  {
     A = sqlite3SelectNew(0,F,0,0,0,0,0,-1,0);
  }
%endif // SQLITE_OMIT_SUBQUERY

%type dbnm {Token}
dbnm(A) ::= .          {A.z=0; A.n=0;}
dbnm(A) ::= DOT nm(X). {A = X;}








|







438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
  // is contained inside parentheses.  This can be either a subquery or
  // a grouping of table and subqueries.
  //
  %type seltablist_paren {Select*}
  %destructor seltablist_paren {sqlite3SelectDelete($$);}
  seltablist_paren(A) ::= select(S).      {A = S;}
  seltablist_paren(A) ::= seltablist(F).  {
     A = sqlite3SelectNew(0,F,0,0,0,0,0,0,0);
  }
%endif // SQLITE_OMIT_SUBQUERY

%type dbnm {Token}
dbnm(A) ::= .          {A.z=0; A.n=0;}
dbnm(A) ::= DOT nm(X). {A = X;}

509
510
511
512
513
514
515




516
517
518
519
520
521
522
523
524
525
526
527
528

%type having_opt {Expr*}
%destructor having_opt {sqlite3ExprDelete($$);}
having_opt(A) ::= .                {A = 0;}
having_opt(A) ::= HAVING expr(X).  {A = X;}

%type limit_opt {struct LimitVal}




limit_opt(A) ::= .                     {A.limit = -1; A.offset = 0;}
limit_opt(A) ::= LIMIT signed(X).      {A.limit = X; A.offset = 0;}
limit_opt(A) ::= LIMIT signed(X) OFFSET signed(Y). 
                                       {A.limit = X; A.offset = Y;}
limit_opt(A) ::= LIMIT signed(X) COMMA signed(Y). 
                                       {A.limit = Y; A.offset = X;}

/////////////////////////// The DELETE statement /////////////////////////////
//
cmd ::= DELETE FROM fullname(X) where_opt(Y). {sqlite3DeleteFrom(pParse,X,Y);}

%type where_opt {Expr*}
%destructor where_opt {sqlite3ExprDelete($$);}







>
>
>
>
|
|
|
|
|
|







509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532

%type having_opt {Expr*}
%destructor having_opt {sqlite3ExprDelete($$);}
having_opt(A) ::= .                {A = 0;}
having_opt(A) ::= HAVING expr(X).  {A = X;}

%type limit_opt {struct LimitVal}
%destructor limit_opt {
  sqlite3ExprDelete($$.pLimit);
  sqlite3ExprDelete($$.pOffset);
}
limit_opt(A) ::= .                     {A.pLimit = 0; A.pOffset = 0;}
limit_opt(A) ::= LIMIT expr(X).        {A.pLimit = X; A.pOffset = 0;}
limit_opt(A) ::= LIMIT expr(X) OFFSET expr(Y). 
                                       {A.pLimit = X; A.pOffset = Y;}
limit_opt(A) ::= LIMIT expr(X) COMMA expr(Y). 
                                       {A.pOffset = X; A.pLimit = Y;}

/////////////////////////// The DELETE statement /////////////////////////////
//
cmd ::= DELETE FROM fullname(X) where_opt(Y). {sqlite3DeleteFrom(pParse,X,Y);}

%type where_opt {Expr*}
%destructor where_opt {sqlite3ExprDelete($$);}
722
723
724
725
726
727
728
729
730
731
732
733
734
735
736
    if( A ) A->pSelect = Y;
    if( N ) A = sqlite3Expr(TK_NOT, A, 0, 0);
    sqlite3ExprSpan(A,&X->span,&E);
  }
  expr(A) ::= expr(X) in_op(N) nm(Y) dbnm(Z). [IN] {
    SrcList *pSrc = sqlite3SrcListAppend(0,&Y,&Z);
    A = sqlite3Expr(TK_IN, X, 0, 0);
    if( A ) A->pSelect = sqlite3SelectNew(0,pSrc,0,0,0,0,0,-1,0);
    if( N ) A = sqlite3Expr(TK_NOT, A, 0, 0);
    sqlite3ExprSpan(A,&X->span,Z.z?&Z:&Y);
  }
  expr(A) ::= EXISTS(B) LP select(Y) RP(E). {
    Expr *p = A = sqlite3Expr(TK_EXISTS, 0, 0, 0);
    if( p ){
      p->pSelect = Y;







|







726
727
728
729
730
731
732
733
734
735
736
737
738
739
740
    if( A ) A->pSelect = Y;
    if( N ) A = sqlite3Expr(TK_NOT, A, 0, 0);
    sqlite3ExprSpan(A,&X->span,&E);
  }
  expr(A) ::= expr(X) in_op(N) nm(Y) dbnm(Z). [IN] {
    SrcList *pSrc = sqlite3SrcListAppend(0,&Y,&Z);
    A = sqlite3Expr(TK_IN, X, 0, 0);
    if( A ) A->pSelect = sqlite3SelectNew(0,pSrc,0,0,0,0,0,0,0);
    if( N ) A = sqlite3Expr(TK_NOT, A, 0, 0);
    sqlite3ExprSpan(A,&X->span,Z.z?&Z:&Y);
  }
  expr(A) ::= EXISTS(B) LP select(Y) RP(E). {
    Expr *p = A = sqlite3Expr(TK_EXISTS, 0, 0, 0);
    if( p ){
      p->pSelect = Y;
Changes to src/select.c.
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
61
62
63
64
**    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.238 2005/02/04 04:07:17 danielk1977 Exp $
*/
#include "sqliteInt.h"


/*
** Allocate a new Select structure and return a pointer to that
** structure.
*/
Select *sqlite3SelectNew(
  ExprList *pEList,     /* which columns to include in the result */
  SrcList *pSrc,        /* the FROM clause -- which tables to scan */
  Expr *pWhere,         /* the WHERE clause */
  ExprList *pGroupBy,   /* the GROUP BY clause */
  Expr *pHaving,        /* the HAVING clause */
  ExprList *pOrderBy,   /* the ORDER BY clause */
  int isDistinct,       /* true if the DISTINCT keyword is present */
  int nLimit,           /* LIMIT value.  -1 means not used */
  int nOffset           /* OFFSET value.  0 means no offset */
){
  Select *pNew;
  pNew = sqliteMalloc( sizeof(*pNew) );

  if( pNew==0 ){
    sqlite3ExprListDelete(pEList);
    sqlite3SrcListDelete(pSrc);
    sqlite3ExprDelete(pWhere);
    sqlite3ExprListDelete(pGroupBy);
    sqlite3ExprDelete(pHaving);
    sqlite3ExprListDelete(pOrderBy);


  }else{
    if( pEList==0 ){
      pEList = sqlite3ExprListAppend(0, sqlite3Expr(TK_ALL,0,0,0), 0);
    }
    pNew->pEList = pEList;
    pNew->pSrc = pSrc;
    pNew->pWhere = pWhere;
    pNew->pGroupBy = pGroupBy;
    pNew->pHaving = pHaving;
    pNew->pOrderBy = pOrderBy;
    pNew->isDistinct = isDistinct;
    pNew->op = TK_SELECT;
    pNew->nLimit = nLimit;
    pNew->nOffset = nOffset;
    pNew->iLimit = -1;
    pNew->iOffset = -1;
  }
  return pNew;
}

/*







|
















|
|



>







>
>












|
|







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
61
62
63
64
65
66
67
**    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.239 2005/02/05 12:48:48 danielk1977 Exp $
*/
#include "sqliteInt.h"


/*
** Allocate a new Select structure and return a pointer to that
** structure.
*/
Select *sqlite3SelectNew(
  ExprList *pEList,     /* which columns to include in the result */
  SrcList *pSrc,        /* the FROM clause -- which tables to scan */
  Expr *pWhere,         /* the WHERE clause */
  ExprList *pGroupBy,   /* the GROUP BY clause */
  Expr *pHaving,        /* the HAVING clause */
  ExprList *pOrderBy,   /* the ORDER BY clause */
  int isDistinct,       /* true if the DISTINCT keyword is present */
  Expr *pLimit,         /* LIMIT value.  NULL means not used */
  Expr *pOffset         /* OFFSET value.  NULL means no offset */
){
  Select *pNew;
  pNew = sqliteMalloc( sizeof(*pNew) );
  assert( !pOffset || pLimit );   /* Can't have OFFSET without LIMIT. */
  if( pNew==0 ){
    sqlite3ExprListDelete(pEList);
    sqlite3SrcListDelete(pSrc);
    sqlite3ExprDelete(pWhere);
    sqlite3ExprListDelete(pGroupBy);
    sqlite3ExprDelete(pHaving);
    sqlite3ExprListDelete(pOrderBy);
    sqlite3ExprDelete(pLimit);
    sqlite3ExprDelete(pOffset);
  }else{
    if( pEList==0 ){
      pEList = sqlite3ExprListAppend(0, sqlite3Expr(TK_ALL,0,0,0), 0);
    }
    pNew->pEList = pEList;
    pNew->pSrc = pSrc;
    pNew->pWhere = pWhere;
    pNew->pGroupBy = pGroupBy;
    pNew->pHaving = pHaving;
    pNew->pOrderBy = pOrderBy;
    pNew->isDistinct = isDistinct;
    pNew->op = TK_SELECT;
    pNew->pLimit = pLimit;
    pNew->pOffset = pOffset;
    pNew->iLimit = -1;
    pNew->iOffset = -1;
  }
  return pNew;
}

/*
304
305
306
307
308
309
310


311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
  sqlite3ExprListDelete(p->pEList);
  sqlite3SrcListDelete(p->pSrc);
  sqlite3ExprDelete(p->pWhere);
  sqlite3ExprListDelete(p->pGroupBy);
  sqlite3ExprDelete(p->pHaving);
  sqlite3ExprListDelete(p->pOrderBy);
  sqlite3SelectDelete(p->pPrior);


  sqliteFree(p);
}

/*
** Delete the aggregate information from the parse structure.
*/
#if 0
static void sqliteAggregateInfoReset(Parse *pParse){
  sqliteFree(pParse->aAgg);
  pParse->aAgg = 0;
  pParse->nAgg = 0;
  pParse->useAgg = 0;
}
#endif

/*
** Insert code into "v" that will push the record on the top of the
** stack into the sorter.
*/
static void pushOntoSorter(Parse *pParse, Vdbe *v, ExprList *pOrderBy){
  int i;
  for(i=0; i<pOrderBy->nExpr; i++){







>
>



<
<
<
<
<
<
<
<
<
<
<
<







307
308
309
310
311
312
313
314
315
316
317
318












319
320
321
322
323
324
325
  sqlite3ExprListDelete(p->pEList);
  sqlite3SrcListDelete(p->pSrc);
  sqlite3ExprDelete(p->pWhere);
  sqlite3ExprListDelete(p->pGroupBy);
  sqlite3ExprDelete(p->pHaving);
  sqlite3ExprListDelete(p->pOrderBy);
  sqlite3SelectDelete(p->pPrior);
  sqlite3ExprDelete(p->pLimit);
  sqlite3ExprDelete(p->pOffset);
  sqliteFree(p);
}













/*
** Insert code into "v" that will push the record on the top of the
** stack into the sorter.
*/
static void pushOntoSorter(Parse *pParse, Vdbe *v, ExprList *pOrderBy){
  int i;
  for(i=0; i<pOrderBy->nExpr; i++){
343
344
345
346
347
348
349
350
351
352

353
354
355
356
357
358
359
  Vdbe *v,          /* Generate code into this VM */
  Select *p,        /* The SELECT statement being coded */
  int iContinue,    /* Jump here to skip the current record */
  int iBreak,       /* Jump here to end the loop */
  int nPop          /* Number of times to pop stack when jumping */
){
  if( p->iOffset>=0 ){
    int addr = sqlite3VdbeCurrentAddr(v) + 2;
    if( nPop>0 ) addr++;
    sqlite3VdbeAddOp(v, OP_MemIncr, p->iOffset, addr);

    if( nPop>0 ){
      sqlite3VdbeAddOp(v, OP_Pop, nPop, 0);
    }
    sqlite3VdbeAddOp(v, OP_Goto, 0, iContinue);
    VdbeComment((v, "# skip OFFSET records"));
  }
  if( p->iLimit>=0 ){







|

|
>







336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
  Vdbe *v,          /* Generate code into this VM */
  Select *p,        /* The SELECT statement being coded */
  int iContinue,    /* Jump here to skip the current record */
  int iBreak,       /* Jump here to end the loop */
  int nPop          /* Number of times to pop stack when jumping */
){
  if( p->iOffset>=0 ){
    int addr = sqlite3VdbeCurrentAddr(v) + 3;
    if( nPop>0 ) addr++;
    sqlite3VdbeAddOp(v, OP_MemIncr, p->iOffset, 0);
    sqlite3VdbeAddOp(v, OP_IfMemPos, p->iOffset, addr);
    if( nPop>0 ){
      sqlite3VdbeAddOp(v, OP_Pop, nPop, 0);
    }
    sqlite3VdbeAddOp(v, OP_Goto, 0, iContinue);
    VdbeComment((v, "# skip OFFSET records"));
  }
  if( p->iLimit>=0 ){
1270
1271
1272
1273
1274
1275
1276
1277
1278
1279
1280
1281
1282
1283
1284
1285
1286
1287
1288
1289
1290
1291
1292
1293
1294
1295
1296
1297
1298
1299
1300
1301
1302
1303
1304
1305
1306

1307

1308
1309
1310
1311
1312
1313
1314
1315


1316
1317
1318
1319
1320
1321
1322
1323
    v = pParse->pVdbe = sqlite3VdbeCreate(pParse->db);
  }
  return v;
}

/*
** Compute the iLimit and iOffset fields of the SELECT based on the
** nLimit and nOffset fields.  nLimit and nOffset hold the integers
** that appear in the original SQL statement after the LIMIT and OFFSET
** keywords.  Or that hold -1 and 0 if those keywords are omitted.
** iLimit and iOffset are the integer memory register numbers for
** counters used to compute the limit and offset.  If there is no
** limit and/or offset, then iLimit and iOffset are negative.
**
** This routine changes the values if iLimit and iOffset only if
** a limit or offset is defined by nLimit and nOffset.  iLimit and
** iOffset should have been preset to appropriate default values
** (usually but not always -1) prior to calling this routine.
** Only if nLimit>=0 or nOffset>0 do the limit registers get
** redefined.  The UNION ALL operator uses this property to force
** the reuse of the same limit and offset registers across multiple
** SELECT statements.
*/
static void computeLimitRegisters(Parse *pParse, Select *p){
  /* 
  ** 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.
  ** The current implementation interprets "LIMIT 0" to mean
  ** no rows.
  */
  if( p->nLimit>=0 ){
    int iMem = pParse->nMem++;
    Vdbe *v = sqlite3GetVdbe(pParse);
    if( v==0 ) return;

    sqlite3VdbeAddOp(v, OP_Integer, -p->nLimit, 0);

    sqlite3VdbeAddOp(v, OP_MemStore, iMem, 1);
    VdbeComment((v, "# LIMIT counter"));
    p->iLimit = iMem;
  }
  if( p->nOffset>0 ){
    int iMem = pParse->nMem++;
    Vdbe *v = sqlite3GetVdbe(pParse);
    if( v==0 ) return;


    sqlite3VdbeAddOp(v, OP_Integer, -p->nOffset, 0);
    sqlite3VdbeAddOp(v, OP_MemStore, iMem, 1);
    VdbeComment((v, "# OFFSET counter"));
    p->iOffset = iMem;
  }
}

/*







|

|
|
|
|












<
<
<





|



>
|
>




|



>
>
|







1264
1265
1266
1267
1268
1269
1270
1271
1272
1273
1274
1275
1276
1277
1278
1279
1280
1281
1282
1283
1284
1285
1286
1287
1288



1289
1290
1291
1292
1293
1294
1295
1296
1297
1298
1299
1300
1301
1302
1303
1304
1305
1306
1307
1308
1309
1310
1311
1312
1313
1314
1315
1316
1317
1318
    v = pParse->pVdbe = sqlite3VdbeCreate(pParse->db);
  }
  return v;
}

/*
** Compute the iLimit and iOffset fields of the SELECT based on the
** pLimit and pOffset expressions.  nLimit and nOffset hold the expressions
** that appear in the original SQL statement after the LIMIT and OFFSET
** keywords.  Or NULL if those keywords are omitted. iLimit and iOffset 
** are the integer memory register numbers for counters used to compute 
** the limit and offset.  If there is no limit and/or offset, then 
** iLimit and iOffset are negative.
**
** This routine changes the values if iLimit and iOffset only if
** a limit or offset is defined by nLimit and nOffset.  iLimit and
** iOffset should have been preset to appropriate default values
** (usually but not always -1) prior to calling this routine.
** Only if nLimit>=0 or nOffset>0 do the limit registers get
** redefined.  The UNION ALL operator uses this property to force
** the reuse of the same limit and offset registers across multiple
** SELECT statements.
*/
static void computeLimitRegisters(Parse *pParse, Select *p){
  /* 



  ** "LIMIT -1" always shows all rows.  There is some
  ** contraversy about what the correct behavior should be.
  ** The current implementation interprets "LIMIT 0" to mean
  ** no rows.
  */
  if( p->pLimit ){
    int iMem = pParse->nMem++;
    Vdbe *v = sqlite3GetVdbe(pParse);
    if( v==0 ) return;
    sqlite3ExprCode(pParse, p->pLimit);
    sqlite3VdbeAddOp(v, OP_MustBeInt, 0, 0);
    sqlite3VdbeAddOp(v, OP_Negative, 0, 0);
    sqlite3VdbeAddOp(v, OP_MemStore, iMem, 1);
    VdbeComment((v, "# LIMIT counter"));
    p->iLimit = iMem;
  }
  if( p->pOffset ){
    int iMem = pParse->nMem++;
    Vdbe *v = sqlite3GetVdbe(pParse);
    if( v==0 ) return;
    sqlite3ExprCode(pParse, p->pOffset);
    sqlite3VdbeAddOp(v, OP_MustBeInt, 0, 0);
    sqlite3VdbeAddOp(v, OP_Negative, 0, 0);
    sqlite3VdbeAddOp(v, OP_MemStore, iMem, 1);
    VdbeComment((v, "# OFFSET counter"));
    p->iOffset = iMem;
  }
}

/*
1459
1460
1461
1462
1463
1464
1465
1466
1467
1468
1469
1470
1471
1472
1473
  pPrior = p->pPrior;
  if( pPrior->pOrderBy ){
    sqlite3ErrorMsg(pParse,"ORDER BY clause should come after %s not before",
      selectOpName(p->op));
    rc = 1;
    goto multi_select_end;
  }
  if( pPrior->nLimit>=0 || pPrior->nOffset>0 ){
    sqlite3ErrorMsg(pParse,"LIMIT clause should come after %s not before",
      selectOpName(p->op));
    rc = 1;
    goto multi_select_end;
  }

  /* Make sure we have a valid query engine.  If not, create a new one.







|







1454
1455
1456
1457
1458
1459
1460
1461
1462
1463
1464
1465
1466
1467
1468
  pPrior = p->pPrior;
  if( pPrior->pOrderBy ){
    sqlite3ErrorMsg(pParse,"ORDER BY clause should come after %s not before",
      selectOpName(p->op));
    rc = 1;
    goto multi_select_end;
  }
  if( pPrior->pLimit ){
    sqlite3ErrorMsg(pParse,"LIMIT clause should come after %s not before",
      selectOpName(p->op));
    rc = 1;
    goto multi_select_end;
  }

  /* Make sure we have a valid query engine.  If not, create a new one.
1500
1501
1502
1503
1504
1505
1506

1507
1508
1509
1510
1511
1512
1513
1514
1515
1516
1517
1518
1519
1520
1521
1522
1523
1524
1525
1526
1527
1528
1529
1530
1531
1532
1533
1534
1535
1536
1537
1538
1539
1540
1541
1542
1543
1544
  }

  /* Generate code for the left and right SELECT statements.
  */
  switch( p->op ){
    case TK_ALL: {
      if( p->pOrderBy==0 ){

        pPrior->nLimit = p->nLimit;
        pPrior->nOffset = p->nOffset;
        rc = sqlite3Select(pParse, pPrior, eDest, iParm, 0, 0, 0, aff);
        if( rc ){
          goto multi_select_end;
        }
        p->pPrior = 0;
        p->iLimit = pPrior->iLimit;
        p->iOffset = pPrior->iOffset;
        p->nLimit = -1;
        p->nOffset = 0;
        rc = sqlite3Select(pParse, p, eDest, iParm, 0, 0, 0, aff);
        p->pPrior = pPrior;
        if( rc ){
          goto multi_select_end;
        }
        break;
      }
      /* For UNION ALL ... ORDER BY fall through to the next case */
    }
    case TK_EXCEPT:
    case TK_UNION: {
      int unionTab;    /* Cursor number of the temporary table holding result */
      int op = 0;      /* One of the SRT_ operations to apply to self */
      int priorOp;     /* The SRT_ operation to apply to prior selects */
      int nLimit, nOffset; /* Saved values of p->nLimit and p->nOffset */
      ExprList *pOrderBy;  /* The ORDER BY clause for the right SELECT */
      int addr;

      priorOp = p->op==TK_ALL ? SRT_Table : SRT_Union;
      if( eDest==priorOp && p->pOrderBy==0 && p->nLimit<0 && p->nOffset==0 ){
        /* We can reuse a temporary table generated by a SELECT to our
        ** right.
        */
        unionTab = iParm;
      }else{
        /* We will need to create our own temporary table to hold the
        ** intermediate results.







>
|
|







|
|














|
|



|







1495
1496
1497
1498
1499
1500
1501
1502
1503
1504
1505
1506
1507
1508
1509
1510
1511
1512
1513
1514
1515
1516
1517
1518
1519
1520
1521
1522
1523
1524
1525
1526
1527
1528
1529
1530
1531
1532
1533
1534
1535
1536
1537
1538
1539
1540
  }

  /* Generate code for the left and right SELECT statements.
  */
  switch( p->op ){
    case TK_ALL: {
      if( p->pOrderBy==0 ){
        assert( !pPrior->pLimit );
        pPrior->pLimit = p->pLimit;
        pPrior->pOffset = p->pOffset;
        rc = sqlite3Select(pParse, pPrior, eDest, iParm, 0, 0, 0, aff);
        if( rc ){
          goto multi_select_end;
        }
        p->pPrior = 0;
        p->iLimit = pPrior->iLimit;
        p->iOffset = pPrior->iOffset;
        p->pLimit = 0;
        p->pOffset = 0;
        rc = sqlite3Select(pParse, p, eDest, iParm, 0, 0, 0, aff);
        p->pPrior = pPrior;
        if( rc ){
          goto multi_select_end;
        }
        break;
      }
      /* For UNION ALL ... ORDER BY fall through to the next case */
    }
    case TK_EXCEPT:
    case TK_UNION: {
      int unionTab;    /* Cursor number of the temporary table holding result */
      int op = 0;      /* One of the SRT_ operations to apply to self */
      int priorOp;     /* The SRT_ operation to apply to prior selects */
      Expr *pLimit, *pOffset; /* Saved values of p->nLimit and p->nOffset */
      ExprList *pOrderBy;     /* The ORDER BY clause for the right SELECT */
      int addr;

      priorOp = p->op==TK_ALL ? SRT_Table : SRT_Union;
      if( eDest==priorOp && p->pOrderBy==0 && !p->pLimit && !p->pOffset ){
        /* We can reuse a temporary table generated by a SELECT to our
        ** right.
        */
        unionTab = iParm;
      }else{
        /* We will need to create our own temporary table to hold the
        ** intermediate results.
1576
1577
1578
1579
1580
1581
1582
1583
1584
1585
1586
1587
1588
1589

1590
1591
1592
1593
1594
1595
1596
1597
1598
         case TK_EXCEPT:  op = SRT_Except;   break;
         case TK_UNION:   op = SRT_Union;    break;
         case TK_ALL:     op = SRT_Table;    break;
      }
      p->pPrior = 0;
      pOrderBy = p->pOrderBy;
      p->pOrderBy = 0;
      nLimit = p->nLimit;
      p->nLimit = -1;
      nOffset = p->nOffset;
      p->nOffset = 0;
      rc = sqlite3Select(pParse, p, op, unionTab, 0, 0, 0, aff);
      p->pPrior = pPrior;
      p->pOrderBy = pOrderBy;

      p->nLimit = nLimit;
      p->nOffset = nOffset;
      p->iLimit = -1;
      p->iOffset = -1;
      if( rc ){
        goto multi_select_end;
      }









|
|
|
|



>
|
|







1572
1573
1574
1575
1576
1577
1578
1579
1580
1581
1582
1583
1584
1585
1586
1587
1588
1589
1590
1591
1592
1593
1594
1595
         case TK_EXCEPT:  op = SRT_Except;   break;
         case TK_UNION:   op = SRT_Union;    break;
         case TK_ALL:     op = SRT_Table;    break;
      }
      p->pPrior = 0;
      pOrderBy = p->pOrderBy;
      p->pOrderBy = 0;
      pLimit = p->pLimit;
      p->pLimit = 0;
      pOffset = p->pOffset;
      p->pOffset = 0;
      rc = sqlite3Select(pParse, p, op, unionTab, 0, 0, 0, aff);
      p->pPrior = pPrior;
      p->pOrderBy = pOrderBy;
      sqlite3ExprDelete(p->pLimit);
      p->pLimit = pLimit;
      p->pOffset = pOffset;
      p->iLimit = -1;
      p->iOffset = -1;
      if( rc ){
        goto multi_select_end;
      }


1623
1624
1625
1626
1627
1628
1629
1630
1631
1632
1633
1634
1635
1636
1637
        sqlite3VdbeAddOp(v, OP_Close, unionTab, 0);
      }
      break;
    }
    case TK_INTERSECT: {
      int tab1, tab2;
      int iCont, iBreak, iStart;
      int nLimit, nOffset;
      int addr;

      /* INTERSECT is different from the others since it requires
      ** two temporary tables.  Hence it has its own case.  Begin
      ** by allocating the tables we will need.
      */
      tab1 = pParse->nTab++;







|







1620
1621
1622
1623
1624
1625
1626
1627
1628
1629
1630
1631
1632
1633
1634
        sqlite3VdbeAddOp(v, OP_Close, unionTab, 0);
      }
      break;
    }
    case TK_INTERSECT: {
      int tab1, tab2;
      int iCont, iBreak, iStart;
      Expr *pLimit, *pOffset;
      int addr;

      /* INTERSECT is different from the others since it requires
      ** two temporary tables.  Hence it has its own case.  Begin
      ** by allocating the tables we will need.
      */
      tab1 = pParse->nTab++;
1665
1666
1667
1668
1669
1670
1671
1672
1673
1674
1675
1676
1677

1678
1679
1680
1681
1682
1683
1684
1685
1686
      if( rc!=SQLITE_OK ){
        goto multi_select_end;
      }
      sqlite3VdbeAddOp(v, OP_KeyAsData, tab2, 1);
      assert( nAddr<sizeof(aAddr)/sizeof(aAddr[0]) );
      aAddr[nAddr++] = sqlite3VdbeAddOp(v, OP_SetNumColumns, tab2, 0);
      p->pPrior = 0;
      nLimit = p->nLimit;
      p->nLimit = -1;
      nOffset = p->nOffset;
      p->nOffset = 0;
      rc = sqlite3Select(pParse, p, SRT_Union, tab2, 0, 0, 0, aff);
      p->pPrior = pPrior;

      p->nLimit = nLimit;
      p->nOffset = nOffset;
      if( rc ){
        goto multi_select_end;
      }

      /* Generate code to take the intersection of the two temporary
      ** tables.
      */







|
|
|
|


>
|
|







1662
1663
1664
1665
1666
1667
1668
1669
1670
1671
1672
1673
1674
1675
1676
1677
1678
1679
1680
1681
1682
1683
1684
      if( rc!=SQLITE_OK ){
        goto multi_select_end;
      }
      sqlite3VdbeAddOp(v, OP_KeyAsData, tab2, 1);
      assert( nAddr<sizeof(aAddr)/sizeof(aAddr[0]) );
      aAddr[nAddr++] = sqlite3VdbeAddOp(v, OP_SetNumColumns, tab2, 0);
      p->pPrior = 0;
      pLimit = p->pLimit;
      p->pLimit = 0;
      pOffset = p->pOffset;
      p->pOffset = 0;
      rc = sqlite3Select(pParse, p, SRT_Union, tab2, 0, 0, 0, aff);
      p->pPrior = pPrior;
      sqlite3ExprDelete(p->pLimit);
      p->pLimit = pLimit;
      p->pOffset = pOffset;
      if( rc ){
        goto multi_select_end;
      }

      /* Generate code to take the intersection of the two temporary
      ** tables.
      */
1955
1956
1957
1958
1959
1960
1961


1962
1963
1964
1965
1966
1967
1968
1969
1970
1971
1972
1973
  pSubitem = &pSrc->a[iFrom];
  pSub = pSubitem->pSelect;
  assert( pSub!=0 );
  if( isAgg && subqueryIsAgg ) return 0;
  if( subqueryIsAgg && pSrc->nSrc>1 ) return 0;
  pSubSrc = pSub->pSrc;
  assert( pSubSrc );


  if( pSubSrc->nSrc==0 ) return 0;
  if( (pSub->isDistinct || pSub->nLimit>=0) &&  (pSrc->nSrc>1 || isAgg) ){
     return 0;
  }
  if( (p->isDistinct || p->nLimit>=0) && subqueryIsAgg ) return 0;
  if( p->pOrderBy && pSub->pOrderBy ) return 0;

  /* Restriction 3:  If the subquery is a join, make sure the subquery is 
  ** not used as the right operand of an outer join.  Examples of why this
  ** is not allowed:
  **
  **         t1 LEFT OUTER JOIN (t2 JOIN t3)







>
>

|


|







1953
1954
1955
1956
1957
1958
1959
1960
1961
1962
1963
1964
1965
1966
1967
1968
1969
1970
1971
1972
1973
  pSubitem = &pSrc->a[iFrom];
  pSub = pSubitem->pSelect;
  assert( pSub!=0 );
  if( isAgg && subqueryIsAgg ) return 0;
  if( subqueryIsAgg && pSrc->nSrc>1 ) return 0;
  pSubSrc = pSub->pSrc;
  assert( pSubSrc );
  if( (pSub->pLimit && p->pLimit) || pSub->pOffset || 
      (pSub->pLimit && isAgg) ) return 0;
  if( pSubSrc->nSrc==0 ) return 0;
  if( pSub->isDistinct && (pSrc->nSrc>1 || isAgg) ){
     return 0;
  }
  if( p->isDistinct && subqueryIsAgg ) return 0;
  if( p->pOrderBy && pSub->pOrderBy ) return 0;

  /* Restriction 3:  If the subquery is a join, make sure the subquery is 
  ** not used as the right operand of an outer join.  Examples of why this
  ** is not allowed:
  **
  **         t1 LEFT OUTER JOIN (t2 JOIN t3)
2091
2092
2093
2094
2095
2096
2097
2098
2099
2100
2101
2102
2103
2104
2105
2106
2107
2108
2109
2110
2111
2112
2113
2114
2115
  }

  /* The flattened query is distinct if either the inner or the
  ** outer query is distinct. 
  */
  p->isDistinct = p->isDistinct || pSub->isDistinct;

  /* Transfer the limit expression from the subquery to the outer
  ** query.
  */
  if( pSub->nLimit>=0 ){
    if( p->nLimit<0 ){
      p->nLimit = pSub->nLimit;
    }else if( p->nLimit+p->nOffset > pSub->nLimit+pSub->nOffset ){
      p->nLimit = pSub->nLimit + pSub->nOffset - p->nOffset;
    }
  }
  p->nOffset += pSub->nOffset;

  /* Finially, delete what is left of the subquery and return
  ** success.
  */
  sqlite3SelectDelete(pSub);
  return 1;
}







<
<
<
|
<
|
<
|
|
<
<







2091
2092
2093
2094
2095
2096
2097



2098

2099

2100
2101


2102
2103
2104
2105
2106
2107
2108
  }

  /* The flattened query is distinct if either the inner or the
  ** outer query is distinct. 
  */
  p->isDistinct = p->isDistinct || pSub->isDistinct;




  if( pSub->pLimit ){

    p->pLimit = pSub->pLimit;

    pSub->pLimit = 0;
  }



  /* Finially, delete what is left of the subquery and return
  ** success.
  */
  sqlite3SelectDelete(pSub);
  return 1;
}
2326
2327
2328
2329
2330
2331
2332
2333
2334

2335
2336
2337
2338
2339
2340
2341














2342
2343
2344
2345
2346
2347
2348
  /* Prepare the select statement. This call will allocate all cursors
  ** required to handle the tables and subqueries in the FROM clause.
  */
  if( prepSelectStmt(pParse, p) ){
    return SQLITE_ERROR;
  }

  /* Set up the local name-context to pass to ExprResolveNames().  */
  sNC.pNext = pOuterNC;

  sNC.pParse = pParse;
  sNC.pSrcList = p->pSrc;
  sNC.allowAgg = 1;
  sNC.hasAgg = 0;
  sNC.nErr = 0;
  sNC.nRef = 0;
  sNC.pEList = 0;















  /* NameContext.nDepth stores the depth of recursion for this query. For
  ** an outer query (e.g. SELECT * FROM sqlite_master) this is 1. For
  ** a subquery it is 2. For a subquery of a subquery, 3. And so on. 
  ** Parse.nMaxDepth is the maximum depth for any subquery resolved so
  ** far. This is used to determine the number of aggregate contexts
  ** required at runtime.







|
|
>

<
<




>
>
>
>
>
>
>
>
>
>
>
>
>
>







2319
2320
2321
2322
2323
2324
2325
2326
2327
2328
2329


2330
2331
2332
2333
2334
2335
2336
2337
2338
2339
2340
2341
2342
2343
2344
2345
2346
2347
2348
2349
2350
2351
2352
2353
2354
  /* Prepare the select statement. This call will allocate all cursors
  ** required to handle the tables and subqueries in the FROM clause.
  */
  if( prepSelectStmt(pParse, p) ){
    return SQLITE_ERROR;
  }

  /* Resolve the expressions in the LIMIT and OFFSET clauses. These
  ** are not allowed to refer to any names, so pass an empty NameContext.
  */
  sNC.pParse = pParse;


  sNC.hasAgg = 0;
  sNC.nErr = 0;
  sNC.nRef = 0;
  sNC.pEList = 0;
  sNC.allowAgg = 0;
  sNC.pSrcList = 0;
  sNC.pNext = 0;
  if( sqlite3ExprResolveNames(&sNC, p->pLimit) ||
      sqlite3ExprResolveNames(&sNC, p->pOffset) ){
    return SQLITE_ERROR;
  }

  /* Set up the local name-context to pass to ExprResolveNames() to
  ** resolve the expression-list.
  */
  sNC.allowAgg = 1;
  sNC.pSrcList = p->pSrc;
  sNC.pNext = pOuterNC;

  /* NameContext.nDepth stores the depth of recursion for this query. For
  ** an outer query (e.g. SELECT * FROM sqlite_master) this is 1. For
  ** a subquery it is 2. For a subquery of a subquery, 3. And so on. 
  ** Parse.nMaxDepth is the maximum depth for any subquery resolved so
  ** far. This is used to determine the number of aggregate contexts
  ** required at runtime.
Changes to src/sqliteInt.h.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
/*
** 2001 September 15
**
** 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.
**
*************************************************************************
** Internal interface definitions for SQLite.
**
** @(#) $Id: sqliteInt.h,v 1.366 2005/02/04 04:07:17 danielk1977 Exp $
*/
#ifndef _SQLITEINT_H_
#define _SQLITEINT_H_

/*
** These #defines should enable >2GB file support on Posix if the
** underlying operating system supports it.  If the OS lacks













|







1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
/*
** 2001 September 15
**
** 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.
**
*************************************************************************
** Internal interface definitions for SQLite.
**
** @(#) $Id: sqliteInt.h,v 1.367 2005/02/05 12:48:48 danielk1977 Exp $
*/
#ifndef _SQLITEINT_H_
#define _SQLITEINT_H_

/*
** These #defines should enable >2GB file support on Posix if the
** underlying operating system supports it.  If the OS lacks
1009
1010
1011
1012
1013
1014
1015

1016
1017
1018
1019
1020
1021
1022
1023
  u8 isDistinct;         /* True if the DISTINCT keyword is present */
  SrcList *pSrc;         /* The FROM clause */
  Expr *pWhere;          /* The WHERE clause */
  ExprList *pGroupBy;    /* The GROUP BY clause */
  Expr *pHaving;         /* The HAVING clause */
  ExprList *pOrderBy;    /* The ORDER BY clause */
  Select *pPrior;        /* Prior select in a compound select statement */

  int nLimit, nOffset;   /* LIMIT and OFFSET values.  -1 means not used */
  int iLimit, iOffset;   /* Memory registers holding LIMIT & OFFSET counters */
  IdList **ppOpenTemp;   /* OP_OpenTemp addresses used by multi-selects */
  Fetch *pFetch;         /* If this stmt is part of a FETCH command */
  u8 isResolved;         /* True once sqlite3SelectResolve() has run. */
  u8 isAgg;              /* True if this is an aggregate query */
};








>
|







1009
1010
1011
1012
1013
1014
1015
1016
1017
1018
1019
1020
1021
1022
1023
1024
  u8 isDistinct;         /* True if the DISTINCT keyword is present */
  SrcList *pSrc;         /* The FROM clause */
  Expr *pWhere;          /* The WHERE clause */
  ExprList *pGroupBy;    /* The GROUP BY clause */
  Expr *pHaving;         /* The HAVING clause */
  ExprList *pOrderBy;    /* The ORDER BY clause */
  Select *pPrior;        /* Prior select in a compound select statement */
  Expr *pLimit;          /* LIMIT expression. NULL means not used. */
  Expr *pOffset;         /* OFFSET expression. NULL means not used. */
  int iLimit, iOffset;   /* Memory registers holding LIMIT & OFFSET counters */
  IdList **ppOpenTemp;   /* OP_OpenTemp addresses used by multi-selects */
  Fetch *pFetch;         /* If this stmt is part of a FETCH command */
  u8 isResolved;         /* True once sqlite3SelectResolve() has run. */
  u8 isAgg;              /* True if this is an aggregate query */
};

1374
1375
1376
1377
1378
1379
1380
1381
1382
1383
1384
1385
1386
1387
1388
void sqlite3CreateIndex(Parse*,Token*,Token*,SrcList*,ExprList*,int,Token*,
                        Token*);
void sqlite3DropIndex(Parse*, SrcList*);
void sqlite3AddKeyType(Vdbe*, ExprList*);
void sqlite3AddIdxKeyType(Vdbe*, Index*);
int sqlite3Select(Parse*, Select*, int, int, Select*, int, int*, char *aff);
Select *sqlite3SelectNew(ExprList*,SrcList*,Expr*,ExprList*,Expr*,ExprList*,
                        int,int,int);
void sqlite3SelectDelete(Select*);
void sqlite3SelectUnbind(Select*);
Table *sqlite3SrcListLookup(Parse*, SrcList*);
int sqlite3IsReadOnly(Parse*, Table*, int);
void sqlite3OpenTableForReading(Vdbe*, int iCur, Table*);
void sqlite3OpenTable(Vdbe*, int iCur, Table*, int);
void sqlite3DeleteFrom(Parse*, SrcList*, Expr*);







|







1375
1376
1377
1378
1379
1380
1381
1382
1383
1384
1385
1386
1387
1388
1389
void sqlite3CreateIndex(Parse*,Token*,Token*,SrcList*,ExprList*,int,Token*,
                        Token*);
void sqlite3DropIndex(Parse*, SrcList*);
void sqlite3AddKeyType(Vdbe*, ExprList*);
void sqlite3AddIdxKeyType(Vdbe*, Index*);
int sqlite3Select(Parse*, Select*, int, int, Select*, int, int*, char *aff);
Select *sqlite3SelectNew(ExprList*,SrcList*,Expr*,ExprList*,Expr*,ExprList*,
                        int,Expr*,Expr*);
void sqlite3SelectDelete(Select*);
void sqlite3SelectUnbind(Select*);
Table *sqlite3SrcListLookup(Parse*, SrcList*);
int sqlite3IsReadOnly(Parse*, Table*, int);
void sqlite3OpenTableForReading(Vdbe*, int iCur, Table*);
void sqlite3OpenTable(Vdbe*, int iCur, Table*, int);
void sqlite3DeleteFrom(Parse*, SrcList*, Expr*);
Changes to src/vacuum.c.
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
**
*************************************************************************
** This file contains code used to implement the VACUUM command.
**
** Most of the code in this file may be omitted by defining the
** SQLITE_OMIT_VACUUM macro.
**
** $Id: vacuum.c,v 1.37 2005/02/03 01:08:20 drh Exp $
*/
#include "sqliteInt.h"
#include "os.h"

#ifndef SQLITE_OMIT_VACUUM
/*
** Generate a random name of 20 character in length.







|







10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
**
*************************************************************************
** This file contains code used to implement the VACUUM command.
**
** Most of the code in this file may be omitted by defining the
** SQLITE_OMIT_VACUUM macro.
**
** $Id: vacuum.c,v 1.38 2005/02/05 12:48:48 danielk1977 Exp $
*/
#include "sqliteInt.h"
#include "os.h"

#ifndef SQLITE_OMIT_VACUUM
/*
** Generate a random name of 20 character in length.
208
209
210
211
212
213
214
215



216
217
218
219
220
221
222
  if( rc!=SQLITE_OK ) goto end_of_vacuum;

  /* Copy over the sequence table
  */
  rc = execExecSql(db, 
      "SELECT 'DELETE FROM vacuum_db.' || quote(name) || ';' "
      "FROM sqlite_master WHERE name='sqlite_sequence' "
      "UNION ALL "



      "SELECT 'INSERT INTO vacuum_db.' || quote(name) "
      "|| ' SELECT * FROM ' || quote(name) || ';' "
      "FROM sqlite_master WHERE name=='sqlite_sequence';"
  );
  if( rc!=SQLITE_OK ) goto end_of_vacuum;









<
>
>
>







208
209
210
211
212
213
214

215
216
217
218
219
220
221
222
223
224
  if( rc!=SQLITE_OK ) goto end_of_vacuum;

  /* Copy over the sequence table
  */
  rc = execExecSql(db, 
      "SELECT 'DELETE FROM vacuum_db.' || quote(name) || ';' "
      "FROM sqlite_master WHERE name='sqlite_sequence' "

  );
  if( rc!=SQLITE_OK ) goto end_of_vacuum;
  rc = execExecSql(db, 
      "SELECT 'INSERT INTO vacuum_db.' || quote(name) "
      "|| ' SELECT * FROM ' || quote(name) || ';' "
      "FROM sqlite_master WHERE name=='sqlite_sequence';"
  );
  if( rc!=SQLITE_OK ) goto end_of_vacuum;


Changes to src/vdbe.c.
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
**
** Various scripts scan this source file in order to generate HTML
** documentation, headers files, or other derived files.  The formatting
** of the code in this file is, therefore, important.  See other comments
** in this file for details.  If in doubt, do not deviate from existing
** commenting and indentation practices when changing or adding code.
**
** $Id: vdbe.c,v 1.452 2005/02/05 06:49:54 danielk1977 Exp $
*/
#include "sqliteInt.h"
#include "os.h"
#include <ctype.h>
#include "vdbeInt.h"

/*







|







39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
**
** Various scripts scan this source file in order to generate HTML
** documentation, headers files, or other derived files.  The formatting
** of the code in this file is, therefore, important.  See other comments
** in this file for details.  If in doubt, do not deviate from existing
** commenting and indentation practices when changing or adding code.
**
** $Id: vdbe.c,v 1.453 2005/02/05 12:48:48 danielk1977 Exp $
*/
#include "sqliteInt.h"
#include "os.h"
#include <ctype.h>
#include "vdbeInt.h"

/*
4199
4200
4201
4202
4203
4204
4205
4206
4207
4208
4209
4210
4211
4212
4213
4214
4215
4216
4217
4218
4219

















4220
4221
4222
4223
4224
4225
4226
  break;
}
#endif /* SQLITE_OMIT_AUTOINCREMENT */

/* Opcode: MemIncr P1 P2 *
**
** Increment the integer valued memory cell P1 by 1.  If P2 is not zero
** and the result after the increment is greater than zero, then jump
** to P2.
**
** This instruction throws an error if the memory cell is not initially
** an integer.
*/
case OP_MemIncr: {
  int i = pOp->p1;
  Mem *pMem;
  assert( i>=0 && i<p->nMem );
  pMem = &p->aMem[i];
  assert( pMem->flags==MEM_Int );
  pMem->i++;
  if( pOp->p2>0 && pMem->i>0 ){

















     pc = pOp->p2 - 1;
  }
  break;
}

/* Opcode: AggReset P1 P2 P3
**







|












|
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>







4199
4200
4201
4202
4203
4204
4205
4206
4207
4208
4209
4210
4211
4212
4213
4214
4215
4216
4217
4218
4219
4220
4221
4222
4223
4224
4225
4226
4227
4228
4229
4230
4231
4232
4233
4234
4235
4236
4237
4238
4239
4240
4241
4242
4243
  break;
}
#endif /* SQLITE_OMIT_AUTOINCREMENT */

/* Opcode: MemIncr P1 P2 *
**
** Increment the integer valued memory cell P1 by 1.  If P2 is not zero
** and the result after the increment is exactly 1, then jump
** to P2.
**
** This instruction throws an error if the memory cell is not initially
** an integer.
*/
case OP_MemIncr: {
  int i = pOp->p1;
  Mem *pMem;
  assert( i>=0 && i<p->nMem );
  pMem = &p->aMem[i];
  assert( pMem->flags==MEM_Int );
  pMem->i++;
  if( pOp->p2>0 && pMem->i==1 ){
     pc = pOp->p2 - 1;
  }
  break;
}

/* Opcode: IfMemPos P1 P2 *
**
** If the value of memory cell P1 is 1 or greater, jump to P2. This
** opcode assumes that memory cell P1 holds an integer value.
*/
case OP_IfMemPos: {
  int i = pOp->p1;
  Mem *pMem;
  assert( i>=0 && i<p->nMem );
  pMem = &p->aMem[i];
  assert( pMem->flags==MEM_Int );
  if( pMem->i>0 ){
     pc = pOp->p2 - 1;
  }
  break;
}

/* Opcode: AggReset P1 P2 P3
**
Changes to test/corrupt.test.
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
#
#***********************************************************************
# This file implements regression tests for SQLite library.
#
# This file implements tests to make sure SQLite does not crash or
# segfault if it sees a corrupt database file.
#
# $Id: corrupt.test,v 1.6 2005/02/05 06:49:55 danielk1977 Exp $

catch {file delete -force test.db}
catch {file delete -force test.db-journal}

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








|







9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
#
#***********************************************************************
# This file implements regression tests for SQLite library.
#
# This file implements tests to make sure SQLite does not crash or
# segfault if it sees a corrupt database file.
#
# $Id: corrupt.test,v 1.7 2005/02/05 12:48:49 danielk1977 Exp $

catch {file delete -force test.db}
catch {file delete -force test.db-journal}

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

117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
# index on t1) in sqlite_master. Then perform a few different queries
# and make sure this is detected as corruption.
#
do_test corrupt-3.1 {
  db close
  copy_file test.bu test.db
  sqlite3 db test.db
  execsql {
    SELECT name, rootpage FROM sqlite_master
  }
} {t1 2 t1i1 85 t2 177}
do_test corrupt-3.2 {
  set t1_r [execsql {SELECT rootpage FROM sqlite_master WHERE name = 't1i1'}]
  set t1i1_r [execsql {SELECT rootpage FROM sqlite_master WHERE name = 't1'}]
  set cookie [expr [execsql {PRAGMA schema_version}] + 1]
  execsql "
    PRAGMA writable_schema = 1;
    UPDATE sqlite_master SET rootpage = $t1_r WHERE name = 't1';
    UPDATE sqlite_master SET rootpage = $t1i1_r WHERE name = 't1i1';
    PRAGMA writable_schema = 0;
    PRAGMA schema_version = $cookie;
    SELECT name, rootpage FROM sqlite_master;
  "
} {t1 85 t1i1 2 t2 177}

# This one tests the case caught by code in checkin [2313].
do_test corrupt-3.3 {
  db close
  sqlite3 db test.db
  catchsql {
    INSERT INTO t1 VALUES('abc');







|
<
|
<










<

|







117
118
119
120
121
122
123
124

125

126
127
128
129
130
131
132
133
134
135

136
137
138
139
140
141
142
143
144
# index on t1) in sqlite_master. Then perform a few different queries
# and make sure this is detected as corruption.
#
do_test corrupt-3.1 {
  db close
  copy_file test.bu test.db
  sqlite3 db test.db
  list

} {}

do_test corrupt-3.2 {
  set t1_r [execsql {SELECT rootpage FROM sqlite_master WHERE name = 't1i1'}]
  set t1i1_r [execsql {SELECT rootpage FROM sqlite_master WHERE name = 't1'}]
  set cookie [expr [execsql {PRAGMA schema_version}] + 1]
  execsql "
    PRAGMA writable_schema = 1;
    UPDATE sqlite_master SET rootpage = $t1_r WHERE name = 't1';
    UPDATE sqlite_master SET rootpage = $t1i1_r WHERE name = 't1i1';
    PRAGMA writable_schema = 0;
    PRAGMA schema_version = $cookie;

  "
} {}

# This one tests the case caught by code in checkin [2313].
do_test corrupt-3.3 {
  db close
  sqlite3 db test.db
  catchsql {
    INSERT INTO t1 VALUES('abc');
Changes to test/limit.test.
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.23 2005/01/21 04:25:47 danielk1977 Exp $

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

# Build some test data
#
execsql {







|







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.24 2005/02/05 12:48:49 danielk1977 Exp $

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

# Build some test data
#
execsql {
383
384
385
386
387
388
389
390





































391
    catchsql {
      SELECT * FROM t6 LIMIT 3
      UNION
      SELECT * FROM t7 LIMIT 3
    }
  } {1 {LIMIT clause should come after UNION not before}}
}






































finish_test








>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>

383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
    catchsql {
      SELECT * FROM t6 LIMIT 3
      UNION
      SELECT * FROM t7 LIMIT 3
    }
  } {1 {LIMIT clause should come after UNION not before}}
}

# Test LIMIT and OFFSET using SQL variables.
do_test limit-10.1 {
  set limit 10
  db eval {
    SELECT x FROM t1 LIMIT $limit;
  }
} {31 30 29 28 27 26 25 24 23 22}
do_test limit-10.2 {
  set limit 5
  set offset 5
  db eval {
    SELECT x FROM t1 LIMIT $limit OFFSET $offset;
  }
} {26 25 24 23 22}
do_test limit-10.3 {
  set limit -1
  db eval {
    SELECT x FROM t1 WHERE x<10 LIMIT $limit;
  }
} {9 8 7 6 5 4 3 2 1 0}
do_test limit-10.4 {
  set limit 1.5
  set rc [catch {
  db eval {
    SELECT x FROM t1 WHERE x<10 LIMIT $limit;
  } } msg]
  list $rc $msg
} {1 {datatype mismatch}}
do_test limit-10.5 {
  set limit "hello world"
  set rc [catch {
  db eval {
    SELECT x FROM t1 WHERE x<10 LIMIT $limit;
  } } msg]
  list $rc $msg
} {1 {datatype mismatch}}

finish_test