/ Check-in [1686196a]
Login

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

Overview
Comment:New ROWIDs are numbered sequentially. (CVS 383)
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 1686196a8aea326f616bc8205df99cd84d955ec4
User & Date: drh 2002-02-19 22:42:05
Context
2002-02-19
22:43
Fix a comment. (CVS 384) check-in: 95392853 user: drh tags: trunk
22:42
New ROWIDs are numbered sequentially. (CVS 383) check-in: 1686196a user: drh tags: trunk
15:00
Optimize simple min() and max() queries. (CVS 382) check-in: cc5abfe3 user: drh tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/main.c.

    10     10   **
    11     11   *************************************************************************
    12     12   ** Main file for the SQLite library.  The routines in this file
    13     13   ** implement the programmer interface to the library.  Routines in
    14     14   ** other files are for internal use by SQLite and should not be
    15     15   ** accessed by users of the library.
    16     16   **
    17         -** $Id: main.c,v 1.59 2002/02/18 18:30:33 drh Exp $
           17  +** $Id: main.c,v 1.60 2002/02/19 22:42:05 drh Exp $
    18     18   */
    19     19   #include "sqliteInt.h"
    20     20   #include "os.h"
    21     21   
    22     22   /*
    23     23   ** This is the callback routine for the code that initializes the
    24     24   ** database.  See sqliteInit() below for additional information.
................................................................................
   271    271     db = sqliteMalloc( sizeof(sqlite) );
   272    272     if( pzErrMsg ) *pzErrMsg = 0;
   273    273     if( db==0 ) goto no_mem_on_open;
   274    274     sqliteHashInit(&db->tblHash, SQLITE_HASH_STRING, 0);
   275    275     sqliteHashInit(&db->idxHash, SQLITE_HASH_STRING, 0);
   276    276     sqliteHashInit(&db->tblDrop, SQLITE_HASH_POINTER, 0);
   277    277     sqliteHashInit(&db->idxDrop, SQLITE_HASH_POINTER, 0);
   278         -  db->nextRowid = sqliteRandomInteger();
   279    278     db->onError = OE_Default;
          279  +  db->priorNewRowid = 0;
   280    280     
   281    281     /* Open the backend database driver */
   282    282     rc = sqliteBtreeOpen(zFilename, mode, MAX_PAGES, &db->pBe);
   283    283     if( rc!=SQLITE_OK ){
   284    284       switch( rc ){
   285    285         default: {
   286    286           sqliteSetString(pzErrMsg, "unable to open database: ", zFilename, 0);

Changes to src/select.c.

     8      8   **    May you find forgiveness for yourself and forgive others.
     9      9   **    May you share freely, never taking more than you give.
    10     10   **
    11     11   *************************************************************************
    12     12   ** This file contains C code routines that are called by the parser
    13     13   ** to handle SELECT statements in SQLite.
    14     14   **
    15         -** $Id: select.c,v 1.62 2002/02/19 15:00:08 drh Exp $
           15  +** $Id: select.c,v 1.63 2002/02/19 22:42:05 drh Exp $
    16     16   */
    17     17   #include "sqliteInt.h"
    18     18   
    19     19   /*
    20     20   ** Allocate a new Select structure and return a pointer to that
    21     21   ** structure.
    22     22   */
................................................................................
   779    779     if( v==0 ) return 0;
   780    780     if( eDest==SRT_Callback ){
   781    781       generateColumnNames(pParse, p->pSrc, p->pEList);
   782    782     }
   783    783   
   784    784     /* Begin generating code
   785    785     */
   786         -  base = pParse->nTab;
   787         -  eList.nExpr = 1;
   788         -  memset(&eListItem, 0, sizeof(eListItem));
   789         -  eList.a = &eListItem;
   790         -  eList.a[0].pExpr = pExpr;
          786  +  if( !pParse->schemaVerified && (pParse->db->flags & SQLITE_InTrans)==0 ){
          787  +    sqliteVdbeAddOp(v, OP_VerifyCookie, pParse->db->schema_cookie, 0);
          788  +    pParse->schemaVerified = 1;
          789  +  }
   791    790     openOp = pTab->isTemp ? OP_OpenAux : OP_Open;
          791  +  base = pParse->nTab;
   792    792     sqliteVdbeAddOp(v, openOp, base, pTab->tnum);
          793  +  sqliteVdbeChangeP3(v, -1, pTab->zName, P3_STATIC);
   793    794     if( pIdx==0 ){
   794    795       sqliteVdbeAddOp(v, seekOp, base, 0);
   795    796     }else{
   796    797       sqliteVdbeAddOp(v, openOp, base+1, pIdx->tnum);
          798  +    sqliteVdbeChangeP3(v, -1, pIdx->zName, P3_STATIC);
   797    799       sqliteVdbeAddOp(v, seekOp, base+1, 0);
   798    800       sqliteVdbeAddOp(v, OP_IdxRecno, base+1, 0);
   799    801       sqliteVdbeAddOp(v, OP_Close, base+1, 0);
   800    802       sqliteVdbeAddOp(v, OP_MoveTo, base, 0);
   801    803     }
          804  +  eList.nExpr = 1;
          805  +  memset(&eListItem, 0, sizeof(eListItem));
          806  +  eList.a = &eListItem;
          807  +  eList.a[0].pExpr = pExpr;
   802    808     cont = sqliteVdbeMakeLabel(v);
   803    809     selectInnerLoop(pParse, &eList, base, 1, 0, -1, eDest, iParm, cont, cont);
   804    810     sqliteVdbeResolveLabel(v, cont);
   805    811     sqliteVdbeAddOp(v, OP_Close, base, 0);
   806    812     return 1;
   807    813   }
   808    814   
................................................................................
  1007   1013       }
  1008   1014     }
  1009   1015   
  1010   1016     /* Check for the special case of a min() or max() function by itself
  1011   1017     ** in the result set.
  1012   1018     */
  1013   1019     if( simpleMinMaxQuery(pParse, p, eDest, iParm) ){
         1020  +    rc = 0;
  1014   1021       goto select_end;
  1015   1022     }
  1016   1023   
  1017   1024     /* Begin generating code.
  1018   1025     */
  1019   1026     v = sqliteGetVdbe(pParse);
  1020   1027     if( v==0 ) goto select_end;

Changes to src/sqliteInt.h.

     7      7   **    May you do good and not evil.
     8      8   **    May you find forgiveness for yourself and forgive others.
     9      9   **    May you share freely, never taking more than you give.
    10     10   **
    11     11   *************************************************************************
    12     12   ** Internal interface definitions for SQLite.
    13     13   **
    14         -** @(#) $Id: sqliteInt.h,v 1.87 2002/02/18 18:30:33 drh Exp $
           14  +** @(#) $Id: sqliteInt.h,v 1.88 2002/02/19 22:42:05 drh Exp $
    15     15   */
    16     16   #include "sqlite.h"
    17     17   #include "hash.h"
    18     18   #include "vdbe.h"
    19     19   #include "parse.h"
    20     20   #include "btree.h"
    21     21   #include <stdio.h>
................................................................................
   178    178     void *pBusyArg;               /* 1st Argument to the busy callback */
   179    179     int (*xBusyCallback)(void *,const char*,int);  /* The busy callback */
   180    180     Hash tblHash;                 /* All tables indexed by name */
   181    181     Hash idxHash;                 /* All (named) indices indexed by name */
   182    182     Hash tblDrop;                 /* Uncommitted DROP TABLEs */
   183    183     Hash idxDrop;                 /* Uncommitted DROP INDEXs */
   184    184     int lastRowid;                /* ROWID of most recent insert */
   185         -  int nextRowid;                /* Next generated rowID */
          185  +  int priorNewRowid;            /* Last randomly generated ROWID */
   186    186     int onError;                  /* Default conflict algorithm */
   187    187   };
   188    188   
   189    189   /*
   190    190   ** Possible values for the sqlite.flags.
   191    191   */
   192    192   #define SQLITE_VdbeTrace      0x00000001  /* True to trace VDBE execution */

Changes to src/vdbe.c.

    26     26   ** type to the other occurs as necessary.
    27     27   ** 
    28     28   ** Most of the code in this file is taken up by the sqliteVdbeExec()
    29     29   ** function which does the work of interpreting a VDBE program.
    30     30   ** But other routines are also provided to help in building up
    31     31   ** a program instruction by instruction.
    32     32   **
    33         -** $Id: vdbe.c,v 1.119 2002/02/19 15:00:08 drh Exp $
           33  +** $Id: vdbe.c,v 1.120 2002/02/19 22:42:05 drh Exp $
    34     34   */
    35     35   #include "sqliteInt.h"
    36     36   #include <ctype.h>
    37     37   
    38     38   /*
    39     39   ** The following global variable is incremented every time a cursor
    40     40   ** moves, either by the OP_MoveTo or the OP_Next opcode.  The test
................................................................................
    67     67   */
    68     68   struct Cursor {
    69     69     BtCursor *pCursor;    /* The cursor structure of the backend */
    70     70     int lastRecno;        /* Last recno from a Next or NextIdx operation */
    71     71     Bool recnoIsValid;    /* True if lastRecno is valid */
    72     72     Bool keyAsData;       /* The OP_Column command works on key instead of data */
    73     73     Bool atFirst;         /* True if pointing to first entry */
           74  +  Bool useRandomRowid;  /* Generate new record numbers semi-randomly */
    74     75     Btree *pBt;           /* Separate file holding temporary table */
    75     76   };
    76     77   typedef struct Cursor Cursor;
    77     78   
    78     79   /*
    79     80   ** A sorter builds a list of elements to be sorted.  Each element of
    80     81   ** the list is an instance of the following structure.
................................................................................
  2909   2910   case OP_NewRecno: {
  2910   2911     int i = pOp->p1;
  2911   2912     int v = 0;
  2912   2913     Cursor *pC;
  2913   2914     if( VERIFY( i<0 || i>=p->nCursor || ) (pC = &p->aCsr[i])->pCursor==0 ){
  2914   2915       v = 0;
  2915   2916     }else{
  2916         -    /* A probablistic algorithm is used to locate an unused rowid.
  2917         -    ** We select a rowid at random and see if it exists in the table.
  2918         -    ** If it does not exist, we have succeeded.  If the random rowid
  2919         -    ** does exist, we select a new one and try again, up to 1000 times.
         2917  +    /* The next rowid or record number (different terms for the same
         2918  +    ** thing) is obtained in a two-step algorithm.
         2919  +    **
         2920  +    ** First we attempt to find the largest existing rowid and add one
         2921  +    ** to that.  But if the largest existing rowid is already the maximum
         2922  +    ** positive integer, we have to fall through to the second
         2923  +    ** probabilistic algorithm
         2924  +    **
         2925  +    ** The second algorithm is to select a rowid at random and see if
         2926  +    ** it already exists in the table.  If it does not exist, we have
         2927  +    ** succeeded.  If the random rowid does exist, we select a new one
         2928  +    ** and try again, up to 1000 times.
  2920   2929       **
  2921   2930       ** For a table with less than 2 billion entries, the probability
  2922   2931       ** of not finding a unused rowid is about 1.0e-300.  This is a 
  2923   2932       ** non-zero probability, but it is still vanishingly small and should
  2924   2933       ** never cause a problem.  You are much, much more likely to have a
  2925   2934       ** hardware failure than for this algorithm to fail.
  2926   2935       **
................................................................................
  2928   2937       ** source of random numbers.  Is a library function like lrand48()
  2929   2938       ** good enough?  Maybe. Maybe not. It's hard to know whether there
  2930   2939       ** might be subtle bugs is some implementations of lrand48() that
  2931   2940       ** could cause problems. To avoid uncertainty, SQLite uses its own 
  2932   2941       ** random number generator based on the RC4 algorithm.
  2933   2942       **
  2934   2943       ** To promote locality of reference for repetitive inserts, the
  2935         -    ** first few attempts at chosing a rowid pick values just a little
         2944  +    ** first few attempts at chosing a random rowid pick values just a little
  2936   2945       ** larger than the previous rowid.  This has been shown experimentally
  2937   2946       ** to double the speed of the COPY operation.
  2938   2947       */
  2939   2948       int res, rx, cnt, x;
  2940   2949       cnt = 0;
  2941         -    v = db->nextRowid;
  2942         -    do{
  2943         -      if( cnt>5 ){
  2944         -        v = sqliteRandomInteger();
         2950  +    if( !pC->useRandomRowid ){
         2951  +      rx = sqliteBtreeLast(pC->pCursor, &res);
         2952  +      if( res ){
         2953  +        v = 1;
  2945   2954         }else{
  2946         -        v += sqliteRandomByte() + 1;
         2955  +        sqliteBtreeKey(pC->pCursor, 0, sizeof(v), (void*)&v);
         2956  +        v = keyToInt(v);
         2957  +        if( v==0x7fffffff ){
         2958  +          pC->useRandomRowid = 1;
         2959  +        }else{
         2960  +          v++;
         2961  +        }
  2947   2962         }
  2948         -      if( v==0 ) continue;
  2949         -      x = intToKey(v);
  2950         -      rx = sqliteBtreeMoveto(pC->pCursor, &x, sizeof(int), &res);
  2951         -      cnt++;
  2952         -    }while( cnt<1000 && rx==SQLITE_OK && res==0 );
  2953         -    db->nextRowid = v;
  2954         -    if( rx==SQLITE_OK && res==0 ){
  2955         -      rc = SQLITE_FULL;
  2956         -      goto abort_due_to_error;
         2963  +    }
         2964  +    if( pC->useRandomRowid ){
         2965  +      v = db->priorNewRowid;
         2966  +      cnt = 0;
         2967  +      do{
         2968  +        if( v==0 || cnt>2 ){
         2969  +          v = sqliteRandomInteger();
         2970  +          if( cnt<5 ) v &= 0xffffff;
         2971  +        }else{
         2972  +          v += sqliteRandomByte() + 1;
         2973  +        }
         2974  +        if( v==0 ) continue;
         2975  +        x = intToKey(v);
         2976  +        rx = sqliteBtreeMoveto(pC->pCursor, &x, sizeof(int), &res);
         2977  +        cnt++;
         2978  +      }while( cnt<1000 && rx==SQLITE_OK && res==0 );
         2979  +      db->priorNewRowid = v;
         2980  +      if( rx==SQLITE_OK && res==0 ){
         2981  +        rc = SQLITE_FULL;
         2982  +        goto abort_due_to_error;
         2983  +      }
  2957   2984       }
  2958   2985     }
  2959   2986     VERIFY( NeedStack(p, p->tos+1); )
  2960   2987     p->tos++;
  2961   2988     aStack[p->tos].i = v;
  2962   2989     aStack[p->tos].flags = STK_Int;
  2963   2990     break;

Changes to test/intpkey.test.

     9      9   #
    10     10   #***********************************************************************
    11     11   # This file implements regression tests for SQLite library.
    12     12   #
    13     13   # This file implements tests for the special processing associated
    14     14   # with INTEGER PRIMARY KEY columns.
    15     15   #
    16         -# $Id: intpkey.test,v 1.7 2002/01/29 23:07:02 drh Exp $
           16  +# $Id: intpkey.test,v 1.8 2002/02/19 22:42:06 drh Exp $
    17     17   
    18     18   set testdir [file dirname $argv0]
    19     19   source $testdir/tester.tcl
    20     20   
    21     21   # Create a table with a primary key and a datatype other than
    22     22   # integer
    23     23   #
................................................................................
   107    107     }
   108    108   } {hello one second}
   109    109   
   110    110   # Try to change the ROWID for the new entry.
   111    111   #
   112    112   do_test intpkey-1.11 {
   113    113     execsql {
   114         -    UPDATE t1 SET a=7 WHERE b='one';
          114  +    UPDATE t1 SET a=4 WHERE b='one';
   115    115       SELECT * FROM t1;
   116    116     }
   117         -} {5 hello world 6 second entry 7 one two}
          117  +} {4 one two 5 hello world 6 second entry}
   118    118   
   119    119   # Make sure SELECT statements are able to use the primary key column
   120    120   # as an index.
   121    121   #
   122    122   do_test intpkey-1.12 {
   123    123     execsql {
   124         -    SELECT * FROM t1 WHERE a==7;
          124  +    SELECT * FROM t1 WHERE a==4;
   125    125     }
   126         -} {7 one two}
          126  +} {4 one two}
   127    127   
   128    128   # Try to insert a non-integer value into the primary key field.  This
   129    129   # should result in a data type mismatch.
   130    130   #
   131    131   do_test intpkey-1.13 {
   132    132     set r [catch {execsql {
   133    133       INSERT INTO t1 VALUES('x','y','z');
................................................................................
   144    144     set r [catch {execsql {
   145    145       INSERT INTO t1 VALUES(-3,'y','z');
   146    146     }} msg]
   147    147     lappend r $msg
   148    148   } {0 {}}
   149    149   do_test intpkey-1.16 {
   150    150     execsql {SELECT * FROM t1}
   151         -} {-3 y z 5 hello world 6 second entry 7 one two}
          151  +} {-3 y z 4 one two 5 hello world 6 second entry}
   152    152   
   153    153   #### INDICES
   154    154   # Check to make sure indices work correctly with integer primary keys
   155    155   #
   156    156   do_test intpkey-2.1 {
   157    157     execsql {
   158    158       CREATE INDEX i1 ON t1(b);
................................................................................
   186    186       SELECT * FROM t1 WHERE b=='y';
   187    187     }
   188    188   } {8 y z}
   189    189   do_test intpkey-2.3 {
   190    190     execsql {
   191    191       SELECT rowid, * FROM t1;
   192    192     }
   193         -} {5 5 hello world 6 6 second entry 7 7 one two 8 8 y z}
          193  +} {4 4 one two 5 5 hello world 6 6 second entry 8 8 y z}
   194    194   do_test intpkey-2.4 {
   195    195     execsql {
   196    196       SELECT rowid, * FROM t1 WHERE b<'second'
   197    197     }
   198         -} {5 5 hello world 7 7 one two}
          198  +} {5 5 hello world 4 4 one two}
   199    199   do_test intpkey-2.4.1 {
   200    200     execsql {
   201    201       SELECT rowid, * FROM t1 WHERE 'second'>b
   202    202     }
   203         -} {5 5 hello world 7 7 one two}
          203  +} {5 5 hello world 4 4 one two}
   204    204   do_test intpkey-2.4.2 {
   205    205     execsql {
   206    206       SELECT rowid, * FROM t1 WHERE 8>rowid AND 'second'>b
   207    207     }
   208         -} {5 5 hello world 7 7 one two}
          208  +} {4 4 one two 5 5 hello world}
   209    209   do_test intpkey-2.4.3 {
   210    210     execsql {
   211    211       SELECT rowid, * FROM t1 WHERE 8>rowid AND 'second'>b AND 0<rowid
   212    212     }
   213         -} {5 5 hello world 7 7 one two}
          213  +} {4 4 one two 5 5 hello world}
   214    214   do_test intpkey-2.5 {
   215    215     execsql {
   216    216       SELECT rowid, * FROM t1 WHERE b>'a'
   217    217     }
   218         -} {5 5 hello world 7 7 one two 6 6 second entry 8 8 y z}
          218  +} {5 5 hello world 4 4 one two 6 6 second entry 8 8 y z}
   219    219   do_test intpkey-2.6 {
   220    220     execsql {
   221         -    DELETE FROM t1 WHERE rowid=7;
          221  +    DELETE FROM t1 WHERE rowid=4;
   222    222       SELECT * FROM t1 WHERE b>'a';
   223    223     }
   224    224   } {5 hello world 6 second entry 8 y z}
   225    225   do_test intpkey-2.7 {
   226    226     execsql {
   227    227       UPDATE t1 SET a=-4 WHERE rowid=8;
   228    228       SELECT * FROM t1 WHERE b>'a';

Added test/minmax.test.

            1  +# 2001 September 15
            2  +#
            3  +# The author disclaims copyright to this source code.  In place of
            4  +# a legal notice, here is a blessing:
            5  +#
            6  +#    May you do good and not evil.
            7  +#    May you find forgiveness for yourself and forgive others.
            8  +#    May you share freely, never taking more than you give.
            9  +#
           10  +#***********************************************************************
           11  +# This file implements regression tests for SQLite library.  The
           12  +# focus of this file is testing SELECT statements that contain
           13  +# aggregate min() and max() functions and which are handled as
           14  +# as a special case.
           15  +#
           16  +# $Id: minmax.test,v 1.1 2002/02/19 22:42:06 drh Exp $
           17  +
           18  +set testdir [file dirname $argv0]
           19  +source $testdir/tester.tcl
           20  +
           21  +do_test minmax-1.0 {
           22  +  execsql {
           23  +    BEGIN;
           24  +    CREATE TABLE t1(x, y);
           25  +    INSERT INTO t1 VALUES(1,1);
           26  +    INSERT INTO t1 VALUES(2,2);
           27  +    INSERT INTO t1 VALUES(3,2);
           28  +    INSERT INTO t1 VALUES(4,3);
           29  +    INSERT INTO t1 VALUES(5,3);
           30  +    INSERT INTO t1 VALUES(6,3);
           31  +    INSERT INTO t1 VALUES(7,3);
           32  +    INSERT INTO t1 VALUES(8,4);
           33  +    INSERT INTO t1 VALUES(9,4);
           34  +    INSERT INTO t1 VALUES(10,4);
           35  +    INSERT INTO t1 VALUES(11,4);
           36  +    INSERT INTO t1 VALUES(12,4);
           37  +    INSERT INTO t1 VALUES(13,4);
           38  +    INSERT INTO t1 VALUES(14,4);
           39  +    INSERT INTO t1 VALUES(15,4);
           40  +    INSERT INTO t1 VALUES(16,5);
           41  +    INSERT INTO t1 VALUES(17,5);
           42  +    INSERT INTO t1 VALUES(18,5);
           43  +    INSERT INTO t1 VALUES(19,5);
           44  +    INSERT INTO t1 VALUES(20,5);
           45  +    COMMIT;
           46  +    SELECT DISTINCT y FROM t1 ORDER BY y;
           47  +  }
           48  +} {1 2 3 4 5}
           49  +
           50  +do_test minmax-1.1 {
           51  +  set sqlite_search_count 0
           52  +  execsql {SELECT min(x) FROM t1}
           53  +} {1}
           54  +do_test minmax-1.2 {
           55  +  set sqlite_search_count
           56  +} {19}
           57  +do_test minmax-1.3 {
           58  +  set sqlite_search_count 0
           59  +  execsql {SELECT max(x) FROM t1}
           60  +} {20}
           61  +do_test minmax-1.4 {
           62  +  set sqlite_search_count
           63  +} {19}
           64  +do_test minmax-1.5 {
           65  +  execsql {CREATE INDEX t1i1 ON t1(x)}
           66  +  set sqlite_search_count 0
           67  +  execsql {SELECT min(x) FROM t1}
           68  +} {1}
           69  +do_test minmax-1.6 {
           70  +  set sqlite_search_count
           71  +} {1}
           72  +do_test minmax-1.7 {
           73  +  set sqlite_search_count 0
           74  +  execsql {SELECT max(x) FROM t1}
           75  +} {20}
           76  +do_test minmax-1.8 {
           77  +  set sqlite_search_count
           78  +} {1}
           79  +do_test minmax-1.9 {
           80  +  set sqlite_search_count 0
           81  +  execsql {SELECT max(y) FROM t1}
           82  +} {5}
           83  +do_test minmax-1.10 {
           84  +  set sqlite_search_count
           85  +} {19}
           86  +
           87  +do_test minmax-2.0 {
           88  +  execsql {
           89  +    CREATE TABLE t2(a INTEGER PRIMARY KEY, b);
           90  +    INSERT INTO t2 SELECT * FROM t1;
           91  +  }
           92  +  set sqlite_search_count 0
           93  +  execsql {SELECT min(a) FROM t2}
           94  +} {1}
           95  +do_test minmax-2.1 {
           96  +  set sqlite_search_count
           97  +} {0}
           98  +do_test minmax-2.2 {
           99  +  set sqlite_search_count 0
          100  +  execsql {SELECT max(a) FROM t2}
          101  +} {20}
          102  +do_test minmax-2.3 {
          103  +  set sqlite_search_count
          104  +} {0}
          105  +
          106  +do_test minmax-3.0 {
          107  +  execsql {INSERT INTO t2 VALUES((SELECT max(a) FROM t2)+1,999)}
          108  +  set sqlite_search_count 0
          109  +  execsql {SELECT max(a) FROM t2}
          110  +} {21}
          111  +do_test minmax-3.1 {
          112  +  set sqlite_search_count
          113  +} {0}
          114  +do_test minmax-3.2 {
          115  +  execsql {INSERT INTO t2 VALUES((SELECT max(a) FROM t2)+1,999)}
          116  +  set sqlite_search_count 0
          117  +  execsql {
          118  +    SELECT b FROM t2 WHERE a=(SELECT max(a) FROM t2)
          119  +  }
          120  +} {999}
          121  +do_test minmax-3.3 {
          122  +  set sqlite_search_count
          123  +} {0}
          124  +
          125  +
          126  +finish_test

Changes to test/rowid.test.

     8      8   #    May you share freely, never taking more than you give.
     9      9   #
    10     10   #***********************************************************************
    11     11   # This file implements regression tests for SQLite library.  The
    12     12   # focus of this file is testing the magic ROWID column that is
    13     13   # found on all tables.
    14     14   #
    15         -# $Id: rowid.test,v 1.7 2002/01/04 03:09:30 drh Exp $
           15  +# $Id: rowid.test,v 1.8 2002/02/19 22:42:06 drh Exp $
    16     16   
    17     17   set testdir [file dirname $argv0]
    18     18   source $testdir/tester.tcl
    19     19   
    20     20   # Basic ROWID functionality tests.
    21     21   #
    22     22   do_test rowid-1.1 {
................................................................................
   151    151   
   152    152   # Columns defined in the CREATE statement override the buildin ROWID
   153    153   # column names.
   154    154   #
   155    155   do_test rowid-3.1 {
   156    156     execsql {
   157    157       CREATE TABLE t2(rowid int, x int, y int);
   158         -    INSERT INTO t2 VALUES(1,2,3);
          158  +    INSERT INTO t2 VALUES(0,2,3);
   159    159       INSERT INTO t2 VALUES(4,5,6);
   160    160       INSERT INTO t2 VALUES(7,8,9);
   161    161       SELECT * FROM t2 ORDER BY x;
   162    162     }
   163         -} {1 2 3 4 5 6 7 8 9}
          163  +} {0 2 3 4 5 6 7 8 9}
   164    164   do_test rowid-3.2 {
   165    165     execsql {SELECT * FROM t2 ORDER BY rowid}
   166         -} {1 2 3 4 5 6 7 8 9}
          166  +} {0 2 3 4 5 6 7 8 9}
   167    167   do_test rowid-3.3 {
   168    168     execsql {SELECT rowid, x, y FROM t2 ORDER BY rowid}
   169         -} {1 2 3 4 5 6 7 8 9}
          169  +} {0 2 3 4 5 6 7 8 9}
   170    170   do_test rowid-3.4 {
   171    171     set r1 [execsql {SELECT _rowid_, rowid FROM t2 ORDER BY rowid}]
   172    172     foreach {a b c d e f} $r1 {}
   173    173     set r2 [execsql {SELECT _rowid_, rowid FROM t2 ORDER BY x DESC}]
   174    174     foreach {u v w x y z} $r2 {}
   175    175     expr {$u==$e && $w==$c && $y==$a}
   176    176   } {1}
................................................................................
   267    267   #
   268    268   do_test rowid-6.1 {
   269    269     execsql {
   270    270       SELECT x FROM t1
   271    271     }
   272    272   } {1 2 3 4 5 6 7 8}
   273    273   do_test rowid-6.2 {
   274         -  for {set ::norow 1} {[execsql {SELECT x FROM t1 WHERE rowid=10}]!=""} \
   275         -    {incr ::norow} {}
          274  +  for {set ::norow 1} {1} {incr ::norow} {
          275  +    if {[execsql "SELECT x FROM t1 WHERE rowid=$::norow"]==""}  break
          276  +  }
   276    277     execsql [subst {
   277    278       DELETE FROM t1 WHERE rowid=$::norow
   278    279     }]
   279    280   } {}
   280    281   do_test rowid-6.3 {
   281    282     execsql {
   282    283       SELECT x FROM t1
   283    284     }
   284    285   } {1 2 3 4 5 6 7 8}
   285    286   
          287  +# Beginning with version 2.3.4, SQLite computes rowids of new rows by
          288  +# finding the maximum current rowid and adding one.  It falls back to
          289  +# the old random algorithm if the maximum rowid is the largest integer.
          290  +# The following tests are for this new behavior.
          291  +#
          292  +do_test rowid-7.0 {
          293  +  execsql {
          294  +    DELETE FROM t1;
          295  +    DROP TABLE t2;
          296  +    DROP INDEX idxt1;
          297  +    INSERT INTO t1 VALUES(1,2);
          298  +    SELECT rowid, * FROM t1;
          299  +  }
          300  +} {1 1 2}
          301  +do_test rowid-7.1 {
          302  +  execsql {
          303  +    INSERT INTO t1 VALUES(99,100);
          304  +    SELECT rowid,* FROM t1
          305  +  }
          306  +} {1 1 2 2 99 100}
          307  +do_test rowid-7.2 {
          308  +  execsql {
          309  +    CREATE TABLE t2(a INTEGER PRIMARY KEY, b);
          310  +    INSERT INTO t2(b) VALUES(55);
          311  +    SELECT * FROM t2;
          312  +  }
          313  +} {1 55}
          314  +do_test rowid-7.3 {
          315  +  execsql {
          316  +    INSERT INTO t2(b) VALUES(66);
          317  +    SELECT * FROM t2;
          318  +  }
          319  +} {1 55 2 66}
          320  +do_test rowid-7.4 {
          321  +  execsql {
          322  +    INSERT INTO t2(a,b) VALUES(1000000,77);
          323  +    INSERT INTO t2(b) VALUES(88);
          324  +    SELECT * FROM t2;
          325  +  }
          326  +} {1 55 2 66 1000000 77 1000001 88}
          327  +do_test rowid-7.5 {
          328  +  execsql {
          329  +    INSERT INTO t2(a,b) VALUES(2147483647,99);
          330  +    INSERT INTO t2(b) VALUES(11);
          331  +    SELECT b FROM t2 ORDER BY b;
          332  +  }
          333  +} {11 55 66 77 88 99}
          334  +do_test rowid-7.6 {
          335  +  execsql {
          336  +    SELECT b FROM t2 WHERE a NOT IN(1,2,1000000,1000001,2147483647);
          337  +  }
          338  +} {11}
          339  +do_test rowid-7.7 {
          340  +  execsql {
          341  +    INSERT INTO t2(b) VALUES(22);
          342  +    INSERT INTO t2(b) VALUES(33);
          343  +    INSERT INTO t2(b) VALUES(44);
          344  +    INSERT INTO t2(b) VALUES(55);
          345  +    SELECT b FROM t2 WHERE a NOT IN(1,2,1000000,1000001,2147483647) ORDER BY b;
          346  +  }
          347  +} {11 22 33 44 55}
          348  +do_test rowid-7.8 {
          349  +  execsql {
          350  +    DELETE FROM t2 WHERE a!=2;
          351  +    INSERT INTO t2(b) VALUES(111);
          352  +    SELECT * FROM t2;
          353  +  }
          354  +} {2 66 3 111}
   286    355   
   287    356   finish_test

Changes to www/c_interface.tcl.

     1      1   #
     2      2   # Run this Tcl script to generate the sqlite.html file.
     3      3   #
     4         -set rcsid {$Id: c_interface.tcl,v 1.23 2002/01/16 21:00:28 drh Exp $}
            4  +set rcsid {$Id: c_interface.tcl,v 1.24 2002/02/19 22:42:06 drh Exp $}
     5      5   
     6      6   puts {<html>
     7      7   <head>
     8      8     <title>The C language interface to the SQLite library</title>
     9      9   </head>
    10     10   <body bgcolor=white>
    11     11   <h1 align=center>
................................................................................
   374    374   header file that comes in the source tree.</p>
   375    375   
   376    376   <h2>The ROWID of the most recent insert</h2>
   377    377   
   378    378   <p>Every row of an SQLite table has a unique integer key.  If the
   379    379   table has a column labeled INTEGER PRIMARY KEY, then that column
   380    380   servers as the key.  If there is no INTEGER PRIMARY KEY column then
   381         -the key is a random integer.  The key for a row can be accessed in
          381  +the key is a unique integer.  The key for a row can be accessed in
   382    382   a SELECT statement or used in a WHERE or ORDER BY clause using any
   383    383   of the names "ROWID", "OID", or "_ROWID_".</p>
   384    384   
   385    385   <p>When you do an insert into a table that does not have an INTEGER PRIMARY
   386    386   KEY column, or if the table does have an INTEGER PRIMARY KEY but the value
   387    387   for that column is not specified in the VALUES clause of the insert, then
   388    388   the key is automatically generated.  You can find the value of the key

Changes to www/changes.tcl.

    16     16     puts "<DT><B>$date</B></DT>"
    17     17     puts "<DD><P><UL>$desc</UL></P></DD>"
    18     18   }
    19     19   
    20     20   chng {2002 Feb * (2.3.4)} {
    21     21   <li>Change the name of the sanity_check PRAGMA to <b>integrity_check</b>
    22     22       and make it available in all compiles.</li>
           23  +<li>SELECT min() or max() of an indexed column with no WHERE or GROUP BY
           24  +    clause is handled as a special case which avoids a complete table scan.</li>
           25  +<li>Automatically generated ROWIDs are now sequential.</li>
    23     26   }
    24     27   
    25     28   chng {2002 Feb 18 (2.3.3)} {
    26     29   <li>Allow identifiers to be quoted in square brackets, for compatibility
    27     30       with MS-Access.</li>
    28     31   <li>Added support for sub-queries in the FROM clause of a SELECT.</li>
    29     32   <li>More efficient implementation of sqliteFileExists() under Windows.

Changes to www/faq.tcl.

     1      1   #
     2      2   # Run this script to generated a faq.html output file
     3      3   #
     4         -set rcsid {$Id: faq.tcl,v 1.7 2002/01/22 03:13:43 drh Exp $}
            4  +set rcsid {$Id: faq.tcl,v 1.8 2002/02/19 22:42:06 drh Exp $}
     5      5   
     6      6   puts {<html>
     7      7   <head>
     8      8     <title>SQLite Frequently Asked Questions</title>
     9      9   </head>
    10     10   <body bgcolor="white">
    11     11   <h1 align="center">Frequently Asked Questions</h1>
................................................................................
    23     23   
    24     24   #############
    25     25   # Enter questions and answers here.
    26     26   
    27     27   faq {
    28     28     How do I create an AUTOINCREMENT field.
    29     29   } {
    30         -  SQLite does not support AUTOINCREMENT.  If you need a unique key for
    31         -  a new entry in a table, you can create an auxiliary table
    32         -  with a single entry that holds the next available value for that key.
    33         -  Like this:
           30  +  <p>Short answer: A column declared INTEGER PRIMARY KEY will
           31  +  autoincrement.</p>
           32  +
           33  +  <p>Here is the long answer:
           34  +  Beginning with version SQLite 2.3.4, If you declare a column of
           35  +  a table to be INTEGER PRIMARY KEY, then whenever you insert a NULL
           36  +  into that column of the table, the NULL is automatically converted
           37  +  into an integer which is one greater than the largest value of that
           38  +  column over all other rows in the table, or 1 if the table is empty.
           39  +  For example, suppose you have a table like this:
           40  +<blockquote><pre>
           41  +CREATE TABLE t1(
           42  +  a INTEGER PRIMARY KEY,
           43  +  b INTEGER
           44  +);
           45  +</pre></blockquote>
           46  +  <p>With this table, the statement</p>
    34     47   <blockquote><pre>
    35         -CREATE TABLE counter(cnt);
    36         -INSERT INTO counter VALUES(1);
           48  +INSERT INTO t1 VALUES(NULL,123);
    37     49   </pre></blockquote>
    38         -  Once you have a counter set up, you can generate a unique key as follows:
           50  +  <p>is logically equivalent to saying:</p>
    39     51   <blockquote><pre>
    40         -BEGIN TRANSACTION;
    41         -SELECT cnt FROM counter;
    42         -UPDATE counter SET cnt=cnt+1;
    43         -COMMIT;
           52  +INSERT INTO t1 VALUES((SELECT max(a) FROM t1)+1,123);
    44     53   </pre></blockquote>
    45         -  There are other ways of simulating the effect of AUTOINCREMENT but
    46         -  this approach seems to be the easiest and most efficient.
    47         -
    48         -  <p><i>New in SQLite version 2.2.0:</i>
    49         -  If one of the columns in a table has type INTEGER PRIMARY KEY and
    50         -  you do an INSERT on that table that does not specify a value for
    51         -  the primary key, then a unique random number is inserted automatically
    52         -  in that column.  This automatically generated key is random, not 
    53         -  sequential, but you can still use it as a unique identifier.</p>
    54         -
    55         -  <p>Here is an example of how the INTEGER PRIMARY KEY feature can be
    56         -  used:</p>
    57         -
    58         -  <blockquote><pre>
    59         -CREATE TABLE ex2(
    60         -  cnum INTEGER PRIMARY KEY,
    61         -  name TEXT,
    62         -  email TEXT
    63         -);
    64         -INSERT INTO ex2(name,email) VALUES('drh','drh@hwaci.com');
    65         -INSERT INTO ex2(name,email) VALUES('alle','alle@hwaci.com');
    66         -SELECT * FROM ex1;
    67         -</pre></blockquote>
    68         -
    69         -  <p>Notice that the primary key column <b>cnum</b> is not specified on
    70         -  the INSERT statements.  The output of the SELECT on the last line will
    71         -  be something like this:</p>
    72         -
    73         -  <blockquote>
    74         -     1597027670|drh|drh@hwaci.com<br>
    75         -     1597027853|alle|alle@hwaci.com
    76         -  </blockquote>
    77         -
    78         -  <p>The randomly generated keys in this case are 1597027670 and
    79         -  1597027853.  You will probably get different keys every time you
    80         -  try this.  The keys will often be ascending, but this is not always
    81         -  the case and you cannot count on that behavior.  The keys will never
    82         -  be sequential.  If you need sequential keys, use the counter implemention
    83         -  described first.</p>
           54  +  <p>For SQLite version 2.2.0 through 2.3.3, if you insert a NULL into
           55  +  an INTEGER PRIMARY KEY column, the NULL will be changed to a unique
           56  +  integer, but it will a semi-random integer.  Unique keys generated this
           57  +  way will not be sequential.  For SQLite version 2.3.4 and beyond, the
           58  +  unique keys will be sequential until the largest key reaches a value
           59  +  of 2147483647.  That is the largest 32-bit signed integer and cannot
           60  +  be incremented, so subsequent insert attempts will revert to the
           61  +  semi-random key generation algorithm of SQLite version 2.3.3 and
           62  +  earlier.</p>
    84     63   
    85     64     <p>Beginning with version 2.2.3, there is a new API function named
    86     65     <b>sqlite_last_insert_rowid()</b> which will return the integer key
    87     66     for the most recent insert operation.  See the API documentation for
    88     67     details.</p>
    89     68   }
    90     69   

Changes to www/lang.tcl.

     1      1   #
     2      2   # Run this Tcl script to generate the sqlite.html file.
     3      3   #
     4         -set rcsid {$Id: lang.tcl,v 1.25 2002/02/19 13:39:23 drh Exp $}
            4  +set rcsid {$Id: lang.tcl,v 1.26 2002/02/19 22:42:06 drh Exp $}
     5      5   
     6      6   puts {<html>
     7      7   <head>
     8      8     <title>Query Language Understood By SQLite</title>
     9      9   </head>
    10     10   <body bgcolor=white>
    11     11   <h1 align=center>
................................................................................
   288    288   on the primary key.  However, if primary key is on a single column
   289    289   that has datatype INTEGER, then that column is used internally
   290    290   as the actual key of the B-Tree for the table.  This means that the column
   291    291   may only hold unique integer values.  (Except for this one case,
   292    292   SQLite ignores the datatype specification of columns and allows
   293    293   any kind of data to be put in a column regardless of its declared
   294    294   datatype.)  If a table does not have an INTEGER PRIMARY KEY column,
   295         -then the B-Tree key will be a randomly generated integer.  The
          295  +then the B-Tree key will be a automatically generated integer.  The
   296    296   B-Tree key for a row can always be accessed using one of the
   297    297   special names "<b>ROWID</b>", "<b>OID</b>", or "<b>_ROWID_</b>".
   298    298   This is true regardless of whether or not there is an INTEGER
   299    299   PRIMARY KEY.</p>
   300    300   
   301    301   <p>If the "TEMP" or "TEMPORARY" keyword occurs in between "CREATE"
   302    302   and "TABLE" then the table that is created is only visible to the