Index: ext/fts3/fts3.c ================================================================== --- ext/fts3/fts3.c +++ ext/fts3/fts3.c @@ -4785,11 +4785,10 @@ ** The first varint is the number of documents currently stored in ** the table. The following nCol varints contain the total amount of ** data stored in all rows of each column of the table, from left ** to right. */ - int rc; Fts3Table *p = (Fts3Table*)pCsr->base.pVtab; sqlite3_stmt *pStmt; sqlite3_int64 nDoc = 0; sqlite3_int64 nByte = 0; const char *pEnd; Index: ext/fts5/fts5_index.c ================================================================== --- ext/fts5/fts5_index.c +++ ext/fts5/fts5_index.c @@ -4190,10 +4190,11 @@ Fts5StructureSegment *pSeg; /* Output segment */ Fts5Buffer term; int bOldest; /* True if the output segment is the oldest */ int eDetail = p->pConfig->eDetail; const int flags = FTS5INDEX_QUERY_NOOUTPUT; + int bTermWritten = 0; /* True if current term already output */ assert( iLvlnLevel ); assert( pLvl->nMerge<=pLvl->nSeg ); memset(&writer, 0, sizeof(Fts5SegWriter)); @@ -4243,22 +4244,26 @@ Fts5SegIter *pSegIter = &pIter->aSeg[ pIter->aFirst[1].iFirst ]; int nPos; /* position-list size field value */ int nTerm; const u8 *pTerm; - /* Check for key annihilation. */ - if( pSegIter->nPos==0 && (bOldest || pSegIter->bDel==0) ) continue; - pTerm = fts5MultiIterTerm(pIter, &nTerm); if( nTerm!=term.n || memcmp(pTerm, term.p, nTerm) ){ if( pnRem && writer.nLeafWritten>nRem ){ break; } + fts5BufferSet(&p->rc, &term, nTerm, pTerm); + bTermWritten =0; + } + /* Check for key annihilation. */ + if( pSegIter->nPos==0 && (bOldest || pSegIter->bDel==0) ) continue; + + if( p->rc==SQLITE_OK && bTermWritten==0 ){ /* This is a new term. Append a term to the output segment. */ fts5WriteAppendTerm(p, &writer, nTerm, pTerm); - fts5BufferSet(&p->rc, &term, nTerm, pTerm); + bTermWritten = 1; } /* Append the rowid to the output */ /* WRITEPOSLISTSIZE */ fts5WriteAppendRowid(p, &writer, fts5MultiIterRowid(pIter)); Index: ext/fts5/fts5_test_tok.c ================================================================== --- ext/fts5/fts5_test_tok.c +++ ext/fts5/fts5_test_tok.c @@ -38,11 +38,11 @@ ** pos: Token offset of token within input. ** */ #if defined(SQLITE_TEST) && defined(SQLITE_ENABLE_FTS5) -#include +#include "fts5.h" #include #include typedef struct Fts5tokTable Fts5tokTable; typedef struct Fts5tokCursor Fts5tokCursor; ADDED ext/fts5/test/fts5delete.test Index: ext/fts5/test/fts5delete.test ================================================================== --- ext/fts5/test/fts5delete.test +++ ext/fts5/test/fts5delete.test @@ -0,0 +1,54 @@ +# 2017 May 12 +# +# The author disclaims copyright to this source code. In place of +# a legal notice, here is a blessing: +# +# May you do good and not evil. +# May you find forgiveness for yourself and forgive others. +# May you share freely, never taking more than you give. +# +#************************************************************************* +# This file implements regression tests for SQLite library. The +# focus of this script is testing the FTS5 module. +# + +source [file join [file dirname [info script]] fts5_common.tcl] +set testprefix fts5delete + +# If SQLITE_ENABLE_FTS5 is not defined, omit this file. +ifcapable !fts5 { + finish_test + return +} +fts5_aux_test_functions db + +do_execsql_test 1.0 { + CREATE VIRTUAL TABLE t1 USING fts5(x); + WITH s(i) AS ( + SELECT 1 UNION ALL SELECT i+1 FROM s WHERE i<5000 + ) + INSERT INTO t1(rowid, x) SELECT i, (i/2)*2 FROM s; +} + +do_test 1.1 { + execsql BEGIN + for {set i 1} {$i<=5000} {incr i} { + if {$i % 2} { + execsql { INSERT INTO t1 VALUES($i) } + } else { + execsql { DELETE FROM t1 WHERE rowid = $i } + } + } + execsql COMMIT +} {} + +do_test 1.2 { + execsql { INSERT INTO t1(t1, rank) VALUES('usermerge', 2); } + for {set i 0} {$i < 5} {incr i} { + execsql { INSERT INTO t1(t1, rank) VALUES('merge', 1) } + execsql { INSERT INTO t1(t1) VALUES('integrity-check') } + } +} {} + +finish_test + Index: ext/misc/json1.c ================================================================== --- ext/misc/json1.c +++ ext/misc/json1.c @@ -169,10 +169,11 @@ const char *zJson; /* Original JSON string */ u32 *aUp; /* Index of parent of each node */ u8 oom; /* Set to true if out of memory */ u8 nErr; /* Number of errors seen */ u16 iDepth; /* Nesting depth */ + int nJson; /* Length of the zJson string in bytes */ }; /* ** Maximum nesting depth of JSON for this implementation. ** @@ -410,10 +411,18 @@ pParse->nNode = 0; pParse->nAlloc = 0; sqlite3_free(pParse->aUp); pParse->aUp = 0; } + +/* +** Free a JsonParse object that was obtained from sqlite3_malloc(). +*/ +static void jsonParseFree(JsonParse *pParse){ + jsonParseReset(pParse); + sqlite3_free(pParse); +} /* ** Convert the JsonNode pNode into a pure JSON string and ** append to pOut. Subsubstructure is also included. Return ** the number of JsonNode objects that are encoded. @@ -961,10 +970,53 @@ return SQLITE_NOMEM; } jsonParseFillInParentage(pParse, 0, 0); return SQLITE_OK; } + +/* +** Magic number used for the JSON parse cache in sqlite3_get_auxdata() +*/ +#define JSON_CACHE_ID (-429938) + +/* +** Obtain a complete parse of the JSON found in the first argument +** of the argv array. Use the sqlite3_get_auxdata() cache for this +** parse if it is available. If the cache is not available or if it +** is no longer valid, parse the JSON again and return the new parse, +** and also register the new parse so that it will be available for +** future sqlite3_get_auxdata() calls. +*/ +static JsonParse *jsonParseCached( + sqlite3_context *pCtx, + sqlite3_value **argv +){ + const char *zJson = (const char*)sqlite3_value_text(argv[0]); + int nJson = sqlite3_value_bytes(argv[0]); + JsonParse *p; + if( zJson==0 ) return 0; + p = (JsonParse*)sqlite3_get_auxdata(pCtx, JSON_CACHE_ID); + if( p && p->nJson==nJson && memcmp(p->zJson,zJson,nJson)==0 ){ + p->nErr = 0; + return p; /* The cached entry matches, so return it */ + } + p = sqlite3_malloc( sizeof(*p) + nJson + 1 ); + if( p==0 ){ + sqlite3_result_error_nomem(pCtx); + return 0; + } + memset(p, 0, sizeof(*p)); + p->zJson = (char*)&p[1]; + memcpy((char*)p->zJson, zJson, nJson+1); + if( jsonParse(p, pCtx, p->zJson) ){ + sqlite3_free(p); + return 0; + } + p->nJson = nJson; + sqlite3_set_auxdata(pCtx, JSON_CACHE_ID, p, (void(*)(void*))jsonParseFree); + return (JsonParse*)sqlite3_get_auxdata(pCtx, JSON_CACHE_ID); +} /* ** Compare the OBJECT label at pNode against zKey,nKey. Return true on ** a match. */ @@ -1327,33 +1379,34 @@ static void jsonArrayLengthFunc( sqlite3_context *ctx, int argc, sqlite3_value **argv ){ - JsonParse x; /* The parse */ + JsonParse *p; /* The parse */ sqlite3_int64 n = 0; u32 i; JsonNode *pNode; - if( jsonParse(&x, ctx, (const char*)sqlite3_value_text(argv[0])) ) return; - assert( x.nNode ); + p = jsonParseCached(ctx, argv); + if( p==0 ) return; + assert( p->nNode ); if( argc==2 ){ const char *zPath = (const char*)sqlite3_value_text(argv[1]); - pNode = jsonLookup(&x, zPath, 0, ctx); + pNode = jsonLookup(p, zPath, 0, ctx); }else{ - pNode = x.aNode; + pNode = p->aNode; } if( pNode==0 ){ - x.nErr = 1; - }else if( pNode->eType==JSON_ARRAY ){ + return; + } + if( pNode->eType==JSON_ARRAY ){ assert( (pNode->jnFlags & JNODE_APPEND)==0 ); for(i=1; i<=pNode->n; n++){ i += jsonNodeSize(&pNode[i]); } } - if( x.nErr==0 ) sqlite3_result_int64(ctx, n); - jsonParseReset(&x); + sqlite3_result_int64(ctx, n); } /* ** json_extract(JSON, PATH, ...) ** @@ -1365,24 +1418,25 @@ static void jsonExtractFunc( sqlite3_context *ctx, int argc, sqlite3_value **argv ){ - JsonParse x; /* The parse */ + JsonParse *p; /* The parse */ JsonNode *pNode; const char *zPath; JsonString jx; int i; if( argc<2 ) return; - if( jsonParse(&x, ctx, (const char*)sqlite3_value_text(argv[0])) ) return; + p = jsonParseCached(ctx, argv); + if( p==0 ) return; jsonInit(&jx, ctx); jsonAppendChar(&jx, '['); for(i=1; inErr ) break; if( argc>2 ){ jsonAppendSeparator(&jx); if( pNode ){ jsonRenderNode(pNode, &jx, 0); }else{ @@ -1396,11 +1450,10 @@ jsonAppendChar(&jx, ']'); jsonResult(&jx); sqlite3_result_subtype(ctx, JSON_SUBTYPE); } jsonReset(&jx); - jsonParseReset(&x); } /* This is the RFC 7396 MergePatch algorithm. */ static JsonNode *jsonMergePatch( Index: ext/rtree/rtree.c ================================================================== --- ext/rtree/rtree.c +++ ext/rtree/rtree.c @@ -3221,10 +3221,11 @@ ** COMMIT; */ static int rtreeSavepoint(sqlite3_vtab *pVtab, int iSavepoint){ Rtree *pRtree = (Rtree *)pVtab; int iwt = pRtree->inWrTrans; + UNUSED_PARAMETER(iSavepoint); pRtree->inWrTrans = 0; nodeBlobReset(pRtree); pRtree->inWrTrans = iwt; return SQLITE_OK; } Index: src/auth.c ================================================================== --- src/auth.c +++ src/auth.c @@ -214,10 +214,22 @@ } if( db->xAuth==0 ){ return SQLITE_OK; } + + /* EVIDENCE-OF: R-43249-19882 The third through sixth parameters to the + ** callback are either NULL pointers or zero-terminated strings that + ** contain additional details about the action to be authorized. + ** + ** The following testcase() macros show that any of the 3rd through 6th + ** parameters can be either NULL or a string. */ + testcase( zArg1==0 ); + testcase( zArg2==0 ); + testcase( zArg3==0 ); + testcase( pParse->zAuthContext==0 ); + rc = db->xAuth(db->pAuthArg, code, zArg1, zArg2, zArg3, pParse->zAuthContext #ifdef SQLITE_USER_AUTHENTICATION ,db->auth.zAuthUser #endif ); Index: src/btree.c ================================================================== --- src/btree.c +++ src/btree.c @@ -8194,10 +8194,11 @@ dropCell(pPage, idx, info.nSize, &rc); if( rc ) goto end_insert; }else if( loc<0 && pPage->nCell>0 ){ assert( pPage->leaf ); idx = ++pCur->ix; + pCur->curFlags &= ~BTCF_ValidNKey; }else{ assert( pPage->leaf ); } insertCell(pPage, idx, newCell, szNew, 0, 0, &rc); assert( pPage->nOverflow==0 || rc==SQLITE_OK ); @@ -9318,10 +9319,11 @@ if( pPage->intKey ){ if( keyCanBeEqual ? (info.nKey > maxKey) : (info.nKey >= maxKey) ){ checkAppendMsg(pCheck, "Rowid %lld out of order", info.nKey); } maxKey = info.nKey; + keyCanBeEqual = 0; /* Only the first key on the page may ==maxKey */ } /* Check the content overflow list */ if( info.nPayload>info.nLocal ){ int nPage; /* Number of pages on the overflow chain */ Index: src/delete.c ================================================================== --- src/delete.c +++ src/delete.c @@ -348,11 +348,18 @@ #ifndef SQLITE_OMIT_TRUNCATE_OPTIMIZATION /* Special case: A DELETE without a WHERE clause deletes everything. ** It is easier just to erase the whole table. Prior to version 3.6.5, ** this optimization caused the row change count (the value returned by - ** API function sqlite3_count_changes) to be set incorrectly. */ + ** API function sqlite3_count_changes) to be set incorrectly. + ** + ** The "rcauth==SQLITE_OK" terms is the + ** IMPLEMENATION-OF: R-17228-37124 If the action code is SQLITE_DELETE and + ** the callback returns SQLITE_IGNORE then the DELETE operation proceeds but + ** the truncate optimization is disabled and all rows are deleted + ** individually. + */ if( rcauth==SQLITE_OK && pWhere==0 && !bComplex && !IsVirtual(pTab) #ifdef SQLITE_ENABLE_PREUPDATE_HOOK Index: src/expr.c ================================================================== --- src/expr.c +++ src/expr.c @@ -1548,11 +1548,11 @@ pList->a[pList->nExpr-1].zName = pColumns->a[i].zName; pColumns->a[i].zName = 0; } } - if( pExpr->op==TK_SELECT && pList ){ + if( !db->mallocFailed && pExpr->op==TK_SELECT && ALWAYS(pList!=0) ){ Expr *pFirst = pList->a[iFirst].pExpr; assert( pFirst!=0 ); assert( pFirst->op==TK_SELECT_COLUMN ); /* Store the SELECT statement in pRight so it will be deleted when @@ -1813,10 +1813,69 @@ */ int sqlite3ExprIsTableConstant(Expr *p, int iCur){ return exprIsConst(p, 3, iCur); } + +/* +** sqlite3WalkExpr() callback used by sqlite3ExprIsConstantOrGroupBy(). +*/ +static int exprNodeIsConstantOrGroupBy(Walker *pWalker, Expr *pExpr){ + ExprList *pGroupBy = pWalker->u.pGroupBy; + int i; + + /* Check if pExpr is identical to any GROUP BY term. If so, consider + ** it constant. */ + for(i=0; inExpr; i++){ + Expr *p = pGroupBy->a[i].pExpr; + if( sqlite3ExprCompare(pExpr, p, -1)<2 ){ + CollSeq *pColl = sqlite3ExprCollSeq(pWalker->pParse, p); + if( pColl==0 || sqlite3_stricmp("BINARY", pColl->zName)==0 ){ + return WRC_Prune; + } + } + } + + /* Check if pExpr is a sub-select. If so, consider it variable. */ + if( ExprHasProperty(pExpr, EP_xIsSelect) ){ + pWalker->eCode = 0; + return WRC_Abort; + } + + return exprNodeIsConstant(pWalker, pExpr); +} + +/* +** Walk the expression tree passed as the first argument. Return non-zero +** if the expression consists entirely of constants or copies of terms +** in pGroupBy that sort with the BINARY collation sequence. +** +** This routine is used to determine if a term of the HAVING clause can +** be promoted into the WHERE clause. In order for such a promotion to work, +** the value of the HAVING clause term must be the same for all members of +** a "group". The requirement that the GROUP BY term must be BINARY +** assumes that no other collating sequence will have a finer-grained +** grouping than binary. In other words (A=B COLLATE binary) implies +** A=B in every other collating sequence. The requirement that the +** GROUP BY be BINARY is stricter than necessary. It would also work +** to promote HAVING clauses that use the same alternative collating +** sequence as the GROUP BY term, but that is much harder to check, +** alternative collating sequences are uncommon, and this is only an +** optimization, so we take the easy way out and simply require the +** GROUP BY to use the BINARY collating sequence. +*/ +int sqlite3ExprIsConstantOrGroupBy(Parse *pParse, Expr *p, ExprList *pGroupBy){ + Walker w; + memset(&w, 0, sizeof(w)); + w.eCode = 1; + w.xExprCallback = exprNodeIsConstantOrGroupBy; + w.u.pGroupBy = pGroupBy; + w.pParse = pParse; + sqlite3WalkExpr(&w, p); + return w.eCode; +} + /* ** Walk an expression tree. Return non-zero if the expression is constant ** or a function call with constant arguments. Return and 0 if there ** are any variables. ** Index: src/global.c ================================================================== --- src/global.c +++ src/global.c @@ -135,13 +135,20 @@ ** using the SQLITE_USE_URI=1 or SQLITE_USE_URI=0 compile-time options. ** ** EVIDENCE-OF: R-43642-56306 By default, URI handling is globally ** disabled. The default value may be changed by compiling with the ** SQLITE_USE_URI symbol defined. +** +** URI filenames are enabled by default if SQLITE_HAS_CODEC is +** enabled. */ #ifndef SQLITE_USE_URI -# define SQLITE_USE_URI 0 +# ifdef SQLITE_HAS_CODEC +# define SQLITE_USE_URI 1 +# else +# define SQLITE_USE_URI 0 +# endif #endif /* EVIDENCE-OF: R-38720-18127 The default setting is determined by the ** SQLITE_ALLOW_COVERING_INDEX_SCAN compile-time option, or is "on" if ** that compile-time option is omitted. Index: src/main.c ================================================================== --- src/main.c +++ src/main.c @@ -3212,20 +3212,22 @@ sqlite3GlobalConfig.xSqllog(pArg, db, zFilename, 0); } #endif #if defined(SQLITE_HAS_CODEC) if( rc==SQLITE_OK ){ - const char *zHexKey = sqlite3_uri_parameter(zOpen, "hexkey"); - if( zHexKey && zHexKey[0] ){ + const char *zKey; + if( (zKey = sqlite3_uri_parameter(zOpen, "hexkey"))!=0 && zKey[0] ){; u8 iByte; int i; - char zKey[40]; - for(i=0, iByte=0; isubjInMemory==0); +#endif assert( (isMainJrnl&~1)==0 ); /* isMainJrnl is 0 or 1 */ assert( (isSavepnt&~1)==0 ); /* isSavepnt is 0 or 1 */ assert( isMainJrnl || pDone ); /* pDone always used on sub-journals */ assert( isSavepnt || pDone==0 ); /* pDone never used on non-savepoint */ @@ -2379,18 +2384,38 @@ && isSynced ){ i64 ofst = (pgno-1)*(i64)pPager->pageSize; testcase( !isSavepnt && pPg!=0 && (pPg->flags&PGHDR_NEED_SYNC)!=0 ); assert( !pagerUseWal(pPager) ); + + /* Write the data read from the journal back into the database file. + ** This is usually safe even for an encrypted database - as the data + ** was encrypted before it was written to the journal file. The exception + ** is if the data was just read from an in-memory sub-journal. In that + ** case it must be encrypted here before it is copied into the database + ** file. */ +#ifdef SQLITE_HAS_CODEC + if( !jrnlEnc ){ + CODEC2(pPager, aData, pgno, 7, rc=SQLITE_NOMEM_BKPT, aData); + rc = sqlite3OsWrite(pPager->fd, (u8 *)aData, pPager->pageSize, ofst); + CODEC1(pPager, aData, pgno, 3, rc=SQLITE_NOMEM_BKPT); + }else +#endif rc = sqlite3OsWrite(pPager->fd, (u8 *)aData, pPager->pageSize, ofst); + if( pgno>pPager->dbFileSize ){ pPager->dbFileSize = pgno; } if( pPager->pBackup ){ - CODEC1(pPager, aData, pgno, 3, rc=SQLITE_NOMEM_BKPT); +#ifdef SQLITE_HAS_CODEC + if( jrnlEnc ){ + CODEC1(pPager, aData, pgno, 3, rc=SQLITE_NOMEM_BKPT); + sqlite3BackupUpdate(pPager->pBackup, pgno, (u8*)aData); + CODEC2(pPager, aData, pgno, 7, rc=SQLITE_NOMEM_BKPT,aData); + }else +#endif sqlite3BackupUpdate(pPager->pBackup, pgno, (u8*)aData); - CODEC2(pPager, aData, pgno, 7, rc=SQLITE_NOMEM_BKPT, aData); } }else if( !isMainJrnl && pPg==0 ){ /* If this is a rollback of a savepoint and data was not written to ** the database and the page is not in-memory, there is a potential ** problem. When the page is next fetched by the b-tree layer, it @@ -2438,11 +2463,13 @@ if( pgno==1 ){ memcpy(&pPager->dbFileVers, &((u8*)pData)[24],sizeof(pPager->dbFileVers)); } /* Decode the page just read from disk */ - CODEC1(pPager, pData, pPg->pgno, 3, rc=SQLITE_NOMEM_BKPT); +#if SQLITE_HAS_CODEC + if( jrnlEnc ){ CODEC1(pPager, pData, pPg->pgno, 3, rc=SQLITE_NOMEM_BKPT); } +#endif sqlite3PcacheRelease(pPg); } return rc; } @@ -4462,12 +4489,17 @@ ** write the journal record into the file. */ if( rc==SQLITE_OK ){ void *pData = pPg->pData; i64 offset = (i64)pPager->nSubRec*(4+pPager->pageSize); char *pData2; - - CODEC2(pPager, pData, pPg->pgno, 7, return SQLITE_NOMEM_BKPT, pData2); + +#if SQLITE_HAS_CODEC + if( !pPager->subjInMemory ){ + CODEC2(pPager, pData, pPg->pgno, 7, return SQLITE_NOMEM_BKPT, pData2); + }else +#endif + pData2 = pData; PAGERTRACE(("STMT-JOURNAL %d page %d\n", PAGERID(pPager), pPg->pgno)); rc = write32bits(pPager->sjfd, offset, pPg->pgno); if( rc==SQLITE_OK ){ rc = sqlite3OsWrite(pPager->sjfd, pData2, pPager->pageSize, offset+4); } Index: src/select.c ================================================================== --- src/select.c +++ src/select.c @@ -4876,10 +4876,107 @@ } } #else # define explainSimpleCount(a,b,c) #endif + +/* +** Context object for havingToWhereExprCb(). +*/ +struct HavingToWhereCtx { + Expr **ppWhere; + ExprList *pGroupBy; +}; + +/* +** sqlite3WalkExpr() callback used by havingToWhere(). +** +** If the node passed to the callback is a TK_AND node, return +** WRC_Continue to tell sqlite3WalkExpr() to iterate through child nodes. +** +** Otherwise, return WRC_Prune. In this case, also check if the +** sub-expression matches the criteria for being moved to the WHERE +** clause. If so, add it to the WHERE clause and replace the sub-expression +** within the HAVING expression with a constant "1". +*/ +static int havingToWhereExprCb(Walker *pWalker, Expr *pExpr){ + if( pExpr->op!=TK_AND ){ + struct HavingToWhereCtx *p = pWalker->u.pHavingCtx; + if( sqlite3ExprIsConstantOrGroupBy(pWalker->pParse, pExpr, p->pGroupBy) ){ + sqlite3 *db = pWalker->pParse->db; + Expr *pNew = sqlite3ExprAlloc(db, TK_INTEGER, &sqlite3IntTokens[1], 0); + if( pNew ){ + Expr *pWhere = *(p->ppWhere); + SWAP(Expr, *pNew, *pExpr); + pNew = sqlite3ExprAnd(db, pWhere, pNew); + *(p->ppWhere) = pNew; + } + } + return WRC_Prune; + } + return WRC_Continue; +} + +/* +** Transfer eligible terms from the HAVING clause of a query, which is +** processed after grouping, to the WHERE clause, which is processed before +** grouping. For example, the query: +** +** SELECT * FROM WHERE a=? GROUP BY b HAVING b=? AND c=? +** +** can be rewritten as: +** +** SELECT * FROM WHERE a=? AND b=? GROUP BY b HAVING c=? +** +** A term of the HAVING expression is eligible for transfer if it consists +** entirely of constants and expressions that are also GROUP BY terms that +** use the "BINARY" collation sequence. +*/ +static void havingToWhere( + Parse *pParse, + ExprList *pGroupBy, + Expr *pHaving, + Expr **ppWhere +){ + struct HavingToWhereCtx sCtx; + Walker sWalker; + + sCtx.ppWhere = ppWhere; + sCtx.pGroupBy = pGroupBy; + + memset(&sWalker, 0, sizeof(sWalker)); + sWalker.pParse = pParse; + sWalker.xExprCallback = havingToWhereExprCb; + sWalker.u.pHavingCtx = &sCtx; + sqlite3WalkExpr(&sWalker, pHaving); +} + +/* +** Check to see if the pThis entry of pTabList is a self-join of a prior view. +** If it is, then return the SrcList_item for the prior view. If it is not, +** then return 0. +*/ +static struct SrcList_item *isSelfJoinView( + SrcList *pTabList, /* Search for self-joins in this FROM clause */ + struct SrcList_item *pThis /* Search for prior reference to this subquery */ +){ + struct SrcList_item *pItem; + for(pItem = pTabList->a; pItempSelect==0 ) continue; + if( pItem->fg.viaCoroutine ) continue; + if( pItem->zName==0 ) continue; + if( sqlite3_stricmp(pItem->zDatabase, pThis->zDatabase)!=0 ) continue; + if( sqlite3_stricmp(pItem->zName, pThis->zName)!=0 ) continue; + if( sqlite3ExprCompare(pThis->pSelect->pWhere, pItem->pSelect->pWhere, -1) ){ + /* The view was modified by some other optimization such as + ** pushDownWhereTerms() */ + continue; + } + return pItem; + } + return 0; +} /* ** Generate code for the SELECT statement given in the p argument. ** ** The results are returned according to the SelectDest structure. @@ -5016,17 +5113,42 @@ #endif return rc; } #endif - /* Generate code for all sub-queries in the FROM clause + /* For each term in the FROM clause, do two things: + ** (1) Authorized unreferenced tables + ** (2) Generate code for all sub-queries */ -#if !defined(SQLITE_OMIT_SUBQUERY) || !defined(SQLITE_OMIT_VIEW) for(i=0; inSrc; i++){ struct SrcList_item *pItem = &pTabList->a[i]; SelectDest dest; - Select *pSub = pItem->pSelect; + Select *pSub; + + /* Issue SQLITE_READ authorizations with a fake column name for any tables that + ** are referenced but from which no values are extracted. Examples of where these + ** kinds of null SQLITE_READ authorizations would occur: + ** + ** SELECT count(*) FROM t1; -- SQLITE_READ t1."" + ** SELECT t1.* FROM t1, t2; -- SQLITE_READ t2."" + ** + ** The fake column name is an empty string. It is possible for a table to + ** have a column named by the empty string, in which case there is no way to + ** distinguish between an unreferenced table and an actual reference to the + ** "" column. The original design was for the fake column name to be a NULL, + ** which would be unambiguous. But legacy authorization callbacks might + ** assume the column name is non-NULL and segfault. The use of an empty string + ** for the fake column name seems safer. + */ + if( pItem->colUsed==0 ){ + sqlite3AuthCheck(pParse, SQLITE_READ, pItem->zName, "", pItem->zDatabase); + } + +#if !defined(SQLITE_OMIT_SUBQUERY) || !defined(SQLITE_OMIT_VIEW) + /* Generate code for all sub-queries in the FROM clause + */ + pSub = pItem->pSelect; if( pSub==0 ) continue; /* Sometimes the code for a subquery will be generated more than ** once, if the subquery is part of the WHERE clause in a LEFT JOIN, ** for example. In that case, do not regenerate the code to manifest @@ -5033,10 +5155,14 @@ ** a view or the co-routine to implement a view. The first instance ** is sufficient, though the subroutine to manifest the view does need ** to be invoked again. */ if( pItem->addrFillSub ){ if( pItem->fg.viaCoroutine==0 ){ + /* The subroutine that manifests the view might be a one-time routine, + ** or it might need to be rerun on each iteration because it + ** encodes a correlated subquery. */ + testcase( sqlite3VdbeGetOp(v, pItem->addrFillSub)->opcode==OP_Once ); sqlite3VdbeAddOp2(v, OP_Gosub, pItem->regReturn, pItem->addrFillSub); } continue; } @@ -5107,10 +5233,12 @@ ** is a register allocated to hold the subroutine return address */ int topAddr; int onceAddr = 0; int retAddr; + struct SrcList_item *pPrior; + assert( pItem->addrFillSub==0 ); pItem->regReturn = ++pParse->nMem; topAddr = sqlite3VdbeAddOp2(v, OP_Integer, 0, pItem->regReturn); pItem->addrFillSub = topAddr+1; if( pItem->fg.isCorrelated==0 ){ @@ -5120,24 +5248,29 @@ onceAddr = sqlite3VdbeAddOp0(v, OP_Once); VdbeCoverage(v); VdbeComment((v, "materialize \"%s\"", pItem->pTab->zName)); }else{ VdbeNoopComment((v, "materialize \"%s\"", pItem->pTab->zName)); } - sqlite3SelectDestInit(&dest, SRT_EphemTab, pItem->iCursor); - explainSetInteger(pItem->iSelectId, (u8)pParse->iNextSelectId); - sqlite3Select(pParse, pSub, &dest); + pPrior = isSelfJoinView(pTabList, pItem); + if( pPrior ){ + sqlite3VdbeAddOp2(v, OP_OpenDup, pItem->iCursor, pPrior->iCursor); + }else{ + sqlite3SelectDestInit(&dest, SRT_EphemTab, pItem->iCursor); + explainSetInteger(pItem->iSelectId, (u8)pParse->iNextSelectId); + sqlite3Select(pParse, pSub, &dest); + } pItem->pTab->nRowLogEst = pSub->nSelectRow; if( onceAddr ) sqlite3VdbeJumpHere(v, onceAddr); retAddr = sqlite3VdbeAddOp1(v, OP_Return, pItem->regReturn); VdbeComment((v, "end %s", pItem->pTab->zName)); sqlite3VdbeChangeP1(v, topAddr, retAddr); sqlite3ClearTempRegCache(pParse); } if( db->mallocFailed ) goto select_end; pParse->nHeight -= sqlite3SelectExprHeight(p); - } #endif + } /* Various elements of the SELECT copied into local variables for ** convenience */ pEList = p->pEList; pWhere = p->pWhere; @@ -5341,10 +5474,15 @@ sAggInfo.nSortingColumn = pGroupBy ? pGroupBy->nExpr : 0; sAggInfo.pGroupBy = pGroupBy; sqlite3ExprAnalyzeAggList(&sNC, pEList); sqlite3ExprAnalyzeAggList(&sNC, sSort.pOrderBy); if( pHaving ){ + if( pGroupBy ){ + assert( pWhere==p->pWhere ); + havingToWhere(pParse, pGroupBy, pHaving, &p->pWhere); + pWhere = p->pWhere; + } sqlite3ExprAnalyzeAggregates(&sNC, pHaving); } sAggInfo.nAccumulator = sAggInfo.nColumn; for(i=0; isizeof(zBuf)/3 ) aw = sizeof(zBuf)/3; + if( aw>(int)sizeof(zBuf)/3 ) aw = (int)sizeof(zBuf)/3; for(i=n=0; zUtf[i]; i++){ if( (zUtf[i]&0xc0)!=0x80 ){ n++; if( n==aw ){ do{ i++; }while( (zUtf[i]&0xc0)==0x80 ); @@ -741,10 +741,14 @@ } u; unsigned nRate; /* Bytes of input accepted per Keccak iteration */ unsigned nLoaded; /* Input bytes loaded into u.x[] so far this cycle */ unsigned ixMask; /* Insert next input into u.x[nLoaded^ixMask]. */ }; + +/* Allow the following routine to use the B0 variable, which is also +** a macro in the termios.h header file */ +#undef B0 /* ** A single step of the Keccak mixing function for a 1600-bit state */ static void KeccakF1600Step(SHA3Context *p){ Index: src/sqlite.h.in ================================================================== --- src/sqlite.h.in +++ src/sqlite.h.in @@ -856,11 +856,11 @@ ** of 25 milliseconds before the first retry and with the delay increasing ** by an additional 25 milliseconds with each subsequent retry. This ** opcode allows these two values (10 retries and 25 milliseconds of delay) ** to be adjusted. The values are changed for all database connections ** within the same process. The argument is a pointer to an array of two -** integers where the first integer i the new retry count and the second +** integers where the first integer is the new retry count and the second ** integer is the delay. If either integer is negative, then the setting ** is not changed but instead the prior value of that setting is written ** into the array entry, allowing the current retry settings to be ** interrogated. The zDbName parameter is ignored. ** @@ -2672,10 +2672,11 @@ void sqlite3_randomness(int N, void *P); /* ** CAPI3REF: Compile-Time Authorization Callbacks ** METHOD: sqlite3 +** KEYWORDS: {authorizer callback} ** ** ^This routine registers an authorizer callback with a particular ** [database connection], supplied in the first argument. ** ^The authorizer callback is invoked as SQL statements are being compiled ** by [sqlite3_prepare()] or its variants [sqlite3_prepare_v2()], @@ -2699,20 +2700,26 @@ ** ** ^The first parameter to the authorizer callback is a copy of the third ** parameter to the sqlite3_set_authorizer() interface. ^The second parameter ** to the callback is an integer [SQLITE_COPY | action code] that specifies ** the particular action to be authorized. ^The third through sixth parameters -** to the callback are zero-terminated strings that contain additional -** details about the action to be authorized. +** to the callback are either NULL pointers or zero-terminated strings +** that contain additional details about the action to be authorized. +** Applications must always be prepared to encounter a NULL pointer in any +** of the third through the sixth parameters of the authorization callback. ** ** ^If the action code is [SQLITE_READ] ** and the callback returns [SQLITE_IGNORE] then the ** [prepared statement] statement is constructed to substitute ** a NULL value in place of the table column that would have ** been read if [SQLITE_OK] had been returned. The [SQLITE_IGNORE] ** return can be used to deny an untrusted user access to individual ** columns of a table. +** ^When a table is referenced by a [SELECT] but no column values are +** extracted from that table (for example in a query like +** "SELECT count(*) FROM tab") then the [SQLITE_READ] authorizer callback +** is invoked once for that table with a column name that is an empty string. ** ^If the action code is [SQLITE_DELETE] and the callback returns ** [SQLITE_IGNORE] then the [DELETE] operation proceeds but the ** [truncate optimization] is disabled and all rows are deleted individually. ** ** An authorizer is used when [sqlite3_prepare | preparing] @@ -4755,14 +4762,15 @@ ** metadata associated with the pattern string. ** Then as long as the pattern string remains the same, ** the compiled regular expression can be reused on multiple ** invocations of the same function. ** -** ^The sqlite3_get_auxdata() interface returns a pointer to the metadata -** associated by the sqlite3_set_auxdata() function with the Nth argument -** value to the application-defined function. ^If there is no metadata -** associated with the function argument, this sqlite3_get_auxdata() interface +** ^The sqlite3_get_auxdata(C,N) interface returns a pointer to the metadata +** associated by the sqlite3_set_auxdata(C,N,P,X) function with the Nth argument +** value to the application-defined function. ^N is zero for the left-most +** function argument. ^If there is no metadata +** associated with the function argument, the sqlite3_get_auxdata(C,N) interface ** returns a NULL pointer. ** ** ^The sqlite3_set_auxdata(C,N,P,X) interface saves P as metadata for the N-th ** argument of the application-defined function. ^Subsequent ** calls to sqlite3_get_auxdata(C,N) return P from the most recent @@ -4788,10 +4796,14 @@ ** sqlite3_set_auxdata() has been called. ** ** ^(In practice, metadata is preserved between function calls for ** function parameters that are compile-time constants, including literal ** values and [parameters] and expressions composed from the same.)^ +** +** The value of the N parameter to these interfaces should be non-negative. +** Future enhancements may make use of negative N values to define new +** kinds of function caching behavior. ** ** These routines must be called from the same thread in which ** the SQL function is running. */ void *sqlite3_get_auxdata(sqlite3_context*, int N); Index: src/sqliteInt.h ================================================================== --- src/sqliteInt.h +++ src/sqliteInt.h @@ -3314,19 +3314,21 @@ int (*xSelectCallback)(Walker*,Select*); /* Callback for SELECTs */ void (*xSelectCallback2)(Walker*,Select*);/* Second callback for SELECTs */ int walkerDepth; /* Number of subqueries */ u8 eCode; /* A small processing code */ union { /* Extra data for callback */ - NameContext *pNC; /* Naming context */ - int n; /* A counter */ - int iCur; /* A cursor number */ - SrcList *pSrcList; /* FROM clause */ - struct SrcCount *pSrcCount; /* Counting column references */ - struct CCurHint *pCCurHint; /* Used by codeCursorHint() */ - int *aiCol; /* array of column indexes */ - struct IdxCover *pIdxCover; /* Check for index coverage */ - struct IdxExprTrans *pIdxTrans; /* Convert indexed expr to column */ + NameContext *pNC; /* Naming context */ + int n; /* A counter */ + int iCur; /* A cursor number */ + SrcList *pSrcList; /* FROM clause */ + struct SrcCount *pSrcCount; /* Counting column references */ + struct CCurHint *pCCurHint; /* Used by codeCursorHint() */ + int *aiCol; /* array of column indexes */ + struct IdxCover *pIdxCover; /* Check for index coverage */ + struct IdxExprTrans *pIdxTrans; /* Convert indexed expr to column */ + ExprList *pGroupBy; /* GROUP BY clause */ + struct HavingToWhereCtx *pHavingCtx; /* HAVING to WHERE clause ctx */ } u; }; /* Forward declarations */ int sqlite3WalkExpr(Walker*, Expr*); @@ -3792,10 +3794,11 @@ void sqlite3CloseSavepoints(sqlite3 *); void sqlite3LeaveMutexAndCloseZombie(sqlite3*); int sqlite3ExprIsConstant(Expr*); int sqlite3ExprIsConstantNotJoin(Expr*); int sqlite3ExprIsConstantOrFunction(Expr*, u8); +int sqlite3ExprIsConstantOrGroupBy(Parse*, Expr*, ExprList*); int sqlite3ExprIsTableConstant(Expr*,int); #ifdef SQLITE_ENABLE_CURSOR_HINTS int sqlite3ExprContainsSubquery(Expr*); #endif int sqlite3ExprIsInteger(Expr*, int*); Index: src/tclsqlite.c ================================================================== --- src/tclsqlite.c +++ src/tclsqlite.c @@ -1031,13 +1031,20 @@ ){ const char *zCode; Tcl_DString str; int rc; const char *zReply; + /* EVIDENCE-OF: R-38590-62769 The first parameter to the authorizer + ** callback is a copy of the third parameter to the + ** sqlite3_set_authorizer() interface. + */ SqliteDb *pDb = (SqliteDb*)pArg; if( pDb->disableAuth ) return SQLITE_OK; + /* EVIDENCE-OF: R-56518-44310 The second parameter to the callback is an + ** integer action code that specifies the particular action to be + ** authorized. */ switch( code ){ case SQLITE_COPY : zCode="SQLITE_COPY"; break; case SQLITE_CREATE_INDEX : zCode="SQLITE_CREATE_INDEX"; break; case SQLITE_CREATE_TABLE : zCode="SQLITE_CREATE_TABLE"; break; case SQLITE_CREATE_TEMP_INDEX : zCode="SQLITE_CREATE_TEMP_INDEX"; break; Index: src/test1.c ================================================================== --- src/test1.c +++ src/test1.c @@ -4965,56 +4965,10 @@ if( getDbPointer(interp, argv[1], &db) ) return TCL_ERROR; sqlite3_interrupt(db); return TCL_OK; } -static u8 *sqlite3_stack_baseline = 0; - -/* -** Fill the stack with a known bitpattern. -*/ -static void prepStack(void){ - int i; - u32 bigBuf[65536]; - for(i=0; i=0 && ((u32*)sqlite3_stack_baseline)[-i]==0xdeadbeef; i--){} - Tcl_SetObjResult(interp, Tcl_NewIntObj(i*4)); - return TCL_OK; -} - /* ** Usage: sqlite_delete_function DB function-name ** ** Delete the user function 'function-name' from database handle DB. It ** is assumed that the user function was created as UTF8, any number of @@ -7662,11 +7616,10 @@ { "sqlite_set_magic", (Tcl_CmdProc*)sqlite_set_magic }, { "sqlite3_interrupt", (Tcl_CmdProc*)test_interrupt }, { "sqlite_delete_function", (Tcl_CmdProc*)delete_function }, { "sqlite_delete_collation", (Tcl_CmdProc*)delete_collation }, { "sqlite3_get_autocommit", (Tcl_CmdProc*)get_autocommit }, - { "sqlite3_stack_used", (Tcl_CmdProc*)test_stack_used }, { "sqlite3_busy_timeout", (Tcl_CmdProc*)test_busy_timeout }, { "printf", (Tcl_CmdProc*)test_printf }, { "sqlite3IoTrace", (Tcl_CmdProc*)test_io_trace }, { "clang_sanitize_address", (Tcl_CmdProc*)clang_sanitize_address }, }; Index: src/util.c ================================================================== --- src/util.c +++ src/util.c @@ -711,10 +711,11 @@ }else{ return 0; } } #endif + if( !sqlite3Isdigit(zNum[0]) ) return 0; while( zNum[0]=='0' ) zNum++; for(i=0; i<11 && (c = zNum[i] - '0')>=0 && c<=9; i++){ v = v*10 + c; } Index: src/vdbe.c ================================================================== --- src/vdbe.c +++ src/vdbe.c @@ -763,11 +763,11 @@ case OP_Goto: { /* jump */ jump_to_p2_and_check_for_interrupt: pOp = &aOp[pOp->p2 - 1]; /* Opcodes that are used as the bottom of a loop (OP_Next, OP_Prev, - ** OP_VNext, OP_RowSetNext, or OP_SorterNext) all jump here upon + ** OP_VNext, or OP_SorterNext) all jump here upon ** completion. Check to see if sqlite3_interrupt() has been called ** or if the progress callback needs to be invoked. ** ** This code uses unstructured "goto" statements and does not look clean. ** But that is not due to sloppy coding habits. The code is written this @@ -1566,11 +1566,11 @@ break; } /* Opcode: CollSeq P1 * * P4 ** -** P4 is a pointer to a CollSeq struct. If the next call to a user function +** P4 is a pointer to a CollSeq object. If the next call to a user function ** or aggregate calls sqlite3GetFuncCollSeq(), this collation sequence will ** be returned. This is used by the built-in min(), max() and nullif() ** functions. ** ** If P1 is not zero, then it is a register that a subsequent min() or @@ -1847,15 +1847,15 @@ ** Synopsis: affinity(r[P1]) ** ** Force the value in register P1 to be the type defined by P2. ** **
    -**
  • TEXT -**
  • BLOB -**
  • NUMERIC -**
  • INTEGER -**
  • REAL +**
  • P2=='A' → BLOB +**
  • P2=='B' → TEXT +**
  • P2=='C' → NUMERIC +**
  • P2=='D' → INTEGER +**
  • P2=='E' → REAL **
** ** A NULL value is not changed by this routine. It remains NULL. */ case OP_Cast: { /* in1 */ @@ -2722,12 +2722,12 @@ /* Opcode: Affinity P1 P2 * P4 * ** Synopsis: affinity(r[P1@P2]) ** ** Apply affinities to a range of P2 registers starting with P1. ** -** P4 is a string that is P2 characters long. The nth character of the -** string indicates the column affinity that should be used for the nth +** P4 is a string that is P2 characters long. The N-th character of the +** string indicates the column affinity that should be used for the N-th ** memory cell in the range. */ case OP_Affinity: { const char *zAffinity; /* The affinity to be applied */ @@ -2750,12 +2750,12 @@ ** ** Convert P2 registers beginning with P1 into the [record format] ** use as a data record in a database table or as a key ** in an index. The OP_Column opcode can decode the record later. ** -** P4 may be a string that is P2 characters long. The nth character of the -** string indicates the column affinity that should be used for the nth +** P4 may be a string that is P2 characters long. The N-th character of the +** string indicates the column affinity that should be used for the N-th ** field of the index key. ** ** The mapping from character to affinity is given by the SQLITE_AFF_ ** macros defined in sqliteInt.h. ** @@ -3538,10 +3538,41 @@ sqlite3BtreeCursorHintFlags(pCur->uc.pCursor, (pOp->p5 & (OPFLAG_BULKCSR|OPFLAG_SEEKEQ))); if( rc ) goto abort_due_to_error; break; } + +/* Opcode: OpenDup P1 P2 * * * +** +** Open a new cursor P1 that points to the same ephemeral table as +** cursor P2. The P2 cursor must have been opened by a prior OP_OpenEphemeral +** opcode. Only ephemeral cursors may be duplicated. +** +** Duplicate ephemeral cursors are used for self-joins of materialized views. +*/ +case OP_OpenDup: { + VdbeCursor *pOrig; /* The original cursor to be duplicated */ + VdbeCursor *pCx; /* The new cursor */ + + pOrig = p->apCsr[pOp->p2]; + assert( pOrig->pBtx!=0 ); /* Only ephemeral cursors can be duplicated */ + + pCx = allocateCursor(p, pOp->p1, pOrig->nField, -1, CURTYPE_BTREE); + if( pCx==0 ) goto no_mem; + pCx->nullRow = 1; + pCx->isEphemeral = 1; + pCx->pKeyInfo = pOrig->pKeyInfo; + pCx->isTable = pOrig->isTable; + rc = sqlite3BtreeCursor(pOrig->pBtx, MASTER_ROOT, BTREE_WRCSR, + pCx->pKeyInfo, pCx->uc.pCursor); + /* The sqlite3BtreeCursor() routine can only fail for the first cursor + ** opened for a database. Since there is already an open cursor when this + ** opcode is run, the sqlite3BtreeCursor() cannot fail */ + assert( rc==SQLITE_OK ); + break; +} + /* Opcode: OpenEphemeral P1 P2 * P4 P5 ** Synopsis: nColumn=P2 ** ** Open a new cursor P1 to a transient table. @@ -5747,11 +5778,11 @@ #endif /* SQLITE_OMIT_INTEGRITY_CHECK */ /* Opcode: RowSetAdd P1 P2 * * * ** Synopsis: rowset(P1)=r[P2] ** -** Insert the integer value held by register P2 into a boolean index +** Insert the integer value held by register P2 into a RowSet object ** held in register P1. ** ** An assertion fails if P2 is not an integer. */ case OP_RowSetAdd: { /* in1, in2 */ @@ -5767,12 +5798,13 @@ } /* Opcode: RowSetRead P1 P2 P3 * * ** Synopsis: r[P3]=rowset(P1) ** -** Extract the smallest value from boolean index P1 and put that value into -** register P3. Or, if boolean index P1 is initially empty, leave P3 +** Extract the smallest value from the RowSet object in P1 +** and put that value into register P3. +** Or, if RowSet object P1 is initially empty, leave P3 ** unchanged and jump to instruction P2. */ case OP_RowSetRead: { /* jump, in1, out3 */ i64 val; @@ -5799,19 +5831,18 @@ ** contains a RowSet object and that RowSet object contains ** the value held in P3, jump to register P2. Otherwise, insert the ** integer in P3 into the RowSet and continue on to the ** next opcode. ** -** The RowSet object is optimized for the case where successive sets -** of integers, where each set contains no duplicates. Each set -** of values is identified by a unique P4 value. The first set -** must have P4==0, the final set P4=-1. P4 must be either -1 or -** non-negative. For non-negative values of P4 only the lower 4 -** bits are significant. +** The RowSet object is optimized for the case where sets of integers +** are inserted in distinct phases, which each set contains no duplicates. +** Each set is identified by a unique P4 value. The first set +** must have P4==0, the final set must have P4==-1, and for all other sets +** must have P4>0. ** ** This allows optimizations: (a) when P4==0 there is no need to test -** the rowset object for P3, as it is guaranteed not to contain it, +** the RowSet object for P3, as it is guaranteed not to contain it, ** (b) when P4==-1 there is no need to insert the value, as it will ** never be tested for, and (c) when a value that is part of set X is ** inserted, there is no need to search to see if the same value was ** previously inserted as part of set X (only if it was previously ** inserted as part of some other set). Index: src/vdbeInt.h ================================================================== --- src/vdbeInt.h +++ src/vdbeInt.h @@ -285,15 +285,15 @@ ** of this structure. All such structures associated with a single VM ** are stored in a linked list headed at Vdbe.pAuxData. All are destroyed ** when the VM is halted (if not before). */ struct AuxData { - int iOp; /* Instruction number of OP_Function opcode */ - int iArg; /* Index of function argument. */ + int iAuxOp; /* Instruction number of OP_Function opcode */ + int iAuxArg; /* Index of function argument. */ void *pAux; /* Aux data pointer */ - void (*xDelete)(void *); /* Destructor for the aux data */ - AuxData *pNext; /* Next element in list */ + void (*xDeleteAux)(void*); /* Destructor for the aux data */ + AuxData *pNextAux; /* Next element in list */ }; /* ** The "context" argument for an installable function. A pointer to an ** instance of this structure is the first argument to the routines used Index: src/vdbeapi.c ================================================================== --- src/vdbeapi.c +++ src/vdbeapi.c @@ -818,10 +818,16 @@ } /* ** Return the auxiliary data pointer, if any, for the iArg'th argument to ** the user-function defined by pCtx. +** +** The left-most argument is 0. +** +** Undocumented behavior: If iArg is negative then access a cache of +** auxiliary data pointers that is available to all functions within a +** single prepared statement. The iArg values must match. */ void *sqlite3_get_auxdata(sqlite3_context *pCtx, int iArg){ AuxData *pAuxData; assert( sqlite3_mutex_held(pCtx->pOut->db->mutex) ); @@ -828,21 +834,28 @@ #if SQLITE_ENABLE_STAT3_OR_STAT4 if( pCtx->pVdbe==0 ) return 0; #else assert( pCtx->pVdbe!=0 ); #endif - for(pAuxData=pCtx->pVdbe->pAuxData; pAuxData; pAuxData=pAuxData->pNext){ - if( pAuxData->iOp==pCtx->iOp && pAuxData->iArg==iArg ) break; + for(pAuxData=pCtx->pVdbe->pAuxData; pAuxData; pAuxData=pAuxData->pNextAux){ + if( pAuxData->iAuxArg==iArg && (pAuxData->iAuxOp==pCtx->iOp || iArg<0) ){ + return pAuxData->pAux; + } } - - return (pAuxData ? pAuxData->pAux : 0); + return 0; } /* ** Set the auxiliary data pointer and delete function, for the iArg'th ** argument to the user-function defined by pCtx. Any previous value is ** deleted by calling the delete function specified when it was set. +** +** The left-most argument is 0. +** +** Undocumented behavior: If iArg is negative then make the data available +** to all functions within the current prepared statement using iArg as an +** access code. */ void sqlite3_set_auxdata( sqlite3_context *pCtx, int iArg, void *pAux, @@ -850,37 +863,38 @@ ){ AuxData *pAuxData; Vdbe *pVdbe = pCtx->pVdbe; assert( sqlite3_mutex_held(pCtx->pOut->db->mutex) ); - if( iArg<0 ) goto failed; #ifdef SQLITE_ENABLE_STAT3_OR_STAT4 if( pVdbe==0 ) goto failed; #else assert( pVdbe!=0 ); #endif - for(pAuxData=pVdbe->pAuxData; pAuxData; pAuxData=pAuxData->pNext){ - if( pAuxData->iOp==pCtx->iOp && pAuxData->iArg==iArg ) break; + for(pAuxData=pVdbe->pAuxData; pAuxData; pAuxData=pAuxData->pNextAux){ + if( pAuxData->iAuxArg==iArg && (pAuxData->iAuxOp==pCtx->iOp || iArg<0) ){ + break; + } } if( pAuxData==0 ){ pAuxData = sqlite3DbMallocZero(pVdbe->db, sizeof(AuxData)); if( !pAuxData ) goto failed; - pAuxData->iOp = pCtx->iOp; - pAuxData->iArg = iArg; - pAuxData->pNext = pVdbe->pAuxData; + pAuxData->iAuxOp = pCtx->iOp; + pAuxData->iAuxArg = iArg; + pAuxData->pNextAux = pVdbe->pAuxData; pVdbe->pAuxData = pAuxData; if( pCtx->fErrorOrAux==0 ){ pCtx->isError = 0; pCtx->fErrorOrAux = 1; } - }else if( pAuxData->xDelete ){ - pAuxData->xDelete(pAuxData->pAux); + }else if( pAuxData->xDeleteAux ){ + pAuxData->xDeleteAux(pAuxData->pAux); } pAuxData->pAux = pAux; - pAuxData->xDelete = xDelete; + pAuxData->xDeleteAux = xDelete; return; failed: if( xDelete ){ xDelete(pAux); Index: src/vdbeaux.c ================================================================== --- src/vdbeaux.c +++ src/vdbeaux.c @@ -2033,12 +2033,12 @@ case CURTYPE_SORTER: { sqlite3VdbeSorterClose(p->db, pCx); break; } case CURTYPE_BTREE: { - if( pCx->pBtx ){ - sqlite3BtreeClose(pCx->pBtx); + if( pCx->isEphemeral ){ + if( pCx->pBtx ) sqlite3BtreeClose(pCx->pBtx); /* The pCx->pCursor will be close automatically, if it exists, by ** the call above. */ }else{ assert( pCx->uc.pCursor!=0 ); sqlite3BtreeCloseCursor(pCx->uc.pCursor); @@ -2966,20 +2966,22 @@ */ void sqlite3VdbeDeleteAuxData(sqlite3 *db, AuxData **pp, int iOp, int mask){ while( *pp ){ AuxData *pAux = *pp; if( (iOp<0) - || (pAux->iOp==iOp && (pAux->iArg>31 || !(mask & MASKBIT32(pAux->iArg)))) + || (pAux->iAuxOp==iOp + && pAux->iAuxArg>=0 + && (pAux->iAuxArg>31 || !(mask & MASKBIT32(pAux->iAuxArg)))) ){ - testcase( pAux->iArg==31 ); - if( pAux->xDelete ){ - pAux->xDelete(pAux->pAux); + testcase( pAux->iAuxArg==31 ); + if( pAux->xDeleteAux ){ + pAux->xDeleteAux(pAux->pAux); } - *pp = pAux->pNext; + *pp = pAux->pNextAux; sqlite3DbFree(db, pAux); }else{ - pp= &pAux->pNext; + pp= &pAux->pNextAux; } } } /* Index: src/wherecode.c ================================================================== --- src/wherecode.c +++ src/wherecode.c @@ -1127,10 +1127,12 @@ int addrBrk; /* Jump here to break out of the loop */ int addrHalt; /* addrBrk for the outermost loop */ int addrCont; /* Jump here to continue with next cycle */ int iRowidReg = 0; /* Rowid is stored in this register, if not zero */ int iReleaseReg = 0; /* Temp register to free before returning */ + Index *pIdx = 0; /* Index used by loop (if any) */ + int loopAgain; /* True if constraint generator loop should repeat */ pParse = pWInfo->pParse; v = pParse->pVdbe; pWC = &pWInfo->sWC; db = pParse->db; @@ -1452,11 +1454,10 @@ WhereTerm *pRangeEnd = 0; /* Inequality constraint at range end */ int startEq; /* True if range start uses ==, >= or <= */ int endEq; /* True if range end uses ==, >= or <= */ int start_constraints; /* Start of range is constrained */ int nConstraint; /* Number of constraint terms */ - Index *pIdx; /* The index we will be using */ int iIdxCur; /* The VDBE cursor for the index */ int nExtraReg = 0; /* Number of extra registers needed */ int op; /* Instruction opcode */ char *zStartAff; /* Affinity for start of range constraint */ char *zEndAff = 0; /* Affinity for end of range constraint */ @@ -1703,10 +1704,11 @@ if( (pLoop->wsFlags & WHERE_CONSTRAINT)==0 ){ pLevel->p5 = SQLITE_STMTSTATUS_FULLSCAN_STEP; }else{ assert( pLevel->p5==0 ); } + if( omitTable ) pIdx = 0; }else #ifndef SQLITE_OMIT_OR_OPTIMIZATION if( pLoop->wsFlags & WHERE_MULTI_OR ){ /* Case 5: Two or more separately indexed terms connected by OR @@ -2020,47 +2022,60 @@ pLevel->addrVisit = sqlite3VdbeCurrentAddr(v); #endif /* Insert code to test every subexpression that can be completely ** computed using the current set of tables. + ** + ** This loop may run either once (pIdx==0) or twice (pIdx!=0). If + ** it is run twice, then the first iteration codes those sub-expressions + ** that can be computed using columns from pIdx only (without seeking + ** the main table cursor). */ - for(pTerm=pWC->a, j=pWC->nTerm; j>0; j--, pTerm++){ - Expr *pE; - int skipLikeAddr = 0; - testcase( pTerm->wtFlags & TERM_VIRTUAL ); - testcase( pTerm->wtFlags & TERM_CODED ); - if( pTerm->wtFlags & (TERM_VIRTUAL|TERM_CODED) ) continue; - if( (pTerm->prereqAll & pLevel->notReady)!=0 ){ - testcase( pWInfo->untestedTerms==0 - && (pWInfo->wctrlFlags & WHERE_OR_SUBCLAUSE)!=0 ); - pWInfo->untestedTerms = 1; - continue; - } - pE = pTerm->pExpr; - assert( pE!=0 ); - if( pLevel->iLeftJoin && !ExprHasProperty(pE, EP_FromJoin) ){ - continue; - } - if( pTerm->wtFlags & TERM_LIKECOND ){ - /* If the TERM_LIKECOND flag is set, that means that the range search - ** is sufficient to guarantee that the LIKE operator is true, so we - ** can skip the call to the like(A,B) function. But this only works - ** for strings. So do not skip the call to the function on the pass - ** that compares BLOBs. */ + do{ + loopAgain = 0; + for(pTerm=pWC->a, j=pWC->nTerm; j>0; j--, pTerm++){ + Expr *pE; + int skipLikeAddr = 0; + testcase( pTerm->wtFlags & TERM_VIRTUAL ); + testcase( pTerm->wtFlags & TERM_CODED ); + if( pTerm->wtFlags & (TERM_VIRTUAL|TERM_CODED) ) continue; + if( (pTerm->prereqAll & pLevel->notReady)!=0 ){ + testcase( pWInfo->untestedTerms==0 + && (pWInfo->wctrlFlags & WHERE_OR_SUBCLAUSE)!=0 ); + pWInfo->untestedTerms = 1; + continue; + } + pE = pTerm->pExpr; + assert( pE!=0 ); + if( pLevel->iLeftJoin && !ExprHasProperty(pE, EP_FromJoin) ){ + continue; + } + if( pIdx && !sqlite3ExprCoveredByIndex(pE, pLevel->iTabCur, pIdx) ){ + loopAgain = 1; + continue; + } + if( pTerm->wtFlags & TERM_LIKECOND ){ + /* If the TERM_LIKECOND flag is set, that means that the range search + ** is sufficient to guarantee that the LIKE operator is true, so we + ** can skip the call to the like(A,B) function. But this only works + ** for strings. So do not skip the call to the function on the pass + ** that compares BLOBs. */ #ifdef SQLITE_LIKE_DOESNT_MATCH_BLOBS - continue; + continue; #else - u32 x = pLevel->iLikeRepCntr; - assert( x>0 ); - skipLikeAddr = sqlite3VdbeAddOp1(v, (x&1)? OP_IfNot : OP_If, (int)(x>>1)); - VdbeCoverage(v); + u32 x = pLevel->iLikeRepCntr; + assert( x>0 ); + skipLikeAddr = sqlite3VdbeAddOp1(v, (x&1)?OP_IfNot:OP_If, (int)(x>>1)); + VdbeCoverage(v); #endif + } + sqlite3ExprIfFalse(pParse, pE, addrCont, SQLITE_JUMPIFNULL); + if( skipLikeAddr ) sqlite3VdbeJumpHere(v, skipLikeAddr); + pTerm->wtFlags |= TERM_CODED; } - sqlite3ExprIfFalse(pParse, pE, addrCont, SQLITE_JUMPIFNULL); - if( skipLikeAddr ) sqlite3VdbeJumpHere(v, skipLikeAddr); - pTerm->wtFlags |= TERM_CODED; - } + pIdx = 0; + }while( loopAgain ); /* Insert code to test for implied constraints based on transitivity ** of the "==" operator. ** ** Example: If the WHERE clause contains "t1.a=t2.b" and "t2.b=123" Index: test/auth.test ================================================================== --- test/auth.test +++ test/auth.test @@ -34,16 +34,24 @@ } do_test auth-1.1.1 { db close set ::DB [sqlite3 db test.db] + proc authx {code arg1 arg2 arg3 arg4 args} {return SQLITE_DENY} proc auth {code arg1 arg2 arg3 arg4 args} { if {$code=="SQLITE_INSERT" && $arg1=="sqlite_master"} { return SQLITE_DENY } return SQLITE_OK } + db authorizer ::authx + # EVIDENCE-OF: R-03993-24285 Only a single authorizer can be in place on + # a database connection at a time. Each call to sqlite3_set_authorizer + # overrides the previous call. + # + # The authx authorizer above is overridden by the auth authorizer below + # authx is never invoked. db authorizer ::auth catchsql {CREATE TABLE t1(a,b,c)} } {1 {not authorized}} do_test auth-1.1.2 { db errorcode @@ -58,10 +66,13 @@ } } {1 {no such column: x}} do_test auth-1.2 { execsql {SELECT name FROM sqlite_master} } {} +# EVIDENCE-OF: R-04452-49349 When the callback returns SQLITE_DENY, the +# sqlite3_prepare_v2() or equivalent call that triggered the authorizer +# will fail with an error message explaining that access is denied. do_test auth-1.3.1 { proc auth {code arg1 arg2 arg3 arg4 args} { if {$code=="SQLITE_CREATE_TABLE"} { set ::authargs [list $arg1 $arg2 $arg3 $arg4] return SQLITE_DENY @@ -310,10 +321,14 @@ execsql {ATTACH DATABASE 'test.db' AS two} catchsql {SELECT * FROM two.t2} } {1 {access to two.t2.b is prohibited}} execsql {DETACH DATABASE two} } +# EVIDENCE-OF: R-38392-49970 If the action code is SQLITE_READ and the +# callback returns SQLITE_IGNORE then the prepared statement statement +# is constructed to substitute a NULL value in place of the table column +# that would have been read if SQLITE_OK had been returned. do_test auth-1.36 { proc auth {code arg1 arg2 arg3 arg4 args} { if {$code=="SQLITE_READ" && $arg1=="t2" && $arg2=="b"} { return SQLITE_IGNORE } @@ -1604,10 +1619,12 @@ } {1 {not authorized}} do_test auth-1.248 { set ::authargs } {COMMIT {} {} {}} do_test auth-1.249 { + # EVIDENCE-OF: R-52112-44167 Disable the authorizer by installing a NULL + # callback. db authorizer {} catchsql {ROLLBACK} } {0 {}} do_test auth-1.250 { execsql {SELECT * FROM t2} @@ -2476,9 +2493,38 @@ SQLITE_SELECT {} {} {} {} \ SQLITE_READ t7 a main {} \ SQLITE_READ t7 c main {} \ ] +# If a table is referenced but no columns are read from the table, +# that causes a single SQLITE_READ authorization with a NULL column +# name. +# +# EVIDENCE-OF: R-31520-16302 When a table is referenced by a SELECT but +# no column values are extracted from that table (for example in a query +# like "SELECT count(*) FROM tab") then the SQLITE_READ authorizer +# callback is invoked once for that table with a column name that is an +# empty string. +# +set ::authargs [list] +do_test auth-8.1 { + execsql {SELECT count(*) FROM t7} + set ::authargs +} [list \ + SQLITE_SELECT {} {} {} {} \ + SQLITE_FUNCTION {} count {} {} \ + SQLITE_READ t7 {} {} {} \ + ] +set ::authargs [list] + +do_test auth-8.2 { + execsql {SELECT t6.a FROM t6, t7} + set ::authargs +} [list \ + SQLITE_SELECT {} {} {} {} \ + SQLITE_READ t6 a main {} \ + SQLITE_READ t7 {} {} {} \ + ] rename proc {} rename proc_real proc finish_test Index: test/auth3.test ================================================================== --- test/auth3.test +++ test/auth3.test @@ -51,10 +51,14 @@ } {} do_test auth3.1.2 { set ::authcode SQLITE_DENY catchsql { DELETE FROM t1 } } {1 {not authorized}} +# EVIDENCE-OF: R-64962-58611 If the authorizer callback returns any +# value other than SQLITE_IGNORE, SQLITE_OK, or SQLITE_DENY then the +# sqlite3_prepare_v2() or equivalent call that triggered the authorizer +# will fail with an error message. do_test auth3.1.3 { set ::authcode SQLITE_INVALID catchsql { DELETE FROM t1 } } {1 {authorizer malfunction}} do_test auth3.1.4 { ADDED test/cachespill.test Index: test/cachespill.test ================================================================== --- test/cachespill.test +++ test/cachespill.test @@ -0,0 +1,77 @@ +# 2017 April 26 +# +# The author disclaims copyright to this source code. In place of +# a legal notice, here is a blessing: +# +# May you do good and not evil. +# May you find forgiveness for yourself and forgive others. +# May you share freely, never taking more than you give. +# +#*********************************************************************** +# + +set testdir [file dirname $argv0] +source $testdir/tester.tcl +set testprefix cachespill + +ifcapable !pager_pragmas { + finish_test + return +} + +#------------------------------------------------------------------------- +# Test that "PRAGMA cache_spill = 0" completely disables cache spilling. +# +do_execsql_test 1.1 { + PRAGMA auto_vacuum = 0; + PRAGMA page_size = 1024; + PRAGMA cache_size = 100; + CREATE TABLE t1(a); +} + +do_test 1.2 { + file size test.db +} {2048} + +do_test 1.3 { + execsql { + BEGIN; + WITH s(i) AS ( + SELECT 1 UNION ALL SELECT i+1 FROM s WHERE i<200 + ) INSERT INTO t1 SELECT randomblob(900) FROM s; + } + expr {[file size test.db] > 50000} +} {1} + +do_test 1.4 { + execsql ROLLBACK + file size test.db +} {2048} + +do_test 1.5 { + execsql { + PRAGMA cache_spill = 0; + BEGIN; + WITH s(i) AS ( + SELECT 1 UNION ALL SELECT i+1 FROM s WHERE i<200 + ) INSERT INTO t1 SELECT randomblob(900) FROM s; + } + file size test.db +} {2048} + +do_test 1.5 { + execsql { + ROLLBACK; + PRAGMA cache_spill = 1; + BEGIN; + WITH s(i) AS ( + SELECT 1 UNION ALL SELECT i+1 FROM s WHERE i<200 + ) INSERT INTO t1 SELECT randomblob(900) FROM s; + } + expr {[file size test.db] > 50000} +} {1} + +do_execsql_test 1.6 { ROLLBACK } + + +finish_test Index: test/conflict3.test ================================================================== --- test/conflict3.test +++ test/conflict3.test @@ -17,17 +17,18 @@ # IGNORE, and FAIL conflict resolution play well together. # set testdir [file dirname $argv0] source $testdir/tester.tcl +set testprefix conflict3 ifcapable !conflict { finish_test return } -do_execsql_test conflict-1.1 { +do_execsql_test 1.1 { CREATE TABLE t1( a INTEGER PRIMARY KEY ON CONFLICT REPLACE, b UNIQUE ON CONFLICT IGNORE, c UNIQUE ON CONFLICT FAIL ); @@ -35,28 +36,28 @@ SELECT a,b,c FROM t1 ORDER BY a; } {1 2 3 2 3 4} # Insert a row that conflicts on column B. The insert should be ignored. # -do_execsql_test conflict-1.2 { +do_execsql_test 1.2 { INSERT INTO t1(a,b,c) VALUES(3,2,5); SELECT a,b,c FROM t1 ORDER BY a; } {1 2 3 2 3 4} # Insert two rows where the second conflicts on C. The first row show go # and and then there should be a constraint error. # -do_test conflict-1.3 { +do_test 1.3 { catchsql {INSERT INTO t1(a,b,c) VALUES(4,5,6), (5,6,4);} } {1 {UNIQUE constraint failed: t1.c}} -do_execsql_test conflict-1.4 { +do_execsql_test 1.4 { SELECT a,b,c FROM t1 ORDER BY a; } {1 2 3 2 3 4 4 5 6} # Replete the tests above, but this time on a table non-INTEGER primary key. # -do_execsql_test conflict-2.1 { +do_execsql_test 2.1 { DROP TABLE t1; CREATE TABLE t1( a INT PRIMARY KEY ON CONFLICT REPLACE, b UNIQUE ON CONFLICT IGNORE, c UNIQUE ON CONFLICT FAIL @@ -65,28 +66,28 @@ SELECT a,b,c FROM t1 ORDER BY a; } {1 2 3 2 3 4} # Insert a row that conflicts on column B. The insert should be ignored. # -do_execsql_test conflict-2.2 { +do_execsql_test 2.2 { INSERT INTO t1(a,b,c) VALUES(3,2,5); SELECT a,b,c FROM t1 ORDER BY a; } {1 2 3 2 3 4} # Insert two rows where the second conflicts on C. The first row show go # and and then there should be a constraint error. # -do_test conflict-2.3 { +do_test 2.3 { catchsql {INSERT INTO t1(a,b,c) VALUES(4,5,6), (5,6,4);} } {1 {UNIQUE constraint failed: t1.c}} -do_execsql_test conflict-2.4 { +do_execsql_test 2.4 { SELECT a,b,c FROM t1 ORDER BY a; } {1 2 3 2 3 4 4 5 6} # Replete again on a WITHOUT ROWID table. # -do_execsql_test conflict-3.1 { +do_execsql_test 3.1 { DROP TABLE t1; CREATE TABLE t1( a INT PRIMARY KEY ON CONFLICT REPLACE, b UNIQUE ON CONFLICT IGNORE, c UNIQUE ON CONFLICT FAIL @@ -95,28 +96,28 @@ SELECT a,b,c FROM t1 ORDER BY a; } {1 2 3 2 3 4} # Insert a row that conflicts on column B. The insert should be ignored. # -do_execsql_test conflict-3.2 { +do_execsql_test 3.2 { INSERT INTO t1(a,b,c) VALUES(3,2,5); SELECT a,b,c FROM t1 ORDER BY a; } {1 2 3 2 3 4} # Insert two rows where the second conflicts on C. The first row show go # and and then there should be a constraint error. # -do_test conflict-3.3 { +do_test 3.3 { catchsql {INSERT INTO t1(a,b,c) VALUES(4,5,6), (5,6,4);} } {1 {UNIQUE constraint failed: t1.c}} -do_execsql_test conflict-3.4 { +do_execsql_test 3.4 { SELECT a,b,c FROM t1 ORDER BY a; } {1 2 3 2 3 4 4 5 6} # Arrange the table rows in a different order and repeat. # -do_execsql_test conflict-4.1 { +do_execsql_test 4.1 { DROP TABLE t1; CREATE TABLE t1( b UNIQUE ON CONFLICT IGNORE, c UNIQUE ON CONFLICT FAIL, a INT PRIMARY KEY ON CONFLICT REPLACE @@ -125,28 +126,28 @@ SELECT a,b,c FROM t1 ORDER BY a; } {1 2 3 2 3 4} # Insert a row that conflicts on column B. The insert should be ignored. # -do_execsql_test conflict-4.2 { +do_execsql_test 4.2 { INSERT INTO t1(a,b,c) VALUES(3,2,5); SELECT a,b,c FROM t1 ORDER BY a; } {1 2 3 2 3 4} # Insert two rows where the second conflicts on C. The first row show go # and and then there should be a constraint error. # -do_test conflict-4.3 { +do_test 4.3 { catchsql {INSERT INTO t1(a,b,c) VALUES(4,5,6), (5,6,4);} } {1 {UNIQUE constraint failed: t1.c}} -do_execsql_test conflict-4.4 { +do_execsql_test 4.4 { SELECT a,b,c FROM t1 ORDER BY a; } {1 2 3 2 3 4 4 5 6} # Arrange the table rows in a different order and repeat. # -do_execsql_test conflict-5.1 { +do_execsql_test 5.1 { DROP TABLE t1; CREATE TABLE t1( b UNIQUE ON CONFLICT IGNORE, a INT PRIMARY KEY ON CONFLICT REPLACE, c UNIQUE ON CONFLICT FAIL @@ -155,28 +156,28 @@ SELECT a,b,c FROM t1 ORDER BY a; } {1 2 3 2 3 4} # Insert a row that conflicts on column B. The insert should be ignored. # -do_execsql_test conflict-5.2 { +do_execsql_test 5.2 { INSERT INTO t1(a,b,c) VALUES(3,2,5); SELECT a,b,c FROM t1 ORDER BY a; } {1 2 3 2 3 4} # Insert two rows where the second conflicts on C. The first row show go # and and then there should be a constraint error. # -do_test conflict-5.3 { +do_test 5.3 { catchsql {INSERT INTO t1(a,b,c) VALUES(4,5,6), (5,6,4);} } {1 {UNIQUE constraint failed: t1.c}} -do_execsql_test conflict-5.4 { +do_execsql_test 5.4 { SELECT a,b,c FROM t1 ORDER BY a; } {1 2 3 2 3 4 4 5 6} # Arrange the table rows in a different order and repeat. # -do_execsql_test conflict-6.1 { +do_execsql_test 6.1 { DROP TABLE t1; CREATE TABLE t1( c UNIQUE ON CONFLICT FAIL, a INT PRIMARY KEY ON CONFLICT REPLACE, b UNIQUE ON CONFLICT IGNORE @@ -185,28 +186,28 @@ SELECT a,b,c FROM t1 ORDER BY a; } {1 2 3 2 3 4} # Insert a row that conflicts on column B. The insert should be ignored. # -do_execsql_test conflict-6.2 { +do_execsql_test 6.2 { INSERT INTO t1(a,b,c) VALUES(3,2,5); SELECT a,b,c FROM t1 ORDER BY a; } {1 2 3 2 3 4} # Insert two rows where the second conflicts on C. The first row show go # and and then there should be a constraint error. # -do_test conflict-6.3 { +do_test 6.3 { catchsql {INSERT INTO t1(a,b,c) VALUES(4,5,6), (5,6,4);} } {1 {UNIQUE constraint failed: t1.c}} -do_execsql_test conflict-6.4 { +do_execsql_test 6.4 { SELECT a,b,c FROM t1 ORDER BY a; } {1 2 3 2 3 4 4 5 6} # Change which column is the PRIMARY KEY # -do_execsql_test conflict-7.1 { +do_execsql_test 7.1 { DROP TABLE t1; CREATE TABLE t1( a UNIQUE ON CONFLICT REPLACE, b INTEGER PRIMARY KEY ON CONFLICT IGNORE, c UNIQUE ON CONFLICT FAIL @@ -215,28 +216,28 @@ SELECT a,b,c FROM t1 ORDER BY a; } {1 2 3 2 3 4} # Insert a row that conflicts on column B. The insert should be ignored. # -do_execsql_test conflict-7.2 { +do_execsql_test 7.2 { INSERT INTO t1(a,b,c) VALUES(3,2,5); SELECT a,b,c FROM t1 ORDER BY a; } {1 2 3 2 3 4} # Insert two rows where the second conflicts on C. The first row show go # and and then there should be a constraint error. # -do_test conflict-7.3 { +do_test 7.3 { catchsql {INSERT INTO t1(a,b,c) VALUES(4,5,6), (5,6,4);} } {1 {UNIQUE constraint failed: t1.c}} -do_execsql_test conflict-7.4 { +do_execsql_test 7.4 { SELECT a,b,c FROM t1 ORDER BY a; } {1 2 3 2 3 4 4 5 6} # Change which column is the PRIMARY KEY # -do_execsql_test conflict-8.1 { +do_execsql_test 8.1 { DROP TABLE t1; CREATE TABLE t1( a UNIQUE ON CONFLICT REPLACE, b INT PRIMARY KEY ON CONFLICT IGNORE, c UNIQUE ON CONFLICT FAIL @@ -245,28 +246,28 @@ SELECT a,b,c FROM t1 ORDER BY a; } {1 2 3 2 3 4} # Insert a row that conflicts on column B. The insert should be ignored. # -do_execsql_test conflict-8.2 { +do_execsql_test 8.2 { INSERT INTO t1(a,b,c) VALUES(3,2,5); SELECT a,b,c FROM t1 ORDER BY a; } {1 2 3 2 3 4} # Insert two rows where the second conflicts on C. The first row show go # and and then there should be a constraint error. # -do_test conflict-8.3 { +do_test 8.3 { catchsql {INSERT INTO t1(a,b,c) VALUES(4,5,6), (5,6,4);} } {1 {UNIQUE constraint failed: t1.c}} -do_execsql_test conflict-8.4 { +do_execsql_test 8.4 { SELECT a,b,c FROM t1 ORDER BY a; } {1 2 3 2 3 4 4 5 6} # Change which column is the PRIMARY KEY # -do_execsql_test conflict-9.1 { +do_execsql_test 9.1 { DROP TABLE t1; CREATE TABLE t1( a UNIQUE ON CONFLICT REPLACE, b INT PRIMARY KEY ON CONFLICT IGNORE, c UNIQUE ON CONFLICT FAIL @@ -275,28 +276,28 @@ SELECT a,b,c FROM t1 ORDER BY a; } {1 2 3 2 3 4} # Insert a row that conflicts on column B. The insert should be ignored. # -do_execsql_test conflict-9.2 { +do_execsql_test 9.2 { INSERT INTO t1(a,b,c) VALUES(3,2,5); SELECT a,b,c FROM t1 ORDER BY a; } {1 2 3 2 3 4} # Insert two rows where the second conflicts on C. The first row show go # and and then there should be a constraint error. # -do_test conflict-9.3 { +do_test 9.3 { catchsql {INSERT INTO t1(a,b,c) VALUES(4,5,6), (5,6,4);} } {1 {UNIQUE constraint failed: t1.c}} -do_execsql_test conflict-9.4 { +do_execsql_test 9.4 { SELECT a,b,c FROM t1 ORDER BY a; } {1 2 3 2 3 4 4 5 6} # Change which column is the PRIMARY KEY # -do_execsql_test conflict-10.1 { +do_execsql_test 10.1 { DROP TABLE t1; CREATE TABLE t1( a UNIQUE ON CONFLICT REPLACE, b UNIQUE ON CONFLICT IGNORE, c INTEGER PRIMARY KEY ON CONFLICT FAIL @@ -305,28 +306,28 @@ SELECT a,b,c FROM t1 ORDER BY a; } {1 2 3 2 3 4} # Insert a row that conflicts on column B. The insert should be ignored. # -do_execsql_test conflict-10.2 { +do_execsql_test 10.2 { INSERT INTO t1(a,b,c) VALUES(3,2,5); SELECT a,b,c FROM t1 ORDER BY a; } {1 2 3 2 3 4} # Insert two rows where the second conflicts on C. The first row show go # and and then there should be a constraint error. # -do_test conflict-10.3 { +do_test 10.3 { catchsql {INSERT INTO t1(a,b,c) VALUES(4,5,6), (5,6,4);} } {1 {UNIQUE constraint failed: t1.c}} -do_execsql_test conflict-10.4 { +do_execsql_test 10.4 { SELECT a,b,c FROM t1 ORDER BY a; } {1 2 3 2 3 4 4 5 6} # Change which column is the PRIMARY KEY # -do_execsql_test conflict-11.1 { +do_execsql_test 11.1 { DROP TABLE t1; CREATE TABLE t1( a UNIQUE ON CONFLICT REPLACE, b UNIQUE ON CONFLICT IGNORE, c PRIMARY KEY ON CONFLICT FAIL @@ -335,22 +336,35 @@ SELECT a,b,c FROM t1 ORDER BY a; } {1 2 3 2 3 4} # Insert a row that conflicts on column B. The insert should be ignored. # -do_execsql_test conflict-11.2 { +do_execsql_test 11.2 { INSERT INTO t1(a,b,c) VALUES(3,2,5); SELECT a,b,c FROM t1 ORDER BY a; } {1 2 3 2 3 4} # Insert two rows where the second conflicts on C. The first row show go # and and then there should be a constraint error. # -do_test conflict-11.3 { +do_test 11.3 { catchsql {INSERT INTO t1(a,b,c) VALUES(4,5,6), (5,6,4);} } {1 {UNIQUE constraint failed: t1.c}} -do_execsql_test conflict-11.4 { +do_execsql_test 11.4 { SELECT a,b,c FROM t1 ORDER BY a; } {1 2 3 2 3 4 4 5 6} + +# Check that ticket [f68dc596c4] has been fixed. +# +do_execsql_test 12.1 { + CREATE TABLE t2(a INTEGER PRIMARY KEY, b TEXT); + INSERT INTO t2 VALUES(111, '111'); +} +do_execsql_test 12.2 { + REPLACE INTO t2 VALUES(NULL, '112'), (111, '111B'); +} +do_execsql_test 12.3 { + SELECT * FROM t2; +} {111 111B 112 112} finish_test Index: test/fkey5.test ================================================================== --- test/fkey5.test +++ test/fkey5.test @@ -113,12 +113,15 @@ # EVIDENCE-OF: R-45728-08709 There are four columns in each result row. # # EVIDENCE-OF: R-55672-01620 The first column is the name of the table # that contains the REFERENCES clause. # -# EVIDENCE-OF: R-25219-25618 The second column is the rowid of the row -# that contains the invalid REFERENCES clause. +# EVIDENCE-OF: R-00471-55166 The second column is the rowid of the row +# that contains the invalid REFERENCES clause, or NULL if the child +# table is a WITHOUT ROWID table. +# +# The second clause in the previous is tested by fkey5-10.3. # # EVIDENCE-OF: R-40482-20265 The third column is the name of the table # that is referred to. # # EVIDENCE-OF: R-62839-07969 The fourth column is the index of the @@ -405,10 +408,13 @@ INSERT INTO c30 (master, line) VALUES (1, 999); } do_execsql_test 10.2 { PRAGMA foreign_key_check; } +# EVIDENCE-OF: R-00471-55166 The second column is the rowid of the row +# that contains the invalid REFERENCES clause, or NULL if the child +# table is a WITHOUT ROWID table. do_execsql_test 10.3 { INSERT INTO c30 VALUES(45, 45); PRAGMA foreign_key_check; } {c30 {} p30 0} Index: test/fts3fault.test ================================================================== --- test/fts3fault.test +++ test/fts3fault.test @@ -175,17 +175,19 @@ execsql "INSERT INTO t8 VALUES('e e e')" execsql "INSERT INTO t8(t8) VALUES('optimize')" faultsim_save_and_close } {} -do_faultsim_test 8.1 -faults oom-t* -prep { - faultsim_restore_and_reopen - db func mit mit -} -body { - execsql { SELECT mit(matchinfo(t8, 'x')) FROM t8 WHERE t8 MATCH 'a b c' } -} -test { - faultsim_test_result {0 {{1 1 1 1 4 2 1 5 5}}} +ifcapable fts4_deferred { + do_faultsim_test 8.1 -faults oom-t* -prep { + faultsim_restore_and_reopen + db func mit mit + } -body { + execsql { SELECT mit(matchinfo(t8, 'x')) FROM t8 WHERE t8 MATCH 'a b c' } + } -test { + faultsim_test_result {0 {{1 1 1 1 4 2 1 5 5}}} + } } do_faultsim_test 8.2 -faults oom-t* -prep { faultsim_restore_and_reopen db func mit mit Index: test/fts3misc.test ================================================================== --- test/fts3misc.test +++ test/fts3misc.test @@ -145,34 +145,36 @@ } {4} #------------------------------------------------------------------------- # reset_db -do_execsql_test 4.0 { - PRAGMA page_size = 512; - CREATE VIRTUAL TABLE t4 USING fts4; - WITH s(i) AS ( SELECT 1 UNION ALL SELECT i+1 FROM s WHERE i<8000 ) - INSERT INTO t4 SELECT 'a b c a b c a b c' FROM s; -} -do_execsql_test 4.1 { - SELECT count(*) FROM t4 WHERE t4 MATCH '"a b c" OR "c a b"' -} {8000} -do_execsql_test 4.2 { - SELECT quote(value) from t4_stat where id=0 -} {X'C03EC0B204C0A608'} -do_execsql_test 4.3 { - UPDATE t4_stat SET value = X'C03EC0B204C0A60800' WHERE id=0; -} -do_catchsql_test 4.4 { - SELECT count(*) FROM t4 WHERE t4 MATCH '"a b c" OR "c a b"' -} {1 {database disk image is malformed}} -do_execsql_test 4.5 { - UPDATE t4_stat SET value = X'00C03EC0B204C0A608' WHERE id=0; -} -do_catchsql_test 4.6 { - SELECT count(*) FROM t4 WHERE t4 MATCH '"a b c" OR "c a b"' -} {1 {database disk image is malformed}} +ifcapable fts4_deferred { + do_execsql_test 4.0 { + PRAGMA page_size = 512; + CREATE VIRTUAL TABLE t4 USING fts4; + WITH s(i) AS ( SELECT 1 UNION ALL SELECT i+1 FROM s WHERE i<8000 ) + INSERT INTO t4 SELECT 'a b c a b c a b c' FROM s; + } + do_execsql_test 4.1 { + SELECT count(*) FROM t4 WHERE t4 MATCH '"a b c" OR "c a b"' + } {8000} + do_execsql_test 4.2 { + SELECT quote(value) from t4_stat where id=0 + } {X'C03EC0B204C0A608'} + do_execsql_test 4.3 { + UPDATE t4_stat SET value = X'C03EC0B204C0A60800' WHERE id=0; + } + do_catchsql_test 4.4 { + SELECT count(*) FROM t4 WHERE t4 MATCH '"a b c" OR "c a b"' + } {1 {database disk image is malformed}} + do_execsql_test 4.5 { + UPDATE t4_stat SET value = X'00C03EC0B204C0A608' WHERE id=0; + } + do_catchsql_test 4.6 { + SELECT count(*) FROM t4 WHERE t4 MATCH '"a b c" OR "c a b"' + } {1 {database disk image is malformed}} +} #------------------------------------------------------------------------- # reset_db do_execsql_test 5.0 { ADDED test/having.test Index: test/having.test ================================================================== --- test/having.test +++ test/having.test @@ -0,0 +1,154 @@ +# 2017 April 30 +# +# The author disclaims copyright to this source code. In place of +# a legal notice, here is a blessing: +# +# May you do good and not evil. +# May you find forgiveness for yourself and forgive others. +# May you share freely, never taking more than you give. +# +#*********************************************************************** +# +# Test the HAVING->WHERE optimization. +# + +set testdir [file dirname $argv0] +source $testdir/tester.tcl +set testprefix having + +do_execsql_test 1.0 { + CREATE TABLE t2(c, d); + + CREATE TABLE t1(a, b); + INSERT INTO t1 VALUES(1, 1); + INSERT INTO t1 VALUES(2, 2); + INSERT INTO t1 VALUES(1, 3); + INSERT INTO t1 VALUES(2, 4); + INSERT INTO t1 VALUES(1, 5); + INSERT INTO t1 VALUES(2, 6); +} {} + +foreach {tn sql res} { + 1 "SELECT a, sum(b) FROM t1 GROUP BY a HAVING a=2" {2 12} + 2 "SELECT a, sum(b) FROM t1 GROUP BY a HAVING a=2 AND sum(b)>10" {2 12} + 3 "SELECT a, sum(b) FROM t1 GROUP BY a HAVING sum(b)>12" {} +} { + do_execsql_test 1.$tn $sql $res +} + +# Run an EXPLAIN command for both SQL statements. Return true if +# the outputs are identical, or false otherwise. +# +proc compare_vdbe {sql1 sql2} { + set r1 [list] + set r2 [list] + db eval "explain $sql1" { lappend r1 $opcode $p1 $p2 $p3 $p4 $p5} + db eval "explain $sql2" { lappend r2 $opcode $p1 $p2 $p3 $p4 $p5} + return [expr {$r1==$r2}] +} + +proc do_compare_vdbe_test {tn sql1 sql2 res} { + uplevel [list do_test $tn [list compare_vdbe $sql1 $sql2] $res] +} + +#------------------------------------------------------------------------- +# Test that various statements that are eligible for the optimization +# produce the same VDBE code as optimizing by hand does. +# +foreach {tn sql1 sql2} { + 1 "SELECT a, sum(b) FROM t1 GROUP BY a HAVING a=2" + "SELECT a, sum(b) FROM t1 WHERE a=2 GROUP BY a" + + 2 "SELECT a, sum(b) FROM t1 GROUP BY a HAVING sum(b)>5 AND a=2" + "SELECT a, sum(b) FROM t1 WHERE a=2 GROUP BY a HAVING sum(b)>5" + + 3 "SELECT a, sum(b) FROM t1 GROUP BY a COLLATE binary HAVING a=2" + "SELECT a, sum(b) FROM t1 WHERE a=2 GROUP BY a COLLATE binary" + + 4 { + SELECT x,y FROM ( + SELECT a AS x, sum(b) AS y FROM t1 + GROUP BY a + ) WHERE x BETWEEN 8888 AND 9999 + } { + SELECT x,y FROM ( + SELECT a AS x, sum(b) AS y FROM t1 + WHERE x BETWEEN 8888 AND 9999 + GROUP BY a + ) + } + + 5 "SELECT a, sum(b) FROM t1 GROUP BY a COLLATE binary HAVING 0" + "SELECT a, sum(b) FROM t1 WHERE 0 GROUP BY a COLLATE binary" + + 6 "SELECT count(*) FROM t1,t2 WHERE a=c GROUP BY b, d HAVING b=d" + "SELECT count(*) FROM t1,t2 WHERE a=c AND b=d GROUP BY b, d" + + 7 { + SELECT count(*) FROM t1,t2 WHERE a=c GROUP BY b, d + HAVING b=d COLLATE nocase + } { + SELECT count(*) FROM t1,t2 WHERE a=c AND b=d COLLATE nocase + GROUP BY b, d + } + + 8 "SELECT a, sum(b) FROM t1 GROUP BY a||b HAVING substr(a||b, 1, 1)='a'" + "SELECT a, sum(b) FROM t1 WHERE substr(a||b, 1, 1)='a' GROUP BY a||b" +} { + do_compare_vdbe_test 2.$tn $sql1 $sql2 1 +} + +#------------------------------------------------------------------------- +# 1: Test that the optimization is only applied if the GROUP BY term +# uses BINARY collation. +# +# 2: Not applied if there is a non-deterministic function in the HAVING +# term. +# +foreach {tn sql1 sql2} { + 1 "SELECT a, sum(b) FROM t1 GROUP BY a COLLATE nocase HAVING a=2" + "SELECT a, sum(b) FROM t1 WHERE a=2 GROUP BY a COLLATE nocase" + + 2 "SELECT a, sum(b) FROM t1 GROUP BY a HAVING randomblob(a)rule; rp; rp=rp->next) rp->doesReduce = LEMON_FALSE; for(i=0; inxstate; i++){ for(ap=lemp->sorted[i]->ap; ap; ap=ap->next){ if( ap->type==REDUCE || ap->type==SHIFTREDUCE ){ - ap->x.rp->doesReduce = i; + ap->x.rp->doesReduce = 1; } } } /* Finish rendering the constants now that the action table has