/ Check-in [d90e5f34]
Login

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

Overview
Comment:Merge changes from trunk. This fixes the SQLITE_ENABLE_UPDATE_DELETE_LIMIT functionality so that it works with views and WITHOUT ROWID tables.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | begin-concurrent
Files: files | file ages | folders
SHA3-256: d90e5f346bcf7adab26ca8dad9dfbd0fbb86604a15f2fe827f11b3faab036750
User & Date: dan 2017-11-14 20:06:15
Wiki:begin-concurrent
Context
2017-12-11
14:02
Abort on an invalid paramater to sqlite3BitvecSet(). check-in: 163c8709 user: drh tags: begin-concurrent
2017-11-14
20:06
Merge changes from trunk. This fixes the SQLITE_ENABLE_UPDATE_DELETE_LIMIT functionality so that it works with views and WITHOUT ROWID tables. check-in: d90e5f34 user: dan tags: begin-concurrent
17:06
Fix the SQLITE_ENABLE_UPDATE_DELETE_LIMIT functionality so that it works with views and WITHOUT ROWID tables. check-in: dae4a97a user: dan tags: trunk
2017-11-06
20:02
Merge latest trunk changes into this branch. check-in: 7f217eda user: dan tags: begin-concurrent
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to ext/lsm1/Makefile.

    39     39                $(LSMDIR)/lsm-test/lsmtest_main.c $(LSMDIR)/lsm-test/lsmtest_mem.c \
    40     40                $(LSMDIR)/lsm-test/lsmtest_tdb.c $(LSMDIR)/lsm-test/lsmtest_tdb3.c \
    41     41                $(LSMDIR)/lsm-test/lsmtest_util.c $(LSMDIR)/lsm-test/lsmtest_win32.c
    42     42   
    43     43   
    44     44   # all: lsm.so
    45     45   
    46         -LSMOPTS += -DLSM_MUTEX_PTHREADS=1 -I$(LSMDIR)
           46  +LSMOPTS += -DLSM_MUTEX_PTHREADS=1 -I$(LSMDIR) -DHAVE_ZLIB
    47     47   
    48     48   lsm.so:	$(LSMOBJ)
    49     49   	$(TCCX) -shared -o lsm.so $(LSMOBJ)
    50     50   
    51     51   %.o:	$(LSMDIR)/%.c $(LSMHDR) sqlite3.h
    52     52   	$(TCCX) $(LSMOPTS) -c $<
    53     53   	
    54     54   lsmtest$(EXE): $(LSMOBJ) $(LSMTESTSRC) $(LSMTESTHDR) sqlite3.o
    55     55   	# $(TCPPX) -c $(TOP)/lsm-test/lsmtest_tdb2.cc
    56         -	$(TCCX) $(LSMOPTS) $(LSMTESTSRC) $(LSMOBJ) sqlite3.o -o lsmtest$(EXE) $(THREADLIB)
           56  +	$(TCCX) $(LSMOPTS) $(LSMTESTSRC) $(LSMOBJ) sqlite3.o -o lsmtest$(EXE) $(THREADLIB) -lz

Changes to ext/lsm1/lsm_file.c.

  2799   2799   */
  2800   2800   int lsmFsSortedPadding(
  2801   2801     FileSystem *pFS, 
  2802   2802     Snapshot *pSnapshot,
  2803   2803     Segment *pSeg
  2804   2804   ){
  2805   2805     int rc = LSM_OK;
  2806         -  if( pFS->pCompress ){
         2806  +  if( pFS->pCompress && pSeg->iFirst ){
  2807   2807       Pgno iLast2;
  2808   2808       Pgno iLast = pSeg->iLastPg;     /* Current last page of segment */
  2809   2809       int nPad;                       /* Bytes of padding required */
  2810   2810       u8 aSz[3];
  2811   2811   
  2812   2812       iLast2 = (1 + iLast/pFS->szSector) * pFS->szSector - 1;
  2813   2813       assert( fsPageToBlock(pFS, iLast)==fsPageToBlock(pFS, iLast2) );

Changes to ext/repair/checkindex.c.

    10     10   **
    11     11   *************************************************************************
    12     12   */
    13     13   
    14     14   #include "sqlite3ext.h"
    15     15   SQLITE_EXTENSION_INIT1
    16     16   
           17  +/*
           18  +** Stuff that is available inside the amalgamation, but which we need to
           19  +** declare ourselves if this module is compiled separately.
           20  +*/
    17     21   #ifndef SQLITE_AMALGAMATION
    18     22   # include <string.h>
    19     23   # include <stdio.h>
    20     24   # include <stdlib.h>
    21     25   # include <assert.h>
    22         -# define ALWAYS(X)  1
    23         -# define NEVER(X)   0
    24         -  typedef unsigned char u8;
    25         -  typedef unsigned short u16;
    26         -  typedef unsigned int u32;
           26  +typedef unsigned char u8;
           27  +typedef unsigned short u16;
           28  +typedef unsigned int u32;
    27     29   #define get4byte(x) (        \
    28     30       ((u32)((x)[0])<<24) +    \
    29     31       ((u32)((x)[1])<<16) +    \
    30     32       ((u32)((x)[2])<<8) +     \
    31     33       ((u32)((x)[3]))          \
    32     34   )
    33     35   #endif
................................................................................
    38     40   struct CidxTable {
    39     41     sqlite3_vtab base;              /* Base class.  Must be first */
    40     42     sqlite3 *db;
    41     43   };
    42     44   
    43     45   struct CidxCursor {
    44     46     sqlite3_vtab_cursor base;       /* Base class.  Must be first */
    45         -  sqlite3_int64 iRowid;
    46         -  sqlite3_stmt *pStmt;
           47  +  sqlite3_int64 iRowid;           /* Row number of the output */
           48  +  char *zIdxName;                 /* Copy of the index_name parameter */
           49  +  char *zAfterKey;                /* Copy of the after_key parameter */
           50  +  sqlite3_stmt *pStmt;            /* SQL statement that generates the output */
    47     51   };
    48     52   
    49     53   typedef struct CidxColumn CidxColumn;
    50     54   struct CidxColumn {
    51     55     char *zExpr;                    /* Text for indexed expression */
    52     56     int bDesc;                      /* True for DESC columns, otherwise false */
    53     57     int bKey;                       /* Part of index, not PK */
    54     58   };
    55     59   
    56     60   typedef struct CidxIndex CidxIndex;
    57     61   struct CidxIndex {
           62  +  char *zWhere;                   /* WHERE clause, if any */
    58     63     int nCol;                       /* Elements in aCol[] array */
    59     64     CidxColumn aCol[1];             /* Array of indexed columns */
    60     65   };
    61     66   
    62     67   static void *cidxMalloc(int *pRc, int n){
    63     68     void *pRet = 0;
    64     69     assert( n!=0 );
................................................................................
    78     83     va_start(ap, zFmt);
    79     84     assert( pCsr->base.pVtab->zErrMsg==0 );
    80     85     pCsr->base.pVtab->zErrMsg = sqlite3_vmprintf(zFmt, ap);
    81     86     va_end(ap);
    82     87   }
    83     88   
    84     89   /*
    85         -** Connect to then incremental_index_check virtual table.
           90  +** Connect to the incremental_index_check virtual table.
    86     91   */
    87     92   static int cidxConnect(
    88     93     sqlite3 *db,
    89     94     void *pAux,
    90     95     int argc, const char *const*argv,
    91     96     sqlite3_vtab **ppVtab,
    92     97     char **pzErr
................................................................................
    94     99     int rc = SQLITE_OK;
    95    100     CidxTable *pRet;
    96    101   
    97    102   #define IIC_ERRMSG        0
    98    103   #define IIC_CURRENT_KEY   1
    99    104   #define IIC_INDEX_NAME    2
   100    105   #define IIC_AFTER_KEY     3
          106  +#define IIC_SCANNER_SQL   4
   101    107     rc = sqlite3_declare_vtab(db,
   102    108         "CREATE TABLE xyz("
   103         -      " errmsg TEXT, current_key TEXT,"
   104         -      " index_name HIDDEN, after_key HIDDEN"
          109  +      " errmsg TEXT,"            /* Error message or NULL if everything is ok */
          110  +      " current_key TEXT,"       /* SQLite quote() text of key values */
          111  +      " index_name HIDDEN,"      /* IN: name of the index being scanned */
          112  +      " after_key HIDDEN,"       /* IN: Start scanning after this key */
          113  +      " scanner_sql HIDDEN"      /* debuggingn info: SQL used for scanner */
   105    114         ")"
   106    115     );
   107    116     pRet = cidxMalloc(&rc, sizeof(CidxTable));
   108    117     if( pRet ){
   109    118       pRet->db = db;
   110    119     }
   111    120   
................................................................................
   119    128   static int cidxDisconnect(sqlite3_vtab *pVtab){
   120    129     CidxTable *pTab = (CidxTable*)pVtab;
   121    130     sqlite3_free(pTab);
   122    131     return SQLITE_OK;
   123    132   }
   124    133   
   125    134   /*
   126         -** xBestIndex method.
          135  +** idxNum and idxStr are not used.  There are only three possible plans,
          136  +** which are all distinguished by the number of parameters.
          137  +**
          138  +**   No parameters:         A degenerate plan.  The result is zero rows.
          139  +**   1 Parameter:           Scan all of the index starting with first entry
          140  +**   2 parameters:          Scan the index starting after the "after_key".    
          141  +**
          142  +** Provide successively smaller costs for each of these plans to encourage
          143  +** the query planner to select the one with the most parameters.
   127    144   */
   128    145   static int cidxBestIndex(sqlite3_vtab *tab, sqlite3_index_info *pInfo){
   129    146     int iIdxName = -1;
   130    147     int iAfterKey = -1;
   131    148     int i;
   132    149   
   133    150     for(i=0; i<pInfo->nConstraint; i++){
................................................................................
   175    192   
   176    193   /*
   177    194   ** Close a btreeinfo cursor.
   178    195   */
   179    196   static int cidxClose(sqlite3_vtab_cursor *pCursor){
   180    197     CidxCursor *pCsr = (CidxCursor*)pCursor;
   181    198     sqlite3_finalize(pCsr->pStmt);
   182         -  pCsr->pStmt = 0;
          199  +  sqlite3_free(pCsr->zIdxName);
          200  +  sqlite3_free(pCsr->zAfterKey);
   183    201     sqlite3_free(pCsr);
   184    202     return SQLITE_OK;
   185    203   }
   186    204   
   187    205   /*
   188    206   ** Move a btreeinfo cursor to the next entry in the file.
   189    207   */
................................................................................
   272    290   
   273    291   static void cidxFreeIndex(CidxIndex *pIdx){
   274    292     if( pIdx ){
   275    293       int i;
   276    294       for(i=0; i<pIdx->nCol; i++){
   277    295         sqlite3_free(pIdx->aCol[i].zExpr);
   278    296       }
          297  +    sqlite3_free(pIdx->zWhere);
   279    298       sqlite3_free(pIdx);
   280    299     }
   281    300   }
   282    301   
   283    302   static int cidx_isspace(char c){
   284    303     return c==' ' || c=='\t' || c=='\r' || c=='\n';
   285    304   }
................................................................................
   291    310   }
   292    311   
   293    312   #define CIDX_PARSE_EOF   0
   294    313   #define CIDX_PARSE_COMMA 1      /*  "," */
   295    314   #define CIDX_PARSE_OPEN  2      /*  "(" */
   296    315   #define CIDX_PARSE_CLOSE 3      /*  ")" */
   297    316   
          317  +/*
          318  +** Argument zIn points into the start, middle or end of a CREATE INDEX
          319  +** statement. If argument pbDoNotTrim is non-NULL, then this function
          320  +** scans the input until it finds EOF, a comma (",") or an open or
          321  +** close parenthesis character. It then sets (*pzOut) to point to said
          322  +** character and returns a CIDX_PARSE_XXX constant as appropriate. The
          323  +** parser is smart enough that special characters inside SQL strings
          324  +** or comments are not returned for.
          325  +**
          326  +** Or, if argument pbDoNotTrim is NULL, then this function sets *pzOut
          327  +** to point to the first character of the string that is not whitespace
          328  +** or part of an SQL comment and returns CIDX_PARSE_EOF.
          329  +**
          330  +** Additionally, if pbDoNotTrim is not NULL and the element immediately
          331  +** before (*pzOut) is an SQL comment of the form "-- comment", then
          332  +** (*pbDoNotTrim) is set before returning. In all other cases it is
          333  +** cleared.
          334  +*/
   298    335   static int cidxFindNext(
   299    336     const char *zIn, 
   300    337     const char **pzOut,
   301    338     int *pbDoNotTrim                /* OUT: True if prev is -- comment */
   302    339   ){
   303    340     const char *z = zIn;
   304    341   
   305    342     while( 1 ){
          343  +    while( cidx_isspace(*z) ) z++;
   306    344       if( z[0]=='-' && z[1]=='-' ){
   307    345         z += 2;
   308    346         while( z[0]!='\n' ){
   309    347           if( z[0]=='\0' ) return CIDX_PARSE_EOF;
   310    348           z++;
   311    349         }
   312    350         while( cidx_isspace(*z) ) z++;
   313         -      *pbDoNotTrim = 1;
          351  +      if( pbDoNotTrim ) *pbDoNotTrim = 1;
          352  +    }else
          353  +    if( z[0]=='/' && z[1]=='*' ){
          354  +      z += 2;
          355  +      while( z[0]!='*' || z[1]!='/' ){
          356  +        if( z[1]=='\0' ) return CIDX_PARSE_EOF;
          357  +        z++;
          358  +      }
          359  +      z += 2;
   314    360       }else{
   315    361         *pzOut = z;
          362  +      if( pbDoNotTrim==0 ) return CIDX_PARSE_EOF;
   316    363         switch( *z ){
   317    364           case '\0':
   318    365             return CIDX_PARSE_EOF;
   319    366           case '(':
   320    367             return CIDX_PARSE_OPEN;
   321    368           case ')':
   322    369             return CIDX_PARSE_CLOSE;
................................................................................
   338    385             break;
   339    386           }
   340    387     
   341    388           case '[':
   342    389             while( *z++!=']' );
   343    390             break;
   344    391     
   345         -        case '/':
   346         -          if( z[1]=='*' ){
   347         -            z += 2;
   348         -            while( z[0]!='*' || z[1]!='/' ){
   349         -              if( z[1]=='\0' ) return CIDX_PARSE_EOF;
   350         -              z++;
   351         -            }
   352         -            z += 2;
   353         -            break;
   354         -          }
   355         -  
   356    392           default:
   357    393             z++;
   358    394             break;
   359    395         }
   360    396         *pbDoNotTrim = 0;
   361    397       }
   362    398     }
................................................................................
   402    438         pCol++;
   403    439         z = z1 = z2+1;
   404    440       }
   405    441       if( e==CIDX_PARSE_OPEN ) nParen++;
   406    442       if( e==CIDX_PARSE_CLOSE ) nParen--;
   407    443       z++;
   408    444     }
          445  +
          446  +  /* Search for a WHERE clause */
          447  +  cidxFindNext(z, &z, 0);
          448  +  if( 0==sqlite3_strnicmp(z, "where", 5) ){
          449  +    pIdx->zWhere = cidxMprintf(&rc, "%s\n", &z[5]);
          450  +  }else if( z[0]!='\0' ){
          451  +    goto parse_error;
          452  +  }
   409    453   
   410    454     return rc;
   411    455   
   412    456    parse_error:
   413    457     cidxCursorError(pCsr, "Parse error in: %s", zSql);
   414    458     return SQLITE_ERROR;
   415    459   }
................................................................................
   456    500           p->bKey = sqlite3_column_int(pInfo, 5);
   457    501           if( zSql==0 || p->bKey==0 ){
   458    502             p->zExpr = cidxMprintf(&rc, "\"%w\" COLLATE %s",zName,zColl);
   459    503           }else{
   460    504             p->zExpr = 0;
   461    505           }
   462    506           pIdx->nCol = iCol;
          507  +        pIdx->zWhere = 0;
   463    508         }
   464    509         cidxFinalize(&rc, pInfo);
   465    510       }
   466    511   
   467    512       if( rc==SQLITE_OK && zSql ){
   468    513         rc = cidxParseSQL(pCsr, pIdx, zSql);
   469    514       }
................................................................................
   645    690             break;
   646    691         }
   647    692       }
   648    693     }
   649    694   
   650    695     return zRet;
   651    696   }
          697  +
          698  +/*
          699  +** Generate SQL (in memory obtained from sqlite3_malloc()) that will
          700  +** continue the index scan for zIdxName starting after zAfterKey.
          701  +*/
          702  +int cidxGenerateScanSql(
          703  +  CidxCursor *pCsr,           /* The cursor which needs the new statement */
          704  +  const char *zIdxName,       /* index to be scanned */
          705  +  const char *zAfterKey,      /* start after this key, if not NULL */
          706  +  char **pzSqlOut             /* OUT: Write the generated SQL here */
          707  +){
          708  +  int rc;
          709  +  char *zTab = 0;
          710  +  char *zCurrentKey = 0;
          711  +  char *zOrderBy = 0;
          712  +  char *zSubWhere = 0;
          713  +  char *zSubExpr = 0;
          714  +  char *zSrcList = 0;
          715  +  char **azAfter = 0;
          716  +  CidxIndex *pIdx = 0;
          717  +
          718  +  *pzSqlOut = 0;
          719  +  rc = cidxLookupIndex(pCsr, zIdxName, &pIdx, &zTab);
          720  +
          721  +  zOrderBy = cidxColumnList(&rc, zIdxName, pIdx, CIDX_CLIST_ORDERBY);
          722  +  zCurrentKey = cidxColumnList(&rc, zIdxName, pIdx, CIDX_CLIST_CURRENT_KEY);
          723  +  zSubWhere = cidxColumnList(&rc, zIdxName, pIdx, CIDX_CLIST_SUBWHERE);
          724  +  zSubExpr = cidxColumnList(&rc, zIdxName, pIdx, CIDX_CLIST_SUBEXPR);
          725  +  zSrcList = cidxColumnList(&rc, zIdxName, pIdx, CIDX_CLIST_ALL);
          726  +
          727  +  if( rc==SQLITE_OK && zAfterKey ){
          728  +    rc = cidxDecodeAfter(pCsr, pIdx->nCol, zAfterKey, &azAfter);
          729  +  }
          730  +
          731  +  if( rc==SQLITE_OK ){
          732  +    if( zAfterKey==0 ){
          733  +      *pzSqlOut = cidxMprintf(&rc,
          734  +          "SELECT (SELECT %s FROM %Q AS t WHERE %s), %s "
          735  +          "FROM (SELECT %s FROM %Q INDEXED BY %Q %s%sORDER BY %s) AS i",
          736  +          zSubExpr, zTab, zSubWhere, zCurrentKey, 
          737  +          zSrcList, zTab, zIdxName, 
          738  +          (pIdx->zWhere ? "WHERE " : ""), (pIdx->zWhere ? pIdx->zWhere : ""),
          739  +          zOrderBy
          740  +      );
          741  +    }else{
          742  +      const char *zSep = "";
          743  +      char *zSql;
          744  +      int i;
          745  +  
          746  +      zSql = cidxMprintf(&rc, 
          747  +          "SELECT (SELECT %s FROM %Q WHERE %s), %s FROM (",
          748  +          zSubExpr, zTab, zSubWhere, zCurrentKey
          749  +      );
          750  +      for(i=pIdx->nCol-1; i>=0; i--){
          751  +        int j;
          752  +        if( pIdx->aCol[i].bDesc && azAfter[i]==0 ) continue;
          753  +        for(j=0; j<2; j++){
          754  +          char *zWhere = cidxWhere(&rc, pIdx->aCol, azAfter, i, j);
          755  +          zSql = cidxMprintf(&rc, "%z"
          756  +              "%sSELECT * FROM ("
          757  +                "SELECT %s FROM %Q INDEXED BY %Q WHERE %s%s%z ORDER BY %s"
          758  +              ")",
          759  +              zSql, zSep, zSrcList, zTab, zIdxName, 
          760  +              pIdx->zWhere ? pIdx->zWhere : "",
          761  +              pIdx->zWhere ? " AND " : "",
          762  +              zWhere, zOrderBy
          763  +          );
          764  +          zSep = " UNION ALL ";
          765  +          if( pIdx->aCol[i].bDesc==0 ) break;
          766  +        }
          767  +      }
          768  +      *pzSqlOut = cidxMprintf(&rc, "%z) AS i", zSql);
          769  +    }
          770  +  }
          771  +
          772  +  sqlite3_free(zTab);
          773  +  sqlite3_free(zCurrentKey);
          774  +  sqlite3_free(zOrderBy);
          775  +  sqlite3_free(zSubWhere);
          776  +  sqlite3_free(zSubExpr);
          777  +  sqlite3_free(zSrcList);
          778  +  cidxFreeIndex(pIdx);
          779  +  sqlite3_free(azAfter);
          780  +  return rc;
          781  +}
          782  +
   652    783   
   653    784   /* 
   654    785   ** Position a cursor back to the beginning.
   655    786   */
   656    787   static int cidxFilter(
   657    788     sqlite3_vtab_cursor *pCursor, 
   658    789     int idxNum, const char *idxStr,
................................................................................
   659    790     int argc, sqlite3_value **argv
   660    791   ){
   661    792     int rc = SQLITE_OK;
   662    793     CidxCursor *pCsr = (CidxCursor*)pCursor;
   663    794     const char *zIdxName = 0;
   664    795     const char *zAfterKey = 0;
   665    796   
          797  +  sqlite3_free(pCsr->zIdxName);
          798  +  pCsr->zIdxName = 0;
          799  +  sqlite3_free(pCsr->zAfterKey);
          800  +  pCsr->zAfterKey = 0;
          801  +  sqlite3_finalize(pCsr->pStmt);
          802  +  pCsr->pStmt = 0;
          803  +
   666    804     if( argc>0 ){
   667    805       zIdxName = (const char*)sqlite3_value_text(argv[0]);
   668    806       if( argc>1 ){
   669    807         zAfterKey = (const char*)sqlite3_value_text(argv[1]);
   670    808       }
   671    809     }
   672    810   
   673    811     if( zIdxName ){
   674         -    char *zTab = 0;
   675         -    char *zCurrentKey = 0;
   676         -    char *zOrderBy = 0;
   677         -    char *zSubWhere = 0;
   678         -    char *zSubExpr = 0;
   679         -    char *zSrcList = 0;
   680         -
   681         -    char **azAfter = 0;
   682         -    CidxIndex *pIdx = 0;
   683         -
   684         -    rc = cidxLookupIndex(pCsr, zIdxName, &pIdx, &zTab);
   685         -
   686         -    zOrderBy = cidxColumnList(&rc, zIdxName, pIdx, CIDX_CLIST_ORDERBY);
   687         -    zCurrentKey = cidxColumnList(&rc, zIdxName, pIdx, CIDX_CLIST_CURRENT_KEY);
   688         -    zSubWhere = cidxColumnList(&rc, zIdxName, pIdx, CIDX_CLIST_SUBWHERE);
   689         -    zSubExpr = cidxColumnList(&rc, zIdxName, pIdx, CIDX_CLIST_SUBEXPR);
   690         -    zSrcList = cidxColumnList(&rc, zIdxName, pIdx, CIDX_CLIST_ALL);
   691         -
   692         -    if( rc==SQLITE_OK && zAfterKey ){
   693         -      rc = cidxDecodeAfter(pCsr, pIdx->nCol, zAfterKey, &azAfter);
   694         -    }
   695         -
   696         -    if( rc || zAfterKey==0 ){
   697         -      pCsr->pStmt = cidxPrepare(&rc, pCsr, 
   698         -          "SELECT (SELECT %s FROM %Q AS t WHERE %s), %s "
   699         -          "FROM (SELECT %s FROM %Q ORDER BY %s) AS i",
   700         -          zSubExpr, zTab, zSubWhere, zCurrentKey, 
   701         -          zSrcList, zTab, zOrderBy
   702         -      );
   703         -      /* printf("SQL: %s\n", sqlite3_sql(pCsr->pStmt)); */
   704         -    }else{
   705         -      const char *zSep = "";
   706         -      char *zSql;
   707         -      int i;
   708         -
   709         -      zSql = cidxMprintf(&rc, 
   710         -          "SELECT (SELECT %s FROM %Q WHERE %s), %s FROM (",
   711         -          zSubExpr, zTab, zSubWhere, zCurrentKey
   712         -      );
   713         -      for(i=pIdx->nCol-1; i>=0; i--){
   714         -        int j;
   715         -        if( pIdx->aCol[i].bDesc && azAfter[i]==0 ) continue;
   716         -        for(j=0; j<2; j++){
   717         -          char *zWhere = cidxWhere(&rc, pIdx->aCol, azAfter, i, j);
   718         -          zSql = cidxMprintf(&rc, "%z"
   719         -              "%sSELECT * FROM (SELECT %s FROM %Q WHERE %z ORDER BY %s)",
   720         -              zSql, zSep, zSrcList, zTab, zWhere, zOrderBy
   721         -          );
   722         -          zSep = " UNION ALL ";
   723         -          if( pIdx->aCol[i].bDesc==0 ) break;
   724         -        }
   725         -      }
   726         -      zSql = cidxMprintf(&rc, "%z) AS i", zSql);
   727         -
   728         -      /* printf("SQL: %s\n", zSql); */
          812  +    char *zSql = 0;
          813  +    pCsr->zIdxName = sqlite3_mprintf("%s", zIdxName);
          814  +    pCsr->zAfterKey = zAfterKey ? sqlite3_mprintf("%s", zAfterKey) : 0;
          815  +    rc = cidxGenerateScanSql(pCsr, zIdxName, zAfterKey, &zSql);
          816  +    if( zSql ){
   729    817         pCsr->pStmt = cidxPrepare(&rc, pCsr, "%z", zSql);
   730    818       }
   731         -
   732         -    sqlite3_free(zTab);
   733         -    sqlite3_free(zCurrentKey);
   734         -    sqlite3_free(zOrderBy);
   735         -    sqlite3_free(zSubWhere);
   736         -    sqlite3_free(zSubExpr);
   737         -    sqlite3_free(zSrcList);
   738         -    cidxFreeIndex(pIdx);
   739         -    sqlite3_free(azAfter);
   740    819     }
   741    820   
   742    821     if( pCsr->pStmt ){
   743    822       assert( rc==SQLITE_OK );
   744    823       rc = cidxNext(pCursor);
   745    824     }
   746    825     pCsr->iRowid = 1;
................................................................................
   752    831   */
   753    832   static int cidxColumn(
   754    833     sqlite3_vtab_cursor *pCursor, 
   755    834     sqlite3_context *ctx, 
   756    835     int iCol
   757    836   ){
   758    837     CidxCursor *pCsr = (CidxCursor*)pCursor;
   759         -  assert( iCol>=IIC_ERRMSG && iCol<=IIC_AFTER_KEY );
   760         -  if( iCol==IIC_ERRMSG ){
   761         -    const char *zVal = 0;
   762         -    if( sqlite3_column_type(pCsr->pStmt, 0)==SQLITE_INTEGER ){
   763         -      if( sqlite3_column_int(pCsr->pStmt, 0)==0 ){
   764         -        zVal = "row data mismatch";
          838  +  assert( iCol>=IIC_ERRMSG && iCol<=IIC_SCANNER_SQL );
          839  +  switch( iCol ){
          840  +    case IIC_ERRMSG: {
          841  +      const char *zVal = 0;
          842  +      if( sqlite3_column_type(pCsr->pStmt, 0)==SQLITE_INTEGER ){
          843  +        if( sqlite3_column_int(pCsr->pStmt, 0)==0 ){
          844  +          zVal = "row data mismatch";
          845  +        }
          846  +      }else{
          847  +        zVal = "row missing";
   765    848         }
   766         -    }else{
   767         -      zVal = "row missing";
          849  +      sqlite3_result_text(ctx, zVal, -1, SQLITE_STATIC);
          850  +      break;
          851  +    }
          852  +    case IIC_CURRENT_KEY: {
          853  +      sqlite3_result_value(ctx, sqlite3_column_value(pCsr->pStmt, 1));
          854  +      break;
          855  +    }
          856  +    case IIC_INDEX_NAME: {
          857  +      sqlite3_result_text(ctx, pCsr->zIdxName, -1, SQLITE_TRANSIENT);
          858  +      break;
          859  +    }
          860  +    case IIC_AFTER_KEY: {
          861  +      sqlite3_result_text(ctx, pCsr->zAfterKey, -1, SQLITE_TRANSIENT);
          862  +      break;
   768    863       }
   769         -    sqlite3_result_text(ctx, zVal, -1, SQLITE_STATIC);
   770         -  }else if( iCol==IIC_CURRENT_KEY ){
   771         -    sqlite3_result_value(ctx, sqlite3_column_value(pCsr->pStmt, 1));
          864  +    case IIC_SCANNER_SQL: {
          865  +      char *zSql = 0;
          866  +      cidxGenerateScanSql(pCsr, pCsr->zIdxName, pCsr->zAfterKey, &zSql);
          867  +      sqlite3_result_text(ctx, zSql, -1, sqlite3_free);
          868  +      break;
          869  +    }
   772    870     }
   773    871     return SQLITE_OK;
   774    872   }
   775    873   
   776    874   /* Return the ROWID for the sqlite_btreeinfo table */
   777    875   static int cidxRowid(sqlite3_vtab_cursor *pCursor, sqlite_int64 *pRowid){
   778         -  *pRowid = 0;
          876  +  CidxCursor *pCsr = (CidxCursor*)pCursor;
          877  +  *pRowid = pCsr->iRowid;
   779    878     return SQLITE_OK;
   780    879   }
   781    880   
   782    881   /*
   783    882   ** Register the virtual table modules with the database handle passed
   784    883   ** as the only argument.
   785    884   */

Changes to ext/repair/sqlite3_checker.tcl.

    38     38         append line \n
    39     39       }
    40     40     }
    41     41   }
    42     42   
    43     43   # Do an incremental integrity check of a single index
    44     44   #
    45         -proc check_index {idxname batchsize} {
           45  +proc check_index {idxname batchsize bTrace} {
    46     46     set i 0
    47     47     set more 1
    48     48     set nerr 0
    49     49     set pct 00.0
    50     50     set max [db one {SELECT nEntry FROM sqlite_btreeinfo('main')
    51     51                       WHERE name=$idxname}]
    52     52     puts -nonewline "$idxname: $i of $max rows ($pct%)\r"
    53     53     flush stdout
    54         -  while {$more} {
    55         -    set more 0
    56         -    db eval {SELECT errmsg, current_key AS key
           54  +  if {$bTrace} {
           55  +    set sql {SELECT errmsg, current_key AS key,
           56  +                    CASE WHEN rowid=1 THEN scanner_sql END AS traceOut
           57  +               FROM incremental_index_check($idxname)
           58  +              WHERE after_key=$key
           59  +              LIMIT $batchsize}
           60  +  } else {
           61  +    set sql {SELECT errmsg, current_key AS key, NULL AS traceOut
    57     62                  FROM incremental_index_check($idxname)
    58     63                 WHERE after_key=$key
    59         -              LIMIT $batchsize} {
           64  +              LIMIT $batchsize}
           65  +  }
           66  +  while {$more} {
           67  +    set more 0
           68  +    db eval $sql {
    60     69         set more 1
    61     70         if {$errmsg!=""} {
    62     71           incr nerr
    63     72           puts "$idxname: key($key): $errmsg"
           73  +      } elseif {$traceOut!=""} {
           74  +        puts "$idxname: $traceOut"
    64     75         }
    65     76         incr i
           77  +      
    66     78       }
    67     79       set x [format {%.1f} [expr {($i*100.0)/$max}]]
    68     80       if {$x!=$pct} {
    69     81         puts -nonewline "$idxname: $i of $max rows ($pct%)\r"
    70     82         flush stdout
    71     83         set pct $x
    72     84       }
................................................................................
    93    105   
    94    106      --summary         Print summary information about the database
    95    107   
    96    108      --table NAME      Run a check of all indexes for table NAME
    97    109   
    98    110      --tclsh           Run the built-in TCL interpreter (for debugging)
    99    111   
          112  +   --trace           (Debugging only:) Output trace information on the scan
          113  +
   100    114      --version         Show the version number of SQLite
   101    115   }
   102    116     exit 1
   103    117   }
   104    118   
   105    119   set file_to_analyze {}
   106    120   append argv {}
   107    121   set bFreelistCheck 0
   108    122   set bSummary 0
   109    123   set zIndex {}
   110    124   set zTable {}
   111    125   set batchsize 1000
   112    126   set bAll 1
          127  +set bTrace 0
   113    128   set argc [llength $argv]
   114    129   for {set i 0} {$i<$argc} {incr i} {
   115    130     set arg [lindex $argv $i]
   116    131     if {[regexp {^-+tclsh$} $arg]} {
   117    132       tclsh
   118    133       exit 0
   119    134     }
................................................................................
   128    143       set bAll 0
   129    144       continue
   130    145     }
   131    146     if {[regexp {^-+summary$} $arg]} {
   132    147       set bSummary 1
   133    148       set bAll 0
   134    149       continue
          150  +  }
          151  +  if {[regexp {^-+trace$} $arg]} {
          152  +    set bTrace 1
          153  +    continue
   135    154     }
   136    155     if {[regexp {^-+batchsize$} $arg]} {
   137    156       incr i
   138    157       if {$i>=$argc} {
   139    158         puts stderr "missing argument on $arg"
   140    159         exit 1
   141    160       }
................................................................................
   220    239         }
   221    240       }
   222    241       puts [format {%7.1f %s index %s of table %s} \
   223    242               [expr {$sz/$scale}] $unit $name $tbl_name]
   224    243     }
   225    244   }
   226    245   if {$zIndex!=""} {
   227         -  check_index $zIndex $batchsize
          246  +  check_index $zIndex $batchsize $bTrace
   228    247   }
   229    248   if {$zTable!=""} {
   230    249     foreach idx [db eval {SELECT name FROM sqlite_master
   231    250                            WHERE type='index' AND rootpage>0
   232    251                              AND tbl_name=$zTable}] {
   233         -    check_index $idx $batchsize
          252  +    check_index $idx $batchsize $bTrace
   234    253     }
   235    254   }
   236    255   if {$bAll} {
   237    256     set allidx [db eval {SELECT name FROM sqlite_btreeinfo('main')
   238    257                           WHERE type='index' AND rootpage>0
   239    258                           ORDER BY nEntry}]
   240    259     foreach idx $allidx {
   241         -    check_index $idx $batchsize
          260  +    check_index $idx $batchsize $bTrace
   242    261     }
   243    262   }

Changes to ext/repair/test/checkindex01.test.

    39     39   
    40     40     uplevel [list do_test $tn.2 "incr_index_check $idx 1" [list {*}$res]]
    41     41     uplevel [list do_test $tn.3 "incr_index_check $idx 2" [list {*}$res]]
    42     42     uplevel [list do_test $tn.4 "incr_index_check $idx 5" [list {*}$res]]
    43     43   }
    44     44   
    45     45   
    46         -do_execsql_test 1.2 {
    47         -  SELECT errmsg IS NULL, current_key FROM incremental_index_check('i1');
           46  +do_execsql_test 1.2.1 {
           47  +  SELECT rowid, errmsg IS NULL, current_key FROM incremental_index_check('i1');
           48  +} {
           49  +  1 1 'five',5
           50  +  2 1 'four',4
           51  +  3 1 'one',1
           52  +  4 1 'three',3
           53  +  5 1 'two',2
           54  +}
           55  +do_execsql_test 1.2.2 {
           56  +  SELECT errmsg IS NULL, current_key, index_name, after_key, scanner_sql
           57  +    FROM incremental_index_check('i1') LIMIT 1;
    48     58   } {
    49         -  1 'five',5
    50         -  1 'four',4
    51         -  1 'one',1
    52         -  1 'three',3
    53         -  1 'two',2
           59  +  1
           60  +  'five',5
           61  +  i1
           62  +  {}
           63  +  {SELECT (SELECT a IS i.i0 FROM 't1' AS t WHERE "rowid" COLLATE BINARY IS i.i1), quote(i0)||','||quote(i1) FROM (SELECT (a) AS i0, ("rowid" COLLATE BINARY) AS i1 FROM 't1' INDEXED BY 'i1' ORDER BY 1,2) AS i}
    54     64   }
    55     65   
    56     66   do_index_check_test 1.3 i1 {
    57     67     {} 'five',5
    58     68     {} 'four',4
    59     69     {} 'one',1
    60     70     {} 'three',3
................................................................................
   305    315     {} 3,2,1 
   306    316     {} 6,5,4
   307    317   }
   308    318   do_index_check_test 6.2 t6x3 {
   309    319     {} 3,2,1 
   310    320     {} 6,5,4
   311    321   }
          322  +
          323  +#-------------------------------------------------------------------------
          324  +#
          325  +do_execsql_test 7.0 {
          326  +  CREATE TABLE t7(x INTEGER PRIMARY KEY, y, z);
          327  +  INSERT INTO t7 VALUES(1, 1, 1);
          328  +  INSERT INTO t7 VALUES(2, 2, 0);
          329  +  INSERT INTO t7 VALUES(3, 3, 1);
          330  +  INSERT INTO t7 VALUES(4, 4, 0);
          331  +
          332  +  CREATE INDEX t7i1 ON t7(y) WHERE z=1;
          333  +  CREATE INDEX t7i2 ON t7(y) /* hello,world */ WHERE z=1;
          334  +  CREATE INDEX t7i3 ON t7(y) WHERE -- yep 
          335  +  z=1;
          336  +  CREATE INDEX t7i4 ON t7(y) WHERE z=1 -- yep;
          337  +}
          338  +do_index_check_test 7.1 t7i1 {
          339  +  {} 1,1 {} 3,3
          340  +}
          341  +do_index_check_test 7.2 t7i2 {
          342  +  {} 1,1 {} 3,3
          343  +}
          344  +do_index_check_test 7.3 t7i3 {
          345  +  {} 1,1 {} 3,3
          346  +}
          347  +do_index_check_test 7.4 t7i4 {
          348  +  {} 1,1 {} 3,3
          349  +}
          350  +
          351  +

Changes to src/dbpage.c.

   299    299   static int dbpageUpdate(
   300    300     sqlite3_vtab *pVtab,
   301    301     int argc,
   302    302     sqlite3_value **argv,
   303    303     sqlite_int64 *pRowid
   304    304   ){
   305    305     DbpageTable *pTab = (DbpageTable *)pVtab;
   306         -  int pgno;
          306  +  Pgno pgno;
   307    307     DbPage *pDbPage = 0;
   308    308     int rc = SQLITE_OK;
   309    309     char *zErr = 0;
   310    310     const char *zSchema;
   311    311     int iDb;
   312    312     Btree *pBt;
   313    313     Pager *pPager;
................................................................................
   314    314     int szPage;
   315    315   
   316    316     if( argc==1 ){
   317    317       zErr = "cannot delete";
   318    318       goto update_fail;
   319    319     }
   320    320     pgno = sqlite3_value_int(argv[0]);
   321         -  if( sqlite3_value_int(argv[1])!=pgno ){
          321  +  if( (Pgno)sqlite3_value_int(argv[1])!=pgno ){
   322    322       zErr = "cannot insert";
   323    323       goto update_fail;
   324    324     }
   325    325     zSchema = (const char*)sqlite3_value_text(argv[4]);
   326    326     iDb = zSchema ? sqlite3FindDbName(pTab->db, zSchema) : -1;
   327    327     if( iDb<0 ){
   328    328       zErr = "no such schema";

Changes to src/delete.c.

    86     86   ** pWhere argument is an optional WHERE clause that restricts the
    87     87   ** set of rows in the view that are to be added to the ephemeral table.
    88     88   */
    89     89   void sqlite3MaterializeView(
    90     90     Parse *pParse,       /* Parsing context */
    91     91     Table *pView,        /* View definition */
    92     92     Expr *pWhere,        /* Optional WHERE clause to be added */
           93  +  ExprList *pOrderBy,  /* Optional ORDER BY clause */
           94  +  Expr *pLimit,        /* Optional LIMIT clause */
           95  +  Expr *pOffset,       /* Optional OFFSET clause */
    93     96     int iCur             /* Cursor number for ephemeral table */
    94     97   ){
    95     98     SelectDest dest;
    96     99     Select *pSel;
    97    100     SrcList *pFrom;
    98    101     sqlite3 *db = pParse->db;
    99    102     int iDb = sqlite3SchemaToIndex(db, pView->pSchema);
................................................................................
   102    105     if( pFrom ){
   103    106       assert( pFrom->nSrc==1 );
   104    107       pFrom->a[0].zName = sqlite3DbStrDup(db, pView->zName);
   105    108       pFrom->a[0].zDatabase = sqlite3DbStrDup(db, db->aDb[iDb].zDbSName);
   106    109       assert( pFrom->a[0].pOn==0 );
   107    110       assert( pFrom->a[0].pUsing==0 );
   108    111     }
   109         -  pSel = sqlite3SelectNew(pParse, 0, pFrom, pWhere, 0, 0, 0, 
   110         -                          SF_IncludeHidden, 0, 0);
          112  +  pSel = sqlite3SelectNew(pParse, 0, pFrom, pWhere, 0, 0, pOrderBy, 
          113  +                          SF_IncludeHidden, pLimit, pOffset);
   111    114     sqlite3SelectDestInit(&dest, SRT_EphemTab, iCur);
   112    115     sqlite3Select(pParse, pSel, &dest);
   113    116     sqlite3SelectDelete(db, pSel);
   114    117   }
   115    118   #endif /* !defined(SQLITE_OMIT_VIEW) && !defined(SQLITE_OMIT_TRIGGER) */
   116    119   
   117    120   #if defined(SQLITE_ENABLE_UPDATE_DELETE_LIMIT) && !defined(SQLITE_OMIT_SUBQUERY)
................................................................................
   128    131     SrcList *pSrc,               /* the FROM clause -- which tables to scan */
   129    132     Expr *pWhere,                /* The WHERE clause.  May be null */
   130    133     ExprList *pOrderBy,          /* The ORDER BY clause.  May be null */
   131    134     Expr *pLimit,                /* The LIMIT clause.  May be null */
   132    135     Expr *pOffset,               /* The OFFSET clause.  May be null */
   133    136     char *zStmtType              /* Either DELETE or UPDATE.  For err msgs. */
   134    137   ){
   135         -  Expr *pWhereRowid = NULL;    /* WHERE rowid .. */
          138  +  sqlite3 *db = pParse->db;
          139  +  Expr *pLhs = NULL;           /* LHS of IN(SELECT...) operator */
   136    140     Expr *pInClause = NULL;      /* WHERE rowid IN ( select ) */
   137         -  Expr *pSelectRowid = NULL;   /* SELECT rowid ... */
   138    141     ExprList *pEList = NULL;     /* Expression list contaning only pSelectRowid */
   139    142     SrcList *pSelectSrc = NULL;  /* SELECT rowid FROM x ... (dup of pSrc) */
   140    143     Select *pSelect = NULL;      /* Complete SELECT tree */
          144  +  Table *pTab;
   141    145   
   142    146     /* Check that there isn't an ORDER BY without a LIMIT clause.
   143    147     */
   144         -  if( pOrderBy && (pLimit == 0) ) {
          148  +  if( pOrderBy && pLimit==0 ) {
   145    149       sqlite3ErrorMsg(pParse, "ORDER BY without LIMIT on %s", zStmtType);
   146         -    goto limit_where_cleanup;
          150  +    sqlite3ExprDelete(pParse->db, pWhere);
          151  +    sqlite3ExprListDelete(pParse->db, pOrderBy);
          152  +    sqlite3ExprDelete(pParse->db, pLimit);
          153  +    sqlite3ExprDelete(pParse->db, pOffset);
          154  +    return 0;
   147    155     }
   148    156   
   149    157     /* We only need to generate a select expression if there
   150    158     ** is a limit/offset term to enforce.
   151    159     */
   152    160     if( pLimit == 0 ) {
   153    161       /* if pLimit is null, pOffset will always be null as well. */
................................................................................
   160    168     **   DELETE FROM table_a WHERE col1=1 ORDER BY col2 LIMIT 1 OFFSET 1
   161    169     ** becomes:
   162    170     **   DELETE FROM table_a WHERE rowid IN ( 
   163    171     **     SELECT rowid FROM table_a WHERE col1=1 ORDER BY col2 LIMIT 1 OFFSET 1
   164    172     **   );
   165    173     */
   166    174   
   167         -  pSelectRowid = sqlite3PExpr(pParse, TK_ROW, 0, 0);
   168         -  if( pSelectRowid == 0 ) goto limit_where_cleanup;
   169         -  pEList = sqlite3ExprListAppend(pParse, 0, pSelectRowid);
   170         -  if( pEList == 0 ) goto limit_where_cleanup;
          175  +  pTab = pSrc->a[0].pTab;
          176  +  if( HasRowid(pTab) ){
          177  +    pLhs = sqlite3PExpr(pParse, TK_ROW, 0, 0);
          178  +    pEList = sqlite3ExprListAppend(
          179  +        pParse, 0, sqlite3PExpr(pParse, TK_ROW, 0, 0)
          180  +    );
          181  +  }else{
          182  +    Index *pPk = sqlite3PrimaryKeyIndex(pTab);
          183  +    if( pPk->nKeyCol==1 ){
          184  +      const char *zName = pTab->aCol[pPk->aiColumn[0]].zName;
          185  +      pLhs = sqlite3Expr(db, TK_ID, zName);
          186  +      pEList = sqlite3ExprListAppend(pParse, 0, sqlite3Expr(db, TK_ID, zName));
          187  +    }else{
          188  +      int i;
          189  +      for(i=0; i<pPk->nKeyCol; i++){
          190  +        Expr *p = sqlite3Expr(db, TK_ID, pTab->aCol[pPk->aiColumn[i]].zName);
          191  +        pEList = sqlite3ExprListAppend(pParse, pEList, p);
          192  +      }
          193  +      pLhs = sqlite3PExpr(pParse, TK_VECTOR, 0, 0);
          194  +      if( pLhs ){
          195  +        pLhs->x.pList = sqlite3ExprListDup(db, pEList, 0);
          196  +      }
          197  +    }
          198  +  }
   171    199   
   172    200     /* duplicate the FROM clause as it is needed by both the DELETE/UPDATE tree
   173    201     ** and the SELECT subtree. */
          202  +  pSrc->a[0].pTab = 0;
   174    203     pSelectSrc = sqlite3SrcListDup(pParse->db, pSrc, 0);
   175         -  if( pSelectSrc == 0 ) {
   176         -    sqlite3ExprListDelete(pParse->db, pEList);
   177         -    goto limit_where_cleanup;
   178         -  }
          204  +  pSrc->a[0].pTab = pTab;
          205  +  pSrc->a[0].pIBIndex = 0;
   179    206   
   180    207     /* generate the SELECT expression tree. */
   181         -  pSelect = sqlite3SelectNew(pParse,pEList,pSelectSrc,pWhere,0,0,
   182         -                             pOrderBy,0,pLimit,pOffset);
   183         -  if( pSelect == 0 ) return 0;
          208  +  pSelect = sqlite3SelectNew(pParse, pEList, pSelectSrc, pWhere, 0 ,0, 
          209  +      pOrderBy,0,pLimit,pOffset
          210  +  );
   184    211   
   185    212     /* now generate the new WHERE rowid IN clause for the DELETE/UDPATE */
   186         -  pWhereRowid = sqlite3PExpr(pParse, TK_ROW, 0, 0);
   187         -  pInClause = pWhereRowid ? sqlite3PExpr(pParse, TK_IN, pWhereRowid, 0) : 0;
          213  +  pInClause = sqlite3PExpr(pParse, TK_IN, pLhs, 0);
   188    214     sqlite3PExprAddSelect(pParse, pInClause, pSelect);
   189    215     return pInClause;
   190         -
   191         -limit_where_cleanup:
   192         -  sqlite3ExprDelete(pParse->db, pWhere);
   193         -  sqlite3ExprListDelete(pParse->db, pOrderBy);
   194         -  sqlite3ExprDelete(pParse->db, pLimit);
   195         -  sqlite3ExprDelete(pParse->db, pOffset);
   196         -  return 0;
   197    216   }
   198    217   #endif /* defined(SQLITE_ENABLE_UPDATE_DELETE_LIMIT) */
   199    218          /*      && !defined(SQLITE_OMIT_SUBQUERY) */
   200    219   
   201    220   /*
   202    221   ** Generate code for a DELETE FROM statement.
   203    222   **
................................................................................
   204    223   **     DELETE FROM table_wxyz WHERE a<5 AND b NOT NULL;
   205    224   **                 \________/       \________________/
   206    225   **                  pTabList              pWhere
   207    226   */
   208    227   void sqlite3DeleteFrom(
   209    228     Parse *pParse,         /* The parser context */
   210    229     SrcList *pTabList,     /* The table from which we should delete things */
   211         -  Expr *pWhere           /* The WHERE clause.  May be null */
          230  +  Expr *pWhere,          /* The WHERE clause.  May be null */
          231  +  ExprList *pOrderBy,    /* ORDER BY clause. May be null */
          232  +  Expr *pLimit,          /* LIMIT clause. May be null */
          233  +  Expr *pOffset          /* OFFSET clause. May be null */
   212    234   ){
   213    235     Vdbe *v;               /* The virtual database engine */
   214    236     Table *pTab;           /* The table from which records will be deleted */
   215    237     int i;                 /* Loop counter */
   216    238     WhereInfo *pWInfo;     /* Information about the WHERE clause */
   217    239     Index *pIdx;           /* For looping over indices of the table */
   218    240     int iTabCur;           /* Cursor number for the table */
................................................................................
   248    270   
   249    271     memset(&sContext, 0, sizeof(sContext));
   250    272     db = pParse->db;
   251    273     if( pParse->nErr || db->mallocFailed ){
   252    274       goto delete_from_cleanup;
   253    275     }
   254    276     assert( pTabList->nSrc==1 );
          277  +
   255    278   
   256    279     /* Locate the table which we want to delete.  This table has to be
   257    280     ** put in an SrcList structure because some of the subroutines we
   258    281     ** will be calling are designed to work with multiple tables and expect
   259    282     ** an SrcList* parameter instead of just a Table* parameter.
   260    283     */
   261    284     pTab = sqlite3SrcListLookup(pParse, pTabList);
................................................................................
   272    295   # define pTrigger 0
   273    296   # define isView 0
   274    297   #endif
   275    298   #ifdef SQLITE_OMIT_VIEW
   276    299   # undef isView
   277    300   # define isView 0
   278    301   #endif
          302  +
          303  +#ifdef SQLITE_ENABLE_UPDATE_DELETE_LIMIT
          304  +  if( !isView ){
          305  +    pWhere = sqlite3LimitWhere(
          306  +        pParse, pTabList, pWhere, pOrderBy, pLimit, pOffset, "DELETE"
          307  +    );
          308  +    pOrderBy = 0;
          309  +    pLimit = pOffset = 0;
          310  +  }
          311  +#endif
   279    312   
   280    313     /* If pTab is really a view, make sure it has been initialized.
   281    314     */
   282    315     if( sqlite3ViewGetColumnNames(pParse, pTab) ){
   283    316       goto delete_from_cleanup;
   284    317     }
   285    318   
................................................................................
   320    353     sqlite3BeginWriteOperation(pParse, 1, iDb);
   321    354   
   322    355     /* If we are trying to delete from a view, realize that view into
   323    356     ** an ephemeral table.
   324    357     */
   325    358   #if !defined(SQLITE_OMIT_VIEW) && !defined(SQLITE_OMIT_TRIGGER)
   326    359     if( isView ){
   327         -    sqlite3MaterializeView(pParse, pTab, pWhere, iTabCur);
          360  +    sqlite3MaterializeView(pParse, pTab, 
          361  +        pWhere, pOrderBy, pLimit, pOffset, iTabCur
          362  +    );
   328    363       iDataCur = iIdxCur = iTabCur;
          364  +    pOrderBy = 0;
          365  +    pLimit = pOffset = 0;
   329    366     }
   330    367   #endif
   331    368   
   332    369     /* Resolve the column names in the WHERE clause.
   333    370     */
   334    371     memset(&sNC, 0, sizeof(sNC));
   335    372     sNC.pParse = pParse;
................................................................................
   565    602       sqlite3VdbeSetColName(v, 0, COLNAME_NAME, "rows deleted", SQLITE_STATIC);
   566    603     }
   567    604   
   568    605   delete_from_cleanup:
   569    606     sqlite3AuthContextPop(&sContext);
   570    607     sqlite3SrcListDelete(db, pTabList);
   571    608     sqlite3ExprDelete(db, pWhere);
          609  +#if defined(SQLITE_ENABLE_UPDATE_DELETE_LIMIT) 
          610  +  sqlite3ExprListDelete(db, pOrderBy);
          611  +  sqlite3ExprDelete(db, pLimit);
          612  +  sqlite3ExprDelete(db, pOffset);
          613  +#endif
   572    614     sqlite3DbFree(db, aToOpen);
   573    615     return;
   574    616   }
   575    617   /* Make sure "isView" and other macros defined above are undefined. Otherwise
   576    618   ** they may interfere with compilation of other functions in this file
   577    619   ** (or in another file, if this file becomes part of the amalgamation).  */
   578    620   #ifdef isView

Changes to src/fkey.c.

   721    721         }
   722    722         if( !p ) return;
   723    723         iSkip = sqlite3VdbeMakeLabel(v);
   724    724         sqlite3VdbeAddOp2(v, OP_FkIfZero, 1, iSkip); VdbeCoverage(v);
   725    725       }
   726    726   
   727    727       pParse->disableTriggers = 1;
   728         -    sqlite3DeleteFrom(pParse, sqlite3SrcListDup(db, pName, 0), 0);
          728  +    sqlite3DeleteFrom(pParse, sqlite3SrcListDup(db, pName, 0), 0, 0, 0, 0);
   729    729       pParse->disableTriggers = 0;
   730    730   
   731    731       /* If the DELETE has generated immediate foreign key constraint 
   732    732       ** violations, halt the VDBE and return an error at this point, before
   733    733       ** any modifications to the schema are made. This is because statement
   734    734       ** transactions are not able to rollback schema changes.  
   735    735       **

Changes to src/parse.y.

   764    764   /////////////////////////// The DELETE statement /////////////////////////////
   765    765   //
   766    766   %ifdef SQLITE_ENABLE_UPDATE_DELETE_LIMIT
   767    767   cmd ::= with(C) DELETE FROM fullname(X) indexed_opt(I) where_opt(W) 
   768    768           orderby_opt(O) limit_opt(L). {
   769    769     sqlite3WithPush(pParse, C, 1);
   770    770     sqlite3SrcListIndexedBy(pParse, X, &I);
   771         -  W = sqlite3LimitWhere(pParse, X, W, O, L.pLimit, L.pOffset, "DELETE");
   772         -  sqlite3DeleteFrom(pParse,X,W);
          771  +  sqlite3DeleteFrom(pParse,X,W,O,L.pLimit,L.pOffset); 
   773    772   }
   774    773   %endif
   775    774   %ifndef SQLITE_ENABLE_UPDATE_DELETE_LIMIT
   776    775   cmd ::= with(C) DELETE FROM fullname(X) indexed_opt(I) where_opt(W). {
   777    776     sqlite3WithPush(pParse, C, 1);
   778    777     sqlite3SrcListIndexedBy(pParse, X, &I);
   779         -  sqlite3DeleteFrom(pParse,X,W);
          778  +  sqlite3DeleteFrom(pParse,X,W,0,0,0);
   780    779   }
   781    780   %endif
   782    781   
   783    782   %type where_opt {Expr*}
   784    783   %destructor where_opt {sqlite3ExprDelete(pParse->db, $$);}
   785    784   
   786    785   where_opt(A) ::= .                    {A = 0;}
................................................................................
   790    789   //
   791    790   %ifdef SQLITE_ENABLE_UPDATE_DELETE_LIMIT
   792    791   cmd ::= with(C) UPDATE orconf(R) fullname(X) indexed_opt(I) SET setlist(Y)
   793    792           where_opt(W) orderby_opt(O) limit_opt(L).  {
   794    793     sqlite3WithPush(pParse, C, 1);
   795    794     sqlite3SrcListIndexedBy(pParse, X, &I);
   796    795     sqlite3ExprListCheckLength(pParse,Y,"set list"); 
   797         -  W = sqlite3LimitWhere(pParse, X, W, O, L.pLimit, L.pOffset, "UPDATE");
   798         -  sqlite3Update(pParse,X,Y,W,R);
          796  +  sqlite3Update(pParse,X,Y,W,R,O,L.pLimit,L.pOffset);
   799    797   }
   800    798   %endif
   801    799   %ifndef SQLITE_ENABLE_UPDATE_DELETE_LIMIT
   802    800   cmd ::= with(C) UPDATE orconf(R) fullname(X) indexed_opt(I) SET setlist(Y)
   803    801           where_opt(W).  {
   804    802     sqlite3WithPush(pParse, C, 1);
   805    803     sqlite3SrcListIndexedBy(pParse, X, &I);
   806    804     sqlite3ExprListCheckLength(pParse,Y,"set list"); 
   807         -  sqlite3Update(pParse,X,Y,W,R);
          805  +  sqlite3Update(pParse,X,Y,W,R,0,0,0);
   808    806   }
   809    807   %endif
   810    808   
   811    809   %type setlist {ExprList*}
   812    810   %destructor setlist {sqlite3ExprListDelete(pParse->db, $$);}
   813    811   
   814    812   setlist(A) ::= setlist(A) COMMA nm(X) EQ expr(Y). {

Changes to src/resolve.c.

   592    592       ** column in the FROM clause.  This is used by the LIMIT and ORDER BY
   593    593       ** clause processing on UPDATE and DELETE statements.
   594    594       */
   595    595       case TK_ROW: {
   596    596         SrcList *pSrcList = pNC->pSrcList;
   597    597         struct SrcList_item *pItem;
   598    598         assert( pSrcList && pSrcList->nSrc==1 );
   599         -      pItem = pSrcList->a; 
          599  +      pItem = pSrcList->a;
          600  +      assert( HasRowid(pItem->pTab) && pItem->pTab->pSelect==0 );
   600    601         pExpr->op = TK_COLUMN;
   601    602         pExpr->pTab = pItem->pTab;
   602    603         pExpr->iTable = pItem->iCursor;
   603    604         pExpr->iColumn = -1;
   604    605         pExpr->affinity = SQLITE_AFF_INTEGER;
   605    606         break;
   606    607       }

Changes to src/sqlite.h.in.

  1127   1127   ** CAPI3REF: OS Interface Object
  1128   1128   **
  1129   1129   ** An instance of the sqlite3_vfs object defines the interface between
  1130   1130   ** the SQLite core and the underlying operating system.  The "vfs"
  1131   1131   ** in the name of the object stands for "virtual file system".  See
  1132   1132   ** the [VFS | VFS documentation] for further information.
  1133   1133   **
  1134         -** The value of the iVersion field is initially 1 but may be larger in
  1135         -** future versions of SQLite.  Additional fields may be appended to this
  1136         -** object when the iVersion value is increased.  Note that the structure
  1137         -** of the sqlite3_vfs object changes in the transaction between
  1138         -** SQLite version 3.5.9 and 3.6.0 and yet the iVersion field was not
  1139         -** modified.
         1134  +** The VFS interface is sometimes extended by adding new methods onto
         1135  +** the end.  Each time such an extension occurs, the iVersion field
         1136  +** is incremented.  The iVersion value started out as 1 in
         1137  +** SQLite [version 3.5.0] on [dateof:3.5.0], then increased to 2
         1138  +** with SQLite [version 3.7.0] on [dateof:3.7.0], and then increased
         1139  +** to 3 with SQLite [version 3.7.6] on [dateof:3.7.6].  Additional fields
         1140  +** may be appended to the sqlite3_vfs object and the iVersion value
         1141  +** may increase again in future versions of SQLite.
         1142  +** Note that the structure
         1143  +** of the sqlite3_vfs object changes in the transition from
         1144  +** SQLite [version 3.5.9] to [version 3.6.0] on [dateof:3.6.0]
         1145  +** and yet the iVersion field was not modified.
  1140   1146   **
  1141   1147   ** The szOsFile field is the size of the subclassed [sqlite3_file]
  1142   1148   ** structure used by this VFS.  mxPathname is the maximum length of
  1143   1149   ** a pathname in this VFS.
  1144   1150   **
  1145   1151   ** Registered sqlite3_vfs objects are kept on a linked list formed by
  1146   1152   ** the pNext pointer.  The [sqlite3_vfs_register()]

Changes to src/sqliteInt.h.

  3761   3761   void sqlite3SelectDelete(sqlite3*, Select*);
  3762   3762   Table *sqlite3SrcListLookup(Parse*, SrcList*);
  3763   3763   int sqlite3IsReadOnly(Parse*, Table*, int);
  3764   3764   void sqlite3OpenTable(Parse*, int iCur, int iDb, Table*, int);
  3765   3765   #if defined(SQLITE_ENABLE_UPDATE_DELETE_LIMIT) && !defined(SQLITE_OMIT_SUBQUERY)
  3766   3766   Expr *sqlite3LimitWhere(Parse*,SrcList*,Expr*,ExprList*,Expr*,Expr*,char*);
  3767   3767   #endif
  3768         -void sqlite3DeleteFrom(Parse*, SrcList*, Expr*);
  3769         -void sqlite3Update(Parse*, SrcList*, ExprList*, Expr*, int);
         3768  +void sqlite3DeleteFrom(Parse*, SrcList*, Expr*, ExprList*, Expr*, Expr*);
         3769  +void sqlite3Update(Parse*, SrcList*, ExprList*,Expr*,int,ExprList*,Expr*,Expr*);
  3770   3770   WhereInfo *sqlite3WhereBegin(Parse*,SrcList*,Expr*,ExprList*,ExprList*,u16,int);
  3771   3771   void sqlite3WhereEnd(WhereInfo*);
  3772   3772   LogEst sqlite3WhereOutputRowCount(WhereInfo*);
  3773   3773   int sqlite3WhereIsDistinct(WhereInfo*);
  3774   3774   int sqlite3WhereIsOrdered(WhereInfo*);
  3775   3775   int sqlite3WhereOrderedInnerLoop(WhereInfo*);
  3776   3776   int sqlite3WhereIsSorted(WhereInfo*);
................................................................................
  3886   3886   void sqlite3RegisterDateTimeFunctions(void);
  3887   3887   void sqlite3RegisterPerConnectionBuiltinFunctions(sqlite3*);
  3888   3888   int sqlite3SafetyCheckOk(sqlite3*);
  3889   3889   int sqlite3SafetyCheckSickOrOk(sqlite3*);
  3890   3890   void sqlite3ChangeCookie(Parse*, int);
  3891   3891   
  3892   3892   #if !defined(SQLITE_OMIT_VIEW) && !defined(SQLITE_OMIT_TRIGGER)
  3893         -void sqlite3MaterializeView(Parse*, Table*, Expr*, int);
         3893  +void sqlite3MaterializeView(Parse*, Table*, Expr*, ExprList*,Expr*,Expr*,int);
  3894   3894   #endif
  3895   3895   
  3896   3896   #ifndef SQLITE_OMIT_TRIGGER
  3897   3897     void sqlite3BeginTrigger(Parse*, Token*,Token*,int,int,IdList*,SrcList*,
  3898   3898                              Expr*,int, int);
  3899   3899     void sqlite3FinishTrigger(Parse*, TriggerStep*, Token*);
  3900   3900     void sqlite3DropTrigger(Parse*, SrcList*, int);

Changes to src/trigger.c.

   707    707   
   708    708       switch( pStep->op ){
   709    709         case TK_UPDATE: {
   710    710           sqlite3Update(pParse, 
   711    711             targetSrcList(pParse, pStep),
   712    712             sqlite3ExprListDup(db, pStep->pExprList, 0), 
   713    713             sqlite3ExprDup(db, pStep->pWhere, 0), 
   714         -          pParse->eOrconf
          714  +          pParse->eOrconf, 0, 0, 0
   715    715           );
   716    716           break;
   717    717         }
   718    718         case TK_INSERT: {
   719    719           sqlite3Insert(pParse, 
   720    720             targetSrcList(pParse, pStep),
   721    721             sqlite3SelectDup(db, pStep->pSelect, 0), 
................................................................................
   723    723             pParse->eOrconf
   724    724           );
   725    725           break;
   726    726         }
   727    727         case TK_DELETE: {
   728    728           sqlite3DeleteFrom(pParse, 
   729    729             targetSrcList(pParse, pStep),
   730         -          sqlite3ExprDup(db, pStep->pWhere, 0)
          730  +          sqlite3ExprDup(db, pStep->pWhere, 0), 0, 0, 0
   731    731           );
   732    732           break;
   733    733         }
   734    734         default: assert( pStep->op==TK_SELECT ); {
   735    735           SelectDest sDest;
   736    736           Select *pSelect = sqlite3SelectDup(db, pStep->pSelect, 0);
   737    737           sqlite3SelectDestInit(&sDest, SRT_Discard, 0);

Changes to src/update.c.

    87     87   *            onError   pTabList      pChanges             pWhere
    88     88   */
    89     89   void sqlite3Update(
    90     90     Parse *pParse,         /* The parser context */
    91     91     SrcList *pTabList,     /* The table in which we should change things */
    92     92     ExprList *pChanges,    /* Things to be changed */
    93     93     Expr *pWhere,          /* The WHERE clause.  May be null */
    94         -  int onError            /* How to handle constraint errors */
           94  +  int onError,           /* How to handle constraint errors */
           95  +  ExprList *pOrderBy,    /* ORDER BY clause. May be null */
           96  +  Expr *pLimit,          /* LIMIT clause. May be null */
           97  +  Expr *pOffset          /* OFFSET clause. May be null */
    95     98   ){
    96     99     int i, j;              /* Loop counters */
    97    100     Table *pTab;           /* The table to be updated */
    98    101     int addrTop = 0;       /* VDBE instruction address of the start of the loop */
    99    102     WhereInfo *pWInfo;     /* Information about the WHERE clause */
   100    103     Vdbe *v;               /* The virtual database engine */
   101    104     Index *pIdx;           /* For looping over indices */
................................................................................
   171    174   # define isView 0
   172    175   # define tmask 0
   173    176   #endif
   174    177   #ifdef SQLITE_OMIT_VIEW
   175    178   # undef isView
   176    179   # define isView 0
   177    180   #endif
          181  +
          182  +#ifdef SQLITE_ENABLE_UPDATE_DELETE_LIMIT
          183  +  if( !isView ){
          184  +    pWhere = sqlite3LimitWhere(
          185  +        pParse, pTabList, pWhere, pOrderBy, pLimit, pOffset, "UPDATE"
          186  +    );
          187  +    pOrderBy = 0;
          188  +    pLimit = pOffset = 0;
          189  +  }
          190  +#endif
   178    191   
   179    192     if( sqlite3ViewGetColumnNames(pParse, pTab) ){
   180    193       goto update_cleanup;
   181    194     }
   182    195     if( sqlite3IsReadOnly(pParse, pTab, tmask) ){
   183    196       goto update_cleanup;
   184    197     }
................................................................................
   340    353     }
   341    354   
   342    355     /* If we are trying to update a view, realize that view into
   343    356     ** an ephemeral table.
   344    357     */
   345    358   #if !defined(SQLITE_OMIT_VIEW) && !defined(SQLITE_OMIT_TRIGGER)
   346    359     if( isView ){
   347         -    sqlite3MaterializeView(pParse, pTab, pWhere, iDataCur);
          360  +    sqlite3MaterializeView(pParse, pTab, 
          361  +        pWhere, pOrderBy, pLimit, pOffset, iDataCur
          362  +    );
          363  +    pOrderBy = 0;
          364  +    pLimit = pOffset = 0;
   348    365     }
   349    366   #endif
   350    367   
   351    368     /* Resolve the column names in all the expressions in the
   352    369     ** WHERE clause.
   353    370     */
   354    371     if( sqlite3ResolveExprNames(&sNC, pWhere) ){
................................................................................
   724    741   
   725    742   update_cleanup:
   726    743     sqlite3AuthContextPop(&sContext);
   727    744     sqlite3DbFree(db, aXRef); /* Also frees aRegIdx[] and aToOpen[] */
   728    745     sqlite3SrcListDelete(db, pTabList);
   729    746     sqlite3ExprListDelete(db, pChanges);
   730    747     sqlite3ExprDelete(db, pWhere);
          748  +#if defined(SQLITE_ENABLE_UPDATE_DELETE_LIMIT) 
          749  +  sqlite3ExprListDelete(db, pOrderBy);
          750  +  sqlite3ExprDelete(db, pLimit);
          751  +  sqlite3ExprDelete(db, pOffset);
          752  +#endif
   731    753     return;
   732    754   }
   733    755   /* Make sure "isView" and other macros defined above are undefined. Otherwise
   734    756   ** they may interfere with compilation of other functions in this file
   735    757   ** (or in another file, if this file becomes part of the amalgamation).  */
   736    758   #ifdef isView
   737    759    #undef isView

Changes to src/where.c.

  2865   2865           pNew->prereq = mPrereq | pTerm->prereqRight;
  2866   2866           rc = whereLoopInsert(pBuilder, pNew);
  2867   2867         }
  2868   2868       }
  2869   2869     }
  2870   2870   #endif /* SQLITE_OMIT_AUTOMATIC_INDEX */
  2871   2871   
  2872         -  /* Loop over all indices
  2873         -  */
  2874         -  for(; rc==SQLITE_OK && pProbe; pProbe=pProbe->pNext, iSortIdx++){
         2872  +  /* Loop over all indices. If there was an INDEXED BY clause, then only 
         2873  +  ** consider index pProbe.  */
         2874  +  for(; rc==SQLITE_OK && pProbe; 
         2875  +      pProbe=(pSrc->pIBIndex ? 0 : pProbe->pNext), iSortIdx++
         2876  +  ){
  2875   2877       if( pProbe->pPartIdxWhere!=0
  2876   2878        && !whereUsablePartialIndex(pSrc->iCursor, pWC, pProbe->pPartIdxWhere) ){
  2877   2879         testcase( pNew->iTab!=pSrc->iCursor );  /* See ticket [98d973b8f5] */
  2878   2880         continue;  /* Partial index inappropriate for this query */
  2879   2881       }
  2880   2882       rSize = pProbe->aiRowLogEst[0];
  2881   2883       pNew->u.btree.nEq = 0;
................................................................................
  2977   2979         pTab->tabFlags |= TF_StatsUsed;
  2978   2980       }
  2979   2981   #ifdef SQLITE_ENABLE_STAT3_OR_STAT4
  2980   2982       sqlite3Stat4ProbeFree(pBuilder->pRec);
  2981   2983       pBuilder->nRecValid = 0;
  2982   2984       pBuilder->pRec = 0;
  2983   2985   #endif
  2984         -
  2985         -    /* If there was an INDEXED BY clause, then only that one index is
  2986         -    ** considered. */
  2987         -    if( pSrc->pIBIndex ) break;
  2988   2986     }
  2989   2987     return rc;
  2990   2988   }
  2991   2989   
  2992   2990   #ifndef SQLITE_OMIT_VIRTUALTABLE
  2993   2991   
  2994   2992   /*

Changes to test/indexedby.test.

   359    359   } {1 1 3}
   360    360   do_execsql_test 11.9 {
   361    361     SELECT a,b,c FROM x2 INDEXED BY x2i WHERE a=1 AND b=1 AND c='3.0';
   362    362   } {1 1 3}
   363    363   do_eqp_test 11.10 {
   364    364     SELECT a,b,c FROM x2 INDEXED BY x2i WHERE a=1 AND b=1 AND c='3.0';
   365    365   } {0 0 0 {SEARCH TABLE x2 USING COVERING INDEX x2i (a=? AND b=? AND rowid=?)}}
          366  +
          367  +#-------------------------------------------------------------------------
          368  +# Check INDEXED BY works (throws an exception) with partial indexes that 
          369  +# cannot be used.
          370  +do_execsql_test 12.1 {
          371  +  CREATE TABLE o1(x INTEGER PRIMARY KEY, y, z);
          372  +  CREATE INDEX p1 ON o1(z);
          373  +  CREATE INDEX p2 ON o1(y) WHERE z=1;
          374  +}
          375  +do_catchsql_test 12.2 {
          376  +  SELECT * FROM o1 INDEXED BY p2 ORDER BY 1;
          377  +} {1 {no query solution}}
          378  +do_execsql_test 12.3 {
          379  +  DROP INDEX p1;
          380  +  DROP INDEX p2;
          381  +  CREATE INDEX p2 ON o1(y) WHERE z=1;
          382  +  CREATE INDEX p1 ON o1(z);
          383  +}
          384  +do_catchsql_test 12.4 {
          385  +  SELECT * FROM o1 INDEXED BY p2 ORDER BY 1;
          386  +} {1 {no query solution}}
   366    387   
   367    388   finish_test

Added test/wherelfault.test.

            1  +# 2008 October 6
            2  +#
            3  +# The author disclaims copyright to this source code.  In place of
            4  +# a legal notice, here is a blessing:
            5  +#
            6  +#    May you do good and not evil.
            7  +#    May you find forgiveness for yourself and forgive others.
            8  +#    May you share freely, never taking more than you give.
            9  +#
           10  +#***********************************************************************
           11  +# This file implements regression tests for SQLite library.  The
           12  +# focus of this file is testing fault-injection with the 
           13  +# LIMIT ... OFFSET ... clause of UPDATE and DELETE statements.
           14  +#
           15  +
           16  +set testdir [file dirname $argv0]
           17  +source $testdir/tester.tcl
           18  +source $testdir/malloc_common.tcl
           19  +set testprefix wherelfault
           20  +
           21  +ifcapable !update_delete_limit {
           22  +  finish_test
           23  +  return
           24  +}
           25  +
           26  +do_execsql_test 1.0 {
           27  +  CREATE TABLE t1(a, b);
           28  +  INSERT INTO t1 VALUES(1, 'f');
           29  +  INSERT INTO t1 VALUES(2, 'e');
           30  +  INSERT INTO t1 VALUES(3, 'd');
           31  +  INSERT INTO t1 VALUES(4, 'c');
           32  +  INSERT INTO t1 VALUES(5, 'b');
           33  +  INSERT INTO t1 VALUES(6, 'a');
           34  +
           35  +  CREATE VIEW v1 AS SELECT a,b FROM t1;
           36  +  CREATE TABLE log(op, a);
           37  +
           38  +  CREATE TRIGGER v1del INSTEAD OF DELETE ON v1 BEGIN
           39  +    INSERT INTO log VALUES('delete', old.a);
           40  +  END;
           41  +
           42  +  CREATE TRIGGER v1upd INSTEAD OF UPDATE ON v1 BEGIN
           43  +    INSERT INTO log VALUES('update', old.a);
           44  +  END;
           45  +}
           46  +
           47  +faultsim_save_and_close
           48  +do_faultsim_test 1.1 -prep {
           49  +  faultsim_restore_and_reopen
           50  +  db eval {SELECT * FROM sqlite_master}
           51  +} -body {
           52  +  execsql { DELETE FROM v1 ORDER BY a LIMIT 3; }
           53  +} -test {
           54  +  faultsim_test_result {0 {}} 
           55  +}
           56  +
           57  +do_faultsim_test 1.2 -prep {
           58  +  faultsim_restore_and_reopen
           59  +  db eval {SELECT * FROM sqlite_master}
           60  +} -body {
           61  +  execsql { UPDATE v1 SET b = 555 ORDER BY a LIMIT 3 }
           62  +} -test {
           63  +  faultsim_test_result {0 {}} 
           64  +}
           65  +
           66  +#-------------------------------------------------------------------------
           67  +sqlite3 db test.db
           68  +do_execsql_test 2.1.0 {
           69  +  CREATE TABLE t2(a, b, c, PRIMARY KEY(a, b)) WITHOUT ROWID;
           70  +}
           71  +faultsim_save_and_close
           72  +
           73  +do_faultsim_test 2.1 -prep {
           74  +  faultsim_restore_and_reopen
           75  +  db eval {SELECT * FROM sqlite_master}
           76  +} -body {
           77  +  execsql { DELETE FROM t2 WHERE c=? ORDER BY a DESC LIMIT 10 }
           78  +} -test {
           79  +  faultsim_test_result {0 {}} 
           80  +}
           81  +
           82  +finish_test

Changes to test/wherelimit.test.

    34     34       COMMIT;
    35     35     }
    36     36     return {}
    37     37   }
    38     38   
    39     39   ifcapable {update_delete_limit} {
    40     40   
           41  +  execsql { CREATE TABLE t1(x, y) }
           42  +
    41     43     # check syntax error support
    42     44     do_test wherelimit-0.1 {
    43     45       catchsql {DELETE FROM t1 ORDER BY x}
    44     46     } {1 {ORDER BY without LIMIT on DELETE}}
    45     47     do_test wherelimit-0.2 {
    46     48       catchsql {DELETE FROM t1 WHERE x=1 ORDER BY x}
    47     49     } {1 {ORDER BY without LIMIT on DELETE}}
    48     50     do_test wherelimit-0.3 {
    49     51       catchsql {UPDATE t1 SET y=1 WHERE x=1 ORDER BY x}
    50     52     } {1 {ORDER BY without LIMIT on UPDATE}}
           53  +
           54  +  execsql { DROP TABLE t1 }
    51     55   
    52     56     # no AS on table sources
    53     57     do_test wherelimit-0.4 {
    54     58       catchsql {DELETE FROM t1 AS a WHERE x=1}
    55     59     } {1 {near "AS": syntax error}}
    56     60     do_test wherelimit-0.5 {
    57     61       catchsql {UPDATE t1 AS a SET y=1 WHERE x=1}
................................................................................
   274    278       execsql {UPDATE t1 SET y=1 WHERE x=2 ORDER BY x LIMIT 30, 50}
   275    279       execsql {SELECT count(*) FROM t1 WHERE y=1}
   276    280     } {6}
   277    281     do_test wherelimit-3.13 {
   278    282       execsql {UPDATE t1 SET y=1 WHERE x=3 ORDER BY x LIMIT 50 OFFSET 50}
   279    283       execsql {SELECT count(*) FROM t1 WHERE y=1}
   280    284     } {6}
          285  +
          286  +  # Cannot use a LIMIT for UPDATE or DELETE against a WITHOUT ROWID table
          287  +  # or a VIEW.  (We should fix this someday).
          288  +  #
          289  +  db close
          290  +  sqlite3 db :memory:
          291  +  do_execsql_test wherelimit-4.1 {
          292  +    CREATE TABLE t1(a int);
          293  +    INSERT INTO t1 VALUES(1);
          294  +    INSERT INTO t1 VALUES(2);
          295  +    INSERT INTO t1 VALUES(3);
          296  +    CREATE TABLE t2(a int);
          297  +    INSERT INTO t2 SELECT a+100 FROM t1;
          298  +    CREATE VIEW tv(r,a) AS
          299  +       SELECT rowid, a FROM t2 UNION ALL SELECT rowid, a FROM t1;
          300  +    CREATE TRIGGER tv_del INSTEAD OF DELETE ON tv
          301  +    BEGIN
          302  +      DELETE FROM t1 WHERE rowid=old.r;
          303  +      DELETE FROM t2 WHERE rowid=old.r;
          304  +    END;
          305  +  } {}
          306  +  do_catchsql_test wherelimit-4.2 {
          307  +    DELETE FROM tv WHERE 1 LIMIT 2;
          308  +  } {0 {}}
          309  +  do_catchsql_test wherelimit-4.3 {
          310  +    DELETE FROM tv WHERE 1 ORDER BY a LIMIT 2;
          311  +  } {0 {}}
          312  +  do_execsql_test wherelimit-4.10 {
          313  +    CREATE TABLE t3(a,b,c,d TEXT, PRIMARY KEY(a,b)) WITHOUT ROWID;
          314  +    INSERT INTO t3(a,b,c,d) VALUES(1,2,3,4),(5,6,7,8),(9,10,11,12);
          315  +  } {}
          316  +  do_catchsql_test wherelimit-4.11 {
          317  +    DELETE FROM t3 WHERE a=5 LIMIT 2;
          318  +  } {0 {}}
          319  +  do_execsql_test wherelimit-4.12 {
          320  +    SELECT a,b,c,d FROM t3 ORDER BY 1;
          321  +  } {1 2 3 4 9 10 11 12}
   281    322   
   282    323   }
   283    324   
   284    325   finish_test

Added test/wherelimit2.test.

            1  +# 2008 October 6
            2  +#
            3  +# The author disclaims copyright to this source code.  In place of
            4  +# a legal notice, here is a blessing:
            5  +#
            6  +#    May you do good and not evil.
            7  +#    May you find forgiveness for yourself and forgive others.
            8  +#    May you share freely, never taking more than you give.
            9  +#
           10  +#***********************************************************************
           11  +# This file implements regression tests for SQLite library.  The
           12  +# focus of this file is testing the LIMIT ... OFFSET ... clause
           13  +#  of UPDATE and DELETE statements.
           14  +#
           15  +
           16  +set testdir [file dirname $argv0]
           17  +source $testdir/tester.tcl
           18  +set testprefix wherelimit2
           19  +
           20  +ifcapable !update_delete_limit {
           21  +  finish_test
           22  +  return
           23  +}
           24  +
           25  +#-------------------------------------------------------------------------
           26  +# Test with views and INSTEAD OF triggers.
           27  +#
           28  +do_execsql_test 1.0 {
           29  +  CREATE TABLE t1(a, b);
           30  +  INSERT INTO t1 VALUES(1, 'f');
           31  +  INSERT INTO t1 VALUES(2, 'e');
           32  +  INSERT INTO t1 VALUES(3, 'd');
           33  +  INSERT INTO t1 VALUES(4, 'c');
           34  +  INSERT INTO t1 VALUES(5, 'b');
           35  +  INSERT INTO t1 VALUES(6, 'a');
           36  +
           37  +  CREATE VIEW v1 AS SELECT a,b FROM t1;
           38  +  CREATE TABLE log(op, a);
           39  +
           40  +  CREATE TRIGGER v1del INSTEAD OF DELETE ON v1 BEGIN
           41  +    INSERT INTO log VALUES('delete', old.a);
           42  +  END;
           43  +
           44  +  CREATE TRIGGER v1upd INSTEAD OF UPDATE ON v1 BEGIN
           45  +    INSERT INTO log VALUES('update', old.a);
           46  +  END;
           47  +}
           48  +
           49  +do_execsql_test 1.1 {
           50  +  DELETE FROM v1 ORDER BY a LIMIT 3;
           51  +  SELECT * FROM log; DELETE FROM log;
           52  +} {
           53  +  delete 1 delete 2 delete 3
           54  +}
           55  +do_execsql_test 1.2 {
           56  +  DELETE FROM v1 ORDER BY b LIMIT 3;
           57  +  SELECT * FROM log; DELETE FROM log;
           58  +} {
           59  +  delete 6 delete 5 delete 4
           60  +}
           61  +do_execsql_test 1.3 {
           62  +  UPDATE v1 SET b = 555 ORDER BY a LIMIT 3;
           63  +  SELECT * FROM log; DELETE FROM log;
           64  +} {
           65  +  update 1 update 2 update 3
           66  +}
           67  +do_execsql_test 1.4 {
           68  +  UPDATE v1 SET b = 555 ORDER BY b LIMIT 3;
           69  +  SELECT * FROM log; DELETE FROM log;
           70  +} {
           71  +  update 6 update 5 update 4
           72  +}
           73  +
           74  +#-------------------------------------------------------------------------
           75  +# Simple test using WITHOUT ROWID table.
           76  +#
           77  +do_execsql_test 2.1.0 {
           78  +  CREATE TABLE t2(a, b, c, PRIMARY KEY(a, b)) WITHOUT ROWID;
           79  +  INSERT INTO t2 VALUES(1, 1, 'h');
           80  +  INSERT INTO t2 VALUES(1, 2, 'g');
           81  +  INSERT INTO t2 VALUES(2, 1, 'f');
           82  +  INSERT INTO t2 VALUES(2, 2, 'e');
           83  +  INSERT INTO t2 VALUES(3, 1, 'd');
           84  +  INSERT INTO t2 VALUES(3, 2, 'c');
           85  +  INSERT INTO t2 VALUES(4, 1, 'b');
           86  +  INSERT INTO t2 VALUES(4, 2, 'a');
           87  +}
           88  +
           89  +do_execsql_test 2.1.1 {
           90  +  BEGIN;
           91  +    DELETE FROM t2 WHERE b=1 ORDER BY c LIMIT 2;
           92  +    SELECT c FROM t2 ORDER BY 1;
           93  +  ROLLBACK;
           94  +} {a c e f g h}
           95  +
           96  +do_execsql_test 2.1.2 {
           97  +  BEGIN;
           98  +    UPDATE t2 SET c=NULL ORDER BY a, b DESC LIMIT 3 OFFSET 1;
           99  +    SELECT a, b, c FROM t2;
          100  +  ROLLBACK;
          101  +} {
          102  +  1 1 {} 
          103  +  1 2 g 
          104  +  2 1 {} 
          105  +  2 2 {} 
          106  +  3 1 d 
          107  +  3 2 c 
          108  +  4 1 b 
          109  +  4 2 a
          110  +}
          111  +
          112  +do_execsql_test 2.2.0 {
          113  +  DROP TABLE t2;
          114  +  CREATE TABLE t2(a INTEGER PRIMARY KEY, b, c) WITHOUT ROWID;
          115  +  INSERT INTO t2 VALUES(1, 1, 'h');
          116  +  INSERT INTO t2 VALUES(2, 2, 'g');
          117  +  INSERT INTO t2 VALUES(3, 1, 'f');
          118  +  INSERT INTO t2 VALUES(4, 2, 'e');
          119  +  INSERT INTO t2 VALUES(5, 1, 'd');
          120  +  INSERT INTO t2 VALUES(6, 2, 'c');
          121  +  INSERT INTO t2 VALUES(7, 1, 'b');
          122  +  INSERT INTO t2 VALUES(8, 2, 'a');
          123  +}
          124  +
          125  +do_execsql_test 2.2.1 {
          126  +  BEGIN;
          127  +    DELETE FROM t2 WHERE b=1 ORDER BY c LIMIT 2;
          128  +    SELECT c FROM t2 ORDER BY 1;
          129  +  ROLLBACK;
          130  +} {a c e f g h}
          131  +
          132  +do_execsql_test 2.2.2 {
          133  +  BEGIN;
          134  +    UPDATE t2 SET c=NULL ORDER BY a DESC LIMIT 3 OFFSET 1;
          135  +    SELECT a, b, c FROM t2;
          136  +  ROLLBACK;
          137  +} {
          138  +  1 1 h
          139  +  2 2 g 
          140  +  3 1 f
          141  +  4 2 e
          142  +  5 1 {}
          143  +  6 2 {} 
          144  +  7 1 {} 
          145  +  8 2 a
          146  +}
          147  +
          148  +#-------------------------------------------------------------------------
          149  +# Test using a virtual table
          150  +#
          151  +ifcapable fts5 {
          152  +  do_execsql_test 3.0 {
          153  +    CREATE VIRTUAL TABLE ft USING fts5(x);
          154  +    INSERT INTO ft(rowid, x) VALUES(-45,   'a a');
          155  +    INSERT INTO ft(rowid, x) VALUES(12,    'a b');
          156  +    INSERT INTO ft(rowid, x) VALUES(444,   'a c');
          157  +    INSERT INTO ft(rowid, x) VALUES(12300, 'a d');
          158  +    INSERT INTO ft(rowid, x) VALUES(25400, 'a c');
          159  +    INSERT INTO ft(rowid, x) VALUES(25401, 'a b');
          160  +    INSERT INTO ft(rowid, x) VALUES(50000, 'a a');
          161  +  }
          162  +
          163  +  do_execsql_test 3.1.1 {
          164  +    BEGIN;
          165  +      DELETE FROM ft ORDER BY rowid LIMIT 3;
          166  +      SELECT x FROM ft;
          167  +    ROLLBACK;
          168  +  } {{a d} {a c} {a b} {a a}}
          169  +
          170  +  do_execsql_test 3.1.2 {
          171  +    BEGIN;
          172  +      DELETE FROM ft WHERE ft MATCH 'a' ORDER BY rowid LIMIT 3;
          173  +      SELECT x FROM ft;
          174  +    ROLLBACK;
          175  +  } {{a d} {a c} {a b} {a a}}
          176  +  
          177  +  do_execsql_test 3.1.3 {
          178  +    BEGIN;
          179  +      DELETE FROM ft WHERE ft MATCH 'b' ORDER BY rowid ASC LIMIT 1 OFFSET 1;
          180  +      SELECT rowid FROM ft;
          181  +    ROLLBACK;
          182  +  } {-45 12 444 12300 25400 50000}
          183  +
          184  +  do_execsql_test 3.2.1 {
          185  +    BEGIN;
          186  +      UPDATE ft SET x='hello' ORDER BY rowid LIMIT 2 OFFSET 2;
          187  +      SELECT x FROM ft;
          188  +    ROLLBACK;
          189  +  } {{a a} {a b} hello hello {a c} {a b} {a a}}
          190  +
          191  +  do_execsql_test 3.2.2 {
          192  +    BEGIN;
          193  +      UPDATE ft SET x='hello' WHERE ft MATCH 'a' 
          194  +          ORDER BY rowid DESC LIMIT 2 OFFSET 2;
          195  +      SELECT x FROM ft;
          196  +    ROLLBACK;
          197  +  } {{a a} {a b} {a c} hello hello {a b} {a a}}
          198  +} ;# fts5
          199  +
          200  +#-------------------------------------------------------------------------
          201  +# Test using INDEXED BY clauses.
          202  +#
          203  +do_execsql_test 4.0 {
          204  +  CREATE TABLE x1(a INTEGER PRIMARY KEY, b, c, d);
          205  +  CREATE INDEX x1bc ON x1(b, c);
          206  +  INSERT INTO x1 VALUES(1,1,1,1);
          207  +  INSERT INTO x1 VALUES(2,1,2,2);
          208  +  INSERT INTO x1 VALUES(3,2,1,3);
          209  +  INSERT INTO x1 VALUES(4,2,2,3);
          210  +  INSERT INTO x1 VALUES(5,3,1,2);
          211  +  INSERT INTO x1 VALUES(6,3,2,1);
          212  +}
          213  +
          214  +do_execsql_test 4.1 {
          215  +  BEGIN;
          216  +    DELETE FROM x1 ORDER BY a LIMIT 2;
          217  +    SELECT a FROM x1;
          218  +  ROLLBACK;
          219  +} {3 4 5 6}
          220  +
          221  +do_catchsql_test 4.2 {
          222  +  DELETE FROM x1 INDEXED BY x1bc WHERE d=3 LIMIT 1;
          223  +} {1 {no query solution}}
          224  +
          225  +do_execsql_test 4.3 {
          226  +  DELETE FROM x1 INDEXED BY x1bc WHERE b=3 LIMIT 1;
          227  +  SELECT a FROM x1;
          228  +} {1 2 3 4 6}
          229  +
          230  +do_catchsql_test 4.4 {
          231  +  UPDATE x1 INDEXED BY x1bc SET d=5 WHERE d=3 LIMIT 1;
          232  +} {1 {no query solution}}
          233  +
          234  +do_execsql_test 4.5 {
          235  +  UPDATE x1 INDEXED BY x1bc SET d=5 WHERE b=2 LIMIT 1;
          236  +  SELECT a, d FROM x1;
          237  +} {1 1 2 2 3 5 4 3 6 1}
          238  +
          239  +#-------------------------------------------------------------------------
          240  +# Test using object names that require quoting.
          241  +#
          242  +do_execsql_test 5.0 {
          243  +  CREATE TABLE "x y"("a b" PRIMARY KEY, "c d") WITHOUT ROWID;
          244  +  CREATE INDEX xycd ON "x y"("c d");
          245  +
          246  +  INSERT INTO "x y" VALUES('a', 'a');
          247  +  INSERT INTO "x y" VALUES('b', 'b');
          248  +  INSERT INTO "x y" VALUES('c', 'c');
          249  +  INSERT INTO "x y" VALUES('d', 'd');
          250  +  INSERT INTO "x y" VALUES('e', 'a');
          251  +  INSERT INTO "x y" VALUES('f', 'b');
          252  +  INSERT INTO "x y" VALUES('g', 'c');
          253  +  INSERT INTO "x y" VALUES('h', 'd');
          254  +}
          255  +
          256  +do_execsql_test 5.1 {
          257  +  BEGIN;
          258  +    DELETE FROM "x y" WHERE "c d"!='e' ORDER BY "c d" LIMIT 2 OFFSET 2;
          259  +    SELECT * FROM "x y" ORDER BY 1;
          260  +  ROLLBACK;
          261  +} {
          262  +  a a c c d d e a g c h d
          263  +}
          264  +
          265  +do_execsql_test 5.2 {
          266  +  BEGIN;
          267  +    UPDATE "x y" SET "c d"='e' WHERE "c d"!='e' ORDER BY "c d" LIMIT 2 OFFSET 2;
          268  +    SELECT * FROM "x y" ORDER BY 1;
          269  +  ROLLBACK;
          270  +} {
          271  +  a a b e c c d d e a f e g c h d
          272  +}
          273  +
          274  +proc log {args} { lappend ::log {*}$args }
          275  +db func log log
          276  +do_execsql_test 5.3 {
          277  +  CREATE VIEW "v w" AS SELECT * FROM "x y";
          278  +  CREATE TRIGGER tr1 INSTEAD OF DELETE ON "v w" BEGIN
          279  +    SELECT log(old."a b", old."c d");
          280  +  END;
          281  +  CREATE TRIGGER tr2 INSTEAD OF UPDATE ON "v w" BEGIN
          282  +    SELECT log(new."a b", new."c d");
          283  +  END;
          284  +}
          285  +
          286  +do_test 5.4 {
          287  +  set ::log {}
          288  +  execsql { DELETE FROM "v w" ORDER BY "a b" LIMIT 3 }
          289  +  set ::log
          290  +} {a a b b c c}
          291  +
          292  +do_test 5.5 {
          293  +  set ::log {}
          294  +  execsql { UPDATE "v w" SET "a b" = "a b" || 'x' ORDER BY "a b" LIMIT 5; }
          295  +  set ::log
          296  +} {ax a bx b cx c dx d ex a}
          297  +
          298  +
          299  +finish_test
          300  +