Index: src/btree.c ================================================================== --- src/btree.c +++ src/btree.c @@ -8137,10 +8137,32 @@ iCellDepth = pCur->iPage; iCellIdx = pCur->aiIdx[iCellDepth]; pPage = pCur->apPage[iCellDepth]; pCell = findCell(pPage, iCellIdx); + + /* If the bPreserve flag is set to true, then the cursor position must + ** be preserved following this delete operation. If the current delete + ** will cause a b-tree rebalance, then this is done by saving the cursor + ** key and leaving the cursor in CURSOR_REQUIRESEEK state before + ** returning. + ** + ** Or, if the current delete will not cause a rebalance, then the cursor + ** will be left in CURSOR_SKIPNEXT state pointing to the entry immediately + ** before or after the deleted entry. In this case set bSkipnext to true. */ + if( bPreserve ){ + if( !pPage->leaf + || (pPage->nFree+cellSizePtr(pPage,pCell)+2)>(int)(pBt->usableSize*2/3) + ){ + /* A b-tree rebalance will be required after deleting this entry. + ** Save the cursor key. */ + rc = saveCursorKey(pCur); + if( rc ) return rc; + }else{ + bSkipnext = 1; + } + } /* If the page containing the entry to delete is not a leaf page, move ** the cursor to the largest entry in the tree that is smaller than ** the entry being deleted. This cell will replace the cell being deleted ** from the internal node. The 'previous' entry is used for this instead @@ -8164,32 +8186,10 @@ ** invalidate any incrblob cursors open on the row being deleted. */ if( pCur->pKeyInfo==0 ){ invalidateIncrblobCursors(p, pCur->info.nKey, 0); } - /* If the bPreserve flag is set to true, then the cursor position must - ** be preserved following this delete operation. If the current delete - ** will cause a b-tree rebalance, then this is done by saving the cursor - ** key and leaving the cursor in CURSOR_REQUIRESEEK state before - ** returning. - ** - ** Or, if the current delete will not cause a rebalance, then the cursor - ** will be left in CURSOR_SKIPNEXT state pointing to the entry immediately - ** before or after the deleted entry. In this case set bSkipnext to true. */ - if( bPreserve ){ - if( !pPage->leaf - || (pPage->nFree+cellSizePtr(pPage,pCell)+2)>(int)(pBt->usableSize*2/3) - ){ - /* A b-tree rebalance will be required after deleting this entry. - ** Save the cursor key. */ - rc = saveCursorKey(pCur); - if( rc ) return rc; - }else{ - bSkipnext = 1; - } - } - /* Make the page containing the entry to be deleted writable. Then free any ** overflow pages associated with the entry and finally remove the cell ** itself from within the page. */ rc = sqlite3PagerWrite(pPage->pDbPage); if( rc ) return rc; Index: src/delete.c ================================================================== --- src/delete.c +++ src/delete.c @@ -141,11 +141,11 @@ /* Check that there isn't an ORDER BY without a LIMIT clause. */ if( pOrderBy && (pLimit == 0) ) { sqlite3ErrorMsg(pParse, "ORDER BY without LIMIT on %s", zStmtType); - goto limit_where_cleanup_2; + goto limit_where_cleanup; } /* We only need to generate a select expression if there ** is a limit/offset term to enforce. */ @@ -163,44 +163,34 @@ ** SELECT rowid FROM table_a WHERE col1=1 ORDER BY col2 LIMIT 1 OFFSET 1 ** ); */ pSelectRowid = sqlite3PExpr(pParse, TK_ROW, 0, 0, 0); - if( pSelectRowid == 0 ) goto limit_where_cleanup_2; + if( pSelectRowid == 0 ) goto limit_where_cleanup; pEList = sqlite3ExprListAppend(pParse, 0, pSelectRowid); - if( pEList == 0 ) goto limit_where_cleanup_2; + if( pEList == 0 ) goto limit_where_cleanup; /* duplicate the FROM clause as it is needed by both the DELETE/UPDATE tree ** and the SELECT subtree. */ pSelectSrc = sqlite3SrcListDup(pParse->db, pSrc, 0); if( pSelectSrc == 0 ) { sqlite3ExprListDelete(pParse->db, pEList); - goto limit_where_cleanup_2; + goto limit_where_cleanup; } /* generate the SELECT expression tree. */ pSelect = sqlite3SelectNew(pParse,pEList,pSelectSrc,pWhere,0,0, pOrderBy,0,pLimit,pOffset); if( pSelect == 0 ) return 0; /* now generate the new WHERE rowid IN clause for the DELETE/UDPATE */ pWhereRowid = sqlite3PExpr(pParse, TK_ROW, 0, 0, 0); - if( pWhereRowid == 0 ) goto limit_where_cleanup_1; - pInClause = sqlite3PExpr(pParse, TK_IN, pWhereRowid, 0, 0); - if( pInClause == 0 ) goto limit_where_cleanup_1; - - pInClause->x.pSelect = pSelect; - pInClause->flags |= EP_xIsSelect; - sqlite3ExprSetHeightAndFlags(pParse, pInClause); + pInClause = pWhereRowid ? sqlite3PExpr(pParse, TK_IN, pWhereRowid, 0, 0) : 0; + sqlite3PExprAddSelect(pParse, pInClause, pSelect); return pInClause; - /* something went wrong. clean up anything allocated. */ -limit_where_cleanup_1: - sqlite3SelectDelete(pParse->db, pSelect); - return 0; - -limit_where_cleanup_2: +limit_where_cleanup: sqlite3ExprDelete(pParse->db, pWhere); sqlite3ExprListDelete(pParse->db, pOrderBy); sqlite3ExprDelete(pParse->db, pLimit); sqlite3ExprDelete(pParse->db, pOffset); return 0; Index: src/expr.c ================================================================== --- src/expr.c +++ src/expr.c @@ -559,10 +559,26 @@ if( p ) { sqlite3ExprCheckHeight(pParse, p->nHeight); } return p; } + +/* +** Add pSelect to the Expr.x.pSelect field. Or, if pExpr is NULL (due +** do a memory allocation failure) then delete the pSelect object. +*/ +void sqlite3PExprAddSelect(Parse *pParse, Expr *pExpr, Select *pSelect){ + if( pExpr ){ + pExpr->x.pSelect = pSelect; + ExprSetProperty(pExpr, EP_xIsSelect|EP_Subquery); + sqlite3ExprSetHeightAndFlags(pParse, pExpr); + }else{ + assert( pParse->db->mallocFailed ); + sqlite3SelectDelete(pParse->db, pSelect); + } +} + /* ** If the expression is always either TRUE or FALSE (respectively), ** then return 1. If one cannot determine the truth value of the ** expression at compile-time return 0. Index: src/memjournal.c ================================================================== --- src/memjournal.c +++ src/memjournal.c @@ -92,10 +92,11 @@ return SQLITE_IOERR_SHORT_READ; } #endif assert( (iAmt+iOfst)<=p->endpoint.iOffset ); + assert( p->readpoint.iOffset==0 || p->readpoint.pChunk!=0 ); if( p->readpoint.iOffset!=iOfst || iOfst==0 ){ sqlite3_int64 iOff = 0; for(pChunk=p->pFirst; ALWAYS(pChunk) && (iOff+p->nChunkSize)<=iOfst; pChunk=pChunk->pNext @@ -102,10 +103,11 @@ ){ iOff += p->nChunkSize; } }else{ pChunk = p->readpoint.pChunk; + assert( pChunk!=0 ); } iChunkOffset = (int)(iOfst%p->nChunkSize); do { int iSpace = p->nChunkSize - iChunkOffset; @@ -113,11 +115,11 @@ memcpy(zOut, (u8*)pChunk->zChunk + iChunkOffset, nCopy); zOut += nCopy; nRead -= iSpace; iChunkOffset = 0; } while( nRead>=0 && (pChunk=pChunk->pNext)!=0 && nRead>0 ); - p->readpoint.iOffset = iOfst+iAmt; + p->readpoint.iOffset = pChunk ? iOfst+iAmt : 0; p->readpoint.pChunk = pChunk; return SQLITE_OK; } Index: src/os_unix.c ================================================================== --- src/os_unix.c +++ src/os_unix.c @@ -1500,20 +1500,26 @@ static int unixLock(sqlite3_file *id, int eFileLock){ /* The following describes the implementation of the various locks and ** lock transitions in terms of the POSIX advisory shared and exclusive ** lock primitives (called read-locks and write-locks below, to avoid ** confusion with SQLite lock names). The algorithms are complicated - ** slightly in order to be compatible with windows systems simultaneously + ** slightly in order to be compatible with Windows95 systems simultaneously ** accessing the same database file, in case that is ever required. ** ** Symbols defined in os.h indentify the 'pending byte' and the 'reserved ** byte', each single bytes at well known offsets, and the 'shared byte ** range', a range of 510 bytes at a well known offset. ** ** To obtain a SHARED lock, a read-lock is obtained on the 'pending - ** byte'. If this is successful, a random byte from the 'shared byte - ** range' is read-locked and the lock on the 'pending byte' released. + ** byte'. If this is successful, 'shared byte range' is read-locked + ** and the lock on the 'pending byte' released. (Legacy note: When + ** SQLite was first developed, Windows95 systems were still very common, + ** and Widnows95 lacks a shared-lock capability. So on Windows95, a + ** single randomly selected by from the 'shared byte range' is locked. + ** Windows95 is now pretty much extinct, but this work-around for the + ** lack of shared-locks on Windows95 lives on, for backwards + ** compatibility.) ** ** A process may only obtain a RESERVED lock after it has a SHARED lock. ** A RESERVED lock is implemented by grabbing a write-lock on the ** 'reserved byte'. ** @@ -1528,15 +1534,10 @@ ** An EXCLUSIVE lock, obtained after a PENDING lock is held, is ** implemented by obtaining a write-lock on the entire 'shared byte ** range'. Since all other locks require a read-lock on one of the bytes ** within this range, this ensures that no other locks are held on the ** database. - ** - ** The reason a single byte cannot be used instead of the 'shared byte - ** range' is that some versions of windows do not support read-locks. By - ** locking a random byte from a range, concurrent SHARED locks may exist - ** even if the locking primitive used is always a write-lock. */ int rc = SQLITE_OK; unixFile *pFile = (unixFile*)id; unixInodeInfo *pInode; struct flock lock; Index: src/parse.y ================================================================== --- src/parse.y +++ src/parse.y @@ -869,26 +869,27 @@ A.pExpr = sqlite3PExpr(pParse, TK_DOT, temp1, temp4, 0); } term(A) ::= INTEGER|FLOAT|BLOB(X). {spanExpr(&A,pParse,@X,X);/*A-overwrites-X*/} term(A) ::= STRING(X). {spanExpr(&A,pParse,@X,X);/*A-overwrites-X*/} expr(A) ::= VARIABLE(X). { - Token t = X; /*A-overwrites-X*/ - if( t.n>=2 && t.z[0]=='#' && sqlite3Isdigit(t.z[1]) ){ + if( !(X.z[0]=='#' && sqlite3Isdigit(X.z[1])) ){ + spanExpr(&A, pParse, TK_VARIABLE, X); + sqlite3ExprAssignVarNumber(pParse, A.pExpr); + }else{ /* When doing a nested parse, one can include terms in an expression ** that look like this: #1 #2 ... These terms refer to registers ** in the virtual machine. #N is the N-th register. */ + Token t = X; /*A-overwrites-X*/ + assert( t.n>=2 ); spanSet(&A, &t, &t); if( pParse->nested==0 ){ sqlite3ErrorMsg(pParse, "near \"%T\": syntax error", &t); A.pExpr = 0; }else{ A.pExpr = sqlite3PExpr(pParse, TK_REGISTER, 0, 0, &t); if( A.pExpr ) sqlite3GetInt32(&t.z[1], &A.pExpr->iTable); } - }else{ - spanExpr(&A, pParse, TK_VARIABLE, t); - sqlite3ExprAssignVarNumber(pParse, A.pExpr); } } expr(A) ::= expr(A) COLLATE ids(C). { A.pExpr = sqlite3ExprAddCollateToken(pParse, A.pExpr, &C, 1); A.zEnd = &C.z[C.n]; @@ -1120,54 +1121,31 @@ A.zEnd = &E.z[E.n]; } expr(A) ::= LP(B) select(X) RP(E). { spanSet(&A,&B,&E); /*A-overwrites-B*/ A.pExpr = sqlite3PExpr(pParse, TK_SELECT, 0, 0, 0); - if( A.pExpr ){ - A.pExpr->x.pSelect = X; - ExprSetProperty(A.pExpr, EP_xIsSelect|EP_Subquery); - sqlite3ExprSetHeightAndFlags(pParse, A.pExpr); - }else{ - sqlite3SelectDelete(pParse->db, X); - } + sqlite3PExprAddSelect(pParse, A.pExpr, X); } expr(A) ::= expr(A) in_op(N) LP select(Y) RP(E). [IN] { A.pExpr = sqlite3PExpr(pParse, TK_IN, A.pExpr, 0, 0); - if( A.pExpr ){ - A.pExpr->x.pSelect = Y; - ExprSetProperty(A.pExpr, EP_xIsSelect|EP_Subquery); - sqlite3ExprSetHeightAndFlags(pParse, A.pExpr); - }else{ - sqlite3SelectDelete(pParse->db, Y); - } + sqlite3PExprAddSelect(pParse, A.pExpr, Y); exprNot(pParse, N, &A); A.zEnd = &E.z[E.n]; } expr(A) ::= expr(A) in_op(N) nm(Y) dbnm(Z). [IN] { SrcList *pSrc = sqlite3SrcListAppend(pParse->db, 0,&Y,&Z); + Select *pSelect = sqlite3SelectNew(pParse, 0,pSrc,0,0,0,0,0,0,0); A.pExpr = sqlite3PExpr(pParse, TK_IN, A.pExpr, 0, 0); - if( A.pExpr ){ - A.pExpr->x.pSelect = sqlite3SelectNew(pParse, 0,pSrc,0,0,0,0,0,0,0); - ExprSetProperty(A.pExpr, EP_xIsSelect|EP_Subquery); - sqlite3ExprSetHeightAndFlags(pParse, A.pExpr); - }else{ - sqlite3SrcListDelete(pParse->db, pSrc); - } + sqlite3PExprAddSelect(pParse, A.pExpr, pSelect); exprNot(pParse, N, &A); A.zEnd = Z.z ? &Z.z[Z.n] : &Y.z[Y.n]; } expr(A) ::= EXISTS(B) LP select(Y) RP(E). { Expr *p; spanSet(&A,&B,&E); /*A-overwrites-B*/ p = A.pExpr = sqlite3PExpr(pParse, TK_EXISTS, 0, 0, 0); - if( p ){ - p->x.pSelect = Y; - ExprSetProperty(p, EP_xIsSelect|EP_Subquery); - sqlite3ExprSetHeightAndFlags(pParse, p); - }else{ - sqlite3SelectDelete(pParse->db, Y); - } + sqlite3PExprAddSelect(pParse, p, Y); } %endif SQLITE_OMIT_SUBQUERY /* CASE expressions */ expr(A) ::= CASE(C) case_operand(X) case_exprlist(Y) case_else(Z) END(E). { Index: src/sqlite.h.in ================================================================== --- src/sqlite.h.in +++ src/sqlite.h.in @@ -8079,24 +8079,33 @@ /* ** CAPI3REF: Start a read transaction on an historical snapshot ** EXPERIMENTAL ** -** ^The [sqlite3_snapshot_open(D,S,P)] interface attempts to move the -** read transaction that is currently open on schema S of -** [database connection] D so that it refers to historical [snapshot] P. +** ^The [sqlite3_snapshot_open(D,S,P)] interface starts a +** read transaction for schema S of +** [database connection] D such that the read transaction +** refers to historical [snapshot] P, rather than the most +** recent change to the database. ** ^The [sqlite3_snapshot_open()] interface returns SQLITE_OK on success ** or an appropriate [error code] if it fails. ** ** ^In order to succeed, a call to [sqlite3_snapshot_open(D,S,P)] must be -** the first operation, apart from other sqlite3_snapshot_open() calls, -** following the [BEGIN] that starts a new read transaction. -** ^A [snapshot] will fail to open if it has been overwritten by a +** the first operation following the [BEGIN] that takes the schema S +** out of [autocommit mode]. +** ^In other words, schema S must not currently be in +** a transaction for [sqlite3_snapshot_open(D,S,P)] to work, but the +** database connection D must be out of [autocommit mode]. +** ^A [snapshot] will fail to open if it has been overwritten by a ** [checkpoint]. -** ^A [snapshot] will fail to open if the database connection D has not -** previously completed at least one read operation against the database -** file. (Hint: Run "[PRAGMA application_id]" against a newly opened +** ^(A call to [sqlite3_snapshot_open(D,S,P)] will fail if the +** database connection D does not know that the database file for +** schema S is in [WAL mode]. A database connection might not know +** that the database file is in [WAL mode] if there has been no prior +** I/O on that database connection, or if the database entered [WAL mode] +** after the most recent I/O on the database connection.)^ +** (Hint: Run "[PRAGMA application_id]" against a newly opened ** database connection in order to make it ready to use snapshots.) ** ** The [sqlite3_snapshot_open()] interface is only available when the ** SQLITE_ENABLE_SNAPSHOT compile-time option is used. */ Index: src/sqliteInt.h ================================================================== --- src/sqliteInt.h +++ src/sqliteInt.h @@ -3447,10 +3447,11 @@ #endif Expr *sqlite3ExprAlloc(sqlite3*,int,const Token*,int); Expr *sqlite3Expr(sqlite3*,int,const char*); void sqlite3ExprAttachSubtrees(sqlite3*,Expr*,Expr*,Expr*); Expr *sqlite3PExpr(Parse*, int, Expr*, Expr*, const Token*); +void sqlite3PExprAddSelect(Parse*, Expr*, Select*); Expr *sqlite3ExprAnd(sqlite3*,Expr*, Expr*); Expr *sqlite3ExprFunction(Parse*,ExprList*, Token*); void sqlite3ExprAssignVarNumber(Parse*, Expr*); void sqlite3ExprDelete(sqlite3*, Expr*); ExprList *sqlite3ExprListAppend(Parse*,ExprList*,Expr*); Index: src/util.c ================================================================== --- src/util.c +++ src/util.c @@ -1446,11 +1446,10 @@ ** Note that this routine is only used when one or more of various ** non-standard compile-time options is enabled. */ u64 sqlite3LogEstToInt(LogEst x){ u64 n; - if( x<10 ) return 1; n = x%10; x /= 10; if( n>=5 ) n -= 2; else if( n>=1 ) n -= 1; #if defined(SQLITE_ENABLE_STMT_SCANSTATUS) || \ Index: src/vdbe.c ================================================================== --- src/vdbe.c +++ src/vdbe.c @@ -2753,11 +2753,13 @@ } nData += len; testcase( serial_type==127 ); testcase( serial_type==128 ); nHdr += serial_type<=127 ? 1 : sqlite3VarintLen(serial_type); - }while( (--pRec)>=pData0 ); + if( pRec==pData0 ) break; + pRec--; + }while(1); /* EVIDENCE-OF: R-22564-11647 The header begins with a single varint ** which determines the total number of bytes in the header. The varint ** value is the size of the header in bytes including the size varint ** itself. */ Index: src/vdbeaux.c ================================================================== --- src/vdbeaux.c +++ src/vdbeaux.c @@ -789,56 +789,54 @@ /* ** Delete a P4 value if necessary. */ static void freeP4(sqlite3 *db, int p4type, void *p4){ - if( p4 ){ - assert( db ); - switch( p4type ){ - case P4_FUNCCTX: { - freeEphemeralFunction(db, ((sqlite3_context*)p4)->pFunc); - /* Fall through into the next case */ - } - case P4_REAL: - case P4_INT64: - case P4_DYNAMIC: - case P4_INTARRAY: { - sqlite3DbFree(db, p4); - break; - } - case P4_KEYINFO: { - if( db->pnBytesFreed==0 ) sqlite3KeyInfoUnref((KeyInfo*)p4); - break; - } + assert( db ); + switch( p4type ){ + case P4_FUNCCTX: { + freeEphemeralFunction(db, ((sqlite3_context*)p4)->pFunc); + /* Fall through into the next case */ + } + case P4_REAL: + case P4_INT64: + case P4_DYNAMIC: + case P4_INTARRAY: { + sqlite3DbFree(db, p4); + break; + } + case P4_KEYINFO: { + if( db->pnBytesFreed==0 ) sqlite3KeyInfoUnref((KeyInfo*)p4); + break; + } #ifdef SQLITE_ENABLE_CURSOR_HINTS - case P4_EXPR: { - sqlite3ExprDelete(db, (Expr*)p4); - break; - } + case P4_EXPR: { + sqlite3ExprDelete(db, (Expr*)p4); + break; + } #endif - case P4_MPRINTF: { - if( db->pnBytesFreed==0 ) sqlite3_free(p4); - break; - } - case P4_FUNCDEF: { - freeEphemeralFunction(db, (FuncDef*)p4); - break; - } - case P4_MEM: { - if( db->pnBytesFreed==0 ){ - sqlite3ValueFree((sqlite3_value*)p4); - }else{ - Mem *p = (Mem*)p4; - if( p->szMalloc ) sqlite3DbFree(db, p->zMalloc); - sqlite3DbFree(db, p); - } - break; - } - case P4_VTAB : { - if( db->pnBytesFreed==0 ) sqlite3VtabUnlock((VTable *)p4); - break; - } + case P4_MPRINTF: { + if( db->pnBytesFreed==0 ) sqlite3_free(p4); + break; + } + case P4_FUNCDEF: { + freeEphemeralFunction(db, (FuncDef*)p4); + break; + } + case P4_MEM: { + if( db->pnBytesFreed==0 ){ + sqlite3ValueFree((sqlite3_value*)p4); + }else{ + Mem *p = (Mem*)p4; + if( p->szMalloc ) sqlite3DbFree(db, p->zMalloc); + sqlite3DbFree(db, p); + } + break; + } + case P4_VTAB : { + if( db->pnBytesFreed==0 ) sqlite3VtabUnlock((VTable *)p4); + break; } } } /* Index: src/where.c ================================================================== --- src/where.c +++ src/where.c @@ -1649,15 +1649,16 @@ /* ** Print a WhereLoop object for debugging purposes */ static void whereLoopPrint(WhereLoop *p, WhereClause *pWC){ WhereInfo *pWInfo = pWC->pWInfo; - int nb = 1+(pWInfo->pTabList->nSrc+7)/8; + int nb = 1+(pWInfo->pTabList->nSrc+3)/4; struct SrcList_item *pItem = pWInfo->pTabList->a + p->iTab; Table *pTab = pItem->pTab; + Bitmask mAll = (((Bitmask)1)<<(nb*4)) - 1; sqlite3DebugPrintf("%c%2d.%0*llx.%0*llx", p->cId, - p->iTab, nb, p->maskSelf, nb, p->prereq); + p->iTab, nb, p->maskSelf, nb, p->prereq & mAll); sqlite3DebugPrintf(" %12s", pItem->zAlias ? pItem->zAlias : pTab->zName); if( (p->wsFlags & WHERE_VIRTUALTABLE)==0 ){ const char *zName; if( p->u.btree.pIndex && (zName = p->u.btree.pIndex->zName)!=0 ){ Index: src/wherecode.c ================================================================== --- src/wherecode.c +++ src/wherecode.c @@ -939,11 +939,17 @@ pCompare->pLeft = 0; sqlite3ExprDelete(db, pCompare); } } } - sqlite3ReleaseTempRange(pParse, iReg, nConstraint+2); + /* These registers need to be preserved in case there is an IN operator + ** loop. So we could deallocate the registers here (and potentially + ** reuse them later) if (pLoop->wsFlags & WHERE_IN_ABLE)==0. But it seems + ** simpler and safer to simply not reuse the registers. + ** + ** sqlite3ReleaseTempRange(pParse, iReg, nConstraint+2); + */ sqlite3ExprCachePop(pParse); }else #endif /* SQLITE_OMIT_VIRTUALTABLE */ if( (pLoop->wsFlags & WHERE_IPK)!=0 Index: test/bestindex1.test ================================================================== --- test/bestindex1.test +++ test/bestindex1.test @@ -162,7 +162,114 @@ do_eqp_test 2.2.$mode.6 { SELECT rowid FROM t1 WHERE a IN ('one', 'four') ORDER BY +rowid } $plan($mode) } + +# 2016-04-09. +# Demonstrate a register overwrite problem when using two virtual +# tables where the outer loop uses the IN operator. +# +set G(collist) [list PrimaryKey flagA columnA] +set G(cols) [join $G(collist) ,] +set G(nulls) "NULL" + +proc vtab_command {method args} { + global G + + switch -- $method { + xConnect { + return "CREATE TABLE t1($G(cols))" + } + + xBestIndex { + set clist [lindex $args 0] + #puts $clist + set W [list] + set U [list] + + set i 0 + for {set idx 0} {$idx < [llength $clist]} {incr idx} { + array set c [lindex $clist $idx] + if {$c(op)=="eq" && $c(usable)} { + lappend W "[lindex $G(collist) $c(column)] = %$i%" + lappend U use $idx + incr i + } + } + + if {$W==""} { + set sql "SELECT rowid, * FROM t1" + } else { + set sql "SELECT rowid, * FROM t1 WHERE [join $W { AND }]" + } + + return [concat [list idxstr $sql] $U] + } + + xFilter { + foreach {idxnum idxstr vals} $args {} + + set map [list] + for {set i 0} {$i < [llength $vals]} {incr i} { + lappend map "%$i%" + set v [lindex $vals $i] + if {[string is integer $v]} { + lappend map $v + } else { + lappend map "'$v'" + } + } + set sql [string map $map $idxstr] + + #puts "SQL: $sql" + return [list sql $sql] + } + } + + return {} +} + +db close +forcedelete test.db +sqlite3 db test.db +register_tcl_module db + +do_execsql_test 3.1 " + CREATE TABLE t1($G(cols)); + INSERT INTO t1 VALUES(1, 0, 'ValueA'); + INSERT INTO t1 VALUES(2, 0, 'ValueA'); + INSERT INTO t1 VALUES(3, 0, 'ValueB'); + INSERT INTO t1 VALUES(4, 0, 'ValueB'); +" + +do_execsql_test 3.2 { + CREATE VIRTUAL TABLE VirtualTableA USING tcl(vtab_command); + CREATE VIRTUAL TABLE VirtualTableB USING tcl(vtab_command); +} + +do_execsql_test 3.3 { SELECT primarykey FROM VirtualTableA } {1 2 3 4} + +do_execsql_test 3.4 { + SELECT * FROM + VirtualTableA a CROSS JOIN VirtualTableB b ON b.PrimaryKey=a.PrimaryKey + WHERE a.ColumnA IN ('ValueA', 'ValueB') AND a.FlagA=0 +} { + 1 0 ValueA 1 0 ValueA + 2 0 ValueA 2 0 ValueA + 3 0 ValueB 3 0 ValueB + 4 0 ValueB 4 0 ValueB +} + +do_execsql_test 3.5 { + SELECT * FROM + VirtualTableA a CROSS JOIN VirtualTableB b ON b.PrimaryKey=a.PrimaryKey + WHERE a.FlagA=0 AND a.ColumnA IN ('ValueA', 'ValueB') +} { + 1 0 ValueA 1 0 ValueA + 2 0 ValueA 2 0 ValueA + 3 0 ValueB 3 0 ValueB + 4 0 ValueB 4 0 ValueB +} + finish_test Index: test/delete4.test ================================================================== --- test/delete4.test +++ test/delete4.test @@ -137,9 +137,28 @@ CREATE INDEX idx_t4_0 ON t4 (col1, col0); DELETE FROM t4 WHERE col0=69 OR col0>7; PRAGMA integrity_check; } {ok} - - +# 2016-04-09 +# Ticket https://sqlite.org/src/info/a306e56ff68b8fa5 +# Failure to completely delete when reverse_unordered_selects is +# engaged. +# +db close +forcedelete test.db +sqlite3 db test.db +do_execsql_test 5.0 { + PRAGMA page_size=1024; + CREATE TABLE t1(a INTEGER PRIMARY KEY, b, c); + CREATE INDEX x1 ON t1(b, c); + INSERT INTO t1(a,b,c) VALUES(1, 1, zeroblob(80)); + INSERT INTO t1(a,b,c) SELECT a+1, 1, c FROM t1; + INSERT INTO t1(a,b,c) SELECT a+2, 1, c FROM t1; + INSERT INTO t1(a,b,c) SELECT a+10, 2, c FROM t1 WHERE b=1; + INSERT INTO t1(a,b,c) SELECT a+20, 3, c FROM t1 WHERE b=1; + PRAGMA reverse_unordered_selects = ON; + DELETE FROM t1 WHERE b=2; + SELECT a FROM t1 WHERE b=2; +} {} finish_test Index: test/savepoint7.test ================================================================== --- test/savepoint7.test +++ test/savepoint7.test @@ -92,7 +92,41 @@ } } } msg] list $rc $msg [db eval {SELECT * FROM t2}] } {1 {abort due to ROLLBACK} {}} + +# Ticket: https://www.sqlite.org/src/tktview/7f7f8026eda387d544b +# Segfault in the in-memory journal logic triggered by a tricky +# combination of SAVEPOINT operations. +# +unset -nocomplain i +for {set i 248} {$i<=253} {incr i} { + do_test savepoint7-3.$i { + db close + forcedelete test.db + sqlite3 db test.db + db eval { + PRAGMA page_size=1024; + PRAGMA temp_store=MEMORY; + BEGIN; + CREATE TABLE t1(x INTEGER PRIMARY KEY, y TEXT); + WITH RECURSIVE c(x) AS (VALUES(1) UNION SELECT x+1 FROM c WHERE x<$::i) + INSERT INTO t1(x,y) SELECT x*10, printf('%04d%.800c',x,'*') FROM c; + SAVEPOINT one; + SELECT count(*) FROM t1; + WITH RECURSIVE c(x) AS (VALUES(1) UNION SELECT x+1 FROM c WHERE x<$::i) + INSERT INTO t1(x,y) SELECT x*10+1, printf('%04d%.800c',x,'*') FROM c; + ROLLBACK TO one; + SELECT count(*) FROM t1; + SAVEPOINT twoB; + WITH RECURSIVE c(x) AS (VALUES(1) UNION SELECT x+1 FROM c WHERE x<10) + INSERT INTO t1(x,y) SELECT x*10+2, printf('%04d%.800c',x,'*') FROM c; + ROLLBACK TO twoB; + RELEASE one; + COMMIT; + } + } [list $i $i] +} + finish_test