SQLite

Check-in [29cab124b4]
Login

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

Overview
Comment:Bug fixing in the new integer primary key code. (CVS 334)
Downloads: Tarball | ZIP archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 29cab124b4f7eae9d9feb60d2f3a2c443fd9b9aa
User & Date: drh 2001-12-22 14:49:25.000
Context
2001-12-22
19:27
Update documentation for the 2.2.0 release. (CVS 335) (check-in: 14392258c5 user: drh tags: trunk)
14:49
Bug fixing in the new integer primary key code. (CVS 334) (check-in: 29cab124b4 user: drh tags: trunk)
2001-12-21
14:30
Added support for the INTEGER PRIMARY KEY column type. (CVS 333) (check-in: 236a54d289 user: drh tags: trunk)
Changes
Unified Diff Ignore Whitespace Patch
Changes to src/build.c.
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
**     COPY
**     VACUUM
**     BEGIN TRANSACTION
**     COMMIT
**     ROLLBACK
**     PRAGMA
**
** $Id: build.c,v 1.60 2001/12/21 14:30:43 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 







|







21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
**     COPY
**     VACUUM
**     BEGIN TRANSACTION
**     COMMIT
**     ROLLBACK
**     PRAGMA
**
** $Id: build.c,v 1.61 2001/12/22 14:49:25 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 
1310
1311
1312
1313
1314
1315
1316




1317

1318
1319
1320
1321





1322

1323
1324
1325
1326
1327
1328
1329
    addr = sqliteVdbeAddOp(v, OP_FileRead, pTab->nCol, end);
    if( pDelimiter ){
      sqliteVdbeChangeP3(v, addr, pDelimiter->z, pDelimiter->n);
      sqliteVdbeDequoteP3(v, addr);
    }else{
      sqliteVdbeChangeP3(v, addr, "\t", 1);
    }




    sqliteVdbeAddOp(v, OP_NewRecno, 0, 0);

    if( pTab->pIndex ){
      sqliteVdbeAddOp(v, OP_Dup, 0, 0);
    }
    for(i=0; i<pTab->nCol; i++){





      sqliteVdbeAddOp(v, OP_FileColumn, i, 0);

    }
    sqliteVdbeAddOp(v, OP_MakeRecord, pTab->nCol, 0);
    sqliteVdbeAddOp(v, OP_Put, 0, 0);
    for(i=1, pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext, i++){
      if( pIdx->pNext ){
        sqliteVdbeAddOp(v, OP_Dup, 0, 0);
      }







>
>
>
>
|
>




>
>
>
>
>
|
>







1310
1311
1312
1313
1314
1315
1316
1317
1318
1319
1320
1321
1322
1323
1324
1325
1326
1327
1328
1329
1330
1331
1332
1333
1334
1335
1336
1337
1338
1339
1340
    addr = sqliteVdbeAddOp(v, OP_FileRead, pTab->nCol, end);
    if( pDelimiter ){
      sqliteVdbeChangeP3(v, addr, pDelimiter->z, pDelimiter->n);
      sqliteVdbeDequoteP3(v, addr);
    }else{
      sqliteVdbeChangeP3(v, addr, "\t", 1);
    }
    if( pTab->iPKey>=0 ){
      sqliteVdbeAddOp(v, OP_FileColumn, pTab->iPKey, 0);
      sqliteVdbeAddOp(v, OP_MustBeInt, 0, 0);
    }else{
      sqliteVdbeAddOp(v, OP_NewRecno, 0, 0);
    }
    if( pTab->pIndex ){
      sqliteVdbeAddOp(v, OP_Dup, 0, 0);
    }
    for(i=0; i<pTab->nCol; i++){
      if( i==pTab->iPKey ){
        /* The integer primary key column is filled with NULL since its
        ** value is always pulled from the record number */
        sqliteVdbeAddOp(v, OP_String, 0, 0);
      }else{
        sqliteVdbeAddOp(v, OP_FileColumn, i, 0);
      }
    }
    sqliteVdbeAddOp(v, OP_MakeRecord, pTab->nCol, 0);
    sqliteVdbeAddOp(v, OP_Put, 0, 0);
    for(i=1, pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext, i++){
      if( pIdx->pNext ){
        sqliteVdbeAddOp(v, OP_Dup, 0, 0);
      }
Changes to src/insert.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 C code routines that are called by the parser
** to handle INSERT statements in SQLite.
**
** $Id: insert.c,v 1.27 2001/12/21 14:30:43 drh Exp $
*/
#include "sqliteInt.h"

/*
** This routine is call to handle SQL of the following forms:
**
**    insert into TABLE (IDLIST) values(EXPRLIST)







|







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 INSERT statements in SQLite.
**
** $Id: insert.c,v 1.28 2001/12/22 14:49:25 drh Exp $
*/
#include "sqliteInt.h"

/*
** This routine is call to handle SQL of the following forms:
**
**    insert into TABLE (IDLIST) values(EXPRLIST)
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
  */
  if( keyColumn>=0 ){
    if( srcTab>=0 ){
      sqliteVdbeAddOp(v, OP_Column, srcTab, keyColumn);
    }else{
      sqliteExprCode(pParse, pList->a[keyColumn].pExpr);
    }
    sqliteVdbeAddOp(v, OP_AddImm, 0, 0);  /* Make sure ROWID is an integer */
  }else{
    sqliteVdbeAddOp(v, OP_NewRecno, base, 0);
  }

  /* If there are indices, we'll need this record number again, so make
  ** a copy.
  */







|







196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
  */
  if( keyColumn>=0 ){
    if( srcTab>=0 ){
      sqliteVdbeAddOp(v, OP_Column, srcTab, keyColumn);
    }else{
      sqliteExprCode(pParse, pList->a[keyColumn].pExpr);
    }
    sqliteVdbeAddOp(v, OP_MustBeInt, 0, 0);
  }else{
    sqliteVdbeAddOp(v, OP_NewRecno, base, 0);
  }

  /* If there are indices, we'll need this record number again, so make
  ** a copy.
  */
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.40 2001/12/21 14:30:43 drh Exp $
*/
%token_prefix TK_
%token_type {Token}
%default_type {Token}
%extra_argument {Parse *pParse}
%syntax_error {
  sqliteSetString(&pParse->zErrMsg,"syntax error",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.41 2001/12/22 14:49:25 drh Exp $
*/
%token_prefix TK_
%token_type {Token}
%default_type {Token}
%extra_argument {Parse *pParse}
%syntax_error {
  sqliteSetString(&pParse->zErrMsg,"syntax error",0);
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531

idxlist(A) ::= idxlist(X) COMMA idxitem(Y).  
     {A = sqliteIdListAppend(X,&Y);}
idxlist(A) ::= idxitem(Y).
     {A = sqliteIdListAppend(0,&Y);}
idxitem(A) ::= ids(X).          {A = X;}

///////////////////////////// The CREATE INDEX command ///////////////////////
//

cmd ::= DROP INDEX ids(X).      {sqliteDropIndex(pParse, &X);}


///////////////////////////// The DROP INDEX command /////////////////////////
//
cmd ::= COPY ids(X) FROM ids(Y) USING DELIMITERS STRING(Z).
    {sqliteCopy(pParse,&X,&Y,&Z);}
cmd ::= COPY ids(X) FROM ids(Y).
    {sqliteCopy(pParse,&X,&Y,0);}

///////////////////////////// The VACUUM command /////////////////////////////







|





|







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

idxlist(A) ::= idxlist(X) COMMA idxitem(Y).  
     {A = sqliteIdListAppend(X,&Y);}
idxlist(A) ::= idxitem(Y).
     {A = sqliteIdListAppend(0,&Y);}
idxitem(A) ::= ids(X).          {A = X;}

///////////////////////////// The DROP INDEX command /////////////////////////
//

cmd ::= DROP INDEX ids(X).      {sqliteDropIndex(pParse, &X);}


///////////////////////////// The COPY command ///////////////////////////////
//
cmd ::= COPY ids(X) FROM ids(Y) USING DELIMITERS STRING(Z).
    {sqliteCopy(pParse,&X,&Y,&Z);}
cmd ::= COPY ids(X) FROM ids(Y).
    {sqliteCopy(pParse,&X,&Y,0);}

///////////////////////////// The VACUUM command /////////////////////////////
Changes to src/select.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 C code routines that are called by the parser
** to handle SELECT statements in SQLite.
**
** $Id: select.c,v 1.50 2001/12/16 20:05:06 drh Exp $
*/
#include "sqliteInt.h"

/*
** Allocate a new Select structure and return a pointer to that
** structure.
*/







|







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.51 2001/12/22 14:49:25 drh Exp $
*/
#include "sqliteInt.h"

/*
** Allocate a new Select structure and return a pointer to that
** structure.
*/
254
255
256
257
258
259
260




261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
    if( p==0 ) continue;
    showFullNames = (pParse->db->flags & SQLITE_FullColNames)!=0;
    if( p->span.z && p->span.z[0] && !showFullNames ){
      int addr = sqliteVdbeAddOp(v,OP_ColumnName, i, 0);
      sqliteVdbeChangeP3(v, -1, p->span.z, p->span.n);
      sqliteVdbeCompressSpace(v, addr);
    }else if( p->op==TK_COLUMN && pTabList ){




      if( pTabList->nId>1 || showFullNames ){
        char *zName = 0;
        Table *pTab = pTabList->a[p->iTable - pParse->nTab].pTab;
        char *zTab;
 
        zTab = pTabList->a[p->iTable - pParse->nTab].zAlias;
        if( showFullNames || zTab==0 ) zTab = pTab->zName;
        sqliteSetString(&zName, zTab, ".", pTab->aCol[p->iColumn].zName, 0);
        sqliteVdbeAddOp(v, OP_ColumnName, i, 0);
        sqliteVdbeChangeP3(v, -1, zName, strlen(zName));
        sqliteFree(zName);
      }else{
        Table *pTab = pTabList->a[0].pTab;
        char *zName = pTab->aCol[p->iColumn].zName;
        sqliteVdbeAddOp(v, OP_ColumnName, i, 0);
        sqliteVdbeChangeP3(v, -1, zName, P3_STATIC);
      }
    }else if( p->span.z && p->span.z[0] ){
      int addr = sqliteVdbeAddOp(v,OP_ColumnName, i, 0);
      sqliteVdbeChangeP3(v, -1, p->span.z, p->span.n);
      sqliteVdbeCompressSpace(v, addr);







>
>
>
>


<




|




<
|







254
255
256
257
258
259
260
261
262
263
264
265
266

267
268
269
270
271
272
273
274
275

276
277
278
279
280
281
282
283
    if( p==0 ) continue;
    showFullNames = (pParse->db->flags & SQLITE_FullColNames)!=0;
    if( p->span.z && p->span.z[0] && !showFullNames ){
      int addr = sqliteVdbeAddOp(v,OP_ColumnName, i, 0);
      sqliteVdbeChangeP3(v, -1, p->span.z, p->span.n);
      sqliteVdbeCompressSpace(v, addr);
    }else if( p->op==TK_COLUMN && pTabList ){
      Table *pTab = pTabList->a[p->iTable - pParse->nTab].pTab;
      int iCol = p->iColumn;
      if( iCol<0 ) iCol = pTab->iPKey;
      assert( iCol>=0 && iCol<pTab->nCol );
      if( pTabList->nId>1 || showFullNames ){
        char *zName = 0;

        char *zTab;
 
        zTab = pTabList->a[p->iTable - pParse->nTab].zAlias;
        if( showFullNames || zTab==0 ) zTab = pTab->zName;
        sqliteSetString(&zName, zTab, ".", pTab->aCol[iCol].zName, 0);
        sqliteVdbeAddOp(v, OP_ColumnName, i, 0);
        sqliteVdbeChangeP3(v, -1, zName, strlen(zName));
        sqliteFree(zName);
      }else{

        char *zName = pTab->aCol[iCol].zName;
        sqliteVdbeAddOp(v, OP_ColumnName, i, 0);
        sqliteVdbeChangeP3(v, -1, zName, P3_STATIC);
      }
    }else if( p->span.z && p->span.z[0] ){
      int addr = sqliteVdbeAddOp(v,OP_ColumnName, i, 0);
      sqliteVdbeChangeP3(v, -1, p->span.z, p->span.n);
      sqliteVdbeCompressSpace(v, addr);
Changes to src/sqlite.h.in.
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 header file defines the interface that the SQLite library
** presents to client programs.
**
** @(#) $Id: sqlite.h.in,v 1.23 2001/11/03 23:57:09 drh Exp $
*/
#ifndef _SQLITE_H_
#define _SQLITE_H_
#include <stdarg.h>     /* Needed for the definition of va_list */

/*
** The version of the SQLite library.







|







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 header file defines the interface that the SQLite library
** presents to client programs.
**
** @(#) $Id: sqlite.h.in,v 1.24 2001/12/22 14:49:25 drh Exp $
*/
#ifndef _SQLITE_H_
#define _SQLITE_H_
#include <stdarg.h>     /* Needed for the definition of va_list */

/*
** The version of the SQLite library.
156
157
158
159
160
161
162

163
164
165
166
167
168
169
#define SQLITE_FULL        13   /* Insertion failed because database is full */
#define SQLITE_CANTOPEN    14   /* Unable to open the database file */
#define SQLITE_PROTOCOL    15   /* Database lock protocol error */
#define SQLITE_EMPTY       16   /* (Internal Only) Database table is empty */
#define SQLITE_SCHEMA      17   /* The database schema changed */
#define SQLITE_TOOBIG      18   /* Too much data for one row of a table */
#define SQLITE_CONSTRAINT  19   /* Abort due to contraint violation */


/* If the parameter to this routine is one of the return value constants
** defined above, then this routine returns a constant text string which
** descripts (in English) the meaning of the return value.
*/
const char *sqlite_error_string(int);
#define sqliteErrStr sqlite_error_string  /* Legacy. Do not use in new code. */







>







156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
#define SQLITE_FULL        13   /* Insertion failed because database is full */
#define SQLITE_CANTOPEN    14   /* Unable to open the database file */
#define SQLITE_PROTOCOL    15   /* Database lock protocol error */
#define SQLITE_EMPTY       16   /* (Internal Only) Database table is empty */
#define SQLITE_SCHEMA      17   /* The database schema changed */
#define SQLITE_TOOBIG      18   /* Too much data for one row of a table */
#define SQLITE_CONSTRAINT  19   /* Abort due to contraint violation */
#define SQLITE_MISMATCH    20   /* Data type mismatch */

/* If the parameter to this routine is one of the return value constants
** defined above, then this routine returns a constant text string which
** descripts (in English) the meaning of the return value.
*/
const char *sqlite_error_string(int);
#define sqliteErrStr sqlite_error_string  /* Legacy. Do not use in new code. */
Changes to src/update.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 C code routines that are called by the parser
** to handle UPDATE statements.
**
** $Id: update.c,v 1.23 2001/12/21 14:30:43 drh Exp $
*/
#include "sqliteInt.h"

/*
** Process an UPDATE statement.
*/
void sqliteUpdate(







|







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 UPDATE statements.
**
** $Id: update.c,v 1.24 2001/12/22 14:49:25 drh Exp $
*/
#include "sqliteInt.h"

/*
** Process an UPDATE statement.
*/
void sqliteUpdate(
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225

  /* If changing the record number, remove the old record number
  ** from the top of the stack and replace it with the new one.
  */
  if( chngRecno ){
    sqliteVdbeAddOp(v, OP_Pop, 1, 0);
    sqliteExprCode(pParse, pRecnoExpr);
    sqliteVdbeAddOp(v, OP_AddImm, 0, 0);
  }

  /* Compute new data for this record.  
  */
  for(i=0; i<pTab->nCol; i++){
    if( i==pTab->iPKey ){
      sqliteVdbeAddOp(v, OP_Dup, i, 0);







|







211
212
213
214
215
216
217
218
219
220
221
222
223
224
225

  /* If changing the record number, remove the old record number
  ** from the top of the stack and replace it with the new one.
  */
  if( chngRecno ){
    sqliteVdbeAddOp(v, OP_Pop, 1, 0);
    sqliteExprCode(pParse, pRecnoExpr);
    sqliteVdbeAddOp(v, OP_MustBeInt, 0, 0);
  }

  /* Compute new data for this record.  
  */
  for(i=0; i<pTab->nCol; i++){
    if( i==pTab->iPKey ){
      sqliteVdbeAddOp(v, OP_Dup, i, 0);
Changes to src/util.c.
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
**
*************************************************************************
** Utility functions used throughout sqlite.
**
** This file contains functions for allocating memory, comparing
** strings, and stuff like that.
**
** $Id: util.c,v 1.33 2001/11/24 00:31:46 drh Exp $
*/
#include "sqliteInt.h"
#include <stdarg.h>
#include <ctype.h>

/*
** If malloc() ever fails, this global variable gets set to 1.







|







10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
**
*************************************************************************
** Utility functions used throughout sqlite.
**
** This file contains functions for allocating memory, comparing
** strings, and stuff like that.
**
** $Id: util.c,v 1.34 2001/12/22 14:49:25 drh Exp $
*/
#include "sqliteInt.h"
#include <stdarg.h>
#include <ctype.h>

/*
** If malloc() ever fails, this global variable gets set to 1.
1066
1067
1068
1069
1070
1071
1072

1073
1074
1075
1076
    case SQLITE_FULL:       z = "database is full";                      break;
    case SQLITE_CANTOPEN:   z = "unable to open database file";          break;
    case SQLITE_PROTOCOL:   z = "database locking protocol failure";     break;
    case SQLITE_EMPTY:      z = "table contains no data";                break;
    case SQLITE_SCHEMA:     z = "database schema has changed";           break;
    case SQLITE_TOOBIG:     z = "too much data for one table row";       break;
    case SQLITE_CONSTRAINT: z = "constraint failed";                     break;

    default:                z = "unknown error";                         break;
  }
  return z;
}







>




1066
1067
1068
1069
1070
1071
1072
1073
1074
1075
1076
1077
    case SQLITE_FULL:       z = "database is full";                      break;
    case SQLITE_CANTOPEN:   z = "unable to open database file";          break;
    case SQLITE_PROTOCOL:   z = "database locking protocol failure";     break;
    case SQLITE_EMPTY:      z = "table contains no data";                break;
    case SQLITE_SCHEMA:     z = "database schema has changed";           break;
    case SQLITE_TOOBIG:     z = "too much data for one table row";       break;
    case SQLITE_CONSTRAINT: z = "constraint failed";                     break;
    case SQLITE_MISMATCH:   z = "datatype mismatch";                     break;
    default:                z = "unknown error";                         break;
  }
  return z;
}
Changes to src/vdbe.c.
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
** type to the other occurs as necessary.
** 
** Most of the code in this file is taken up by the sqliteVdbeExec()
** function which does the work of interpreting a VDBE program.
** But other routines are also provided to help in building up
** a program instruction by instruction.
**
** $Id: vdbe.c,v 1.101 2001/12/21 14:30:43 drh Exp $
*/
#include "sqliteInt.h"
#include <ctype.h>

/*
** The following global variable is incremented every time a cursor
** moves, either by the OP_MoveTo or the OP_Next opcode.  The test







|







26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
** type to the other occurs as necessary.
** 
** Most of the code in this file is taken up by the sqliteVdbeExec()
** function which does the work of interpreting a VDBE program.
** But other routines are also provided to help in building up
** a program instruction by instruction.
**
** $Id: vdbe.c,v 1.102 2001/12/22 14:49:25 drh Exp $
*/
#include "sqliteInt.h"
#include <ctype.h>

/*
** The following global variable is incremented every time a cursor
** moves, either by the OP_MoveTo or the OP_Next opcode.  The test
704
705
706
707
708
709
710









711
712
713
714
715
716
717
  if( *zNum!='e' && *zNum!='E' ) return 0;
  zNum++;
  if( *zNum=='-' || *zNum=='+' ) zNum++;
  if( !isdigit(*zNum) ) return 0;
  while( isdigit(*zNum) ) zNum++;
  return *zNum==0;
}










/*
** Delete a keylist
*/
static void KeylistFree(Keylist *p){
  while( p ){
    Keylist *pNext = p->pNext;







>
>
>
>
>
>
>
>
>







704
705
706
707
708
709
710
711
712
713
714
715
716
717
718
719
720
721
722
723
724
725
726
  if( *zNum!='e' && *zNum!='E' ) return 0;
  zNum++;
  if( *zNum=='-' || *zNum=='+' ) zNum++;
  if( !isdigit(*zNum) ) return 0;
  while( isdigit(*zNum) ) zNum++;
  return *zNum==0;
}

/*
** Return TRUE if zNum is an integer.
*/
static int isInteger(const char *zNum){
  if( *zNum=='-' || *zNum=='+' ) zNum++;
  while( isdigit(*zNum) ) zNum++;
  return *zNum==0;
}

/*
** Delete a keylist
*/
static void KeylistFree(Keylist *p){
  while( p ){
    Keylist *pNext = p->pNext;
856
857
858
859
860
861
862
863
864
865
866
867
868
869
870
871
872
873
874
875
876
877
878
  "FileOpen",          "FileRead",          "FileColumn",        "AggReset",
  "AggFocus",          "AggIncr",           "AggNext",           "AggSet",
  "AggGet",            "SetInsert",         "SetFound",          "SetNotFound",
  "MakeRecord",        "MakeKey",           "MakeIdxKey",        "IncrKey",
  "Goto",              "If",                "Halt",              "ColumnCount",
  "ColumnName",        "Callback",          "NullCallback",      "Integer",
  "String",            "Pop",               "Dup",               "Pull",
  "Add",               "AddImm",            "Subtract",          "Multiply",
  "Divide",            "Remainder",         "BitAnd",            "BitOr",
  "BitNot",            "ShiftLeft",         "ShiftRight",        "AbsValue",
  "Precision",         "Min",               "Max",               "Like",
  "Glob",              "Eq",                "Ne",                "Lt",
  "Le",                "Gt",                "Ge",                "IsNull",
  "NotNull",           "Negative",          "And",               "Or",
  "Not",               "Concat",            "Noop",              "Strlen",
  "Substr",            "Limit",           
};

/*
** Given the name of an opcode, return its number.  Return 0 if
** there is no match.
**
** This routine is used for testing and debugging.







|
|
|
|
|
|
|
|
|







865
866
867
868
869
870
871
872
873
874
875
876
877
878
879
880
881
882
883
884
885
886
887
  "FileOpen",          "FileRead",          "FileColumn",        "AggReset",
  "AggFocus",          "AggIncr",           "AggNext",           "AggSet",
  "AggGet",            "SetInsert",         "SetFound",          "SetNotFound",
  "MakeRecord",        "MakeKey",           "MakeIdxKey",        "IncrKey",
  "Goto",              "If",                "Halt",              "ColumnCount",
  "ColumnName",        "Callback",          "NullCallback",      "Integer",
  "String",            "Pop",               "Dup",               "Pull",
  "MustBeInt",         "Add",               "AddImm",            "Subtract",
  "Multiply",          "Divide",            "Remainder",         "BitAnd",
  "BitOr",             "BitNot",            "ShiftLeft",         "ShiftRight",
  "AbsValue",          "Precision",         "Min",               "Max",
  "Like",              "Glob",              "Eq",                "Ne",
  "Lt",                "Le",                "Gt",                "Ge",
  "IsNull",            "NotNull",           "Negative",          "And",
  "Or",                "Not",               "Concat",            "Noop",
  "Strlen",            "Substr",            "Limit",           
};

/*
** Given the name of an opcode, return its number.  Return 0 if
** there is no match.
**
** This routine is used for testing and debugging.
962
963
964
965
966
967
968
969


970









971
972
973
974
975
976
977
978
979
980
981
982
983









984
985
986
987
988
989
990
  }else if( pRight ){
    pTail->pNext = pRight;
  }
  return sHead.pNext;
}

/*
** Convert an integer into a big-endian integer.  In other words,


** make sure the most significant byte comes first.









*/
static int bigEndian(int x){
  union {
     char zBuf[sizeof(int)];
     int i;
  } ux;
  ux.zBuf[3] = x&0xff;
  ux.zBuf[2] = (x>>8)&0xff;
  ux.zBuf[1] = (x>>16)&0xff;
  ux.zBuf[0] = (x>>24)&0xff;
  return ux.i;
}










/*
** Code contained within the VERIFY() macro is not needed for correct
** execution.  It is there only to catch errors.  So when we compile
** with NDEBUG=1, the VERIFY() code is omitted.
*/
#ifdef NDEBUG
# define VERIFY(X)







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

|











>
>
>
>
>
>
>
>
>







971
972
973
974
975
976
977
978
979
980
981
982
983
984
985
986
987
988
989
990
991
992
993
994
995
996
997
998
999
1000
1001
1002
1003
1004
1005
1006
1007
1008
1009
1010
1011
1012
1013
1014
1015
1016
1017
1018
1019
  }else if( pRight ){
    pTail->pNext = pRight;
  }
  return sHead.pNext;
}

/*
** Convert an integer in between the native integer format and
** the bigEndian format used as the record number for tables.
**
** The bigEndian format (most significant byte first) is used for
** record numbers so that records will sort into the correct order
** even though memcmp() is used to compare the keys.  On machines
** whose native integer format is little endian (ex: i486) the
** order of bytes is reversed.  On native big-endian machines
** (ex: Alpha, Sparc, Motorola) the byte order is the same.
**
** This function is its own inverse.  In other words
**
**         X == byteSwap(byteSwap(X))
*/
static int byteSwap(int x){
  union {
     char zBuf[sizeof(int)];
     int i;
  } ux;
  ux.zBuf[3] = x&0xff;
  ux.zBuf[2] = (x>>8)&0xff;
  ux.zBuf[1] = (x>>16)&0xff;
  ux.zBuf[0] = (x>>24)&0xff;
  return ux.i;
}

/*
** When converting from the native format to the key format and back
** again, in addition to changing the byte order we invert the high-order
** bit of the most significant byte.  This causes negative numbers to
** sort before positive numbers in the memcmp() function.
*/
#define keyToInt(X)   (byteSwap(X) ^ 0x80000000)
#define intToKey(X)   (byteSwap((X) ^ 0x80000000))

/*
** Code contained within the VERIFY() macro is not needed for correct
** execution.  It is there only to catch errors.  So when we compile
** with NDEBUG=1, the VERIFY() code is omitted.
*/
#ifdef NDEBUG
# define VERIFY(X)
1662
1663
1664
1665
1666
1667
1668









































1669
1670
1671
1672
1673
1674
1675
case OP_AddImm: {
  int tos = p->tos;
  VERIFY( if( tos<0 ) goto not_enough_stack; )
  Integerify(p, tos);
  aStack[tos].i += pOp->p1;
  break;
}










































/* Opcode: Eq * P2 *
**
** Pop the top two elements from the stack.  If they are equal, then
** jump to instruction P2.  Otherwise, continue to the next instruction.
*/
/* Opcode: Ne * P2 *







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







1691
1692
1693
1694
1695
1696
1697
1698
1699
1700
1701
1702
1703
1704
1705
1706
1707
1708
1709
1710
1711
1712
1713
1714
1715
1716
1717
1718
1719
1720
1721
1722
1723
1724
1725
1726
1727
1728
1729
1730
1731
1732
1733
1734
1735
1736
1737
1738
1739
1740
1741
1742
1743
1744
1745
case OP_AddImm: {
  int tos = p->tos;
  VERIFY( if( tos<0 ) goto not_enough_stack; )
  Integerify(p, tos);
  aStack[tos].i += pOp->p1;
  break;
}

/* Opcode: MustBeInt  * P2 *
** 
** Force the top of the stack to be an integer.  If the top of the
** stack is not an integer and cannot be comverted into an integer
** with out data loss, then jump immediately to P2, or if P2==0
** raise an SQLITE_MISMATCH exception.
*/
case OP_MustBeInt: {
  int tos = p->tos;
  VERIFY( if( tos<0 ) goto not_enough_stack; )
  if( aStack[tos].flags & STK_Int ){
    /* Do nothing */
  }else if( aStack[tos].flags & STK_Real ){
    int i = aStack[tos].r;
    double r = i;
    if( r!=aStack[tos].r ){
      goto mismatch;
    }
    aStack[tos].i = i;
  }else if( aStack[tos].flags & STK_Str ){
    if( !isInteger(zStack[tos]) ){
      goto mismatch;
    }
    p->aStack[tos].i = atoi(p->zStack[tos]);
  }else{
    goto mismatch;
  }
  Release(p, tos);
  p->aStack[tos].flags = STK_Int;
  break;

mismatch:
  if( pOp->p2==0 ){
    rc = SQLITE_MISMATCH;
    goto abort_due_to_error;
  }else{
    pc = pOp->p2 - 1;
  }
  break;
}

/* Opcode: Eq * P2 *
**
** Pop the top two elements from the stack.  If they are equal, then
** jump to instruction P2.  Otherwise, continue to the next instruction.
*/
/* Opcode: Ne * P2 *
2151
2152
2153
2154
2155
2156
2157
2158
2159
2160
2161
2162
2163
2164
2165
      memcpy(&zNewKey[j], zStack[i] ? zStack[i] : aStack[i].z, aStack[i].n);
      j += aStack[i].n;
    }
  }
  if( addRowid ){
    u32 iKey;
    Integerify(p, p->tos-nField);
    iKey = bigEndian(aStack[p->tos-nField].i);
    memcpy(&zNewKey[j], &iKey, sizeof(u32));
  }
  if( pOp->p2==0 ) PopStack(p, nField+addRowid);
  VERIFY( NeedStack(p, p->tos+1); )
  p->tos++;
  aStack[p->tos].n = nByte;
  aStack[p->tos].flags = STK_Str|STK_Dyn;







|







2221
2222
2223
2224
2225
2226
2227
2228
2229
2230
2231
2232
2233
2234
2235
      memcpy(&zNewKey[j], zStack[i] ? zStack[i] : aStack[i].z, aStack[i].n);
      j += aStack[i].n;
    }
  }
  if( addRowid ){
    u32 iKey;
    Integerify(p, p->tos-nField);
    iKey = intToKey(aStack[p->tos-nField].i);
    memcpy(&zNewKey[j], &iKey, sizeof(u32));
  }
  if( pOp->p2==0 ) PopStack(p, nField+addRowid);
  VERIFY( NeedStack(p, p->tos+1); )
  p->tos++;
  aStack[p->tos].n = nByte;
  aStack[p->tos].flags = STK_Str|STK_Dyn;
2536
2537
2538
2539
2540
2541
2542
2543
2544
2545
2546
2547
2548
2549
2550
2551
2552
2553
  int tos = p->tos;
  Cursor *pC;

  VERIFY( if( tos<0 ) goto not_enough_stack; )
  if( i>=0 && i<p->nCursor && (pC = &p->aCsr[i])->pCursor!=0 ){
    int res;
    if( aStack[tos].flags & STK_Int ){
      int iKey = bigEndian(aStack[tos].i);
      sqliteBtreeMoveto(pC->pCursor, (char*)&iKey, sizeof(int), &res);
      pC->lastRecno = aStack[tos].i;
      pC->recnoIsValid = 1;
    }else{
      if( Stringify(p, tos) ) goto no_mem;
      sqliteBtreeMoveto(pC->pCursor, zStack[tos], aStack[tos].n, &res);
      pC->recnoIsValid = 0;
    }
    sqlite_search_count++;
    if( res<0 ){







|


|







2606
2607
2608
2609
2610
2611
2612
2613
2614
2615
2616
2617
2618
2619
2620
2621
2622
2623
  int tos = p->tos;
  Cursor *pC;

  VERIFY( if( tos<0 ) goto not_enough_stack; )
  if( i>=0 && i<p->nCursor && (pC = &p->aCsr[i])->pCursor!=0 ){
    int res;
    if( aStack[tos].flags & STK_Int ){
      int iKey = intToKey(aStack[tos].i);
      sqliteBtreeMoveto(pC->pCursor, (char*)&iKey, sizeof(int), &res);
      pC->lastRecno = aStack[tos].i;
      pC->recnoIsValid = res==0;
    }else{
      if( Stringify(p, tos) ) goto no_mem;
      sqliteBtreeMoveto(pC->pCursor, zStack[tos], aStack[tos].n, &res);
      pC->recnoIsValid = 0;
    }
    sqlite_search_count++;
    if( res<0 ){
2602
2603
2604
2605
2606
2607
2608
2609
2610
2611
2612
2613
2614
2615
2616
  int tos = p->tos;
  int alreadyExists = 0;
  Cursor *pC;
  VERIFY( if( tos<0 ) goto not_enough_stack; )
  if( VERIFY( i>=0 && i<p->nCursor && ) (pC = &p->aCsr[i])->pCursor!=0 ){
    int res, rx;
    if( aStack[tos].flags & STK_Int ){
      int iKey = bigEndian(aStack[tos].i);
      rx = sqliteBtreeMoveto(pC->pCursor, (char*)&iKey, sizeof(int), &res);
    }else{
      if( Stringify(p, tos) ) goto no_mem;
      rx = sqliteBtreeMoveto(pC->pCursor, zStack[tos], aStack[tos].n, &res);
    }
    alreadyExists = rx==SQLITE_OK && res==0;
  }







|







2672
2673
2674
2675
2676
2677
2678
2679
2680
2681
2682
2683
2684
2685
2686
  int tos = p->tos;
  int alreadyExists = 0;
  Cursor *pC;
  VERIFY( if( tos<0 ) goto not_enough_stack; )
  if( VERIFY( i>=0 && i<p->nCursor && ) (pC = &p->aCsr[i])->pCursor!=0 ){
    int res, rx;
    if( aStack[tos].flags & STK_Int ){
      int iKey = intToKey(aStack[tos].i);
      rx = sqliteBtreeMoveto(pC->pCursor, (char*)&iKey, sizeof(int), &res);
    }else{
      if( Stringify(p, tos) ) goto no_mem;
      rx = sqliteBtreeMoveto(pC->pCursor, zStack[tos], aStack[tos].n, &res);
    }
    alreadyExists = rx==SQLITE_OK && res==0;
  }
2661
2662
2663
2664
2665
2666
2667
2668
2669
2670
2671
2672
2673
2674
2675
    do{
      if( cnt>5 ){
        v = sqliteRandomInteger();
      }else{
        v += sqliteRandomByte() + 1;
      }
      if( v==0 ) continue;
      x = bigEndian(v);
      rx = sqliteBtreeMoveto(pC->pCursor, &x, sizeof(int), &res);
      cnt++;
    }while( cnt<1000 && rx==SQLITE_OK && res==0 );
    db->nextRowid = v;
    if( rx==SQLITE_OK && res==0 ){
      rc = SQLITE_FULL;
      goto abort_due_to_error;







|







2731
2732
2733
2734
2735
2736
2737
2738
2739
2740
2741
2742
2743
2744
2745
    do{
      if( cnt>5 ){
        v = sqliteRandomInteger();
      }else{
        v += sqliteRandomByte() + 1;
      }
      if( v==0 ) continue;
      x = intToKey(v);
      rx = sqliteBtreeMoveto(pC->pCursor, &x, sizeof(int), &res);
      cnt++;
    }while( cnt<1000 && rx==SQLITE_OK && res==0 );
    db->nextRowid = v;
    if( rx==SQLITE_OK && res==0 ){
      rc = SQLITE_FULL;
      goto abort_due_to_error;
2703
2704
2705
2706
2707
2708
2709
2710
2711
2712
2713
2714
2715
2716
2717
    int nKey, iKey;
    if( (aStack[nos].flags & STK_Int)==0 ){
      if( Stringify(p, nos) ) goto no_mem;
      nKey = aStack[nos].n;
      zKey = zStack[nos];
    }else{
      nKey = sizeof(int);
      iKey = bigEndian(aStack[nos].i);
      zKey = (char*)&iKey;
    }
    if( pOp->p2 ){
      int res;
      rc = sqliteBtreeMoveto(p->aCsr[i].pCursor, zKey, nKey, &res);
      if( res==0 && rc==SQLITE_OK ){
        rc = SQLITE_CONSTRAINT;







|







2773
2774
2775
2776
2777
2778
2779
2780
2781
2782
2783
2784
2785
2786
2787
    int nKey, iKey;
    if( (aStack[nos].flags & STK_Int)==0 ){
      if( Stringify(p, nos) ) goto no_mem;
      nKey = aStack[nos].n;
      zKey = zStack[nos];
    }else{
      nKey = sizeof(int);
      iKey = intToKey(aStack[nos].i);
      zKey = (char*)&iKey;
    }
    if( pOp->p2 ){
      int res;
      rc = sqliteBtreeMoveto(p->aCsr[i].pCursor, zKey, nKey, &res);
      if( res==0 && rc==SQLITE_OK ){
        rc = SQLITE_CONSTRAINT;
2871
2872
2873
2874
2875
2876
2877
2878
2879
2880
2881
2882
2883
2884
2885
  VERIFY( if( NeedStack(p, p->tos) ) goto no_mem; )
  if( VERIFY( i>=0 && i<p->nCursor && ) (pCrsr = p->aCsr[i].pCursor)!=0 ){
    int v;
    if( p->aCsr[i].recnoIsValid ){
      v = p->aCsr[i].lastRecno;
    }else{
      sqliteBtreeKey(pCrsr, 0, sizeof(u32), (char*)&v);
      v = bigEndian(v);
    }
    aStack[tos].i = v;
    aStack[tos].flags = STK_Int;
  }
  break;
}








|







2941
2942
2943
2944
2945
2946
2947
2948
2949
2950
2951
2952
2953
2954
2955
  VERIFY( if( NeedStack(p, p->tos) ) goto no_mem; )
  if( VERIFY( i>=0 && i<p->nCursor && ) (pCrsr = p->aCsr[i].pCursor)!=0 ){
    int v;
    if( p->aCsr[i].recnoIsValid ){
      v = p->aCsr[i].lastRecno;
    }else{
      sqliteBtreeKey(pCrsr, 0, sizeof(u32), (char*)&v);
      v = keyToInt(v);
    }
    aStack[tos].i = v;
    aStack[tos].flags = STK_Int;
  }
  break;
}

3056
3057
3058
3059
3060
3061
3062
3063
3064
3065
3066
3067
3068
3069
3070

  VERIFY( if( NeedStack(p, p->tos) ) goto no_mem; )
  if( VERIFY( i>=0 && i<p->nCursor && ) (pCrsr = p->aCsr[i].pCursor)!=0 ){
    int v;
    int sz;
    sqliteBtreeKeySize(pCrsr, &sz);
    sqliteBtreeKey(pCrsr, sz - sizeof(u32), sizeof(u32), (char*)&v);
    v = bigEndian(v);
    aStack[tos].i = v;
    aStack[tos].flags = STK_Int;
  }
  break;
}

/* Opcode: IdxGT P1 P2 *







|







3126
3127
3128
3129
3130
3131
3132
3133
3134
3135
3136
3137
3138
3139
3140

  VERIFY( if( NeedStack(p, p->tos) ) goto no_mem; )
  if( VERIFY( i>=0 && i<p->nCursor && ) (pCrsr = p->aCsr[i].pCursor)!=0 ){
    int v;
    int sz;
    sqliteBtreeKeySize(pCrsr, &sz);
    sqliteBtreeKey(pCrsr, sz - sizeof(u32), sizeof(u32), (char*)&v);
    v = keyToInt(v);
    aStack[tos].i = v;
    aStack[tos].flags = STK_Int;
  }
  break;
}

/* Opcode: IdxGT P1 P2 *
Changes to src/vdbe.h.
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
*************************************************************************
** Header file for the Virtual DataBase Engine (VDBE)
**
** This header defines the interface to the virtual database engine
** or VDBE.  The VDBE implements an abstract machine that runs a
** simple program to access and modify the underlying database.
**
** $Id: vdbe.h,v 1.36 2001/11/08 00:45:22 drh Exp $
*/
#ifndef _SQLITE_VDBE_H_
#define _SQLITE_VDBE_H_
#include <stdio.h>

/*
** A single VDBE is an opaque structure named "Vdbe".  Only routines







|







11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
*************************************************************************
** Header file for the Virtual DataBase Engine (VDBE)
**
** This header defines the interface to the virtual database engine
** or VDBE.  The VDBE implements an abstract machine that runs a
** simple program to access and modify the underlying database.
**
** $Id: vdbe.h,v 1.37 2001/12/22 14:49:26 drh Exp $
*/
#ifndef _SQLITE_VDBE_H_
#define _SQLITE_VDBE_H_
#include <stdio.h>

/*
** A single VDBE is an opaque structure named "Vdbe".  Only routines
154
155
156
157
158
159
160

161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
#define OP_NullCallback       71

#define OP_Integer            72
#define OP_String             73
#define OP_Pop                74
#define OP_Dup                75
#define OP_Pull               76


#define OP_Add                77
#define OP_AddImm             78
#define OP_Subtract           79
#define OP_Multiply           80
#define OP_Divide             81
#define OP_Remainder          82
#define OP_BitAnd             83
#define OP_BitOr              84
#define OP_BitNot             85
#define OP_ShiftLeft          86
#define OP_ShiftRight         87
#define OP_AbsValue           88
#define OP_Precision          89
#define OP_Min                90
#define OP_Max                91
#define OP_Like               92
#define OP_Glob               93
#define OP_Eq                 94
#define OP_Ne                 95
#define OP_Lt                 96
#define OP_Le                 97
#define OP_Gt                 98
#define OP_Ge                 99
#define OP_IsNull            100
#define OP_NotNull           101
#define OP_Negative          102
#define OP_And               103
#define OP_Or                104
#define OP_Not               105
#define OP_Concat            106
#define OP_Noop              107

#define OP_Strlen            108
#define OP_Substr            109

#define OP_Limit             110

#define OP_MAX               110

/*
** Prototypes for the VDBE interface.  See comments on the implementation
** for a description of what each of these routines does.
*/
Vdbe *sqliteVdbeCreate(sqlite*);
void sqliteVdbeCreateCallback(Vdbe*, int*);







>

|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|

|
|

|

|







154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
#define OP_NullCallback       71

#define OP_Integer            72
#define OP_String             73
#define OP_Pop                74
#define OP_Dup                75
#define OP_Pull               76
#define OP_MustBeInt          77

#define OP_Add                78
#define OP_AddImm             79
#define OP_Subtract           80
#define OP_Multiply           81
#define OP_Divide             82
#define OP_Remainder          83
#define OP_BitAnd             84
#define OP_BitOr              85
#define OP_BitNot             86
#define OP_ShiftLeft          87
#define OP_ShiftRight         88
#define OP_AbsValue           89
#define OP_Precision          90
#define OP_Min                91
#define OP_Max                92
#define OP_Like               93
#define OP_Glob               94
#define OP_Eq                 95
#define OP_Ne                 96
#define OP_Lt                 97
#define OP_Le                 98
#define OP_Gt                 99
#define OP_Ge                100
#define OP_IsNull            101
#define OP_NotNull           102
#define OP_Negative          103
#define OP_And               104
#define OP_Or                105
#define OP_Not               106
#define OP_Concat            107
#define OP_Noop              108

#define OP_Strlen            109
#define OP_Substr            110

#define OP_Limit             111

#define OP_MAX               111

/*
** Prototypes for the VDBE interface.  See comments on the implementation
** for a description of what each of these routines does.
*/
Vdbe *sqliteVdbeCreate(sqlite*);
void sqliteVdbeCreateCallback(Vdbe*, int*);
Changes to src/where.c.
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
**    May you share freely, never taking more than you give.
**
*************************************************************************
** This module contains C code that generates VDBE code used to process
** the WHERE clause of SQL statements.  Also found here are subroutines
** to generate VDBE code to evaluate expressions.
**
** $Id: where.c,v 1.28 2001/11/12 13:51:43 drh Exp $
*/
#include "sqliteInt.h"

/*
** The query generator uses an array of instances of this structure to
** help it analyze the subexpressions of the WHERE clause.  Each WHERE
** clause subexpression is separated from the others by an AND operator.







|







9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
**    May you share freely, never taking more than you give.
**
*************************************************************************
** This module contains C code that generates VDBE code used to process
** the WHERE clause of SQL statements.  Also found here are subroutines
** to generate VDBE code to evaluate expressions.
**
** $Id: where.c,v 1.29 2001/12/22 14:49:26 drh Exp $
*/
#include "sqliteInt.h"

/*
** The query generator uses an array of instances of this structure to
** help it analyze the subexpressions of the WHERE clause.  Each WHERE
** clause subexpression is separated from the others by an AND operator.
163
164
165
166
167
168
169



170
171
172
173
174
175
176
  int *aOrder;         /* Order in which pTabList entries are searched */
  int nExpr;           /* Number of subexpressions in the WHERE clause */
  int loopMask;        /* One bit set for each outer loop */
  int haveKey;         /* True if KEY is on the stack */
  int base;            /* First available index for OP_Open opcodes */
  int nCur;            /* Next unused cursor number */
  int aDirect[32];     /* If TRUE, then index this table using ROWID */



  ExprInfo aExpr[50];  /* The WHERE clause is divided into these expressions */

  /* Allocate space for aOrder[] and aiMem[]. */
  aOrder = sqliteMalloc( sizeof(int) * pTabList->nId );

  /* Allocate and initialize the WhereInfo structure that will become the
  ** return value.







>
>
>







163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
  int *aOrder;         /* Order in which pTabList entries are searched */
  int nExpr;           /* Number of subexpressions in the WHERE clause */
  int loopMask;        /* One bit set for each outer loop */
  int haveKey;         /* True if KEY is on the stack */
  int base;            /* First available index for OP_Open opcodes */
  int nCur;            /* Next unused cursor number */
  int aDirect[32];     /* If TRUE, then index this table using ROWID */
  int iDirectEq[32];   /* Term of the form ROWID==X for the N-th table */
  int iDirectLt[32];   /* Term of the form ROWID<X or ROWID<=X */
  int iDirectGt[32];   /* Term of the form ROWID>X or ROWID>=X */
  ExprInfo aExpr[50];  /* The WHERE clause is divided into these expressions */

  /* Allocate space for aOrder[] and aiMem[]. */
  aOrder = sqliteMalloc( sizeof(int) * pTabList->nId );

  /* Allocate and initialize the WhereInfo structure that will become the
  ** return value.
214
215
216
217
218
219
220
221
222





223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238


239
240


241
242
243

244

245



246
247
248

249

250


251
252

253
254
255
256
257
258
259
260
  for(i=0; i<pTabList->nId; i++){
    aOrder[i] = i;
  }

  /* Figure out what index to use (if any) for each nested loop.
  ** Make pWInfo->a[i].pIdx point to the index to use for the i-th nested
  ** loop where i==0 is the outer loop and i==pTabList->nId-1 is the inner
  ** loop.  If the expression uses only the ROWID field, then set
  ** aDirect[i] to 1.





  **
  ** Actually, if there are more than 32 tables in the join, only the
  ** first 32 tables are candidates for indices.
  */
  loopMask = 0;
  for(i=0; i<pTabList->nId && i<ARRAYSIZE(aDirect); i++){
    int j;
    int idx = aOrder[i];
    Table *pTab = pTabList->a[idx].pTab;
    Index *pIdx;
    Index *pBestIdx = 0;
    int bestScore = 0;

    /* Check to see if there is an expression that uses only the
    ** ROWID field of this table.  If so, set aDirect[i] to 1.
    ** If not, set aDirect[i] to 0.


    */
    aDirect[i] = 0;


    for(j=0; j<nExpr; j++){
      if( aExpr[j].idxLeft==idx && aExpr[j].p->pLeft->iColumn<0
            && (aExpr[j].prereqRight & loopMask)==aExpr[j].prereqRight ){

        aDirect[i] = 1;

        break;



      }
      if( aExpr[j].idxRight==idx && aExpr[j].p->pRight->iColumn<0
            && (aExpr[j].prereqLeft & loopMask)==aExpr[j].prereqLeft ){

        aDirect[i] = 1;

        break;


      }
    }

    if( aDirect[i] ){
      loopMask |= 1<<idx;
      pWInfo->a[i].pIdx = 0;
      continue;
    }

    /* Do a search for usable indices.  Leave pBestIdx pointing to
    ** the "best" index.  pBestIdx is left set to NULL if no indices







|
|
>
>
>
>
>














|
|
>
>

|
>
>



>
|
>
|
>
>
>



>
|
>
|
>
>
|
|
>
|







217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
  for(i=0; i<pTabList->nId; i++){
    aOrder[i] = i;
  }

  /* Figure out what index to use (if any) for each nested loop.
  ** Make pWInfo->a[i].pIdx point to the index to use for the i-th nested
  ** loop where i==0 is the outer loop and i==pTabList->nId-1 is the inner
  ** loop. 
  **
  ** If terms exist that use the ROWID of any table, then set the
  ** iDirectEq[], iDirectLt[], or iDirectGt[] elements for that table
  ** to the index of the term containing the ROWID.  We always prefer
  ** to use a ROWID which can directly access a table rather than an
  ** index which requires two accesses.
  **
  ** Actually, if there are more than 32 tables in the join, only the
  ** first 32 tables are candidates for indices.
  */
  loopMask = 0;
  for(i=0; i<pTabList->nId && i<ARRAYSIZE(aDirect); i++){
    int j;
    int idx = aOrder[i];
    Table *pTab = pTabList->a[idx].pTab;
    Index *pIdx;
    Index *pBestIdx = 0;
    int bestScore = 0;

    /* Check to see if there is an expression that uses only the
    ** ROWID field of this table.  For terms of the form ROWID==expr
    ** set iDirectEq[i] to the index of the term.  For terms of the
    ** form ROWID<expr or ROWID<=expr set iDirectLt[i] to the term index.
    ** For terms like ROWID>expr or ROWID>=expr set iDirectGt[i].
    */
    iDirectEq[i] = -1;
    iDirectLt[i] = -1;
    iDirectGt[i] = -1;
    for(j=0; j<nExpr; j++){
      if( aExpr[j].idxLeft==idx && aExpr[j].p->pLeft->iColumn<0
            && (aExpr[j].prereqRight & loopMask)==aExpr[j].prereqRight ){
        switch( aExpr[j].p->op ){
          case TK_EQ: iDirectEq[i] = j; break;
          case TK_LE:
          case TK_LT: iDirectLt[i] = j; break;
          case TK_GE:
          case TK_GT: iDirectGt[i] = j;  break;
        }
      }
      if( aExpr[j].idxRight==idx && aExpr[j].p->pRight->iColumn<0
            && (aExpr[j].prereqLeft & loopMask)==aExpr[j].prereqLeft ){
        switch( aExpr[j].p->op ){
          case TK_EQ: iDirectEq[i] = j;  break;
          case TK_LE:
          case TK_LT: iDirectGt[i] = j;  break;
          case TK_GE:
          case TK_GT: iDirectLt[i] = j;  break;
        }
      }
    }
    if( iDirectEq[i]>=0 ){
      loopMask |= 1<<idx;
      pWInfo->a[i].pIdx = 0;
      continue;
    }

    /* Do a search for usable indices.  Leave pBestIdx pointing to
    ** the "best" index.  pBestIdx is left set to NULL if no indices
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
429
430
431
432
433

434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
  /* Generate the code to do the search
  */
  loopMask = 0;
  pWInfo->iBreak = sqliteVdbeMakeLabel(v);
  for(i=0; i<pTabList->nId; i++){
    int j, k;
    int idx = aOrder[i];
    int goDirect;
    Index *pIdx;
    WhereLevel *pLevel = &pWInfo->a[i];

    if( i<ARRAYSIZE(aDirect) ){
      pIdx = pLevel->pIdx;
      goDirect = aDirect[i];
    }else{
      pIdx = 0;
      goDirect = 0;
    }

    if( goDirect ){
      /* Case 1:  We can directly reference a single row using the ROWID field.

      */

      for(k=0; k<nExpr; k++){
        if( aExpr[k].p==0 ) continue;

        if( aExpr[k].idxLeft==idx 
           && (aExpr[k].prereqRight & loopMask)==aExpr[k].prereqRight 
           && aExpr[k].p->pLeft->iColumn<0
        ){
          sqliteExprCode(pParse, aExpr[k].p->pRight);

          aExpr[k].p = 0;
          break;
        }
        if( aExpr[k].idxRight==idx 
           && (aExpr[k].prereqLeft & loopMask)==aExpr[k].prereqLeft
           && aExpr[k].p->pRight->iColumn<0
        ){
          sqliteExprCode(pParse, aExpr[k].p->pLeft);
          aExpr[k].p = 0;
          break;
        }
      }
      sqliteVdbeAddOp(v, OP_AddImm, 0, 0);
      brk = pLevel->brk = sqliteVdbeMakeLabel(v);
      cont = pLevel->cont = brk;

      if( i==pTabList->nId-1 && pushKey ){
        haveKey = 1;
      }else{
        sqliteVdbeAddOp(v, OP_NotFound, base+idx, brk);
        haveKey = 0;
      }
      pLevel->op = OP_Noop;
    }else if( pIdx==0 ){
      /* Case 2:  There was no usable index.  We must do a complete
      **          scan of the entire database table.
      */
      int start;

      brk = pLevel->brk = sqliteVdbeMakeLabel(v);
      cont = pLevel->cont = sqliteVdbeMakeLabel(v);
      sqliteVdbeAddOp(v, OP_Rewind, base+idx, brk);
      start = sqliteVdbeCurrentAddr(v);
      pLevel->op = OP_Next;
      pLevel->p1 = base+idx;
      pLevel->p2 = start;
      haveKey = 0;
    }else if( pLevel->score%4==0 ){
      /* Case 3:  All index constraints are equality operators.
      */
      int start;
      int testOp;
      int nColumn = pLevel->score/4;
      for(j=0; j<nColumn; j++){
        for(k=0; k<nExpr; k++){
          if( aExpr[k].p==0 ) continue;







<



<
|
<
<
<
<
<
|
<
|
>

>
|
|
>
|
<
<
<
|
>
|
<
|
<
<
<
<
<
|
<
<
<
<


>







|
<
<
<
<
<
<
<
<
<
<
<
<
<
<
|







412
413
414
415
416
417
418

419
420
421

422





423

424
425
426
427
428
429
430
431



432
433
434

435





436




437
438
439
440
441
442
443
444
445
446
447














448
449
450
451
452
453
454
455
  /* Generate the code to do the search
  */
  loopMask = 0;
  pWInfo->iBreak = sqliteVdbeMakeLabel(v);
  for(i=0; i<pTabList->nId; i++){
    int j, k;
    int idx = aOrder[i];

    Index *pIdx;
    WhereLevel *pLevel = &pWInfo->a[i];


    pIdx = pLevel->pIdx;





    if( i<ARRAYSIZE(iDirectEq) && iDirectEq[i]>=0 ){

      /* Case 1:  We can directly reference a single row using an
      **          equality comparison against the ROWID field.
      */
      k = iDirectEq[i];
      assert( k<nExpr );
      assert( aExpr[k].p!=0 );
      assert( aExpr[k].idxLeft==idx || aExpr[k].idxRight==idx );
      if( aExpr[k].idxLeft==idx ){



        sqliteExprCode(pParse, aExpr[k].p->pRight);
      }else{
        sqliteExprCode(pParse, aExpr[k].p->pLeft);

      }





      aExpr[k].p = 0;




      brk = pLevel->brk = sqliteVdbeMakeLabel(v);
      cont = pLevel->cont = brk;
      sqliteVdbeAddOp(v, OP_MustBeInt, 0, brk);
      if( i==pTabList->nId-1 && pushKey ){
        haveKey = 1;
      }else{
        sqliteVdbeAddOp(v, OP_NotFound, base+idx, brk);
        haveKey = 0;
      }
      pLevel->op = OP_Noop;
    }else if( pIdx!=0 && pLevel->score%4==0 ){














      /* Case 2:  All index constraints are equality operators.
      */
      int start;
      int testOp;
      int nColumn = pLevel->score/4;
      for(j=0; j<nColumn; j++){
        for(k=0; k<nExpr; k++){
          if( aExpr[k].p==0 ) continue;
503
504
505
506
507
508
509















510
























































511
512
513
514
515
516
517
518
      }else{
        sqliteVdbeAddOp(v, OP_MoveTo, base+idx, 0);
        haveKey = 0;
      }
      pLevel->op = OP_Next;
      pLevel->p1 = pLevel->iCur;
      pLevel->p2 = start;















    }else{
























































      /* Case 4: The contraints on the right-most index field are
      **         inequalities.
      */
      int score = pLevel->score;
      int nEqColumn = score/4;
      int start;
      int leFlag, geFlag;
      int testOp;







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







495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
      }else{
        sqliteVdbeAddOp(v, OP_MoveTo, base+idx, 0);
        haveKey = 0;
      }
      pLevel->op = OP_Next;
      pLevel->p1 = pLevel->iCur;
      pLevel->p2 = start;
    }else if( i<ARRAYSIZE(iDirectLt) && (iDirectLt[i]>=0 || iDirectGt[i]>=0) ){
      /* Case 3:  We have an inequality comparison against the ROWID field.
      */
      int testOp = OP_Noop;
      int start;

      brk = pLevel->brk = sqliteVdbeMakeLabel(v);
      cont = pLevel->cont = sqliteVdbeMakeLabel(v);
      if( iDirectGt[i]>=0 ){
        k = iDirectGt[i];
        assert( k<nExpr );
        assert( aExpr[k].p!=0 );
        assert( aExpr[k].idxLeft==idx || aExpr[k].idxRight==idx );
        if( aExpr[k].idxLeft==idx ){
          sqliteExprCode(pParse, aExpr[k].p->pRight);
        }else{
          sqliteExprCode(pParse, aExpr[k].p->pLeft);
        }
        sqliteVdbeAddOp(v, OP_MustBeInt, 0, brk);
        if( aExpr[k].p->op==TK_LT || aExpr[k].p->op==TK_GT ){
          sqliteVdbeAddOp(v, OP_AddImm, 1, 0);
        }
        sqliteVdbeAddOp(v, OP_MoveTo, base+idx, brk);
        aExpr[k].p = 0;
      }else{
        sqliteVdbeAddOp(v, OP_Rewind, base+idx, brk);
      }
      if( iDirectLt[i]>=0 ){
        k = iDirectLt[i];
        assert( k<nExpr );
        assert( aExpr[k].p!=0 );
        assert( aExpr[k].idxLeft==idx || aExpr[k].idxRight==idx );
        if( aExpr[k].idxLeft==idx ){
          sqliteExprCode(pParse, aExpr[k].p->pRight);
        }else{
          sqliteExprCode(pParse, aExpr[k].p->pLeft);
        }
        sqliteVdbeAddOp(v, OP_MustBeInt, 0, sqliteVdbeCurrentAddr(v)+1);
        pLevel->iMem = pParse->nMem++;
        sqliteVdbeAddOp(v, OP_MemStore, pLevel->iMem, 0);
        if( aExpr[k].p->op==TK_LT || aExpr[k].p->op==TK_GT ){
          testOp = OP_Ge;
        }else{
          testOp = OP_Gt;
        }
        aExpr[k].p = 0;
      }
      start = sqliteVdbeCurrentAddr(v);
      pLevel->op = OP_Next;
      pLevel->p1 = base+idx;
      pLevel->p2 = start;
      if( testOp!=OP_Noop ){
        sqliteVdbeAddOp(v, OP_Recno, base+idx, 0);
        sqliteVdbeAddOp(v, OP_MemLoad, pLevel->iMem, 0);
        sqliteVdbeAddOp(v, testOp, 0, brk);
      }
      haveKey = 0;
    }else if( pIdx==0 ){
      /* Case 4:  There was no usable index.  We must do a complete
      **          scan of the entire database table.
      */
      int start;

      brk = pLevel->brk = sqliteVdbeMakeLabel(v);
      cont = pLevel->cont = sqliteVdbeMakeLabel(v);
      sqliteVdbeAddOp(v, OP_Rewind, base+idx, brk);
      start = sqliteVdbeCurrentAddr(v);
      pLevel->op = OP_Next;
      pLevel->p1 = base+idx;
      pLevel->p2 = start;
      haveKey = 0;
    }else{
      /* Case 5: The contraints on the right-most index field are
      **         inequalities.
      */
      int score = pLevel->score;
      int nEqColumn = score/4;
      int start;
      int leFlag, geFlag;
      int testOp;
Changes to test/func.test.
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.
#
#***********************************************************************
# This file implements regression tests for SQLite library.  The
# focus of this file is testing built-in functions.
#
# $Id: func.test,v 1.6 2001/10/20 12:30:12 drh Exp $

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

# Create a table to work with.
#
do_test func-0.0 {













|







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.
#
#***********************************************************************
# This file implements regression tests for SQLite library.  The
# focus of this file is testing built-in functions.
#
# $Id: func.test,v 1.7 2001/12/22 14:49:26 drh Exp $

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

# Create a table to work with.
#
do_test func-0.0 {
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
  catchsql {SELECT abs(c) FROM t1 ORDER BY a}
} {0 {3 12345.6789 5}}

do_test func-4.5 {
  catchsql {SELECT round(a,b,c) FROM t1}
} {1 {too many arguments to function round()}}
do_test func-4.6 {
  catchsql {SELECT round(b,2) FROM t1}
} {0 {2.00 1.23 -2.00}}
do_test func-4.7 {
  catchsql {SELECT round(b,0) FROM t1 ORDER BY a}
} {0 {2 1 -2}}
do_test func-4.8 {
  catchsql {SELECT round(c) FROM t1 ORDER BY a}
} {0 {3 -12346 -5}}
do_test func-4.9 {







|
|







142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
  catchsql {SELECT abs(c) FROM t1 ORDER BY a}
} {0 {3 12345.6789 5}}

do_test func-4.5 {
  catchsql {SELECT round(a,b,c) FROM t1}
} {1 {too many arguments to function round()}}
do_test func-4.6 {
  catchsql {SELECT round(b,2) FROM t1 ORDER BY b}
} {0 {-2.00 1.23 2.00}}
do_test func-4.7 {
  catchsql {SELECT round(b,0) FROM t1 ORDER BY a}
} {0 {2 1 -2}}
do_test func-4.8 {
  catchsql {SELECT round(c) FROM t1 ORDER BY a}
} {0 {3 -12346 -5}}
do_test func-4.9 {
Changes to test/intpkey.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 for the special processing associated
# with INTEGER PRIMARY KEY columns.
#
# $Id: intpkey.test,v 1.1 2001/12/21 14:30:44 drh Exp $

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

# Create a table with a primary key and a datatype other than
# integer
#







|







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 for the special processing associated
# with INTEGER PRIMARY KEY columns.
#
# $Id: intpkey.test,v 1.2 2001/12/22 14:49:26 drh Exp $

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

# Create a table with a primary key and a datatype other than
# integer
#
116
117
118
119
120
121
122
123


124



















































































































































































































































125
# as an index.
#
do_test intpkey-1.12 {
  execsql {
    SELECT * FROM t1 WHERE a==7;
  }
} {7 one two}























































































































































































































































finish_test








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

116
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
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
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
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
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
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
# as an index.
#
do_test intpkey-1.12 {
  execsql {
    SELECT * FROM t1 WHERE a==7;
  }
} {7 one two}

# Try to insert a non-integer value into the primary key field.  This
# should result in a data type mismatch.
#
do_test intpkey-1.13 {
  set r [catch {execsql {
    INSERT INTO t1 VALUES('x','y','z');
  }} msg]
  lappend r $msg
} {1 {datatype mismatch}}
do_test intpkey-1.14 {
  set r [catch {execsql {
    INSERT INTO t1 VALUES(3.4,'y','z');
  }} msg]
  lappend r $msg
} {1 {datatype mismatch}}
do_test intpkey-1.15 {
  set r [catch {execsql {
    INSERT INTO t1 VALUES(-3,'y','z');
  }} msg]
  lappend r $msg
} {0 {}}
do_test intpkey-1.16 {
  execsql {SELECT * FROM t1}
} {-3 y z 5 hello world 6 second entry 7 one two}

#### INDICES
# Check to make sure indices work correctly with integer primary keys
#
do_test intpkey-2.1 {
  execsql {
    CREATE INDEX i1 ON t1(b);
    SELECT * FROM t1 WHERE b=='y'
  }
} {-3 y z}
do_test intpkey-2.1.1 {
  execsql {
    SELECT * FROM t1 WHERE b=='y' AND rowid<0
  }
} {-3 y z}
do_test intpkey-2.1.2 {
  execsql {
    SELECT * FROM t1 WHERE b=='y' AND rowid<0 AND rowid>=-20
  }
} {-3 y z}
do_test intpkey-2.1.3 {
  execsql {
    SELECT * FROM t1 WHERE b>='y'
  }
} {-3 y z}
do_test intpkey-2.1.4 {
  execsql {
    SELECT * FROM t1 WHERE b>='y' AND rowid<10
  }
} {-3 y z}
do_test intpkey-2.2 {
  execsql {
    UPDATE t1 SET a=8 WHERE b=='y';
    SELECT * FROM t1 WHERE b=='y';
  }
} {8 y z}
do_test intpkey-2.3 {
  execsql {
    SELECT rowid, * FROM t1;
  }
} {5 5 hello world 6 6 second entry 7 7 one two 8 8 y z}
do_test intpkey-2.4 {
  execsql {
    SELECT rowid, * FROM t1 WHERE b<'second'
  }
} {5 5 hello world 7 7 one two}
do_test intpkey-2.4.1 {
  execsql {
    SELECT rowid, * FROM t1 WHERE 'second'>b
  }
} {5 5 hello world 7 7 one two}
do_test intpkey-2.4.2 {
  execsql {
    SELECT rowid, * FROM t1 WHERE 8>rowid AND 'second'>b
  }
} {5 5 hello world 7 7 one two}
do_test intpkey-2.4.3 {
  execsql {
    SELECT rowid, * FROM t1 WHERE 8>rowid AND 'second'>b AND 0<rowid
  }
} {5 5 hello world 7 7 one two}
do_test intpkey-2.5 {
  execsql {
    SELECT rowid, * FROM t1 WHERE b>'a'
  }
} {5 5 hello world 7 7 one two 6 6 second entry 8 8 y z}
do_test intpkey-2.6 {
  execsql {
    DELETE FROM t1 WHERE rowid=7;
    SELECT * FROM t1 WHERE b>'a';
  }
} {5 hello world 6 second entry 8 y z}
do_test intpkey-2.7 {
  execsql {
    UPDATE t1 SET a=-4 WHERE rowid=8;
    SELECT * FROM t1 WHERE b>'a';
  }
} {5 hello world 6 second entry -4 y z}
do_test intpkey-2.7 {
  execsql {
    SELECT * FROM t1
  }
} {-4 y z 5 hello world 6 second entry}

# Do an SQL statement.  Append the search count to the end of the result.
#
proc count sql {
  set ::sqlite_search_count 0
  return [concat [execsql $sql] $::sqlite_search_count]
}

# Create indices that include the integer primary key as one of their
# columns.
#
do_test intpkey-3.1 {
  execsql {
    CREATE INDEX i2 ON t1(a);
  }
} {}
do_test intpkey-3.2 {
  count {
    SELECT * FROM t1 WHERE a=5;
  }
} {5 hello world 0}
do_test intpkey-3.3 {
  count {
    SELECT * FROM t1 WHERE a>4 AND a<6;
  }
} {5 hello world 2}
do_test intpkey-3.4 {
  count {
    SELECT * FROM t1 WHERE b>='hello' AND b<'hello2';
  }
} {5 hello world 3}
do_test intpkey-3.5 {
  execsql {
    CREATE INDEX i3 ON t1(c,a);
  }
} {}
do_test intpkey-3.6 {
  count {
    SELECT * FROM t1 WHERE c=='world';
  }
} {5 hello world 3}
do_test intpkey-3.7 {
  execsql {INSERT INTO t1 VALUES(11,'hello','world')}
  count {
    SELECT * FROM t1 WHERE c=='world';
  }
} {5 hello world 11 hello world 5}
do_test intpkey-3.8 {
  count {
    SELECT * FROM t1 WHERE c=='world' AND a>7;
  }
} {11 hello world 5}
do_test intpkey-3.9 {
  count {
    SELECT * FROM t1 WHERE 7<a;
  }
} {11 hello world 1}

# Test inequality constraints on integer primary keys and rowids
#
do_test intpkey-4.1 {
  count {
    SELECT * FROM t1 WHERE 11=rowid
  }
} {11 hello world 0}
do_test intpkey-4.2 {
  count {
    SELECT * FROM t1 WHERE 11=rowid AND b=='hello'
  }
} {11 hello world 0}
do_test intpkey-4.3 {
  count {
    SELECT * FROM t1 WHERE 11=rowid AND b=='hello' AND c IS NOT NULL;
  }
} {11 hello world 0}
do_test intpkey-4.4 {
  count {
    SELECT * FROM t1 WHERE rowid==11
  }
} {11 hello world 0}
do_test intpkey-4.5 {
  count {
    SELECT * FROM t1 WHERE oid==11 AND b=='hello'
  }
} {11 hello world 0}
do_test intpkey-4.6 {
  count {
    SELECT * FROM t1 WHERE a==11 AND b=='hello' AND c IS NOT NULL;
  }
} {11 hello world 0}

do_test intpkey-4.7 {
  count {
    SELECT * FROM t1 WHERE 8<rowid;
  }
} {11 hello world 1}
do_test intpkey-4.8 {
  count {
    SELECT * FROM t1 WHERE 8<rowid AND 11>=oid;
  }
} {11 hello world 1}
do_test intpkey-4.9 {
  count {
    SELECT * FROM t1 WHERE 11<=_rowid_ AND 12>=a;
  }
} {11 hello world 1}
do_test intpkey-4.10 {
  count {
    SELECT * FROM t1 WHERE 0>=_rowid_;
  }
} {-4 y z 1}
do_test intpkey-4.11 {
  count {
    SELECT * FROM t1 WHERE a<0;
  }
} {-4 y z 1}
do_test intpkey-4.12 {
  count {
    SELECT * FROM t1 WHERE a<0 AND a>10;
  }
} {1}

# Make sure it is OK to insert a rowid of 0
#
do_test intpkey-5.1 {
  execsql {
    INSERT INTO t1 VALUES(0,'zero','entry');
  }
  count {
    SELECT * FROM t1 WHERE a=0;
  }
} {0 zero entry 0}
do_test intpkey=5.2 {
  execsql {
    SELECT rowid, a FROM t1
  }
} {-4 -4 0 0 5 5 6 6 11 11}


finish_test
Changes to test/where.test.
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.
#
#***********************************************************************
# This file implements regression tests for SQLite library.  The
# focus of this file is testing the use of indices in WHERE clases.
#
# $Id: where.test,v 1.4 2001/11/08 00:45:22 drh Exp $

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

# Build some test data
#
do_test where-1.0 {













|







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.
#
#***********************************************************************
# This file implements regression tests for SQLite library.  The
# focus of this file is testing the use of indices in WHERE clases.
#
# $Id: where.test,v 1.5 2001/12/22 14:49:26 drh Exp $

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

# Build some test data
#
do_test where-1.0 {
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
do_test where-1.35 {
  count {SELECT w FROM t1 WHERE w<3}
} {1 2 4}
do_test where-1.36 {
  count {SELECT w FROM t1 WHERE w<=3}
} {1 2 3 6}
do_test where-1.37 {
  count {SELECT w FROM t1 WHERE w+1<=4}
} {1 2 3 99}


# Do the same kind of thing except use a join as the data source.
#
do_test where-2.1 {
  count {







|







163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
do_test where-1.35 {
  count {SELECT w FROM t1 WHERE w<3}
} {1 2 4}
do_test where-1.36 {
  count {SELECT w FROM t1 WHERE w<=3}
} {1 2 3 6}
do_test where-1.37 {
  count {SELECT w FROM t1 WHERE w+1<=4 ORDER BY w}
} {1 2 3 99}


# Do the same kind of thing except use a join as the data source.
#
do_test where-2.1 {
  count {