Index: VERSION ================================================================== --- VERSION +++ VERSION @@ -1,1 +1,1 @@ -3.8.7 +3.8.7.4 Index: configure ================================================================== --- configure +++ configure @@ -1,8 +1,8 @@ #! /bin/sh # Guess values for system-dependent variables and create Makefiles. -# Generated by GNU Autoconf 2.62 for sqlite 3.8.7. +# Generated by GNU Autoconf 2.62 for sqlite 3.8.7.4. # # Copyright (C) 1992, 1993, 1994, 1995, 1996, 1998, 1999, 2000, 2001, # 2002, 2003, 2004, 2005, 2006, 2007, 2008 Free Software Foundation, Inc. # This configure script is free software; the Free Software Foundation # gives unlimited permission to copy, distribute and modify it. @@ -741,12 +741,12 @@ SHELL=${CONFIG_SHELL-/bin/sh} # Identity of this package. PACKAGE_NAME='sqlite' PACKAGE_TARNAME='sqlite' -PACKAGE_VERSION='3.8.7' -PACKAGE_STRING='sqlite 3.8.7' +PACKAGE_VERSION='3.8.7.4' +PACKAGE_STRING='sqlite 3.8.7.4' PACKAGE_BUGREPORT='' # Factoring default headers for most tests. ac_includes_default="\ #include @@ -1481,11 +1481,11 @@ # if test "$ac_init_help" = "long"; then # Omit some internal or obsolete options to make the list less imposing. # This message is too long to be a string in the A/UX 3.1 sh. cat <<_ACEOF -\`configure' configures sqlite 3.8.7 to adapt to many kinds of systems. +\`configure' configures sqlite 3.8.7.4 to adapt to many kinds of systems. Usage: $0 [OPTION]... [VAR=VALUE]... To assign environment variables (e.g., CC, CFLAGS...), specify them as VAR=VALUE. See below for descriptions of some of the useful variables. @@ -1546,11 +1546,11 @@ _ACEOF fi if test -n "$ac_init_help"; then case $ac_init_help in - short | recursive ) echo "Configuration of sqlite 3.8.7:";; + short | recursive ) echo "Configuration of sqlite 3.8.7.4:";; esac cat <<\_ACEOF Optional Features: --disable-option-checking ignore unrecognized --enable/--with options @@ -1662,11 +1662,11 @@ fi test -n "$ac_init_help" && exit $ac_status if $ac_init_version; then cat <<\_ACEOF -sqlite configure 3.8.7 +sqlite configure 3.8.7.4 generated by GNU Autoconf 2.62 Copyright (C) 1992, 1993, 1994, 1995, 1996, 1998, 1999, 2000, 2001, 2002, 2003, 2004, 2005, 2006, 2007, 2008 Free Software Foundation, Inc. This configure script is free software; the Free Software Foundation @@ -1676,11 +1676,11 @@ fi cat >config.log <<_ACEOF This file contains any messages produced by compilers while running configure, to aid debugging if configure makes a mistake. -It was created by sqlite $as_me 3.8.7, which was +It was created by sqlite $as_me 3.8.7.4, which was generated by GNU Autoconf 2.62. Invocation command line was $ $0 $@ _ACEOF @@ -14019,11 +14019,11 @@ # Save the log message, to keep $[0] and so on meaningful, and to # report actual input values of CONFIG_FILES etc. instead of their # values after options handling. ac_log=" -This file was extended by sqlite $as_me 3.8.7, which was +This file was extended by sqlite $as_me 3.8.7.4, which was generated by GNU Autoconf 2.62. Invocation command line was CONFIG_FILES = $CONFIG_FILES CONFIG_HEADERS = $CONFIG_HEADERS CONFIG_LINKS = $CONFIG_LINKS @@ -14072,11 +14072,11 @@ Report bugs to ." _ACEOF cat >>$CONFIG_STATUS <<_ACEOF || ac_write_fail=1 ac_cs_version="\\ -sqlite config.status 3.8.7 +sqlite config.status 3.8.7.4 configured by $0, generated by GNU Autoconf 2.62, with options \\"`$as_echo "$ac_configure_args" | sed 's/^ //; s/[\\""\`\$]/\\\\&/g'`\\" Copyright (C) 2008 Free Software Foundation, Inc. This config.status script is free software; the Free Software Foundation Index: src/analyze.c ================================================================== --- src/analyze.c +++ src/analyze.c @@ -1589,11 +1589,11 @@ tRowcnt avgEq = 0; tRowcnt nRow; /* Number of rows in index */ i64 nSum100 = 0; /* Number of terms contributing to sumEq */ i64 nDist100; /* Number of distinct values in index */ - if( pIdx->aiRowEst==0 || pIdx->aiRowEst[iCol+1]==0 ){ + if( !pIdx->aiRowEst || iCol>=pIdx->nKeyCol || pIdx->aiRowEst[iCol+1]==0 ){ nRow = pFinal->anLt[iCol]; nDist100 = (i64)100 * pFinal->anDLt[iCol]; nSample--; }else{ nRow = pIdx->aiRowEst[0]; Index: src/backup.c ================================================================== --- src/backup.c +++ src/backup.c @@ -595,11 +595,11 @@ } *pp = p->pNext; } /* If a transaction is still open on the Btree, roll it back. */ - sqlite3BtreeRollback(p->pDest, SQLITE_OK); + sqlite3BtreeRollback(p->pDest, SQLITE_OK, 0); /* Set the error code of the destination database handle. */ rc = (p->rc==SQLITE_DONE) ? SQLITE_OK : p->rc; if( p->pDestDb ){ sqlite3Error(p->pDestDb, rc); Index: src/btree.c ================================================================== --- src/btree.c +++ src/btree.c @@ -2169,11 +2169,11 @@ /* Rollback any active transaction and free the handle structure. ** The call to sqlite3BtreeRollback() drops any table-locks held by ** this handle. */ - sqlite3BtreeRollback(p, SQLITE_OK); + sqlite3BtreeRollback(p, SQLITE_OK, 0); sqlite3BtreeLeave(p); /* If there are still other outstanding references to the shared-btree ** structure, return now. The remainder of this procedure cleans ** up the shared-btree. @@ -3462,64 +3462,95 @@ return rc; } /* ** This routine sets the state to CURSOR_FAULT and the error -** code to errCode for every cursor on BtShared that pBtree -** references. -** -** Every cursor is tripped, including cursors that belong -** to other database connections that happen to be sharing -** the cache with pBtree. -** -** This routine gets called when a rollback occurs. -** All cursors using the same cache must be tripped -** to prevent them from trying to use the btree after -** the rollback. The rollback may have deleted tables -** or moved root pages, so it is not sufficient to -** save the state of the cursor. The cursor must be -** invalidated. -*/ -void sqlite3BtreeTripAllCursors(Btree *pBtree, int errCode){ +** code to errCode for every cursor on any BtShared that pBtree +** references. Or if the writeOnly flag is set to 1, then only +** trip write cursors and leave read cursors unchanged. +** +** Every cursor is a candidate to be tripped, including cursors +** that belong to other database connections that happen to be +** sharing the cache with pBtree. +** +** This routine gets called when a rollback occurs. If the writeOnly +** flag is true, then only write-cursors need be tripped - read-only +** cursors save their current positions so that they may continue +** following the rollback. Or, if writeOnly is false, all cursors are +** tripped. In general, writeOnly is false if the transaction being +** rolled back modified the database schema. In this case b-tree root +** pages may be moved or deleted from the database altogether, making +** it unsafe for read cursors to continue. +** +** If the writeOnly flag is true and an error is encountered while +** saving the current position of a read-only cursor, all cursors, +** including all read-cursors are tripped. +** +** SQLITE_OK is returned if successful, or if an error occurs while +** saving a cursor position, an SQLite error code. +*/ +int sqlite3BtreeTripAllCursors(Btree *pBtree, int errCode, int writeOnly){ BtCursor *p; - if( pBtree==0 ) return; - sqlite3BtreeEnter(pBtree); - for(p=pBtree->pBt->pCursor; p; p=p->pNext){ - int i; - sqlite3BtreeClearCursor(p); - p->eState = CURSOR_FAULT; - p->skipNext = errCode; - for(i=0; i<=p->iPage; i++){ - releasePage(p->apPage[i]); - p->apPage[i] = 0; - } - } - sqlite3BtreeLeave(pBtree); + int rc = SQLITE_OK; + + assert( (writeOnly==0 || writeOnly==1) && BTCF_WriteFlag==1 ); + if( pBtree ){ + sqlite3BtreeEnter(pBtree); + for(p=pBtree->pBt->pCursor; p; p=p->pNext){ + int i; + if( writeOnly && (p->curFlags & BTCF_WriteFlag)==0 ){ + if( p->eState==CURSOR_VALID ){ + rc = saveCursorPosition(p); + if( rc!=SQLITE_OK ){ + (void)sqlite3BtreeTripAllCursors(pBtree, rc, 0); + break; + } + } + }else{ + sqlite3BtreeClearCursor(p); + p->eState = CURSOR_FAULT; + p->skipNext = errCode; + } + for(i=0; i<=p->iPage; i++){ + releasePage(p->apPage[i]); + p->apPage[i] = 0; + } + } + sqlite3BtreeLeave(pBtree); + } + return rc; } /* -** Rollback the transaction in progress. All cursors will be -** invalided by this operation. Any attempt to use a cursor -** that was open at the beginning of this operation will result -** in an error. +** Rollback the transaction in progress. +** +** If tripCode is not SQLITE_OK then cursors will be invalidated (tripped). +** Only write cursors are tripped if writeOnly is true but all cursors are +** tripped if writeOnly is false. Any attempt to use +** a tripped cursor will result in an error. ** ** This will release the write lock on the database file. If there ** are no active cursors, it also releases the read lock. */ -int sqlite3BtreeRollback(Btree *p, int tripCode){ +int sqlite3BtreeRollback(Btree *p, int tripCode, int writeOnly){ int rc; BtShared *pBt = p->pBt; MemPage *pPage1; + assert( writeOnly==1 || writeOnly==0 ); + assert( tripCode==SQLITE_ABORT_ROLLBACK || tripCode==SQLITE_OK ); sqlite3BtreeEnter(p); if( tripCode==SQLITE_OK ){ rc = tripCode = saveAllCursors(pBt, 0, 0); + if( rc ) writeOnly = 0; }else{ rc = SQLITE_OK; } if( tripCode ){ - sqlite3BtreeTripAllCursors(p, tripCode); + int rc2 = sqlite3BtreeTripAllCursors(p, tripCode, writeOnly); + assert( rc==SQLITE_OK || (writeOnly==0 && rc2==SQLITE_OK) ); + if( rc2!=SQLITE_OK ) rc = rc2; } btreeIntegrity(p); if( p->inTrans==TRANS_WRITE ){ int rc2; @@ -3850,17 +3881,13 @@ ** ** This routine cannot fail. It always returns SQLITE_OK. */ int sqlite3BtreeKeySize(BtCursor *pCur, i64 *pSize){ assert( cursorHoldsMutex(pCur) ); - assert( pCur->eState==CURSOR_INVALID || pCur->eState==CURSOR_VALID ); - if( pCur->eState!=CURSOR_VALID ){ - *pSize = 0; - }else{ - getCellInfo(pCur); - *pSize = pCur->info.nKey; - } + assert( pCur->eState==CURSOR_VALID ); + getCellInfo(pCur); + *pSize = pCur->info.nKey; return SQLITE_OK; } /* ** Set *pSize to the number of bytes of data in the entry the Index: src/btree.h ================================================================== --- src/btree.h +++ src/btree.h @@ -81,11 +81,11 @@ int sqlite3BtreeGetAutoVacuum(Btree *); int sqlite3BtreeBeginTrans(Btree*,int); int sqlite3BtreeCommitPhaseOne(Btree*, const char *zMaster); int sqlite3BtreeCommitPhaseTwo(Btree*, int); int sqlite3BtreeCommit(Btree*); -int sqlite3BtreeRollback(Btree*,int); +int sqlite3BtreeRollback(Btree*,int,int); int sqlite3BtreeBeginStmt(Btree*,int); int sqlite3BtreeCreateTable(Btree*, int*, int flags); int sqlite3BtreeIsInTrans(Btree*); int sqlite3BtreeIsInReadTrans(Btree*); int sqlite3BtreeIsInBackup(Btree*); @@ -114,11 +114,11 @@ #define BTREE_BLOBKEY 2 /* Table has keys only - no data */ int sqlite3BtreeDropTable(Btree*, int, int*); int sqlite3BtreeClearTable(Btree*, int, int*); int sqlite3BtreeClearTableOfCursor(BtCursor*); -void sqlite3BtreeTripAllCursors(Btree*, int); +int sqlite3BtreeTripAllCursors(Btree*, int, int); void sqlite3BtreeGetMeta(Btree *pBtree, int idx, u32 *pValue); int sqlite3BtreeUpdateMeta(Btree*, int idx, u32 value); int sqlite3BtreeNewDb(Btree *p); Index: src/btreeInt.h ================================================================== --- src/btreeInt.h +++ src/btreeInt.h @@ -487,10 +487,15 @@ ** but cursors cannot be shared. Each cursor is associated with a ** particular database connection identified BtCursor.pBtree.db. ** ** Fields in this structure are accessed under the BtShared.mutex ** found at self->pBt->mutex. +** +** skipNext meaning: +** eState==SKIPNEXT && skipNext>0: Next sqlite3BtreeNext() is no-op. +** eState==SKIPNEXT && skipNext<0: Next sqlite3BtreePrevious() is no-op. +** eState==FAULT: Cursor fault with skipNext as error code. */ struct BtCursor { Btree *pBtree; /* The Btree to which this cursor belongs */ BtShared *pBt; /* The BtShared this cursor points to */ BtCursor *pNext, *pPrev; /* Forms a linked list of all cursors */ @@ -499,11 +504,12 @@ CellInfo info; /* A parse of the cell we are pointing at */ i64 nKey; /* Size of pKey, or last integer key */ void *pKey; /* Saved key that was cursor last known position */ Pgno pgnoRoot; /* The root page of this tree */ int nOvflAlloc; /* Allocated size of aOverflow[] array */ - int skipNext; /* Prev() is noop if negative. Next() is noop if positive */ + int skipNext; /* Prev() is noop if negative. Next() is noop if positive. + ** Error code if eState==CURSOR_FAULT */ u8 curFlags; /* zero or more BTCF_* flags defined below */ u8 eState; /* One of the CURSOR_XXX constants (see below) */ u8 hints; /* As configured by CursorSetHints() */ i16 iPage; /* Index of current page in apPage */ u16 aiIdx[BTCURSOR_MAX_DEPTH]; /* Current index in apPage[i] */ @@ -545,11 +551,11 @@ ** CURSOR_FAULT: ** An unrecoverable error (an I/O error or a malloc failure) has occurred ** on a different connection that shares the BtShared cache with this ** cursor. The error has left the cache in an inconsistent state. ** Do nothing else with this cursor. Any attempt to use the cursor -** should return the error code stored in BtCursor.skip +** should return the error code stored in BtCursor.skipNext */ #define CURSOR_INVALID 0 #define CURSOR_VALID 1 #define CURSOR_SKIPNEXT 2 #define CURSOR_REQUIRESEEK 3 Index: src/delete.c ================================================================== --- src/delete.c +++ src/delete.c @@ -479,11 +479,11 @@ if( okOnePass ){ /* Just one row. Hence the top-of-loop is a no-op */ assert( nKey==nPk ); /* OP_Found will use an unpacked key */ assert( !IsVirtual(pTab) ); if( aToOpen[iDataCur-iTabCur] ){ - assert( pPk!=0 ); + assert( pPk!=0 || pTab->pSelect!=0 ); sqlite3VdbeAddOp4Int(v, OP_NotFound, iDataCur, addrBypass, iKey, nKey); VdbeCoverage(v); } }else if( pPk ){ addrLoop = sqlite3VdbeAddOp1(v, OP_Rewind, iEphCur); VdbeCoverage(v); Index: src/expr.c ================================================================== --- src/expr.c +++ src/expr.c @@ -1391,11 +1391,12 @@ case TK_FLOAT: case TK_BLOB: return 0; case TK_COLUMN: assert( p->pTab!=0 ); - return p->iColumn>=0 && p->pTab->aCol[p->iColumn].notNull==0; + return ExprHasProperty(p, EP_CanBeNull) || + (p->iColumn>=0 && p->pTab->aCol[p->iColumn].notNull==0); default: return 1; } } @@ -1834,11 +1835,10 @@ sqlite3SelectDestInit(&dest, SRT_Set, pExpr->iTable); dest.affSdst = (u8)affinity; assert( (pExpr->iTable&0x0000FFFF)==pExpr->iTable ); pSelect->iLimit = 0; testcase( pSelect->selFlags & SF_Distinct ); - pSelect->selFlags &= ~SF_Distinct; testcase( pKeyInfo==0 ); /* Caused by OOM in sqlite3KeyInfoAlloc() */ if( sqlite3Select(pParse, pSelect, &dest) ){ sqlite3KeyInfoUnref(pKeyInfo); return 0; } Index: src/main.c ================================================================== --- src/main.c +++ src/main.c @@ -928,10 +928,20 @@ /* Close all database connections */ for(j=0; jnDb; j++){ struct Db *pDb = &db->aDb[j]; if( pDb->pBt ){ + if( pDb->pSchema ){ + /* Must clear the KeyInfo cache. See ticket [e4a18565a36884b00edf] */ + sqlite3BtreeEnter(pDb->pBt); + for(i=sqliteHashFirst(&pDb->pSchema->idxHash); i; i=sqliteHashNext(i)){ + Index *pIdx = sqliteHashData(i); + sqlite3KeyInfoUnref(pIdx->pKeyInfo); + pIdx->pKeyInfo = 0; + } + sqlite3BtreeLeave(pDb->pBt); + } sqlite3BtreeClose(pDb->pBt); pDb->pBt = 0; if( j!=1 ){ pDb->pSchema = 0; } @@ -1014,17 +1024,19 @@ sqlite3_free(db); } /* ** Rollback all database files. If tripCode is not SQLITE_OK, then -** any open cursors are invalidated ("tripped" - as in "tripping a circuit +** any write cursors are invalidated ("tripped" - as in "tripping a circuit ** breaker") and made to return tripCode if there are any further -** attempts to use that cursor. +** attempts to use that cursor. Read cursors remain open and valid +** but are "saved" in case the table pages are moved around. */ void sqlite3RollbackAll(sqlite3 *db, int tripCode){ int i; int inTrans = 0; + int schemaChange; assert( sqlite3_mutex_held(db->mutex) ); sqlite3BeginBenignMalloc(); /* Obtain all b-tree mutexes before making any calls to BtreeRollback(). ** This is important in case the transaction being rolled back has @@ -1031,18 +1043,19 @@ ** modified the database schema. If the b-tree mutexes are not taken ** here, then another shared-cache connection might sneak in between ** the database rollback and schema reset, which can cause false ** corruption reports in some cases. */ sqlite3BtreeEnterAll(db); + schemaChange = (db->flags & SQLITE_InternChanges)!=0 && db->init.busy==0; for(i=0; inDb; i++){ Btree *p = db->aDb[i].pBt; if( p ){ if( sqlite3BtreeIsInTrans(p) ){ inTrans = 1; } - sqlite3BtreeRollback(p, tripCode); + sqlite3BtreeRollback(p, tripCode, !schemaChange); } } sqlite3VtabRollback(db); sqlite3EndBenignMalloc(); @@ -2572,11 +2585,13 @@ rc = SQLITE_NOMEM; } sqlite3Error(db, rc); goto opendb_out; } + sqlite3BtreeEnter(db->aDb[0].pBt); db->aDb[0].pSchema = sqlite3SchemaGet(db, db->aDb[0].pBt); + sqlite3BtreeLeave(db->aDb[0].pBt); db->aDb[1].pSchema = sqlite3SchemaGet(db, 0); /* The default safety_level for the main database is 'full'; for the temp ** database it is 'NONE'. This matches the pager layer defaults. */ Index: src/pager.c ================================================================== --- src/pager.c +++ src/pager.c @@ -1939,10 +1939,18 @@ }else if( pPager->journalMode==PAGER_JOURNALMODE_TRUNCATE ){ if( pPager->journalOff==0 ){ rc = SQLITE_OK; }else{ rc = sqlite3OsTruncate(pPager->jfd, 0); + if( rc==SQLITE_OK && pPager->fullSync ){ + /* Make sure the new file size is written into the inode right away. + ** Otherwise the journal might resurrect following a power loss and + ** cause the last transaction to roll back. See + ** https://bugzilla.mozilla.org/show_bug.cgi?id=1072773 + */ + rc = sqlite3OsSync(pPager->jfd, pPager->syncFlags); + } } pPager->journalOff = 0; }else if( pPager->journalMode==PAGER_JOURNALMODE_PERSIST || (pPager->exclusiveMode && pPager->journalMode!=PAGER_JOURNALMODE_WAL) ){ Index: src/printf.c ================================================================== --- src/printf.c +++ src/printf.c @@ -19,15 +19,11 @@ ** HAVE_STRCHRNUL. If that routine is not available, this module ** will supply its own. The built-in version is slower than ** the glibc version so the glibc version is definitely preferred. */ #if !defined(HAVE_STRCHRNUL) -# if defined(linux) -# define HAVE_STRCHRNUL 1 -# else -# define HAVE_STRCHRNUL 0 -# endif +# define HAVE_STRCHRNUL 0 #endif /* ** Conversion types fall into various categories as defined by the @@ -214,11 +210,11 @@ sqlite_uint64 longvalue; /* Value for integer types */ LONGDOUBLE_TYPE realvalue; /* Value for real types */ const et_info *infop; /* Pointer to the appropriate info structure */ char *zOut; /* Rendering buffer */ int nOut; /* Size of the rendering buffer */ - char *zExtra; /* Malloced memory used by some conversion */ + char *zExtra = 0; /* Malloced memory used by some conversion */ #ifndef SQLITE_OMIT_FLOATING_POINT int exp, e2; /* exponent of real numbers */ int nsd; /* Number of significant digits returned */ double rounder; /* Used for rounding floating point values */ etByte flag_dp; /* True if decimal point should be shown */ @@ -331,11 +327,10 @@ return; } break; } } - zExtra = 0; /* ** At this point, variables are initialized as follows: ** ** flag_alternateform TRUE if a '#' is present. @@ -622,17 +617,20 @@ bufpt = getTextArg(pArgList); c = bufpt ? bufpt[0] : 0; }else{ c = va_arg(ap,int); } - buf[0] = (char)c; - if( precision>=0 ){ - for(idx=1; idx1 ){ + width -= precision-1; + if( width>1 && !flag_leftjustify ){ + sqlite3AppendChar(pAccum, width-1, ' '); + width = 0; + } + sqlite3AppendChar(pAccum, precision-1, c); } + length = 1; + buf[0] = c; bufpt = buf; break; case etSTRING: case etDYNSTRING: if( bArgList ){ @@ -729,15 +727,18 @@ ** The text of the conversion is pointed to by "bufpt" and is ** "length" characters long. The field width is "width". Do ** the output. */ width -= length; - if( width>0 && !flag_leftjustify ) sqlite3AppendSpace(pAccum, width); + if( width>0 && !flag_leftjustify ) sqlite3AppendChar(pAccum, width, ' '); sqlite3StrAccumAppend(pAccum, bufpt, length); - if( width>0 && flag_leftjustify ) sqlite3AppendSpace(pAccum, width); + if( width>0 && flag_leftjustify ) sqlite3AppendChar(pAccum, width, ' '); - if( zExtra ) sqlite3_free(zExtra); + if( zExtra ){ + sqlite3_free(zExtra); + zExtra = 0; + } }/* End for loop over the format string */ } /* End of function */ /* ** Enlarge the memory allocation on a StrAccum object so that it is @@ -786,15 +787,15 @@ } return N; } /* -** Append N space characters to the given string buffer. +** Append N copies of character c to the given string buffer. */ -void sqlite3AppendSpace(StrAccum *p, int N){ +void sqlite3AppendChar(StrAccum *p, int N, char c){ if( p->nChar+N >= p->nAlloc && (N = sqlite3StrAccumEnlarge(p, N))<=0 ) return; - while( (N--)>0 ) p->zText[p->nChar++] = ' '; + while( (N--)>0 ) p->zText[p->nChar++] = c; } /* ** The StrAccum "p" is not large enough to accept N new bytes of z[]. ** So enlarge if first, then do the append. Index: src/resolve.c ================================================================== --- src/resolve.c +++ src/resolve.c @@ -318,10 +318,14 @@ } } if( pMatch ){ pExpr->iTable = pMatch->iCursor; pExpr->pTab = pMatch->pTab; + assert( (pMatch->jointype & JT_RIGHT)==0 ); /* RIGHT JOIN not (yet) supported */ + if( (pMatch->jointype & JT_LEFT)!=0 ){ + ExprSetProperty(pExpr, EP_CanBeNull); + } pSchema = pExpr->pTab->pSchema; } } /* if( pSrcList ) */ #ifndef SQLITE_OMIT_TRIGGER Index: src/sqliteInt.h ================================================================== --- src/sqliteInt.h +++ src/sqliteInt.h @@ -2012,11 +2012,11 @@ }; /* ** The following are the meanings of bits in the Expr.flags field. */ -#define EP_FromJoin 0x000001 /* Originated in ON or USING clause of a join */ +#define EP_FromJoin 0x000001 /* Originates in ON/USING clause of outer join */ #define EP_Agg 0x000002 /* Contains one or more aggregate functions */ #define EP_Resolved 0x000004 /* IDs have been resolved to COLUMNs */ #define EP_Error 0x000008 /* Expression contains one or more errors */ #define EP_Distinct 0x000010 /* Aggregate function with DISTINCT keyword */ #define EP_VarSelect 0x000020 /* pSelect is correlated, not constant */ @@ -2032,10 +2032,11 @@ #define EP_Static 0x008000 /* Held in memory not obtained from malloc() */ #define EP_MemToken 0x010000 /* Need to sqlite3DbFree() Expr.zToken */ #define EP_NoReduce 0x020000 /* Cannot EXPRDUP_REDUCE this Expr */ #define EP_Unlikely 0x040000 /* unlikely() or likelihood() function */ #define EP_Constant 0x080000 /* Node is a constant */ +#define EP_CanBeNull 0x100000 /* Can be null despite NOT NULL constraint */ /* ** These macros can be used to test, set, or clear bits in the ** Expr.flags field. */ @@ -3537,11 +3538,11 @@ int sqlite3OpenTempDatabase(Parse *); void sqlite3StrAccumInit(StrAccum*, char*, int, int); void sqlite3StrAccumAppend(StrAccum*,const char*,int); void sqlite3StrAccumAppendAll(StrAccum*,const char*); -void sqlite3AppendSpace(StrAccum*,int); +void sqlite3AppendChar(StrAccum*,int,char); char *sqlite3StrAccumFinish(StrAccum*); void sqlite3StrAccumReset(StrAccum*); void sqlite3SelectDestInit(SelectDest*,int,int); Expr *sqlite3CreateColumnExpr(sqlite3 *, SrcList *, int, int); Index: src/update.c ================================================================== --- src/update.c +++ src/update.c @@ -429,12 +429,12 @@ 0, 0); } /* Top of the update loop */ if( okOnePass ){ - if( aToOpen[iDataCur-iBaseCur] ){ - assert( pPk!=0 ); + if( aToOpen[iDataCur-iBaseCur] && !isView ){ + assert( pPk ); sqlite3VdbeAddOp4Int(v, OP_NotFound, iDataCur, labelBreak, regKey, nKey); VdbeCoverageNeverTaken(v); } labelContinue = labelBreak; sqlite3VdbeAddOp2(v, OP_IsNull, pPk ? regKey : regOldRowid, labelBreak); Index: src/vdbe.c ================================================================== --- src/vdbe.c +++ src/vdbe.c @@ -2296,11 +2296,11 @@ assert( pReg->flags & MEM_Blob ); assert( memIsValid(pReg) ); pC->payloadSize = pC->szRow = avail = pReg->n; pC->aRow = (u8*)pReg->z; }else{ - MemSetTypeFlag(pDest, MEM_Null); + sqlite3VdbeMemSetNull(pDest); goto op_column_out; } }else{ assert( pCrsr ); if( pC->isTable==0 ){ @@ -2439,11 +2439,11 @@ */ if( pC->nHdrParsed<=p2 ){ if( pOp->p4type==P4_MEM ){ sqlite3VdbeMemShallowCopy(pDest, pOp->p4.pMem, MEM_Static); }else{ - MemSetTypeFlag(pDest, MEM_Null); + sqlite3VdbeMemSetNull(pDest); } goto op_column_out; } } @@ -2820,23 +2820,30 @@ goto vdbe_return; } db->isTransactionSavepoint = 0; rc = p->rc; }else{ + int isSchemaChange; iSavepoint = db->nSavepoint - iSavepoint - 1; if( p1==SAVEPOINT_ROLLBACK ){ + isSchemaChange = (db->flags & SQLITE_InternChanges)!=0; for(ii=0; iinDb; ii++){ - sqlite3BtreeTripAllCursors(db->aDb[ii].pBt, SQLITE_ABORT); + rc = sqlite3BtreeTripAllCursors(db->aDb[ii].pBt, + SQLITE_ABORT_ROLLBACK, + isSchemaChange==0); + if( rc!=SQLITE_OK ) goto abort_due_to_error; } + }else{ + isSchemaChange = 0; } for(ii=0; iinDb; ii++){ rc = sqlite3BtreeSavepoint(db->aDb[ii].pBt, p1, iSavepoint); if( rc!=SQLITE_OK ){ goto abort_due_to_error; } } - if( p1==SAVEPOINT_ROLLBACK && (db->flags&SQLITE_InternChanges)!=0 ){ + if( isSchemaChange ){ sqlite3ExpirePreparedStatements(db); sqlite3ResetAllSchemasOfConnection(db); db->flags = (db->flags | SQLITE_InternChanges); } } @@ -3229,11 +3236,11 @@ assert( p->bIsReader ); assert( pOp->opcode==OP_OpenRead || pOp->opcode==OP_ReopenIdx || p->readOnly==0 ); if( p->expired ){ - rc = SQLITE_ABORT; + rc = SQLITE_ABORT_ROLLBACK; break; } nField = 0; pKeyInfo = 0; @@ -4396,10 +4403,14 @@ #endif /* SQLITE_OMIT_VIRTUALTABLE */ }else{ assert( pC->pCursor!=0 ); rc = sqlite3VdbeCursorRestore(pC); if( rc ) goto abort_due_to_error; + if( pC->nullRow ){ + pOut->flags = MEM_Null; + break; + } rc = sqlite3BtreeKeySize(pC->pCursor, &v); assert( rc==SQLITE_OK ); /* Always so because of CursorRestore() above */ } pOut->u.i = v; break; Index: src/wal.c ================================================================== --- src/wal.c +++ src/wal.c @@ -2523,11 +2523,10 @@ assert( walFramePgno(pWal, iFrame)!=1 ); rc = xUndo(pUndoCtx, walFramePgno(pWal, iFrame)); } if( iMax!=pWal->hdr.mxFrame ) walCleanupHash(pWal); } - assert( rc==SQLITE_OK ); return rc; } /* ** Argument aWalData must point to an array of WAL_SAVEPOINT_NDATA u32 Index: test/capi3.test ================================================================== --- test/capi3.test +++ test/capi3.test @@ -910,24 +910,24 @@ do_test capi3-11.9.3 { sqlite3_get_autocommit $DB } 1 do_test capi3-11.10 { sqlite3_step $STMT -} {SQLITE_ERROR} +} {SQLITE_ROW} ifcapable !autoreset { # If SQLITE_OMIT_AUTORESET is defined, then the statement must be # reset() before it can be passed to step() again. do_test capi3-11.11a { sqlite3_step $STMT } {SQLITE_MISUSE} do_test capi3-11.11b { sqlite3_reset $STMT } {SQLITE_ABORT} } do_test capi3-11.11 { sqlite3_step $STMT -} {SQLITE_ROW} +} {SQLITE_DONE} do_test capi3-11.12 { sqlite3_step $STMT sqlite3_step $STMT -} {SQLITE_DONE} +} {SQLITE_ROW} do_test capi3-11.13 { sqlite3_finalize $STMT } {SQLITE_OK} do_test capi3-11.14 { execsql { Index: test/capi3c.test ================================================================== --- test/capi3c.test +++ test/capi3c.test @@ -861,24 +861,24 @@ do_test capi3c-11.9.3 { sqlite3_get_autocommit $DB } 1 do_test capi3c-11.10 { sqlite3_step $STMT -} {SQLITE_ABORT} +} {SQLITE_ROW} ifcapable !autoreset { # If SQLITE_OMIT_AUTORESET is defined, then the statement must be # reset() before it can be passed to step() again. do_test capi3-11.11a { sqlite3_step $STMT } {SQLITE_MISUSE} do_test capi3-11.11b { sqlite3_reset $STMT } {SQLITE_ABORT} } do_test capi3c-11.11 { sqlite3_step $STMT -} {SQLITE_ROW} +} {SQLITE_DONE} do_test capi3c-11.12 { sqlite3_step $STMT sqlite3_step $STMT -} {SQLITE_DONE} +} {SQLITE_ROW} do_test capi3c-11.13 { sqlite3_finalize $STMT } {SQLITE_OK} do_test capi3c-11.14 { execsql { Index: test/in5.test ================================================================== --- test/in5.test +++ test/in5.test @@ -10,10 +10,11 @@ #*********************************************************************** # set testdir [file dirname $argv0] source $testdir/tester.tcl +set testprefix in5 do_test in5-1.1 { execsql { CREATE TABLE t1x(x INTEGER PRIMARY KEY); INSERT INTO t1x VALUES(1),(3),(5),(7),(9); @@ -132,7 +133,54 @@ do_test in5-5.3 { regexp {OpenEphemeral} [db eval { EXPLAIN SELECT d FROM t2 WHERE a IN t1x AND b IN t1y AND c IN t1z }] } {0} + +#------------------------------------------------------------------------- +# At one point SQLite was removing the DISTINCT keyword from expressions +# similar to: +# +# IN (SELECT DISTINCT FROM...) +# +# However, there are a few obscure cases where this is incorrect. For +# example, if the SELECT features a LIMIT clause, or if the collation +# sequence or affinity used by the DISTINCT does not match the one used +# by the IN(...) expression. +# +do_execsql_test 6.1.1 { + CREATE TABLE t1(a COLLATE nocase); + INSERT INTO t1 VALUES('one'); + INSERT INTO t1 VALUES('ONE'); +} +do_execsql_test 6.1.2 { + SELECT count(*) FROM t1 WHERE a COLLATE BINARY IN (SELECT DISTINCT a FROM t1) +} {1} + +do_execsql_test 6.2.1 { + CREATE TABLE t3(a, b); + INSERT INTO t3 VALUES(1, 1); + INSERT INTO t3 VALUES(1, 2); + INSERT INTO t3 VALUES(1, 3); + INSERT INTO t3 VALUES(2, 4); + INSERT INTO t3 VALUES(2, 5); + INSERT INTO t3 VALUES(2, 6); + INSERT INTO t3 VALUES(3, 7); + INSERT INTO t3 VALUES(3, 8); + INSERT INTO t3 VALUES(3, 9); +} +do_execsql_test 6.2.2 { + SELECT count(*) FROM t3 WHERE b IN (SELECT DISTINCT a FROM t3 LIMIT 5); +} {3} +do_execsql_test 6.2.3 { + SELECT count(*) FROM t3 WHERE b IN (SELECT a FROM t3 LIMIT 5); +} {2} + +do_execsql_test 6.3.1 { + CREATE TABLE x1(a); + CREATE TABLE x2(b); + INSERT INTO x1 VALUES(1), (1), (2); + INSERT INTO x2 VALUES(1), (2); + SELECT count(*) FROM x2 WHERE b IN (SELECT DISTINCT a FROM x1 LIMIT 2); +} {2} finish_test Index: test/ioerr2.test ================================================================== --- test/ioerr2.test +++ test/ioerr2.test @@ -110,10 +110,16 @@ foreach {::go res} [catchsql $sql] {} check_db ioerr2-4.[expr {$bPersist+2}].$::N } } +# When this test was written, an IO error within the UPDATE statement caused +# a rollback, which tripped all read-cursors, causing the outer SELECT to +# fail with "abort due to ROLLBACK". Now, the loop continues until the UPDATE +# is run successfully. At this point the next IO error occurs within the +# SELECT - throwing the "disk I/O error" that the test case now expects. +# do_test ioerr2-5 { execsql { CREATE TABLE t2 AS SELECT * FROM t1; PRAGMA temp_store = memory; } @@ -128,11 +134,11 @@ foreach {::go res} [catchsql $sql] {} } } } msg] list $rc $msg -} {1 {abort due to ROLLBACK}} +} {1 {disk I/O error}} ;# used to be "{1 {abort due to ROLLBACK}}" if {$::tcl_platform(platform) == "unix"} { # Cause the call to xAccess used by [pragma temp_store_directory] to # determine if the specified directory is writable to fail. This causes # SQLite to report "not a writable directory", which is probably the Index: test/join5.test ================================================================== --- test/join5.test +++ test/join5.test @@ -104,7 +104,61 @@ } {} do_test join5-2.12 { execsql {SELECT * FROM xy LEFT JOIN ab ON NULL WHERE NULL} } {} +# Ticket https://www.sqlite.org/src/tktview/6f2222d550f5b0ee7ed37601 +# Incorrect output on a LEFT JOIN. +# +do_execsql_test join5-3.1 { + DROP TABLE IF EXISTS t1; + DROP TABLE IF EXISTS t2; + DROP TABLE IF EXISTS t3; + CREATE TABLE x1(a); + INSERT INTO x1 VALUES(1); + CREATE TABLE x2(b NOT NULL); + CREATE TABLE x3(c, d); + INSERT INTO x3 VALUES('a', NULL); + INSERT INTO x3 VALUES('b', NULL); + INSERT INTO x3 VALUES('c', NULL); + SELECT * FROM x1 LEFT JOIN x2 LEFT JOIN x3 ON x3.d = x2.b; +} {1 {} {} {}} +do_execsql_test join5-3.2 { + DROP TABLE IF EXISTS t1; + DROP TABLE IF EXISTS t2; + DROP TABLE IF EXISTS t3; + DROP TABLE IF EXISTS t4; + DROP TABLE IF EXISTS t5; + CREATE TABLE t1(x text NOT NULL, y text); + CREATE TABLE t2(u text NOT NULL, x text NOT NULL); + CREATE TABLE t3(w text NOT NULL, v text); + CREATE TABLE t4(w text NOT NULL, z text NOT NULL); + CREATE TABLE t5(z text NOT NULL, m text); + INSERT INTO t1 VALUES('f6d7661f-4efe-4c90-87b5-858e61cd178b',NULL); + INSERT INTO t1 VALUES('f6ea82c3-2cad-45ce-ae8f-3ddca4fb2f48',NULL); + INSERT INTO t1 VALUES('f6f47499-ecb4-474b-9a02-35be73c235e5',NULL); + INSERT INTO t1 VALUES('56f47499-ecb4-474b-9a02-35be73c235e5',NULL); + INSERT INTO t3 VALUES('007f2033-cb20-494c-b135-a1e4eb66130c', + 'f6d7661f-4efe-4c90-87b5-858e61cd178b'); + SELECT * + FROM t3 + INNER JOIN t1 ON t1.x= t3.v AND t1.y IS NULL + LEFT JOIN t4 ON t4.w = t3.w + LEFT JOIN t5 ON t5.z = t4.z + LEFT JOIN t2 ON t2.u = t5.m + LEFT JOIN t1 xyz ON xyz.y = t2.x; +} {007f2033-cb20-494c-b135-a1e4eb66130c f6d7661f-4efe-4c90-87b5-858e61cd178b f6d7661f-4efe-4c90-87b5-858e61cd178b {} {} {} {} {} {} {} {} {}} +do_execsql_test join5-3.3 { + DROP TABLE IF EXISTS x1; + DROP TABLE IF EXISTS x2; + DROP TABLE IF EXISTS x3; + CREATE TABLE x1(a); + INSERT INTO x1 VALUES(1); + CREATE TABLE x2(b NOT NULL); + CREATE TABLE x3(c, d); + INSERT INTO x3 VALUES('a', NULL); + INSERT INTO x3 VALUES('b', NULL); + INSERT INTO x3 VALUES('c', NULL); + SELECT * FROM x1 LEFT JOIN x2 JOIN x3 WHERE x3.d = x2.b; +} {} finish_test Index: test/printf2.test ================================================================== --- test/printf2.test +++ test/printf2.test @@ -92,8 +92,28 @@ # string for %s. # do_execsql_test printf2-2.3 { SELECT printf('%s=(%d/%g/%s)',a) FROM t1 ORDER BY a; } {-1=(0/0/) 1=(0/0/) 1.5=(0/0/) abc=(0/0/)} + +# The precision of the %c conversion causes the character to repeat. +# +do_execsql_test printf2-3.1 { + SELECT printf('|%110.100c|','*'); +} {{| ****************************************************************************************************|}} +do_execsql_test printf2-3.2 { + SELECT printf('|%-110.100c|','*'); +} {{|**************************************************************************************************** |}} +do_execsql_test printf2-3.3 { + SELECT printf('|%9.8c|%-9.8c|','*','*'); +} {{| ********|******** |}} +do_execsql_test printf2-3.4 { + SELECT printf('|%8.8c|%-8.8c|','*','*'); +} {|********|********|} +do_execsql_test printf2-3.5 { + SELECT printf('|%7.8c|%-7.8c|','*','*'); +} {|********|********|} + + finish_test Index: test/rollback.test ================================================================== --- test/rollback.test +++ test/rollback.test @@ -58,15 +58,15 @@ # Try to continue with the SELECT statement # do_test rollback-1.5 { sqlite3_step $STMT - } {SQLITE_ERROR} + } {SQLITE_ROW} # Restart the SELECT statement # - do_test rollback-1.6 { sqlite3_reset $STMT } {SQLITE_ABORT} + do_test rollback-1.6 { sqlite3_reset $STMT } {SQLITE_OK} } else { do_test rollback-1.6 { sqlite3_reset $STMT } {SQLITE_OK} } do_test rollback-1.7 { ADDED test/rollback2.test Index: test/rollback2.test ================================================================== --- /dev/null +++ test/rollback2.test @@ -0,0 +1,74 @@ +# 2014 November 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. +# +#*********************************************************************** +# + +set testdir [file dirname $argv0] +source $testdir/tester.tcl +set ::testprefix rollback2 + +proc int2hex {i} { format %.2X $i } +db func int2hex int2hex + +do_execsql_test 1.0 { + SELECT int2hex(0), int2hex(100), int2hex(255) +} {00 64 FF} +do_execsql_test 1.1 { + CREATE TABLE t1(i, h); + CREATE INDEX i1 ON t1(h); + WITH data(a, b) AS ( + SELECT 1, int2hex(1) + UNION ALL + SELECT a+1, int2hex(a+1) FROM data WHERE a<40 + ) + INSERT INTO t1 SELECT * FROM data; +} {} + + +proc do_rollback_test {tn args} { + set A(-setup) "" + set A(-select) "" + set A(-result) "" + set A(-rollback) ROLLBACK + + array set O $args + foreach k [array names O] { + if {[info exists A($k)]==0} { error "unknown option: $k" } + set A($k) $O($k) + } + + for {set iRollback 0} 1 {incr iRollback} { + catch { db eval ROLLBACK } + set res [list] + db eval $A(-setup) + + set i 0 + db eval $A(-select) x { + if {$i==$iRollback} { db eval $A(-rollback) } + foreach k $x(*) { lappend res $x($k) } + incr i + } + + do_test $tn.$iRollback [list set {} $res] [list {*}$A(-result)] + if {$i < $iRollback} break + } +} + +do_rollback_test 2 -setup { + BEGIN; + DELETE FROM t1 WHERE (i%2)==1; +} -select { + SELECT i FROM t1 WHERE (i%2)==0 +} -result { + 2 4 6 8 10 12 14 16 18 20 22 24 26 28 30 32 34 36 38 40 +} + +finish_test + Index: test/savepoint.test ================================================================== --- test/savepoint.test +++ test/savepoint.test @@ -313,11 +313,11 @@ catchsql {ROLLBACK TO def} } {0 {}} do_test savepoint-5.3.2.3 { set rc [catch {seek $fd 0; read $fd} res] set rc - } {1} + } {0} do_test savepoint-5.3.3 { catchsql {RELEASE def} } {0 {}} do_test savepoint-5.3.4 { close $fd Index: test/savepoint7.test ================================================================== --- test/savepoint7.test +++ test/savepoint7.test @@ -28,10 +28,11 @@ SAVEPOINT x1; } db eval {SELECT * FROM t1} { db eval { SAVEPOINT x2; + CREATE TABLE IF NOT EXISTS t3(xyz); INSERT INTO t2 VALUES($a,$b,$c); RELEASE x2; } } db eval {SELECT * FROM t2; RELEASE x1} @@ -44,11 +45,11 @@ SAVEPOINT x2; INSERT INTO t2 VALUES($a,$b,$c); RELEASE x2; } } - db eval {SELECT * FROM t2} + db eval {SELECT * FROM t2;} } {1 2 3 4 5 6 7 8 9} do_test savepoint7-1.3 { db eval {DELETE FROM t2; BEGIN;} db eval {SELECT * FROM t1} { @@ -63,11 +64,11 @@ # However, a ROLLBACK of an inner savepoint will abort all queries, including # queries in outer contexts. # do_test savepoint7-2.1 { - db eval {DELETE FROM t2; SAVEPOINT x1;} + db eval {DELETE FROM t2; SAVEPOINT x1; CREATE TABLE t4(abc);} set rc [catch { db eval {SELECT * FROM t1} { db eval { SAVEPOINT x2; INSERT INTO t2 VALUES($a,$b,$c); @@ -75,22 +76,23 @@ } } } msg] db eval {RELEASE x1} list $rc $msg [db eval {SELECT * FROM t2}] -} {1 {callback requested query abort} {}} +} {1 {abort due to ROLLBACK} {}} do_test savepoint7-2.2 { db eval {DELETE FROM t2;} set rc [catch { db eval {SELECT * FROM t1} { db eval { SAVEPOINT x2; + CREATE TABLE t5(pqr); INSERT INTO t2 VALUES($a,$b,$c); ROLLBACK TO x2; } } } msg] list $rc $msg [db eval {SELECT * FROM t2}] -} {1 {callback requested query abort} {}} +} {1 {abort due to ROLLBACK} {}} finish_test ADDED test/sharedB.test Index: test/sharedB.test ================================================================== --- /dev/null +++ test/sharedB.test @@ -0,0 +1,60 @@ +# 2014-12-05 +# +# 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. +# +#*********************************************************************** +# +# Open two database connections on the same database in shared cache +# mode. Hold one open while repeatedly closing, reopening, and using +# the second. +# +# This test is designed to demostrate that the fix for ticket +# [e4a18565a36884b00edf66541f38c693827968ab] works. +# + + +set testdir [file dirname $argv0] +source $testdir/tester.tcl +if {[run_thread_tests]==0} { finish_test ; return } +db close +set ::testprefix sharedB + +set ::enable_shared_cache [sqlite3_enable_shared_cache 1] + +#------------------------------------------------------------------------- +# +do_test 1.1 { + sqlite3 db1 test.db + sqlite3 db2 test.db + + db1 eval { + CREATE TABLE t1(x,y TEXT COLLATE nocase); + WITH RECURSIVE + c(i) AS (VALUES(1) UNION ALL SELECT i+1 FROM c WHERE i<100) + INSERT INTO t1(x,y) SELECT i, printf('x%03dy',i) FROM c; + CREATE INDEX t1yx ON t1(y,x); + } + db2 eval { + SELECT x FROM t1 WHERE y='X014Y'; + } +} {14} + +for {set j 1} {$j<=100} {incr j} { + do_test 1.2.$j { + db2 close + sqlite3 db2 test.db + db2 eval { + SELECT x FROM t1 WHERE y='X014Y'; + } + } {14} +} + +db1 close +db2 close +sqlite3_enable_shared_cache $::enable_shared_cache +finish_test Index: test/shared_err.test ================================================================== --- test/shared_err.test +++ test/shared_err.test @@ -444,13 +444,19 @@ ($rc1=="SQLITE_ERROR" && $rc2=="SQLITE_CORRUPT") } } {1} db2 close } + +# When this test case was written, OOM errors in write statements would +# cause transaction rollback, which would trip cursors in other statements, +# aborting them. This no longer happens. +# do_test shared_malloc-8.X { # Test that one or more queries were aborted due to the malloc() failure. - expr $::aborted>=1 + # expr $::aborted>=1 + expr $::aborted==0 } {1} # This test is designed to catch a specific bug that was present during # development of 3.5.0. If a malloc() failed while setting the page-size, # a buffer (Pager.pTmpSpace) was being freed. This could cause a seg-fault Index: test/table.test ================================================================== --- test/table.test +++ test/table.test @@ -9,11 +9,10 @@ # #*********************************************************************** # This file implements regression tests for SQLite library. The # focus of this file is testing the CREATE TABLE statement. # -# $Id: table.test,v 1.53 2009/06/05 17:09:12 drh Exp $ set testdir [file dirname $argv0] source $testdir/tester.tcl # Create a basic table and verify it is added to sqlite_master @@ -770,7 +769,24 @@ SELECT rowid, x FROM t16; } {1 {unknown function: group_concat()}} do_catchsql_test table-16.7 { INSERT INTO t16 DEFAULT VALUES; } {1 {unknown function: group_concat()}} + +# Ticket [https://www.sqlite.org/src/info/094d39a4c95ee4abbc417f04214617675ba15c63] +# describes a assertion fault that occurs on a CREATE TABLE .. AS SELECT statement. +# the following test verifies that the problem has been fixed. +# +do_execsql_test table-17.1 { + DROP TABLE IF EXISTS t1; + CREATE TABLE t1(a TEXT); + INSERT INTO t1(a) VALUES(1),(2); + DROP TABLE IF EXISTS t2; + CREATE TABLE t2(x TEXT, y TEXT); + INSERT INTO t2(x,y) VALUES(3,4); + DROP TABLE IF EXISTS t3; + CREATE TABLE t3 AS + SELECT a AS p, coalesce(y,a) AS q FROM t1 LEFT JOIN t2 ON a=x; + SELECT p, q, '|' FROM t3 ORDER BY p; +} {1 1 | 2 2 |} finish_test Index: test/threadtest3.c ================================================================== --- test/threadtest3.c +++ test/threadtest3.c @@ -1391,12 +1391,15 @@ join_all_threads(&err, &threads); print_and_free_err(&err); } + + #include "tt3_checkpoint.c" +#include "tt3_index.c" int main(int argc, char **argv){ struct ThreadTest { void (*xTest)(int); const char *zTest; @@ -1412,10 +1415,12 @@ { cgt_pager_1, "cgt_pager_1", 0 }, { dynamic_triggers, "dynamic_triggers", 20000 }, { checkpoint_starvation_1, "checkpoint_starvation_1", 10000 }, { checkpoint_starvation_2, "checkpoint_starvation_2", 10000 }, + + { create_drop_index_1, "create_drop_index_1", 10000 }, }; int i; char *zTest = 0; int nTest = 0; Index: test/tkt-f777251dc7a.test ================================================================== --- test/tkt-f777251dc7a.test +++ test/tkt-f777251dc7a.test @@ -36,12 +36,14 @@ catch {db eval {INSERT OR ROLLBACK INTO t1 VALUES(1)}} } db function force_rollback force_rollback do_test tkt-f7772-1.2 { +breakpoint catchsql { BEGIN IMMEDIATE; + CREATE TABLE xyzzy(abc); SELECT x, force_rollback(), EXISTS(SELECT 1 FROM t3 WHERE w=x) FROM t2; } } {1 {abort due to ROLLBACK}} do_test tkt-f7772-1.3 { sqlite3_get_autocommit db @@ -65,11 +67,11 @@ CREATE TEMP TABLE t3(w, z); } catchsql { SELECT x, force_rollback(), EXISTS(SELECT 1 FROM t3 WHERE w=x) FROM t2 } -} {1 {callback requested query abort}} +} {1 {abort due to ROLLBACK}} do_test tkt-f7772-2.3 { sqlite3_get_autocommit db } {1} do_test tkt-f7772-3.1 { Index: test/trans3.test ================================================================== --- test/trans3.test +++ test/trans3.test @@ -50,11 +50,11 @@ } 1 do_test trans3-1.4 { db eval {SELECT * FROM t1} } {1 2 3 4} do_test trans3-1.5 { - db eval BEGIN + db eval {BEGIN; CREATE TABLE xyzzy(abc);} db eval {INSERT INTO t1 VALUES(5);} set ::ecode {} set x [catch { db eval {SELECT * FROM t1} { if {[catch {db eval ROLLBACK} errmsg]} { Index: test/trigger9.test ================================================================== --- test/trigger9.test +++ test/trigger9.test @@ -30,10 +30,11 @@ source $testdir/tester.tcl ifcapable {!trigger} { finish_test return } +set ::testprefix trigger9 proc has_rowdata {sql} { expr {[lsearch [execsql "explain $sql"] RowData]>=0} } @@ -217,7 +218,39 @@ SELECT * FROM t2; ROLLBACK; } } {2} } + +reset_db +do_execsql_test 4.1 { + CREATE TABLE t1(a, b); + CREATE TABLE log(x); + INSERT INTO t1 VALUES(1, 2); + INSERT INTO t1 VALUES(3, 4); + CREATE VIEW v1 AS SELECT a, b FROM t1; + + CREATE TRIGGER tr1 INSTEAD OF DELETE ON v1 BEGIN + INSERT INTO log VALUES('delete'); + END; + + CREATE TRIGGER tr2 INSTEAD OF UPDATE ON v1 BEGIN + INSERT INTO log VALUES('update'); + END; + + CREATE TRIGGER tr3 INSTEAD OF INSERT ON v1 BEGIN + INSERT INTO log VALUES('insert'); + END; +} + +do_execsql_test 4.2 { + DELETE FROM v1 WHERE rowid=1; +} {} + +do_execsql_test 4.3 { + UPDATE v1 SET a=b WHERE rowid=2; +} {} + + + finish_test ADDED test/tt3_index.c Index: test/tt3_index.c ================================================================== --- /dev/null +++ test/tt3_index.c @@ -0,0 +1,74 @@ +/* +** 2014 December 9 +** +** 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. +** +************************************************************************* +** +** create_drop_index_1 +*/ + + +static char *create_drop_index_thread(int iTid, int iArg){ + Error err = {0}; /* Error code and message */ + Sqlite db = {0}; /* SQLite database connection */ + + while( !timetostop(&err) ){ + opendb(&err, &db, "test.db", 0); + + sql_script(&err, &db, + + "DROP INDEX IF EXISTS i1;" + "DROP INDEX IF EXISTS i2;" + "DROP INDEX IF EXISTS i3;" + "DROP INDEX IF EXISTS i4;" + + "CREATE INDEX IF NOT EXISTS i1 ON t1(a);" + "CREATE INDEX IF NOT EXISTS i2 ON t1(b);" + "CREATE INDEX IF NOT EXISTS i3 ON t1(c);" + "CREATE INDEX IF NOT EXISTS i4 ON t1(d);" + + "SELECT * FROM t1 ORDER BY a;" + "SELECT * FROM t1 ORDER BY b;" + "SELECT * FROM t1 ORDER BY c;" + "SELECT * FROM t1 ORDER BY d;" + ); + + closedb(&err, &db); + } + + print_and_free_err(&err); + return sqlite3_mprintf("ok"); +} + +static void create_drop_index_1(int nMs){ + Error err = {0}; + Sqlite db = {0}; + Threadset threads = {0}; + + opendb(&err, &db, "test.db", 1); + sql_script(&err, &db, + "CREATE TABLE t1(a, b, c, d);" + "WITH data(x) AS (SELECT 1 UNION ALL SELECT x+1 FROM data WHERE x<100) " + "INSERT INTO t1 SELECT x,x,x,x FROM data;" + ); + closedb(&err, &db); + + setstoptime(&err, nMs); + + sqlite3_enable_shared_cache(1); + launch_thread(&err, &threads, create_drop_index_thread, 0); + launch_thread(&err, &threads, create_drop_index_thread, 0); + launch_thread(&err, &threads, create_drop_index_thread, 0); + launch_thread(&err, &threads, create_drop_index_thread, 0); + launch_thread(&err, &threads, create_drop_index_thread, 0); + sqlite3_enable_shared_cache(0); + + join_all_threads(&err, &threads); + print_and_free_err(&err); +} Index: test/update.test ================================================================== --- test/update.test +++ test/update.test @@ -602,7 +602,21 @@ } } {1 {no such column: nosuchcol}} } ;# ifcapable {trigger} +# Ticket [https://www.sqlite.org/src/tktview/43107840f1c02] on 2014-10-29 +# An assertion fault on UPDATE +# +do_execsql_test update-15.1 { + CREATE TABLE t15(a INTEGER PRIMARY KEY, b); + INSERT INTO t15(a,b) VALUES(10,'abc'),(20,'def'),(30,'ghi'); + ALTER TABLE t15 ADD COLUMN c; + CREATE INDEX t15c ON t15(c); + INSERT INTO t15(a,b) + VALUES(5,'zyx'),(15,'wvu'),(25,'tsr'),(35,'qpo'); + UPDATE t15 SET c=printf("y%d",a) WHERE c IS NULL; + SELECT a,b,c,'|' FROM t15 ORDER BY a; +} {5 zyx y5 | 10 abc y10 | 15 wvu y15 | 20 def y20 | 25 tsr y25 | 30 ghi y30 | 35 qpo y35 |} + finish_test