/ Check-in [b55c0f14]
Login

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

Overview
Comment:Merge recent enhancements from trunk.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | apple-osx
Files: files | file ages | folders
SHA3-256: b55c0f14c3250cdd0b38193d9f4c4ad3da977d280d7509d0c8db8552176b2e10
User & Date: drh 2017-05-11 18:49:57
Context
2017-05-22
19:24
Pull in all changes from the 3.19.0 release. check-in: bbd2d0e1 user: drh tags: apple-osx
2017-05-11
18:49
Merge recent enhancements from trunk. check-in: b55c0f14 user: drh tags: apple-osx
18:42
Enhance the json_extract() function to reuse parses of the same JSON when the function appears multiple times in the same query. check-in: 3ba9e7ab user: drh tags: trunk
2017-04-24
16:14
Bring in all the latest enhancements from trunk. check-in: 031feebc user: drh tags: apple-osx
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to ext/fts3/fts3.c.

  4783   4783       ** Entry 0 of the %_stat table is a blob containing (nCol+1) FTS3 
  4784   4784       ** varints, where nCol is the number of columns in the FTS3 table.
  4785   4785       ** The first varint is the number of documents currently stored in
  4786   4786       ** the table. The following nCol varints contain the total amount of
  4787   4787       ** data stored in all rows of each column of the table, from left
  4788   4788       ** to right.
  4789   4789       */
  4790         -    int rc;
  4791   4790       Fts3Table *p = (Fts3Table*)pCsr->base.pVtab;
  4792   4791       sqlite3_stmt *pStmt;
  4793   4792       sqlite3_int64 nDoc = 0;
  4794   4793       sqlite3_int64 nByte = 0;
  4795   4794       const char *pEnd;
  4796   4795       const char *a;
  4797   4796   

Changes to ext/fts5/fts5_index.c.

  4188   4188     int nInput;                     /* Number of input segments */
  4189   4189     Fts5SegWriter writer;           /* Writer object */
  4190   4190     Fts5StructureSegment *pSeg;     /* Output segment */
  4191   4191     Fts5Buffer term;
  4192   4192     int bOldest;                    /* True if the output segment is the oldest */
  4193   4193     int eDetail = p->pConfig->eDetail;
  4194   4194     const int flags = FTS5INDEX_QUERY_NOOUTPUT;
         4195  +  int bTermWritten = 0;           /* True if current term already output */
  4195   4196   
  4196   4197     assert( iLvl<pStruct->nLevel );
  4197   4198     assert( pLvl->nMerge<=pLvl->nSeg );
  4198   4199   
  4199   4200     memset(&writer, 0, sizeof(Fts5SegWriter));
  4200   4201     memset(&term, 0, sizeof(Fts5Buffer));
  4201   4202     if( pLvl->nMerge ){
................................................................................
  4241   4242         fts5MultiIterNext(p, pIter, 0, 0)
  4242   4243     ){
  4243   4244       Fts5SegIter *pSegIter = &pIter->aSeg[ pIter->aFirst[1].iFirst ];
  4244   4245       int nPos;                     /* position-list size field value */
  4245   4246       int nTerm;
  4246   4247       const u8 *pTerm;
  4247   4248   
  4248         -    /* Check for key annihilation. */
  4249         -    if( pSegIter->nPos==0 && (bOldest || pSegIter->bDel==0) ) continue;
  4250         -
  4251   4249       pTerm = fts5MultiIterTerm(pIter, &nTerm);
  4252   4250       if( nTerm!=term.n || memcmp(pTerm, term.p, nTerm) ){
  4253   4251         if( pnRem && writer.nLeafWritten>nRem ){
  4254   4252           break;
  4255   4253         }
         4254  +      fts5BufferSet(&p->rc, &term, nTerm, pTerm);
         4255  +      bTermWritten =0;
         4256  +    }
  4256   4257   
         4258  +    /* Check for key annihilation. */
         4259  +    if( pSegIter->nPos==0 && (bOldest || pSegIter->bDel==0) ) continue;
         4260  +
         4261  +    if( p->rc==SQLITE_OK && bTermWritten==0 ){
  4257   4262         /* This is a new term. Append a term to the output segment. */
  4258   4263         fts5WriteAppendTerm(p, &writer, nTerm, pTerm);
  4259         -      fts5BufferSet(&p->rc, &term, nTerm, pTerm);
         4264  +      bTermWritten = 1;
  4260   4265       }
  4261   4266   
  4262   4267       /* Append the rowid to the output */
  4263   4268       /* WRITEPOSLISTSIZE */
  4264   4269       fts5WriteAppendRowid(p, &writer, fts5MultiIterRowid(pIter));
  4265   4270   
  4266   4271       if( eDetail==FTS5_DETAIL_NONE ){

Changes to ext/fts5/fts5_test_tok.c.

    36     36   **   end:     Byte offset of the byte immediately following the end of the
    37     37   **            token within the input string.
    38     38   **   pos:     Token offset of token within input.
    39     39   **
    40     40   */
    41     41   #if defined(SQLITE_TEST) && defined(SQLITE_ENABLE_FTS5)
    42     42   
    43         -#include <fts5.h>
           43  +#include "fts5.h"
    44     44   #include <string.h>
    45     45   #include <assert.h>
    46     46   
    47     47   typedef struct Fts5tokTable Fts5tokTable;
    48     48   typedef struct Fts5tokCursor Fts5tokCursor;
    49     49   typedef struct Fts5tokRow Fts5tokRow;
    50     50   

Added ext/fts5/test/fts5delete.test.

            1  +# 2017 May 12
            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 script is testing the FTS5 module.
           13  +#
           14  +
           15  +source [file join [file dirname [info script]] fts5_common.tcl]
           16  +set testprefix fts5delete
           17  +
           18  +# If SQLITE_ENABLE_FTS5 is not defined, omit this file.
           19  +ifcapable !fts5 {
           20  +  finish_test
           21  +  return
           22  +}
           23  +fts5_aux_test_functions db
           24  +
           25  +do_execsql_test 1.0 {
           26  +  CREATE VIRTUAL TABLE t1 USING fts5(x);
           27  +  WITH s(i) AS (
           28  +    SELECT 1 UNION ALL SELECT i+1 FROM s WHERE i<5000
           29  +  )
           30  +  INSERT INTO t1(rowid, x) SELECT i, (i/2)*2 FROM s;
           31  +}
           32  +
           33  +do_test 1.1 {
           34  +  execsql BEGIN
           35  +  for {set i 1} {$i<=5000} {incr i} {
           36  +    if {$i % 2} {
           37  +      execsql { INSERT INTO t1 VALUES($i) }
           38  +    } else {
           39  +      execsql { DELETE FROM t1 WHERE rowid = $i }
           40  +    }
           41  +  }
           42  +  execsql COMMIT
           43  +} {}
           44  +
           45  +do_test 1.2 {
           46  +  execsql { INSERT INTO t1(t1, rank) VALUES('usermerge', 2); }
           47  +  for {set i 0} {$i < 5} {incr i} {
           48  +    execsql { INSERT INTO t1(t1, rank) VALUES('merge', 1) }
           49  +    execsql { INSERT INTO t1(t1) VALUES('integrity-check') }
           50  +  }
           51  +} {}
           52  +
           53  +finish_test
           54  +

Changes to ext/misc/json1.c.

   167    167     u32 nAlloc;        /* Number of slots of aNode[] allocated */
   168    168     JsonNode *aNode;   /* Array of nodes containing the parse */
   169    169     const char *zJson; /* Original JSON string */
   170    170     u32 *aUp;          /* Index of parent of each node */
   171    171     u8 oom;            /* Set to true if out of memory */
   172    172     u8 nErr;           /* Number of errors seen */
   173    173     u16 iDepth;        /* Nesting depth */
          174  +  int nJson;         /* Length of the zJson string in bytes */
   174    175   };
   175    176   
   176    177   /*
   177    178   ** Maximum nesting depth of JSON for this implementation.
   178    179   **
   179    180   ** This limit is needed to avoid a stack overflow in the recursive
   180    181   ** descent parser.  A depth of 2000 is far deeper than any sane JSON
................................................................................
   408    409     sqlite3_free(pParse->aNode);
   409    410     pParse->aNode = 0;
   410    411     pParse->nNode = 0;
   411    412     pParse->nAlloc = 0;
   412    413     sqlite3_free(pParse->aUp);
   413    414     pParse->aUp = 0;
   414    415   }
          416  +
          417  +/*
          418  +** Free a JsonParse object that was obtained from sqlite3_malloc().
          419  +*/
          420  +static void jsonParseFree(JsonParse *pParse){
          421  +  jsonParseReset(pParse);
          422  +  sqlite3_free(pParse);
          423  +}
   415    424   
   416    425   /*
   417    426   ** Convert the JsonNode pNode into a pure JSON string and
   418    427   ** append to pOut.  Subsubstructure is also included.  Return
   419    428   ** the number of JsonNode objects that are encoded.
   420    429   */
   421    430   static void jsonRenderNode(
................................................................................
   959    968     if( aUp==0 ){
   960    969       pParse->oom = 1;
   961    970       return SQLITE_NOMEM;
   962    971     }
   963    972     jsonParseFillInParentage(pParse, 0, 0);
   964    973     return SQLITE_OK;
   965    974   }
          975  +
          976  +/*
          977  +** Magic number used for the JSON parse cache in sqlite3_get_auxdata()
          978  +*/
          979  +#define JSON_CACHE_ID  (-429938)
          980  +
          981  +/*
          982  +** Obtain a complete parse of the JSON found in the first argument
          983  +** of the argv array.  Use the sqlite3_get_auxdata() cache for this
          984  +** parse if it is available.  If the cache is not available or if it
          985  +** is no longer valid, parse the JSON again and return the new parse,
          986  +** and also register the new parse so that it will be available for
          987  +** future sqlite3_get_auxdata() calls.
          988  +*/
          989  +static JsonParse *jsonParseCached(
          990  +  sqlite3_context *pCtx,
          991  +  sqlite3_value **argv
          992  +){
          993  +  const char *zJson = (const char*)sqlite3_value_text(argv[0]);
          994  +  int nJson = sqlite3_value_bytes(argv[0]);
          995  +  JsonParse *p;
          996  +  if( zJson==0 ) return 0;
          997  +  p = (JsonParse*)sqlite3_get_auxdata(pCtx, JSON_CACHE_ID);
          998  +  if( p && p->nJson==nJson && memcmp(p->zJson,zJson,nJson)==0 ){
          999  +    p->nErr = 0;
         1000  +    return p; /* The cached entry matches, so return it */
         1001  +  }
         1002  +  p = sqlite3_malloc( sizeof(*p) + nJson + 1 );
         1003  +  if( p==0 ){
         1004  +    sqlite3_result_error_nomem(pCtx);
         1005  +    return 0;
         1006  +  }
         1007  +  memset(p, 0, sizeof(*p));
         1008  +  p->zJson = (char*)&p[1];
         1009  +  memcpy((char*)p->zJson, zJson, nJson+1);
         1010  +  if( jsonParse(p, pCtx, p->zJson) ){
         1011  +    sqlite3_free(p);
         1012  +    return 0;
         1013  +  }
         1014  +  p->nJson = nJson;
         1015  +  sqlite3_set_auxdata(pCtx, JSON_CACHE_ID, p, (void(*)(void*))jsonParseFree);
         1016  +  return (JsonParse*)sqlite3_get_auxdata(pCtx, JSON_CACHE_ID);
         1017  +}
   966   1018   
   967   1019   /*
   968   1020   ** Compare the OBJECT label at pNode against zKey,nKey.  Return true on
   969   1021   ** a match.
   970   1022   */
   971   1023   static int jsonLabelCompare(JsonNode *pNode, const char *zKey, u32 nKey){
   972   1024     if( pNode->jnFlags & JNODE_RAW ){
................................................................................
  1325   1377   ** Return 0 if the input is not a well-formed JSON array.
  1326   1378   */
  1327   1379   static void jsonArrayLengthFunc(
  1328   1380     sqlite3_context *ctx,
  1329   1381     int argc,
  1330   1382     sqlite3_value **argv
  1331   1383   ){
  1332         -  JsonParse x;          /* The parse */
         1384  +  JsonParse *p;          /* The parse */
  1333   1385     sqlite3_int64 n = 0;
  1334   1386     u32 i;
  1335   1387     JsonNode *pNode;
  1336   1388   
  1337         -  if( jsonParse(&x, ctx, (const char*)sqlite3_value_text(argv[0])) ) return;
  1338         -  assert( x.nNode );
         1389  +  p = jsonParseCached(ctx, argv);
         1390  +  if( p==0 ) return;
         1391  +  assert( p->nNode );
  1339   1392     if( argc==2 ){
  1340   1393       const char *zPath = (const char*)sqlite3_value_text(argv[1]);
  1341         -    pNode = jsonLookup(&x, zPath, 0, ctx);
         1394  +    pNode = jsonLookup(p, zPath, 0, ctx);
  1342   1395     }else{
  1343         -    pNode = x.aNode;
         1396  +    pNode = p->aNode;
  1344   1397     }
  1345   1398     if( pNode==0 ){
  1346         -    x.nErr = 1;
  1347         -  }else if( pNode->eType==JSON_ARRAY ){
         1399  +    return;
         1400  +  }
         1401  +  if( pNode->eType==JSON_ARRAY ){
  1348   1402       assert( (pNode->jnFlags & JNODE_APPEND)==0 );
  1349   1403       for(i=1; i<=pNode->n; n++){
  1350   1404         i += jsonNodeSize(&pNode[i]);
  1351   1405       }
  1352   1406     }
  1353         -  if( x.nErr==0 ) sqlite3_result_int64(ctx, n);
  1354         -  jsonParseReset(&x);
         1407  +  sqlite3_result_int64(ctx, n);
  1355   1408   }
  1356   1409   
  1357   1410   /*
  1358   1411   ** json_extract(JSON, PATH, ...)
  1359   1412   **
  1360   1413   ** Return the element described by PATH.  Return NULL if there is no
  1361   1414   ** PATH element.  If there are multiple PATHs, then return a JSON array
................................................................................
  1363   1416   ** is malformed.
  1364   1417   */
  1365   1418   static void jsonExtractFunc(
  1366   1419     sqlite3_context *ctx,
  1367   1420     int argc,
  1368   1421     sqlite3_value **argv
  1369   1422   ){
  1370         -  JsonParse x;          /* The parse */
         1423  +  JsonParse *p;          /* The parse */
  1371   1424     JsonNode *pNode;
  1372   1425     const char *zPath;
  1373   1426     JsonString jx;
  1374   1427     int i;
  1375   1428   
  1376   1429     if( argc<2 ) return;
  1377         -  if( jsonParse(&x, ctx, (const char*)sqlite3_value_text(argv[0])) ) return;
         1430  +  p = jsonParseCached(ctx, argv);
         1431  +  if( p==0 ) return;
  1378   1432     jsonInit(&jx, ctx);
  1379   1433     jsonAppendChar(&jx, '[');
  1380   1434     for(i=1; i<argc; i++){
  1381   1435       zPath = (const char*)sqlite3_value_text(argv[i]);
  1382         -    pNode = jsonLookup(&x, zPath, 0, ctx);
  1383         -    if( x.nErr ) break;
         1436  +    pNode = jsonLookup(p, zPath, 0, ctx);
         1437  +    if( p->nErr ) break;
  1384   1438       if( argc>2 ){
  1385   1439         jsonAppendSeparator(&jx);
  1386   1440         if( pNode ){
  1387   1441           jsonRenderNode(pNode, &jx, 0);
  1388   1442         }else{
  1389   1443           jsonAppendRaw(&jx, "null", 4);
  1390   1444         }
................................................................................
  1394   1448     }
  1395   1449     if( argc>2 && i==argc ){
  1396   1450       jsonAppendChar(&jx, ']');
  1397   1451       jsonResult(&jx);
  1398   1452       sqlite3_result_subtype(ctx, JSON_SUBTYPE);
  1399   1453     }
  1400   1454     jsonReset(&jx);
  1401         -  jsonParseReset(&x);
  1402   1455   }
  1403   1456   
  1404   1457   /* This is the RFC 7396 MergePatch algorithm.
  1405   1458   */
  1406   1459   static JsonNode *jsonMergePatch(
  1407   1460     JsonParse *pParse,   /* The JSON parser that contains the TARGET */
  1408   1461     u32 iTarget,         /* Node of the TARGET in pParse */

Changes to ext/rtree/rtree.c.

  3219   3219   **     INSERT INTO rtree...
  3220   3220   **     DROP TABLE <tablename>;    -- Would fail with SQLITE_LOCKED
  3221   3221   **   COMMIT;
  3222   3222   */
  3223   3223   static int rtreeSavepoint(sqlite3_vtab *pVtab, int iSavepoint){
  3224   3224     Rtree *pRtree = (Rtree *)pVtab;
  3225   3225     int iwt = pRtree->inWrTrans;
         3226  +  UNUSED_PARAMETER(iSavepoint);
  3226   3227     pRtree->inWrTrans = 0;
  3227   3228     nodeBlobReset(pRtree);
  3228   3229     pRtree->inWrTrans = iwt;
  3229   3230     return SQLITE_OK;
  3230   3231   }
  3231   3232   
  3232   3233   /*

Changes to src/auth.c.

   212    212     if( db->init.busy || IN_DECLARE_VTAB ){
   213    213       return SQLITE_OK;
   214    214     }
   215    215   
   216    216     if( db->xAuth==0 ){
   217    217       return SQLITE_OK;
   218    218     }
          219  +
          220  +  /* EVIDENCE-OF: R-43249-19882 The third through sixth parameters to the
          221  +  ** callback are either NULL pointers or zero-terminated strings that
          222  +  ** contain additional details about the action to be authorized.
          223  +  **
          224  +  ** The following testcase() macros show that any of the 3rd through 6th
          225  +  ** parameters can be either NULL or a string. */
          226  +  testcase( zArg1==0 );
          227  +  testcase( zArg2==0 );
          228  +  testcase( zArg3==0 );
          229  +  testcase( pParse->zAuthContext==0 );
          230  +
   219    231     rc = db->xAuth(db->pAuthArg, code, zArg1, zArg2, zArg3, pParse->zAuthContext
   220    232   #ifdef SQLITE_USER_AUTHENTICATION
   221    233                    ,db->auth.zAuthUser
   222    234   #endif
   223    235                   );
   224    236     if( rc==SQLITE_DENY ){
   225    237       sqlite3ErrorMsg(pParse, "not authorized");

Changes to src/btree.c.

  8192   8192         return SQLITE_OK;
  8193   8193       }
  8194   8194       dropCell(pPage, idx, info.nSize, &rc);
  8195   8195       if( rc ) goto end_insert;
  8196   8196     }else if( loc<0 && pPage->nCell>0 ){
  8197   8197       assert( pPage->leaf );
  8198   8198       idx = ++pCur->ix;
         8199  +    pCur->curFlags &= ~BTCF_ValidNKey;
  8199   8200     }else{
  8200   8201       assert( pPage->leaf );
  8201   8202     }
  8202   8203     insertCell(pPage, idx, newCell, szNew, 0, 0, &rc);
  8203   8204     assert( pPage->nOverflow==0 || rc==SQLITE_OK );
  8204   8205     assert( rc!=SQLITE_OK || pPage->nCell>0 || pPage->nOverflow>0 );
  8205   8206   
................................................................................
  9316   9317   
  9317   9318       /* Check for integer primary key out of range */
  9318   9319       if( pPage->intKey ){
  9319   9320         if( keyCanBeEqual ? (info.nKey > maxKey) : (info.nKey >= maxKey) ){
  9320   9321           checkAppendMsg(pCheck, "Rowid %lld out of order", info.nKey);
  9321   9322         }
  9322   9323         maxKey = info.nKey;
         9324  +      keyCanBeEqual = 0;     /* Only the first key on the page may ==maxKey */
  9323   9325       }
  9324   9326   
  9325   9327       /* Check the content overflow list */
  9326   9328       if( info.nPayload>info.nLocal ){
  9327   9329         int nPage;       /* Number of pages on the overflow chain */
  9328   9330         Pgno pgnoOvfl;   /* First page of the overflow chain */
  9329   9331         assert( pc + info.nSize - 4 <= usableSize );

Changes to src/delete.c.

   346    346       sqlite3VdbeAddOp2(v, OP_Integer, 0, memCnt);
   347    347     }
   348    348   
   349    349   #ifndef SQLITE_OMIT_TRUNCATE_OPTIMIZATION
   350    350     /* Special case: A DELETE without a WHERE clause deletes everything.
   351    351     ** It is easier just to erase the whole table. Prior to version 3.6.5,
   352    352     ** this optimization caused the row change count (the value returned by 
   353         -  ** API function sqlite3_count_changes) to be set incorrectly.  */
          353  +  ** API function sqlite3_count_changes) to be set incorrectly.
          354  +  **
          355  +  ** The "rcauth==SQLITE_OK" terms is the
          356  +  ** IMPLEMENATION-OF: R-17228-37124 If the action code is SQLITE_DELETE and
          357  +  ** the callback returns SQLITE_IGNORE then the DELETE operation proceeds but
          358  +  ** the truncate optimization is disabled and all rows are deleted
          359  +  ** individually.
          360  +  */
   354    361     if( rcauth==SQLITE_OK
   355    362      && pWhere==0
   356    363      && !bComplex
   357    364      && !IsVirtual(pTab)
   358    365   #ifdef SQLITE_ENABLE_PREUPDATE_HOOK
   359    366      && db->xPreUpdateCallback==0
   360    367   #endif

Changes to src/expr.c.

  1546   1546       if( pList ){
  1547   1547         assert( pList->nExpr==iFirst+i+1 );
  1548   1548         pList->a[pList->nExpr-1].zName = pColumns->a[i].zName;
  1549   1549         pColumns->a[i].zName = 0;
  1550   1550       }
  1551   1551     }
  1552   1552   
  1553         -  if( pExpr->op==TK_SELECT && pList ){
         1553  +  if( !db->mallocFailed && pExpr->op==TK_SELECT && ALWAYS(pList!=0) ){
  1554   1554       Expr *pFirst = pList->a[iFirst].pExpr;
  1555   1555       assert( pFirst!=0 );
  1556   1556       assert( pFirst->op==TK_SELECT_COLUMN );
  1557   1557        
  1558   1558       /* Store the SELECT statement in pRight so it will be deleted when
  1559   1559       ** sqlite3ExprListDelete() is called */
  1560   1560       pFirst->pRight = pExpr;
................................................................................
  1811   1811   ** expression must not refer to any non-deterministic function nor any
  1812   1812   ** table other than iCur.
  1813   1813   */
  1814   1814   int sqlite3ExprIsTableConstant(Expr *p, int iCur){
  1815   1815     return exprIsConst(p, 3, iCur);
  1816   1816   }
  1817   1817   
         1818  +
         1819  +/*
         1820  +** sqlite3WalkExpr() callback used by sqlite3ExprIsConstantOrGroupBy().
         1821  +*/
         1822  +static int exprNodeIsConstantOrGroupBy(Walker *pWalker, Expr *pExpr){
         1823  +  ExprList *pGroupBy = pWalker->u.pGroupBy;
         1824  +  int i;
         1825  +
         1826  +  /* Check if pExpr is identical to any GROUP BY term. If so, consider
         1827  +  ** it constant.  */
         1828  +  for(i=0; i<pGroupBy->nExpr; i++){
         1829  +    Expr *p = pGroupBy->a[i].pExpr;
         1830  +    if( sqlite3ExprCompare(pExpr, p, -1)<2 ){
         1831  +      CollSeq *pColl = sqlite3ExprCollSeq(pWalker->pParse, p);
         1832  +      if( pColl==0 || sqlite3_stricmp("BINARY", pColl->zName)==0 ){
         1833  +        return WRC_Prune;
         1834  +      }
         1835  +    }
         1836  +  }
         1837  +
         1838  +  /* Check if pExpr is a sub-select. If so, consider it variable. */
         1839  +  if( ExprHasProperty(pExpr, EP_xIsSelect) ){
         1840  +    pWalker->eCode = 0;
         1841  +    return WRC_Abort;
         1842  +  }
         1843  +
         1844  +  return exprNodeIsConstant(pWalker, pExpr);
         1845  +}
         1846  +
         1847  +/*
         1848  +** Walk the expression tree passed as the first argument. Return non-zero
         1849  +** if the expression consists entirely of constants or copies of terms 
         1850  +** in pGroupBy that sort with the BINARY collation sequence.
         1851  +**
         1852  +** This routine is used to determine if a term of the HAVING clause can
         1853  +** be promoted into the WHERE clause.  In order for such a promotion to work,
         1854  +** the value of the HAVING clause term must be the same for all members of
         1855  +** a "group".  The requirement that the GROUP BY term must be BINARY
         1856  +** assumes that no other collating sequence will have a finer-grained
         1857  +** grouping than binary.  In other words (A=B COLLATE binary) implies
         1858  +** A=B in every other collating sequence.  The requirement that the
         1859  +** GROUP BY be BINARY is stricter than necessary.  It would also work
         1860  +** to promote HAVING clauses that use the same alternative collating
         1861  +** sequence as the GROUP BY term, but that is much harder to check,
         1862  +** alternative collating sequences are uncommon, and this is only an
         1863  +** optimization, so we take the easy way out and simply require the
         1864  +** GROUP BY to use the BINARY collating sequence.
         1865  +*/
         1866  +int sqlite3ExprIsConstantOrGroupBy(Parse *pParse, Expr *p, ExprList *pGroupBy){
         1867  +  Walker w;
         1868  +  memset(&w, 0, sizeof(w));
         1869  +  w.eCode = 1;
         1870  +  w.xExprCallback = exprNodeIsConstantOrGroupBy;
         1871  +  w.u.pGroupBy = pGroupBy;
         1872  +  w.pParse = pParse;
         1873  +  sqlite3WalkExpr(&w, p);
         1874  +  return w.eCode;
         1875  +}
         1876  +
  1818   1877   /*
  1819   1878   ** Walk an expression tree.  Return non-zero if the expression is constant
  1820   1879   ** or a function call with constant arguments.  Return and 0 if there
  1821   1880   ** are any variables.
  1822   1881   **
  1823   1882   ** For the purposes of this function, a double-quoted string (ex: "abc")
  1824   1883   ** is considered a variable but a single-quoted string (ex: 'abc') is

Changes to src/global.c.

   133    133   **
   134    134   ** EVIDENCE-OF: R-38799-08373 URI filenames can be enabled or disabled
   135    135   ** using the SQLITE_USE_URI=1 or SQLITE_USE_URI=0 compile-time options.
   136    136   **
   137    137   ** EVIDENCE-OF: R-43642-56306 By default, URI handling is globally
   138    138   ** disabled. The default value may be changed by compiling with the
   139    139   ** SQLITE_USE_URI symbol defined.
          140  +**
          141  +** URI filenames are enabled by default if SQLITE_HAS_CODEC is
          142  +** enabled.
   140    143   */
   141    144   #ifndef SQLITE_USE_URI
   142         -# define  SQLITE_USE_URI 0
          145  +# ifdef SQLITE_HAS_CODEC
          146  +#  define SQLITE_USE_URI 1
          147  +# else
          148  +#  define SQLITE_USE_URI 0
          149  +# endif
   143    150   #endif
   144    151   
   145    152   /* EVIDENCE-OF: R-38720-18127 The default setting is determined by the
   146    153   ** SQLITE_ALLOW_COVERING_INDEX_SCAN compile-time option, or is "on" if
   147    154   ** that compile-time option is omitted.
   148    155   */
   149    156   #ifndef SQLITE_ALLOW_COVERING_INDEX_SCAN

Changes to src/main.c.

  3210   3210       /* Opening a db handle. Fourth parameter is passed 0. */
  3211   3211       void *pArg = sqlite3GlobalConfig.pSqllogArg;
  3212   3212       sqlite3GlobalConfig.xSqllog(pArg, db, zFilename, 0);
  3213   3213     }
  3214   3214   #endif
  3215   3215   #if defined(SQLITE_HAS_CODEC)
  3216   3216     if( rc==SQLITE_OK ){
  3217         -    const char *zHexKey = sqlite3_uri_parameter(zOpen, "hexkey");
  3218         -    if( zHexKey && zHexKey[0] ){
         3217  +    const char *zKey;
         3218  +    if( (zKey = sqlite3_uri_parameter(zOpen, "hexkey"))!=0 && zKey[0] ){;
  3219   3219         u8 iByte;
  3220   3220         int i;
  3221         -      char zKey[40];
  3222         -      for(i=0, iByte=0; i<sizeof(zKey)*2 && sqlite3Isxdigit(zHexKey[i]); i++){
  3223         -        iByte = (iByte<<4) + sqlite3HexToInt(zHexKey[i]);
  3224         -        if( (i&1)!=0 ) zKey[i/2] = iByte;
         3221  +      char zDecoded[40];
         3222  +      for(i=0, iByte=0; i<sizeof(zDecoded)*2 && sqlite3Isxdigit(zKey[i]); i++){
         3223  +        iByte = (iByte<<4) + sqlite3HexToInt(zKey[i]);
         3224  +        if( (i&1)!=0 ) zDecoded[i/2] = iByte;
  3225   3225         }
  3226         -      sqlite3_key_v2(db, 0, zKey, i/2);
         3226  +      sqlite3_key_v2(db, 0, zDecoded, i/2);
         3227  +    }else if( (zKey = sqlite3_uri_parameter(zOpen, "key"))!=0 ){
         3228  +      sqlite3_key_v2(db, 0, zKey, sqlite3Strlen30(zKey));
  3227   3229       }
  3228   3230     }
  3229   3231   #endif
  3230   3232     sqlite3_free(zOpen);
  3231   3233     return rc & 0xff;
  3232   3234   }
  3233   3235   

Changes to src/pager.c.

  2254   2254     int rc;
  2255   2255     PgHdr *pPg;                   /* An existing page in the cache */
  2256   2256     Pgno pgno;                    /* The page number of a page in journal */
  2257   2257     u32 cksum;                    /* Checksum used for sanity checking */
  2258   2258     char *aData;                  /* Temporary storage for the page */
  2259   2259     sqlite3_file *jfd;            /* The file descriptor for the journal file */
  2260   2260     int isSynced;                 /* True if journal page is synced */
         2261  +#ifdef SQLITE_HAS_CODEC
         2262  +  /* The jrnlEnc flag is true if Journal pages should be passed through
         2263  +  ** the codec.  It is false for pure in-memory journals. */
         2264  +  const int jrnlEnc = (isMainJrnl || pPager->subjInMemory==0);
         2265  +#endif
  2261   2266   
  2262   2267     assert( (isMainJrnl&~1)==0 );      /* isMainJrnl is 0 or 1 */
  2263   2268     assert( (isSavepnt&~1)==0 );       /* isSavepnt is 0 or 1 */
  2264   2269     assert( isMainJrnl || pDone );     /* pDone always used on sub-journals */
  2265   2270     assert( isSavepnt || pDone==0 );   /* pDone never used on non-savepoint */
  2266   2271   
  2267   2272     aData = pPager->pTmpSpace;
................................................................................
  2377   2382     if( isOpen(pPager->fd)
  2378   2383      && (pPager->eState>=PAGER_WRITER_DBMOD || pPager->eState==PAGER_OPEN)
  2379   2384      && isSynced
  2380   2385     ){
  2381   2386       i64 ofst = (pgno-1)*(i64)pPager->pageSize;
  2382   2387       testcase( !isSavepnt && pPg!=0 && (pPg->flags&PGHDR_NEED_SYNC)!=0 );
  2383   2388       assert( !pagerUseWal(pPager) );
         2389  +
         2390  +    /* Write the data read from the journal back into the database file.
         2391  +    ** This is usually safe even for an encrypted database - as the data
         2392  +    ** was encrypted before it was written to the journal file. The exception
         2393  +    ** is if the data was just read from an in-memory sub-journal. In that
         2394  +    ** case it must be encrypted here before it is copied into the database
         2395  +    ** file.  */
         2396  +#ifdef SQLITE_HAS_CODEC
         2397  +    if( !jrnlEnc ){
         2398  +      CODEC2(pPager, aData, pgno, 7, rc=SQLITE_NOMEM_BKPT, aData);
         2399  +      rc = sqlite3OsWrite(pPager->fd, (u8 *)aData, pPager->pageSize, ofst);
         2400  +      CODEC1(pPager, aData, pgno, 3, rc=SQLITE_NOMEM_BKPT);
         2401  +    }else
         2402  +#endif
  2384   2403       rc = sqlite3OsWrite(pPager->fd, (u8 *)aData, pPager->pageSize, ofst);
         2404  +
  2385   2405       if( pgno>pPager->dbFileSize ){
  2386   2406         pPager->dbFileSize = pgno;
  2387   2407       }
  2388   2408       if( pPager->pBackup ){
  2389         -      CODEC1(pPager, aData, pgno, 3, rc=SQLITE_NOMEM_BKPT);
         2409  +#ifdef SQLITE_HAS_CODEC
         2410  +      if( jrnlEnc ){
         2411  +        CODEC1(pPager, aData, pgno, 3, rc=SQLITE_NOMEM_BKPT);
         2412  +        sqlite3BackupUpdate(pPager->pBackup, pgno, (u8*)aData);
         2413  +        CODEC2(pPager, aData, pgno, 7, rc=SQLITE_NOMEM_BKPT,aData);
         2414  +      }else
         2415  +#endif
  2390   2416         sqlite3BackupUpdate(pPager->pBackup, pgno, (u8*)aData);
  2391         -      CODEC2(pPager, aData, pgno, 7, rc=SQLITE_NOMEM_BKPT, aData);
  2392   2417       }
  2393   2418     }else if( !isMainJrnl && pPg==0 ){
  2394   2419       /* If this is a rollback of a savepoint and data was not written to
  2395   2420       ** the database and the page is not in-memory, there is a potential
  2396   2421       ** problem. When the page is next fetched by the b-tree layer, it 
  2397   2422       ** will be read from the database file, which may or may not be 
  2398   2423       ** current. 
................................................................................
  2436   2461       /* If this was page 1, then restore the value of Pager.dbFileVers.
  2437   2462       ** Do this before any decoding. */
  2438   2463       if( pgno==1 ){
  2439   2464         memcpy(&pPager->dbFileVers, &((u8*)pData)[24],sizeof(pPager->dbFileVers));
  2440   2465       }
  2441   2466   
  2442   2467       /* Decode the page just read from disk */
  2443         -    CODEC1(pPager, pData, pPg->pgno, 3, rc=SQLITE_NOMEM_BKPT);
         2468  +#if SQLITE_HAS_CODEC
         2469  +    if( jrnlEnc ){ CODEC1(pPager, pData, pPg->pgno, 3, rc=SQLITE_NOMEM_BKPT); }
         2470  +#endif
  2444   2471       sqlite3PcacheRelease(pPg);
  2445   2472     }
  2446   2473     return rc;
  2447   2474   }
  2448   2475   
  2449   2476   /*
  2450   2477   ** Parameter zMaster is the name of a master journal file. A single journal
................................................................................
  4460   4487   
  4461   4488       /* If the sub-journal was opened successfully (or was already open),
  4462   4489       ** write the journal record into the file.  */
  4463   4490       if( rc==SQLITE_OK ){
  4464   4491         void *pData = pPg->pData;
  4465   4492         i64 offset = (i64)pPager->nSubRec*(4+pPager->pageSize);
  4466   4493         char *pData2;
  4467         -  
  4468         -      CODEC2(pPager, pData, pPg->pgno, 7, return SQLITE_NOMEM_BKPT, pData2);
         4494  +
         4495  +#if SQLITE_HAS_CODEC   
         4496  +      if( !pPager->subjInMemory ){
         4497  +        CODEC2(pPager, pData, pPg->pgno, 7, return SQLITE_NOMEM_BKPT, pData2);
         4498  +      }else
         4499  +#endif
         4500  +      pData2 = pData;
  4469   4501         PAGERTRACE(("STMT-JOURNAL %d page %d\n", PAGERID(pPager), pPg->pgno));
  4470   4502         rc = write32bits(pPager->sjfd, offset, pPg->pgno);
  4471   4503         if( rc==SQLITE_OK ){
  4472   4504           rc = sqlite3OsWrite(pPager->sjfd, pData2, pPager->pageSize, offset+4);
  4473   4505         }
  4474   4506       }
  4475   4507     }

Changes to src/select.c.

  4874   4874           pParse->pVdbe, OP_Explain, pParse->iSelectId, 0, 0, zEqp, P4_DYNAMIC
  4875   4875       );
  4876   4876     }
  4877   4877   }
  4878   4878   #else
  4879   4879   # define explainSimpleCount(a,b,c)
  4880   4880   #endif
         4881  +
         4882  +/*
         4883  +** Context object for havingToWhereExprCb().
         4884  +*/
         4885  +struct HavingToWhereCtx {
         4886  +  Expr **ppWhere;
         4887  +  ExprList *pGroupBy;
         4888  +};
         4889  +
         4890  +/*
         4891  +** sqlite3WalkExpr() callback used by havingToWhere().
         4892  +**
         4893  +** If the node passed to the callback is a TK_AND node, return 
         4894  +** WRC_Continue to tell sqlite3WalkExpr() to iterate through child nodes.
         4895  +**
         4896  +** Otherwise, return WRC_Prune. In this case, also check if the 
         4897  +** sub-expression matches the criteria for being moved to the WHERE
         4898  +** clause. If so, add it to the WHERE clause and replace the sub-expression
         4899  +** within the HAVING expression with a constant "1".
         4900  +*/
         4901  +static int havingToWhereExprCb(Walker *pWalker, Expr *pExpr){
         4902  +  if( pExpr->op!=TK_AND ){
         4903  +    struct HavingToWhereCtx *p = pWalker->u.pHavingCtx;
         4904  +    if( sqlite3ExprIsConstantOrGroupBy(pWalker->pParse, pExpr, p->pGroupBy) ){
         4905  +      sqlite3 *db = pWalker->pParse->db;
         4906  +      Expr *pNew = sqlite3ExprAlloc(db, TK_INTEGER, &sqlite3IntTokens[1], 0);
         4907  +      if( pNew ){
         4908  +        Expr *pWhere = *(p->ppWhere);
         4909  +        SWAP(Expr, *pNew, *pExpr);
         4910  +        pNew = sqlite3ExprAnd(db, pWhere, pNew);
         4911  +        *(p->ppWhere) = pNew;
         4912  +      }
         4913  +    }
         4914  +    return WRC_Prune;
         4915  +  }
         4916  +  return WRC_Continue;
         4917  +}
         4918  +
         4919  +/*
         4920  +** Transfer eligible terms from the HAVING clause of a query, which is
         4921  +** processed after grouping, to the WHERE clause, which is processed before
         4922  +** grouping. For example, the query:
         4923  +**
         4924  +**   SELECT * FROM <tables> WHERE a=? GROUP BY b HAVING b=? AND c=?
         4925  +**
         4926  +** can be rewritten as:
         4927  +**
         4928  +**   SELECT * FROM <tables> WHERE a=? AND b=? GROUP BY b HAVING c=?
         4929  +**
         4930  +** A term of the HAVING expression is eligible for transfer if it consists
         4931  +** entirely of constants and expressions that are also GROUP BY terms that
         4932  +** use the "BINARY" collation sequence.
         4933  +*/
         4934  +static void havingToWhere(
         4935  +  Parse *pParse,
         4936  +  ExprList *pGroupBy,
         4937  +  Expr *pHaving, 
         4938  +  Expr **ppWhere
         4939  +){
         4940  +  struct HavingToWhereCtx sCtx;
         4941  +  Walker sWalker;
         4942  +
         4943  +  sCtx.ppWhere = ppWhere;
         4944  +  sCtx.pGroupBy = pGroupBy;
         4945  +
         4946  +  memset(&sWalker, 0, sizeof(sWalker));
         4947  +  sWalker.pParse = pParse;
         4948  +  sWalker.xExprCallback = havingToWhereExprCb;
         4949  +  sWalker.u.pHavingCtx = &sCtx;
         4950  +  sqlite3WalkExpr(&sWalker, pHaving);
         4951  +}
         4952  +
         4953  +/*
         4954  +** Check to see if the pThis entry of pTabList is a self-join of a prior view.
         4955  +** If it is, then return the SrcList_item for the prior view.  If it is not,
         4956  +** then return 0.
         4957  +*/
         4958  +static struct SrcList_item *isSelfJoinView(
         4959  +  SrcList *pTabList,           /* Search for self-joins in this FROM clause */
         4960  +  struct SrcList_item *pThis   /* Search for prior reference to this subquery */
         4961  +){
         4962  +  struct SrcList_item *pItem;
         4963  +  for(pItem = pTabList->a; pItem<pThis; pItem++){
         4964  +    if( pItem->pSelect==0 ) continue;
         4965  +    if( pItem->fg.viaCoroutine ) continue;
         4966  +    if( pItem->zName==0 ) continue;
         4967  +    if( sqlite3_stricmp(pItem->zDatabase, pThis->zDatabase)!=0 ) continue;
         4968  +    if( sqlite3_stricmp(pItem->zName, pThis->zName)!=0 ) continue;
         4969  +    if( sqlite3ExprCompare(pThis->pSelect->pWhere, pItem->pSelect->pWhere, -1) ){
         4970  +      /* The view was modified by some other optimization such as
         4971  +      ** pushDownWhereTerms() */
         4972  +      continue;
         4973  +    }
         4974  +    return pItem;
         4975  +  }
         4976  +  return 0;
         4977  +}
  4881   4978   
  4882   4979   /*
  4883   4980   ** Generate code for the SELECT statement given in the p argument.  
  4884   4981   **
  4885   4982   ** The results are returned according to the SelectDest structure.
  4886   4983   ** See comments in sqliteInt.h for further information.
  4887   4984   **
................................................................................
  5014   5111       SELECTTRACE(1,pParse,p,("end compound-select processing\n"));
  5015   5112       pParse->nSelectIndent--;
  5016   5113   #endif
  5017   5114       return rc;
  5018   5115     }
  5019   5116   #endif
  5020   5117   
  5021         -  /* Generate code for all sub-queries in the FROM clause
         5118  +  /* For each term in the FROM clause, do two things:
         5119  +  ** (1) Authorized unreferenced tables
         5120  +  ** (2) Generate code for all sub-queries
  5022   5121     */
  5023         -#if !defined(SQLITE_OMIT_SUBQUERY) || !defined(SQLITE_OMIT_VIEW)
  5024   5122     for(i=0; i<pTabList->nSrc; i++){
  5025   5123       struct SrcList_item *pItem = &pTabList->a[i];
  5026   5124       SelectDest dest;
  5027         -    Select *pSub = pItem->pSelect;
         5125  +    Select *pSub;
         5126  +
         5127  +    /* Issue SQLITE_READ authorizations with a fake column name for any tables that
         5128  +    ** are referenced but from which no values are extracted. Examples of where these
         5129  +    ** kinds of null SQLITE_READ authorizations would occur:
         5130  +    **
         5131  +    **     SELECT count(*) FROM t1;   -- SQLITE_READ t1.""
         5132  +    **     SELECT t1.* FROM t1, t2;   -- SQLITE_READ t2.""
         5133  +    **
         5134  +    ** The fake column name is an empty string.  It is possible for a table to
         5135  +    ** have a column named by the empty string, in which case there is no way to
         5136  +    ** distinguish between an unreferenced table and an actual reference to the
         5137  +    ** "" column.  The original design was for the fake column name to be a NULL,
         5138  +    ** which would be unambiguous.  But legacy authorization callbacks might
         5139  +    ** assume the column name is non-NULL and segfault.  The use of an empty string
         5140  +    ** for the fake column name seems safer.
         5141  +    */
         5142  +    if( pItem->colUsed==0 ){
         5143  +      sqlite3AuthCheck(pParse, SQLITE_READ, pItem->zName, "", pItem->zDatabase);
         5144  +    }
         5145  +
         5146  +#if !defined(SQLITE_OMIT_SUBQUERY) || !defined(SQLITE_OMIT_VIEW)
         5147  +    /* Generate code for all sub-queries in the FROM clause
         5148  +    */
         5149  +    pSub = pItem->pSelect;
  5028   5150       if( pSub==0 ) continue;
  5029   5151   
  5030   5152       /* Sometimes the code for a subquery will be generated more than
  5031   5153       ** once, if the subquery is part of the WHERE clause in a LEFT JOIN,
  5032   5154       ** for example.  In that case, do not regenerate the code to manifest
  5033   5155       ** a view or the co-routine to implement a view.  The first instance
  5034   5156       ** is sufficient, though the subroutine to manifest the view does need
  5035   5157       ** to be invoked again. */
  5036   5158       if( pItem->addrFillSub ){
  5037   5159         if( pItem->fg.viaCoroutine==0 ){
         5160  +        /* The subroutine that manifests the view might be a one-time routine,
         5161  +        ** or it might need to be rerun on each iteration because it
         5162  +        ** encodes a correlated subquery. */
         5163  +        testcase( sqlite3VdbeGetOp(v, pItem->addrFillSub)->opcode==OP_Once );
  5038   5164           sqlite3VdbeAddOp2(v, OP_Gosub, pItem->regReturn, pItem->addrFillSub);
  5039   5165         }
  5040   5166         continue;
  5041   5167       }
  5042   5168   
  5043   5169       /* Increment Parse.nHeight by the height of the largest expression
  5044   5170       ** tree referred to by this, the parent select. The child select
................................................................................
  5105   5231         ** the content of this subquery.  pItem->addrFillSub will point
  5106   5232         ** to the address of the generated subroutine.  pItem->regReturn
  5107   5233         ** is a register allocated to hold the subroutine return address
  5108   5234         */
  5109   5235         int topAddr;
  5110   5236         int onceAddr = 0;
  5111   5237         int retAddr;
         5238  +      struct SrcList_item *pPrior;
         5239  +
  5112   5240         assert( pItem->addrFillSub==0 );
  5113   5241         pItem->regReturn = ++pParse->nMem;
  5114   5242         topAddr = sqlite3VdbeAddOp2(v, OP_Integer, 0, pItem->regReturn);
  5115   5243         pItem->addrFillSub = topAddr+1;
  5116   5244         if( pItem->fg.isCorrelated==0 ){
  5117   5245           /* If the subquery is not correlated and if we are not inside of
  5118   5246           ** a trigger, then we only need to compute the value of the subquery
  5119   5247           ** once. */
  5120   5248           onceAddr = sqlite3VdbeAddOp0(v, OP_Once); VdbeCoverage(v);
  5121   5249           VdbeComment((v, "materialize \"%s\"", pItem->pTab->zName));
  5122   5250         }else{
  5123   5251           VdbeNoopComment((v, "materialize \"%s\"", pItem->pTab->zName));
  5124   5252         }
  5125         -      sqlite3SelectDestInit(&dest, SRT_EphemTab, pItem->iCursor);
  5126         -      explainSetInteger(pItem->iSelectId, (u8)pParse->iNextSelectId);
  5127         -      sqlite3Select(pParse, pSub, &dest);
         5253  +      pPrior = isSelfJoinView(pTabList, pItem);
         5254  +      if( pPrior ){
         5255  +        sqlite3VdbeAddOp2(v, OP_OpenDup, pItem->iCursor, pPrior->iCursor);
         5256  +      }else{
         5257  +        sqlite3SelectDestInit(&dest, SRT_EphemTab, pItem->iCursor);
         5258  +        explainSetInteger(pItem->iSelectId, (u8)pParse->iNextSelectId);
         5259  +        sqlite3Select(pParse, pSub, &dest);
         5260  +      }
  5128   5261         pItem->pTab->nRowLogEst = pSub->nSelectRow;
  5129   5262         if( onceAddr ) sqlite3VdbeJumpHere(v, onceAddr);
  5130   5263         retAddr = sqlite3VdbeAddOp1(v, OP_Return, pItem->regReturn);
  5131   5264         VdbeComment((v, "end %s", pItem->pTab->zName));
  5132   5265         sqlite3VdbeChangeP1(v, topAddr, retAddr);
  5133   5266         sqlite3ClearTempRegCache(pParse);
  5134   5267       }
  5135   5268       if( db->mallocFailed ) goto select_end;
  5136   5269       pParse->nHeight -= sqlite3SelectExprHeight(p);
  5137         -  }
  5138   5270   #endif
         5271  +  }
  5139   5272   
  5140   5273     /* Various elements of the SELECT copied into local variables for
  5141   5274     ** convenience */
  5142   5275     pEList = p->pEList;
  5143   5276     pWhere = p->pWhere;
  5144   5277     pGroupBy = p->pGroupBy;
  5145   5278     pHaving = p->pHaving;
................................................................................
  5339   5472       sNC.pAggInfo = &sAggInfo;
  5340   5473       sAggInfo.mnReg = pParse->nMem+1;
  5341   5474       sAggInfo.nSortingColumn = pGroupBy ? pGroupBy->nExpr : 0;
  5342   5475       sAggInfo.pGroupBy = pGroupBy;
  5343   5476       sqlite3ExprAnalyzeAggList(&sNC, pEList);
  5344   5477       sqlite3ExprAnalyzeAggList(&sNC, sSort.pOrderBy);
  5345   5478       if( pHaving ){
         5479  +      if( pGroupBy ){
         5480  +        assert( pWhere==p->pWhere );
         5481  +        havingToWhere(pParse, pGroupBy, pHaving, &p->pWhere);
         5482  +        pWhere = p->pWhere;
         5483  +      }
  5346   5484         sqlite3ExprAnalyzeAggregates(&sNC, pHaving);
  5347   5485       }
  5348   5486       sAggInfo.nAccumulator = sAggInfo.nColumn;
  5349   5487       for(i=0; i<sAggInfo.nFunc; i++){
  5350   5488         assert( !ExprHasProperty(sAggInfo.aFunc[i].pExpr, EP_xIsSelect) );
  5351   5489         sNC.ncFlags |= NC_InAggFunc;
  5352   5490         sqlite3ExprAnalyzeAggList(&sNC, sAggInfo.aFunc[i].pExpr->x.pList);

Changes to src/shell.c.

   434    434   ** since with %*.*s the width is measured in bytes, not characters.
   435    435   */
   436    436   static void utf8_width_print(FILE *pOut, int w, const char *zUtf){
   437    437     int i;
   438    438     int n;
   439    439     int aw = w<0 ? -w : w;
   440    440     char zBuf[1000];
   441         -  if( aw>sizeof(zBuf)/3 ) aw = sizeof(zBuf)/3;
          441  +  if( aw>(int)sizeof(zBuf)/3 ) aw = (int)sizeof(zBuf)/3;
   442    442     for(i=n=0; zUtf[i]; i++){
   443    443       if( (zUtf[i]&0xc0)!=0x80 ){
   444    444         n++;
   445    445         if( n==aw ){
   446    446           do{ i++; }while( (zUtf[i]&0xc0)==0x80 );
   447    447           break;
   448    448         }
................................................................................
   739    739       u64 s[25];                /* Keccak state. 5x5 lines of 64 bits each */
   740    740       unsigned char x[1600];    /* ... or 1600 bytes */
   741    741     } u;
   742    742     unsigned nRate;        /* Bytes of input accepted per Keccak iteration */
   743    743     unsigned nLoaded;      /* Input bytes loaded into u.x[] so far this cycle */
   744    744     unsigned ixMask;       /* Insert next input into u.x[nLoaded^ixMask]. */
   745    745   };
          746  +
          747  +/* Allow the following routine to use the B0 variable, which is also
          748  +** a macro in the termios.h header file */
          749  +#undef B0
   746    750   
   747    751   /*
   748    752   ** A single step of the Keccak mixing function for a 1600-bit state
   749    753   */
   750    754   static void KeccakF1600Step(SHA3Context *p){
   751    755     int i;
   752    756     u64 B0, B1, B2, B3, B4;

Changes to src/sqlite.h.in.

   854    854   ** anti-virus programs.  By default, the windows VFS will retry file read,
   855    855   ** file write, and file delete operations up to 10 times, with a delay
   856    856   ** of 25 milliseconds before the first retry and with the delay increasing
   857    857   ** by an additional 25 milliseconds with each subsequent retry.  This
   858    858   ** opcode allows these two values (10 retries and 25 milliseconds of delay)
   859    859   ** to be adjusted.  The values are changed for all database connections
   860    860   ** within the same process.  The argument is a pointer to an array of two
   861         -** integers where the first integer i the new retry count and the second
          861  +** integers where the first integer is the new retry count and the second
   862    862   ** integer is the delay.  If either integer is negative, then the setting
   863    863   ** is not changed but instead the prior value of that setting is written
   864    864   ** into the array entry, allowing the current retry settings to be
   865    865   ** interrogated.  The zDbName parameter is ignored.
   866    866   **
   867    867   ** <li>[[SQLITE_FCNTL_PERSIST_WAL]]
   868    868   ** ^The [SQLITE_FCNTL_PERSIST_WAL] opcode is used to set or query the
................................................................................
  2670   2670   ** method.
  2671   2671   */
  2672   2672   void sqlite3_randomness(int N, void *P);
  2673   2673   
  2674   2674   /*
  2675   2675   ** CAPI3REF: Compile-Time Authorization Callbacks
  2676   2676   ** METHOD: sqlite3
         2677  +** KEYWORDS: {authorizer callback}
  2677   2678   **
  2678   2679   ** ^This routine registers an authorizer callback with a particular
  2679   2680   ** [database connection], supplied in the first argument.
  2680   2681   ** ^The authorizer callback is invoked as SQL statements are being compiled
  2681   2682   ** by [sqlite3_prepare()] or its variants [sqlite3_prepare_v2()],
  2682   2683   ** [sqlite3_prepare16()] and [sqlite3_prepare16_v2()].  ^At various
  2683   2684   ** points during the compilation process, as logic is being created
................................................................................
  2697   2698   ** authorizer will fail with an error message explaining that
  2698   2699   ** access is denied. 
  2699   2700   **
  2700   2701   ** ^The first parameter to the authorizer callback is a copy of the third
  2701   2702   ** parameter to the sqlite3_set_authorizer() interface. ^The second parameter
  2702   2703   ** to the callback is an integer [SQLITE_COPY | action code] that specifies
  2703   2704   ** the particular action to be authorized. ^The third through sixth parameters
  2704         -** to the callback are zero-terminated strings that contain additional
  2705         -** details about the action to be authorized.
         2705  +** to the callback are either NULL pointers or zero-terminated strings
         2706  +** that contain additional details about the action to be authorized.
         2707  +** Applications must always be prepared to encounter a NULL pointer in any
         2708  +** of the third through the sixth parameters of the authorization callback.
  2706   2709   **
  2707   2710   ** ^If the action code is [SQLITE_READ]
  2708   2711   ** and the callback returns [SQLITE_IGNORE] then the
  2709   2712   ** [prepared statement] statement is constructed to substitute
  2710   2713   ** a NULL value in place of the table column that would have
  2711   2714   ** been read if [SQLITE_OK] had been returned.  The [SQLITE_IGNORE]
  2712   2715   ** return can be used to deny an untrusted user access to individual
  2713   2716   ** columns of a table.
         2717  +** ^When a table is referenced by a [SELECT] but no column values are
         2718  +** extracted from that table (for example in a query like
         2719  +** "SELECT count(*) FROM tab") then the [SQLITE_READ] authorizer callback
         2720  +** is invoked once for that table with a column name that is an empty string.
  2714   2721   ** ^If the action code is [SQLITE_DELETE] and the callback returns
  2715   2722   ** [SQLITE_IGNORE] then the [DELETE] operation proceeds but the
  2716   2723   ** [truncate optimization] is disabled and all rows are deleted individually.
  2717   2724   **
  2718   2725   ** An authorizer is used when [sqlite3_prepare | preparing]
  2719   2726   ** SQL statements from an untrusted source, to ensure that the SQL statements
  2720   2727   ** do not try to access data they are not allowed to see, or that they do not
................................................................................
  4753   4760   ** of where this might be useful is in a regular-expression matching
  4754   4761   ** function. The compiled version of the regular expression can be stored as
  4755   4762   ** metadata associated with the pattern string.  
  4756   4763   ** Then as long as the pattern string remains the same,
  4757   4764   ** the compiled regular expression can be reused on multiple
  4758   4765   ** invocations of the same function.
  4759   4766   **
  4760         -** ^The sqlite3_get_auxdata() interface returns a pointer to the metadata
  4761         -** associated by the sqlite3_set_auxdata() function with the Nth argument
  4762         -** value to the application-defined function. ^If there is no metadata
  4763         -** associated with the function argument, this sqlite3_get_auxdata() interface
         4767  +** ^The sqlite3_get_auxdata(C,N) interface returns a pointer to the metadata
         4768  +** associated by the sqlite3_set_auxdata(C,N,P,X) function with the Nth argument
         4769  +** value to the application-defined function.  ^N is zero for the left-most
         4770  +** function argument.  ^If there is no metadata
         4771  +** associated with the function argument, the sqlite3_get_auxdata(C,N) interface
  4764   4772   ** returns a NULL pointer.
  4765   4773   **
  4766   4774   ** ^The sqlite3_set_auxdata(C,N,P,X) interface saves P as metadata for the N-th
  4767   4775   ** argument of the application-defined function.  ^Subsequent
  4768   4776   ** calls to sqlite3_get_auxdata(C,N) return P from the most recent
  4769   4777   ** sqlite3_set_auxdata(C,N,P,X) call if the metadata is still valid or
  4770   4778   ** NULL if the metadata has been discarded.
................................................................................
  4786   4794   ** should be called near the end of the function implementation and the
  4787   4795   ** function implementation should not make any use of P after
  4788   4796   ** sqlite3_set_auxdata() has been called.
  4789   4797   **
  4790   4798   ** ^(In practice, metadata is preserved between function calls for
  4791   4799   ** function parameters that are compile-time constants, including literal
  4792   4800   ** values and [parameters] and expressions composed from the same.)^
         4801  +**
         4802  +** The value of the N parameter to these interfaces should be non-negative.
         4803  +** Future enhancements may make use of negative N values to define new
         4804  +** kinds of function caching behavior.
  4793   4805   **
  4794   4806   ** These routines must be called from the same thread in which
  4795   4807   ** the SQL function is running.
  4796   4808   */
  4797   4809   void *sqlite3_get_auxdata(sqlite3_context*, int N);
  4798   4810   void sqlite3_set_auxdata(sqlite3_context*, int N, void*, void (*)(void*));
  4799   4811   

Changes to src/sqliteInt.h.

  3312   3312     Parse *pParse;                            /* Parser context.  */
  3313   3313     int (*xExprCallback)(Walker*, Expr*);     /* Callback for expressions */
  3314   3314     int (*xSelectCallback)(Walker*,Select*);  /* Callback for SELECTs */
  3315   3315     void (*xSelectCallback2)(Walker*,Select*);/* Second callback for SELECTs */
  3316   3316     int walkerDepth;                          /* Number of subqueries */
  3317   3317     u8 eCode;                                 /* A small processing code */
  3318   3318     union {                                   /* Extra data for callback */
  3319         -    NameContext *pNC;                          /* Naming context */
  3320         -    int n;                                     /* A counter */
  3321         -    int iCur;                                  /* A cursor number */
  3322         -    SrcList *pSrcList;                         /* FROM clause */
  3323         -    struct SrcCount *pSrcCount;                /* Counting column references */
  3324         -    struct CCurHint *pCCurHint;                /* Used by codeCursorHint() */
  3325         -    int *aiCol;                                /* array of column indexes */
  3326         -    struct IdxCover *pIdxCover;                /* Check for index coverage */
  3327         -    struct IdxExprTrans *pIdxTrans;            /* Convert indexed expr to column */
         3319  +    NameContext *pNC;                         /* Naming context */
         3320  +    int n;                                    /* A counter */
         3321  +    int iCur;                                 /* A cursor number */
         3322  +    SrcList *pSrcList;                        /* FROM clause */
         3323  +    struct SrcCount *pSrcCount;               /* Counting column references */
         3324  +    struct CCurHint *pCCurHint;               /* Used by codeCursorHint() */
         3325  +    int *aiCol;                               /* array of column indexes */
         3326  +    struct IdxCover *pIdxCover;               /* Check for index coverage */
         3327  +    struct IdxExprTrans *pIdxTrans;           /* Convert indexed expr to column */
         3328  +    ExprList *pGroupBy;                       /* GROUP BY clause */
         3329  +    struct HavingToWhereCtx *pHavingCtx;      /* HAVING to WHERE clause ctx */
  3328   3330     } u;
  3329   3331   };
  3330   3332   
  3331   3333   /* Forward declarations */
  3332   3334   int sqlite3WalkExpr(Walker*, Expr*);
  3333   3335   int sqlite3WalkExprList(Walker*, ExprList*);
  3334   3336   int sqlite3WalkSelect(Walker*, Select*);
................................................................................
  3790   3792   void sqlite3RollbackTransaction(Parse*);
  3791   3793   void sqlite3Savepoint(Parse*, int, Token*);
  3792   3794   void sqlite3CloseSavepoints(sqlite3 *);
  3793   3795   void sqlite3LeaveMutexAndCloseZombie(sqlite3*);
  3794   3796   int sqlite3ExprIsConstant(Expr*);
  3795   3797   int sqlite3ExprIsConstantNotJoin(Expr*);
  3796   3798   int sqlite3ExprIsConstantOrFunction(Expr*, u8);
         3799  +int sqlite3ExprIsConstantOrGroupBy(Parse*, Expr*, ExprList*);
  3797   3800   int sqlite3ExprIsTableConstant(Expr*,int);
  3798   3801   #ifdef SQLITE_ENABLE_CURSOR_HINTS
  3799   3802   int sqlite3ExprContainsSubquery(Expr*);
  3800   3803   #endif
  3801   3804   int sqlite3ExprIsInteger(Expr*, int*);
  3802   3805   int sqlite3ExprCanBeNull(const Expr*);
  3803   3806   int sqlite3ExprNeedsNoAffinityChange(const Expr*, char);

Changes to src/tclsqlite.c.

  1029   1029     ,const char *zArg5
  1030   1030   #endif
  1031   1031   ){
  1032   1032     const char *zCode;
  1033   1033     Tcl_DString str;
  1034   1034     int rc;
  1035   1035     const char *zReply;
         1036  +  /* EVIDENCE-OF: R-38590-62769 The first parameter to the authorizer
         1037  +  ** callback is a copy of the third parameter to the
         1038  +  ** sqlite3_set_authorizer() interface.
         1039  +  */
  1036   1040     SqliteDb *pDb = (SqliteDb*)pArg;
  1037   1041     if( pDb->disableAuth ) return SQLITE_OK;
  1038   1042   
         1043  +  /* EVIDENCE-OF: R-56518-44310 The second parameter to the callback is an
         1044  +  ** integer action code that specifies the particular action to be
         1045  +  ** authorized. */
  1039   1046     switch( code ){
  1040   1047       case SQLITE_COPY              : zCode="SQLITE_COPY"; break;
  1041   1048       case SQLITE_CREATE_INDEX      : zCode="SQLITE_CREATE_INDEX"; break;
  1042   1049       case SQLITE_CREATE_TABLE      : zCode="SQLITE_CREATE_TABLE"; break;
  1043   1050       case SQLITE_CREATE_TEMP_INDEX : zCode="SQLITE_CREATE_TEMP_INDEX"; break;
  1044   1051       case SQLITE_CREATE_TEMP_TABLE : zCode="SQLITE_CREATE_TEMP_TABLE"; break;
  1045   1052       case SQLITE_CREATE_TEMP_TRIGGER: zCode="SQLITE_CREATE_TEMP_TRIGGER"; break;

Changes to src/test1.c.

  4963   4963       return TCL_ERROR;
  4964   4964     }
  4965   4965     if( getDbPointer(interp, argv[1], &db) ) return TCL_ERROR;
  4966   4966     sqlite3_interrupt(db);
  4967   4967     return TCL_OK;
  4968   4968   }
  4969   4969   
  4970         -static u8 *sqlite3_stack_baseline = 0;
  4971         -
  4972         -/*
  4973         -** Fill the stack with a known bitpattern.
  4974         -*/
  4975         -static void prepStack(void){
  4976         -  int i;
  4977         -  u32 bigBuf[65536];
  4978         -  for(i=0; i<sizeof(bigBuf)/sizeof(bigBuf[0]); i++) bigBuf[i] = 0xdeadbeef;
  4979         -  sqlite3_stack_baseline = (u8*)&bigBuf[65536];
  4980         -}
  4981         -
  4982         -/*
  4983         -** Get the current stack depth.  Used for debugging only.
  4984         -*/
  4985         -u64 sqlite3StackDepth(void){
  4986         -  u8 x;
  4987         -  return (u64)(sqlite3_stack_baseline - &x);
  4988         -}
  4989         -
  4990         -/*
  4991         -** Usage:  sqlite3_stack_used DB SQL
  4992         -**
  4993         -** Try to measure the amount of stack space used by a call to sqlite3_exec
  4994         -*/
  4995         -static int SQLITE_TCLAPI test_stack_used(
  4996         -  void * clientData,
  4997         -  Tcl_Interp *interp,
  4998         -  int argc,
  4999         -  char **argv
  5000         -){
  5001         -  sqlite3 *db;
  5002         -  int i;
  5003         -  if( argc!=3 ){
  5004         -    Tcl_AppendResult(interp, "wrong # args: should be \"", argv[0], 
  5005         -        " DB SQL", 0);
  5006         -    return TCL_ERROR;
  5007         -  }
  5008         -  if( getDbPointer(interp, argv[1], &db) ) return TCL_ERROR;
  5009         -  prepStack();
  5010         -  (void)sqlite3_exec(db, argv[2], 0, 0, 0);
  5011         -  for(i=65535; i>=0 && ((u32*)sqlite3_stack_baseline)[-i]==0xdeadbeef; i--){}
  5012         -  Tcl_SetObjResult(interp, Tcl_NewIntObj(i*4));
  5013         -  return TCL_OK;
  5014         -}
  5015         -
  5016   4970   /*
  5017   4971   ** Usage: sqlite_delete_function DB function-name
  5018   4972   **
  5019   4973   ** Delete the user function 'function-name' from database handle DB. It
  5020   4974   ** is assumed that the user function was created as UTF8, any number of
  5021   4975   ** arguments (the way the TCL interface does it).
  5022   4976   */
................................................................................
  7660   7614        { "sqlite3_key",                   (Tcl_CmdProc*)test_key              },
  7661   7615        { "sqlite3_rekey",                 (Tcl_CmdProc*)test_rekey            },
  7662   7616        { "sqlite_set_magic",              (Tcl_CmdProc*)sqlite_set_magic      },
  7663   7617        { "sqlite3_interrupt",             (Tcl_CmdProc*)test_interrupt        },
  7664   7618        { "sqlite_delete_function",        (Tcl_CmdProc*)delete_function       },
  7665   7619        { "sqlite_delete_collation",       (Tcl_CmdProc*)delete_collation      },
  7666   7620        { "sqlite3_get_autocommit",        (Tcl_CmdProc*)get_autocommit        },
  7667         -     { "sqlite3_stack_used",            (Tcl_CmdProc*)test_stack_used       },
  7668   7621        { "sqlite3_busy_timeout",          (Tcl_CmdProc*)test_busy_timeout     },
  7669   7622        { "printf",                        (Tcl_CmdProc*)test_printf           },
  7670   7623        { "sqlite3IoTrace",              (Tcl_CmdProc*)test_io_trace         },
  7671   7624        { "clang_sanitize_address",        (Tcl_CmdProc*)clang_sanitize_address },
  7672   7625     };
  7673   7626     static struct {
  7674   7627        char *zName;

Changes to src/util.c.

   709    709         memcpy(pValue, &u, 4);
   710    710         return 1;
   711    711       }else{
   712    712         return 0;
   713    713       }
   714    714     }
   715    715   #endif
          716  +  if( !sqlite3Isdigit(zNum[0]) ) return 0;
   716    717     while( zNum[0]=='0' ) zNum++;
   717    718     for(i=0; i<11 && (c = zNum[i] - '0')>=0 && c<=9; i++){
   718    719       v = v*10 + c;
   719    720     }
   720    721   
   721    722     /* The longest decimal representation of a 32 bit integer is 10 digits:
   722    723     **

Changes to src/vdbe.c.

   761    761   ** to the current line should be indented for EXPLAIN output.
   762    762   */
   763    763   case OP_Goto: {             /* jump */
   764    764   jump_to_p2_and_check_for_interrupt:
   765    765     pOp = &aOp[pOp->p2 - 1];
   766    766   
   767    767     /* Opcodes that are used as the bottom of a loop (OP_Next, OP_Prev,
   768         -  ** OP_VNext, OP_RowSetNext, or OP_SorterNext) all jump here upon
          768  +  ** OP_VNext, or OP_SorterNext) all jump here upon
   769    769     ** completion.  Check to see if sqlite3_interrupt() has been called
   770    770     ** or if the progress callback needs to be invoked. 
   771    771     **
   772    772     ** This code uses unstructured "goto" statements and does not look clean.
   773    773     ** But that is not due to sloppy coding habits. The code is written this
   774    774     ** way for performance, to avoid having to run the interrupt and progress
   775    775     ** checks on every opcode.  This helps sqlite3_step() to run about 1.5%
................................................................................
  1564   1564   arithmetic_result_is_null:
  1565   1565     sqlite3VdbeMemSetNull(pOut);
  1566   1566     break;
  1567   1567   }
  1568   1568   
  1569   1569   /* Opcode: CollSeq P1 * * P4
  1570   1570   **
  1571         -** P4 is a pointer to a CollSeq struct. If the next call to a user function
         1571  +** P4 is a pointer to a CollSeq object. If the next call to a user function
  1572   1572   ** or aggregate calls sqlite3GetFuncCollSeq(), this collation sequence will
  1573   1573   ** be returned. This is used by the built-in min(), max() and nullif()
  1574   1574   ** functions.
  1575   1575   **
  1576   1576   ** If P1 is not zero, then it is a register that a subsequent min() or
  1577   1577   ** max() aggregate will set to 1 if the current row is not the minimum or
  1578   1578   ** maximum.  The P1 register is initialized to 0 by this instruction.
................................................................................
  1845   1845   #ifndef SQLITE_OMIT_CAST
  1846   1846   /* Opcode: Cast P1 P2 * * *
  1847   1847   ** Synopsis: affinity(r[P1])
  1848   1848   **
  1849   1849   ** Force the value in register P1 to be the type defined by P2.
  1850   1850   ** 
  1851   1851   ** <ul>
  1852         -** <li value="97"> TEXT
  1853         -** <li value="98"> BLOB
  1854         -** <li value="99"> NUMERIC
  1855         -** <li value="100"> INTEGER
  1856         -** <li value="101"> REAL
         1852  +** <li> P2=='A' &rarr; BLOB
         1853  +** <li> P2=='B' &rarr; TEXT
         1854  +** <li> P2=='C' &rarr; NUMERIC
         1855  +** <li> P2=='D' &rarr; INTEGER
         1856  +** <li> P2=='E' &rarr; REAL
  1857   1857   ** </ul>
  1858   1858   **
  1859   1859   ** A NULL value is not changed by this routine.  It remains NULL.
  1860   1860   */
  1861   1861   case OP_Cast: {                  /* in1 */
  1862   1862     assert( pOp->p2>=SQLITE_AFF_BLOB && pOp->p2<=SQLITE_AFF_REAL );
  1863   1863     testcase( pOp->p2==SQLITE_AFF_TEXT );
................................................................................
  2720   2720   }
  2721   2721   
  2722   2722   /* Opcode: Affinity P1 P2 * P4 *
  2723   2723   ** Synopsis: affinity(r[P1@P2])
  2724   2724   **
  2725   2725   ** Apply affinities to a range of P2 registers starting with P1.
  2726   2726   **
  2727         -** P4 is a string that is P2 characters long. The nth character of the
  2728         -** string indicates the column affinity that should be used for the nth
         2727  +** P4 is a string that is P2 characters long. The N-th character of the
         2728  +** string indicates the column affinity that should be used for the N-th
  2729   2729   ** memory cell in the range.
  2730   2730   */
  2731   2731   case OP_Affinity: {
  2732   2732     const char *zAffinity;   /* The affinity to be applied */
  2733   2733   
  2734   2734     zAffinity = pOp->p4.z;
  2735   2735     assert( zAffinity!=0 );
................................................................................
  2748   2748   /* Opcode: MakeRecord P1 P2 P3 P4 *
  2749   2749   ** Synopsis: r[P3]=mkrec(r[P1@P2])
  2750   2750   **
  2751   2751   ** Convert P2 registers beginning with P1 into the [record format]
  2752   2752   ** use as a data record in a database table or as a key
  2753   2753   ** in an index.  The OP_Column opcode can decode the record later.
  2754   2754   **
  2755         -** P4 may be a string that is P2 characters long.  The nth character of the
  2756         -** string indicates the column affinity that should be used for the nth
         2755  +** P4 may be a string that is P2 characters long.  The N-th character of the
         2756  +** string indicates the column affinity that should be used for the N-th
  2757   2757   ** field of the index key.
  2758   2758   **
  2759   2759   ** The mapping from character to affinity is given by the SQLITE_AFF_
  2760   2760   ** macros defined in sqliteInt.h.
  2761   2761   **
  2762   2762   ** If P4 is NULL then all index fields have the affinity BLOB.
  2763   2763   */
................................................................................
  3536   3536     testcase( pOp->p2 & OPFLAG_SEEKEQ );
  3537   3537   #endif
  3538   3538     sqlite3BtreeCursorHintFlags(pCur->uc.pCursor,
  3539   3539                                  (pOp->p5 & (OPFLAG_BULKCSR|OPFLAG_SEEKEQ)));
  3540   3540     if( rc ) goto abort_due_to_error;
  3541   3541     break;
  3542   3542   }
         3543  +
         3544  +/* Opcode: OpenDup P1 P2 * * *
         3545  +**
         3546  +** Open a new cursor P1 that points to the same ephemeral table as
         3547  +** cursor P2.  The P2 cursor must have been opened by a prior OP_OpenEphemeral
         3548  +** opcode.  Only ephemeral cursors may be duplicated.
         3549  +**
         3550  +** Duplicate ephemeral cursors are used for self-joins of materialized views.
         3551  +*/
         3552  +case OP_OpenDup: {
         3553  +  VdbeCursor *pOrig;    /* The original cursor to be duplicated */
         3554  +  VdbeCursor *pCx;      /* The new cursor */
         3555  +
         3556  +  pOrig = p->apCsr[pOp->p2];
         3557  +  assert( pOrig->pBtx!=0 );  /* Only ephemeral cursors can be duplicated */
         3558  +
         3559  +  pCx = allocateCursor(p, pOp->p1, pOrig->nField, -1, CURTYPE_BTREE);
         3560  +  if( pCx==0 ) goto no_mem;
         3561  +  pCx->nullRow = 1;
         3562  +  pCx->isEphemeral = 1;
         3563  +  pCx->pKeyInfo = pOrig->pKeyInfo;
         3564  +  pCx->isTable = pOrig->isTable;
         3565  +  rc = sqlite3BtreeCursor(pOrig->pBtx, MASTER_ROOT, BTREE_WRCSR,
         3566  +                          pCx->pKeyInfo, pCx->uc.pCursor);
         3567  +  /* The sqlite3BtreeCursor() routine can only fail for the first cursor
         3568  +  ** opened for a database.  Since there is already an open cursor when this
         3569  +  ** opcode is run, the sqlite3BtreeCursor() cannot fail */
         3570  +  assert( rc==SQLITE_OK );
         3571  +  break;
         3572  +}
         3573  +
  3543   3574   
  3544   3575   /* Opcode: OpenEphemeral P1 P2 * P4 P5
  3545   3576   ** Synopsis: nColumn=P2
  3546   3577   **
  3547   3578   ** Open a new cursor P1 to a transient table.
  3548   3579   ** The cursor is always opened read/write even if 
  3549   3580   ** the main database is read-only.  The ephemeral
................................................................................
  5745   5776     break;
  5746   5777   }
  5747   5778   #endif /* SQLITE_OMIT_INTEGRITY_CHECK */
  5748   5779   
  5749   5780   /* Opcode: RowSetAdd P1 P2 * * *
  5750   5781   ** Synopsis: rowset(P1)=r[P2]
  5751   5782   **
  5752         -** Insert the integer value held by register P2 into a boolean index
         5783  +** Insert the integer value held by register P2 into a RowSet object
  5753   5784   ** held in register P1.
  5754   5785   **
  5755   5786   ** An assertion fails if P2 is not an integer.
  5756   5787   */
  5757   5788   case OP_RowSetAdd: {       /* in1, in2 */
  5758   5789     pIn1 = &aMem[pOp->p1];
  5759   5790     pIn2 = &aMem[pOp->p2];
................................................................................
  5765   5796     sqlite3RowSetInsert(pIn1->u.pRowSet, pIn2->u.i);
  5766   5797     break;
  5767   5798   }
  5768   5799   
  5769   5800   /* Opcode: RowSetRead P1 P2 P3 * *
  5770   5801   ** Synopsis: r[P3]=rowset(P1)
  5771   5802   **
  5772         -** Extract the smallest value from boolean index P1 and put that value into
  5773         -** register P3.  Or, if boolean index P1 is initially empty, leave P3
         5803  +** Extract the smallest value from the RowSet object in P1
         5804  +** and put that value into register P3.
         5805  +** Or, if RowSet object P1 is initially empty, leave P3
  5774   5806   ** unchanged and jump to instruction P2.
  5775   5807   */
  5776   5808   case OP_RowSetRead: {       /* jump, in1, out3 */
  5777   5809     i64 val;
  5778   5810   
  5779   5811     pIn1 = &aMem[pOp->p1];
  5780   5812     if( (pIn1->flags & MEM_RowSet)==0 
................................................................................
  5797   5829   **
  5798   5830   ** Register P3 is assumed to hold a 64-bit integer value. If register P1
  5799   5831   ** contains a RowSet object and that RowSet object contains
  5800   5832   ** the value held in P3, jump to register P2. Otherwise, insert the
  5801   5833   ** integer in P3 into the RowSet and continue on to the
  5802   5834   ** next opcode.
  5803   5835   **
  5804         -** The RowSet object is optimized for the case where successive sets
  5805         -** of integers, where each set contains no duplicates. Each set
  5806         -** of values is identified by a unique P4 value. The first set
  5807         -** must have P4==0, the final set P4=-1.  P4 must be either -1 or
  5808         -** non-negative.  For non-negative values of P4 only the lower 4
  5809         -** bits are significant.
         5836  +** The RowSet object is optimized for the case where sets of integers
         5837  +** are inserted in distinct phases, which each set contains no duplicates.
         5838  +** Each set is identified by a unique P4 value. The first set
         5839  +** must have P4==0, the final set must have P4==-1, and for all other sets
         5840  +** must have P4>0.
  5810   5841   **
  5811   5842   ** This allows optimizations: (a) when P4==0 there is no need to test
  5812         -** the rowset object for P3, as it is guaranteed not to contain it,
         5843  +** the RowSet object for P3, as it is guaranteed not to contain it,
  5813   5844   ** (b) when P4==-1 there is no need to insert the value, as it will
  5814   5845   ** never be tested for, and (c) when a value that is part of set X is
  5815   5846   ** inserted, there is no need to search to see if the same value was
  5816   5847   ** previously inserted as part of set X (only if it was previously
  5817   5848   ** inserted as part of some other set).
  5818   5849   */
  5819   5850   case OP_RowSetTest: {                     /* jump, in1, in3 */

Changes to src/vdbeInt.h.

   283    283   ** Each auxiliary data pointer stored by a user defined function 
   284    284   ** implementation calling sqlite3_set_auxdata() is stored in an instance
   285    285   ** of this structure. All such structures associated with a single VM
   286    286   ** are stored in a linked list headed at Vdbe.pAuxData. All are destroyed
   287    287   ** when the VM is halted (if not before).
   288    288   */
   289    289   struct AuxData {
   290         -  int iOp;                        /* Instruction number of OP_Function opcode */
   291         -  int iArg;                       /* Index of function argument. */
          290  +  int iAuxOp;                     /* Instruction number of OP_Function opcode */
          291  +  int iAuxArg;                    /* Index of function argument. */
   292    292     void *pAux;                     /* Aux data pointer */
   293         -  void (*xDelete)(void *);        /* Destructor for the aux data */
   294         -  AuxData *pNext;                 /* Next element in list */
          293  +  void (*xDeleteAux)(void*);      /* Destructor for the aux data */
          294  +  AuxData *pNextAux;              /* Next element in list */
   295    295   };
   296    296   
   297    297   /*
   298    298   ** The "context" argument for an installable function.  A pointer to an
   299    299   ** instance of this structure is the first argument to the routines used
   300    300   ** implement the SQL functions.
   301    301   **

Changes to src/vdbeapi.c.

   816    816       return (void*)p->pMem->z;
   817    817     }
   818    818   }
   819    819   
   820    820   /*
   821    821   ** Return the auxiliary data pointer, if any, for the iArg'th argument to
   822    822   ** the user-function defined by pCtx.
          823  +**
          824  +** The left-most argument is 0.
          825  +**
          826  +** Undocumented behavior:  If iArg is negative then access a cache of
          827  +** auxiliary data pointers that is available to all functions within a
          828  +** single prepared statement.  The iArg values must match.
   823    829   */
   824    830   void *sqlite3_get_auxdata(sqlite3_context *pCtx, int iArg){
   825    831     AuxData *pAuxData;
   826    832   
   827    833     assert( sqlite3_mutex_held(pCtx->pOut->db->mutex) );
   828    834   #if SQLITE_ENABLE_STAT3_OR_STAT4
   829    835     if( pCtx->pVdbe==0 ) return 0;
   830    836   #else
   831    837     assert( pCtx->pVdbe!=0 );
   832    838   #endif
   833         -  for(pAuxData=pCtx->pVdbe->pAuxData; pAuxData; pAuxData=pAuxData->pNext){
   834         -    if( pAuxData->iOp==pCtx->iOp && pAuxData->iArg==iArg ) break;
          839  +  for(pAuxData=pCtx->pVdbe->pAuxData; pAuxData; pAuxData=pAuxData->pNextAux){
          840  +    if(  pAuxData->iAuxArg==iArg && (pAuxData->iAuxOp==pCtx->iOp || iArg<0) ){
          841  +      return pAuxData->pAux;
          842  +    }
   835    843     }
   836         -
   837         -  return (pAuxData ? pAuxData->pAux : 0);
          844  +  return 0;
   838    845   }
   839    846   
   840    847   /*
   841    848   ** Set the auxiliary data pointer and delete function, for the iArg'th
   842    849   ** argument to the user-function defined by pCtx. Any previous value is
   843    850   ** deleted by calling the delete function specified when it was set.
          851  +**
          852  +** The left-most argument is 0.
          853  +**
          854  +** Undocumented behavior:  If iArg is negative then make the data available
          855  +** to all functions within the current prepared statement using iArg as an
          856  +** access code.
   844    857   */
   845    858   void sqlite3_set_auxdata(
   846    859     sqlite3_context *pCtx, 
   847    860     int iArg, 
   848    861     void *pAux, 
   849    862     void (*xDelete)(void*)
   850    863   ){
   851    864     AuxData *pAuxData;
   852    865     Vdbe *pVdbe = pCtx->pVdbe;
   853    866   
   854    867     assert( sqlite3_mutex_held(pCtx->pOut->db->mutex) );
   855         -  if( iArg<0 ) goto failed;
   856    868   #ifdef SQLITE_ENABLE_STAT3_OR_STAT4
   857    869     if( pVdbe==0 ) goto failed;
   858    870   #else
   859    871     assert( pVdbe!=0 );
   860    872   #endif
   861    873   
   862         -  for(pAuxData=pVdbe->pAuxData; pAuxData; pAuxData=pAuxData->pNext){
   863         -    if( pAuxData->iOp==pCtx->iOp && pAuxData->iArg==iArg ) break;
          874  +  for(pAuxData=pVdbe->pAuxData; pAuxData; pAuxData=pAuxData->pNextAux){
          875  +    if( pAuxData->iAuxArg==iArg && (pAuxData->iAuxOp==pCtx->iOp || iArg<0) ){
          876  +      break;
          877  +    }
   864    878     }
   865    879     if( pAuxData==0 ){
   866    880       pAuxData = sqlite3DbMallocZero(pVdbe->db, sizeof(AuxData));
   867    881       if( !pAuxData ) goto failed;
   868         -    pAuxData->iOp = pCtx->iOp;
   869         -    pAuxData->iArg = iArg;
   870         -    pAuxData->pNext = pVdbe->pAuxData;
          882  +    pAuxData->iAuxOp = pCtx->iOp;
          883  +    pAuxData->iAuxArg = iArg;
          884  +    pAuxData->pNextAux = pVdbe->pAuxData;
   871    885       pVdbe->pAuxData = pAuxData;
   872    886       if( pCtx->fErrorOrAux==0 ){
   873    887         pCtx->isError = 0;
   874    888         pCtx->fErrorOrAux = 1;
   875    889       }
   876         -  }else if( pAuxData->xDelete ){
   877         -    pAuxData->xDelete(pAuxData->pAux);
          890  +  }else if( pAuxData->xDeleteAux ){
          891  +    pAuxData->xDeleteAux(pAuxData->pAux);
   878    892     }
   879    893   
   880    894     pAuxData->pAux = pAux;
   881         -  pAuxData->xDelete = xDelete;
          895  +  pAuxData->xDeleteAux = xDelete;
   882    896     return;
   883    897   
   884    898   failed:
   885    899     if( xDelete ){
   886    900       xDelete(pAux);
   887    901     }
   888    902   }

Changes to src/vdbeaux.c.

  2031   2031     assert( pCx->pBtx==0 || pCx->eCurType==CURTYPE_BTREE );
  2032   2032     switch( pCx->eCurType ){
  2033   2033       case CURTYPE_SORTER: {
  2034   2034         sqlite3VdbeSorterClose(p->db, pCx);
  2035   2035         break;
  2036   2036       }
  2037   2037       case CURTYPE_BTREE: {
  2038         -      if( pCx->pBtx ){
  2039         -        sqlite3BtreeClose(pCx->pBtx);
         2038  +      if( pCx->isEphemeral ){
         2039  +        if( pCx->pBtx ) sqlite3BtreeClose(pCx->pBtx);
  2040   2040           /* The pCx->pCursor will be close automatically, if it exists, by
  2041   2041           ** the call above. */
  2042   2042         }else{
  2043   2043           assert( pCx->uc.pCursor!=0 );
  2044   2044           sqlite3BtreeCloseCursor(pCx->uc.pCursor);
  2045   2045         }
  2046   2046         break;
................................................................................
  2964   2964   **    * the corresponding bit in argument mask is clear (where the first
  2965   2965   **      function parameter corresponds to bit 0 etc.).
  2966   2966   */
  2967   2967   void sqlite3VdbeDeleteAuxData(sqlite3 *db, AuxData **pp, int iOp, int mask){
  2968   2968     while( *pp ){
  2969   2969       AuxData *pAux = *pp;
  2970   2970       if( (iOp<0)
  2971         -     || (pAux->iOp==iOp && (pAux->iArg>31 || !(mask & MASKBIT32(pAux->iArg))))
         2971  +     || (pAux->iAuxOp==iOp
         2972  +          && pAux->iAuxArg>=0
         2973  +          && (pAux->iAuxArg>31 || !(mask & MASKBIT32(pAux->iAuxArg))))
  2972   2974       ){
  2973         -      testcase( pAux->iArg==31 );
  2974         -      if( pAux->xDelete ){
  2975         -        pAux->xDelete(pAux->pAux);
         2975  +      testcase( pAux->iAuxArg==31 );
         2976  +      if( pAux->xDeleteAux ){
         2977  +        pAux->xDeleteAux(pAux->pAux);
  2976   2978         }
  2977         -      *pp = pAux->pNext;
         2979  +      *pp = pAux->pNextAux;
  2978   2980         sqlite3DbFree(db, pAux);
  2979   2981       }else{
  2980         -      pp= &pAux->pNext;
         2982  +      pp= &pAux->pNextAux;
  2981   2983       }
  2982   2984     }
  2983   2985   }
  2984   2986   
  2985   2987   /*
  2986   2988   ** Free all memory associated with the Vdbe passed as the second argument,
  2987   2989   ** except for object itself, which is preserved.

Changes to src/wherecode.c.

  1125   1125     Vdbe *v;                        /* The prepared stmt under constructions */
  1126   1126     struct SrcList_item *pTabItem;  /* FROM clause term being coded */
  1127   1127     int addrBrk;                    /* Jump here to break out of the loop */
  1128   1128     int addrHalt;                   /* addrBrk for the outermost loop */
  1129   1129     int addrCont;                   /* Jump here to continue with next cycle */
  1130   1130     int iRowidReg = 0;        /* Rowid is stored in this register, if not zero */
  1131   1131     int iReleaseReg = 0;      /* Temp register to free before returning */
         1132  +  Index *pIdx = 0;          /* Index used by loop (if any) */
         1133  +  int loopAgain;            /* True if constraint generator loop should repeat */
  1132   1134   
  1133   1135     pParse = pWInfo->pParse;
  1134   1136     v = pParse->pVdbe;
  1135   1137     pWC = &pWInfo->sWC;
  1136   1138     db = pParse->db;
  1137   1139     pLevel = &pWInfo->a[iLevel];
  1138   1140     pLoop = pLevel->pWLoop;
................................................................................
  1450   1452       int regBase;                 /* Base register holding constraint values */
  1451   1453       WhereTerm *pRangeStart = 0;  /* Inequality constraint at range start */
  1452   1454       WhereTerm *pRangeEnd = 0;    /* Inequality constraint at range end */
  1453   1455       int startEq;                 /* True if range start uses ==, >= or <= */
  1454   1456       int endEq;                   /* True if range end uses ==, >= or <= */
  1455   1457       int start_constraints;       /* Start of range is constrained */
  1456   1458       int nConstraint;             /* Number of constraint terms */
  1457         -    Index *pIdx;                 /* The index we will be using */
  1458   1459       int iIdxCur;                 /* The VDBE cursor for the index */
  1459   1460       int nExtraReg = 0;           /* Number of extra registers needed */
  1460   1461       int op;                      /* Instruction opcode */
  1461   1462       char *zStartAff;             /* Affinity for start of range constraint */
  1462   1463       char *zEndAff = 0;           /* Affinity for end of range constraint */
  1463   1464       u8 bSeekPastNull = 0;        /* True to seek past initial nulls */
  1464   1465       u8 bStopAtNull = 0;          /* Add condition to terminate at NULLs */
................................................................................
  1701   1702       pLevel->p1 = iIdxCur;
  1702   1703       pLevel->p3 = (pLoop->wsFlags&WHERE_UNQ_WANTED)!=0 ? 1:0;
  1703   1704       if( (pLoop->wsFlags & WHERE_CONSTRAINT)==0 ){
  1704   1705         pLevel->p5 = SQLITE_STMTSTATUS_FULLSCAN_STEP;
  1705   1706       }else{
  1706   1707         assert( pLevel->p5==0 );
  1707   1708       }
         1709  +    if( omitTable ) pIdx = 0;
  1708   1710     }else
  1709   1711   
  1710   1712   #ifndef SQLITE_OMIT_OR_OPTIMIZATION
  1711   1713     if( pLoop->wsFlags & WHERE_MULTI_OR ){
  1712   1714       /* Case 5:  Two or more separately indexed terms connected by OR
  1713   1715       **
  1714   1716       ** Example:
................................................................................
  2018   2020   
  2019   2021   #ifdef SQLITE_ENABLE_STMT_SCANSTATUS
  2020   2022     pLevel->addrVisit = sqlite3VdbeCurrentAddr(v);
  2021   2023   #endif
  2022   2024   
  2023   2025     /* Insert code to test every subexpression that can be completely
  2024   2026     ** computed using the current set of tables.
         2027  +  **
         2028  +  ** This loop may run either once (pIdx==0) or twice (pIdx!=0). If
         2029  +  ** it is run twice, then the first iteration codes those sub-expressions
         2030  +  ** that can be computed using columns from pIdx only (without seeking
         2031  +  ** the main table cursor). 
  2025   2032     */
  2026         -  for(pTerm=pWC->a, j=pWC->nTerm; j>0; j--, pTerm++){
  2027         -    Expr *pE;
  2028         -    int skipLikeAddr = 0;
  2029         -    testcase( pTerm->wtFlags & TERM_VIRTUAL );
  2030         -    testcase( pTerm->wtFlags & TERM_CODED );
  2031         -    if( pTerm->wtFlags & (TERM_VIRTUAL|TERM_CODED) ) continue;
  2032         -    if( (pTerm->prereqAll & pLevel->notReady)!=0 ){
  2033         -      testcase( pWInfo->untestedTerms==0
  2034         -               && (pWInfo->wctrlFlags & WHERE_OR_SUBCLAUSE)!=0 );
  2035         -      pWInfo->untestedTerms = 1;
  2036         -      continue;
  2037         -    }
  2038         -    pE = pTerm->pExpr;
  2039         -    assert( pE!=0 );
  2040         -    if( pLevel->iLeftJoin && !ExprHasProperty(pE, EP_FromJoin) ){
  2041         -      continue;
  2042         -    }
  2043         -    if( pTerm->wtFlags & TERM_LIKECOND ){
  2044         -      /* If the TERM_LIKECOND flag is set, that means that the range search
  2045         -      ** is sufficient to guarantee that the LIKE operator is true, so we
  2046         -      ** can skip the call to the like(A,B) function.  But this only works
  2047         -      ** for strings.  So do not skip the call to the function on the pass
  2048         -      ** that compares BLOBs. */
         2033  +  do{
         2034  +    loopAgain = 0;
         2035  +    for(pTerm=pWC->a, j=pWC->nTerm; j>0; j--, pTerm++){
         2036  +      Expr *pE;
         2037  +      int skipLikeAddr = 0;
         2038  +      testcase( pTerm->wtFlags & TERM_VIRTUAL );
         2039  +      testcase( pTerm->wtFlags & TERM_CODED );
         2040  +      if( pTerm->wtFlags & (TERM_VIRTUAL|TERM_CODED) ) continue;
         2041  +      if( (pTerm->prereqAll & pLevel->notReady)!=0 ){
         2042  +        testcase( pWInfo->untestedTerms==0
         2043  +            && (pWInfo->wctrlFlags & WHERE_OR_SUBCLAUSE)!=0 );
         2044  +        pWInfo->untestedTerms = 1;
         2045  +        continue;
         2046  +      }
         2047  +      pE = pTerm->pExpr;
         2048  +      assert( pE!=0 );
         2049  +      if( pLevel->iLeftJoin && !ExprHasProperty(pE, EP_FromJoin) ){
         2050  +        continue;
         2051  +      }
         2052  +      if( pIdx && !sqlite3ExprCoveredByIndex(pE, pLevel->iTabCur, pIdx) ){
         2053  +        loopAgain = 1;
         2054  +        continue;
         2055  +      }
         2056  +      if( pTerm->wtFlags & TERM_LIKECOND ){
         2057  +        /* If the TERM_LIKECOND flag is set, that means that the range search
         2058  +        ** is sufficient to guarantee that the LIKE operator is true, so we
         2059  +        ** can skip the call to the like(A,B) function.  But this only works
         2060  +        ** for strings.  So do not skip the call to the function on the pass
         2061  +        ** that compares BLOBs. */
  2049   2062   #ifdef SQLITE_LIKE_DOESNT_MATCH_BLOBS
  2050         -      continue;
         2063  +        continue;
  2051   2064   #else
  2052         -      u32 x = pLevel->iLikeRepCntr;
  2053         -      assert( x>0 );
  2054         -      skipLikeAddr = sqlite3VdbeAddOp1(v, (x&1)? OP_IfNot : OP_If, (int)(x>>1));
  2055         -      VdbeCoverage(v);
         2065  +        u32 x = pLevel->iLikeRepCntr;
         2066  +        assert( x>0 );
         2067  +        skipLikeAddr = sqlite3VdbeAddOp1(v, (x&1)?OP_IfNot:OP_If, (int)(x>>1));
         2068  +        VdbeCoverage(v);
  2056   2069   #endif
         2070  +      }
         2071  +      sqlite3ExprIfFalse(pParse, pE, addrCont, SQLITE_JUMPIFNULL);
         2072  +      if( skipLikeAddr ) sqlite3VdbeJumpHere(v, skipLikeAddr);
         2073  +      pTerm->wtFlags |= TERM_CODED;
  2057   2074       }
  2058         -    sqlite3ExprIfFalse(pParse, pE, addrCont, SQLITE_JUMPIFNULL);
  2059         -    if( skipLikeAddr ) sqlite3VdbeJumpHere(v, skipLikeAddr);
  2060         -    pTerm->wtFlags |= TERM_CODED;
  2061         -  }
         2075  +    pIdx = 0;
         2076  +  }while( loopAgain );
  2062   2077   
  2063   2078     /* Insert code to test for implied constraints based on transitivity
  2064   2079     ** of the "==" operator.
  2065   2080     **
  2066   2081     ** Example: If the WHERE clause contains "t1.a=t2.b" and "t2.b=123"
  2067   2082     ** and we are coding the t1 loop and the t2 loop has not yet coded,
  2068   2083     ** then we cannot use the "t1.a=t2.b" constraint, but we can code

Changes to test/auth.test.

    32     32       db authorizer ::auth
    33     33     }
    34     34   }
    35     35   
    36     36   do_test auth-1.1.1 {
    37     37     db close
    38     38     set ::DB [sqlite3 db test.db]
           39  +  proc authx {code arg1 arg2 arg3 arg4 args} {return SQLITE_DENY}
    39     40     proc auth {code arg1 arg2 arg3 arg4 args} {
    40     41       if {$code=="SQLITE_INSERT" && $arg1=="sqlite_master"} {
    41     42         return SQLITE_DENY
    42     43       }
    43     44       return SQLITE_OK
    44     45     }
           46  +  db authorizer ::authx
           47  +  # EVIDENCE-OF: R-03993-24285 Only a single authorizer can be in place on
           48  +  # a database connection at a time. Each call to sqlite3_set_authorizer
           49  +  # overrides the previous call.
           50  +  #
           51  +  # The authx authorizer above is overridden by the auth authorizer below
           52  +  # authx is never invoked.
    45     53     db authorizer ::auth
    46     54     catchsql {CREATE TABLE t1(a,b,c)}
    47     55   } {1 {not authorized}}
    48     56   do_test auth-1.1.2 {
    49     57     db errorcode
    50     58   } {23}
    51     59   do_test auth-1.1.3 {
................................................................................
    56     64     catchsql {
    57     65       SELECT x;
    58     66     }
    59     67   } {1 {no such column: x}}
    60     68   do_test auth-1.2 {
    61     69     execsql {SELECT name FROM sqlite_master}
    62     70   } {}
           71  +# EVIDENCE-OF: R-04452-49349 When the callback returns SQLITE_DENY, the
           72  +# sqlite3_prepare_v2() or equivalent call that triggered the authorizer
           73  +# will fail with an error message explaining that access is denied.
    63     74   do_test auth-1.3.1 {
    64     75     proc auth {code arg1 arg2 arg3 arg4 args} {
    65     76       if {$code=="SQLITE_CREATE_TABLE"} {
    66     77         set ::authargs [list $arg1 $arg2 $arg3 $arg4]
    67     78         return SQLITE_DENY
    68     79       }
    69     80       return SQLITE_OK
................................................................................
   308    319   ifcapable attach {
   309    320     do_test auth-1.35.2 {
   310    321       execsql {ATTACH DATABASE 'test.db' AS two}
   311    322       catchsql {SELECT * FROM two.t2}
   312    323     } {1 {access to two.t2.b is prohibited}}
   313    324     execsql {DETACH DATABASE two}
   314    325   }
          326  +# EVIDENCE-OF: R-38392-49970 If the action code is SQLITE_READ and the
          327  +# callback returns SQLITE_IGNORE then the prepared statement statement
          328  +# is constructed to substitute a NULL value in place of the table column
          329  +# that would have been read if SQLITE_OK had been returned.
   315    330   do_test auth-1.36 {
   316    331     proc auth {code arg1 arg2 arg3 arg4 args} {
   317    332       if {$code=="SQLITE_READ" && $arg1=="t2" && $arg2=="b"} {
   318    333         return SQLITE_IGNORE
   319    334       }
   320    335       return SQLITE_OK
   321    336     }
................................................................................
  1602   1617   do_test auth-1.247 {
  1603   1618     catchsql {END TRANSACTION}
  1604   1619   } {1 {not authorized}}
  1605   1620   do_test auth-1.248 {
  1606   1621     set ::authargs
  1607   1622   } {COMMIT {} {} {}}
  1608   1623   do_test auth-1.249 {
         1624  +  # EVIDENCE-OF: R-52112-44167 Disable the authorizer by installing a NULL
         1625  +  # callback.
  1609   1626     db authorizer {}
  1610   1627     catchsql {ROLLBACK}
  1611   1628   } {0 {}}
  1612   1629   do_test auth-1.250 {
  1613   1630     execsql {SELECT * FROM t2}
  1614   1631   } {11 2 33 7 8 9}
  1615   1632   
................................................................................
  2474   2491     set ::authargs
  2475   2492   } [list                          \
  2476   2493     SQLITE_SELECT {} {} {} {}      \
  2477   2494     SQLITE_READ t7 a main {}       \
  2478   2495     SQLITE_READ t7 c main {}       \
  2479   2496   ]
  2480   2497   
         2498  +# If a table is referenced but no columns are read from the table,
         2499  +# that causes a single SQLITE_READ authorization with a NULL column
         2500  +# name.
         2501  +#
         2502  +# EVIDENCE-OF: R-31520-16302 When a table is referenced by a SELECT but
         2503  +# no column values are extracted from that table (for example in a query
         2504  +# like "SELECT count(*) FROM tab") then the SQLITE_READ authorizer
         2505  +# callback is invoked once for that table with a column name that is an
         2506  +# empty string.
         2507  +#
         2508  +set ::authargs [list]
         2509  +do_test auth-8.1 {
         2510  +  execsql {SELECT count(*) FROM t7}
         2511  +  set ::authargs
         2512  +} [list \
         2513  +  SQLITE_SELECT {} {} {} {}          \
         2514  +  SQLITE_FUNCTION {} count {} {}     \
         2515  +  SQLITE_READ t7 {} {} {}            \
         2516  +  ]
         2517  +set ::authargs [list]
         2518  +
         2519  +do_test auth-8.2 {
         2520  +  execsql {SELECT t6.a FROM t6, t7}
         2521  +  set ::authargs
         2522  +} [list \
         2523  +  SQLITE_SELECT {} {} {} {}          \
         2524  +  SQLITE_READ t6 a main {}           \
         2525  +  SQLITE_READ t7 {} {} {}            \
         2526  +  ]
  2481   2527   
  2482   2528   rename proc {}
  2483   2529   rename proc_real proc
  2484   2530   finish_test

Changes to test/auth3.test.

    49     49       INSERT INTO t1 VALUES(4, 5, 6);
    50     50     }
    51     51   } {}
    52     52   do_test auth3.1.2 {
    53     53     set ::authcode SQLITE_DENY
    54     54     catchsql { DELETE FROM t1 }
    55     55   } {1 {not authorized}}
           56  +# EVIDENCE-OF: R-64962-58611 If the authorizer callback returns any
           57  +# value other than SQLITE_IGNORE, SQLITE_OK, or SQLITE_DENY then the
           58  +# sqlite3_prepare_v2() or equivalent call that triggered the authorizer
           59  +# will fail with an error message.
    56     60   do_test auth3.1.3 {
    57     61     set ::authcode SQLITE_INVALID
    58     62     catchsql { DELETE FROM t1 }
    59     63   } {1 {authorizer malfunction}}
    60     64   do_test auth3.1.4 {
    61     65     execsql { SELECT * FROM t1 }
    62     66   } {1 2 3 4 5 6}

Added test/cachespill.test.

            1  +# 2017 April 26
            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  +#
           12  +
           13  +set testdir [file dirname $argv0]
           14  +source $testdir/tester.tcl
           15  +set testprefix cachespill
           16  +
           17  +ifcapable !pager_pragmas {
           18  +  finish_test
           19  +  return
           20  +}
           21  +
           22  +#-------------------------------------------------------------------------
           23  +# Test that "PRAGMA cache_spill = 0" completely disables cache spilling.
           24  +#
           25  +do_execsql_test 1.1 {
           26  +  PRAGMA auto_vacuum = 0;
           27  +  PRAGMA page_size = 1024;
           28  +  PRAGMA cache_size = 100;
           29  +  CREATE TABLE t1(a);
           30  +}
           31  +
           32  +do_test 1.2 {
           33  +  file size test.db
           34  +} {2048}
           35  +
           36  +do_test 1.3 {
           37  +  execsql {
           38  +    BEGIN;
           39  +      WITH s(i) AS (
           40  +        SELECT 1 UNION ALL SELECT i+1 FROM s WHERE i<200
           41  +      ) INSERT INTO t1 SELECT randomblob(900) FROM s;
           42  +  }
           43  +  expr {[file size test.db] > 50000}
           44  +} {1}
           45  +
           46  +do_test 1.4 {
           47  +  execsql ROLLBACK
           48  +  file size test.db
           49  +} {2048}
           50  +
           51  +do_test 1.5 {
           52  +  execsql {
           53  +    PRAGMA cache_spill = 0;
           54  +    BEGIN;
           55  +      WITH s(i) AS (
           56  +        SELECT 1 UNION ALL SELECT i+1 FROM s WHERE i<200
           57  +      ) INSERT INTO t1 SELECT randomblob(900) FROM s;
           58  +  }
           59  +  file size test.db
           60  +} {2048}
           61  +
           62  +do_test 1.5 {
           63  +  execsql {
           64  +    ROLLBACK;
           65  +    PRAGMA cache_spill = 1;
           66  +    BEGIN;
           67  +      WITH s(i) AS (
           68  +        SELECT 1 UNION ALL SELECT i+1 FROM s WHERE i<200
           69  +      ) INSERT INTO t1 SELECT randomblob(900) FROM s;
           70  +  }
           71  +  expr {[file size test.db] > 50000}
           72  +} {1}
           73  +
           74  +do_execsql_test 1.6 { ROLLBACK }
           75  +
           76  +
           77  +finish_test

Changes to test/conflict3.test.

    15     15   #
    16     16   # This file focuses on making sure that combinations of REPLACE,
    17     17   # IGNORE, and FAIL conflict resolution play well together.
    18     18   #
    19     19   
    20     20   set testdir [file dirname $argv0]
    21     21   source $testdir/tester.tcl
           22  +set testprefix conflict3
    22     23   
    23     24   ifcapable !conflict {
    24     25     finish_test
    25     26     return
    26     27   }
    27     28   
    28         -do_execsql_test conflict-1.1 {
           29  +do_execsql_test 1.1 {
    29     30     CREATE TABLE t1(
    30     31       a INTEGER PRIMARY KEY ON CONFLICT REPLACE, 
    31     32       b UNIQUE ON CONFLICT IGNORE,
    32     33       c UNIQUE ON CONFLICT FAIL
    33     34     );
    34     35     INSERT INTO t1(a,b,c) VALUES(1,2,3), (2,3,4);
    35     36     SELECT a,b,c FROM t1 ORDER BY a;
    36     37   } {1 2 3 2 3 4}
    37     38   
    38     39   # Insert a row that conflicts on column B.  The insert should be ignored.
    39     40   #
    40         -do_execsql_test conflict-1.2 {
           41  +do_execsql_test 1.2 {
    41     42     INSERT INTO t1(a,b,c) VALUES(3,2,5);
    42     43     SELECT a,b,c FROM t1 ORDER BY a;
    43     44   } {1 2 3 2 3 4}
    44     45   
    45     46   # Insert two rows where the second conflicts on C.  The first row show go
    46     47   # and and then there should be a constraint error.
    47     48   #
    48         -do_test conflict-1.3 {
           49  +do_test 1.3 {
    49     50     catchsql {INSERT INTO t1(a,b,c) VALUES(4,5,6), (5,6,4);}
    50     51   } {1 {UNIQUE constraint failed: t1.c}}
    51         -do_execsql_test conflict-1.4 {
           52  +do_execsql_test 1.4 {
    52     53     SELECT a,b,c FROM t1 ORDER BY a;
    53     54   } {1 2 3 2 3 4 4 5 6}
    54     55   
    55     56   # Replete the tests above, but this time on a table non-INTEGER primary key.
    56     57   #
    57         -do_execsql_test conflict-2.1 {
           58  +do_execsql_test 2.1 {
    58     59     DROP TABLE t1;
    59     60     CREATE TABLE t1(
    60     61       a INT PRIMARY KEY ON CONFLICT REPLACE, 
    61     62       b UNIQUE ON CONFLICT IGNORE,
    62     63       c UNIQUE ON CONFLICT FAIL
    63     64     );
    64     65     INSERT INTO t1(a,b,c) VALUES(1,2,3), (2,3,4);
    65     66     SELECT a,b,c FROM t1 ORDER BY a;
    66     67   } {1 2 3 2 3 4}
    67     68   
    68     69   # Insert a row that conflicts on column B.  The insert should be ignored.
    69     70   #
    70         -do_execsql_test conflict-2.2 {
           71  +do_execsql_test 2.2 {
    71     72     INSERT INTO t1(a,b,c) VALUES(3,2,5);
    72     73     SELECT a,b,c FROM t1 ORDER BY a;
    73     74   } {1 2 3 2 3 4}
    74     75   
    75     76   # Insert two rows where the second conflicts on C.  The first row show go
    76     77   # and and then there should be a constraint error.
    77     78   #
    78         -do_test conflict-2.3 {
           79  +do_test 2.3 {
    79     80     catchsql {INSERT INTO t1(a,b,c) VALUES(4,5,6), (5,6,4);}
    80     81   } {1 {UNIQUE constraint failed: t1.c}}
    81         -do_execsql_test conflict-2.4 {
           82  +do_execsql_test 2.4 {
    82     83     SELECT a,b,c FROM t1 ORDER BY a;
    83     84   } {1 2 3 2 3 4 4 5 6}
    84     85   
    85     86   # Replete again on a WITHOUT ROWID table.
    86     87   #
    87         -do_execsql_test conflict-3.1 {
           88  +do_execsql_test 3.1 {
    88     89     DROP TABLE t1;
    89     90     CREATE TABLE t1(
    90     91       a INT PRIMARY KEY ON CONFLICT REPLACE, 
    91     92       b UNIQUE ON CONFLICT IGNORE,
    92     93       c UNIQUE ON CONFLICT FAIL
    93     94     ) WITHOUT ROWID;
    94     95     INSERT INTO t1(a,b,c) VALUES(1,2,3), (2,3,4);
    95     96     SELECT a,b,c FROM t1 ORDER BY a;
    96     97   } {1 2 3 2 3 4}
    97     98   
    98     99   # Insert a row that conflicts on column B.  The insert should be ignored.
    99    100   #
   100         -do_execsql_test conflict-3.2 {
          101  +do_execsql_test 3.2 {
   101    102     INSERT INTO t1(a,b,c) VALUES(3,2,5);
   102    103     SELECT a,b,c FROM t1 ORDER BY a;
   103    104   } {1 2 3 2 3 4}
   104    105   
   105    106   # Insert two rows where the second conflicts on C.  The first row show go
   106    107   # and and then there should be a constraint error.
   107    108   #
   108         -do_test conflict-3.3 {
          109  +do_test 3.3 {
   109    110     catchsql {INSERT INTO t1(a,b,c) VALUES(4,5,6), (5,6,4);}
   110    111   } {1 {UNIQUE constraint failed: t1.c}}
   111         -do_execsql_test conflict-3.4 {
          112  +do_execsql_test 3.4 {
   112    113     SELECT a,b,c FROM t1 ORDER BY a;
   113    114   } {1 2 3 2 3 4 4 5 6}
   114    115   
   115    116   # Arrange the table rows in a different order and repeat.
   116    117   #
   117         -do_execsql_test conflict-4.1 {
          118  +do_execsql_test 4.1 {
   118    119     DROP TABLE t1;
   119    120     CREATE TABLE t1(
   120    121       b UNIQUE ON CONFLICT IGNORE,
   121    122       c UNIQUE ON CONFLICT FAIL,
   122    123       a INT PRIMARY KEY ON CONFLICT REPLACE
   123    124     ) WITHOUT ROWID;
   124    125     INSERT INTO t1(a,b,c) VALUES(1,2,3), (2,3,4);
   125    126     SELECT a,b,c FROM t1 ORDER BY a;
   126    127   } {1 2 3 2 3 4}
   127    128   
   128    129   # Insert a row that conflicts on column B.  The insert should be ignored.
   129    130   #
   130         -do_execsql_test conflict-4.2 {
          131  +do_execsql_test 4.2 {
   131    132     INSERT INTO t1(a,b,c) VALUES(3,2,5);
   132    133     SELECT a,b,c FROM t1 ORDER BY a;
   133    134   } {1 2 3 2 3 4}
   134    135   
   135    136   # Insert two rows where the second conflicts on C.  The first row show go
   136    137   # and and then there should be a constraint error.
   137    138   #
   138         -do_test conflict-4.3 {
          139  +do_test 4.3 {
   139    140     catchsql {INSERT INTO t1(a,b,c) VALUES(4,5,6), (5,6,4);}
   140    141   } {1 {UNIQUE constraint failed: t1.c}}
   141         -do_execsql_test conflict-4.4 {
          142  +do_execsql_test 4.4 {
   142    143     SELECT a,b,c FROM t1 ORDER BY a;
   143    144   } {1 2 3 2 3 4 4 5 6}
   144    145   
   145    146   # Arrange the table rows in a different order and repeat.
   146    147   #
   147         -do_execsql_test conflict-5.1 {
          148  +do_execsql_test 5.1 {
   148    149     DROP TABLE t1;
   149    150     CREATE TABLE t1(
   150    151       b UNIQUE ON CONFLICT IGNORE,
   151    152       a INT PRIMARY KEY ON CONFLICT REPLACE,
   152    153       c UNIQUE ON CONFLICT FAIL
   153    154     );
   154    155     INSERT INTO t1(a,b,c) VALUES(1,2,3), (2,3,4);
   155    156     SELECT a,b,c FROM t1 ORDER BY a;
   156    157   } {1 2 3 2 3 4}
   157    158   
   158    159   # Insert a row that conflicts on column B.  The insert should be ignored.
   159    160   #
   160         -do_execsql_test conflict-5.2 {
          161  +do_execsql_test 5.2 {
   161    162     INSERT INTO t1(a,b,c) VALUES(3,2,5);
   162    163     SELECT a,b,c FROM t1 ORDER BY a;
   163    164   } {1 2 3 2 3 4}
   164    165   
   165    166   # Insert two rows where the second conflicts on C.  The first row show go
   166    167   # and and then there should be a constraint error.
   167    168   #
   168         -do_test conflict-5.3 {
          169  +do_test 5.3 {
   169    170     catchsql {INSERT INTO t1(a,b,c) VALUES(4,5,6), (5,6,4);}
   170    171   } {1 {UNIQUE constraint failed: t1.c}}
   171         -do_execsql_test conflict-5.4 {
          172  +do_execsql_test 5.4 {
   172    173     SELECT a,b,c FROM t1 ORDER BY a;
   173    174   } {1 2 3 2 3 4 4 5 6}
   174    175   
   175    176   # Arrange the table rows in a different order and repeat.
   176    177   #
   177         -do_execsql_test conflict-6.1 {
          178  +do_execsql_test 6.1 {
   178    179     DROP TABLE t1;
   179    180     CREATE TABLE t1(
   180    181       c UNIQUE ON CONFLICT FAIL,
   181    182       a INT PRIMARY KEY ON CONFLICT REPLACE,
   182    183       b UNIQUE ON CONFLICT IGNORE
   183    184     );
   184    185     INSERT INTO t1(a,b,c) VALUES(1,2,3), (2,3,4);
   185    186     SELECT a,b,c FROM t1 ORDER BY a;
   186    187   } {1 2 3 2 3 4}
   187    188   
   188    189   # Insert a row that conflicts on column B.  The insert should be ignored.
   189    190   #
   190         -do_execsql_test conflict-6.2 {
          191  +do_execsql_test 6.2 {
   191    192     INSERT INTO t1(a,b,c) VALUES(3,2,5);
   192    193     SELECT a,b,c FROM t1 ORDER BY a;
   193    194   } {1 2 3 2 3 4}
   194    195   
   195    196   # Insert two rows where the second conflicts on C.  The first row show go
   196    197   # and and then there should be a constraint error.
   197    198   #
   198         -do_test conflict-6.3 {
          199  +do_test 6.3 {
   199    200     catchsql {INSERT INTO t1(a,b,c) VALUES(4,5,6), (5,6,4);}
   200    201   } {1 {UNIQUE constraint failed: t1.c}}
   201         -do_execsql_test conflict-6.4 {
          202  +do_execsql_test 6.4 {
   202    203     SELECT a,b,c FROM t1 ORDER BY a;
   203    204   } {1 2 3 2 3 4 4 5 6}
   204    205   
   205    206   # Change which column is the PRIMARY KEY
   206    207   #
   207         -do_execsql_test conflict-7.1 {
          208  +do_execsql_test 7.1 {
   208    209     DROP TABLE t1;
   209    210     CREATE TABLE t1(
   210    211       a UNIQUE ON CONFLICT REPLACE, 
   211    212       b INTEGER PRIMARY KEY ON CONFLICT IGNORE,
   212    213       c UNIQUE ON CONFLICT FAIL
   213    214     );
   214    215     INSERT INTO t1(a,b,c) VALUES(1,2,3), (2,3,4);
   215    216     SELECT a,b,c FROM t1 ORDER BY a;
   216    217   } {1 2 3 2 3 4}
   217    218   
   218    219   # Insert a row that conflicts on column B.  The insert should be ignored.
   219    220   #
   220         -do_execsql_test conflict-7.2 {
          221  +do_execsql_test 7.2 {
   221    222     INSERT INTO t1(a,b,c) VALUES(3,2,5);
   222    223     SELECT a,b,c FROM t1 ORDER BY a;
   223    224   } {1 2 3 2 3 4}
   224    225   
   225    226   # Insert two rows where the second conflicts on C.  The first row show go
   226    227   # and and then there should be a constraint error.
   227    228   #
   228         -do_test conflict-7.3 {
          229  +do_test 7.3 {
   229    230     catchsql {INSERT INTO t1(a,b,c) VALUES(4,5,6), (5,6,4);}
   230    231   } {1 {UNIQUE constraint failed: t1.c}}
   231         -do_execsql_test conflict-7.4 {
          232  +do_execsql_test 7.4 {
   232    233     SELECT a,b,c FROM t1 ORDER BY a;
   233    234   } {1 2 3 2 3 4 4 5 6}
   234    235   
   235    236   # Change which column is the PRIMARY KEY
   236    237   #
   237         -do_execsql_test conflict-8.1 {
          238  +do_execsql_test 8.1 {
   238    239     DROP TABLE t1;
   239    240     CREATE TABLE t1(
   240    241       a UNIQUE ON CONFLICT REPLACE, 
   241    242       b INT PRIMARY KEY ON CONFLICT IGNORE,
   242    243       c UNIQUE ON CONFLICT FAIL
   243    244     );
   244    245     INSERT INTO t1(a,b,c) VALUES(1,2,3), (2,3,4);
   245    246     SELECT a,b,c FROM t1 ORDER BY a;
   246    247   } {1 2 3 2 3 4}
   247    248   
   248    249   # Insert a row that conflicts on column B.  The insert should be ignored.
   249    250   #
   250         -do_execsql_test conflict-8.2 {
          251  +do_execsql_test 8.2 {
   251    252     INSERT INTO t1(a,b,c) VALUES(3,2,5);
   252    253     SELECT a,b,c FROM t1 ORDER BY a;
   253    254   } {1 2 3 2 3 4}
   254    255   
   255    256   # Insert two rows where the second conflicts on C.  The first row show go
   256    257   # and and then there should be a constraint error.
   257    258   #
   258         -do_test conflict-8.3 {
          259  +do_test 8.3 {
   259    260     catchsql {INSERT INTO t1(a,b,c) VALUES(4,5,6), (5,6,4);}
   260    261   } {1 {UNIQUE constraint failed: t1.c}}
   261         -do_execsql_test conflict-8.4 {
          262  +do_execsql_test 8.4 {
   262    263     SELECT a,b,c FROM t1 ORDER BY a;
   263    264   } {1 2 3 2 3 4 4 5 6}
   264    265   
   265    266   # Change which column is the PRIMARY KEY
   266    267   #
   267         -do_execsql_test conflict-9.1 {
          268  +do_execsql_test 9.1 {
   268    269     DROP TABLE t1;
   269    270     CREATE TABLE t1(
   270    271       a UNIQUE ON CONFLICT REPLACE, 
   271    272       b INT PRIMARY KEY ON CONFLICT IGNORE,
   272    273       c UNIQUE ON CONFLICT FAIL
   273    274     ) WITHOUT ROWID;
   274    275     INSERT INTO t1(a,b,c) VALUES(1,2,3), (2,3,4);
   275    276     SELECT a,b,c FROM t1 ORDER BY a;
   276    277   } {1 2 3 2 3 4}
   277    278   
   278    279   # Insert a row that conflicts on column B.  The insert should be ignored.
   279    280   #
   280         -do_execsql_test conflict-9.2 {
          281  +do_execsql_test 9.2 {
   281    282     INSERT INTO t1(a,b,c) VALUES(3,2,5);
   282    283     SELECT a,b,c FROM t1 ORDER BY a;
   283    284   } {1 2 3 2 3 4}
   284    285   
   285    286   # Insert two rows where the second conflicts on C.  The first row show go
   286    287   # and and then there should be a constraint error.
   287    288   #
   288         -do_test conflict-9.3 {
          289  +do_test 9.3 {
   289    290     catchsql {INSERT INTO t1(a,b,c) VALUES(4,5,6), (5,6,4);}
   290    291   } {1 {UNIQUE constraint failed: t1.c}}
   291         -do_execsql_test conflict-9.4 {
          292  +do_execsql_test 9.4 {
   292    293     SELECT a,b,c FROM t1 ORDER BY a;
   293    294   } {1 2 3 2 3 4 4 5 6}
   294    295   
   295    296   # Change which column is the PRIMARY KEY
   296    297   #
   297         -do_execsql_test conflict-10.1 {
          298  +do_execsql_test 10.1 {
   298    299     DROP TABLE t1;
   299    300     CREATE TABLE t1(
   300    301       a UNIQUE ON CONFLICT REPLACE, 
   301    302       b UNIQUE ON CONFLICT IGNORE,
   302    303       c INTEGER PRIMARY KEY ON CONFLICT FAIL
   303    304     );
   304    305     INSERT INTO t1(a,b,c) VALUES(1,2,3), (2,3,4);
   305    306     SELECT a,b,c FROM t1 ORDER BY a;
   306    307   } {1 2 3 2 3 4}
   307    308   
   308    309   # Insert a row that conflicts on column B.  The insert should be ignored.
   309    310   #
   310         -do_execsql_test conflict-10.2 {
          311  +do_execsql_test 10.2 {
   311    312     INSERT INTO t1(a,b,c) VALUES(3,2,5);
   312    313     SELECT a,b,c FROM t1 ORDER BY a;
   313    314   } {1 2 3 2 3 4}
   314    315   
   315    316   # Insert two rows where the second conflicts on C.  The first row show go
   316    317   # and and then there should be a constraint error.
   317    318   #
   318         -do_test conflict-10.3 {
          319  +do_test 10.3 {
   319    320     catchsql {INSERT INTO t1(a,b,c) VALUES(4,5,6), (5,6,4);}
   320    321   } {1 {UNIQUE constraint failed: t1.c}}
   321         -do_execsql_test conflict-10.4 {
          322  +do_execsql_test 10.4 {
   322    323     SELECT a,b,c FROM t1 ORDER BY a;
   323    324   } {1 2 3 2 3 4 4 5 6}
   324    325   
   325    326   # Change which column is the PRIMARY KEY
   326    327   #
   327         -do_execsql_test conflict-11.1 {
          328  +do_execsql_test 11.1 {
   328    329     DROP TABLE t1;
   329    330     CREATE TABLE t1(
   330    331       a UNIQUE ON CONFLICT REPLACE, 
   331    332       b UNIQUE ON CONFLICT IGNORE,
   332    333       c PRIMARY KEY ON CONFLICT FAIL
   333    334     ) WITHOUT ROWID;
   334    335     INSERT INTO t1(a,b,c) VALUES(1,2,3), (2,3,4);
   335    336     SELECT a,b,c FROM t1 ORDER BY a;
   336    337   } {1 2 3 2 3 4}
   337    338   
   338    339   # Insert a row that conflicts on column B.  The insert should be ignored.
   339    340   #
   340         -do_execsql_test conflict-11.2 {
          341  +do_execsql_test 11.2 {
   341    342     INSERT INTO t1(a,b,c) VALUES(3,2,5);
   342    343     SELECT a,b,c FROM t1 ORDER BY a;
   343    344   } {1 2 3 2 3 4}
   344    345   
   345    346   # Insert two rows where the second conflicts on C.  The first row show go
   346    347   # and and then there should be a constraint error.
   347    348   #
   348         -do_test conflict-11.3 {
          349  +do_test 11.3 {
   349    350     catchsql {INSERT INTO t1(a,b,c) VALUES(4,5,6), (5,6,4);}
   350    351   } {1 {UNIQUE constraint failed: t1.c}}
   351         -do_execsql_test conflict-11.4 {
          352  +do_execsql_test 11.4 {
   352    353     SELECT a,b,c FROM t1 ORDER BY a;
   353    354   } {1 2 3 2 3 4 4 5 6}
          355  +
          356  +# Check that ticket [f68dc596c4] has been fixed.
          357  +#
          358  +do_execsql_test 12.1 {
          359  +  CREATE TABLE t2(a INTEGER PRIMARY KEY, b TEXT);
          360  +  INSERT INTO t2 VALUES(111, '111');
          361  +}
          362  +do_execsql_test 12.2 {
          363  +  REPLACE INTO t2 VALUES(NULL, '112'), (111, '111B');
          364  +}
          365  +do_execsql_test 12.3 {
          366  +  SELECT * FROM t2;
          367  +} {111 111B 112 112}
   354    368   
   355    369   
   356    370   finish_test

Changes to test/fkey5.test.

   111    111   } {1 {no such table: temp.c2}}
   112    112   
   113    113   # EVIDENCE-OF: R-45728-08709 There are four columns in each result row.
   114    114   #
   115    115   # EVIDENCE-OF: R-55672-01620 The first column is the name of the table
   116    116   # that contains the REFERENCES clause.
   117    117   #
   118         -# EVIDENCE-OF: R-25219-25618 The second column is the rowid of the row
   119         -# that contains the invalid REFERENCES clause.
          118  +# EVIDENCE-OF: R-00471-55166 The second column is the rowid of the row
          119  +# that contains the invalid REFERENCES clause, or NULL if the child
          120  +# table is a WITHOUT ROWID table.
          121  +#
          122  +# The second clause in the previous is tested by fkey5-10.3.
   120    123   #
   121    124   # EVIDENCE-OF: R-40482-20265 The third column is the name of the table
   122    125   # that is referred to.
   123    126   #
   124    127   # EVIDENCE-OF: R-62839-07969 The fourth column is the index of the
   125    128   # specific foreign key constraint that failed.
   126    129   #
................................................................................
   403    406   
   404    407     INSERT INTO p30 (id) VALUES (1);
   405    408     INSERT INTO c30 (master, line)  VALUES (1, 999);
   406    409   }
   407    410   do_execsql_test 10.2 {
   408    411     PRAGMA foreign_key_check;
   409    412   }
          413  +# EVIDENCE-OF: R-00471-55166 The second column is the rowid of the row
          414  +# that contains the invalid REFERENCES clause, or NULL if the child
          415  +# table is a WITHOUT ROWID table.
   410    416   do_execsql_test 10.3 {
   411    417     INSERT INTO c30 VALUES(45, 45);
   412    418     PRAGMA foreign_key_check;
   413    419   } {c30 {} p30 0}
   414    420   
   415    421   #-------------------------------------------------------------------------
   416    422   # Test "foreign key mismatch" errors.

Changes to test/fts3fault.test.

   173    173     execsql "INSERT INTO t8 VALUES('[string repeat {c } 50000]')"
   174    174     execsql "INSERT INTO t8 VALUES('d d d')"
   175    175     execsql "INSERT INTO t8 VALUES('e e e')"
   176    176     execsql "INSERT INTO t8(t8) VALUES('optimize')"
   177    177     faultsim_save_and_close
   178    178   } {}
   179    179   
   180         -do_faultsim_test 8.1 -faults oom-t* -prep { 
   181         -  faultsim_restore_and_reopen
   182         -  db func mit mit
   183         -} -body {
   184         -  execsql { SELECT mit(matchinfo(t8, 'x')) FROM t8 WHERE t8 MATCH 'a b c' }
   185         -} -test {
   186         -  faultsim_test_result {0 {{1 1 1 1 4 2 1 5 5}}}
          180  +ifcapable fts4_deferred {
          181  +  do_faultsim_test 8.1 -faults oom-t* -prep { 
          182  +    faultsim_restore_and_reopen
          183  +    db func mit mit
          184  +  } -body {
          185  +    execsql { SELECT mit(matchinfo(t8, 'x')) FROM t8 WHERE t8 MATCH 'a b c' }
          186  +  } -test {
          187  +    faultsim_test_result {0 {{1 1 1 1 4 2 1 5 5}}}
          188  +  }
   187    189   }
   188    190   
   189    191   do_faultsim_test 8.2 -faults oom-t* -prep { 
   190    192     faultsim_restore_and_reopen
   191    193     db func mit mit
   192    194   } -body {
   193    195     execsql { SELECT mit(matchinfo(t8, 's')) FROM t8 WHERE t8 MATCH 'a b c' }

Changes to test/fts3misc.test.

   143    143   do_execsql_test 3.1.5 {
   144    144     SELECT rowid FROM t3 WHERE t3 MATCH '"2 3 4 5 6 7 8 9"'
   145    145   } {4}
   146    146   
   147    147   #-------------------------------------------------------------------------
   148    148   #
   149    149   reset_db
   150         -do_execsql_test 4.0 {
   151         -  PRAGMA page_size = 512;
   152         -  CREATE VIRTUAL TABLE t4 USING fts4;
   153         -  WITH s(i) AS ( SELECT 1 UNION ALL SELECT i+1 FROM s WHERE i<8000 )
   154         -  INSERT INTO t4 SELECT 'a b c a b c a b c' FROM s;
   155         -}
   156         -do_execsql_test 4.1 {
   157         -  SELECT count(*) FROM t4 WHERE t4 MATCH '"a b c" OR "c a b"'
   158         -} {8000}
   159         -do_execsql_test 4.2 {
   160         -  SELECT quote(value) from t4_stat where id=0
   161         -} {X'C03EC0B204C0A608'}
   162         -do_execsql_test 4.3 {
   163         -  UPDATE t4_stat SET value = X'C03EC0B204C0A60800' WHERE id=0;
   164         -}
   165         -do_catchsql_test 4.4 {
   166         -  SELECT count(*) FROM t4 WHERE t4 MATCH '"a b c" OR "c a b"'
   167         -} {1 {database disk image is malformed}}
   168         -do_execsql_test 4.5 {
   169         -  UPDATE t4_stat SET value = X'00C03EC0B204C0A608' WHERE id=0;
   170         -}
   171         -do_catchsql_test 4.6 {
   172         -  SELECT count(*) FROM t4 WHERE t4 MATCH '"a b c" OR "c a b"'
   173         -} {1 {database disk image is malformed}}
          150  +ifcapable fts4_deferred {
          151  +  do_execsql_test 4.0 {
          152  +    PRAGMA page_size = 512;
          153  +    CREATE VIRTUAL TABLE t4 USING fts4;
          154  +    WITH s(i) AS ( SELECT 1 UNION ALL SELECT i+1 FROM s WHERE i<8000 )
          155  +    INSERT INTO t4 SELECT 'a b c a b c a b c' FROM s;
          156  +  }
          157  +  do_execsql_test 4.1 {
          158  +    SELECT count(*) FROM t4 WHERE t4 MATCH '"a b c" OR "c a b"'
          159  +  } {8000}
          160  +  do_execsql_test 4.2 {
          161  +    SELECT quote(value) from t4_stat where id=0
          162  +  } {X'C03EC0B204C0A608'}
          163  +  do_execsql_test 4.3 {
          164  +    UPDATE t4_stat SET value = X'C03EC0B204C0A60800' WHERE id=0;
          165  +  }
          166  +  do_catchsql_test 4.4 {
          167  +    SELECT count(*) FROM t4 WHERE t4 MATCH '"a b c" OR "c a b"'
          168  +  } {1 {database disk image is malformed}}
          169  +  do_execsql_test 4.5 {
          170  +    UPDATE t4_stat SET value = X'00C03EC0B204C0A608' WHERE id=0;
          171  +  }
          172  +  do_catchsql_test 4.6 {
          173  +    SELECT count(*) FROM t4 WHERE t4 MATCH '"a b c" OR "c a b"'
          174  +  } {1 {database disk image is malformed}}
          175  +}
   174    176   
   175    177   #-------------------------------------------------------------------------
   176    178   #
   177    179   reset_db
   178    180   do_execsql_test 5.0 {
   179    181     CREATE VIRTUAL TABLE t5 USING fts4;
   180    182     INSERT INTO t5 VALUES('a x x x x b x x x x c');

Added test/having.test.

            1  +# 2017 April 30
            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  +#
           12  +# Test the HAVING->WHERE optimization.
           13  +#
           14  +
           15  +set testdir [file dirname $argv0]
           16  +source $testdir/tester.tcl
           17  +set testprefix having
           18  +
           19  +do_execsql_test 1.0 {
           20  +  CREATE TABLE t2(c, d);
           21  +
           22  +  CREATE TABLE t1(a, b);
           23  +  INSERT INTO t1 VALUES(1, 1);
           24  +  INSERT INTO t1 VALUES(2, 2);
           25  +  INSERT INTO t1 VALUES(1, 3);
           26  +  INSERT INTO t1 VALUES(2, 4);
           27  +  INSERT INTO t1 VALUES(1, 5);
           28  +  INSERT INTO t1 VALUES(2, 6);
           29  +} {}
           30  +
           31  +foreach {tn sql res} {
           32  +  1 "SELECT a, sum(b) FROM t1 GROUP BY a HAVING a=2" {2 12}
           33  +  2 "SELECT a, sum(b) FROM t1 GROUP BY a HAVING a=2 AND sum(b)>10" {2 12}
           34  +  3 "SELECT a, sum(b) FROM t1 GROUP BY a HAVING sum(b)>12" {}
           35  +} {
           36  +  do_execsql_test 1.$tn $sql $res
           37  +}
           38  +
           39  +# Run an EXPLAIN command for both SQL statements. Return true if 
           40  +# the outputs are identical, or false otherwise.
           41  +#
           42  +proc compare_vdbe {sql1 sql2} {
           43  +  set r1 [list]
           44  +  set r2 [list]
           45  +  db eval "explain $sql1" { lappend r1 $opcode $p1 $p2 $p3 $p4 $p5}
           46  +  db eval "explain $sql2" { lappend r2 $opcode $p1 $p2 $p3 $p4 $p5}
           47  +  return [expr {$r1==$r2}]
           48  +}
           49  +
           50  +proc do_compare_vdbe_test {tn sql1 sql2 res} {
           51  +  uplevel [list do_test $tn [list compare_vdbe $sql1 $sql2] $res]
           52  +}
           53  +
           54  +#-------------------------------------------------------------------------
           55  +# Test that various statements that are eligible for the optimization
           56  +# produce the same VDBE code as optimizing by hand does.
           57  +#
           58  +foreach {tn sql1 sql2} {
           59  +  1 "SELECT a, sum(b) FROM t1 GROUP BY a HAVING a=2"
           60  +    "SELECT a, sum(b) FROM t1 WHERE a=2 GROUP BY a"
           61  +
           62  +  2 "SELECT a, sum(b) FROM t1 GROUP BY a HAVING sum(b)>5 AND a=2"
           63  +    "SELECT a, sum(b) FROM t1 WHERE a=2 GROUP BY a HAVING sum(b)>5"
           64  +
           65  +  3 "SELECT a, sum(b) FROM t1 GROUP BY a COLLATE binary HAVING a=2"
           66  +    "SELECT a, sum(b) FROM t1 WHERE a=2 GROUP BY a COLLATE binary"
           67  +
           68  +  4 {
           69  +      SELECT x,y FROM (
           70  +        SELECT a AS x, sum(b) AS y FROM t1 
           71  +        GROUP BY a
           72  +      ) WHERE x BETWEEN 8888 AND 9999
           73  +    } {
           74  +      SELECT x,y FROM (
           75  +        SELECT a AS x, sum(b) AS y FROM t1 
           76  +        WHERE x BETWEEN 8888 AND 9999 
           77  +        GROUP BY a
           78  +      )
           79  +    }
           80  +
           81  +  5 "SELECT a, sum(b) FROM t1 GROUP BY a COLLATE binary HAVING 0"
           82  +    "SELECT a, sum(b) FROM t1 WHERE 0 GROUP BY a COLLATE binary"
           83  +
           84  +  6 "SELECT count(*) FROM t1,t2 WHERE a=c GROUP BY b, d HAVING b=d"
           85  +    "SELECT count(*) FROM t1,t2 WHERE a=c AND b=d GROUP BY b, d"
           86  +
           87  +  7 {
           88  +      SELECT count(*) FROM t1,t2 WHERE a=c GROUP BY b, d 
           89  +      HAVING b=d COLLATE nocase
           90  +    } {
           91  +      SELECT count(*) FROM t1,t2 WHERE a=c AND b=d COLLATE nocase 
           92  +      GROUP BY b, d
           93  +    }
           94  +
           95  +  8 "SELECT a, sum(b) FROM t1 GROUP BY a||b HAVING substr(a||b, 1, 1)='a'"
           96  +    "SELECT a, sum(b) FROM t1 WHERE substr(a||b, 1, 1)='a' GROUP BY a||b"
           97  +} {
           98  +  do_compare_vdbe_test 2.$tn $sql1 $sql2 1
           99  +}
          100  +
          101  +#-------------------------------------------------------------------------
          102  +# 1: Test that the optimization is only applied if the GROUP BY term
          103  +#    uses BINARY collation.
          104  +#
          105  +# 2: Not applied if there is a non-deterministic function in the HAVING
          106  +#    term.
          107  +#
          108  +foreach {tn sql1 sql2} {
          109  +  1 "SELECT a, sum(b) FROM t1 GROUP BY a COLLATE nocase HAVING a=2"
          110  +    "SELECT a, sum(b) FROM t1 WHERE a=2 GROUP BY a COLLATE nocase"
          111  +
          112  +  2 "SELECT a, sum(b) FROM t1 GROUP BY a HAVING randomblob(a)<X'88'"
          113  +    "SELECT a, sum(b) FROM t1 WHERE randomblob(a)<X'88' GROUP BY a"
          114  +} {
          115  +  do_compare_vdbe_test 3.$tn $sql1 $sql2 0
          116  +}
          117  +
          118  +
          119  +#-------------------------------------------------------------------------
          120  +# Test that non-deterministic functions disqualify a term from being
          121  +# moved from the HAVING to WHERE clause.
          122  +#
          123  +do_execsql_test 4.1 {
          124  +  CREATE TABLE t3(a, b);
          125  +  INSERT INTO t3 VALUES(1, 1);
          126  +  INSERT INTO t3 VALUES(1, 2);
          127  +  INSERT INTO t3 VALUES(1, 3);
          128  +  INSERT INTO t3 VALUES(2, 1);
          129  +  INSERT INTO t3 VALUES(2, 2);
          130  +  INSERT INTO t3 VALUES(2, 3);
          131  +}
          132  +
          133  +proc nondeter {args} {
          134  +  incr ::nondeter_ret
          135  +  expr {$::nondeter_ret % 2}
          136  +}
          137  +db func nondeter nondeter
          138  +
          139  +set ::nondeter_ret 0
          140  +do_execsql_test 4.2 {
          141  +  SELECT a, sum(b) FROM t3 GROUP BY a HAVING nondeter(a)
          142  +} {1 6}
          143  +
          144  +# If the term where moved, the query above would return the same
          145  +# result as the following. But it does not.
          146  +#
          147  +set ::nondeter_ret 0
          148  +do_execsql_test 4.3 {
          149  +  SELECT a, sum(b) FROM t3 WHERE nondeter(a) GROUP BY a
          150  +} {1 4 2 2}
          151  +
          152  +
          153  +finish_test
          154  +

Added test/pushdown.test.

            1  +# 2017 April 29
            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  +
           12  +set testdir [file dirname $argv0]
           13  +source $testdir/tester.tcl
           14  +set testprefix pushdown
           15  +
           16  +do_execsql_test 1.0 {
           17  +  CREATE TABLE t1(a, b, c);
           18  +  INSERT INTO t1 VALUES(1, 'b1', 'c1');
           19  +  INSERT INTO t1 VALUES(2, 'b2', 'c2');
           20  +  INSERT INTO t1 VALUES(3, 'b3', 'c3');
           21  +  INSERT INTO t1 VALUES(4, 'b4', 'c4');
           22  +  CREATE INDEX i1 ON t1(a, c);
           23  +}
           24  +
           25  +proc f {val} {
           26  +  lappend ::L $val
           27  +  return 0
           28  +}
           29  +db func f f 
           30  +
           31  +do_test 1.1 {
           32  +  set L [list]
           33  +  execsql { SELECT * FROM t1 WHERE a=2 AND f(b) AND f(c) }
           34  +  set L
           35  +} {c2}
           36  +
           37  +do_test 1.2 {
           38  +  set L [list]
           39  +  execsql { SELECT * FROM t1 WHERE a=3 AND f(c) AND f(b) }
           40  +  set L
           41  +} {c3}
           42  +
           43  +do_execsql_test 1.3 {
           44  +  DROP INDEX i1;
           45  +  CREATE INDEX i1 ON t1(a, b);
           46  +}
           47  +do_test 1.4 {
           48  +  set L [list]
           49  +  execsql { SELECT * FROM t1 WHERE a=2 AND f(b) AND f(c) }
           50  +  set L
           51  +} {b2}
           52  +
           53  +do_test 1.5 {
           54  +  set L [list]
           55  +  execsql { SELECT * FROM t1 WHERE a=3 AND f(c) AND f(b) }
           56  +  set L
           57  +} {b3}
           58  +  
           59  +finish_test

Added test/subjournal.test.

            1  +# 2017 May 9
            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  +#
           12  +
           13  +set testdir [file dirname $argv0]
           14  +source $testdir/tester.tcl
           15  +set testprefix subjournal
           16  +
           17  +do_execsql_test 1.0 {
           18  +  PRAGMA temp_store = memory;
           19  +  CREATE TABLE t1(a,b,c);
           20  +  INSERT INTO t1 VALUES(1, 2, 3);
           21  +} {}
           22  +do_execsql_test 1.1 {
           23  +  BEGIN;
           24  +    INSERT INTO t1 VALUES(4, 5, 6);
           25  +    SAVEPOINT one;
           26  +      INSERT INTO t1 VALUES(7, 8, 9);
           27  +    ROLLBACK TO one;
           28  +    SELECT * FROM t1;
           29  +} {1 2 3 4 5 6}
           30  +do_execsql_test 1.2 {
           31  +  COMMIT;
           32  +}
           33  +
           34  +do_execsql_test 2.0 {
           35  +  PRAGMA cache_size = 5;
           36  +  CREATE TABLE t2(a BLOB);
           37  +  CREATE INDEX i2 ON t2(a);
           38  +  WITH s(i) AS (
           39  +    SELECT 1 UNION ALL SELECT i+1 FROM s WHERE i<100
           40  +  ) INSERT INTO t2 SELECT randomblob(500) FROM s;
           41  +}
           42  +
           43  +do_test 2.1 {
           44  +  forcedelete test.db2
           45  +  sqlite3 db2 test2.db
           46  +  sqlite3_backup B db2 main db main
           47  +  set nPage [db one {PRAGMA page_count}]
           48  +  B step [expr $nPage-10]
           49  +} {SQLITE_OK}
           50  +
           51  +do_execsql_test 2.2 {
           52  +  BEGIN;
           53  +    UPDATE t2 SET a=randomblob(499);
           54  +    SAVEPOINT two;
           55  +      UPDATE t2 SET a=randomblob(498);
           56  +    ROLLBACK TO two;
           57  +  COMMIT;
           58  +  PRAGMA integrity_check;
           59  +} {ok}
           60  +
           61  +do_test 2.3 {
           62  +  B step 1000
           63  +} {SQLITE_DONE}
           64  +do_test 2.4 {
           65  +  B finish
           66  +  execsql { PRAGMA integrity_check } db2
           67  +} {ok}
           68  +
           69  +finish_test
           70  +

Changes to tool/lemon.c.

  4157   4157     /* Mark rules that are actually used for reduce actions after all
  4158   4158     ** optimizations have been applied
  4159   4159     */
  4160   4160     for(rp=lemp->rule; rp; rp=rp->next) rp->doesReduce = LEMON_FALSE;
  4161   4161     for(i=0; i<lemp->nxstate; i++){
  4162   4162       for(ap=lemp->sorted[i]->ap; ap; ap=ap->next){
  4163   4163         if( ap->type==REDUCE || ap->type==SHIFTREDUCE ){
  4164         -        ap->x.rp->doesReduce = i;
         4164  +        ap->x.rp->doesReduce = 1;
  4165   4165         }
  4166   4166       }
  4167   4167     }
  4168   4168   
  4169   4169     /* Finish rendering the constants now that the action table has
  4170   4170     ** been computed */
  4171   4171     fprintf(out,"#define YYNSTATE             %d\n",lemp->nxstate);  lineno++;