/ Changes On Branch branch-3.8.9
Login

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

Changes In Branch branch-3.8.9 Excluding Merge-Ins

This is equivalent to a diff from 8a8ffc86 to 11368e67

2018-12-14
20:28
Add a missing OP_ColumnsUsed opcode to code for expressions like "? IN (SELECT ...)" in cases where expression can use an index that may contain NULL values. (Backported fix from version 3.14.0). (Leaf check-in: 11368e67 user: drh tags: branch-3.8.9)
20:20
Add the OP_ColumnsUsed opcode (when compiled with SQLITE_ENABLE_COLUMN_USED_MASK) as a hint to the b-tree layer as to which columns of a btree cursor will be used. (Backport from 3.8.11) (check-in: b29e02f8 user: drh tags: branch-3.8.9)
2016-09-14
01:43
Backport the ORDER BY LIMIT optimization to version 3.8.9. (check-in: db361482 user: drh tags: branch-3.8.9)
2016-06-16
17:14
Add a missing OP_ColumnsUsed opcode to code for expressions like "? IN (SELECT ...)" in cases where expression can use an index that may contain NULL values. (check-in: 0b1579ca user: dan tags: trunk)
2015-04-09
11:24
Remove a variable initializion to silence a harmless compiler warning. (check-in: 79861adb user: drh tags: trunk)
2015-04-08
14:01
Merge all changes for version 3.8.9 into the ota-update branch. (check-in: ec9d907a user: drh tags: ota-update)
12:47
Merge in all version 3.8.9 changes from trunk. (check-in: 30121870 user: drh tags: apple-osx)
12:16
Version 3.8.9 (check-in: 8a8ffc86 user: drh tags: trunk, release, version-3.8.9)
2015-04-07
15:39
Avoid signed integer overflow when converting oversized in-line integer widths and precisions in printf(). (check-in: 8e4ac2ce user: drh tags: trunk)

Changes to src/analyze.c.

  1615   1615            || aSample[i].anDLt[iCol]!=aSample[i+1].anDLt[iCol] 
  1616   1616           ){
  1617   1617             sumEq += aSample[i].anEq[iCol];
  1618   1618             nSum100 += 100;
  1619   1619           }
  1620   1620         }
  1621   1621   
  1622         -      if( nDist100>nSum100 ){
         1622  +      if( nDist100>nSum100 && sumEq<nRow ){
  1623   1623           avgEq = ((i64)100 * (nRow - sumEq))/(nDist100 - nSum100);
  1624   1624         }
  1625   1625         if( avgEq==0 ) avgEq = 1;
  1626   1626         pIdx->aAvgEq[iCol] = avgEq;
  1627   1627       }
  1628   1628     }
  1629   1629   }

Changes to src/build.c.

   945    945       pParse->nErr++;
   946    946       goto begin_table_error;
   947    947     }
   948    948     pTable->zName = zName;
   949    949     pTable->iPKey = -1;
   950    950     pTable->pSchema = db->aDb[iDb].pSchema;
   951    951     pTable->nRef = 1;
          952  +#ifdef SQLITE_DEFAULT_ROWEST
          953  +  pTable->nRowLogEst = sqlite3LogEst(SQLITE_DEFAULT_ROWEST);
          954  +#else
   952    955     pTable->nRowLogEst = 200; assert( 200==sqlite3LogEst(1048576) );
          956  +#endif
   953    957     assert( pParse->pNewTable==0 );
   954    958     pParse->pNewTable = pTable;
   955    959   
   956    960     /* If this is the magic sqlite_sequence table used by autoincrement,
   957    961     ** then record a pointer to this table in the main database structure
   958    962     ** so that INSERT can find the table easily.
   959    963     */

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].zName);
   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, 0, 0, 0);
          112  +  pSel = sqlite3SelectNew(pParse, 0, pFrom, pWhere, 0, 0, pOrderBy, 0, 
          113  +                          pLimit, pOffset);
   110    114     sqlite3SelectDestInit(&dest, SRT_EphemTab, iCur);
   111    115     sqlite3Select(pParse, pSel, &dest);
   112    116     sqlite3SelectDelete(db, pSel);
   113    117   }
   114    118   #endif /* !defined(SQLITE_OMIT_VIEW) && !defined(SQLITE_OMIT_TRIGGER) */
   115    119   
   116    120   #if defined(SQLITE_ENABLE_UPDATE_DELETE_LIMIT) && !defined(SQLITE_OMIT_SUBQUERY)
................................................................................
   127    131     SrcList *pSrc,               /* the FROM clause -- which tables to scan */
   128    132     Expr *pWhere,                /* The WHERE clause.  May be null */
   129    133     ExprList *pOrderBy,          /* The ORDER BY clause.  May be null */
   130    134     Expr *pLimit,                /* The LIMIT clause.  May be null */
   131    135     Expr *pOffset,               /* The OFFSET clause.  May be null */
   132    136     char *zStmtType              /* Either DELETE or UPDATE.  For err msgs. */
   133    137   ){
   134         -  Expr *pWhereRowid = NULL;    /* WHERE rowid .. */
          138  +  sqlite3 *db = pParse->db;
          139  +  Expr *pLhs = NULL;           /* LHS of IN(SELECT...) operator */
   135    140     Expr *pInClause = NULL;      /* WHERE rowid IN ( select ) */
   136         -  Expr *pSelectRowid = NULL;   /* SELECT rowid ... */
   137    141     ExprList *pEList = NULL;     /* Expression list contaning only pSelectRowid */
   138    142     SrcList *pSelectSrc = NULL;  /* SELECT rowid FROM x ... (dup of pSrc) */
   139    143     Select *pSelect = NULL;      /* Complete SELECT tree */
          144  +  Table *pTab;
   140    145   
   141    146     /* Check that there isn't an ORDER BY without a LIMIT clause.
   142    147     */
   143         -  if( pOrderBy && (pLimit == 0) ) {
          148  +  if( pOrderBy && pLimit==0 ) {
   144    149       sqlite3ErrorMsg(pParse, "ORDER BY without LIMIT on %s", zStmtType);
   145         -    goto limit_where_cleanup_2;
          150  +    goto limit_where_cleanup;
   146    151     }
   147    152   
   148    153     /* We only need to generate a select expression if there
   149    154     ** is a limit/offset term to enforce.
   150    155     */
   151    156     if( pLimit == 0 ) {
   152    157       /* if pLimit is null, pOffset will always be null as well. */
................................................................................
   158    163     ** term for the DELETE or UPDATE statement.  For example:
   159    164     **   DELETE FROM table_a WHERE col1=1 ORDER BY col2 LIMIT 1 OFFSET 1
   160    165     ** becomes:
   161    166     **   DELETE FROM table_a WHERE rowid IN ( 
   162    167     **     SELECT rowid FROM table_a WHERE col1=1 ORDER BY col2 LIMIT 1 OFFSET 1
   163    168     **   );
   164    169     */
   165         -
   166         -  pSelectRowid = sqlite3PExpr(pParse, TK_ROW, 0, 0, 0);
   167         -  if( pSelectRowid == 0 ) goto limit_where_cleanup_2;
   168         -  pEList = sqlite3ExprListAppend(pParse, 0, pSelectRowid);
   169         -  if( pEList == 0 ) goto limit_where_cleanup_2;
          170  +  pTab = pSrc->a[0].pTab;
          171  +  if( HasRowid(pTab) ){
          172  +    pLhs = sqlite3PExpr(pParse, TK_ROW, 0, 0, 0);
          173  +    pEList = sqlite3ExprListAppend(
          174  +        pParse, 0, sqlite3PExpr(pParse, TK_ROW, 0, 0, 0)
          175  +    );
          176  +  }else{
          177  +    Index *pPk = sqlite3PrimaryKeyIndex(pTab);
          178  +    if( pPk->nKeyCol==1 ){
          179  +      const char *zName = pTab->aCol[pPk->aiColumn[0]].zName;
          180  +      pLhs = sqlite3Expr(db, TK_ID, zName);
          181  +      pEList = sqlite3ExprListAppend(pParse, 0, sqlite3Expr(db, TK_ID, zName));
          182  +    }else{
          183  +      sqlite3ErrorMsg(pParse, 
          184  +          "ORDER BY and LIMIT are not supported for table %s", pTab->zName
          185  +      );
          186  +      goto limit_where_cleanup;
          187  +    }
          188  +  }
   170    189   
   171    190     /* duplicate the FROM clause as it is needed by both the DELETE/UPDATE tree
   172    191     ** and the SELECT subtree. */
          192  +  pSrc->a[0].pTab = 0;
   173    193     pSelectSrc = sqlite3SrcListDup(pParse->db, pSrc, 0);
   174         -  if( pSelectSrc == 0 ) {
   175         -    sqlite3ExprListDelete(pParse->db, pEList);
   176         -    goto limit_where_cleanup_2;
   177         -  }
          194  +  pSrc->a[0].pTab = pTab;
          195  +  pSrc->a[0].pIndex = 0;
   178    196   
   179    197     /* generate the SELECT expression tree. */
   180         -  pSelect = sqlite3SelectNew(pParse,pEList,pSelectSrc,pWhere,0,0,
   181         -                             pOrderBy,0,pLimit,pOffset);
   182         -  if( pSelect == 0 ) return 0;
          198  +  pSelect = sqlite3SelectNew(pParse, pEList, pSelectSrc, pWhere, 0 ,0, 
          199  +      pOrderBy,0,pLimit,pOffset
          200  +  );
   183    201   
   184    202     /* now generate the new WHERE rowid IN clause for the DELETE/UDPATE */
   185         -  pWhereRowid = sqlite3PExpr(pParse, TK_ROW, 0, 0, 0);
   186         -  if( pWhereRowid == 0 ) goto limit_where_cleanup_1;
   187         -  pInClause = sqlite3PExpr(pParse, TK_IN, pWhereRowid, 0, 0);
   188         -  if( pInClause == 0 ) goto limit_where_cleanup_1;
   189         -
   190         -  pInClause->x.pSelect = pSelect;
   191         -  pInClause->flags |= EP_xIsSelect;
   192         -  sqlite3ExprSetHeightAndFlags(pParse, pInClause);
          203  +  pInClause = sqlite3PExpr(pParse, TK_IN, pLhs, 0, 0);
          204  +  if( pInClause ){
          205  +    pInClause->x.pSelect = pSelect;
          206  +    ExprSetProperty(pInClause, EP_xIsSelect|EP_Subquery);
          207  +    sqlite3ExprSetHeightAndFlags(pParse, pInClause);
          208  +  }else{
          209  +    sqlite3SelectDelete(pParse->db, pSelect);
          210  +  }
   193    211     return pInClause;
   194    212   
   195         -  /* something went wrong. clean up anything allocated. */
   196         -limit_where_cleanup_1:
   197         -  sqlite3SelectDelete(pParse->db, pSelect);
   198         -  return 0;
   199         -
   200         -limit_where_cleanup_2:
          213  +limit_where_cleanup:
   201    214     sqlite3ExprDelete(pParse->db, pWhere);
   202    215     sqlite3ExprListDelete(pParse->db, pOrderBy);
   203    216     sqlite3ExprDelete(pParse->db, pLimit);
   204    217     sqlite3ExprDelete(pParse->db, pOffset);
   205    218     return 0;
   206    219   }
   207    220   #endif /* defined(SQLITE_ENABLE_UPDATE_DELETE_LIMIT) */
................................................................................
   213    226   **     DELETE FROM table_wxyz WHERE a<5 AND b NOT NULL;
   214    227   **                 \________/       \________________/
   215    228   **                  pTabList              pWhere
   216    229   */
   217    230   void sqlite3DeleteFrom(
   218    231     Parse *pParse,         /* The parser context */
   219    232     SrcList *pTabList,     /* The table from which we should delete things */
   220         -  Expr *pWhere           /* The WHERE clause.  May be null */
          233  +  Expr *pWhere,          /* The WHERE clause.  May be null */
          234  +  ExprList *pOrderBy,    /* ORDER BY clause. May be null */
          235  +  Expr *pLimit,          /* LIMIT clause. May be null */
          236  +  Expr *pOffset          /* OFFSET clause. May be null */
   221    237   ){
   222    238     Vdbe *v;               /* The virtual database engine */
   223    239     Table *pTab;           /* The table from which records will be deleted */
   224    240     const char *zDb;       /* Name of database holding pTab */
   225    241     int i;                 /* Loop counter */
   226    242     WhereInfo *pWInfo;     /* Information about the WHERE clause */
   227    243     Index *pIdx;           /* For looping over indices of the table */
................................................................................
   257    273   
   258    274     memset(&sContext, 0, sizeof(sContext));
   259    275     db = pParse->db;
   260    276     if( pParse->nErr || db->mallocFailed ){
   261    277       goto delete_from_cleanup;
   262    278     }
   263    279     assert( pTabList->nSrc==1 );
          280  +
   264    281   
   265    282     /* Locate the table which we want to delete.  This table has to be
   266    283     ** put in an SrcList structure because some of the subroutines we
   267    284     ** will be calling are designed to work with multiple tables and expect
   268    285     ** an SrcList* parameter instead of just a Table* parameter.
   269    286     */
   270    287     pTab = sqlite3SrcListLookup(pParse, pTabList);
................................................................................
   280    297   # define pTrigger 0
   281    298   # define isView 0
   282    299   #endif
   283    300   #ifdef SQLITE_OMIT_VIEW
   284    301   # undef isView
   285    302   # define isView 0
   286    303   #endif
          304  +
          305  +#ifdef SQLITE_ENABLE_UPDATE_DELETE_LIMIT
          306  +  if( !isView ){
          307  +    pWhere = sqlite3LimitWhere(
          308  +        pParse, pTabList, pWhere, pOrderBy, pLimit, pOffset, "DELETE"
          309  +    );
          310  +    pOrderBy = 0;
          311  +    pLimit = pOffset = 0;
          312  +  }
          313  +#endif
   287    314   
   288    315     /* If pTab is really a view, make sure it has been initialized.
   289    316     */
   290    317     if( sqlite3ViewGetColumnNames(pParse, pTab) ){
   291    318       goto delete_from_cleanup;
   292    319     }
   293    320   
................................................................................
   328    355     sqlite3BeginWriteOperation(pParse, 1, iDb);
   329    356   
   330    357     /* If we are trying to delete from a view, realize that view into
   331    358     ** an ephemeral table.
   332    359     */
   333    360   #if !defined(SQLITE_OMIT_VIEW) && !defined(SQLITE_OMIT_TRIGGER)
   334    361     if( isView ){
   335         -    sqlite3MaterializeView(pParse, pTab, pWhere, iTabCur);
          362  +    sqlite3MaterializeView(pParse, pTab, 
          363  +        pWhere, pOrderBy, pLimit, pOffset, iTabCur
          364  +    );
   336    365       iDataCur = iIdxCur = iTabCur;
          366  +    pOrderBy = 0;
          367  +    pLimit = pOffset = 0;
   337    368     }
   338    369   #endif
   339    370   
   340    371     /* Resolve the column names in the WHERE clause.
   341    372     */
   342    373     memset(&sNC, 0, sizeof(sNC));
   343    374     sNC.pParse = pParse;
................................................................................
   549    580       sqlite3VdbeSetColName(v, 0, COLNAME_NAME, "rows deleted", SQLITE_STATIC);
   550    581     }
   551    582   
   552    583   delete_from_cleanup:
   553    584     sqlite3AuthContextPop(&sContext);
   554    585     sqlite3SrcListDelete(db, pTabList);
   555    586     sqlite3ExprDelete(db, pWhere);
          587  +#if defined(SQLITE_ENABLE_UPDATE_DELETE_LIMIT) 
          588  +  sqlite3ExprListDelete(db, pOrderBy);
          589  +  sqlite3ExprDelete(db, pLimit);
          590  +  sqlite3ExprDelete(db, pOffset);
          591  +#endif
   556    592     sqlite3DbFree(db, aToOpen);
   557    593     return;
   558    594   }
   559    595   /* Make sure "isView" and other macros defined above are undefined. Otherwise
   560    596   ** they may interfere with compilation of other functions in this file
   561    597   ** (or in another file, if this file becomes part of the amalgamation).  */
   562    598   #ifdef isView

Changes to src/expr.c.

  1747   1747             sqlite3VdbeAddOp3(v, OP_OpenRead, iTab, pIdx->tnum, iDb);
  1748   1748             sqlite3VdbeSetP4KeyInfo(pParse, pIdx);
  1749   1749             VdbeComment((v, "%s", pIdx->zName));
  1750   1750             assert( IN_INDEX_INDEX_DESC == IN_INDEX_INDEX_ASC+1 );
  1751   1751             eType = IN_INDEX_INDEX_ASC + pIdx->aSortOrder[0];
  1752   1752   
  1753   1753             if( prRhsHasNull && !pTab->aCol[iCol].notNull ){
         1754  +#ifdef SQLITE_ENABLE_COLUMN_USED_MASK
         1755  +            const i64 sOne = 1;
         1756  +            sqlite3VdbeAddOp4Dup8(v, OP_ColumnsUsed, 
         1757  +                iTab, 0, 0, (u8*)&sOne, P4_INT64);
         1758  +#endif
  1754   1759               *prRhsHasNull = ++pParse->nMem;
  1755   1760               sqlite3SetHasNullFlag(v, iTab, *prRhsHasNull);
  1756   1761             }
  1757   1762             sqlite3VdbeJumpHere(v, iAddr);
  1758   1763           }
  1759   1764         }
  1760   1765       }
................................................................................
  2202   2207     }
  2203   2208     sqlite3ReleaseTempReg(pParse, r1);
  2204   2209     sqlite3ExprCachePop(pParse);
  2205   2210     VdbeComment((v, "end IN expr"));
  2206   2211   }
  2207   2212   #endif /* SQLITE_OMIT_SUBQUERY */
  2208   2213   
  2209         -/*
  2210         -** Duplicate an 8-byte value
  2211         -*/
  2212         -static char *dup8bytes(Vdbe *v, const char *in){
  2213         -  char *out = sqlite3DbMallocRaw(sqlite3VdbeDb(v), 8);
  2214         -  if( out ){
  2215         -    memcpy(out, in, 8);
  2216         -  }
  2217         -  return out;
  2218         -}
  2219         -
  2220   2214   #ifndef SQLITE_OMIT_FLOATING_POINT
  2221   2215   /*
  2222   2216   ** Generate an instruction that will put the floating point
  2223   2217   ** value described by z[0..n-1] into register iMem.
  2224   2218   **
  2225   2219   ** The z[] string will probably not be zero-terminated.  But the 
  2226   2220   ** z[n] character is guaranteed to be something that does not look
  2227   2221   ** like the continuation of the number.
  2228   2222   */
  2229   2223   static void codeReal(Vdbe *v, const char *z, int negateFlag, int iMem){
  2230   2224     if( ALWAYS(z!=0) ){
  2231   2225       double value;
  2232         -    char *zV;
  2233   2226       sqlite3AtoF(z, &value, sqlite3Strlen30(z), SQLITE_UTF8);
  2234   2227       assert( !sqlite3IsNaN(value) ); /* The new AtoF never returns NaN */
  2235   2228       if( negateFlag ) value = -value;
  2236         -    zV = dup8bytes(v, (char*)&value);
  2237         -    sqlite3VdbeAddOp4(v, OP_Real, 0, iMem, 0, zV, P4_REAL);
         2229  +    sqlite3VdbeAddOp4Dup8(v, OP_Real, 0, iMem, 0, (u8*)&value, P4_REAL);
  2238   2230     }
  2239   2231   }
  2240   2232   #endif
  2241   2233   
  2242   2234   
  2243   2235   /*
  2244   2236   ** Generate an instruction that will put the integer describe by
................................................................................
  2256   2248     }else{
  2257   2249       int c;
  2258   2250       i64 value;
  2259   2251       const char *z = pExpr->u.zToken;
  2260   2252       assert( z!=0 );
  2261   2253       c = sqlite3DecOrHexToI64(z, &value);
  2262   2254       if( c==0 || (c==2 && negFlag) ){
  2263         -      char *zV;
  2264   2255         if( negFlag ){ value = c==2 ? SMALLEST_INT64 : -value; }
  2265         -      zV = dup8bytes(v, (char*)&value);
  2266         -      sqlite3VdbeAddOp4(v, OP_Int64, 0, iMem, 0, zV, P4_INT64);
         2256  +      sqlite3VdbeAddOp4Dup8(v, OP_Int64, 0, iMem, 0, (u8*)&value, P4_INT64);
  2267   2257       }else{
  2268   2258   #ifdef SQLITE_OMIT_FLOATING_POINT
  2269   2259         sqlite3ErrorMsg(pParse, "oversized integer: %s%s", negFlag ? "-" : "", z);
  2270   2260   #else
  2271   2261   #ifndef SQLITE_OMIT_HEX_INTEGER
  2272   2262         if( sqlite3_strnicmp(z,"0x",2)==0 ){
  2273   2263           sqlite3ErrorMsg(pParse, "hex literal too big: %s", z);

Changes to src/fkey.c.

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

Changes to src/func.c.

  1645   1645     setLikeOptFlag(db, "like", 
  1646   1646         caseSensitive ? (SQLITE_FUNC_LIKE | SQLITE_FUNC_CASE) : SQLITE_FUNC_LIKE);
  1647   1647   }
  1648   1648   
  1649   1649   /*
  1650   1650   ** pExpr points to an expression which implements a function.  If
  1651   1651   ** it is appropriate to apply the LIKE optimization to that function
  1652         -** then set aWc[0] through aWc[2] to the wildcard characters and
  1653         -** return TRUE.  If the function is not a LIKE-style function then
  1654         -** return FALSE.
         1652  +** then set aWc[0] through aWc[2] to the wildcard characters and the
         1653  +** escape character and then return TRUE.  If the function is not a 
         1654  +** LIKE-style function then return FALSE.
         1655  +**
         1656  +** The expression "a LIKE b ESCAPE c" is only considered a valid LIKE
         1657  +** operator if c is a string literal that is exactly one byte in length.
         1658  +** That one byte is stored in aWc[3].  aWc[3] is set to zero if there is
         1659  +** no ESCAPE clause.
  1655   1660   **
  1656   1661   ** *pIsNocase is set to true if uppercase and lowercase are equivalent for
  1657   1662   ** the function (default for LIKE).  If the function makes the distinction
  1658   1663   ** between uppercase and lowercase (as does GLOB) then *pIsNocase is set to
  1659   1664   ** false.
  1660   1665   */
  1661   1666   int sqlite3IsLikeFunction(sqlite3 *db, Expr *pExpr, int *pIsNocase, char *aWc){
  1662   1667     FuncDef *pDef;
  1663         -  if( pExpr->op!=TK_FUNCTION 
  1664         -   || !pExpr->x.pList 
  1665         -   || pExpr->x.pList->nExpr!=2
  1666         -  ){
         1668  +  int nExpr;
         1669  +  if( pExpr->op!=TK_FUNCTION || !pExpr->x.pList ){
  1667   1670       return 0;
  1668   1671     }
  1669   1672     assert( !ExprHasProperty(pExpr, EP_xIsSelect) );
         1673  +  nExpr = pExpr->x.pList->nExpr;
  1670   1674     pDef = sqlite3FindFunction(db, pExpr->u.zToken, 
  1671   1675                                sqlite3Strlen30(pExpr->u.zToken),
  1672         -                             2, SQLITE_UTF8, 0);
         1676  +                             nExpr, SQLITE_UTF8, 0);
  1673   1677     if( NEVER(pDef==0) || (pDef->funcFlags & SQLITE_FUNC_LIKE)==0 ){
  1674   1678       return 0;
  1675   1679     }
         1680  +  if( nExpr<3 ){
         1681  +    aWc[3] = 0;
         1682  +  }else{
         1683  +    Expr *pEscape = pExpr->x.pList->a[2].pExpr;
         1684  +    char *zEscape;
         1685  +    if( pEscape->op!=TK_STRING ) return 0;
         1686  +    zEscape = pEscape->u.zToken;
         1687  +    if( zEscape[0]==0 || zEscape[1]!=0 ) return 0;
         1688  +    aWc[3] = zEscape[0];
         1689  +  }
  1676   1690   
  1677   1691     /* The memcpy() statement assumes that the wildcard characters are
  1678   1692     ** the first three statements in the compareInfo structure.  The
  1679   1693     ** asserts() that follow verify that assumption
  1680   1694     */
  1681   1695     memcpy(aWc, pDef->pUserData, 3);
  1682   1696     assert( (char*)&likeInfoAlt == (char*)&likeInfoAlt.matchAll );

Changes to src/parse.y.

   707    707   /////////////////////////// The DELETE statement /////////////////////////////
   708    708   //
   709    709   %ifdef SQLITE_ENABLE_UPDATE_DELETE_LIMIT
   710    710   cmd ::= with(C) DELETE FROM fullname(X) indexed_opt(I) where_opt(W) 
   711    711           orderby_opt(O) limit_opt(L). {
   712    712     sqlite3WithPush(pParse, C, 1);
   713    713     sqlite3SrcListIndexedBy(pParse, X, &I);
   714         -  W = sqlite3LimitWhere(pParse, X, W, O, L.pLimit, L.pOffset, "DELETE");
   715         -  sqlite3DeleteFrom(pParse,X,W);
          714  +  sqlite3DeleteFrom(pParse,X,W,O,L.pLimit,L.pOffset); 
   716    715   }
   717    716   %endif
   718    717   %ifndef SQLITE_ENABLE_UPDATE_DELETE_LIMIT
   719    718   cmd ::= with(C) DELETE FROM fullname(X) indexed_opt(I) where_opt(W). {
   720    719     sqlite3WithPush(pParse, C, 1);
   721    720     sqlite3SrcListIndexedBy(pParse, X, &I);
   722         -  sqlite3DeleteFrom(pParse,X,W);
          721  +  sqlite3DeleteFrom(pParse,X,W,0,0,0);
   723    722   }
   724    723   %endif
   725    724   
   726    725   %type where_opt {Expr*}
   727    726   %destructor where_opt {sqlite3ExprDelete(pParse->db, $$);}
   728    727   
   729    728   where_opt(A) ::= .                    {A = 0;}
................................................................................
   733    732   //
   734    733   %ifdef SQLITE_ENABLE_UPDATE_DELETE_LIMIT
   735    734   cmd ::= with(C) UPDATE orconf(R) fullname(X) indexed_opt(I) SET setlist(Y)
   736    735           where_opt(W) orderby_opt(O) limit_opt(L).  {
   737    736     sqlite3WithPush(pParse, C, 1);
   738    737     sqlite3SrcListIndexedBy(pParse, X, &I);
   739    738     sqlite3ExprListCheckLength(pParse,Y,"set list"); 
   740         -  W = sqlite3LimitWhere(pParse, X, W, O, L.pLimit, L.pOffset, "UPDATE");
   741         -  sqlite3Update(pParse,X,Y,W,R);
          739  +  sqlite3Update(pParse,X,Y,W,R,O,L.pLimit,L.pOffset);
   742    740   }
   743    741   %endif
   744    742   %ifndef SQLITE_ENABLE_UPDATE_DELETE_LIMIT
   745    743   cmd ::= with(C) UPDATE orconf(R) fullname(X) indexed_opt(I) SET setlist(Y)
   746    744           where_opt(W).  {
   747    745     sqlite3WithPush(pParse, C, 1);
   748    746     sqlite3SrcListIndexedBy(pParse, X, &I);
   749    747     sqlite3ExprListCheckLength(pParse,Y,"set list"); 
   750         -  sqlite3Update(pParse,X,Y,W,R);
          748  +  sqlite3Update(pParse,X,Y,W,R,0,0,0);
   751    749   }
   752    750   %endif
   753    751   
   754    752   %type setlist {ExprList*}
   755    753   %destructor setlist {sqlite3ExprListDelete(pParse->db, $$);}
   756    754   
   757    755   setlist(A) ::= setlist(Z) COMMA nm(X) EQ expr(Y). {

Changes to src/resolve.c.

   631    631       ** column in the FROM clause.  This is used by the LIMIT and ORDER BY
   632    632       ** clause processing on UPDATE and DELETE statements.
   633    633       */
   634    634       case TK_ROW: {
   635    635         SrcList *pSrcList = pNC->pSrcList;
   636    636         struct SrcList_item *pItem;
   637    637         assert( pSrcList && pSrcList->nSrc==1 );
   638         -      pItem = pSrcList->a; 
          638  +      pItem = pSrcList->a;
          639  +      assert( HasRowid(pItem->pTab) && pItem->pTab->pSelect==0 );
   639    640         pExpr->op = TK_COLUMN;
   640    641         pExpr->pTab = pItem->pTab;
   641    642         pExpr->iTable = pItem->iCursor;
   642    643         pExpr->iColumn = -1;
   643    644         pExpr->affinity = SQLITE_AFF_INTEGER;
   644    645         break;
   645    646       }

Changes to src/select.c.

    49     49   struct SortCtx {
    50     50     ExprList *pOrderBy;   /* The ORDER BY (or GROUP BY clause) */
    51     51     int nOBSat;           /* Number of ORDER BY terms satisfied by indices */
    52     52     int iECursor;         /* Cursor number for the sorter */
    53     53     int regReturn;        /* Register holding block-output return address */
    54     54     int labelBkOut;       /* Start label for the block-output subroutine */
    55     55     int addrSortIndex;    /* Address of the OP_SorterOpen or OP_OpenEphemeral */
           56  +  int labelDone;        /* Jump here when done, ex: LIMIT reached */
    56     57     u8 sortFlags;         /* Zero or more SORTFLAG_* bits */
           58  +  u8 bOrderedInnerLoop; /* ORDER BY correctly sorts the inner loop */
    57     59   };
    58     60   #define SORTFLAG_UseSorter  0x01   /* Use SorterOpen instead of OpenEphemeral */
    59     61   
    60     62   /*
    61     63   ** Delete all the content of a Select structure.  Deallocate the structure
    62     64   ** itself only if bFree is true.
    63     65   */
................................................................................
   497    499     int bSeq = ((pSort->sortFlags & SORTFLAG_UseSorter)==0);
   498    500     int nExpr = pSort->pOrderBy->nExpr;              /* No. of ORDER BY terms */
   499    501     int nBase = nExpr + bSeq + nData;                /* Fields in sorter record */
   500    502     int regBase;                                     /* Regs for sorter record */
   501    503     int regRecord = ++pParse->nMem;                  /* Assembled sorter record */
   502    504     int nOBSat = pSort->nOBSat;                      /* ORDER BY terms to skip */
   503    505     int op;                            /* Opcode to add sorter record to sorter */
          506  +  int iLimit;                        /* LIMIT counter */
   504    507   
   505    508     assert( bSeq==0 || bSeq==1 );
   506    509     if( nPrefixReg ){
   507    510       assert( nPrefixReg==nExpr+bSeq );
   508    511       regBase = regData - nExpr - bSeq;
   509    512     }else{
   510    513       regBase = pParse->nMem + 1;
   511    514       pParse->nMem += nBase;
   512    515     }
          516  +  assert( pSelect->iOffset==0 || pSelect->iLimit!=0 );
          517  +  iLimit = pSelect->iOffset ? pSelect->iOffset+1 : pSelect->iLimit;
          518  +  pSort->labelDone = sqlite3VdbeMakeLabel(v);
   513    519     sqlite3ExprCodeExprList(pParse, pSort->pOrderBy, regBase, SQLITE_ECEL_DUP);
   514    520     if( bSeq ){
   515    521       sqlite3VdbeAddOp2(v, OP_Sequence, pSort->iECursor, regBase+nExpr);
   516    522     }
   517    523     if( nPrefixReg==0 ){
   518    524       sqlite3ExprCodeMove(pParse, regData, regBase+nExpr+bSeq, nData);
   519    525     }
   520         -
   521    526     sqlite3VdbeAddOp3(v, OP_MakeRecord, regBase+nOBSat, nBase-nOBSat, regRecord);
   522    527     if( nOBSat>0 ){
   523    528       int regPrevKey;   /* The first nOBSat columns of the previous row */
   524    529       int addrFirst;    /* Address of the OP_IfNot opcode */
   525    530       int addrJmp;      /* Address of the OP_Jump opcode */
   526    531       VdbeOp *pOp;      /* Opcode that opens the sorter */
   527    532       int nKey;         /* Number of sorting key columns, including OP_Sequence */
................................................................................
   548    553                                              pKI->nXField-1);
   549    554       addrJmp = sqlite3VdbeCurrentAddr(v);
   550    555       sqlite3VdbeAddOp3(v, OP_Jump, addrJmp+1, 0, addrJmp+1); VdbeCoverage(v);
   551    556       pSort->labelBkOut = sqlite3VdbeMakeLabel(v);
   552    557       pSort->regReturn = ++pParse->nMem;
   553    558       sqlite3VdbeAddOp2(v, OP_Gosub, pSort->regReturn, pSort->labelBkOut);
   554    559       sqlite3VdbeAddOp1(v, OP_ResetSorter, pSort->iECursor);
          560  +    if( iLimit ){
          561  +      sqlite3VdbeAddOp2(v, OP_IfNot, iLimit, pSort->labelDone);
          562  +      VdbeCoverage(v);
          563  +    }
   555    564       sqlite3VdbeJumpHere(v, addrFirst);
   556    565       sqlite3ExprCodeMove(pParse, regBase, regPrevKey, pSort->nOBSat);
   557    566       sqlite3VdbeJumpHere(v, addrJmp);
   558    567     }
   559    568     if( pSort->sortFlags & SORTFLAG_UseSorter ){
   560    569       op = OP_SorterInsert;
   561    570     }else{
   562    571       op = OP_IdxInsert;
   563    572     }
   564    573     sqlite3VdbeAddOp2(v, op, pSort->iECursor, regRecord);
   565         -  if( pSelect->iLimit ){
          574  +  if( iLimit ){
   566    575       int addr;
   567         -    int iLimit;
   568         -    if( pSelect->iOffset ){
   569         -      iLimit = pSelect->iOffset+1;
   570         -    }else{
   571         -      iLimit = pSelect->iLimit;
   572         -    }
          576  +    int r1 = 0;
          577  +    /* Fill the sorter until it contains LIMIT+OFFSET entries.  (The iLimit
          578  +    ** register is initialized with value of LIMIT+OFFSET.)  After the sorter
          579  +    ** fills up, delete the least entry in the sorter after each insert.
          580  +    ** Thus we never hold more than the LIMIT+OFFSET rows in memory at once */
   573    581       addr = sqlite3VdbeAddOp3(v, OP_IfNotZero, iLimit, 0, -1); VdbeCoverage(v);
   574    582       sqlite3VdbeAddOp1(v, OP_Last, pSort->iECursor);
          583  +    if( pSort->bOrderedInnerLoop ){
          584  +      r1 = ++pParse->nMem;
          585  +      sqlite3VdbeAddOp3(v, OP_Column, pSort->iECursor, nExpr, r1);
          586  +      VdbeComment((v, "seq"));
          587  +    }
   575    588       sqlite3VdbeAddOp1(v, OP_Delete, pSort->iECursor);
          589  +    if( pSort->bOrderedInnerLoop ){
          590  +      /* If the inner loop is driven by an index such that values from
          591  +      ** the same iteration of the inner loop are in sorted order, then
          592  +      ** immediately jump to the next iteration of an inner loop if the
          593  +      ** entry from the current iteration does not fit into the top
          594  +      ** LIMIT+OFFSET entries of the sorter. */
          595  +      int iBrk = sqlite3VdbeCurrentAddr(v) + 2;
          596  +      sqlite3VdbeAddOp3(v, OP_Eq, regBase+nExpr, iBrk, r1);
          597  +      sqlite3VdbeChangeP5(v, SQLITE_NULLEQ);
          598  +      VdbeCoverage(v);
          599  +    }
   576    600       sqlite3VdbeJumpHere(v, addr);
   577    601     }
   578    602   }
   579    603   
   580    604   /*
   581    605   ** Add code to implement the OFFSET
   582    606   */
................................................................................
  1164   1188     Parse *pParse,    /* Parsing context */
  1165   1189     Select *p,        /* The SELECT statement */
  1166   1190     SortCtx *pSort,   /* Information on the ORDER BY clause */
  1167   1191     int nColumn,      /* Number of columns of data */
  1168   1192     SelectDest *pDest /* Write the sorted results here */
  1169   1193   ){
  1170   1194     Vdbe *v = pParse->pVdbe;                     /* The prepared statement */
  1171         -  int addrBreak = sqlite3VdbeMakeLabel(v);     /* Jump here to exit loop */
         1195  +  int addrBreak = pSort->labelDone;            /* Jump here to exit loop */
  1172   1196     int addrContinue = sqlite3VdbeMakeLabel(v);  /* Jump here for next cycle */
  1173   1197     int addr;
  1174   1198     int addrOnce = 0;
  1175   1199     int iTab;
  1176   1200     ExprList *pOrderBy = pSort->pOrderBy;
  1177   1201     int eDest = pDest->eDest;
  1178   1202     int iParm = pDest->iSDParm;
................................................................................
  1183   1207     int nSortData;                  /* Trailing values to read from sorter */
  1184   1208     int i;
  1185   1209     int bSeq;                       /* True if sorter record includes seq. no. */
  1186   1210   #ifdef SQLITE_ENABLE_EXPLAIN_COMMENTS
  1187   1211     struct ExprList_item *aOutEx = p->pEList->a;
  1188   1212   #endif
  1189   1213   
         1214  +  assert( addrBreak<0 );
  1190   1215     if( pSort->labelBkOut ){
  1191   1216       sqlite3VdbeAddOp2(v, OP_Gosub, pSort->regReturn, pSort->labelBkOut);
  1192   1217       sqlite3VdbeAddOp2(v, OP_Goto, 0, addrBreak);
  1193   1218       sqlite3VdbeResolveLabel(v, pSort->labelBkOut);
  1194   1219     }
  1195   1220     iTab = pSort->iECursor;
  1196   1221     if( eDest==SRT_Output || eDest==SRT_Coroutine ){
................................................................................
  2852   2877     /* Compute the comparison permutation and keyinfo that is used with
  2853   2878     ** the permutation used to determine if the next
  2854   2879     ** row of results comes from selectA or selectB.  Also add explicit
  2855   2880     ** collations to the ORDER BY clause terms so that when the subqueries
  2856   2881     ** to the right and the left are evaluated, they use the correct
  2857   2882     ** collation.
  2858   2883     */
  2859         -  aPermute = sqlite3DbMallocRaw(db, sizeof(int)*nOrderBy);
         2884  +  aPermute = sqlite3DbMallocRaw(db, sizeof(int)*(nOrderBy + 1));
  2860   2885     if( aPermute ){
  2861   2886       struct ExprList_item *pItem;
  2862         -    for(i=0, pItem=pOrderBy->a; i<nOrderBy; i++, pItem++){
         2887  +    aPermute[0] = nOrderBy;
         2888  +    for(i=1, pItem=pOrderBy->a; i<=nOrderBy; i++, pItem++){
  2863   2889         assert( pItem->u.x.iOrderByCol>0
  2864   2890             && pItem->u.x.iOrderByCol<=p->pEList->nExpr );
  2865   2891         aPermute[i] = pItem->u.x.iOrderByCol - 1;
  2866   2892       }
  2867   2893       pKeyMerge = multiSelectOrderByKeyInfo(pParse, p, 1);
  2868   2894     }else{
  2869   2895       pKeyMerge = 0;
................................................................................
  3716   3742     if( sqlite3SelectTrace & 0x100 ){
  3717   3743       sqlite3DebugPrintf("After flattening:\n");
  3718   3744       sqlite3TreeViewSelect(0, p, 0);
  3719   3745     }
  3720   3746   #endif
  3721   3747   
  3722   3748     return 1;
         3749  +}
         3750  +#endif /* !defined(SQLITE_OMIT_SUBQUERY) || !defined(SQLITE_OMIT_VIEW) */
         3751  +
         3752  +
         3753  +
         3754  +#if !defined(SQLITE_OMIT_SUBQUERY) || !defined(SQLITE_OMIT_VIEW)
         3755  +/*
         3756  +** Make copies of relevant WHERE clause terms of the outer query into
         3757  +** the WHERE clause of subquery.  Example:
         3758  +**
         3759  +**    SELECT * FROM (SELECT a AS x, c-d AS y FROM t1) WHERE x=5 AND y=10;
         3760  +**
         3761  +** Transformed into:
         3762  +**
         3763  +**    SELECT * FROM (SELECT a AS x, c-d AS y FROM t1 WHERE a=5 AND c-d=10)
         3764  +**     WHERE x=5 AND y=10;
         3765  +**
         3766  +** The hope is that the terms added to the inner query will make it more
         3767  +** efficient.
         3768  +**
         3769  +** Do not attempt this optimization if:
         3770  +**
         3771  +**   (1) The inner query is an aggregate.  (In that case, we'd really want
         3772  +**       to copy the outer WHERE-clause terms onto the HAVING clause of the
         3773  +**       inner query.  But they probably won't help there so do not bother.)
         3774  +**
         3775  +**   (2) The inner query is the recursive part of a common table expression.
         3776  +**
         3777  +**   (3) The inner query has a LIMIT clause (since the changes to the WHERE
         3778  +**       close would change the meaning of the LIMIT).
         3779  +**
         3780  +**   (4) The inner query is the right operand of a LEFT JOIN.  (The caller
         3781  +**       enforces this restriction since this routine does not have enough
         3782  +**       information to know.)
         3783  +**
         3784  +**   (5) The WHERE clause expression originates in the ON or USING clause
         3785  +**       of a LEFT JOIN.
         3786  +**
         3787  +** Return 0 if no changes are made and non-zero if one or more WHERE clause
         3788  +** terms are duplicated into the subquery.
         3789  +*/
         3790  +static int pushDownWhereTerms(
         3791  +  sqlite3 *db,          /* The database connection (for malloc()) */
         3792  +  Select *pSubq,        /* The subquery whose WHERE clause is to be augmented */
         3793  +  Expr *pWhere,         /* The WHERE clause of the outer query */
         3794  +  int iCursor           /* Cursor number of the subquery */
         3795  +){
         3796  +  Expr *pNew;
         3797  +  int nChng = 0;
         3798  +  Select *pX;           /* For looping over compound SELECTs in pSubq */
         3799  +  if( pWhere==0 ) return 0;
         3800  +  for(pX=pSubq; pX; pX=pX->pPrior){
         3801  +    if( (pX->selFlags & (SF_Aggregate|SF_Recursive))!=0 ){
         3802  +      testcase( pX->selFlags & SF_Aggregate );
         3803  +      testcase( pX->selFlags & SF_Recursive );
         3804  +      testcase( pX!=pSubq );
         3805  +      return 0; /* restrictions (1) and (2) */
         3806  +    }
         3807  +  }
         3808  +  if( pSubq->pLimit!=0 ){
         3809  +    return 0; /* restriction (3) */
         3810  +  }
         3811  +  while( pWhere->op==TK_AND ){
         3812  +    nChng += pushDownWhereTerms(db, pSubq, pWhere->pRight, iCursor);
         3813  +    pWhere = pWhere->pLeft;
         3814  +  }
         3815  +  if( ExprHasProperty(pWhere,EP_FromJoin) ) return 0; /* restriction 5 */
         3816  +  if( sqlite3ExprIsTableConstant(pWhere, iCursor) ){
         3817  +    nChng++;
         3818  +    while( pSubq ){
         3819  +      pNew = sqlite3ExprDup(db, pWhere, 0);
         3820  +      pNew = substExpr(db, pNew, iCursor, pSubq->pEList);
         3821  +      pSubq->pWhere = sqlite3ExprAnd(db, pSubq->pWhere, pNew);
         3822  +      pSubq = pSubq->pPrior;
         3823  +    }
         3824  +  }
         3825  +  return nChng;
  3723   3826   }
  3724   3827   #endif /* !defined(SQLITE_OMIT_SUBQUERY) || !defined(SQLITE_OMIT_VIEW) */
  3725   3828   
  3726   3829   /*
  3727   3830   ** Based on the contents of the AggInfo structure indicated by the first
  3728   3831   ** argument, this function checks if the following are true:
  3729   3832   **
................................................................................
  4681   4784       );
  4682   4785     }
  4683   4786   }
  4684   4787   #else
  4685   4788   # define explainSimpleCount(a,b,c)
  4686   4789   #endif
  4687   4790   
         4791  +#ifdef SQLITE_COUNTOFVIEW_OPTIMIZATION
         4792  +/*
         4793  +** Attempt to transform a query of the form
         4794  +**
         4795  +**    SELECT count(*) FROM (SELECT x FROM t1 UNION ALL SELECT y FROM t2)
         4796  +**
         4797  +** Into this:
         4798  +**
         4799  +**    SELECT (SELECT count(*) FROM t1)+(SELECT count(*) FROM t2)
         4800  +**
         4801  +** The transformation only works if all of the following are true:
         4802  +**
         4803  +**   *  The subquery is a UNION ALL of two or more terms
         4804  +**   *  There is no WHERE or GROUP BY or HAVING clauses on the subqueries
         4805  +**   *  The outer query is a simple count(*)
         4806  +**
         4807  +** Return TRUE if the optimization is undertaken.
         4808  +*/
         4809  +static int countOfViewOptimization(Parse *pParse, Select *p){
         4810  +  Select *pSub, *pPrior;
         4811  +  Expr *pExpr;
         4812  +  Expr *pCount;
         4813  +  sqlite3 *db;
         4814  +  if( (p->selFlags & SF_Aggregate)==0 ) return 0;   /* This is an aggregate query */
         4815  +  if( p->pEList->nExpr!=1 ) return 0;               /* Single result column */
         4816  +  pExpr = p->pEList->a[0].pExpr;
         4817  +  if( pExpr->op!=TK_AGG_FUNCTION ) return 0;        /* Result is an aggregate */
         4818  +  if( sqlite3_stricmp(pExpr->u.zToken,"count") ) return 0;  /* Must be count() */
         4819  +  if( pExpr->x.pList!=0 ) return 0;                 /* Must be count(*) */
         4820  +  if( p->pSrc->nSrc!=1 ) return 0;                  /* One table in the FROM clause */
         4821  +  pSub = p->pSrc->a[0].pSelect;
         4822  +  if( pSub==0 ) return 0;                           /* The FROM is a subquery */
         4823  +  if( pSub->pPrior==0 ) return 0;                   /* Must be a compound subquery */
         4824  +  do{
         4825  +    if( pSub->op!=TK_ALL && pSub->pPrior ) return 0;  /* Must be UNION ALL */
         4826  +    if( pSub->pWhere ) return 0;                      /* No WHERE clause */
         4827  +    if( pSub->selFlags & SF_Aggregate ) return 0;     /* Not an aggregate */
         4828  +    pSub = pSub->pPrior;                              /* Repeat over compound terms */
         4829  +  }while( pSub );
         4830  +
         4831  +  /* If we reach this point, that means it is OK to perform the transformation */
         4832  +
         4833  +  db = pParse->db;
         4834  +  pCount = pExpr;
         4835  +  pExpr = 0;
         4836  +  pSub = p->pSrc->a[0].pSelect;
         4837  +  p->pSrc->a[0].pSelect = 0;
         4838  +  sqlite3SrcListDelete(db, p->pSrc);
         4839  +  p->pSrc = sqlite3DbMallocZero(pParse->db, sizeof(*p->pSrc));
         4840  +  while( pSub ){
         4841  +    Expr *pTerm;
         4842  +    pPrior = pSub->pPrior;
         4843  +    pSub->pPrior = 0;
         4844  +    pSub->pNext = 0;
         4845  +    pSub->selFlags |= SF_Aggregate;
         4846  +    pSub->selFlags &= ~SF_Compound;
         4847  +    pSub->nSelectRow = 0;
         4848  +    sqlite3ExprListDelete(db, pSub->pEList);
         4849  +    pTerm = pPrior ? sqlite3ExprDup(db, pCount, 0) : pCount;
         4850  +    pSub->pEList = sqlite3ExprListAppend(pParse, 0, pTerm);
         4851  +    pTerm = sqlite3PExpr(pParse, TK_SELECT, 0, 0, 0);
         4852  +    if( pTerm ){
         4853  +      pTerm->x.pSelect = pSub;
         4854  +      ExprSetProperty(pTerm, EP_xIsSelect|EP_Subquery);
         4855  +      sqlite3ExprSetHeightAndFlags(pParse, pTerm);
         4856  +      if( pExpr==0 ){
         4857  +        pExpr = pTerm;
         4858  +      }else{
         4859  +        pExpr = sqlite3PExpr(pParse, TK_PLUS, pTerm, pExpr, 0);
         4860  +      }
         4861  +    }else{
         4862  +      sqlite3SelectDelete(db, pSub);
         4863  +    }
         4864  +    pSub = pPrior;
         4865  +  }
         4866  +  p->pEList->a[0].pExpr = pExpr;
         4867  +  p->selFlags &= ~SF_Aggregate;
         4868  +
         4869  +#if SELECTTRACE_ENABLED
         4870  +  if( sqlite3SelectTrace & 0x400 ){
         4871  +    SELECTTRACE(0x400,pParse,p,("After count-of-view optimization:\n"));
         4872  +    sqlite3TreeViewSelect(0, p, 0);
         4873  +  }
         4874  +#endif
         4875  +  return 1;
         4876  +}
         4877  +#endif /* SQLITE_COUNTOFVIEW_OPTIMIZATION */
         4878  +
  4688   4879   /*
  4689   4880   ** Generate code for the SELECT statement given in the p argument.  
  4690   4881   **
  4691   4882   ** The results are returned according to the SelectDest structure.
  4692   4883   ** See comments in sqliteInt.h for further information.
  4693   4884   **
  4694   4885   ** This routine returns the number of errors.  If any errors are
................................................................................
  4822   5013       if( flattenSubquery(pParse, p, i, isAgg, isAggSub) ){
  4823   5014         /* This subquery can be absorbed into its parent. */
  4824   5015         if( isAggSub ){
  4825   5016           isAgg = 1;
  4826   5017           p->selFlags |= SF_Aggregate;
  4827   5018         }
  4828   5019         i = -1;
  4829         -    }else if( pTabList->nSrc==1
  4830         -           && OptimizationEnabled(db, SQLITE_SubqCoroutine)
  4831         -    ){
  4832         -      /* Implement a co-routine that will return a single row of the result
  4833         -      ** set on each invocation.
  4834         -      */
  4835         -      int addrTop = sqlite3VdbeCurrentAddr(v)+1;
  4836         -      pItem->regReturn = ++pParse->nMem;
  4837         -      sqlite3VdbeAddOp3(v, OP_InitCoroutine, pItem->regReturn, 0, addrTop);
  4838         -      VdbeComment((v, "%s", pItem->pTab->zName));
  4839         -      pItem->addrFillSub = addrTop;
  4840         -      sqlite3SelectDestInit(&dest, SRT_Coroutine, pItem->regReturn);
  4841         -      explainSetInteger(pItem->iSelectId, (u8)pParse->iNextSelectId);
  4842         -      sqlite3Select(pParse, pSub, &dest);
  4843         -      pItem->pTab->nRowLogEst = sqlite3LogEst(pSub->nSelectRow);
  4844         -      pItem->viaCoroutine = 1;
  4845         -      pItem->regResult = dest.iSdst;
  4846         -      sqlite3VdbeAddOp1(v, OP_EndCoroutine, pItem->regReturn);
  4847         -      sqlite3VdbeJumpHere(v, addrTop-1);
  4848         -      sqlite3ClearTempRegCache(pParse);
  4849   5020       }else{
  4850         -      /* Generate a subroutine that will fill an ephemeral table with
  4851         -      ** the content of this subquery.  pItem->addrFillSub will point
  4852         -      ** to the address of the generated subroutine.  pItem->regReturn
  4853         -      ** is a register allocated to hold the subroutine return address
  4854         -      */
  4855         -      int topAddr;
  4856         -      int onceAddr = 0;
  4857         -      int retAddr;
  4858         -      assert( pItem->addrFillSub==0 );
  4859         -      pItem->regReturn = ++pParse->nMem;
  4860         -      topAddr = sqlite3VdbeAddOp2(v, OP_Integer, 0, pItem->regReturn);
  4861         -      pItem->addrFillSub = topAddr+1;
  4862         -      if( pItem->isCorrelated==0 ){
  4863         -        /* If the subquery is not correlated and if we are not inside of
  4864         -        ** a trigger, then we only need to compute the value of the subquery
  4865         -        ** once. */
  4866         -        onceAddr = sqlite3CodeOnce(pParse); VdbeCoverage(v);
  4867         -        VdbeComment((v, "materialize \"%s\"", pItem->pTab->zName));
         5021  +      if( (pItem->jointype & JT_OUTER)==0
         5022  +       && pushDownWhereTerms(db, pSub, p->pWhere, pItem->iCursor)
         5023  +      ){
         5024  +#if SELECTTRACE_ENABLED
         5025  +        if( sqlite3SelectTrace & 0x100 ){
         5026  +          sqlite3DebugPrintf("After WHERE-clause push-down:\n");
         5027  +          sqlite3TreeViewSelect(0, p, 0);
         5028  +        }
         5029  +#endif
         5030  +      }
         5031  +      if( pTabList->nSrc==1
         5032  +       && OptimizationEnabled(db, SQLITE_SubqCoroutine)
         5033  +      ){
         5034  +        /* Implement a co-routine that will return a single row of the result
         5035  +        ** set on each invocation.
         5036  +        */
         5037  +        int addrTop = sqlite3VdbeCurrentAddr(v)+1;
         5038  +        pItem->regReturn = ++pParse->nMem;
         5039  +        sqlite3VdbeAddOp3(v, OP_InitCoroutine, pItem->regReturn, 0, addrTop);
         5040  +        VdbeComment((v, "%s", pItem->pTab->zName));
         5041  +        pItem->addrFillSub = addrTop;
         5042  +        sqlite3SelectDestInit(&dest, SRT_Coroutine, pItem->regReturn);
         5043  +        explainSetInteger(pItem->iSelectId, (u8)pParse->iNextSelectId);
         5044  +        sqlite3Select(pParse, pSub, &dest);
         5045  +        pItem->pTab->nRowLogEst = sqlite3LogEst(pSub->nSelectRow);
         5046  +        pItem->viaCoroutine = 1;
         5047  +        pItem->regResult = dest.iSdst;
         5048  +        sqlite3VdbeAddOp1(v, OP_EndCoroutine, pItem->regReturn);
         5049  +        sqlite3VdbeJumpHere(v, addrTop-1);
         5050  +        sqlite3ClearTempRegCache(pParse);
  4868   5051         }else{
  4869         -        VdbeNoopComment((v, "materialize \"%s\"", pItem->pTab->zName));
  4870         -      }
  4871         -      sqlite3SelectDestInit(&dest, SRT_EphemTab, pItem->iCursor);
  4872         -      explainSetInteger(pItem->iSelectId, (u8)pParse->iNextSelectId);
  4873         -      sqlite3Select(pParse, pSub, &dest);
  4874         -      pItem->pTab->nRowLogEst = sqlite3LogEst(pSub->nSelectRow);
  4875         -      if( onceAddr ) sqlite3VdbeJumpHere(v, onceAddr);
  4876         -      retAddr = sqlite3VdbeAddOp1(v, OP_Return, pItem->regReturn);
  4877         -      VdbeComment((v, "end %s", pItem->pTab->zName));
  4878         -      sqlite3VdbeChangeP1(v, topAddr, retAddr);
  4879         -      sqlite3ClearTempRegCache(pParse);
  4880         -    }
  4881         -    if( /*pParse->nErr ||*/ db->mallocFailed ){
         5052  +        /* Generate a subroutine that will fill an ephemeral table with
         5053  +        ** the content of this subquery.  pItem->addrFillSub will point
         5054  +        ** to the address of the generated subroutine.  pItem->regReturn
         5055  +        ** is a register allocated to hold the subroutine return address
         5056  +        */
         5057  +        int topAddr;
         5058  +        int onceAddr = 0;
         5059  +        int retAddr;
         5060  +        assert( pItem->addrFillSub==0 );
         5061  +        pItem->regReturn = ++pParse->nMem;
         5062  +        topAddr = sqlite3VdbeAddOp2(v, OP_Integer, 0, pItem->regReturn);
         5063  +        pItem->addrFillSub = topAddr+1;
         5064  +        if( pItem->isCorrelated==0 ){
         5065  +          /* If the subquery is not correlated and if we are not inside of
         5066  +          ** a trigger, then we only need to compute the value of the subquery
         5067  +          ** once. */
         5068  +          onceAddr = sqlite3CodeOnce(pParse); VdbeCoverage(v);
         5069  +          VdbeComment((v, "materialize \"%s\"", pItem->pTab->zName));
         5070  +        }else{
         5071  +          VdbeNoopComment((v, "materialize \"%s\"", pItem->pTab->zName));
         5072  +        }
         5073  +        sqlite3SelectDestInit(&dest, SRT_EphemTab, pItem->iCursor);
         5074  +        explainSetInteger(pItem->iSelectId, (u8)pParse->iNextSelectId);
         5075  +        sqlite3Select(pParse, pSub, &dest);
         5076  +        pItem->pTab->nRowLogEst = sqlite3LogEst(pSub->nSelectRow);
         5077  +        if( onceAddr ) sqlite3VdbeJumpHere(v, onceAddr);
         5078  +        retAddr = sqlite3VdbeAddOp1(v, OP_Return, pItem->regReturn);
         5079  +        VdbeComment((v, "end %s", pItem->pTab->zName));
         5080  +        sqlite3VdbeChangeP1(v, topAddr, retAddr);
         5081  +        sqlite3ClearTempRegCache(pParse);
         5082  +      }
         5083  +    }
         5084  +    if( db->mallocFailed ){
  4882   5085         goto select_end;
  4883   5086       }
  4884   5087       pParse->nHeight -= sqlite3SelectExprHeight(p);
  4885   5088       pTabList = p->pSrc;
  4886   5089       if( !IgnorableOrderby(pDest) ){
  4887   5090         sSort.pOrderBy = p->pOrderBy;
  4888   5091       }
................................................................................
  4903   5106   #if SELECTTRACE_ENABLED
  4904   5107       SELECTTRACE(1,pParse,p,("end compound-select processing\n"));
  4905   5108       pParse->nSelectIndent--;
  4906   5109   #endif
  4907   5110       return rc;
  4908   5111     }
  4909   5112   #endif
         5113  +
         5114  +#ifdef SQLITE_COUNTOFVIEW_OPTIMIZATION
         5115  +  if( OptimizationEnabled(db, SQLITE_QueryFlattener|SQLITE_CountOfView)
         5116  +   && countOfViewOptimization(pParse, p)
         5117  +  ){
         5118  +    if( db->mallocFailed ) goto select_end;
         5119  +    pEList = p->pEList;
         5120  +    pTabList = p->pSrc;
         5121  +  }
         5122  +#endif
  4910   5123   
  4911   5124     /* If the query is DISTINCT with an ORDER BY but is not an aggregate, and 
  4912   5125     ** if the select-list is the same as the ORDER BY list, then this query
  4913   5126     ** can be rewritten as a GROUP BY. In other words, this:
  4914   5127     **
  4915   5128     **     SELECT DISTINCT xyz FROM ... ORDER BY xyz
  4916   5129     **
................................................................................
  4997   5210         p->nSelectRow = sqlite3WhereOutputRowCount(pWInfo);
  4998   5211       }
  4999   5212       if( sDistinct.isTnct && sqlite3WhereIsDistinct(pWInfo) ){
  5000   5213         sDistinct.eTnctType = sqlite3WhereIsDistinct(pWInfo);
  5001   5214       }
  5002   5215       if( sSort.pOrderBy ){
  5003   5216         sSort.nOBSat = sqlite3WhereIsOrdered(pWInfo);
         5217  +      sSort.bOrderedInnerLoop = sqlite3WhereOrderedInnerLoop(pWInfo);
  5004   5218         if( sSort.nOBSat==sSort.pOrderBy->nExpr ){
  5005   5219           sSort.pOrderBy = 0;
  5006   5220         }
  5007   5221       }
  5008   5222   
  5009   5223       /* If sorting index that was created by a prior OP_OpenEphemeral 
  5010   5224       ** instruction ended up not being needed, then change the OP_OpenEphemeral

Changes to src/sqliteInt.h.

  1241   1241   #define SQLITE_DistinctOpt    0x0020   /* DISTINCT using indexes */
  1242   1242   #define SQLITE_CoverIdxScan   0x0040   /* Covering index scans */
  1243   1243   #define SQLITE_OrderByIdxJoin 0x0080   /* ORDER BY of joins via index */
  1244   1244   #define SQLITE_SubqCoroutine  0x0100   /* Evaluate subqueries as coroutines */
  1245   1245   #define SQLITE_Transitive     0x0200   /* Transitive constraints */
  1246   1246   #define SQLITE_OmitNoopJoin   0x0400   /* Omit unused tables in joins */
  1247   1247   #define SQLITE_Stat34         0x0800   /* Use STAT3 or STAT4 data */
         1248  +#define SQLITE_CountOfView    0x1000   /* The count-of-view optimization */
  1248   1249   #define SQLITE_AllOpts        0xffff   /* All optimizations */
  1249   1250   
  1250   1251   /*
  1251   1252   ** Macros for testing whether or not optimizations are enabled or disabled.
  1252   1253   */
  1253   1254   #ifndef SQLITE_OMIT_BUILTIN_TEST
  1254   1255   #define OptimizationDisabled(db, mask)  (((db)->dbOptFlags&(mask))!=0)
................................................................................
  2272   2273   #define WHERE_ONETABLE_ONLY    0x0040 /* Only code the 1st table in pTabList */
  2273   2274   #define WHERE_NO_AUTOINDEX     0x0080 /* Disallow automatic indexes */
  2274   2275   #define WHERE_GROUPBY          0x0100 /* pOrderBy is really a GROUP BY */
  2275   2276   #define WHERE_DISTINCTBY       0x0200 /* pOrderby is really a DISTINCT clause */
  2276   2277   #define WHERE_WANT_DISTINCT    0x0400 /* All output needs to be distinct */
  2277   2278   #define WHERE_SORTBYGROUP      0x0800 /* Support sqlite3WhereIsSorted() */
  2278   2279   #define WHERE_REOPEN_IDX       0x1000 /* Try to use OP_ReopenIdx */
         2280  +#define WHERE_ORDERBY_LIMIT    0x2000 /* ORDERBY+LIMIT on the inner loop */
  2279   2281   
  2280   2282   /* Allowed return values from sqlite3WhereIsDistinct()
  2281   2283   */
  2282   2284   #define WHERE_DISTINCT_NOOP      0  /* DISTINCT keyword not used */
  2283   2285   #define WHERE_DISTINCT_UNIQUE    1  /* No duplicates */
  2284   2286   #define WHERE_DISTINCT_ORDERED   2  /* All duplicates are adjacent */
  2285   2287   #define WHERE_DISTINCT_UNORDERED 3  /* Duplicates are scattered */
................................................................................
  3278   3280   void sqlite3SelectDelete(sqlite3*, Select*);
  3279   3281   Table *sqlite3SrcListLookup(Parse*, SrcList*);
  3280   3282   int sqlite3IsReadOnly(Parse*, Table*, int);
  3281   3283   void sqlite3OpenTable(Parse*, int iCur, int iDb, Table*, int);
  3282   3284   #if defined(SQLITE_ENABLE_UPDATE_DELETE_LIMIT) && !defined(SQLITE_OMIT_SUBQUERY)
  3283   3285   Expr *sqlite3LimitWhere(Parse*,SrcList*,Expr*,ExprList*,Expr*,Expr*,char*);
  3284   3286   #endif
  3285         -void sqlite3DeleteFrom(Parse*, SrcList*, Expr*);
  3286         -void sqlite3Update(Parse*, SrcList*, ExprList*, Expr*, int);
         3287  +void sqlite3DeleteFrom(Parse*, SrcList*, Expr*, ExprList*, Expr*, Expr*);
         3288  +void sqlite3Update(Parse*, SrcList*, ExprList*,Expr*,int,ExprList*,Expr*,Expr*);
  3287   3289   WhereInfo *sqlite3WhereBegin(Parse*,SrcList*,Expr*,ExprList*,ExprList*,u16,int);
  3288   3290   void sqlite3WhereEnd(WhereInfo*);
  3289   3291   u64 sqlite3WhereOutputRowCount(WhereInfo*);
  3290   3292   int sqlite3WhereIsDistinct(WhereInfo*);
  3291   3293   int sqlite3WhereIsOrdered(WhereInfo*);
         3294  +int sqlite3WhereOrderedInnerLoop(WhereInfo*);
  3292   3295   int sqlite3WhereIsSorted(WhereInfo*);
  3293   3296   int sqlite3WhereContinueLabel(WhereInfo*);
  3294   3297   int sqlite3WhereBreakLabel(WhereInfo*);
  3295   3298   int sqlite3WhereOkOnePass(WhereInfo*, int*);
  3296   3299   int sqlite3ExprCodeGetColumn(Parse*, Table*, int, int, int, u8);
  3297   3300   void sqlite3ExprCodeGetColumnOfTable(Vdbe*, Table*, int, int, int);
  3298   3301   void sqlite3ExprCodeMove(Parse*, int, int, int);
................................................................................
  3378   3381   void sqlite3RegisterDateTimeFunctions(void);
  3379   3382   void sqlite3RegisterGlobalFunctions(void);
  3380   3383   int sqlite3SafetyCheckOk(sqlite3*);
  3381   3384   int sqlite3SafetyCheckSickOrOk(sqlite3*);
  3382   3385   void sqlite3ChangeCookie(Parse*, int);
  3383   3386   
  3384   3387   #if !defined(SQLITE_OMIT_VIEW) && !defined(SQLITE_OMIT_TRIGGER)
  3385         -void sqlite3MaterializeView(Parse*, Table*, Expr*, int);
         3388  +void sqlite3MaterializeView(Parse*, Table*, Expr*, ExprList*,Expr*,Expr*,int);
  3386   3389   #endif
  3387   3390   
  3388   3391   #ifndef SQLITE_OMIT_TRIGGER
  3389   3392     void sqlite3BeginTrigger(Parse*, Token*,Token*,int,int,IdList*,SrcList*,
  3390   3393                              Expr*,int, int);
  3391   3394     void sqlite3FinishTrigger(Parse*, TriggerStep*, Token*);
  3392   3395     void sqlite3DropTrigger(Parse*, SrcList*, int);

Changes to src/trigger.c.

   726    726   
   727    727       switch( pStep->op ){
   728    728         case TK_UPDATE: {
   729    729           sqlite3Update(pParse, 
   730    730             targetSrcList(pParse, pStep),
   731    731             sqlite3ExprListDup(db, pStep->pExprList, 0), 
   732    732             sqlite3ExprDup(db, pStep->pWhere, 0), 
   733         -          pParse->eOrconf
          733  +          pParse->eOrconf, 0, 0, 0
   734    734           );
   735    735           break;
   736    736         }
   737    737         case TK_INSERT: {
   738    738           sqlite3Insert(pParse, 
   739    739             targetSrcList(pParse, pStep),
   740    740             sqlite3SelectDup(db, pStep->pSelect, 0), 
................................................................................
   742    742             pParse->eOrconf
   743    743           );
   744    744           break;
   745    745         }
   746    746         case TK_DELETE: {
   747    747           sqlite3DeleteFrom(pParse, 
   748    748             targetSrcList(pParse, pStep),
   749         -          sqlite3ExprDup(db, pStep->pWhere, 0)
          749  +          sqlite3ExprDup(db, pStep->pWhere, 0), 0, 0, 0
   750    750           );
   751    751           break;
   752    752         }
   753    753         default: assert( pStep->op==TK_SELECT ); {
   754    754           SelectDest sDest;
   755    755           Select *pSelect = sqlite3SelectDup(db, pStep->pSelect, 0);
   756    756           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 */
................................................................................
   166    169   # define isView 0
   167    170   # define tmask 0
   168    171   #endif
   169    172   #ifdef SQLITE_OMIT_VIEW
   170    173   # undef isView
   171    174   # define isView 0
   172    175   #endif
          176  +
          177  +#ifdef SQLITE_ENABLE_UPDATE_DELETE_LIMIT
          178  +  if( !isView ){
          179  +    pWhere = sqlite3LimitWhere(
          180  +        pParse, pTabList, pWhere, pOrderBy, pLimit, pOffset, "UPDATE"
          181  +    );
          182  +    pOrderBy = 0;
          183  +    pLimit = pOffset = 0;
          184  +  }
          185  +#endif
   173    186   
   174    187     if( sqlite3ViewGetColumnNames(pParse, pTab) ){
   175    188       goto update_cleanup;
   176    189     }
   177    190     if( sqlite3IsReadOnly(pParse, pTab, tmask) ){
   178    191       goto update_cleanup;
   179    192     }
................................................................................
   327    340     }
   328    341   
   329    342     /* If we are trying to update a view, realize that view into
   330    343     ** an ephemeral table.
   331    344     */
   332    345   #if !defined(SQLITE_OMIT_VIEW) && !defined(SQLITE_OMIT_TRIGGER)
   333    346     if( isView ){
   334         -    sqlite3MaterializeView(pParse, pTab, pWhere, iDataCur);
          347  +    sqlite3MaterializeView(pParse, pTab, 
          348  +        pWhere, pOrderBy, pLimit, pOffset, iDataCur
          349  +    );
          350  +    pOrderBy = 0;
          351  +    pLimit = pOffset = 0;
   335    352     }
   336    353   #endif
   337    354   
   338    355     /* Resolve the column names in all the expressions in the
   339    356     ** WHERE clause.
   340    357     */
   341    358     if( sqlite3ResolveExprNames(&sNC, pWhere) ){
................................................................................
   661    678   
   662    679   update_cleanup:
   663    680     sqlite3AuthContextPop(&sContext);
   664    681     sqlite3DbFree(db, aXRef); /* Also frees aRegIdx[] and aToOpen[] */
   665    682     sqlite3SrcListDelete(db, pTabList);
   666    683     sqlite3ExprListDelete(db, pChanges);
   667    684     sqlite3ExprDelete(db, pWhere);
          685  +#if defined(SQLITE_ENABLE_UPDATE_DELETE_LIMIT) 
          686  +  sqlite3ExprListDelete(db, pOrderBy);
          687  +  sqlite3ExprDelete(db, pLimit);
          688  +  sqlite3ExprDelete(db, pOffset);
          689  +#endif
   668    690     return;
   669    691   }
   670    692   /* Make sure "isView" and other macros defined above are undefined. Otherwise
   671    693   ** they may interfere with compilation of other functions in this file
   672    694   ** (or in another file, if this file becomes part of the amalgamation).  */
   673    695   #ifdef isView
   674    696    #undef isView

Changes to src/vdbe.c.

  1976   1976   **
  1977   1977   ** Set the permutation used by the OP_Compare operator to be the array
  1978   1978   ** of integers in P4.
  1979   1979   **
  1980   1980   ** The permutation is only valid until the next OP_Compare that has
  1981   1981   ** the OPFLAG_PERMUTE bit set in P5. Typically the OP_Permutation should 
  1982   1982   ** occur immediately prior to the OP_Compare.
         1983  +**
         1984  +** The first integer in the P4 integer array is the length of the array
         1985  +** and does not become part of the permutation.
  1983   1986   */
  1984   1987   case OP_Permutation: {
  1985   1988     assert( pOp->p4type==P4_INTARRAY );
  1986   1989     assert( pOp->p4.ai );
  1987         -  aPermute = pOp->p4.ai;
         1990  +  aPermute = pOp->p4.ai + 1;
  1988   1991     break;
  1989   1992   }
  1990   1993   
  1991   1994   /* Opcode: Compare P1 P2 P3 P4 P5
  1992   1995   ** Synopsis: r[P1@P3] <-> r[P2@P3]
  1993   1996   **
  1994   1997   ** Compare two vectors of registers in reg(P1)..reg(P1+P3-1) (call this
................................................................................
  2286   2289     u32 offset;        /* Offset into the data */
  2287   2290     u32 szField;       /* Number of bytes in the content of a field */
  2288   2291     u32 avail;         /* Number of bytes of available data */
  2289   2292     u32 t;             /* A type code from the record header */
  2290   2293     u16 fx;            /* pDest->flags value */
  2291   2294     Mem *pReg;         /* PseudoTable input register */
  2292   2295   
         2296  +  pC = p->apCsr[pOp->p1];
  2293   2297     p2 = pOp->p2;
         2298  +
         2299  +  /* If the cursor cache is stale, bring it up-to-date */
         2300  +  rc = sqlite3VdbeCursorMoveto(&pC, &p2);
         2301  +
  2294   2302     assert( pOp->p3>0 && pOp->p3<=(p->nMem-p->nCursor) );
  2295   2303     pDest = &aMem[pOp->p3];
  2296   2304     memAboutToChange(p, pDest);
  2297   2305     assert( pOp->p1>=0 && pOp->p1<p->nCursor );
  2298         -  pC = p->apCsr[pOp->p1];
  2299   2306     assert( pC!=0 );
  2300   2307     assert( p2<pC->nField );
  2301   2308     aOffset = pC->aOffset;
  2302   2309   #ifndef SQLITE_OMIT_VIRTUALTABLE
  2303   2310     assert( pC->pVtabCursor==0 ); /* OP_Column never called on virtual table */
  2304   2311   #endif
  2305   2312     pCrsr = pC->pCursor;
  2306   2313     assert( pCrsr!=0 || pC->pseudoTableReg>0 ); /* pCrsr NULL on PseudoTables */
  2307   2314     assert( pCrsr!=0 || pC->nullRow );          /* pC->nullRow on PseudoTables */
  2308   2315   
  2309         -  /* If the cursor cache is stale, bring it up-to-date */
  2310         -  rc = sqlite3VdbeCursorMoveto(pC);
  2311   2316     if( rc ) goto abort_due_to_error;
  2312   2317     if( pC->cacheStatus!=p->cacheCtr ){
  2313   2318       if( pC->nullRow ){
  2314   2319         if( pCrsr==0 ){
  2315   2320           assert( pC->pseudoTableReg>0 );
  2316   2321           pReg = &aMem[pC->pseudoTableReg];
  2317   2322           assert( pReg->flags & MEM_Blob );
................................................................................
  3494   3499   */
  3495   3500   case OP_Close: {
  3496   3501     assert( pOp->p1>=0 && pOp->p1<p->nCursor );
  3497   3502     sqlite3VdbeFreeCursor(p, p->apCsr[pOp->p1]);
  3498   3503     p->apCsr[pOp->p1] = 0;
  3499   3504     break;
  3500   3505   }
         3506  +
         3507  +#ifdef SQLITE_ENABLE_COLUMN_USED_MASK
         3508  +/* Opcode: ColumnsUsed P1 * * P4 *
         3509  +**
         3510  +** This opcode (which only exists if SQLite was compiled with
         3511  +** SQLITE_ENABLE_COLUMN_USED_MASK) identifies which columns of the
         3512  +** table or index for cursor P1 are used.  P4 is a 64-bit integer
         3513  +** (P4_INT64) in which the first 63 bits are one for each of the
         3514  +** first 63 columns of the table or index that are actually used
         3515  +** by the cursor.  The high-order bit is set if any column after
         3516  +** the 64th is used.
         3517  +*/
         3518  +case OP_ColumnsUsed: {
         3519  +  VdbeCursor *pC;
         3520  +  pC = p->apCsr[pOp->p1];
         3521  +  assert( pC->pCursor );
         3522  +  pC->maskUsed = *(u64*)pOp->p4.pI64;
         3523  +  break;
         3524  +}
         3525  +#endif
  3501   3526   
  3502   3527   /* Opcode: SeekGE P1 P2 P3 P4 *
  3503   3528   ** Synopsis: key=r[P3@P4]
  3504   3529   **
  3505   3530   ** If cursor P1 refers to an SQL table (B-Tree that uses integer keys), 
  3506   3531   ** use the value in register P3 as the key.  If cursor P1 refers 
  3507   3532   ** to an SQL index, then P3 is the first in an array of P4 registers 
................................................................................
  3721   3746     VdbeBranchTaken(res!=0,2);
  3722   3747     if( res ){
  3723   3748       pc = pOp->p2 - 1;
  3724   3749     }
  3725   3750     break;
  3726   3751   }
  3727   3752   
  3728         -/* Opcode: Seek P1 P2 * * *
         3753  +/* Opcode: Seek P1 P2 P3 P4 *
  3729   3754   ** Synopsis:  intkey=r[P2]
  3730   3755   **
  3731   3756   ** P1 is an open table cursor and P2 is a rowid integer.  Arrange
  3732   3757   ** for P1 to move so that it points to the rowid given by P2.
  3733   3758   **
  3734   3759   ** This is actually a deferred seek.  Nothing actually happens until
  3735   3760   ** the cursor is used to read a record.  That way, if no reads
  3736   3761   ** occur, no unnecessary I/O happens.
         3762  +**
         3763  +** P4 may contain an array of integers (type P4_INTARRAY) containing
         3764  +** one entry for each column in the table P1 is open on. If so, then
         3765  +** parameter P3 is a cursor open on a database index. If array entry
         3766  +** a[i] is non-zero, then reading column (a[i]-1) from cursor P3 is 
         3767  +** equivalent to performing the deferred seek and then reading column i 
         3768  +** from P1.
  3737   3769   */
  3738   3770   case OP_Seek: {    /* in2 */
  3739   3771     VdbeCursor *pC;
  3740   3772   
  3741   3773     assert( pOp->p1>=0 && pOp->p1<p->nCursor );
  3742   3774     pC = p->apCsr[pOp->p1];
  3743   3775     assert( pC!=0 );
  3744   3776     assert( pC->pCursor!=0 );
  3745   3777     assert( pC->isTable );
  3746   3778     pC->nullRow = 0;
  3747   3779     pIn2 = &aMem[pOp->p2];
  3748   3780     pC->movetoTarget = sqlite3VdbeIntValue(pIn2);
  3749   3781     pC->deferredMoveto = 1;
         3782  +  assert( pOp->p4type==P4_INTARRAY || pOp->p4.ai==0 );
         3783  +  pC->aAltMap = pOp->p4.ai;
         3784  +  pC->pAltCursor = p->apCsr[pOp->p3];
  3750   3785     break;
  3751   3786   }
  3752   3787     
  3753   3788   
  3754   3789   /* Opcode: Found P1 P2 P3 P4 *
  3755   3790   ** Synopsis: key=r[P3@P4]
  3756   3791   **

Changes to src/vdbe.h.

   165    165   */
   166    166   Vdbe *sqlite3VdbeCreate(Parse*);
   167    167   int sqlite3VdbeAddOp0(Vdbe*,int);
   168    168   int sqlite3VdbeAddOp1(Vdbe*,int,int);
   169    169   int sqlite3VdbeAddOp2(Vdbe*,int,int,int);
   170    170   int sqlite3VdbeAddOp3(Vdbe*,int,int,int,int);
   171    171   int sqlite3VdbeAddOp4(Vdbe*,int,int,int,int,const char *zP4,int);
          172  +int sqlite3VdbeAddOp4Dup8(Vdbe*,int,int,int,int,const u8*,int);
   172    173   int sqlite3VdbeAddOp4Int(Vdbe*,int,int,int,int,int);
   173    174   int sqlite3VdbeAddOpList(Vdbe*, int nOp, VdbeOpList const *aOp, int iLineno);
   174    175   void sqlite3VdbeAddParseSchemaOp(Vdbe*,int,char*);
   175    176   void sqlite3VdbeChangeP1(Vdbe*, u32 addr, int P1);
   176    177   void sqlite3VdbeChangeP2(Vdbe*, u32 addr, int P2);
   177    178   void sqlite3VdbeChangeP3(Vdbe*, u32 addr, int P3);
   178    179   void sqlite3VdbeChangeP5(Vdbe*, u8 P5);

Changes to src/vdbeInt.h.

    56     56   **
    57     57   ** Cursors can also point to virtual tables, sorters, or "pseudo-tables".
    58     58   ** A pseudo-table is a single-row table implemented by registers.
    59     59   ** 
    60     60   ** Every cursor that the virtual machine has open is represented by an
    61     61   ** instance of the following structure.
    62     62   */
           63  +typedef struct VdbeCursor VdbeCursor;
    63     64   struct VdbeCursor {
    64     65     BtCursor *pCursor;    /* The cursor structure of the backend */
    65     66     Btree *pBt;           /* Separate file holding temporary table */
    66     67     KeyInfo *pKeyInfo;    /* Info about index keys needed by index cursors */
    67     68     int seekResult;       /* Result of previous sqlite3BtreeMoveto() */
    68     69     int pseudoTableReg;   /* Register holding pseudotable content. */
    69     70     i16 nField;           /* Number of fields in the header */
................................................................................
    79     80     Bool isTable:1;       /* True if a table requiring integer keys */
    80     81     Bool isOrdered:1;     /* True if the underlying table is BTREE_UNORDERED */
    81     82     Pgno pgnoRoot;        /* Root page of the open btree cursor */
    82     83     sqlite3_vtab_cursor *pVtabCursor;  /* The cursor for a virtual table */
    83     84     i64 seqCount;         /* Sequence counter */
    84     85     i64 movetoTarget;     /* Argument to the deferred sqlite3BtreeMoveto() */
    85     86     VdbeSorter *pSorter;  /* Sorter object for OP_SorterOpen cursors */
    86         -
           87  +  VdbeCursor *pAltCursor; /* Associated index cursor from which to read */
           88  +  int *aAltMap;           /* Mapping from table to index column numbers */
           89  +#ifdef SQLITE_ENABLE_COLUMN_USED_MASK
           90  +  u64 maskUsed;         /* Mask of columns used by this cursor */
           91  +#endif
    87     92     /* Cached information about the header for the data record that the
    88     93     ** cursor is currently pointing to.  Only valid if cacheStatus matches
    89     94     ** Vdbe.cacheCtr.  Vdbe.cacheCtr will never take on the value of
    90     95     ** CACHE_STALE and so setting cacheStatus=CACHE_STALE guarantees that
    91     96     ** the cache is out of date.
    92     97     **
    93     98     ** aRow might point to (ephemeral) data for the current row, or it might
................................................................................
   100    105     const u8 *aRow;       /* Data for the current row, if all on one page */
   101    106     u32 *aOffset;         /* Pointer to aType[nField] */
   102    107     u32 aType[1];         /* Type values for all entries in the record */
   103    108     /* 2*nField extra array elements allocated for aType[], beyond the one
   104    109     ** static element declared in the structure.  nField total array slots for
   105    110     ** aType[] and nField+1 array slots for aOffset[] */
   106    111   };
   107         -typedef struct VdbeCursor VdbeCursor;
   108    112   
   109    113   /*
   110    114   ** When a sub-program is executed (OP_Program), a structure of this type
   111    115   ** is allocated to store the current value of the program counter, as
   112    116   ** well as the current memory cell array and various other frame specific
   113    117   ** values stored in the Vdbe struct. When the sub-program is finished, 
   114    118   ** these values are copied back to the Vdbe from the VdbeFrame structure,
................................................................................
   389    393   #define VDBE_MAGIC_DEAD     0xb606c3c8    /* The VDBE has been deallocated */
   390    394   
   391    395   /*
   392    396   ** Function prototypes
   393    397   */
   394    398   void sqlite3VdbeFreeCursor(Vdbe *, VdbeCursor*);
   395    399   void sqliteVdbePopStack(Vdbe*,int);
   396         -int sqlite3VdbeCursorMoveto(VdbeCursor*);
          400  +int sqlite3VdbeCursorMoveto(VdbeCursor**, int*);
   397    401   int sqlite3VdbeCursorRestore(VdbeCursor*);
   398    402   #if defined(SQLITE_DEBUG) || defined(VDBE_PROFILE)
   399    403   void sqlite3VdbePrintOp(FILE*, int, Op*);
   400    404   #endif
   401    405   u32 sqlite3VdbeSerialTypeLen(u32);
   402    406   u32 sqlite3VdbeSerialType(Mem*, int);
   403    407   u32 sqlite3VdbeSerialPut(unsigned char*, Mem*, u32);

Changes to src/vdbeaux.c.

   217    217     const char *zP4,    /* The P4 operand */
   218    218     int p4type          /* P4 operand type */
   219    219   ){
   220    220     int addr = sqlite3VdbeAddOp3(p, op, p1, p2, p3);
   221    221     sqlite3VdbeChangeP4(p, addr, zP4, p4type);
   222    222     return addr;
   223    223   }
          224  +
          225  +/*
          226  +** Add an opcode that includes the p4 value with a P4_INT64 type.
          227  +*/
          228  +int sqlite3VdbeAddOp4Dup8(
          229  +  Vdbe *p,            /* Add the opcode to this VM */
          230  +  int op,             /* The new opcode */
          231  +  int p1,             /* The P1 operand */
          232  +  int p2,             /* The P2 operand */
          233  +  int p3,             /* The P3 operand */
          234  +  const u8 *zP4,      /* The P4 operand */
          235  +  int p4type          /* P4 operand type */
          236  +){
          237  +  char *p4copy = sqlite3DbMallocRaw(sqlite3VdbeDb(p), 8);
          238  +  if( p4copy ) memcpy(p4copy, zP4, 8);
          239  +  return sqlite3VdbeAddOp4(p, op, p1, p2, p3, p4copy, p4type);
          240  +}
   224    241   
   225    242   /*
   226    243   ** Add an OP_ParseSchema opcode.  This routine is broken out from
   227    244   ** sqlite3VdbeAddOp4() since it needs to also needs to mark all btrees
   228    245   ** as having been used.
   229    246   **
   230    247   ** The zWhere string must have been obtained from sqlite3_malloc().
................................................................................
  1119   1136       case P4_VTAB: {
  1120   1137         sqlite3_vtab *pVtab = pOp->p4.pVtab->pVtab;
  1121   1138         sqlite3_snprintf(nTemp, zTemp, "vtab:%p", pVtab);
  1122   1139         break;
  1123   1140       }
  1124   1141   #endif
  1125   1142       case P4_INTARRAY: {
  1126         -      sqlite3_snprintf(nTemp, zTemp, "intarray");
         1143  +      int i, j;
         1144  +      int *ai = pOp->p4.ai;
         1145  +      int n = ai[0];   /* The first element of an INTARRAY is always the
         1146  +                       ** count of the number of elements to follow */
         1147  +      zTemp[0] = '[';
         1148  +      for(i=j=1; i<n && j<nTemp-7; i++){
         1149  +        if( j>1 ) zTemp[j++] = ',';
         1150  +        sqlite3_snprintf(nTemp-j, zTemp+j, "%d", ai[i]);
         1151  +        j += sqlite3Strlen30(zTemp+j);
         1152  +      }
         1153  +      if( i<n ){
         1154  +        memcpy(zTemp+j, ",...]", 6);
         1155  +      }else{
         1156  +        memcpy(zTemp+j, "]", 2);
         1157  +      }
  1127   1158         break;
  1128   1159       }
  1129   1160       case P4_SUBPROGRAM: {
  1130   1161         sqlite3_snprintf(nTemp, zTemp, "program");
  1131   1162         break;
  1132   1163       }
  1133   1164       case P4_ADVANCE: {
................................................................................
  2814   2845   ** MoveTo now.  If no move is pending, check to see if the row has been
  2815   2846   ** deleted out from under the cursor and if it has, mark the row as
  2816   2847   ** a NULL row.
  2817   2848   **
  2818   2849   ** If the cursor is already pointing to the correct row and that row has
  2819   2850   ** not been deleted out from under the cursor, then this routine is a no-op.
  2820   2851   */
  2821         -int sqlite3VdbeCursorMoveto(VdbeCursor *p){
         2852  +int sqlite3VdbeCursorMoveto(VdbeCursor **pp, int *piCol){
         2853  +  VdbeCursor *p = *pp;
  2822   2854     if( p->deferredMoveto ){
         2855  +    int iMap;
         2856  +    if( p->aAltMap && (iMap = p->aAltMap[1+*piCol])>0 ){
         2857  +      *pp = p->pAltCursor;
         2858  +      *piCol = iMap - 1;
         2859  +      return SQLITE_OK;
         2860  +    }
  2823   2861       return handleDeferredMoveto(p);
  2824   2862     }
  2825   2863     if( p->pCursor && sqlite3BtreeCursorHasMoved(p->pCursor) ){
  2826   2864       return handleMovedCursor(p);
  2827   2865     }
  2828   2866     return SQLITE_OK;
  2829   2867   }

Changes to src/where.c.

    37     37   /*
    38     38   ** Return TRUE if the WHERE clause returns rows in ORDER BY order.
    39     39   ** Return FALSE if the output needs to be sorted.
    40     40   */
    41     41   int sqlite3WhereIsOrdered(WhereInfo *pWInfo){
    42     42     return pWInfo->nOBSat;
    43     43   }
           44  +
           45  +/*
           46  +** Return TRUE if the innermost loop of the WHERE clause implementation
           47  +** returns rows in ORDER BY order for complete run of the inner loop.
           48  +**
           49  +** Across multiple iterations of outer loops, the output rows need not be
           50  +** sorted.  As long as rows are sorted for just the innermost loop, this
           51  +** routine can return TRUE.
           52  +*/
           53  +int sqlite3WhereOrderedInnerLoop(WhereInfo *pWInfo){
           54  +  return pWInfo->bOrderedInnerLoop;
           55  +}
    44     56   
    45     57   /*
    46     58   ** Return the VDBE address or label to jump to in order to continue
    47     59   ** immediately with the next row of a WHERE clause.
    48     60   */
    49     61   int sqlite3WhereContinueLabel(WhereInfo *pWInfo){
    50     62     assert( pWInfo->iContinue!=0 );
................................................................................
   641    653     int *pnoCase      /* True if uppercase is equivalent to lowercase */
   642    654   ){
   643    655     const char *z = 0;         /* String on RHS of LIKE operator */
   644    656     Expr *pRight, *pLeft;      /* Right and left size of LIKE operator */
   645    657     ExprList *pList;           /* List of operands to the LIKE operator */
   646    658     int c;                     /* One character in z[] */
   647    659     int cnt;                   /* Number of non-wildcard prefix characters */
   648         -  char wc[3];                /* Wildcard characters */
          660  +  char wc[4];                /* Wildcard characters */
   649    661     sqlite3 *db = pParse->db;  /* Database connection */
   650    662     sqlite3_value *pVal = 0;
   651    663     int op;                    /* Opcode of pRight */
   652    664   
   653    665     if( !sqlite3IsLikeFunction(db, pExpr, pnoCase, wc) ){
   654    666       return 0;
   655    667     }
................................................................................
   679    691       }
   680    692       sqlite3VdbeSetVarmask(pParse->pVdbe, iCol);
   681    693       assert( pRight->op==TK_VARIABLE || pRight->op==TK_REGISTER );
   682    694     }else if( op==TK_STRING ){
   683    695       z = pRight->u.zToken;
   684    696     }
   685    697     if( z ){
          698  +    /* Count the number of prefix characters prior to the first wildcard */
   686    699       cnt = 0;
   687    700       while( (c=z[cnt])!=0 && c!=wc[0] && c!=wc[1] && c!=wc[2] ){
   688    701         cnt++;
          702  +      if( c==wc[3] && z[cnt]!=0 ){
          703  +        if( z[cnt++]>0xc0 ) while( (z[cnt]&0xc0)==0x80 ){ cnt++; }
          704  +      }
   689    705       }
          706  +
          707  +    /* The optimization is possible only if (1) the pattern does not begin
          708  +    ** with a wildcard and if (2) the non-wildcard prefix does not end with
          709  +    ** an (illegal 0xff) character.  The second condition is necessary so
          710  +    ** that we can increment the prefix key to find an upper bound for the
          711  +    ** range search. 
          712  +    */
   690    713       if( cnt!=0 && 255!=(u8)z[cnt-1] ){
   691    714         Expr *pPrefix;
          715  +
          716  +      /* A "complete" match if the pattern ends with "*" or "%" */
   692    717         *pisComplete = c==wc[0] && z[cnt+1]==0;
          718  +
          719  +      /* Get the pattern prefix.  Remove all escapes from the prefix. */
   693    720         pPrefix = sqlite3Expr(db, TK_STRING, z);
   694         -      if( pPrefix ) pPrefix->u.zToken[cnt] = 0;
          721  +      if( pPrefix ){
          722  +        int iFrom, iTo;
          723  +        char *zNew = pPrefix->u.zToken;
          724  +        zNew[cnt] = 0;
          725  +        for(iFrom=iTo=0; iFrom<cnt; iFrom++){
          726  +          if( zNew[iFrom]==wc[3] ) iFrom++;
          727  +          zNew[iTo++] = zNew[iFrom];
          728  +        }
          729  +        zNew[iTo] = 0;
          730  +      }
   695    731         *ppPrefix = pPrefix;
          732  +
          733  +      /* If the RHS pattern is a bound parameter, make arrangements to
          734  +      ** reprepare the statement when that parameter is rebound */
   696    735         if( op==TK_VARIABLE ){
   697    736           Vdbe *v = pParse->pVdbe;
   698    737           sqlite3VdbeSetVarmask(v, pRight->iColumn);
   699    738           if( *pisComplete && pRight->u.zToken[1] ){
   700    739             /* If the rhs of the LIKE expression is a variable, and the current
   701    740             ** value of the variable means there is no need to invoke the LIKE
   702    741             ** function, then no OP_Variable will be added to the program.
................................................................................
  2205   2244       }
  2206   2245       if( roundUp ){
  2207   2246         iGap = (iGap*2)/3;
  2208   2247       }else{
  2209   2248         iGap = iGap/3;
  2210   2249       }
  2211   2250       aStat[0] = iLower + iGap;
  2212         -    aStat[1] = pIdx->aAvgEq[iCol];
         2251  +    aStat[1] = pIdx->aAvgEq[nField-1];
  2213   2252     }
  2214   2253   
  2215   2254     /* Restore the pRec->nField value before returning.  */
  2216   2255     pRec->nField = nField;
  2217   2256     return i;
  2218   2257   }
  2219   2258   #endif /* SQLITE_ENABLE_STAT3_OR_STAT4 */
................................................................................
  3231   3270       assert( pOp!=0 );
  3232   3271       assert( pOp->opcode==OP_String8 
  3233   3272               || pTerm->pWC->pWInfo->pParse->db->mallocFailed );
  3234   3273       pOp->p3 = pLevel->iLikeRepCntr;
  3235   3274       pOp->p5 = 1;
  3236   3275     }
  3237   3276   }
         3277  +
         3278  +/*
         3279  +** Cursor iCur is open on an intkey b-tree (a table). Register iRowid contains
         3280  +** a rowid value just read from cursor iIdxCur, open on index pIdx. This
         3281  +** function generates code to do a deferred seek of cursor iCur to the 
         3282  +** rowid stored in register iRowid.
         3283  +**
         3284  +** Normally, this is just:
         3285  +**
         3286  +**   OP_Seek $iCur $iRowid
         3287  +**
         3288  +** However, if the scan currently being coded is a branch of an OR-loop and
         3289  +** the statement currently being coded is a SELECT, then P3 of the OP_Seek
         3290  +** is set to iIdxCur and P4 is set to point to an array of integers
         3291  +** containing one entry for each column of the table cursor iCur is open 
         3292  +** on. For each table column, if the column is the i'th column of the 
         3293  +** index, then the corresponding array entry is set to (i+1). If the column
         3294  +** does not appear in the index at all, the array entry is set to 0.
         3295  +*/
         3296  +static void codeDeferredSeek(
         3297  +  WhereInfo *pWInfo,              /* Where clause context */
         3298  +  Index *pIdx,                    /* Index scan is using */
         3299  +  int iCur,                       /* Cursor for IPK b-tree */
         3300  +  int iRowid,                     /* Register containing rowid to seek to */
         3301  +  int iIdxCur                     /* Index cursor */
         3302  +){
         3303  +  Parse *pParse = pWInfo->pParse; /* Parse context */
         3304  +  Vdbe *v = pParse->pVdbe;        /* Vdbe to generate code within */
         3305  +
         3306  +  assert( iIdxCur>0 );
         3307  +  assert( pIdx->aiColumn[pIdx->nColumn-1]==-1 );
         3308  +  
         3309  +  sqlite3VdbeAddOp3(v, OP_Seek, iCur, iRowid, iIdxCur);
         3310  +  if( (pWInfo->wctrlFlags & WHERE_FORCE_TABLE)
         3311  +   && DbMaskAllZero(sqlite3ParseToplevel(pParse)->writeMask)
         3312  +  ){
         3313  +    int i;
         3314  +    Table *pTab = pIdx->pTable;
         3315  +    int *ai = (int*)sqlite3DbMallocZero(pParse->db, sizeof(int)*(pTab->nCol+1));
         3316  +    if( ai ){
         3317  +      ai[0] = pTab->nCol;
         3318  +      for(i=0; i<pIdx->nColumn-1; i++){
         3319  +        assert( pIdx->aiColumn[i]<pTab->nCol );
         3320  +        if( pIdx->aiColumn[i]>=0 ) ai[pIdx->aiColumn[i]+1] = i+1;
         3321  +      }
         3322  +      sqlite3VdbeChangeP4(v, -1, (char*)ai, P4_INTARRAY);
         3323  +    }
         3324  +  }
         3325  +}
  3238   3326   
  3239   3327   /*
  3240   3328   ** Generate code for the start of the iLevel-th loop in the WHERE clause
  3241   3329   ** implementation described by pWInfo.
  3242   3330   */
  3243   3331   static Bitmask codeOneLoopStart(
  3244   3332     WhereInfo *pWInfo,   /* Complete information about the WHERE clause */
................................................................................
  3710   3798       disableTerm(pLevel, pRangeEnd);
  3711   3799       if( omitTable ){
  3712   3800         /* pIdx is a covering index.  No need to access the main table. */
  3713   3801       }else if( HasRowid(pIdx->pTable) ){
  3714   3802         iRowidReg = ++pParse->nMem;
  3715   3803         sqlite3VdbeAddOp2(v, OP_IdxRowid, iIdxCur, iRowidReg);
  3716   3804         sqlite3ExprCacheStore(pParse, iCur, -1, iRowidReg);
  3717         -      sqlite3VdbeAddOp2(v, OP_Seek, iCur, iRowidReg);  /* Deferred seek */
         3805  +      codeDeferredSeek(pWInfo, pIdx, iCur, iRowidReg, iIdxCur);
  3718   3806       }else if( iCur!=iIdxCur ){
  3719   3807         Index *pPk = sqlite3PrimaryKeyIndex(pIdx->pTable);
  3720   3808         iRowidReg = sqlite3GetTempRange(pParse, pPk->nKeyCol);
  3721   3809         for(j=0; j<pPk->nKeyCol; j++){
  3722   3810           k = sqlite3ColumnOfIndex(pIdx, pPk->aiColumn[j]);
  3723   3811           sqlite3VdbeAddOp3(v, OP_Column, iIdxCur, k, iRowidReg+j);
  3724   3812         }
................................................................................
  5598   5686   ** the pOrderBy terms can be matched in any order.  With ORDER BY, the 
  5599   5687   ** pOrderBy terms must be matched in strict left-to-right order.
  5600   5688   */
  5601   5689   static i8 wherePathSatisfiesOrderBy(
  5602   5690     WhereInfo *pWInfo,    /* The WHERE clause */
  5603   5691     ExprList *pOrderBy,   /* ORDER BY or GROUP BY or DISTINCT clause to check */
  5604   5692     WherePath *pPath,     /* The WherePath to check */
  5605         -  u16 wctrlFlags,       /* Might contain WHERE_GROUPBY or WHERE_DISTINCTBY */
         5693  +  u16 wctrlFlags,       /* WHERE_GROUPBY, _DISTINCTBY  or _ORDERBY_LIMIT */
  5606   5694     u16 nLoop,            /* Number of entries in pPath->aLoop[] */
  5607   5695     WhereLoop *pLast,     /* Add this WhereLoop to the end of pPath->aLoop[] */
  5608   5696     Bitmask *pRevMask     /* OUT: Mask of WhereLoops to run in reverse order */
  5609   5697   ){
  5610   5698     u8 revSet;            /* True if rev is known */
  5611   5699     u8 rev;               /* Composite sort order */
  5612   5700     u8 revIdx;            /* Index sort order */
  5613   5701     u8 isOrderDistinct;   /* All prior WhereLoops are order-distinct */
  5614   5702     u8 distinctColumns;   /* True if the loop has UNIQUE NOT NULL columns */
  5615   5703     u8 isMatch;           /* iColumn matches a term of the ORDER BY clause */
         5704  +  u16 eqOpMask;         /* Allowed equality operators */
  5616   5705     u16 nKeyCol;          /* Number of key columns in pIndex */
  5617   5706     u16 nColumn;          /* Total number of ordered columns in the index */
  5618   5707     u16 nOrderBy;         /* Number terms in the ORDER BY clause */
  5619   5708     int iLoop;            /* Index of WhereLoop in pPath being processed */
  5620   5709     int i, j;             /* Loop counters */
  5621   5710     int iCur;             /* Cursor number for current WhereLoop */
  5622   5711     int iColumn;          /* A column number within table iCur */
................................................................................
  5659   5748     nOrderBy = pOrderBy->nExpr;
  5660   5749     testcase( nOrderBy==BMS-1 );
  5661   5750     if( nOrderBy>BMS-1 ) return 0;  /* Cannot optimize overly large ORDER BYs */
  5662   5751     isOrderDistinct = 1;
  5663   5752     obDone = MASKBIT(nOrderBy)-1;
  5664   5753     orderDistinctMask = 0;
  5665   5754     ready = 0;
         5755  +  eqOpMask = WO_EQ | WO_ISNULL;
         5756  +  if( wctrlFlags & WHERE_ORDERBY_LIMIT ) eqOpMask |= WO_IN;
  5666   5757     for(iLoop=0; isOrderDistinct && obSat<obDone && iLoop<=nLoop; iLoop++){
  5667   5758       if( iLoop>0 ) ready |= pLoop->maskSelf;
         5759  +    if( iLoop<nLoop ){
         5760  +      pLoop = pPath->aLoop[iLoop];
         5761  +      if( wctrlFlags & WHERE_ORDERBY_LIMIT ) continue;
         5762  +    }else{
         5763  +      pLoop = pLast;
         5764  +    }
  5668   5765       pLoop = iLoop<nLoop ? pPath->aLoop[iLoop] : pLast;
  5669   5766       if( pLoop->wsFlags & WHERE_VIRTUALTABLE ){
  5670   5767         if( pLoop->u.vtab.isOrdered ) obSat = obDone;
  5671   5768         break;
  5672   5769       }
  5673   5770       iCur = pWInfo->pTabList->a[pLoop->iTab].iCursor;
  5674   5771   
................................................................................
  5679   5776       */
  5680   5777       for(i=0; i<nOrderBy; i++){
  5681   5778         if( MASKBIT(i) & obSat ) continue;
  5682   5779         pOBExpr = sqlite3ExprSkipCollate(pOrderBy->a[i].pExpr);
  5683   5780         if( pOBExpr->op!=TK_COLUMN ) continue;
  5684   5781         if( pOBExpr->iTable!=iCur ) continue;
  5685   5782         pTerm = findTerm(&pWInfo->sWC, iCur, pOBExpr->iColumn,
  5686         -                       ~ready, WO_EQ|WO_ISNULL, 0);
         5783  +                       ~ready, eqOpMask, 0);
  5687   5784         if( pTerm==0 ) continue;
         5785  +      if( pTerm->eOperator==WO_IN ){
         5786  +        /* IN terms are only valid for sorting in the ORDER BY LIMIT 
         5787  +        ** optimization, and then only if they are actually used
         5788  +        ** by the query plan */
         5789  +        assert( wctrlFlags & WHERE_ORDERBY_LIMIT );
         5790  +        for(j=0; j<pLoop->nLTerm && pTerm!=pLoop->aLTerm[j]; j++){}
         5791  +        if( j>=pLoop->nLTerm ) continue;
         5792  +      }
  5688   5793         if( (pTerm->eOperator&WO_EQ)!=0 && pOBExpr->iColumn>=0 ){
  5689   5794           const char *z1, *z2;
  5690   5795           pColl = sqlite3ExprCollSeq(pWInfo->pParse, pOrderBy->a[i].pExpr);
  5691   5796           if( !pColl ) pColl = db->pDfltColl;
  5692   5797           z1 = pColl->zName;
  5693   5798           pColl = sqlite3ExprCollSeq(pWInfo->pParse, pTerm->pExpr);
  5694   5799           if( !pColl ) pColl = db->pDfltColl;
................................................................................
  5717   5822         ** that are not constrained by == or IN.
  5718   5823         */
  5719   5824         rev = revSet = 0;
  5720   5825         distinctColumns = 0;
  5721   5826         for(j=0; j<nColumn; j++){
  5722   5827           u8 bOnce;   /* True to run the ORDER BY search loop */
  5723   5828   
  5724         -        /* Skip over == and IS NULL terms */
         5829  +        /* Skip over == and IS NULL terms
         5830  +        ** (Also skip IN terms when doing WHERE_ORDERBY_LIMIT processing)
         5831  +        */
  5725   5832           if( j<pLoop->u.btree.nEq
  5726   5833            && pLoop->nSkip==0
  5727         -         && ((i = pLoop->aLTerm[j]->eOperator) & (WO_EQ|WO_ISNULL))!=0
         5834  +         && ((i = pLoop->aLTerm[j]->eOperator) & eqOpMask)!=0
  5728   5835           ){
  5729   5836             if( i & WO_ISNULL ){
  5730   5837               testcase( isOrderDistinct );
  5731   5838               isOrderDistinct = 0;
  5732   5839             }
  5733   5840             continue;  
  5734   5841           }
................................................................................
  6086   6193              && (rCost>mxCost || (rCost==mxCost && rUnsorted>=mxUnsorted))
  6087   6194             ){
  6088   6195               /* The current candidate is no better than any of the mxChoice
  6089   6196               ** paths currently in the best-so-far buffer.  So discard
  6090   6197               ** this candidate as not viable. */
  6091   6198   #ifdef WHERETRACE_ENABLED /* 0x4 */
  6092   6199               if( sqlite3WhereTrace&0x4 ){
  6093         -              sqlite3DebugPrintf("Skip   %s cost=%-3d,%3d order=%c\n",
  6094         -                  wherePathName(pFrom, iLoop, pWLoop), rCost, nOut,
         6200  +              sqlite3DebugPrintf("Skip   %s cost=%-3d,%3d,%3d order=%c\n",
         6201  +                  wherePathName(pFrom, iLoop, pWLoop), rCost, nOut, rUnsorted,
  6095   6202                     isOrdered>=0 ? isOrdered+'0' : '?');
  6096   6203               }
  6097   6204   #endif
  6098   6205               continue;
  6099   6206             }
  6100   6207             /* If we reach this points it means that the new candidate path
  6101   6208             ** needs to be added to the set of best-so-far paths. */
................................................................................
  6105   6212             }else{
  6106   6213               /* New path replaces the prior worst to keep count below mxChoice */
  6107   6214               jj = mxI;
  6108   6215             }
  6109   6216             pTo = &aTo[jj];
  6110   6217   #ifdef WHERETRACE_ENABLED /* 0x4 */
  6111   6218             if( sqlite3WhereTrace&0x4 ){
  6112         -            sqlite3DebugPrintf("New    %s cost=%-3d,%3d order=%c\n",
  6113         -                wherePathName(pFrom, iLoop, pWLoop), rCost, nOut,
         6219  +            sqlite3DebugPrintf("New    %s cost=%-3d,%3d,%3d order=%c\n",
         6220  +                wherePathName(pFrom, iLoop, pWLoop), rCost, nOut, rUnsorted,
  6114   6221                   isOrdered>=0 ? isOrdered+'0' : '?');
  6115   6222             }
  6116   6223   #endif
  6117   6224           }else{
  6118   6225             /* Control reaches here if best-so-far path pTo=aTo[jj] covers the
  6119         -          ** same set of loops and has the sam isOrdered setting as the
         6226  +          ** same set of loops and has the same isOrdered setting as the
  6120   6227             ** candidate path.  Check to see if the candidate should replace
  6121         -          ** pTo or if the candidate should be skipped */
  6122         -          if( pTo->rCost<rCost || (pTo->rCost==rCost && pTo->nRow<=nOut) ){
         6228  +          ** pTo or if the candidate should be skipped.
         6229  +          ** 
         6230  +          ** The conditional is an expanded vector comparison equivalent to:
         6231  +          **   (pTo->rCost,pTo->nRow,pTo->rUnsorted) <= (rCost,nOut,rUnsorted)
         6232  +          */
         6233  +          if( pTo->rCost<rCost 
         6234  +           || (pTo->rCost==rCost
         6235  +               && (pTo->nRow<nOut
         6236  +                   || (pTo->nRow==nOut && pTo->rUnsorted<=rUnsorted)
         6237  +                  )
         6238  +              )
         6239  +          ){
  6123   6240   #ifdef WHERETRACE_ENABLED /* 0x4 */
  6124   6241               if( sqlite3WhereTrace&0x4 ){
  6125   6242                 sqlite3DebugPrintf(
  6126         -                  "Skip   %s cost=%-3d,%3d order=%c",
  6127         -                  wherePathName(pFrom, iLoop, pWLoop), rCost, nOut,
         6243  +                  "Skip   %s cost=%-3d,%3d,%3d order=%c",
         6244  +                  wherePathName(pFrom, iLoop, pWLoop), rCost, nOut, rUnsorted,
  6128   6245                     isOrdered>=0 ? isOrdered+'0' : '?');
  6129         -              sqlite3DebugPrintf("   vs %s cost=%-3d,%d order=%c\n",
         6246  +              sqlite3DebugPrintf("   vs %s cost=%-3d,%3d,%3d order=%c\n",
  6130   6247                     wherePathName(pTo, iLoop+1, 0), pTo->rCost, pTo->nRow,
  6131         -                  pTo->isOrdered>=0 ? pTo->isOrdered+'0' : '?');
         6248  +                  pTo->rUnsorted, pTo->isOrdered>=0 ? pTo->isOrdered+'0' : '?');
  6132   6249               }
  6133   6250   #endif
  6134   6251               /* Discard the candidate path from further consideration */
  6135   6252               testcase( pTo->rCost==rCost );
  6136   6253               continue;
  6137   6254             }
  6138   6255             testcase( pTo->rCost==rCost+1 );
  6139   6256             /* Control reaches here if the candidate path is better than the
  6140   6257             ** pTo path.  Replace pTo with the candidate. */
  6141   6258   #ifdef WHERETRACE_ENABLED /* 0x4 */
  6142   6259             if( sqlite3WhereTrace&0x4 ){
  6143   6260               sqlite3DebugPrintf(
  6144         -                "Update %s cost=%-3d,%3d order=%c",
  6145         -                wherePathName(pFrom, iLoop, pWLoop), rCost, nOut,
         6261  +                "Update %s cost=%-3d,%3d,%3d order=%c",
         6262  +                wherePathName(pFrom, iLoop, pWLoop), rCost, nOut, rUnsorted,
  6146   6263                   isOrdered>=0 ? isOrdered+'0' : '?');
  6147         -            sqlite3DebugPrintf("  was %s cost=%-3d,%3d order=%c\n",
         6264  +            sqlite3DebugPrintf("  was %s cost=%-3d,%3d,%3d order=%c\n",
  6148   6265                   wherePathName(pTo, iLoop+1, 0), pTo->rCost, pTo->nRow,
  6149         -                pTo->isOrdered>=0 ? pTo->isOrdered+'0' : '?');
         6266  +                pTo->rUnsorted, pTo->isOrdered>=0 ? pTo->isOrdered+'0' : '?');
  6150   6267             }
  6151   6268   #endif
  6152   6269           }
  6153   6270           /* pWLoop is a winner.  Add it to the set of best so far */
  6154   6271           pTo->maskLoop = pFrom->maskLoop | pWLoop->maskSelf;
  6155   6272           pTo->revLoop = revMask;
  6156   6273           pTo->nRow = nOut;
................................................................................
  6233   6350     if( pWInfo->pOrderBy ){
  6234   6351       if( pWInfo->wctrlFlags & WHERE_DISTINCTBY ){
  6235   6352         if( pFrom->isOrdered==pWInfo->pOrderBy->nExpr ){
  6236   6353           pWInfo->eDistinct = WHERE_DISTINCT_ORDERED;
  6237   6354         }
  6238   6355       }else{
  6239   6356         pWInfo->nOBSat = pFrom->isOrdered;
  6240         -      if( pWInfo->nOBSat<0 ) pWInfo->nOBSat = 0;
  6241   6357         pWInfo->revMask = pFrom->revLoop;
         6358  +      if( pWInfo->nOBSat<=0 ){
         6359  +        pWInfo->nOBSat = 0;
         6360  +        if( nLoop>0 && (pFrom->aLoop[nLoop-1]->wsFlags & WHERE_ONEROW)==0 ){
         6361  +          Bitmask m = 0;
         6362  +          int rc = wherePathSatisfiesOrderBy(pWInfo, pWInfo->pOrderBy, pFrom,
         6363  +                      WHERE_ORDERBY_LIMIT, nLoop-1, pFrom->aLoop[nLoop-1], &m);
         6364  +          if( rc==pWInfo->pOrderBy->nExpr ){
         6365  +            pWInfo->bOrderedInnerLoop = 1;
         6366  +            pWInfo->revMask = m;
         6367  +          }
         6368  +        }
         6369  +      }
  6242   6370       }
  6243   6371       if( (pWInfo->wctrlFlags & WHERE_SORTBYGROUP)
  6244   6372           && pWInfo->nOBSat==pWInfo->pOrderBy->nExpr
  6245   6373       ){
  6246   6374         Bitmask revMask = 0;
  6247   6375         int nOrder = wherePathSatisfiesOrderBy(pWInfo, pWInfo->pOrderBy, 
  6248   6376             pFrom, 0, nLoop-1, pFrom->aLoop[nLoop-1], &revMask
................................................................................
  6759   6887           Bitmask b = pTabItem->colUsed;
  6760   6888           int n = 0;
  6761   6889           for(; b; b=b>>1, n++){}
  6762   6890           sqlite3VdbeChangeP4(v, sqlite3VdbeCurrentAddr(v)-1, 
  6763   6891                               SQLITE_INT_TO_PTR(n), P4_INT32);
  6764   6892           assert( n<=pTab->nCol );
  6765   6893         }
         6894  +#ifdef SQLITE_ENABLE_COLUMN_USED_MASK
         6895  +      sqlite3VdbeAddOp4Dup8(v, OP_ColumnsUsed, pTabItem->iCursor, 0, 0,
         6896  +                            (const u8*)&pTabItem->colUsed, P4_INT64);
         6897  +#endif
  6766   6898       }else{
  6767   6899         sqlite3TableLock(pParse, iDb, pTab->tnum, 0, pTab->zName);
  6768   6900       }
  6769   6901       if( pLoop->wsFlags & WHERE_INDEXED ){
  6770   6902         Index *pIx = pLoop->u.btree.pIndex;
  6771   6903         int iIndexCur;
  6772   6904         int op = OP_OpenRead;
................................................................................
  6804   6936           if( (pLoop->wsFlags & WHERE_CONSTRAINT)!=0
  6805   6937            && (pLoop->wsFlags & (WHERE_COLUMN_RANGE|WHERE_SKIPSCAN))==0
  6806   6938            && (pWInfo->wctrlFlags&WHERE_ORDERBY_MIN)==0
  6807   6939           ){
  6808   6940             sqlite3VdbeChangeP5(v, OPFLAG_SEEKEQ); /* Hint to COMDB2 */
  6809   6941           }
  6810   6942           VdbeComment((v, "%s", pIx->zName));
         6943  +#ifdef SQLITE_ENABLE_COLUMN_USED_MASK
         6944  +        {
         6945  +          u64 colUsed = 0;
         6946  +          int ii, jj;
         6947  +          for(ii=0; ii<pIx->nColumn; ii++){
         6948  +            jj = pIx->aiColumn[ii];
         6949  +            if( jj<0 ) continue;
         6950  +            if( jj>63 ) jj = 63;
         6951  +            if( (pTabItem->colUsed & MASKBIT(jj))==0 ) continue;
         6952  +            colUsed |= ((u64)1)<<(ii<63 ? ii : 63);
         6953  +          }
         6954  +          sqlite3VdbeAddOp4Dup8(v, OP_ColumnsUsed, iIndexCur, 0, 0,
         6955  +                                (u8*)&colUsed, P4_INT64);
         6956  +        }
         6957  +#endif /* SQLITE_ENABLE_COLUMN_USED_MASK */
  6811   6958         }
  6812   6959       }
  6813   6960       if( iDb>=0 ) sqlite3CodeVerifySchema(pParse, iDb);
  6814   6961       notReady &= ~getMask(&pWInfo->sMaskSet, pTabItem->iCursor);
  6815   6962     }
  6816   6963     pWInfo->iTop = sqlite3VdbeCurrentAddr(v);
  6817   6964     if( db->mallocFailed ) goto whereBeginError;

Changes to src/whereInt.h.

   408    408     u16 wctrlFlags;           /* Flags originally passed to sqlite3WhereBegin() */
   409    409     i8 nOBSat;                /* Number of ORDER BY terms satisfied by indices */
   410    410     u8 sorted;                /* True if really sorted (not just grouped) */
   411    411     u8 okOnePass;             /* Ok to use one-pass algorithm for UPDATE/DELETE */
   412    412     u8 untestedTerms;         /* Not all WHERE terms resolved by outer loop */
   413    413     u8 eDistinct;             /* One of the WHERE_DISTINCT_* values below */
   414    414     u8 nLevel;                /* Number of nested loop */
          415  +  u8 bOrderedInnerLoop;     /* True if only the inner-most loop is ordered */
   415    416     int iTop;                 /* The very beginning of the WHERE loop */
   416    417     int iContinue;            /* Jump here to continue with next record */
   417    418     int iBreak;               /* Jump here to break out of the loop */
   418    419     int savedNQueryLoop;      /* pParse->nQueryLoop outside the WHERE loop */
   419    420     int aiCurOnePass[2];      /* OP_OpenWrite cursors for the ONEPASS opt */
   420    421     WhereMaskSet sMaskSet;    /* Map cursor numbers to bitmasks */
   421    422     WhereClause sWC;          /* Decomposition of the WHERE clause */

Changes to test/join5.test.

   156    156     CREATE TABLE x2(b NOT NULL);
   157    157     CREATE TABLE x3(c, d);
   158    158     INSERT INTO x3 VALUES('a', NULL);
   159    159     INSERT INTO x3 VALUES('b', NULL);
   160    160     INSERT INTO x3 VALUES('c', NULL);
   161    161     SELECT * FROM x1 LEFT JOIN x2 JOIN x3 WHERE x3.d = x2.b;
   162    162   } {}
          163  +
          164  +# Ticket https://www.sqlite.org/src/tktview/c2a19d81652f40568c770c43 on
          165  +# 2015-08-20.  LEFT JOIN and the push-down optimization.
          166  +#
          167  +do_execsql_test join6-4.1 {
          168  +  SELECT *
          169  +  FROM (
          170  +      SELECT 'apple' fruit
          171  +      UNION ALL SELECT 'banana'
          172  +  ) a
          173  +  JOIN (
          174  +      SELECT 'apple' fruit
          175  +      UNION ALL SELECT 'banana'
          176  +  ) b ON a.fruit=b.fruit
          177  +  LEFT JOIN (
          178  +      SELECT 1 isyellow
          179  +  ) c ON b.fruit='banana';
          180  +} {apple apple {} banana banana 1}
          181  +do_execsql_test join6-4.2 {
          182  +  SELECT *
          183  +    FROM (SELECT 'apple' fruit UNION ALL SELECT 'banana')
          184  +         LEFT JOIN (SELECT 1) ON fruit='banana';
          185  +} {apple {} banana 1}
   163    186   
   164    187   finish_test

Changes to test/like.test.

   944    944     SELECT id FROM t12nc WHERE x LIKE 'abc%' COLLATE binary ORDER BY +id;
   945    945   } {/SEARCH/}
   946    946   do_execsql_test like-12.16 {
   947    947     EXPLAIN QUERY PLAN
   948    948     SELECT id FROM t12b WHERE x LIKE 'abc%' COLLATE binary ORDER BY +id;
   949    949   } {/SCAN/}
   950    950   
          951  +
          952  +# As of 2017-07-27 (3.21.0) the LIKE optimization works with ESCAPE as
          953  +# long as the ESCAPE is a single-byte literal.
          954  +#
          955  +db close
          956  +sqlite3 db :memory:
          957  +do_execsql_test like-15.100 {
          958  +  CREATE TABLE t15(x TEXT COLLATE nocase, y, PRIMARY KEY(x));
          959  +  INSERT INTO t15(x,y) VALUES
          960  +    ('abcde',1), ('ab%de',2), ('a_cde',3),
          961  +    ('uvwxy',11),('uvwx%',12),('uvwx_',13),
          962  +    ('_bcde',21),('%bcde',22),
          963  +    ('abcd_',31),('abcd%',32),
          964  +    ('ab%xy',41);
          965  +  SELECT y FROM t15 WHERE x LIKE 'ab/%d%' ESCAPE '/';
          966  +} {2}
          967  +do_execsql_test like-15.101 {
          968  +  EXPLAIN QUERY PLAN
          969  +  SELECT y FROM t15 WHERE x LIKE 'ab/%d%' ESCAPE '/';
          970  +} {/SEARCH/}
          971  +do_execsql_test like-15.102 {
          972  +  EXPLAIN QUERY PLAN
          973  +  SELECT y FROM t15 WHERE x LIKE 'ab/%d%' ESCAPE '//';
          974  +} {/SCAN/}
          975  +do_execsql_test like-15.103 {
          976  +  EXPLAIN QUERY PLAN
          977  +  SELECT y FROM t15 WHERE x LIKE 'ab/%d%' ESCAPE '';
          978  +} {/SCAN/}
          979  +do_execsql_test like-15.110 {
          980  +  SELECT y FROM t15 WHERE x LIKE 'abcdx%%' ESCAPE 'x';
          981  +} {32}
          982  +do_execsql_test like-15.111 {
          983  +  SELECT y FROM t15 WHERE x LIKE 'abx%%' ESCAPE 'x' ORDER BY +y
          984  +} {2 41}
          985  +do_execsql_test like-15.112 {
          986  +  EXPLAIN QUERY PLAN
          987  +  SELECT y FROM t15 WHERE x LIKE 'abx%%' ESCAPE 'x' ORDER BY +y
          988  +} {/SEARCH/}
          989  +do_execsql_test like-15.120 {
          990  +  SELECT y FROM t15 WHERE x LIKE '/%bc%' ESCAPE '/';
          991  +} {22}
          992  +do_execsql_test like-15.121 {
          993  +  EXPLAIN QUERY PLAN
          994  +  SELECT y FROM t15 WHERE x LIKE '/%bc%' ESCAPE '/';
          995  +} {/SEARCH/}
   951    996   
   952    997   finish_test

Added test/limit2.test.

            1  +# 2016-05-20
            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 in combination with ORDER BY
           13  +# and in particular, the optimizations in the inner loop that cause an
           14  +# early exit of the inner loop when the LIMIT is reached and the inner
           15  +# loop is emitting rows in ORDER BY order.
           16  +
           17  +
           18  +set testdir [file dirname $argv0]
           19  +source $testdir/tester.tcl
           20  +
           21  +do_execsql_test limit2-100 {
           22  +  CREATE TABLE t1(a,b);
           23  +  WITH RECURSIVE c(x) AS (VALUES(1) UNION ALL SELECT x+1 FROM c WHERE x<1000)
           24  +    INSERT INTO t1(a,b) SELECT 1, (x*17)%1000 + 1000 FROM c;
           25  +  INSERT INTO t1(a,b) VALUES(2,2),(3,1006),(4,4),(5,9999);
           26  +  CREATE INDEX t1ab ON t1(a,b);
           27  +}
           28  +set sqlite_search_count 0
           29  +do_execsql_test limit2-100.1 {
           30  +  SELECT a, b, '|' FROM t1 WHERE a IN (2,4,5,3,1) ORDER BY b LIMIT 5;
           31  +} {2 2 | 4 4 | 1 1000 | 1 1001 | 1 1002 |}
           32  +set fast_count $sqlite_search_count
           33  +set sqlite_search_count 0
           34  +do_execsql_test limit2-100.2 {
           35  +  SELECT a, b, '|' FROM t1 WHERE a IN (2,4,5,3,1) ORDER BY +b LIMIT 5;
           36  +} {2 2 | 4 4 | 1 1000 | 1 1001 | 1 1002 |}
           37  +do_test limit2-100.3 {
           38  +  set slow_count $sqlite_search_count
           39  +  expr {$fast_count < 0.02*$slow_count}
           40  +} {1}
           41  +
           42  +do_execsql_test limit2-110 {
           43  +  CREATE TABLE t2(x,y);
           44  +  INSERT INTO t2(x,y) VALUES('a',1),('a',2),('a',3),('a',4);
           45  +  INSERT INTO t2(x,y) VALUES('b',1),('c',2),('d',3),('e',4);
           46  +  CREATE INDEX t2xy ON t2(x,y);
           47  +}
           48  +set sqlite_search_count 0
           49  +do_execsql_test limit2-110.1 {
           50  +  SELECT a, b, '|' FROM t2, t1 WHERE t2.x='a' AND t1.a=t2.y ORDER BY t1.b LIMIT 5;
           51  +} {2 2 | 4 4 | 1 1000 | 1 1001 | 1 1002 |}
           52  +set fast_count $sqlite_search_count
           53  +set sqlite_search_count 0
           54  +do_execsql_test limit2-110.2 {
           55  +  SELECT a, b, '|' FROM t2, t1 WHERE t2.x='a' AND t1.a=t2.y ORDER BY +t1.b LIMIT 5;
           56  +} {2 2 | 4 4 | 1 1000 | 1 1001 | 1 1002 |}
           57  +set slow_count $sqlite_search_count
           58  +do_test limit2-110.3 {
           59  +  expr {$fast_count < 0.02*$slow_count}
           60  +} {1}
           61  +
           62  +do_execsql_test limit2-120 {
           63  +  DROP INDEX t1ab;
           64  +  CREATE INDEX t1ab ON t1(a,b DESC);
           65  +}
           66  +set sqlite_search_count 0
           67  +do_execsql_test limit2-120.1 {
           68  +  SELECT a, b, '|' FROM t1 WHERE a IN (2,4,5,3,1) ORDER BY b DESC LIMIT 5;
           69  +} {5 9999 | 1 1999 | 1 1998 | 1 1997 | 1 1996 |}
           70  +set fast_count $sqlite_search_count
           71  +set sqlite_search_count 0
           72  +do_execsql_test limit2-120.2 {
           73  +  SELECT a, b, '|' FROM t1 WHERE a IN (2,4,5,3,1) ORDER BY +b DESC LIMIT 5;
           74  +} {5 9999 | 1 1999 | 1 1998 | 1 1997 | 1 1996 |}
           75  +do_test limit2-120.3 {
           76  +  set slow_count $sqlite_search_count
           77  +  expr {$fast_count < 0.02*$slow_count}
           78  +} {1}
           79  +
           80  +# Bug report against the new ORDER BY LIMIT optimization just prior to
           81  +# release.  (Unreleased so there is no ticket).
           82  +#
           83  +# Make sure the optimization is not applied if the inner loop can only
           84  +# provide a single row of output.
           85  +#
           86  +do_execsql_test limit2-200 {
           87  +  CREATE TABLE t200(a, b);
           88  +  WITH RECURSIVE c(x) AS (VALUES(1) UNION ALL SELECT x+1 FROM c WHERE x<1000)
           89  +    INSERT INTO t200(a,b) SELECT x, x FROM c;
           90  +  CREATE TABLE t201(x INTEGER PRIMARY KEY, y);
           91  +  INSERT INTO t201(x,y) VALUES(2,12345);
           92  +
           93  +  SELECT *, '|' FROM t200, t201 WHERE x=b ORDER BY y LIMIT 3;
           94  +} {2 2 2 12345 |}
           95  +do_execsql_test limit2-210 {
           96  +  SELECT *, '|' FROM t200 LEFT JOIN t201 ON x=b ORDER BY y LIMIT 3;
           97  +} {1 1 {} {} | 3 3 {} {} | 4 4 {} {} |}
           98  +
           99  +# Bug in the ORDER BY LIMIT optimization reported on 2016-09-06.
          100  +# Ticket https://www.sqlite.org/src/info/559733b09e96
          101  +#
          102  +do_execsql_test limit2-300 {
          103  +  CREATE TABLE t300(a,b,c);
          104  +  CREATE INDEX t300x ON t300(a,b,c);
          105  +  INSERT INTO t300 VALUES(0,1,99),(0,1,0),(0,0,0);
          106  +  SELECT *,'.' FROM t300 WHERE a=0 AND (c=0 OR c=99) ORDER BY c DESC;
          107  +} {0 1 99 . 0 0 0 . 0 1 0 .}
          108  +do_execsql_test limit2-310 {
          109  +  SELECT *,'.' FROM t300 WHERE a=0 AND (c=0 OR c=99) ORDER BY c DESC LIMIT 1;
          110  +} {0 1 99 .}
          111  +
          112  +# Make sure the SELECT loop is ordered correctly for the direction of
          113  +# the ORDER BY
          114  +#
          115  +do_execsql_test limit2-400 {
          116  +  CREATE TABLE t400(a,b);
          117  +  CREATE INDEX t400_ab ON t400(a,b);
          118  +  INSERT INTO t400(a,b) VALUES(1,90),(1,40),(2,80),(2,30),(3,70),(3,20);
          119  +  SELECT *,'x' FROM t400 WHERE a IN (1,2,3) ORDER BY b DESC LIMIT 3;
          120  +  SELECT *,'y' FROM t400 WHERE a IN (1,2,3) ORDER BY +b DESC LIMIT 3;
          121  +} {1 90 x 2 80 x 3 70 x 1 90 y 2 80 y 3 70 y}
          122  +
          123  +finish_test

Changes to test/orderby1.test.

   491    491     CREATE TABLE t7(a,b);
   492    492     CREATE INDEX t7a ON t7(a);
   493    493     CREATE INDEX t7ab ON t7(a,b);
   494    494     EXPLAIN QUERY PLAN
   495    495     SELECT * FROM t7 WHERE a=?1 ORDER BY rowid;
   496    496   } {~/ORDER BY/}
   497    497   
          498  +
          499  +#---------------------------------------------------------------------------
          500  +# https://www.sqlite.org/src/tktview/cb3aa0641d9a413841c004293a4fc06cdc122029
          501  +#
          502  +# Adverse interaction between scalar subqueries and the partial-sorting
          503  +# logic.
          504  +#
          505  +do_execsql_test 9.0 {
          506  +  DROP TABLE IF EXISTS t1;
          507  +  CREATE TABLE t1(x INTEGER PRIMARY KEY);
          508  +  INSERT INTO t1 VALUES(1),(2);
          509  +  DROP TABLE IF EXISTS t2;
          510  +  CREATE TABLE t2(y);
          511  +  INSERT INTO t2 VALUES(9),(8),(3),(4);
          512  +  SELECT (SELECT x||y FROM t2, t1 ORDER BY x, y);
          513  +} {13}
          514  +
   498    515   
   499    516   finish_test

Changes to test/select4.test.

   859    859   } {}
   860    860   do_execsql_test select4-14.8 {
   861    861     SELECT * FROM t14 EXCEPT VALUES('a','b','c') EXCEPT VALUES(4,5,6)
   862    862   } {1 2 3}
   863    863   do_execsql_test select4-14.9 {
   864    864     SELECT * FROM t14 UNION ALL VALUES(3,2,1),(2,3,1),(1,2,3),(2,1,3);
   865    865   } {1 2 3 4 5 6 3 2 1 2 3 1 1 2 3 2 1 3}
          866  +
          867  +# Ticket https://www.sqlite.org/src/tktview/f7f8c97e975978d45  on 2016-04-25
          868  +#
          869  +# The where push-down optimization from 2015-06-02 is suppose to disable
          870  +# on aggregate subqueries.  But if the subquery is a compound where the
          871  +# last SELECT is non-aggregate but some other SELECT is an aggregate, the
          872  +# test is incomplete and the optimization is not properly disabled.
          873  +# 
          874  +# The following test cases verify that the fix works.
          875  +#
          876  +do_execsql_test select4-17.1 {
          877  +  DROP TABLE IF EXISTS t1;
          878  +  CREATE TABLE t1(a int, b int);
          879  +  INSERT INTO t1 VALUES(1,2),(1,18),(2,19);
          880  +  SELECT x, y FROM (
          881  +    SELECT 98 AS x, 99 AS y
          882  +    UNION
          883  +    SELECT a AS x, sum(b) AS y FROM t1 GROUP BY a
          884  +  ) AS w WHERE y>=20
          885  +  ORDER BY +x;
          886  +} {1 20 98 99}
          887  +do_execsql_test select4-17.2 {
          888  +  SELECT x, y FROM (
          889  +    SELECT a AS x, sum(b) AS y FROM t1 GROUP BY a
          890  +    UNION
          891  +    SELECT 98 AS x, 99 AS y
          892  +  ) AS w WHERE y>=20
          893  +  ORDER BY +x;
          894  +} {1 20 98 99}
          895  +do_catchsql_test select4-17.3 {
          896  +  SELECT x, y FROM (
          897  +    SELECT a AS x, sum(b) AS y FROM t1 GROUP BY a LIMIT 3
          898  +    UNION
          899  +    SELECT 98 AS x, 99 AS y
          900  +  ) AS w WHERE y>=20
          901  +  ORDER BY +x;
          902  +} {1 {LIMIT clause should come after UNION not before}}
   866    903   
   867    904   finish_test

Changes to test/whereD.test.

   152    152     SELECT a, b FROM t3 WHERE 
   153    153           (a=2 AND b=(SELECT y FROM t4 WHERE x='b')) 
   154    154        OR (a=1 AND b=(SELECT y FROM t4 WHERE x='a')) 
   155    155   } {2 two 1 one search 6}
   156    156   
   157    157   do_searchcount_test 3.5.1 {
   158    158     SELECT a, b FROM t3 WHERE (a=1 AND b='one') OR rowid=4
   159         -} {1 one 2 two search 2}
          159  +} {1 one 2 two search 1}
   160    160   do_searchcount_test 3.5.2 {
   161    161     SELECT a, c FROM t3 WHERE (a=1 AND b='one') OR rowid=4
   162    162   } {1 i 2 ii search 2}
   163    163   
   164    164   # Ticket [d02e1406a58ea02d] (2012-10-04)
   165    165   # LEFT JOIN with an OR in the ON clause causes segfault 
   166    166   #
................................................................................
   267    267       c0=1 or  c1=1 or  c2=1 or  c3=1 or
   268    268       c4=1 or  c5=1 or  c6=1 or  c7=1 or
   269    269       c8=1 or  c9=1 or c10=1 or c11=1 or
   270    270       c12=1 or c13=1 or c14=1 or c15=1 or
   271    271       c16=1 or c17=1;
   272    272   } {1 {} {} {} {} {} {} {} {} {} {} {} {} {} {} 1 {} {}}
   273    273   
          274  +#-------------------------------------------------------------------------
          275  +#-------------------------------------------------------------------------
          276  +do_execsql_test 6.1 {
          277  +  CREATE TABLE x1(a, b, c, d, e);
          278  +  CREATE INDEX x1a  ON x1(a);
          279  +  CREATE INDEX x1bc ON x1(b, c);
          280  +  CREATE INDEX x1cd ON x1(c, d);
          281  +
          282  +  INSERT INTO x1 VALUES(1, 2, 3, 4, 'A');
          283  +  INSERT INTO x1 VALUES(5, 6, 7, 8, 'B');
          284  +  INSERT INTO x1 VALUES(9, 10, 11, 12, 'C');
          285  +  INSERT INTO x1 VALUES(13, 14, 15, 16, 'D');
          286  +}
          287  +
          288  +do_searchcount_test 6.2.1 {
          289  +  SELECT e FROM x1 WHERE b=2 OR c=7;
          290  +} {A B search 6}
          291  +do_searchcount_test 6.2.2 {
          292  +  SELECT c FROM x1 WHERE b=2 OR c=7;
          293  +} {3 7 search 4}
          294  +
          295  +do_searchcount_test 6.3.1 {
          296  +  SELECT e FROM x1 WHERE a=1 OR b=10;
          297  +} {A C search 6}
          298  +do_searchcount_test 6.3.2 {
          299  +  SELECT c FROM x1 WHERE a=1 OR b=10;
          300  +} {3 11 search 5}
          301  +do_searchcount_test 6.3.3 {
          302  +  SELECT rowid FROM x1 WHERE a=1 OR b=10;
          303  +} {1 3 search 4}
          304  +
          305  +do_searchcount_test 6.4.1 {
          306  +  SELECT a FROM x1 WHERE b BETWEEN 1 AND 4 OR c BETWEEN 8 AND 12
          307  +} {1 9 search 6}
          308  +do_searchcount_test 6.4.2 {
          309  +  SELECT b, c FROM x1 WHERE b BETWEEN 1 AND 4 OR c BETWEEN 8 AND 12
          310  +} {2 3 10 11 search 5}
          311  +do_searchcount_test 6.4.3 {
          312  +  SELECT rowid, c FROM x1 WHERE b BETWEEN 1 AND 4 OR c BETWEEN 8 AND 12
          313  +} {1 3 3 11 search 4}
          314  +
          315  +db eval {
          316  +  WITH RECURSIVE c(x) AS (VALUES(1000) UNION ALL SELECT x+1 FROM c WHERE x<2000)
          317  +  INSERT INTO x1(rowid,a,b,c,d,e) SELECT x,x,x,x,x,x FROM c;
          318  +}
          319  +
          320  +do_searchcount_test 6.5.1 {
          321  +  SELECT a FROM x1 WHERE rowid = 2 OR c=11
          322  +} {5 9 search 3}
          323  +do_searchcount_test 6.5.2 {
          324  +  SELECT d FROM x1 WHERE rowid = 2 OR c=11
          325  +} {8 12 search 2}
          326  +do_searchcount_test 6.5.3 {
          327  +  SELECT d FROM x1 WHERE c=11 OR rowid = 2
          328  +} {12 8 search 2}
          329  +do_searchcount_test 6.5.4 {
          330  +  SELECT a FROM x1 WHERE c=11 OR rowid = 2 
          331  +} {9 5 search 3}
          332  +
          333  +do_searchcount_test 6.6.1 {
          334  +  SELECT rowid FROM x1 WHERE a=1 OR b=6 OR c=11
          335  +} {1 2 3 search 6}
          336  +do_searchcount_test 6.6.2 {
          337  +  SELECT c FROM x1 WHERE a=1 OR b=6 OR c=11
          338  +} {3 7 11 search 7}
          339  +do_searchcount_test 6.6.3 {
          340  +  SELECT c FROM x1 WHERE c=11 OR a=1 OR b=6 
          341  +} {11 3 7 search 7}
          342  +do_searchcount_test 6.6.4 {
          343  +  SELECT c FROM x1 WHERE b=6 OR c=11 OR a=1
          344  +} {7 11 3 search 7}
   274    345   
   275    346   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)) 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 AS
          299  +       SELECT rowid AS r, 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)) 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.2.0 {
           78  +  CREATE TABLE t2(a INTEGER PRIMARY KEY, b, c) WITHOUT ROWID;
           79  +  INSERT INTO t2 VALUES(1, 1, 'h');
           80  +  INSERT INTO t2 VALUES(2, 2, 'g');
           81  +  INSERT INTO t2 VALUES(3, 1, 'f');
           82  +  INSERT INTO t2 VALUES(4, 2, 'e');
           83  +  INSERT INTO t2 VALUES(5, 1, 'd');
           84  +  INSERT INTO t2 VALUES(6, 2, 'c');
           85  +  INSERT INTO t2 VALUES(7, 1, 'b');
           86  +  INSERT INTO t2 VALUES(8, 2, 'a');
           87  +}
           88  +
           89  +do_execsql_test 2.2.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.2.2 {
           97  +  BEGIN;
           98  +    UPDATE t2 SET c=NULL ORDER BY a DESC LIMIT 3 OFFSET 1;
           99  +    SELECT a, b, c FROM t2;
          100  +  ROLLBACK;
          101  +} {
          102  +  1 1 h
          103  +  2 2 g 
          104  +  3 1 f
          105  +  4 2 e
          106  +  5 1 {}
          107  +  6 2 {} 
          108  +  7 1 {} 
          109  +  8 2 a
          110  +}
          111  +
          112  +#-------------------------------------------------------------------------
          113  +# Test using a virtual table
          114  +#
          115  +ifcapable fts3 {
          116  +  do_execsql_test 3.0 {
          117  +    CREATE VIRTUAL TABLE ft USING fts3(x);
          118  +    INSERT INTO ft(rowid, x) VALUES(-45,   'a a');
          119  +    INSERT INTO ft(rowid, x) VALUES(12,    'a b');
          120  +    INSERT INTO ft(rowid, x) VALUES(444,   'a c');
          121  +    INSERT INTO ft(rowid, x) VALUES(12300, 'a d');
          122  +    INSERT INTO ft(rowid, x) VALUES(25400, 'a c');
          123  +    INSERT INTO ft(rowid, x) VALUES(25401, 'a b');
          124  +    INSERT INTO ft(rowid, x) VALUES(50000, 'a a');
          125  +  }
          126  +
          127  +  do_execsql_test 3.1.1 {
          128  +    BEGIN;
          129  +      DELETE FROM ft ORDER BY rowid LIMIT 3;
          130  +      SELECT x FROM ft;
          131  +    ROLLBACK;
          132  +  } {{a d} {a c} {a b} {a a}}
          133  +
          134  +  do_execsql_test 3.1.2 {
          135  +    BEGIN;
          136  +      DELETE FROM ft WHERE ft MATCH 'a' ORDER BY rowid LIMIT 3;
          137  +      SELECT x FROM ft;
          138  +    ROLLBACK;
          139  +  } {{a d} {a c} {a b} {a a}}
          140  +  
          141  +  do_execsql_test 3.1.3 {
          142  +    BEGIN;
          143  +      DELETE FROM ft WHERE ft MATCH 'b' ORDER BY rowid ASC LIMIT 1 OFFSET 1;
          144  +      SELECT rowid FROM ft;
          145  +    ROLLBACK;
          146  +  } {-45 12 444 12300 25400 50000}
          147  +
          148  +  do_execsql_test 3.2.1 {
          149  +    BEGIN;
          150  +      UPDATE ft SET x='hello' ORDER BY rowid LIMIT 2 OFFSET 2;
          151  +      SELECT x FROM ft;
          152  +    ROLLBACK;
          153  +  } {{a a} {a b} hello hello {a c} {a b} {a a}}
          154  +
          155  +  do_execsql_test 3.2.2 {
          156  +    BEGIN;
          157  +      UPDATE ft SET x='hello' WHERE ft MATCH 'a' 
          158  +          ORDER BY rowid DESC LIMIT 2 OFFSET 2;
          159  +      SELECT x FROM ft;
          160  +    ROLLBACK;
          161  +  } {{a a} {a b} {a c} hello hello {a b} {a a}}
          162  +} ;# fts5
          163  +
          164  +#-------------------------------------------------------------------------
          165  +# Test using INDEXED BY clauses.
          166  +#
          167  +do_execsql_test 4.0 {
          168  +  CREATE TABLE x1(a INTEGER PRIMARY KEY, b, c, d);
          169  +  CREATE INDEX x1bc ON x1(b, c);
          170  +  INSERT INTO x1 VALUES(1,1,1,1);
          171  +  INSERT INTO x1 VALUES(2,1,2,2);
          172  +  INSERT INTO x1 VALUES(3,2,1,3);
          173  +  INSERT INTO x1 VALUES(4,2,2,3);
          174  +  INSERT INTO x1 VALUES(5,3,1,2);
          175  +  INSERT INTO x1 VALUES(6,3,2,1);
          176  +}
          177  +
          178  +do_execsql_test 4.1 {
          179  +  BEGIN;
          180  +    DELETE FROM x1 ORDER BY a LIMIT 2;
          181  +    SELECT a FROM x1;
          182  +  ROLLBACK;
          183  +} {3 4 5 6}
          184  +
          185  +do_catchsql_test 4.2 {
          186  +  DELETE FROM x1 INDEXED BY x1bc WHERE d=3 LIMIT 1;
          187  +} {1 {no query solution}}
          188  +
          189  +do_execsql_test 4.3 {
          190  +  DELETE FROM x1 INDEXED BY x1bc WHERE b=3 LIMIT 1;
          191  +  SELECT a FROM x1;
          192  +} {1 2 3 4 6}
          193  +
          194  +do_catchsql_test 4.4 {
          195  +  UPDATE x1 INDEXED BY x1bc SET d=5 WHERE d=3 LIMIT 1;
          196  +} {1 {no query solution}}
          197  +
          198  +do_execsql_test 4.5 {
          199  +  UPDATE x1 INDEXED BY x1bc SET d=5 WHERE b=2 LIMIT 1;
          200  +  SELECT a, d FROM x1;
          201  +} {1 1 2 2 3 5 4 3 6 1}
          202  +
          203  +#-------------------------------------------------------------------------
          204  +# Test using object names that require quoting.
          205  +#
          206  +do_execsql_test 5.0 {
          207  +  CREATE TABLE "x y"("a b" PRIMARY KEY, "c d") WITHOUT ROWID;
          208  +  CREATE INDEX xycd ON "x y"("c d");
          209  +
          210  +  INSERT INTO "x y" VALUES('a', 'a');
          211  +  INSERT INTO "x y" VALUES('b', 'b');
          212  +  INSERT INTO "x y" VALUES('c', 'c');
          213  +  INSERT INTO "x y" VALUES('d', 'd');
          214  +  INSERT INTO "x y" VALUES('e', 'a');
          215  +  INSERT INTO "x y" VALUES('f', 'b');
          216  +  INSERT INTO "x y" VALUES('g', 'c');
          217  +  INSERT INTO "x y" VALUES('h', 'd');
          218  +}
          219  +
          220  +do_execsql_test 5.1 {
          221  +  BEGIN;
          222  +    DELETE FROM "x y" WHERE "c d"!='e' ORDER BY "c d" LIMIT 2 OFFSET 2;
          223  +    SELECT * FROM "x y" ORDER BY 1;
          224  +  ROLLBACK;
          225  +} {
          226  +  a a c c d d e a g c h d
          227  +}
          228  +
          229  +do_execsql_test 5.2 {
          230  +  BEGIN;
          231  +    UPDATE "x y" SET "c d"='e' WHERE "c d"!='e' ORDER BY "c d" LIMIT 2 OFFSET 2;
          232  +    SELECT * FROM "x y" ORDER BY 1;
          233  +  ROLLBACK;
          234  +} {
          235  +  a a b e c c d d e a f e g c h d
          236  +}
          237  +
          238  +proc log {args} { lappend ::log {*}$args }
          239  +db func log log
          240  +do_execsql_test 5.3 {
          241  +  CREATE VIEW "v w" AS SELECT * FROM "x y";
          242  +  CREATE TRIGGER tr1 INSTEAD OF DELETE ON "v w" BEGIN
          243  +    SELECT log(old."a b", old."c d");
          244  +  END;
          245  +  CREATE TRIGGER tr2 INSTEAD OF UPDATE ON "v w" BEGIN
          246  +    SELECT log(new."a b", new."c d");
          247  +  END;
          248  +}
          249  +
          250  +do_test 5.4 {
          251  +  set ::log {}
          252  +  execsql { DELETE FROM "v w" ORDER BY "a b" LIMIT 3 }
          253  +  set ::log
          254  +} {a a b b c c}
          255  +
          256  +do_test 5.5 {
          257  +  set ::log {}
          258  +  execsql { UPDATE "v w" SET "a b" = "a b" || 'x' ORDER BY "a b" LIMIT 5; }
          259  +  set ::log
          260  +} {ax a bx b cx c dx d ex a}
          261  +
          262  +
          263  +finish_test
          264  +