Index: VERSION
==================================================================
--- VERSION
+++ VERSION
@@ -1,1 +1,1 @@
-3.41.0
+3.41.3
Index: autoconf/tea/configure.ac
==================================================================
--- autoconf/tea/configure.ac
+++ autoconf/tea/configure.ac
@@ -17,11 +17,11 @@
# so you can encode the package version directly into the source files.
# This will also define a special symbol for Windows (BUILD_<PACKAGE_NAME>
# so that we create the export library with the dll.
#-----------------------------------------------------------------------
-AC_INIT([sqlite],[3.41.0])
+AC_INIT([sqlite],[3.41.3])
#--------------------------------------------------------------------
# Call TEA_INIT as the first TEA_ macro to set up initial vars.
# This will define a ${TEA_PLATFORM} variable == "unix" or "windows"
# as well as PKG_LIB_FILE and PKG_STUB_LIB_FILE.
Index: configure
==================================================================
--- configure
+++ configure
@@ -1,8 +1,8 @@
#! /bin/sh
# Guess values for system-dependent variables and create Makefiles.
-# Generated by GNU Autoconf 2.69 for sqlite 3.41.0.
+# Generated by GNU Autoconf 2.69 for sqlite 3.41.3.
#
#
# Copyright (C) 1992-1996, 1998-2012 Free Software Foundation, Inc.
#
#
@@ -724,12 +724,12 @@
MAKEFLAGS=
# Identity of this package.
PACKAGE_NAME='sqlite'
PACKAGE_TARNAME='sqlite'
-PACKAGE_VERSION='3.41.0'
-PACKAGE_STRING='sqlite 3.41.0'
+PACKAGE_VERSION='3.41.3'
+PACKAGE_STRING='sqlite 3.41.3'
PACKAGE_BUGREPORT=''
PACKAGE_URL=''
# Factoring default headers for most tests.
ac_includes_default="\
@@ -1468,11 +1468,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.41.0 to adapt to many kinds of systems.
+\`configure' configures sqlite 3.41.3 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.
@@ -1533,11 +1533,11 @@
_ACEOF
fi
if test -n "$ac_init_help"; then
case $ac_init_help in
- short | recursive ) echo "Configuration of sqlite 3.41.0:";;
+ short | recursive ) echo "Configuration of sqlite 3.41.3:";;
esac
cat <<\_ACEOF
Optional Features:
--disable-option-checking ignore unrecognized --enable/--with options
@@ -1663,11 +1663,11 @@
fi
test -n "$ac_init_help" && exit $ac_status
if $ac_init_version; then
cat <<\_ACEOF
-sqlite configure 3.41.0
+sqlite configure 3.41.3
generated by GNU Autoconf 2.69
Copyright (C) 2012 Free Software Foundation, Inc.
This configure script is free software; the Free Software Foundation
gives unlimited permission to copy, distribute and modify it.
@@ -2082,11 +2082,11 @@
} # ac_fn_c_check_header_mongrel
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.41.0, which was
+It was created by sqlite $as_me 3.41.3, which was
generated by GNU Autoconf 2.69. Invocation command line was
$ $0 $@
_ACEOF
@@ -12455,11 +12455,11 @@
cat >>$CONFIG_STATUS <<\_ACEOF || ac_write_fail=1
# 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.41.0, which was
+This file was extended by sqlite $as_me 3.41.3, which was
generated by GNU Autoconf 2.69. Invocation command line was
CONFIG_FILES = $CONFIG_FILES
CONFIG_HEADERS = $CONFIG_HEADERS
CONFIG_LINKS = $CONFIG_LINKS
@@ -12521,11 +12521,11 @@
_ACEOF
cat >>$CONFIG_STATUS <<_ACEOF || ac_write_fail=1
ac_cs_config="`$as_echo "$ac_configure_args" | sed 's/^ //; s/[\\""\`\$]/\\\\&/g'`"
ac_cs_version="\\
-sqlite config.status 3.41.0
+sqlite config.status 3.41.3
configured by $0, generated by GNU Autoconf 2.69,
with options \\"\$ac_cs_config\\"
Copyright (C) 2012 Free Software Foundation, Inc.
This config.status script is free software; the Free Software Foundation
Index: ext/fts3/fts3_write.c
==================================================================
--- ext/fts3/fts3_write.c
+++ ext/fts3/fts3_write.c
@@ -2665,20 +2665,22 @@
static int fts3MsrBufferData(
Fts3MultiSegReader *pMsr, /* Multi-segment-reader handle */
char *pList,
i64 nList
){
- if( nList>pMsr->nBuffer ){
+ if( (nList+FTS3_NODE_PADDING)>pMsr->nBuffer ){
char *pNew;
- pMsr->nBuffer = nList*2;
- pNew = (char *)sqlite3_realloc64(pMsr->aBuffer, pMsr->nBuffer);
+ int nNew = nList*2 + FTS3_NODE_PADDING;
+ pNew = (char *)sqlite3_realloc64(pMsr->aBuffer, nNew);
if( !pNew ) return SQLITE_NOMEM;
pMsr->aBuffer = pNew;
+ pMsr->nBuffer = nNew;
}
assert( nList>0 );
memcpy(pMsr->aBuffer, pList, nList);
+ memset(&pMsr->aBuffer[nList], 0, FTS3_NODE_PADDING);
return SQLITE_OK;
}
int sqlite3Fts3MsrIncrNext(
Fts3Table *p, /* Virtual table handle */
Index: ext/fts5/fts5_expr.c
==================================================================
--- ext/fts5/fts5_expr.c
+++ ext/fts5/fts5_expr.c
@@ -405,11 +405,11 @@
int sqlite3Fts5ExprAnd(Fts5Expr **pp1, Fts5Expr *p2){
Fts5Parse sParse;
memset(&sParse, 0, sizeof(sParse));
- if( *pp1 ){
+ if( *pp1 && p2 ){
Fts5Expr *p1 = *pp1;
int nPhrase = p1->nPhrase + p2->nPhrase;
p1->pRoot = sqlite3Fts5ParseNode(&sParse, FTS5_AND, p1->pRoot, p2->pRoot,0);
p2->pRoot = 0;
@@ -430,11 +430,11 @@
p1->apExprPhrase = ap;
}
}
sqlite3_free(p2->apExprPhrase);
sqlite3_free(p2);
- }else{
+ }else if( p2 ){
*pp1 = p2;
}
return sParse.rc;
}
Index: ext/fts5/test/fts5ak.test
==================================================================
--- ext/fts5/test/fts5ak.test
+++ ext/fts5/test/fts5ak.test
@@ -151,7 +151,33 @@
{a b c c d e}
{a b c d e}
}
}
+
+# 2023-04-06 https://sqlite.org/forum/forumpost/cae4367d9b
+#
+# This is not a test of FTS5, but rather a test of the of what happens to
+# prepared statements that encounter SQLITE_SCHEMA while other prepared
+# statements are running. The original problem POC used FTS5, and so
+# is seems reasonable to put the test here.
+#
+# The vdbeaux24.test module in TH3 also tests this same behavior but
+# without requiring FTS5 or an other extension.
+#
+reset_db
+db null NULL
+do_execsql_test 4.0 {
+ CREATE TABLE t5(a PRIMARY KEY);
+ INSERT INTO t5 VALUES(0);
+ CREATE VIRTUAL TABLE t6 USING fts5(0);
+ DELETE FROM t6;
+ CREATE TABLE t7(x);
+ WITH cte(a) AS (
+ SELECT a FROM t5
+ WHERE ((0,0) IN (SELECT 0, LAG(0) OVER (PARTITION BY 0) FROM t6), 0)
+ < (a,0)
+ )
+ SELECT max(a) FROM cte;
+} NULL
finish_test
Index: ext/fts5/test/fts5misc.test
==================================================================
--- ext/fts5/test/fts5misc.test
+++ ext/fts5/test/fts5misc.test
@@ -440,8 +440,36 @@
}
do_execsql_test -db db2 16.6 {
SELECT * FROM x1
} {abc def}
+
+#-------------------------------------------------------------------------
+reset_db
+do_execsql_test 17.1 {
+ CREATE VIRTUAL TABLE ft USING fts5(x, tokenize="unicode61 separators 'X'");
+}
+do_execsql_test 17.2 {
+ SELECT 0 FROM ft WHERE ft MATCH 'X' AND ft MATCH 'X'
+}
+do_execsql_test 17.3 {
+ SELECT 0 FROM ft('X')
+}
+
+do_execsql_test 17.4 {
+ CREATE VIRTUAL TABLE t0 USING fts5(c0, t="trigram");
+ INSERT INTO t0 VALUES('assertionfaultproblem');
+}
+do_execsql_test 17.5 {
+ SELECT 0 FROM t0(0) WHERE c0 GLOB 0;
+} {}
+
+do_execsql_test 17.5 {
+ SELECT c0 FROM t0 WHERE c0 GLOB '*f*';
+} {assertionfaultproblem}
+do_execsql_test 17.5 {
+ SELECT c0 FROM t0 WHERE c0 GLOB '*faul*';
+} {assertionfaultproblem}
+
finish_test
Index: ext/misc/base64.c
==================================================================
--- ext/misc/base64.c
+++ ext/misc/base64.c
@@ -145,22 +145,22 @@
*pOut = 0;
return pOut;
}
/* Skip over text which is not base64 numeral(s). */
-static char * skipNonB64( char *s ){
+static char * skipNonB64( char *s, int nc ){
char c;
- while( (c = *s) && !IS_BX_DIGIT(BX_DV_PROTO(c)) ) ++s;
+ while( nc-- > 0 && (c = *s) && !IS_BX_DIGIT(BX_DV_PROTO(c)) ) ++s;
return s;
}
/* Decode base64 text into a byte buffer. */
static u8* fromBase64( char *pIn, int ncIn, u8 *pOut ){
if( ncIn>0 && pIn[ncIn-1]=='\n' ) --ncIn;
while( ncIn>0 && *pIn!=PAD_CHAR ){
static signed char nboi[] = { 0, 0, 1, 2, 3 };
- char *pUse = skipNonB64(pIn);
+ char *pUse = skipNonB64(pIn, ncIn);
unsigned long qv = 0L;
int nti, nbo, nac;
ncIn -= (pUse - pIn);
pIn = pUse;
nti = (ncIn>4)? 4 : ncIn;
@@ -215,14 +215,21 @@
nc = 4*(nv+2/3); /* quads needed */
nc += (nc+(B64_DARK_MAX-1))/B64_DARK_MAX + 1; /* LFs and a 0-terminator */
if( nvMax < nc ){
sqlite3_result_error(context, "blob expanded to base64 too big", -1);
return;
+ }
+ bBuf = (u8*)sqlite3_value_blob(av[0]);
+ if( !bBuf ){
+ if( SQLITE_NOMEM==sqlite3_errcode(sqlite3_context_db_handle(context)) ){
+ goto memFail;
+ }
+ sqlite3_result_text(context,"",-1,SQLITE_STATIC);
+ break;
}
cBuf = sqlite3_malloc(nc);
if( !cBuf ) goto memFail;
- bBuf = (u8*)sqlite3_value_blob(av[0]);
nc = (int)(toBase64(bBuf, nb, cBuf) - cBuf);
sqlite3_result_text(context, cBuf, nc, sqlite3_free);
break;
case SQLITE_TEXT:
nc = nv;
@@ -230,14 +237,21 @@
if( nvMax < nb ){
sqlite3_result_error(context, "blob from base64 may be too big", -1);
return;
}else if( nb<1 ){
nb = 1;
+ }
+ cBuf = (char *)sqlite3_value_text(av[0]);
+ if( !cBuf ){
+ if( SQLITE_NOMEM==sqlite3_errcode(sqlite3_context_db_handle(context)) ){
+ goto memFail;
+ }
+ sqlite3_result_zeroblob(context, 0);
+ break;
}
bBuf = sqlite3_malloc(nb);
if( !bBuf ) goto memFail;
- cBuf = (char *)sqlite3_value_text(av[0]);
nb = (int)(fromBase64(cBuf, nc, bBuf) - bBuf);
sqlite3_result_blob(context, bBuf, nb, sqlite3_free);
break;
default:
sqlite3_result_error(context, "base64 accepts only blob or text", -1);
Index: ext/misc/base85.c
==================================================================
--- ext/misc/base85.c
+++ ext/misc/base85.c
@@ -138,13 +138,13 @@
/* Width of base64 lines. Should be an integer multiple of 5. */
#define B85_DARK_MAX 80
-static char * skipNonB85( char *s ){
+static char * skipNonB85( char *s, int nc ){
char c;
- while( (c = *s) && !IS_B85(c) ) ++s;
+ while( nc-- > 0 && (c = *s) && !IS_B85(c) ) ++s;
return s;
}
/* Convert small integer, known to be in 0..84 inclusive, to base85 numeral.
* Do not use the macro form with argument expression having a side-effect.*/
@@ -210,11 +210,11 @@
/* Decode base85 text into a byte buffer. */
static u8* fromBase85( char *pIn, int ncIn, u8 *pOut ){
if( ncIn>0 && pIn[ncIn-1]=='\n' ) --ncIn;
while( ncIn>0 ){
static signed char nboi[] = { 0, 0, 1, 2, 3, 4 };
- char *pUse = skipNonB85(pIn);
+ char *pUse = skipNonB85(pIn, ncIn);
unsigned long qv = 0L;
int nti, nbo;
ncIn -= (pUse - pIn);
pIn = pUse;
nti = (ncIn>5)? 5 : ncIn;
@@ -294,14 +294,21 @@
/* ulongs tail newlines tailenc+nul*/
nc = 5*(nv/4) + nv%4 + nv/64+1 + 2;
if( nvMax < nc ){
sqlite3_result_error(context, "blob expanded to base85 too big", -1);
return;
+ }
+ bBuf = (u8*)sqlite3_value_blob(av[0]);
+ if( !bBuf ){
+ if( SQLITE_NOMEM==sqlite3_errcode(sqlite3_context_db_handle(context)) ){
+ goto memFail;
+ }
+ sqlite3_result_text(context,"",-1,SQLITE_STATIC);
+ break;
}
cBuf = sqlite3_malloc(nc);
if( !cBuf ) goto memFail;
- bBuf = (u8*)sqlite3_value_blob(av[0]);
nc = (int)(toBase85(bBuf, nb, cBuf, "\n") - cBuf);
sqlite3_result_text(context, cBuf, nc, sqlite3_free);
break;
case SQLITE_TEXT:
nc = nv;
@@ -309,14 +316,21 @@
if( nvMax < nb ){
sqlite3_result_error(context, "blob from base85 may be too big", -1);
return;
}else if( nb<1 ){
nb = 1;
+ }
+ cBuf = (char *)sqlite3_value_text(av[0]);
+ if( !cBuf ){
+ if( SQLITE_NOMEM==sqlite3_errcode(sqlite3_context_db_handle(context)) ){
+ goto memFail;
+ }
+ sqlite3_result_zeroblob(context, 0);
+ break;
}
bBuf = sqlite3_malloc(nb);
if( !bBuf ) goto memFail;
- cBuf = (char *)sqlite3_value_text(av[0]);
nb = (int)(fromBase85(cBuf, nc, bBuf) - bBuf);
sqlite3_result_blob(context, bBuf, nb, sqlite3_free);
break;
default:
sqlite3_result_error(context, "base85 accepts only blob or text.", -1);
Index: ext/misc/zipfile.c
==================================================================
--- ext/misc/zipfile.c
+++ ext/misc/zipfile.c
@@ -1095,11 +1095,14 @@
}else{
/* Figure out if this is a directory or a zero-sized file. Consider
** it to be a directory either if the mode suggests so, or if
** the final character in the name is '/'. */
u32 mode = pCDS->iExternalAttr >> 16;
- if( !(mode & S_IFDIR) && pCDS->zFile[pCDS->nFile-1]!='/' ){
+ if( !(mode & S_IFDIR)
+ && pCDS->nFile>=1
+ && pCDS->zFile[pCDS->nFile-1]!='/'
+ ){
sqlite3_result_blob(ctx, "", 0, SQLITE_STATIC);
}
}
}
break;
Index: ext/recover/dbdata.c
==================================================================
--- ext/recover/dbdata.c
+++ ext/recover/dbdata.c
@@ -510,14 +510,18 @@
if( pCsr->aPage==0 ){
while( 1 ){
if( pCsr->bOnePage==0 && pCsr->iPgno>pCsr->szDb ) return SQLITE_OK;
rc = dbdataLoadPage(pCsr, pCsr->iPgno, &pCsr->aPage, &pCsr->nPage);
if( rc!=SQLITE_OK ) return rc;
- if( pCsr->aPage ) break;
+ if( pCsr->aPage && pCsr->nPage>=256 ) break;
+ sqlite3_free(pCsr->aPage);
+ pCsr->aPage = 0;
if( pCsr->bOnePage ) return SQLITE_OK;
pCsr->iPgno++;
}
+
+ assert( iOff+3+2<=pCsr->nPage );
pCsr->iCell = pTab->bPtr ? -2 : 0;
pCsr->nCell = get_uint16(&pCsr->aPage[iOff+3]);
}
if( pTab->bPtr ){
@@ -748,12 +752,11 @@
static int dbdataGetEncoding(DbdataCursor *pCsr){
int rc = SQLITE_OK;
int nPg1 = 0;
u8 *aPg1 = 0;
rc = dbdataLoadPage(pCsr, 1, &aPg1, &nPg1);
- assert( rc!=SQLITE_OK || nPg1==0 || nPg1>=512 );
- if( rc==SQLITE_OK && nPg1>0 ){
+ if( rc==SQLITE_OK && nPg1>=(56+4) ){
pCsr->enc = get_uint32(&aPg1[56]);
}
sqlite3_free(aPg1);
return rc;
}
Index: src/alter.c
==================================================================
--- src/alter.c
+++ src/alter.c
@@ -1276,10 +1276,23 @@
}
sqlite3_free(zQuot);
return rc;
}
+
+/*
+** Set all pEList->a[].fg.eEName fields in the expression-list to val.
+*/
+static void renameSetENames(ExprList *pEList, int val){
+ if( pEList ){
+ int i;
+ for(i=0; i<pEList->nExpr; i++){
+ assert( val==ENAME_NAME || pEList->a[i].fg.eEName==ENAME_NAME );
+ pEList->a[i].fg.eEName = val;
+ }
+ }
+}
/*
** Resolve all symbols in the trigger at pParse->pNewTrigger, assuming
** it was read from the schema of database zDb. Return SQLITE_OK if
** successful. Otherwise, return an SQLite error code and leave an error
@@ -1324,11 +1337,21 @@
if( pSel==0 ){
pStep->pExprList = 0;
pSrc = 0;
rc = SQLITE_NOMEM;
}else{
+ /* pStep->pExprList contains an expression-list used for an UPDATE
+ ** statement. So the a[].zEName values are the RHS of the
+ ** "<col> = <expr>" clauses of the UPDATE statement. So, before
+ ** running SelectPrep(), change all the eEName values in
+ ** pStep->pExprList to ENAME_SPAN (from their current value of
+ ** ENAME_NAME). This is to prevent any ids in ON() clauses that are
+ ** part of pSrc from being incorrectly resolved against the
+ ** a[].zEName values as if they were column aliases. */
+ renameSetENames(pStep->pExprList, ENAME_SPAN);
sqlite3SelectPrep(pParse, pSel, 0);
+ renameSetENames(pStep->pExprList, ENAME_NAME);
rc = pParse->nErr ? SQLITE_ERROR : SQLITE_OK;
assert( pStep->pExprList==0 || pStep->pExprList==pSel->pEList );
assert( pSrc==pSel->pSrc );
if( pStep->pExprList ) pSel->pEList = 0;
pSel->pSrc = 0;
Index: src/analyze.c
==================================================================
--- src/analyze.c
+++ src/analyze.c
@@ -992,15 +992,19 @@
int regTemp2 = iMem++; /* Second temporary use register */
int regTabname = iMem++; /* Register containing table name */
int regIdxname = iMem++; /* Register containing index name */
int regStat1 = iMem++; /* Value for the stat column of sqlite_stat1 */
int regPrev = iMem; /* MUST BE LAST (see below) */
+#ifdef SQLITE_ENABLE_STAT4
+ int doOnce = 1; /* Flag for a one-time computation */
+#endif
#ifdef SQLITE_ENABLE_PREUPDATE_HOOK
- Table *pStat1 = 0;
+ Table *pStat1 = 0;
#endif
- pParse->nMem = MAX(pParse->nMem, iMem);
+ sqlite3TouchRegister(pParse, iMem);
+ assert( sqlite3NoTempsInRange(pParse, regNewRowid, iMem) );
v = sqlite3GetVdbe(pParse);
if( v==0 || NEVER(pTab==0) ){
return;
}
if( !IsOrdinaryTable(pTab) ){
@@ -1102,11 +1106,11 @@
/* Make sure there are enough memory cells allocated to accommodate
** the regPrev array and a trailing rowid (the rowid slot is required
** when building a record to insert into the sample column of
** the sqlite_stat4 table. */
- pParse->nMem = MAX(pParse->nMem, regPrev+nColTest);
+ sqlite3TouchRegister(pParse, regPrev+nColTest);
/* Open a read-only cursor on the index being analyzed. */
assert( iDb==sqlite3SchemaToIndex(db, pIdx->pSchema) );
sqlite3VdbeAddOp3(v, OP_OpenRead, iIdxCur, pIdx->tnum, iDb);
sqlite3VdbeSetP4KeyInfo(pParse, pIdx);
@@ -1274,11 +1278,39 @@
int regSampleRowid = regCol + nCol;
int addrNext;
int addrIsNull;
u8 seekOp = HasRowid(pTab) ? OP_NotExists : OP_NotFound;
- pParse->nMem = MAX(pParse->nMem, regCol+nCol);
+ if( doOnce ){
+ int mxCol = nCol;
+ Index *pX;
+
+ /* Compute the maximum number of columns in any index */
+ for(pX=pTab->pIndex; pX; pX=pX->pNext){
+ int nColX; /* Number of columns in pX */
+ if( !HasRowid(pTab) && IsPrimaryKeyIndex(pX) ){
+ nColX = pX->nKeyCol;
+ }else{
+ nColX = pX->nColumn;
+ }
+ if( nColX>mxCol ) mxCol = nColX;
+ }
+
+ /* Allocate space to compute results for the largest index */
+ sqlite3TouchRegister(pParse, regCol+mxCol);
+ doOnce = 0;
+#ifdef SQLITE_DEBUG
+ /* Verify that the call to sqlite3ClearTempRegCache() below
+ ** really is needed.
+ ** https://sqlite.org/forum/forumpost/83cb4a95a0 (2023-03-25)
+ */
+ testcase( !sqlite3NoTempsInRange(pParse, regEq, regCol+mxCol) );
+#endif
+ sqlite3ClearTempRegCache(pParse); /* tag-20230325-1 */
+ assert( sqlite3NoTempsInRange(pParse, regEq, regCol+mxCol) );
+ }
+ assert( sqlite3NoTempsInRange(pParse, regEq, regCol+nCol) );
addrNext = sqlite3VdbeCurrentAddr(v);
callStatGet(pParse, regStat, STAT_GET_ROWID, regSampleRowid);
addrIsNull = sqlite3VdbeAddOp1(v, OP_IsNull, regSampleRowid);
VdbeCoverage(v);
@@ -1355,10 +1387,11 @@
iTab = pParse->nTab;
assert( sqlite3SchemaMutexHeld(db, iDb, 0) );
for(k=sqliteHashFirst(&pSchema->tblHash); k; k=sqliteHashNext(k)){
Table *pTab = (Table*)sqliteHashData(k);
analyzeOneTable(pParse, pTab, 0, iStatCur, iMem, iTab);
+ iMem = sqlite3FirstAvailableRegister(pParse, iMem);
}
loadAnalysis(pParse, iDb);
}
/*
@@ -1835,11 +1868,11 @@
assert( db->lookaside.bDisable );
if( (pStat4 = sqlite3FindTable(db, "sqlite_stat4", zDb))!=0
&& IsOrdinaryTable(pStat4)
){
rc = loadStatTbl(db,
- "SELECT idx,count(*) FROM %Q.sqlite_stat4 GROUP BY idx",
+ "SELECT idx,count(*) FROM %Q.sqlite_stat4 GROUP BY idx COLLATE nocase",
"SELECT idx,neq,nlt,ndlt,sample FROM %Q.sqlite_stat4",
zDb
);
}
return rc;
Index: src/backup.c
==================================================================
--- src/backup.c
+++ src/backup.c
@@ -240,17 +240,11 @@
assert( sqlite3BtreeGetReserveNoMutex(p->pSrc)>=0 );
assert( p->bDestLocked );
assert( !isFatalError(p->rc) );
assert( iSrcPg!=PENDING_BYTE_PAGE(p->pSrc->pBt) );
assert( zSrcData );
-
- /* Catch the case where the destination is an in-memory database and the
- ** page sizes of the source and destination differ.
- */
- if( nSrcPgsz!=nDestPgsz && sqlite3PagerIsMemdb(pDestPager) ){
- rc = SQLITE_READONLY;
- }
+ assert( nSrcPgsz==nDestPgsz || sqlite3PagerIsMemdb(pDestPager)==0 );
/* This loop runs once for each destination page spanned by the source
** page. For each iteration, variable iOff is set to the byte offset
** of the destination page.
*/
@@ -379,11 +373,14 @@
/* Do not allow backup if the destination database is in WAL mode
** and the page sizes are different between source and destination */
pgszSrc = sqlite3BtreeGetPageSize(p->pSrc);
pgszDest = sqlite3BtreeGetPageSize(p->pDest);
destMode = sqlite3PagerGetJournalMode(sqlite3BtreePager(p->pDest));
- if( SQLITE_OK==rc && destMode==PAGER_JOURNALMODE_WAL && pgszSrc!=pgszDest ){
+ if( SQLITE_OK==rc
+ && (destMode==PAGER_JOURNALMODE_WAL || sqlite3PagerIsMemdb(pDestPager))
+ && pgszSrc!=pgszDest
+ ){
rc = SQLITE_READONLY;
}
/* Now that there is a read-lock on the source database, query the
** source pager for the number of pages in the database.
Index: src/btree.c
==================================================================
--- src/btree.c
+++ src/btree.c
@@ -1722,17 +1722,18 @@
** then the cell content offset of an empty page wants to be 65536.
** However, that integer is too large to be stored in a 2-byte unsigned
** integer, so a value of 0 is used in its place. */
pTmp = &data[hdr+5];
top = get2byte(pTmp);
- assert( top<=(int)pPage->pBt->usableSize ); /* by btreeComputeFreeSpace() */
if( gap>top ){
if( top==0 && pPage->pBt->usableSize==65536 ){
top = 65536;
}else{
return SQLITE_CORRUPT_PAGE(pPage);
}
+ }else if( top>(int)pPage->pBt->usableSize ){
+ return SQLITE_CORRUPT_PAGE(pPage);
}
/* If there is enough space between gap and top for one more cell pointer,
** and if the freelist is not empty, then search the
** freelist looking for a slot big enough to satisfy the request.
@@ -6082,11 +6083,11 @@
}
}
pPage = pCur->pPage;
idx = ++pCur->ix;
- if( NEVER(!pPage->isInit) || sqlite3FaultSim(412) ){
+ if( !pPage->isInit || sqlite3FaultSim(412) ){
return SQLITE_CORRUPT_BKPT;
}
if( idx>=pPage->nCell ){
if( !pPage->leaf ){
@@ -7300,11 +7301,11 @@
int k; /* Current slot in pCArray->apEnd[] */
u8 *pSrcEnd; /* Current pCArray->apEnd[k] value */
assert( i<iEnd );
j = get2byte(&aData[hdr+5]);
- if( j>(u32)usableSize ){ j = 0; }
+ if( NEVER(j>(u32)usableSize) ){ j = 0; }
memcpy(&pTmp[j], &aData[j], usableSize - j);
for(k=0; pCArray->ixNx[k]<=i && ALWAYS(k<NB*2); k++){}
pSrcEnd = pCArray->apEnd[k];
@@ -7534,11 +7535,11 @@
nCell -= nTail;
}
pData = &aData[get2byteNotZero(&aData[hdr+5])];
if( pData<pBegin ) goto editpage_fail;
- if( pData>pPg->aDataEnd ) goto editpage_fail;
+ if( NEVER(pData>pPg->aDataEnd) ) goto editpage_fail;
/* Add cells to the start of the page */
if( iNew<iOld ){
int nAdd = MIN(nNew,iOld-iNew);
assert( (iOld-iNew)<nNew || nCell==0 || CORRUPT_DB );
@@ -9169,11 +9170,11 @@
return btreeOverwriteCell(pCur, &x2);
}
}
}
assert( pCur->eState==CURSOR_VALID
- || (pCur->eState==CURSOR_INVALID && loc) );
+ || (pCur->eState==CURSOR_INVALID && loc) || CORRUPT_DB );
pPage = pCur->pPage;
assert( pPage->intKey || pX->nKey>=0 || (flags & BTREE_PREFORMAT) );
assert( pPage->leaf || !pPage->intKey );
if( pPage->nFree<0 ){
@@ -9211,10 +9212,11 @@
if( rc ) goto end_insert;
}
assert( szNew==pPage->xCellSize(pPage, newCell) );
assert( szNew <= MX_CELL_SIZE(p->pBt) );
idx = pCur->ix;
+ pCur->info.nSize = 0;
if( loc==0 ){
CellInfo info;
assert( idx>=0 );
if( idx>=pPage->nCell ){
return SQLITE_CORRUPT_BKPT;
@@ -9283,11 +9285,10 @@
** the b-tree if possible. If the cursor is left pointing to the last
** entry in the table, and the next row inserted has an integer key
** larger than the largest existing key, it is possible to insert the
** row without seeking the cursor. This can be a big performance boost.
*/
- pCur->info.nSize = 0;
if( pPage->nOverflow ){
assert( rc==SQLITE_OK );
pCur->curFlags &= ~(BTCF_ValidNKey);
rc = balance(pCur);
@@ -9483,10 +9484,13 @@
return SQLITE_CORRUPT_BKPT;
}
pCell = findCell(pPage, iCellIdx);
if( pPage->nFree<0 && btreeComputeFreeSpace(pPage) ){
return SQLITE_CORRUPT_BKPT;
+ }
+ if( pCell<&pPage->aCellIdx[pPage->nCell] ){
+ return SQLITE_CORRUPT_BKPT;
}
/* If the BTREE_SAVEPOSITION bit is on, 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
Index: src/build.c
==================================================================
--- src/build.c
+++ src/build.c
@@ -1449,11 +1449,11 @@
Hash *pHash;
sqlite3 *db = pParse->db;
if( pParse->pNewTrigger ){
sqlite3ErrorMsg(pParse, "cannot use RETURNING in a trigger");
}else{
- assert( pParse->bReturning==0 );
+ assert( pParse->bReturning==0 || pParse->ifNotExists );
}
pParse->bReturning = 1;
pRet = sqlite3DbMallocZero(db, sizeof(*pRet));
if( pRet==0 ){
sqlite3ExprListDelete(db, pList);
@@ -1475,11 +1475,12 @@
pRet->retTrig.step_list = &pRet->retTStep;
pRet->retTStep.op = TK_RETURNING;
pRet->retTStep.pTrig = &pRet->retTrig;
pRet->retTStep.pExprList = pList;
pHash = &(db->aDb[1].pSchema->trigHash);
- assert( sqlite3HashFind(pHash, RETURNING_TRIGGER_NAME)==0 || pParse->nErr );
+ assert( sqlite3HashFind(pHash, RETURNING_TRIGGER_NAME)==0
+ || pParse->nErr || pParse->ifNotExists );
if( sqlite3HashInsert(pHash, RETURNING_TRIGGER_NAME, &pRet->retTrig)
==&pRet->retTrig ){
sqlite3OomFault(db);
}
}
@@ -2010,10 +2011,11 @@
** just a reference to another column, in order for covering index
** optimizations to work correctly. So if the value is not an expression,
** turn it into one by adding a unary "+" operator. */
pExpr = sqlite3PExpr(pParse, TK_UPLUS, pExpr, 0);
}
+ if( pExpr && pExpr->op!=TK_RAISE ) pExpr->affExpr = pCol->affinity;
sqlite3ColumnSetExpr(pParse, pTab, pCol, pExpr);
pExpr = 0;
goto generated_done;
generated_error:
Index: src/delete.c
==================================================================
--- src/delete.c
+++ src/delete.c
@@ -112,17 +112,19 @@
**
** If pTab is not writable -> generate an error message and return 1.
** If pTab is writable but other errors have occurred -> return 1.
** If pTab is writable and no prior errors -> return 0;
*/
-int sqlite3IsReadOnly(Parse *pParse, Table *pTab, int viewOk){
+int sqlite3IsReadOnly(Parse *pParse, Table *pTab, Trigger *pTrigger){
if( tabIsReadOnly(pParse, pTab) ){
sqlite3ErrorMsg(pParse, "table %s may not be modified", pTab->zName);
return 1;
}
#ifndef SQLITE_OMIT_VIEW
- if( !viewOk && IsView(pTab) ){
+ if( IsView(pTab)
+ && (pTrigger==0 || (pTrigger->bReturning && pTrigger->pNext==0))
+ ){
sqlite3ErrorMsg(pParse,"cannot modify %s because it is a view",pTab->zName);
return 1;
}
#endif
return 0;
@@ -372,11 +374,11 @@
*/
if( sqlite3ViewGetColumnNames(pParse, pTab) ){
goto delete_from_cleanup;
}
- if( sqlite3IsReadOnly(pParse, pTab, (pTrigger?1:0)) ){
+ if( sqlite3IsReadOnly(pParse, pTab, pTrigger) ){
goto delete_from_cleanup;
}
iDb = sqlite3SchemaToIndex(db, pTab->pSchema);
assert( iDb<db->nDb );
rcauth = sqlite3AuthCheck(pParse, SQLITE_DELETE, pTab->zName, 0,
@@ -481,11 +483,11 @@
}
}else
#endif /* SQLITE_OMIT_TRUNCATE_OPTIMIZATION */
{
u16 wcf = WHERE_ONEPASS_DESIRED|WHERE_DUPLICATES_OK;
- if( sNC.ncFlags & NC_VarSelect ) bComplex = 1;
+ if( sNC.ncFlags & NC_Subquery ) bComplex = 1;
wcf |= (bComplex ? 0 : WHERE_ONEPASS_MULTIROW);
if( HasRowid(pTab) ){
/* For a rowid table, initialize the RowSet to an empty set */
pPk = 0;
nPk = 1;
Index: src/expr.c
==================================================================
--- src/expr.c
+++ src/expr.c
@@ -276,15 +276,14 @@
if( p->pLeft && (p->pLeft->flags & EP_Collate)!=0 ){
p = p->pLeft;
}else{
Expr *pNext = p->pRight;
/* The Expr.x union is never used at the same time as Expr.pRight */
- assert( ExprUseXList(p) );
- assert( p->x.pList==0 || p->pRight==0 );
- if( p->x.pList!=0 && !db->mallocFailed ){
+ assert( !ExprUseXList(p) || p->x.pList==0 || p->pRight==0 );
+ if( ExprUseXList(p) && p->x.pList!=0 && !db->mallocFailed ){
int i;
- for(i=0; ALWAYS(i<p->x.pList->nExpr); i++){
+ for(i=0; i<p->x.pList->nExpr; i++){
if( ExprHasProperty(p->x.pList->a[i].pExpr, EP_Collate) ){
pNext = p->x.pList->a[i].pExpr;
break;
}
}
@@ -2934,11 +2933,10 @@
Expr *pLhs = sqlite3VectorFieldSubexpr(pX->pLeft, i);
Expr *pRhs = pEList->a[i].pExpr;
CollSeq *pReq = sqlite3BinaryCompareCollSeq(pParse, pLhs, pRhs);
int j;
- assert( pReq!=0 || pRhs->iColumn==XN_ROWID || pParse->nErr );
for(j=0; j<nExpr; j++){
if( pIdx->aiColumn[j]!=pRhs->iColumn ) continue;
assert( pIdx->azColl[j] );
if( pReq!=0 && sqlite3StrICmp(pReq->zName, pIdx->azColl[j])!=0 ){
continue;
@@ -3837,22 +3835,24 @@
Column *pCol, /* The generated column */
int regOut /* Put the result in this register */
){
int iAddr;
Vdbe *v = pParse->pVdbe;
+ int nErr = pParse->nErr;
assert( v!=0 );
assert( pParse->iSelfTab!=0 );
- if( pParse->iSelfTab>0 ){
+ if( pParse->iSelfTab>0 ){
iAddr = sqlite3VdbeAddOp3(v, OP_IfNullRow, pParse->iSelfTab-1, 0, regOut);
}else{
iAddr = 0;
}
sqlite3ExprCodeCopy(pParse, sqlite3ColumnExpr(pTab,pCol), regOut);
if( pCol->affinity>=SQLITE_AFF_TEXT ){
sqlite3VdbeAddOp4(v, OP_Affinity, regOut, 1, 0, &pCol->affinity, 1);
}
if( iAddr ) sqlite3VdbeJumpHere(v, iAddr);
+ if( pParse->nErr>nErr ) pParse->db->errByteOffset = -1;
}
#endif /* SQLITE_OMIT_GENERATED_COLUMNS */
/*
** Generate code to extract the value of the iCol-th column of a table.
@@ -3865,10 +3865,11 @@
int regOut /* Extract the value into this register */
){
Column *pCol;
assert( v!=0 );
assert( pTab!=0 );
+ assert( iCol!=XN_EXPR );
if( iCol<0 || iCol==pTab->iPKey ){
sqlite3VdbeAddOp2(v, OP_Rowid, iTabCur, regOut);
VdbeComment((v, "%s.rowid", pTab->zName));
}else{
int op;
@@ -4131,17 +4132,28 @@
int target /* Where to store the result of the expression */
){
IndexedExpr *p;
Vdbe *v;
for(p=pParse->pIdxEpr; p; p=p->pIENext){
+ u8 exprAff;
int iDataCur = p->iDataCur;
if( iDataCur<0 ) continue;
if( pParse->iSelfTab ){
if( p->iDataCur!=pParse->iSelfTab-1 ) continue;
iDataCur = -1;
}
if( sqlite3ExprCompare(0, pExpr, p->pExpr, iDataCur)!=0 ) continue;
+ assert( p->aff>=SQLITE_AFF_BLOB && p->aff<=SQLITE_AFF_NUMERIC );
+ exprAff = sqlite3ExprAffinity(pExpr);
+ if( (exprAff<=SQLITE_AFF_BLOB && p->aff!=SQLITE_AFF_BLOB)
+ || (exprAff==SQLITE_AFF_TEXT && p->aff!=SQLITE_AFF_TEXT)
+ || (exprAff>=SQLITE_AFF_NUMERIC && p->aff!=SQLITE_AFF_NUMERIC)
+ ){
+ /* Affinity mismatch on a generated column */
+ continue;
+ }
+
v = pParse->pVdbe;
assert( v!=0 );
if( p->bMaybeNullRow ){
/* If the index is on a NULL row due to an outer join, then we
** cannot extract the value from the index. The value must be
@@ -4206,11 +4218,23 @@
switch( op ){
case TK_AGG_COLUMN: {
AggInfo *pAggInfo = pExpr->pAggInfo;
struct AggInfo_col *pCol;
assert( pAggInfo!=0 );
- assert( pExpr->iAgg>=0 && pExpr->iAgg<pAggInfo->nColumn );
+ assert( pExpr->iAgg>=0 );
+ if( pExpr->iAgg>=pAggInfo->nColumn ){
+ /* Happens when the left table of a RIGHT JOIN is null and
+ ** is using an expression index */
+ sqlite3VdbeAddOp2(v, OP_Null, 0, target);
+#ifdef SQLITE_VDBE_COVERAGE
+ /* Verify that the OP_Null above is exercised by tests
+ ** tag-20230325-2 */
+ sqlite3VdbeAddOp2(v, OP_NotNull, target, 1);
+ VdbeCoverageNeverTaken(v);
+#endif
+ break;
+ }
pCol = &pAggInfo->aCol[pExpr->iAgg];
if( !pAggInfo->directMode ){
return AggInfoColumnReg(pAggInfo, pExpr->iAgg);
}else if( pAggInfo->useSortingIdx ){
Table *pTab = pCol->pTab;
@@ -4381,15 +4405,12 @@
return pExpr->iTable;
}
#ifndef SQLITE_OMIT_CAST
case TK_CAST: {
/* Expressions of the form: CAST(pLeft AS token) */
- inReg = sqlite3ExprCodeTarget(pParse, pExpr->pLeft, target);
- if( inReg!=target ){
- sqlite3VdbeAddOp2(v, OP_SCopy, inReg, target);
- inReg = target;
- }
+ sqlite3ExprCode(pParse, pExpr->pLeft, target);
+ assert( inReg==target );
assert( !ExprHasProperty(pExpr, EP_IntValue) );
sqlite3VdbeAddOp2(v, OP_Cast, target,
sqlite3AffinityType(pExpr->u.zToken, 0));
return inReg;
}
@@ -4717,21 +4738,20 @@
case TK_BETWEEN: {
exprCodeBetween(pParse, pExpr, target, 0, 0);
return target;
}
case TK_COLLATE: {
- if( !ExprHasProperty(pExpr, EP_Collate)
- && ALWAYS(pExpr->pLeft)
- && pExpr->pLeft->op==TK_FUNCTION
- ){
- inReg = sqlite3ExprCodeTarget(pParse, pExpr->pLeft, target);
- if( inReg!=target ){
- sqlite3VdbeAddOp2(v, OP_SCopy, inReg, target);
- inReg = target;
- }
- sqlite3VdbeAddOp1(v, OP_ClrSubtype, inReg);
- return inReg;
+ if( !ExprHasProperty(pExpr, EP_Collate) ){
+ /* A TK_COLLATE Expr node without the EP_Collate tag is a so-called
+ ** "SOFT-COLLATE" that is added to constraints that are pushed down
+ ** from outer queries into sub-queries by the push-down optimization.
+ ** Clear subtypes as subtypes may not cross a subquery boundary.
+ */
+ assert( pExpr->pLeft );
+ sqlite3ExprCode(pParse, pExpr->pLeft, target);
+ sqlite3VdbeAddOp1(v, OP_ClrSubtype, target);
+ return target;
}else{
pExpr = pExpr->pLeft;
goto expr_code_doover; /* 2018-04-28: Prevent deep recursion. */
}
}
@@ -4828,20 +4848,23 @@
target);
inReg = target;
break;
}
}
- addrINR = sqlite3VdbeAddOp1(v, OP_IfNullRow, pExpr->iTable);
- /* Temporarily disable factoring of constant expressions, since
- ** even though expressions may appear to be constant, they are not
- ** really constant because they originate from the right-hand side
- ** of a LEFT JOIN. */
- pParse->okConstFactor = 0;
- inReg = sqlite3ExprCodeTarget(pParse, pExpr->pLeft, target);
+ addrINR = sqlite3VdbeAddOp3(v, OP_IfNullRow, pExpr->iTable, 0, target);
+ /* The OP_IfNullRow opcode above can overwrite the result register with
+ ** NULL. So we have to ensure that the result register is not a value
+ ** that is suppose to be a constant. Two defenses are needed:
+ ** (1) Temporarily disable factoring of constant expressions
+ ** (2) Make sure the computed value really is stored in register
+ ** "target" and not someplace else.
+ */
+ pParse->okConstFactor = 0; /* note (1) above */
+ sqlite3ExprCode(pParse, pExpr->pLeft, target);
+ assert( target==inReg );
pParse->okConstFactor = okConstFactor;
sqlite3VdbeJumpHere(v, addrINR);
- sqlite3VdbeChangeP3(v, addrINR, inReg);
break;
}
/*
** Form A:
@@ -5074,11 +5097,13 @@
assert( pParse->pVdbe!=0 || pParse->db->mallocFailed );
if( pParse->pVdbe==0 ) return;
inReg = sqlite3ExprCodeTarget(pParse, pExpr, target);
if( inReg!=target ){
u8 op;
- if( ALWAYS(pExpr) && ExprHasProperty(pExpr,EP_Subquery) ){
+ if( ALWAYS(pExpr)
+ && (ExprHasProperty(pExpr,EP_Subquery) || pExpr->op==TK_REGISTER)
+ ){
op = OP_Copy;
}else{
op = OP_SCopy;
}
sqlite3VdbeAddOp2(pParse->pVdbe, op, inReg, target);
@@ -6259,23 +6284,26 @@
){
AggInfo *pAggInfo = pExpr->pAggInfo;
int iAgg = pExpr->iAgg;
Parse *pParse = pWalker->pParse;
sqlite3 *db = pParse->db;
+ assert( iAgg>=0 );
if( pExpr->op!=TK_AGG_FUNCTION ){
- assert( iAgg>=0 && iAgg<pAggInfo->nColumn );
- if( pAggInfo->aCol[iAgg].pCExpr==pExpr ){
+ if( iAgg<pAggInfo->nColumn
+ && pAggInfo->aCol[iAgg].pCExpr==pExpr
+ ){
pExpr = sqlite3ExprDup(db, pExpr, 0);
if( pExpr ){
pAggInfo->aCol[iAgg].pCExpr = pExpr;
sqlite3ExprDeferredDelete(pParse, pExpr);
}
}
}else{
assert( pExpr->op==TK_AGG_FUNCTION );
- assert( iAgg>=0 && iAgg<pAggInfo->nFunc );
- if( pAggInfo->aFunc[iAgg].pFExpr==pExpr ){
+ if( ALWAYS(iAgg<pAggInfo->nFunc)
+ && pAggInfo->aFunc[iAgg].pFExpr==pExpr
+ ){
pExpr = sqlite3ExprDup(db, pExpr, 0);
if( pExpr ){
pAggInfo->aFunc[iAgg].pFExpr = pExpr;
sqlite3ExprDeferredDelete(pParse, pExpr);
}
@@ -6411,10 +6439,11 @@
assert( pAggInfo->iFirstReg==0 );
switch( pExpr->op ){
default: {
IndexedExpr *pIEpr;
Expr tmp;
+ int i;
assert( pParse->iSelfTab==0 );
if( (pNC->ncFlags & NC_InAggFunc)==0 ) break;
if( pParse->pIdxEpr==0 ) break;
for(pIEpr=pParse->pIdxEpr; pIEpr; pIEpr=pIEpr->pIENext){
int iDataCur = pIEpr->iDataCur;
@@ -6421,11 +6450,16 @@
if( iDataCur<0 ) continue;
if( sqlite3ExprCompare(0, pExpr, pIEpr->pExpr, iDataCur)==0 ) break;
}
if( pIEpr==0 ) break;
if( NEVER(!ExprUseYTab(pExpr)) ) break;
- if( pExpr->pAggInfo!=0 ) break; /* Already resolved by outer context */
+ for(i=0; i<pSrcList->nSrc; i++){
+ if( pSrcList->a[0].iCursor==pIEpr->iDataCur ) break;
+ }
+ if( i>=pSrcList->nSrc ) break;
+ if( NEVER(pExpr->pAggInfo!=0) ) break; /* Resolved by outer context */
+ if( pParse->nErr ){ return WRC_Abort; }
/* If we reach this point, it means that expression pExpr can be
** translated into a reference to an index column as described by
** pIEpr.
*/
@@ -6432,10 +6466,13 @@
memset(&tmp, 0, sizeof(tmp));
tmp.op = TK_AGG_COLUMN;
tmp.iTable = pIEpr->iIdxCur;
tmp.iColumn = pIEpr->iIdxCol;
findOrCreateAggInfoColumn(pParse, pAggInfo, &tmp);
+ if( pParse->nErr ){ return WRC_Abort; }
+ assert( pAggInfo->aCol!=0 );
+ assert( tmp.iAgg<pAggInfo->nColumn );
pAggInfo->aCol[tmp.iAgg].pCExpr = pExpr;
pExpr->pAggInfo = pAggInfo;
pExpr->iAgg = tmp.iAgg;
return WRC_Prune;
}
@@ -6455,11 +6492,11 @@
findOrCreateAggInfoColumn(pParse, pAggInfo, pExpr);
break;
} /* endif pExpr->iTable==pItem->iCursor */
} /* end loop over pSrcList */
}
- return WRC_Prune;
+ return WRC_Continue;
}
case TK_AGG_FUNCTION: {
if( (pNC->ncFlags & NC_InAggFunc)==0
&& pWalker->walkerDepth==pExpr->op2
){
@@ -6607,10 +6644,39 @@
*/
void sqlite3ClearTempRegCache(Parse *pParse){
pParse->nTempReg = 0;
pParse->nRangeReg = 0;
}
+
+/*
+** Make sure sufficient registers have been allocated so that
+** iReg is a valid register number.
+*/
+void sqlite3TouchRegister(Parse *pParse, int iReg){
+ if( pParse->nMem<iReg ) pParse->nMem = iReg;
+}
+
+/*
+** Return the latest reusable register in the set of all registers.
+** The value returned is no less than iMin. If any register iMin or
+** greater is in permanent use, then return one more than that last
+** permanent register.
+*/
+int sqlite3FirstAvailableRegister(Parse *pParse, int iMin){
+ const ExprList *pList = pParse->pConstExpr;
+ if( pList ){
+ int i;
+ for(i=0; i<pList->nExpr; i++){
+ if( pList->a[i].u.iConstExprReg>=iMin ){
+ iMin = pList->a[i].u.iConstExprReg + 1;
+ }
+ }
+ }
+ pParse->nTempReg = 0;
+ pParse->nRangeReg = 0;
+ return iMin;
+}
/*
** Validate that no temporary register falls within the range of
** iFirst..iLast, inclusive. This routine is only call from within assert()
** statements.
@@ -6626,9 +6692,17 @@
}
for(i=0; i<pParse->nTempReg; i++){
if( pParse->aTempReg[i]>=iFirst && pParse->aTempReg[i]<=iLast ){
return 0;
}
+ }
+ if( pParse->pConstExpr ){
+ ExprList *pList = pParse->pConstExpr;
+ for(i=0; i<pList->nExpr; i++){
+ int iReg = pList->a[i].u.iConstExprReg;
+ if( iReg==0 ) continue;
+ if( iReg>=iFirst && iReg<=iLast ) return 0;
+ }
}
return 1;
}
#endif /* SQLITE_DEBUG */
Index: src/fkey.c
==================================================================
--- src/fkey.c
+++ src/fkey.c
@@ -1315,26 +1315,26 @@
zFrom = pFKey->pFrom->zName;
nFrom = sqlite3Strlen30(zFrom);
if( action==OE_Restrict ){
int iDb = sqlite3SchemaToIndex(db, pTab->pSchema);
- Token tFrom;
- Token tDb;
+ SrcList *pSrc;
Expr *pRaise;
- tFrom.z = zFrom;
- tFrom.n = nFrom;
- tDb.z = db->aDb[iDb].zDbSName;
- tDb.n = sqlite3Strlen30(tDb.z);
-
pRaise = sqlite3Expr(db, TK_RAISE, "FOREIGN KEY constraint failed");
if( pRaise ){
pRaise->affExpr = OE_Abort;
}
+ pSrc = sqlite3SrcListAppend(pParse, 0, 0, 0);
+ if( pSrc ){
+ assert( pSrc->nSrc==1 );
+ pSrc->a[0].zName = sqlite3DbStrDup(db, zFrom);
+ pSrc->a[0].zDatabase = sqlite3DbStrDup(db, db->aDb[iDb].zDbSName);
+ }
pSelect = sqlite3SelectNew(pParse,
sqlite3ExprListAppend(pParse, 0, pRaise),
- sqlite3SrcListAppend(pParse, 0, &tDb, &tFrom),
+ pSrc,
pWhere,
0, 0, 0, 0, 0
);
pWhere = 0;
}
Index: src/func.c
==================================================================
--- src/func.c
+++ src/func.c
@@ -2158,10 +2158,22 @@
** unable to take a pointer to these functions. Hence, we here wrap them
** in our own actual functions.
*/
static double xCeil(double x){ return ceil(x); }
static double xFloor(double x){ return floor(x); }
+
+/*
+** Some systems do not have log2() and log10() in their standard math
+** libraries.
+*/
+#if defined(HAVE_LOG10) && HAVE_LOG10==0
+# define log10(X) (0.4342944819032517867*log(X))
+#endif
+#if defined(HAVE_LOG2) && HAVE_LOG2==0
+# define log2(X) (1.442695040888963456*log(X))
+#endif
+
/*
** Implementation of SQL functions:
**
** ln(X) - natural logarithm
Index: src/insert.c
==================================================================
--- src/insert.c
+++ src/insert.c
@@ -792,11 +792,11 @@
goto insert_cleanup;
}
/* Cannot insert into a read-only table.
*/
- if( sqlite3IsReadOnly(pParse, pTab, tmask) ){
+ if( sqlite3IsReadOnly(pParse, pTab, pTrigger) ){
goto insert_cleanup;
}
/* Allocate a VDBE
*/
@@ -1239,11 +1239,11 @@
sqlite3VdbeJumpHere(v, addr1);
sqlite3VdbeAddOp1(v, OP_MustBeInt, regCols); VdbeCoverage(v);
}
/* Copy the new data already generated. */
- assert( pTab->nNVCol>0 );
+ assert( pTab->nNVCol>0 || pParse->nErr>0 );
sqlite3VdbeAddOp3(v, OP_Copy, regRowid+1, regCols+1, pTab->nNVCol-1);
#ifndef SQLITE_OMIT_GENERATED_COLUMNS
/* Compute the new value for generated columns after all other
** columns have already been computed. This must be done after
Index: src/pager.c
==================================================================
--- src/pager.c
+++ src/pager.c
@@ -5520,10 +5520,14 @@
assert( !isOpen(pPager->fd) || !MEMDB );
if( !isOpen(pPager->fd) || pPager->dbSize<pgno || noContent ){
if( pgno>pPager->mxPgno ){
rc = SQLITE_FULL;
+ if( pgno<=pPager->dbSize ){
+ sqlite3PcacheRelease(pPg);
+ pPg = 0;
+ }
goto pager_acquire_err;
}
if( noContent ){
/* Failure to set the bits in the InJournal bit-vectors is benign.
** It merely means that we might do some extra work to journal a
Index: src/parse.y
==================================================================
--- src/parse.y
+++ src/parse.y
@@ -711,11 +711,11 @@
A = sqlite3SrcListAppendFromTerm(pParse,A,0,0,&Z,S,&N);
}
seltablist(A) ::= stl_prefix(A) LP seltablist(F) RP as(Z) on_using(N). {
if( A==0 && Z.n==0 && N.pOn==0 && N.pUsing==0 ){
A = F;
- }else if( F->nSrc==1 ){
+ }else if( ALWAYS(F!=0) && F->nSrc==1 ){
A = sqlite3SrcListAppendFromTerm(pParse,A,0,0,&Z,0,&N);
if( A ){
SrcItem *pNew = &A->a[A->nSrc-1];
SrcItem *pOld = F->a;
pNew->zName = pOld->zName;
Index: src/pcache.c
==================================================================
--- src/pcache.c
+++ src/pcache.c
@@ -39,11 +39,11 @@
** pointers).
*/
struct PCache {
PgHdr *pDirty, *pDirtyTail; /* List of dirty pages in LRU order */
PgHdr *pSynced; /* Last synced page in dirty page list */
- int nRefSum; /* Sum of ref counts over all pages */
+ i64 nRefSum; /* Sum of ref counts over all pages */
int szCache; /* Configured cache size */
int szSpill; /* Size before spilling occurs */
int szPage; /* Size of every page in this cache */
int szExtra; /* Size of extra space for each page */
u8 bPurgeable; /* True if pages are on backing store */
@@ -69,11 +69,11 @@
static void pcachePageTrace(int i, sqlite3_pcache_page *pLower){
PgHdr *pPg;
unsigned char *a;
int j;
pPg = (PgHdr*)pLower->pExtra;
- printf("%3d: nRef %2d flgs %02x data ", i, pPg->nRef, pPg->flags);
+ printf("%3lld: nRef %2d flgs %02x data ", i, pPg->nRef, pPg->flags);
a = (unsigned char *)pLower->pBuf;
for(j=0; j<12; j++) printf("%02x", a[j]);
printf(" ptr %p\n", pPg);
}
static void pcacheDump(PCache *pCache){
@@ -813,18 +813,18 @@
** Return the total number of references to all pages held by the cache.
**
** This is not the total number of pages referenced, but the sum of the
** reference count for all pages.
*/
-int sqlite3PcacheRefCount(PCache *pCache){
+i64 sqlite3PcacheRefCount(PCache *pCache){
return pCache->nRefSum;
}
/*
** Return the number of references to the page supplied as an argument.
*/
-int sqlite3PcachePageRefcount(PgHdr *p){
+i64 sqlite3PcachePageRefcount(PgHdr *p){
return p->nRef;
}
/*
** Return the total number of pages in the cache.
Index: src/pcache.h
==================================================================
--- src/pcache.h
+++ src/pcache.h
@@ -38,11 +38,11 @@
/**********************************************************************
** Elements above, except pCache, are public. All that follow are
** private to pcache.c and should not be accessed by other modules.
** pCache is grouped with the public elements for efficiency.
*/
- i16 nRef; /* Number of users of this page */
+ i64 nRef; /* Number of users of this page */
PgHdr *pDirtyNext; /* Next element in list of dirty pages */
PgHdr *pDirtyPrev; /* Previous element in list of dirty pages */
/* NB: pDirtyNext and pDirtyPrev are undefined if the
** PgHdr object is not dirty */
};
@@ -119,16 +119,16 @@
/* Discard the contents of the cache */
void sqlite3PcacheClear(PCache*);
/* Return the total number of outstanding page references */
-int sqlite3PcacheRefCount(PCache*);
+i64 sqlite3PcacheRefCount(PCache*);
/* Increment the reference count of an existing page */
void sqlite3PcacheRef(PgHdr*);
-int sqlite3PcachePageRefcount(PgHdr*);
+i64 sqlite3PcachePageRefcount(PgHdr*);
/* Return the total number of pages stored in the cache */
int sqlite3PcachePagecount(PCache*);
#if defined(SQLITE_CHECK_PAGES) || defined(SQLITE_DEBUG)
Index: src/pragma.c
==================================================================
--- src/pragma.c
+++ src/pragma.c
@@ -1522,11 +1522,11 @@
if( pTab==0 || !IsOrdinaryTable(pTab) || pTab->u.tab.pFKey==0 ) continue;
iDb = sqlite3SchemaToIndex(db, pTab->pSchema);
zDb = db->aDb[iDb].zDbSName;
sqlite3CodeVerifySchema(pParse, iDb);
sqlite3TableLock(pParse, iDb, pTab->tnum, 0, pTab->zName);
- if( pTab->nCol+regRow>pParse->nMem ) pParse->nMem = pTab->nCol + regRow;
+ sqlite3TouchRegister(pParse, pTab->nCol+regRow);
sqlite3OpenTable(pParse, 0, iDb, pTab, OP_OpenRead);
sqlite3VdbeLoadString(v, regResult, pTab->zName);
assert( IsOrdinaryTable(pTab) );
for(i=1, pFK=pTab->u.tab.pFKey; pFK; i++, pFK=pFK->pNextFrom){
pParent = sqlite3FindTable(db, pFK->zTo, zDb);
@@ -1563,11 +1563,11 @@
/* Generate code to read the child key values into registers
** regRow..regRow+n. If any of the child key values are NULL, this
** row cannot cause an FK violation. Jump directly to addrOk in
** this case. */
- if( regRow+pFK->nCol>pParse->nMem ) pParse->nMem = regRow+pFK->nCol;
+ sqlite3TouchRegister(pParse, regRow + pFK->nCol);
for(j=0; j<pFK->nCol; j++){
int iCol = aiCols ? aiCols[j] : pFK->aCol[j].iFrom;
sqlite3ExprCodeGetColumnOfTable(v, pTab, 0, iCol, regRow+j);
sqlite3VdbeAddOp2(v, OP_IsNull, regRow+j, addrOk); VdbeCoverage(v);
}
@@ -1692,10 +1692,11 @@
if( OMIT_TEMPDB && i==1 ) continue;
if( iDb>=0 && i!=iDb ) continue;
sqlite3CodeVerifySchema(pParse, i);
+ pParse->okConstFactor = 0; /* tag-20230327-1 */
/* Do an integrity check of the B-Tree
**
** Begin by finding the root pages numbers
** for all tables and indices in the database.
@@ -1727,11 +1728,11 @@
}
}
aRoot[0] = cnt;
/* Make sure sufficient number of registers have been allocated */
- pParse->nMem = MAX( pParse->nMem, 8+mxIdx );
+ sqlite3TouchRegister(pParse, 8+mxIdx);
sqlite3ClearTempRegCache(pParse);
/* Do the b-tree integrity checks */
sqlite3VdbeAddOp4(v, OP_IntegrityCk, 2, cnt, 1, (char*)aRoot,P4_INTARRAY);
sqlite3VdbeChangeP5(v, (u8)i);
@@ -1877,19 +1878,33 @@
labelError = sqlite3VdbeMakeLabel(pParse);
labelOk = sqlite3VdbeMakeLabel(pParse);
if( pCol->notNull ){
/* (1) NOT NULL columns may not contain a NULL */
+ int jmp3;
int jmp2 = sqlite3VdbeAddOp4Int(v, OP_IsType, p1, labelOk, p3, p4);
- sqlite3VdbeChangeP5(v, 0x0f);
VdbeCoverage(v);
+ if( p1<0 ){
+ sqlite3VdbeChangeP5(v, 0x0f); /* INT, REAL, TEXT, or BLOB */
+ jmp3 = jmp2;
+ }else{
+ sqlite3VdbeChangeP5(v, 0x0d); /* INT, TEXT, or BLOB */
+ /* OP_IsType does not detect NaN values in the database file
+ ** which should be treated as a NULL. So if the header type
+ ** is REAL, we have to load the actual data using OP_Column
+ ** to reliably determine if the value is a NULL. */
+ sqlite3VdbeAddOp3(v, OP_Column, p1, p3, 3);
+ jmp3 = sqlite3VdbeAddOp2(v, OP_NotNull, 3, labelOk);
+ VdbeCoverage(v);
+ }
zErr = sqlite3MPrintf(db, "NULL value in %s.%s", pTab->zName,
pCol->zCnName);
sqlite3VdbeAddOp4(v, OP_String8, 0, 3, 0, zErr, P4_DYNAMIC);
if( doTypeCheck ){
sqlite3VdbeGoto(v, labelError);
sqlite3VdbeJumpHere(v, jmp2);
+ sqlite3VdbeJumpHere(v, jmp3);
}else{
/* VDBE byte code will fall thru */
}
}
if( bStrict && doTypeCheck ){
@@ -1984,10 +1999,27 @@
sqlite3VdbeAddOp3(v, OP_Concat, 4, 3, 3);
jmp5 = sqlite3VdbeLoadString(v, 4, pIdx->zName);
sqlite3VdbeAddOp3(v, OP_Concat, 4, 3, 3);
jmp4 = integrityCheckResultRow(v);
sqlite3VdbeJumpHere(v, jmp2);
+
+ /* The OP_IdxRowid opcode is an optimized version of OP_Column
+ ** that extracts the rowid off the end of the index record.
+ ** But it only works correctly if index record does not have
+ ** any extra bytes at the end. Verify that this is the case. */
+ if( HasRowid(pTab) ){
+ int jmp7;
+ sqlite3VdbeAddOp2(v, OP_IdxRowid, iIdxCur+j, 3);
+ jmp7 = sqlite3VdbeAddOp3(v, OP_Eq, 3, 0, r1+pIdx->nColumn-1);
+ VdbeCoverage(v);
+ sqlite3VdbeLoadString(v, 3,
+ "rowid not at end-of-record for row ");
+ sqlite3VdbeAddOp3(v, OP_Concat, 7, 3, 3);
+ sqlite3VdbeLoadString(v, 4, " of index ");
+ sqlite3VdbeGoto(v, jmp5-1);
+ sqlite3VdbeJumpHere(v, jmp7);
+ }
/* Any indexed columns with non-BINARY collations must still hold
** the exact same text value as the table. */
label6 = 0;
for(kk=0; kk<pIdx->nKeyCol; kk++){
Index: src/prepare.c
==================================================================
--- src/prepare.c
+++ src/prepare.c
@@ -304,11 +304,13 @@
encoding = (u8)meta[BTREE_TEXT_ENCODING-1] & 3;
if( encoding==0 ) encoding = SQLITE_UTF8;
#else
encoding = SQLITE_UTF8;
#endif
- if( db->nVdbeActive>0 && encoding!=ENC(db) ){
+ if( db->nVdbeActive>0 && encoding!=ENC(db)
+ && (db->mDbFlags & DBFLAG_Vacuum)==0
+ ){
rc = SQLITE_LOCKED;
goto initone_error_out;
}else{
sqlite3SetTextEncoding(db, encoding);
}
@@ -698,11 +700,15 @@
sParse.pOuterParse = db->pParse;
db->pParse = &sParse;
sParse.db = db;
sParse.pReprepare = pReprepare;
assert( ppStmt && *ppStmt==0 );
- if( db->mallocFailed ) sqlite3ErrorMsg(&sParse, "out of memory");
+ if( db->mallocFailed ){
+ sqlite3ErrorMsg(&sParse, "out of memory");
+ db->errCode = rc = SQLITE_NOMEM;
+ goto end_prepare;
+ }
assert( sqlite3_mutex_held(db->mutex) );
/* For a long-term use prepared statement avoid the use of
** lookaside memory.
*/
Index: src/resolve.c
==================================================================
--- src/resolve.c
+++ src/resolve.c
@@ -945,18 +945,14 @@
}
sqlite3WalkExpr(pWalker, pExpr->pLeft);
if( 0==sqlite3ExprCanBeNull(pExpr->pLeft) && !IN_RENAME_OBJECT ){
testcase( ExprHasProperty(pExpr, EP_OuterON) );
assert( !ExprHasProperty(pExpr, EP_IntValue) );
- if( pExpr->op==TK_NOTNULL ){
- pExpr->u.zToken = "true";
- ExprSetProperty(pExpr, EP_IsTrue);
- }else{
- pExpr->u.zToken = "false";
- ExprSetProperty(pExpr, EP_IsFalse);
- }
- pExpr->op = TK_TRUEFALSE;
+ pExpr->u.iValue = (pExpr->op==TK_NOTNULL);
+ pExpr->flags |= EP_IntValue;
+ pExpr->op = TK_INTEGER;
+
for(i=0, p=pNC; p && i<ArraySize(anRef); p=p->pNext, i++){
p->nRef = anRef[i];
}
sqlite3ExprDelete(pParse->db, pExpr->pLeft);
pExpr->pLeft = 0;
@@ -1254,12 +1250,12 @@
sqlite3WalkSelect(pWalker, pExpr->x.pSelect);
}
assert( pNC->nRef>=nRef );
if( nRef!=pNC->nRef ){
ExprSetProperty(pExpr, EP_VarSelect);
- pNC->ncFlags |= NC_VarSelect;
}
+ pNC->ncFlags |= NC_Subquery;
}
break;
}
case TK_VARIABLE: {
testcase( pNC->ncFlags & NC_IsCheck );
Index: src/select.c
==================================================================
--- src/select.c
+++ src/select.c
@@ -2316,11 +2316,11 @@
assert( pSelect!=0 );
assert( (pSelect->selFlags & SF_Resolved)!=0 );
assert( pTab->nCol==pSelect->pEList->nExpr || pParse->nErr>0 );
assert( aff==SQLITE_AFF_NONE || aff==SQLITE_AFF_BLOB );
- if( db->mallocFailed ) return;
+ if( db->mallocFailed || IN_RENAME_OBJECT ) return;
while( pSelect->pPrior ) pSelect = pSelect->pPrior;
a = pSelect->pEList->a;
memset(&sNC, 0, sizeof(sNC));
sNC.pSrcList = pSelect->pSrc;
for(i=0, pCol=pTab->aCol; i<pTab->nCol; i++, pCol++){
@@ -2330,12 +2330,10 @@
p = a[i].pExpr;
/* pCol->szEst = ... // Column size est for SELECT tables never used */
pCol->affinity = sqlite3ExprAffinity(p);
if( pCol->affinity<=SQLITE_AFF_NONE ){
pCol->affinity = aff;
- }else if( pCol->affinity>=SQLITE_AFF_NUMERIC && p->op==TK_CAST ){
- pCol->affinity = SQLITE_AFF_FLEXNUM;
}
if( pCol->affinity>=SQLITE_AFF_TEXT && pSelect->pNext ){
int m = 0;
Select *pS2;
for(m=0, pS2=pSelect->pNext; pS2; pS2=pS2->pNext){
@@ -2344,10 +2342,13 @@
if( pCol->affinity==SQLITE_AFF_TEXT && (m&0x01)!=0 ){
pCol->affinity = SQLITE_AFF_BLOB;
}else
if( pCol->affinity>=SQLITE_AFF_NUMERIC && (m&0x02)!=0 ){
pCol->affinity = SQLITE_AFF_BLOB;
+ }
+ if( pCol->affinity>=SQLITE_AFF_NUMERIC && p->op==TK_CAST ){
+ pCol->affinity = SQLITE_AFF_FLEXNUM;
}
}
zType = columnType(&sNC, p, 0, 0, 0);
if( zType==0 || pCol->affinity!=sqlite3AffinityType(zType, 0) ){
if( pCol->affinity==SQLITE_AFF_NUMERIC
@@ -2360,22 +2361,22 @@
if( sqlite3StdTypeAffinity[j]==pCol->affinity ){
zType = sqlite3StdType[j];
break;
}
}
- }
- }
- if( zType ){
- i64 m = sqlite3Strlen30(zType);
- n = sqlite3Strlen30(pCol->zCnName);
- pCol->zCnName = sqlite3DbReallocOrFree(db, pCol->zCnName, n+m+2);
- if( pCol->zCnName ){
- memcpy(&pCol->zCnName[n+1], zType, m+1);
- pCol->colFlags |= COLFLAG_HASTYPE;
- }else{
- testcase( pCol->colFlags & COLFLAG_HASTYPE );
- pCol->colFlags &= ~(COLFLAG_HASTYPE|COLFLAG_HASCOLL);
+ }
+ }
+ if( zType ){
+ i64 m = sqlite3Strlen30(zType);
+ n = sqlite3Strlen30(pCol->zCnName);
+ pCol->zCnName = sqlite3DbReallocOrFree(db, pCol->zCnName, n+m+2);
+ pCol->colFlags &= ~(COLFLAG_HASTYPE|COLFLAG_HASCOLL);
+ if( pCol->zCnName ){
+ memcpy(&pCol->zCnName[n+1], zType, m+1);
+ pCol->colFlags |= COLFLAG_HASTYPE;
+ }else{
+ testcase( pCol->colFlags & COLFLAG_HASTYPE );
}
}
pColl = sqlite3ExprCollSeq(pParse, p);
if( pColl ){
assert( pTab->pIndex==0 );
@@ -3859,11 +3860,13 @@
assert( pExpr->pRight==0 );
if( sqlite3ExprIsVector(pCopy) ){
sqlite3VectorErrorMsg(pSubst->pParse, pCopy);
}else{
sqlite3 *db = pSubst->pParse->db;
- if( pSubst->isOuterJoin && pCopy->op!=TK_COLUMN ){
+ if( pSubst->isOuterJoin
+ && (pCopy->op!=TK_COLUMN || pCopy->iTable!=pSubst->iNewTable)
+ ){
memset(&ifNullRow, 0, sizeof(ifNullRow));
ifNullRow.op = TK_IF_NULL_ROW;
ifNullRow.pLeft = pCopy;
ifNullRow.iTable = pSubst->iNewTable;
ifNullRow.iColumn = -99;
@@ -6375,18 +6378,21 @@
Select *pSelect, /* The SELECT statement being processed */
AggInfo *pAggInfo, /* The aggregate info */
NameContext *pNC /* Name context used to resolve agg-func args */
){
assert( pAggInfo->iFirstReg==0 );
+ assert( pSelect!=0 );
+ assert( pSelect->pGroupBy!=0 );
pAggInfo->nColumn = pAggInfo->nAccumulator;
if( ALWAYS(pAggInfo->nSortingColumn>0) ){
- if( pAggInfo->nColumn==0 ){
- pAggInfo->nSortingColumn = 0;
- }else{
- pAggInfo->nSortingColumn =
- pAggInfo->aCol[pAggInfo->nColumn-1].iSorterColumn+1;
+ int mx = pSelect->pGroupBy->nExpr - 1;
+ int j, k;
+ for(j=0; j<pAggInfo->nColumn; j++){
+ k = pAggInfo->aCol[j].iSorterColumn;
+ if( k>mx ) mx = k;
}
+ pAggInfo->nSortingColumn = mx+1;
}
analyzeAggFuncArgs(pAggInfo, pNC);
#if TREETRACE_ENABLED
if( sqlite3TreeTrace & 0x20 ){
IndexedExpr *pIEpr;
@@ -6416,15 +6422,17 @@
if( pExpr->pAggInfo==0 ) return WRC_Continue;
if( pExpr->op==TK_AGG_COLUMN ) return WRC_Continue;
if( pExpr->op==TK_AGG_FUNCTION ) return WRC_Continue;
if( pExpr->op==TK_IF_NULL_ROW ) return WRC_Continue;
pAggInfo = pExpr->pAggInfo;
- assert( pExpr->iAgg>=0 && pExpr->iAgg<pAggInfo->nColumn );
+ if( NEVER(pExpr->iAgg>=pAggInfo->nColumn) ) return WRC_Continue;
+ assert( pExpr->iAgg>=0 );
pCol = &pAggInfo->aCol[pExpr->iAgg];
pExpr->op = TK_AGG_COLUMN;
pExpr->iTable = pCol->iTable;
pExpr->iColumn = pCol->iColumn;
+ ExprClearProperty(pExpr, EP_Skip|EP_Collate);
return WRC_Prune;
}
/*
** Convert every pAggInfo->aFunc[].pExpr such that any node within
@@ -6802,11 +6810,13 @@
Expr *pCount;
sqlite3 *db;
if( (p->selFlags & SF_Aggregate)==0 ) return 0; /* This is an aggregate */
if( p->pEList->nExpr!=1 ) return 0; /* Single result column */
if( p->pWhere ) return 0;
+ if( p->pHaving ) return 0;
if( p->pGroupBy ) return 0;
+ if( p->pOrderBy ) return 0;
pExpr = p->pEList->a[0].pExpr;
if( pExpr->op!=TK_AGG_FUNCTION ) return 0; /* Result is an aggregate */
assert( ExprUseUToken(pExpr) );
if( sqlite3_stricmp(pExpr->u.zToken,"count") ) return 0; /* Is count() */
assert( ExprUseXList(pExpr) );
@@ -6813,17 +6823,19 @@
if( pExpr->x.pList!=0 ) return 0; /* Must be count(*) */
if( p->pSrc->nSrc!=1 ) return 0; /* One table in FROM */
if( ExprHasProperty(pExpr, EP_WinFunc) ) return 0;/* Not a window function */
pSub = p->pSrc->a[0].pSelect;
if( pSub==0 ) return 0; /* The FROM is a subquery */
- if( pSub->pPrior==0 ) return 0; /* Must be a compound ry */
+ if( pSub->pPrior==0 ) return 0; /* Must be a compound */
+ if( pSub->selFlags & SF_CopyCte ) return 0; /* Not a CTE */
do{
if( pSub->op!=TK_ALL && pSub->pPrior ) return 0; /* Must be UNION ALL */
if( pSub->pWhere ) return 0; /* No WHERE clause */
if( pSub->pLimit ) return 0; /* No LIMIT clause */
if( pSub->selFlags & SF_Aggregate ) return 0; /* Not an aggregate */
- pSub = pSub->pPrior; /* Repeat over compound */
+ assert( pSub->pHaving==0 ); /* Due to the previous */
+ pSub = pSub->pPrior; /* Repeat over compound */
}while( pSub );
/* If we reach this point then it is OK to perform the transformation */
db = pParse->db;
@@ -7256,11 +7268,10 @@
#ifdef SQLITE_COUNTOFVIEW_OPTIMIZATION
if( OptimizationEnabled(db, SQLITE_QueryFlattener|SQLITE_CountOfView)
&& countOfViewOptimization(pParse, p)
){
if( db->mallocFailed ) goto select_end;
- pEList = p->pEList;
pTabList = p->pSrc;
}
#endif
/* For each term in the FROM clause, do two things:
Index: src/shell.c.in
==================================================================
--- src/shell.c.in
+++ src/shell.c.in
@@ -1827,10 +1827,11 @@
/*
** Output the given string as a quoted according to JSON quoting rules.
*/
static void output_json_string(FILE *out, const char *z, i64 n){
unsigned int c;
+ if( z==0 ) z = "";
if( n<0 ) n = strlen(z);
fputc('"', out);
while( n-- ){
c = *(z++);
if( c=='\\' || c=='"' ){
@@ -2086,10 +2087,11 @@
const char *zOrig = z;
static const char *azTerm[] = { "", "*/", "\n" };
int i;
for(i=0; i<ArraySize(azTerm); i++){
char *zNew = sqlite3_mprintf("%s%s;", zOrig, azTerm[i]);
+ shell_check_oom(zNew);
if( sqlite3_complete(zNew) ){
size_t n = strlen(zNew);
zNew[n-1] = 0;
zToFree = zNew;
z = zNew;
@@ -2772,10 +2774,11 @@
char *zMsg;
int i;
if( db==0
|| zSql==0
|| (iOffset = sqlite3_error_offset(db))<0
+ || iOffset>=strlen(zSql)
){
return sqlite3_mprintf("");
}
while( iOffset>50 ){
iOffset--;
@@ -2783,11 +2786,11 @@
while( (zSql[0]&0xc0)==0x80 ){ zSql++; iOffset--; }
}
len = strlen(zSql);
if( len>78 ){
len = 78;
- while( (zSql[len]&0xc0)==0x80 ) len--;
+ while( len>0 && (zSql[len]&0xc0)==0x80 ) len--;
}
zCode = sqlite3_mprintf("%.*s", len, zSql);
shell_check_oom(zCode);
for(i=0; zCode[i]; i++){ if( IsSpace(zSql[i]) ) zCode[i] = ' '; }
if( iOffset<25 ){
@@ -3673,10 +3676,11 @@
if( wx==0 ){
wx = p->cmOpts.iWrap;
}
if( wx<0 ) wx = -wx;
uz = (const unsigned char*)sqlite3_column_name(pStmt,i);
+ if( uz==0 ) uz = (u8*)"";
azData[i] = translateForDisplayAndDup(uz, &zNotUsed, wx, bw);
}
do{
int useNextLine = bNextLine;
bNextLine = 0;
@@ -5088,57 +5092,10 @@
utf8_printf(stderr,"Error on line %d of --hexdb input\n", nLine);
return 0;
}
#endif /* SQLITE_OMIT_DESERIALIZE */
-/*
-** Scalar function "shell_int32". The first argument to this function
-** must be a blob. The second a non-negative integer. This function
-** reads and returns a 32-bit big-endian integer from byte
-** offset (4*<arg2>) of the blob.
-*/
-static void shellInt32(
- sqlite3_context *context,
- int argc,
- sqlite3_value **argv
-){
- const unsigned char *pBlob;
- int nBlob;
- int iInt;
-
- UNUSED_PARAMETER(argc);
- nBlob = sqlite3_value_bytes(argv[0]);
- pBlob = (const unsigned char*)sqlite3_value_blob(argv[0]);
- iInt = sqlite3_value_int(argv[1]);
-
- if( iInt>=0 && (iInt+1)*4<=nBlob ){
- const unsigned char *a = &pBlob[iInt*4];
- sqlite3_int64 iVal = ((sqlite3_int64)a[0]<<24)
- + ((sqlite3_int64)a[1]<<16)
- + ((sqlite3_int64)a[2]<< 8)
- + ((sqlite3_int64)a[3]<< 0);
- sqlite3_result_int64(context, iVal);
- }
-}
-
-/*
-** Scalar function "shell_idquote(X)" returns string X quoted as an identifier,
-** using "..." with internal double-quote characters doubled.
-*/
-static void shellIdQuote(
- sqlite3_context *context,
- int argc,
- sqlite3_value **argv
-){
- const char *zName = (const char*)sqlite3_value_text(argv[0]);
- UNUSED_PARAMETER(argc);
- if( zName ){
- char *z = sqlite3_mprintf("\"%w\"", zName);
- sqlite3_result_text(context, z, -1, sqlite3_free);
- }
-}
-
/*
** Scalar function "usleep(X)" invokes sqlite3_sleep(X) and returns X.
*/
static void shellUSleepFunc(
sqlite3_context *context,
@@ -5149,101 +5106,10 @@
(void)argcUnused;
sqlite3_sleep(sleep/1000);
sqlite3_result_int(context, sleep);
}
-/*
-** Scalar function "shell_escape_crnl" used by the .recover command.
-** The argument passed to this function is the output of built-in
-** function quote(). If the first character of the input is "'",
-** indicating that the value passed to quote() was a text value,
-** then this function searches the input for "\n" and "\r" characters
-** and adds a wrapper similar to the following:
-**
-** replace(replace(<input>, '\n', char(10), '\r', char(13));
-**
-** Or, if the first character of the input is not "'", then a copy
-** of the input is returned.
-*/
-static void shellEscapeCrnl(
- sqlite3_context *context,
- int argc,
- sqlite3_value **argv
-){
- const char *zText = (const char*)sqlite3_value_text(argv[0]);
- UNUSED_PARAMETER(argc);
- if( zText && zText[0]=='\'' ){
- i64 nText = sqlite3_value_bytes(argv[0]);
- i64 i;
- char zBuf1[20];
- char zBuf2[20];
- const char *zNL = 0;
- const char *zCR = 0;
- i64 nCR = 0;
- i64 nNL = 0;
-
- for(i=0; zText[i]; i++){
- if( zNL==0 && zText[i]=='\n' ){
- zNL = unused_string(zText, "\\n", "\\012", zBuf1);
- nNL = strlen(zNL);
- }
- if( zCR==0 && zText[i]=='\r' ){
- zCR = unused_string(zText, "\\r", "\\015", zBuf2);
- nCR = strlen(zCR);
- }
- }
-
- if( zNL || zCR ){
- i64 iOut = 0;
- i64 nMax = (nNL > nCR) ? nNL : nCR;
- i64 nAlloc = nMax * nText + (nMax+64)*2;
- char *zOut = (char*)sqlite3_malloc64(nAlloc);
- if( zOut==0 ){
- sqlite3_result_error_nomem(context);
- return;
- }
-
- if( zNL && zCR ){
- memcpy(&zOut[iOut], "replace(replace(", 16);
- iOut += 16;
- }else{
- memcpy(&zOut[iOut], "replace(", 8);
- iOut += 8;
- }
- for(i=0; zText[i]; i++){
- if( zText[i]=='\n' ){
- memcpy(&zOut[iOut], zNL, nNL);
- iOut += nNL;
- }else if( zText[i]=='\r' ){
- memcpy(&zOut[iOut], zCR, nCR);
- iOut += nCR;
- }else{
- zOut[iOut] = zText[i];
- iOut++;
- }
- }
-
- if( zNL ){
- memcpy(&zOut[iOut], ",'", 2); iOut += 2;
- memcpy(&zOut[iOut], zNL, nNL); iOut += nNL;
- memcpy(&zOut[iOut], "', char(10))", 12); iOut += 12;
- }
- if( zCR ){
- memcpy(&zOut[iOut], ",'", 2); iOut += 2;
- memcpy(&zOut[iOut], zCR, nCR); iOut += nCR;
- memcpy(&zOut[iOut], "', char(13))", 12); iOut += 12;
- }
-
- sqlite3_result_text(context, zOut, iOut, SQLITE_TRANSIENT);
- sqlite3_free(zOut);
- return;
- }
- }
-
- sqlite3_result_value(context, argv[0]);
-}
-
/* Flags for open_db().
**
** The default behavior of open_db() is to exit(1) if the database fails to
** open. The OPEN_DB_KEEPALIVE flag changes that so that it prints an error
** but still returns without calling exit.
@@ -5364,16 +5230,10 @@
shellAddSchemaName, 0, 0);
sqlite3_create_function(p->db, "shell_module_schema", 1, SQLITE_UTF8, 0,
shellModuleSchema, 0, 0);
sqlite3_create_function(p->db, "shell_putsnl", 1, SQLITE_UTF8, p,
shellPutsFunc, 0, 0);
- sqlite3_create_function(p->db, "shell_escape_crnl", 1, SQLITE_UTF8, 0,
- shellEscapeCrnl, 0, 0);
- sqlite3_create_function(p->db, "shell_int32", 2, SQLITE_UTF8, 0,
- shellInt32, 0, 0);
- sqlite3_create_function(p->db, "shell_idquote", 1, SQLITE_UTF8, 0,
- shellIdQuote, 0, 0);
sqlite3_create_function(p->db, "usleep",1,SQLITE_UTF8,0,
shellUSleepFunc, 0, 0);
#ifndef SQLITE_NOHAVE_SYSTEM
sqlite3_create_function(p->db, "edit", 1, SQLITE_UTF8, 0,
editFunc, 0, 0);
@@ -5639,11 +5499,11 @@
if( p->traceOut==0 ) return 0;
if( mType==SQLITE_TRACE_CLOSE ){
utf8_printf(p->traceOut, "-- closing database connection\n");
return 0;
}
- if( mType!=SQLITE_TRACE_ROW && ((const char*)pX)[0]=='-' ){
+ if( mType!=SQLITE_TRACE_ROW && pX!=0 && ((const char*)pX)[0]=='-' ){
zSql = (const char*)pX;
}else{
pStmt = (sqlite3_stmt*)pP;
switch( p->eTraceType ){
case SHELL_TRACE_EXPANDED: {
@@ -6199,11 +6059,13 @@
}
sqlite3_bind_text(pStmt, 1, zDb, -1, SQLITE_STATIC);
if( sqlite3_step(pStmt)==SQLITE_ROW
&& sqlite3_column_bytes(pStmt,0)>100
){
- memcpy(aHdr, sqlite3_column_blob(pStmt,0), 100);
+ const u8 *pb = sqlite3_column_blob(pStmt,0);
+ shell_check_oom((void*)pb);
+ memcpy(aHdr, pb, 100);
sqlite3_finalize(pStmt);
}else{
raw_printf(stderr, "unable to read database header\n");
sqlite3_finalize(pStmt);
return 1;
Index: src/sqliteInt.h
==================================================================
--- src/sqliteInt.h
+++ src/sqliteInt.h
@@ -3339,11 +3339,11 @@
#define NC_IsCheck 0x000004 /* True if resolving a CHECK constraint */
#define NC_GenCol 0x000008 /* True for a GENERATED ALWAYS AS clause */
#define NC_HasAgg 0x000010 /* One or more aggregate functions seen */
#define NC_IdxExpr 0x000020 /* True if resolving columns of CREATE INDEX */
#define NC_SelfRef 0x00002e /* Combo: PartIdx, isCheck, GenCol, and IdxExpr */
-#define NC_VarSelect 0x000040 /* A correlated subquery has been seen */
+#define NC_Subquery 0x000040 /* A subquery has been seen */
#define NC_UEList 0x000080 /* True if uNC.pEList is used */
#define NC_UAggInfo 0x000100 /* True if uNC.pAggInfo is used */
#define NC_UUpsert 0x000200 /* True if uNC.pUpsert is used */
#define NC_UBaseReg 0x000400 /* True if uNC.iBaseReg is used */
#define NC_MinMaxAgg 0x001000 /* min/max aggregates seen. See note above */
@@ -3658,10 +3658,11 @@
Expr *pExpr; /* The expression contained in the index */
int iDataCur; /* The data cursor associated with the index */
int iIdxCur; /* The index cursor */
int iIdxCol; /* The index column that contains value of pExpr */
u8 bMaybeNullRow; /* True if we need an OP_IfNullRow check */
+ u8 aff; /* Affinity of the pExpr expression */
IndexedExpr *pIENext; /* Next in a list of all indexed expressions */
#ifdef SQLITE_ENABLE_EXPLAIN_COMMENTS
const char *zIdxName; /* Name of index, used only for bytecode comments */
#endif
};
@@ -3710,10 +3711,13 @@
u8 prepFlags; /* SQLITE_PREPARE_* flags */
u8 withinRJSubrtn; /* Nesting level for RIGHT JOIN body subroutines */
#if defined(SQLITE_DEBUG) || defined(SQLITE_COVERAGE_TEST)
u8 earlyCleanup; /* OOM inside sqlite3ParserAddCleanup() */
#endif
+#ifdef SQLITE_DEBUG
+ u8 ifNotExists; /* Might be true if IF NOT EXISTS. Assert()s only */
+#endif
int nRangeReg; /* Size of the temporary register block */
int iRangeReg; /* First register in temporary register block */
int nErr; /* Number of errors seen */
int nTab; /* Number of previously allocated VDBE cursors */
int nMem; /* Number of memory cells used so far */
@@ -4641,10 +4645,12 @@
int sqlite3GetTempReg(Parse*);
void sqlite3ReleaseTempReg(Parse*,int);
int sqlite3GetTempRange(Parse*,int);
void sqlite3ReleaseTempRange(Parse*,int,int);
void sqlite3ClearTempRegCache(Parse*);
+void sqlite3TouchRegister(Parse*,int);
+int sqlite3FirstAvailableRegister(Parse*,int);
#ifdef SQLITE_DEBUG
int sqlite3NoTempsInRange(Parse*,int,int);
#endif
Expr *sqlite3ExprAlloc(sqlite3*,int,const Token*,int);
Expr *sqlite3Expr(sqlite3*,int,const char*);
@@ -4791,11 +4797,11 @@
int sqlite3Select(Parse*, Select*, SelectDest*);
Select *sqlite3SelectNew(Parse*,ExprList*,SrcList*,Expr*,ExprList*,
Expr*,ExprList*,u32,Expr*);
void sqlite3SelectDelete(sqlite3*, Select*);
Table *sqlite3SrcListLookup(Parse*, SrcList*);
-int sqlite3IsReadOnly(Parse*, Table*, int);
+int sqlite3IsReadOnly(Parse*, Table*, Trigger*);
void sqlite3OpenTable(Parse*, int iCur, int iDb, Table*, int);
#if defined(SQLITE_ENABLE_UPDATE_DELETE_LIMIT) && !defined(SQLITE_OMIT_SUBQUERY)
Expr *sqlite3LimitWhere(Parse*,SrcList*,Expr*,ExprList*,Expr*,char*);
#endif
void sqlite3CodeChangeCount(Vdbe*,int,const char*);
Index: src/trigger.c
==================================================================
--- src/trigger.c
+++ src/trigger.c
@@ -200,10 +200,11 @@
if( !noErr ){
sqlite3ErrorMsg(pParse, "trigger %T already exists", pName);
}else{
assert( !db->init.busy );
sqlite3CodeVerifySchema(pParse, iDb);
+ VVA_ONLY( pParse->ifNotExists = 1; )
}
goto trigger_cleanup;
}
}
@@ -981,11 +982,11 @@
assert( db->mallocFailed==0 );
sqlite3GenerateColumnNames(pParse, &sSelect);
}
sqlite3ExprListDelete(db, sSelect.pEList);
pNew = sqlite3ExpandReturning(pParse, pReturning->pReturnEL, pTab);
- if( !db->mallocFailed ){
+ if( pParse->nErr==0 ){
NameContext sNC;
memset(&sNC, 0, sizeof(sNC));
if( pReturning->nRetCol==0 ){
pReturning->nRetCol = pNew->nExpr;
pReturning->iRetCur = pParse->nTab++;
@@ -1450,10 +1451,13 @@
const int op = pChanges ? TK_UPDATE : TK_DELETE;
u32 mask = 0;
Trigger *p;
assert( isNew==1 || isNew==0 );
+ if( IsView(pTab) ){
+ return 0xffffffff;
+ }
for(p=pTrigger; p; p=p->pNext){
if( p->op==op
&& (tr_tm&p->tr_tm)
&& checkColumnOverlap(p->pColumns,pChanges)
){
Index: src/update.c
==================================================================
--- src/update.c
+++ src/update.c
@@ -406,11 +406,11 @@
#endif
if( sqlite3ViewGetColumnNames(pParse, pTab) ){
goto update_cleanup;
}
- if( sqlite3IsReadOnly(pParse, pTab, tmask) ){
+ if( sqlite3IsReadOnly(pParse, pTab, pTrigger) ){
goto update_cleanup;
}
/* Allocate a cursors for the main database table and for all indices.
** The index cursors might not be used, but if they are used they
@@ -725,16 +725,26 @@
bFinishSeek = 0;
}else{
/* Begin the database scan.
**
** Do not consider a single-pass strategy for a multi-row update if
- ** there are any triggers or foreign keys to process, or rows may
- ** be deleted as a result of REPLACE conflict handling. Any of these
- ** things might disturb a cursor being used to scan through the table
- ** or index, causing a single-pass approach to malfunction. */
+ ** there is anything that might disrupt the cursor being used to do
+ ** the UPDATE:
+ ** (1) This is a nested UPDATE
+ ** (2) There are triggers
+ ** (3) There are FOREIGN KEY constraints
+ ** (4) There are REPLACE conflict handlers
+ ** (5) There are subqueries in the WHERE clause
+ */
flags = WHERE_ONEPASS_DESIRED;
- if( !pParse->nested && !pTrigger && !hasFK && !chngKey && !bReplace ){
+ if( !pParse->nested
+ && !pTrigger
+ && !hasFK
+ && !chngKey
+ && !bReplace
+ && (sNC.ncFlags & NC_Subquery)==0
+ ){
flags |= WHERE_ONEPASS_MULTIROW;
}
pWInfo = sqlite3WhereBegin(pParse, pTabList, pWhere,0,0,0,flags,iIdxCur);
if( pWInfo==0 ) goto update_cleanup;
Index: src/vdbe.c
==================================================================
--- src/vdbe.c
+++ src/vdbe.c
@@ -681,12 +681,11 @@
if( p->flags & (MEM_Int|MEM_IntReal) ){
h += p->u.i;
}else if( p->flags & MEM_Real ){
h += sqlite3VdbeIntValue(p);
}else if( p->flags & (MEM_Str|MEM_Blob) ){
- h += p->n;
- if( p->flags & MEM_Zero ) h += p->u.nZero;
+ /* no-op */
}
}
return h;
}
@@ -2616,10 +2615,16 @@
** from the value in that register.
**
** P5 is a bitmask of data types. SQLITE_INTEGER is the least significant
** (0x01) bit. SQLITE_FLOAT is the 0x02 bit. SQLITE_TEXT is 0x04.
** SQLITE_BLOB is 0x08. SQLITE_NULL is 0x10.
+**
+** WARNING: This opcode does not reliably distinguish between NULL and REAL
+** when P1>=0. If the database contains a NaN value, this opcode will think
+** that the datatype is REAL when it should be NULL. When P1<0 and the value
+** is already stored in register P3, then this opcode does reliably
+** distinguish between NULL and REAL. The problem only arises then P1>=0.
**
** Take the jump to address P2 if and only if the datatype of the
** value determined by P1 and P3 corresponds to one of the bits in the
** P5 bitmask.
**
@@ -4964,10 +4969,11 @@
#endif
VdbeBranchTaken(0,3);
break;
}
nStep--;
+ pC->cacheStatus = CACHE_STALE;
rc = sqlite3BtreeNext(pC->uc.pCursor, 0);
if( rc ){
if( rc==SQLITE_DONE ){
rc = SQLITE_OK;
goto seekscan_search_fail;
Index: src/vdbeapi.c
==================================================================
--- src/vdbeapi.c
+++ src/vdbeapi.c
@@ -269,11 +269,11 @@
SQLITE_NULL, /* 0x1d (not possible) */
SQLITE_INTEGER, /* 0x1e (not possible) */
SQLITE_NULL, /* 0x1f (not possible) */
SQLITE_FLOAT, /* 0x20 INTREAL */
SQLITE_NULL, /* 0x21 (not possible) */
- SQLITE_TEXT, /* 0x22 INTREAL + TEXT */
+ SQLITE_FLOAT, /* 0x22 INTREAL + TEXT */
SQLITE_NULL, /* 0x23 (not possible) */
SQLITE_FLOAT, /* 0x24 (not possible) */
SQLITE_NULL, /* 0x25 (not possible) */
SQLITE_FLOAT, /* 0x26 (not possible) */
SQLITE_NULL, /* 0x27 (not possible) */
@@ -2136,19 +2136,28 @@
int iScanStatusOp, /* Which metric to return */
int flags,
void *pOut /* OUT: Write the answer here */
){
Vdbe *p = (Vdbe*)pStmt;
+ VdbeOp *aOp = p->aOp;
+ int nOp = p->nOp;
ScanStatus *pScan;
int idx;
+
+ if( p->pFrame ){
+ VdbeFrame *pFrame;
+ for(pFrame=p->pFrame; pFrame->pParent; pFrame=pFrame->pParent);
+ aOp = pFrame->aOp;
+ nOp = pFrame->nOp;
+ }
if( iScan<0 ){
int ii;
if( iScanStatusOp==SQLITE_SCANSTAT_NCYCLE ){
i64 res = 0;
- for(ii=0; ii<p->nOp; ii++){
- res += p->aOp[ii].nCycle;
+ for(ii=0; ii<nOp; ii++){
+ res += aOp[ii].nCycle;
}
*(i64*)pOut = res;
return 0;
}
return 1;
@@ -2170,19 +2179,19 @@
if( idx>=p->nScan ) return 1;
switch( iScanStatusOp ){
case SQLITE_SCANSTAT_NLOOP: {
if( pScan->addrLoop>0 ){
- *(sqlite3_int64*)pOut = p->aOp[pScan->addrLoop].nExec;
+ *(sqlite3_int64*)pOut = aOp[pScan->addrLoop].nExec;
}else{
*(sqlite3_int64*)pOut = -1;
}
break;
}
case SQLITE_SCANSTAT_NVISIT: {
if( pScan->addrVisit>0 ){
- *(sqlite3_int64*)pOut = p->aOp[pScan->addrVisit].nExec;
+ *(sqlite3_int64*)pOut = aOp[pScan->addrVisit].nExec;
}else{
*(sqlite3_int64*)pOut = -1;
}
break;
}
@@ -2200,27 +2209,27 @@
*(const char**)pOut = pScan->zName;
break;
}
case SQLITE_SCANSTAT_EXPLAIN: {
if( pScan->addrExplain ){
- *(const char**)pOut = p->aOp[ pScan->addrExplain ].p4.z;
+ *(const char**)pOut = aOp[ pScan->addrExplain ].p4.z;
}else{
*(const char**)pOut = 0;
}
break;
}
case SQLITE_SCANSTAT_SELECTID: {
if( pScan->addrExplain ){
- *(int*)pOut = p->aOp[ pScan->addrExplain ].p1;
+ *(int*)pOut = aOp[ pScan->addrExplain ].p1;
}else{
*(int*)pOut = -1;
}
break;
}
case SQLITE_SCANSTAT_PARENTID: {
if( pScan->addrExplain ){
- *(int*)pOut = p->aOp[ pScan->addrExplain ].p2;
+ *(int*)pOut = aOp[ pScan->addrExplain ].p2;
}else{
*(int*)pOut = -1;
}
break;
}
@@ -2234,22 +2243,22 @@
int iIns = pScan->aAddrRange[ii];
int iEnd = pScan->aAddrRange[ii+1];
if( iIns==0 ) break;
if( iIns>0 ){
while( iIns<=iEnd ){
- res += p->aOp[iIns].nCycle;
+ res += aOp[iIns].nCycle;
iIns++;
}
}else{
int iOp;
- for(iOp=0; iOp<p->nOp; iOp++){
- Op *pOp = &p->aOp[iOp];
+ for(iOp=0; iOp<nOp; iOp++){
+ Op *pOp = &aOp[iOp];
if( pOp->p1!=iEnd ) continue;
if( (sqlite3OpcodeProperty[pOp->opcode] & OPFLG_NCYCLE)==0 ){
continue;
}
- res += p->aOp[iOp].nCycle;
+ res += aOp[iOp].nCycle;
}
}
}
}
*(i64*)pOut = res;
Index: src/vdbeaux.c
==================================================================
--- src/vdbeaux.c
+++ src/vdbeaux.c
@@ -3318,10 +3318,12 @@
db->nDeferredCons = 0;
db->nDeferredImmCons = 0;
db->flags &= ~(u64)SQLITE_DeferFKs;
sqlite3CommitInternalChanges(db);
}
+ }else if( p->rc==SQLITE_SCHEMA && db->nVdbeActive>1 ){
+ p->nChange = 0;
}else{
sqlite3RollbackAll(db, SQLITE_OK);
p->nChange = 0;
}
db->nStatement = 0;
@@ -5327,10 +5329,20 @@
sqlite3 *db = v->db;
i64 iKey2;
PreUpdate preupdate;
const char *zTbl = pTab->zName;
static const u8 fakeSortOrder = 0;
+#ifdef SQLITE_DEBUG
+ int nRealCol;
+ if( pTab->tabFlags & TF_WithoutRowid ){
+ nRealCol = sqlite3PrimaryKeyIndex(pTab)->nColumn;
+ }else if( pTab->tabFlags & TF_HasVirtual ){
+ nRealCol = pTab->nNVCol;
+ }else{
+ nRealCol = pTab->nCol;
+ }
+#endif
assert( db->pPreUpdate==0 );
memset(&preupdate, 0, sizeof(PreUpdate));
if( HasRowid(pTab)==0 ){
iKey1 = iKey2 = 0;
@@ -5343,12 +5355,12 @@
}
}
assert( pCsr!=0 );
assert( pCsr->eCurType==CURTYPE_BTREE );
- assert( pCsr->nField==pTab->nCol
- || (pCsr->nField==pTab->nCol+1 && op==SQLITE_DELETE && iReg==-1)
+ assert( pCsr->nField==nRealCol
+ || (pCsr->nField==nRealCol+1 && op==SQLITE_DELETE && iReg==-1)
);
preupdate.v = v;
preupdate.pCsr = pCsr;
preupdate.op = op;
Index: src/vdbemem.c
==================================================================
--- src/vdbemem.c
+++ src/vdbemem.c
@@ -1481,12 +1481,15 @@
assert( ExprUseXList(p) );
pList = p->x.pList;
if( pList ) nVal = pList->nExpr;
assert( !ExprHasProperty(p, EP_IntValue) );
pFunc = sqlite3FindFunction(db, p->u.zToken, nVal, enc, 0);
+#ifdef SQLITE_ENABLE_UNKNOWN_SQL_FUNCTION
+ if( pFunc==0 ) return SQLITE_OK;
+#endif
assert( pFunc );
- if( (pFunc->funcFlags & (SQLITE_FUNC_CONSTANT|SQLITE_FUNC_SLOCHNG))==0
+ if( (pFunc->funcFlags & (SQLITE_FUNC_CONSTANT|SQLITE_FUNC_SLOCHNG))==0
|| (pFunc->funcFlags & SQLITE_FUNC_NEEDCOLL)
){
return SQLITE_OK;
}
@@ -1517,20 +1520,15 @@
rc = ctx.isError;
sqlite3ErrorMsg(pCtx->pParse, "%s", sqlite3_value_text(pVal));
}else{
sqlite3ValueApplyAffinity(pVal, aff, SQLITE_UTF8);
assert( rc==SQLITE_OK );
- assert( enc==pVal->enc
- || (pVal->flags & MEM_Str)==0
- || db->mallocFailed );
-#if 0 /* Not reachable except after a prior failure */
rc = sqlite3VdbeChangeEncoding(pVal, enc);
- if( rc==SQLITE_OK && sqlite3VdbeMemTooBig(pVal) ){
+ if( NEVER(rc==SQLITE_OK && sqlite3VdbeMemTooBig(pVal)) ){
rc = SQLITE_TOOBIG;
pCtx->pParse->nErr++;
}
-#endif
}
value_from_function_out:
if( rc!=SQLITE_OK ){
pVal = 0;
@@ -1590,10 +1588,11 @@
assert( !ExprHasProperty(pExpr, EP_IntValue) );
aff = sqlite3AffinityType(pExpr->u.zToken,0);
rc = valueFromExpr(db, pExpr->pLeft, enc, aff, ppVal, pCtx);
testcase( rc!=SQLITE_OK );
if( *ppVal ){
+ rc = ExpandBlob(*ppVal);
sqlite3VdbeMemCast(*ppVal, aff, enc);
sqlite3ValueApplyAffinity(*ppVal, affinity, enc);
}
return rc;
}
Index: src/vtab.c
==================================================================
--- src/vtab.c
+++ src/vtab.c
@@ -608,11 +608,13 @@
sCtx.pTab = pTab;
sCtx.pVTable = pVTable;
sCtx.pPrior = db->pVtabCtx;
sCtx.bDeclared = 0;
db->pVtabCtx = &sCtx;
+ pTab->nTabRef++;
rc = xConstruct(db, pMod->pAux, nArg, azArg, &pVTable->pVtab, &zErr);
+ sqlite3DeleteTable(db, pTab);
db->pVtabCtx = sCtx.pPrior;
if( rc==SQLITE_NOMEM ) sqlite3OomFault(db);
assert( sCtx.pTab==pTab );
if( SQLITE_OK!=rc ){
Index: src/where.c
==================================================================
--- src/where.c
+++ src/where.c
@@ -960,11 +960,15 @@
** original table never needs to be accessed. Automatic indices must
** be a covering index because the index will not be updated if the
** original table changes and the index and table cannot both be used
** if they go out of sync.
*/
- extraCols = pSrc->colUsed & (~idxCols | MASKBIT(BMS-1));
+ if( IsView(pTable) ){
+ extraCols = ALLBITS;
+ }else{
+ extraCols = pSrc->colUsed & (~idxCols | MASKBIT(BMS-1));
+ }
mxBitCol = MIN(BMS-1,pTable->nCol);
testcase( pTable->nCol==BMS-1 );
testcase( pTable->nCol==BMS-2 );
for(i=0; i<mxBitCol; i++){
if( extraCols & MASKBIT(i) ) nKeyCol++;
@@ -1121,10 +1125,14 @@
const WhereTerm *pWCEnd; /* Last WHERE clause term */
Parse *pParse = pWInfo->pParse; /* Parsing context */
Vdbe *v = pParse->pVdbe; /* VDBE under construction */
WhereLoop *pLoop = pLevel->pWLoop; /* The loop being coded */
int iCur; /* Cursor for table getting the filter */
+ IndexedExpr *saved_pIdxEpr; /* saved copy of Parse.pIdxEpr */
+
+ saved_pIdxEpr = pParse->pIdxEpr;
+ pParse->pIdxEpr = 0;
assert( pLoop!=0 );
assert( v!=0 );
assert( pLoop->wsFlags & WHERE_BLOOMFILTER );
@@ -1177,13 +1185,12 @@
Index *pIdx = pLoop->u.btree.pIndex;
int n = pLoop->u.btree.nEq;
int r1 = sqlite3GetTempRange(pParse, n);
int jj;
for(jj=0; jj<n; jj++){
- int iCol = pIdx->aiColumn[jj];
assert( pIdx->pTable==pItem->pTab );
- sqlite3ExprCodeGetColumnOfTable(v, pIdx->pTable, iCur, iCol,r1+jj);
+ sqlite3ExprCodeLoadIndexColumn(pParse, pIdx, iCur, jj, r1+jj);
}
sqlite3VdbeAddOp4Int(v, OP_FilterAdd, pLevel->regFilter, 0, r1, n);
sqlite3ReleaseTempRange(pParse, r1, n);
}
sqlite3VdbeResolveLabel(v, addrCont);
@@ -1210,10 +1217,11 @@
break;
}
}
}while( iLevel < pWInfo->nLevel );
sqlite3VdbeJumpHere(v, addrOnce);
+ pParse->pIdxEpr = saved_pIdxEpr;
}
#ifndef SQLITE_OMIT_VIRTUALTABLE
/*
@@ -1508,10 +1516,11 @@
UNUSED_PARAMETER( pParse );
#endif
assert( pRec!=0 );
assert( pIdx->nSample>0 );
assert( pRec->nField>0 );
+
/* Do a binary search to find the first sample greater than or equal
** to pRec. If pRec contains a single field, the set of samples to search
** is simply the aSample[] array. If the samples in aSample[] contain more
** than one fields, all fields following the first are ignored.
@@ -1553,11 +1562,16 @@
** appears that it should be 1 field in size. However, that would make it
** smaller than sample 1, so the binary search would not work. As a result,
** it is extended to two fields. The duplicates that this creates do not
** cause any problems.
*/
- nField = MIN(pRec->nField, pIdx->nSample);
+ if( !HasRowid(pIdx->pTable) && IsPrimaryKeyIndex(pIdx) ){
+ nField = pIdx->nKeyCol;
+ }else{
+ nField = pIdx->nColumn;
+ }
+ nField = MIN(pRec->nField, nField);
iCol = 0;
iSample = pIdx->nSample * nField;
do{
int iSamp; /* Index in aSample[] of test sample */
int n; /* Number of fields in test sample */
@@ -1989,11 +2003,11 @@
#else
UNUSED_PARAMETER(pParse);
UNUSED_PARAMETER(pBuilder);
assert( pLower || pUpper );
#endif
- assert( pUpper==0 || (pUpper->wtFlags & TERM_VNULL)==0 );
+ assert( pUpper==0 || (pUpper->wtFlags & TERM_VNULL)==0 || pParse->nErr>0 );
nNew = whereRangeAdjust(pLower, nOut);
nNew = whereRangeAdjust(pUpper, nNew);
/* TUNING: If there is both an upper and lower limit and neither limit
** has an application-defined likelihood(), assume the range is
@@ -5281,10 +5295,14 @@
pWInfo->nOBSat = pFrom->isOrdered;
if( pWInfo->wctrlFlags & WHERE_DISTINCTBY ){
if( pFrom->isOrdered==pWInfo->pOrderBy->nExpr ){
pWInfo->eDistinct = WHERE_DISTINCT_ORDERED;
}
+ if( pWInfo->pSelect->pOrderBy
+ && pWInfo->nOBSat > pWInfo->pSelect->pOrderBy->nExpr ){
+ pWInfo->nOBSat = pWInfo->pSelect->pOrderBy->nExpr;
+ }
}else{
pWInfo->revMask = pFrom->revLoop;
if( pWInfo->nOBSat<=0 ){
pWInfo->nOBSat = 0;
if( nLoop>0 ){
@@ -5692,10 +5710,13 @@
p->pExpr = sqlite3ExprDup(pParse->db, pExpr, 0);
p->iDataCur = pTabItem->iCursor;
p->iIdxCur = iIdxCur;
p->iIdxCol = i;
p->bMaybeNullRow = bMaybeNullRow;
+ if( sqlite3IndexAffinityStr(pParse->db, pIdx) ){
+ p->aff = pIdx->zColAff[i];
+ }
#ifdef SQLITE_ENABLE_EXPLAIN_COMMENTS
p->zIdxName = pIdx->zName;
#endif
pParse->pIdxEpr = p;
if( p->pIENext==0 ){
@@ -5949,26 +5970,49 @@
if( pSelect && pSelect->pLimit ){
sqlite3WhereAddLimit(&pWInfo->sWC, pSelect);
}
if( pParse->nErr ) goto whereBeginError;
- /* Special case: WHERE terms that do not refer to any tables in the join
- ** (constant expressions). Evaluate each such term, and jump over all the
- ** generated code if the result is not true.
- **
- ** Do not do this if the expression contains non-deterministic functions
- ** that are not within a sub-select. This is not strictly required, but
- ** preserves SQLite's legacy behaviour in the following two cases:
- **
- ** FROM ... WHERE random()>0; -- eval random() once per row
- ** FROM ... WHERE (SELECT random())>0; -- eval random() once overall
+ /* The False-WHERE-Term-Bypass optimization:
+ **
+ ** If there are WHERE terms that are false, then no rows will be output,
+ ** so skip over all of the code generated here.
+ **
+ ** Conditions:
+ **
+ ** (1) The WHERE term must not refer to any tables in the join.
+ ** (2) The term must not come from an ON clause on the
+ ** right-hand side of a LEFT or FULL JOIN.
+ ** (3) The term must not come from an ON clause, or there must be
+ ** no RIGHT or FULL OUTER joins in pTabList.
+ ** (4) If the expression contains non-deterministic functions
+ ** that are not within a sub-select. This is not required
+ ** for correctness but rather to preserves SQLite's legacy
+ ** behaviour in the following two cases:
+ **
+ ** WHERE random()>0; -- eval random() once per row
+ ** WHERE (SELECT random())>0; -- eval random() just once overall
+ **
+ ** Note that the Where term need not be a constant in order for this
+ ** optimization to apply, though it does need to be constant relative to
+ ** the current subquery (condition 1). The term might include variables
+ ** from outer queries so that the value of the term changes from one
+ ** invocation of the current subquery to the next.
*/
for(ii=0; ii<sWLB.pWC->nBase; ii++){
- WhereTerm *pT = &sWLB.pWC->a[ii];
+ WhereTerm *pT = &sWLB.pWC->a[ii]; /* A term of the WHERE clause */
+ Expr *pX; /* The expression of pT */
if( pT->wtFlags & TERM_VIRTUAL ) continue;
- if( pT->prereqAll==0 && (nTabList==0 || exprIsDeterministic(pT->pExpr)) ){
- sqlite3ExprIfFalse(pParse, pT->pExpr, pWInfo->iBreak, SQLITE_JUMPIFNULL);
+ pX = pT->pExpr;
+ assert( pX!=0 );
+ assert( pT->prereqAll!=0 || !ExprHasProperty(pX, EP_OuterON) );
+ if( pT->prereqAll==0 /* Conditions (1) and (2) */
+ && (nTabList==0 || exprIsDeterministic(pX)) /* Condition (4) */
+ && !(ExprHasProperty(pX, EP_InnerON) /* Condition (3) */
+ && (pTabList->a[0].fg.jointype & JT_LTORJ)!=0 )
+ ){
+ sqlite3ExprIfFalse(pParse, pX, pWInfo->iBreak, SQLITE_JUMPIFNULL);
pT->wtFlags |= TERM_CODED;
}
}
if( wctrlFlags & WHERE_WANT_DISTINCT ){
@@ -6207,11 +6251,11 @@
for(; b; b=b>>1, n++){}
sqlite3VdbeChangeP4(v, -1, SQLITE_INT_TO_PTR(n), P4_INT32);
assert( n<=pTab->nCol );
}
#ifdef SQLITE_ENABLE_CURSOR_HINTS
- if( pLoop->u.btree.pIndex!=0 ){
+ if( pLoop->u.btree.pIndex!=0 && (pTab->tabFlags & TF_WithoutRowid)==0 ){
sqlite3VdbeChangeP5(v, OPFLAG_SEEKEQ|bFordelete);
}else
#endif
{
sqlite3VdbeChangeP5(v, bFordelete);
Index: src/wherecode.c
==================================================================
--- src/wherecode.c
+++ src/wherecode.c
@@ -1923,11 +1923,11 @@
** of entries in the tree, so basing the number of steps to try
** on the estimated number of rows in the btree seems like a good
** guess. */
addrSeekScan = sqlite3VdbeAddOp1(v, OP_SeekScan,
(pIdx->aiRowLogEst[0]+9)/10);
- if( pRangeStart ){
+ if( pRangeStart || pRangeEnd ){
sqlite3VdbeChangeP5(v, 1);
sqlite3VdbeChangeP2(v, addrSeekScan, sqlite3VdbeCurrentAddr(v)+1);
addrSeekScan = 0;
}
VdbeCoverage(v);
@@ -1964,20 +1964,11 @@
*/
nConstraint = nEq;
assert( pLevel->p2==0 );
if( pRangeEnd ){
Expr *pRight = pRangeEnd->pExpr->pRight;
- if( addrSeekScan ){
- /* For a seek-scan that has a range on the lowest term of the index,
- ** we have to make the top of the loop be code that sets the end
- ** condition of the range. Otherwise, the OP_SeekScan might jump
- ** over that initialization, leaving the range-end value set to the
- ** range-start value, resulting in a wrong answer.
- ** See ticket 5981a8c041a3c2f3 (2021-11-02).
- */
- pLevel->p2 = sqlite3VdbeCurrentAddr(v);
- }
+ assert( addrSeekScan==0 );
codeExprOrVector(pParse, pRight, regBase+nEq, nTop);
whereLikeOptimizationStringFixup(v, pLevel, pRangeEnd);
if( (pRangeEnd->wtFlags & TERM_VNULL)==0
&& sqlite3ExprCanBeNull(pRight)
){
Index: src/whereexpr.c
==================================================================
--- src/whereexpr.c
+++ src/whereexpr.c
@@ -1854,11 +1854,11 @@
pColRef->y.pTab = pTab;
pItem->colUsed |= sqlite3ExprColUsed(pColRef);
pRhs = sqlite3PExpr(pParse, TK_UPLUS,
sqlite3ExprDup(pParse->db, pArgs->a[j].pExpr, 0), 0);
pTerm = sqlite3PExpr(pParse, TK_EQ, pColRef, pRhs);
- if( pItem->fg.jointype & (JT_LEFT|JT_LTORJ) ){
+ if( pItem->fg.jointype & (JT_LEFT|JT_LTORJ|JT_RIGHT) ){
joinType = EP_OuterON;
}else{
joinType = EP_InnerON;
}
sqlite3SetJoinExpr(pTerm, pItem->iCursor, joinType);
Index: src/window.c
==================================================================
--- src/window.c
+++ src/window.c
@@ -783,10 +783,11 @@
}
}
}
/* no break */ deliberate_fall_through
+ case TK_IF_NULL_ROW:
case TK_AGG_FUNCTION:
case TK_COLUMN: {
int iCol = -1;
if( pParse->db->mallocFailed ) return WRC_Abort;
if( p->pSub ){
ADDED test/aggfault.test
Index: test/aggfault.test
==================================================================
--- /dev/null
+++ test/aggfault.test
@@ -0,0 +1,43 @@
+# 2023 March 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.
+#
+#***********************************************************************
+#
+
+set testdir [file dirname $argv0]
+source $testdir/tester.tcl
+set testprefix aggfault
+
+
+do_execsql_test 1 {
+ CREATE TABLE t1(x);
+ CREATE INDEX t1x ON t1(x, x=0);
+}
+faultsim_save_and_close
+
+do_faultsim_test 2 -faults oom* -prep {
+ faultsim_restore_and_reopen
+ execsql { SELECT * FROM sqlite_schema }
+} -body {
+ execsql {
+ SELECT * FROM t1 AS a1 WHERE (
+ SELECT count(x AND 0=a1.x) FROM t1 GROUP BY abs(1)
+ ) AND x=(
+ SELECT * FROM t1 AS a1
+ WHERE (SELECT count(x IS 1 AND a1.x=0)
+ FROM t1
+ GROUP BY abs(1)) AND x=0
+ );
+ }
+} -test {
+ faultsim_test_result {0 {}}
+}
+
+
+finish_test
Index: test/altertab.test
==================================================================
--- test/altertab.test
+++ test/altertab.test
@@ -978,7 +978,25 @@
CREATE TRIGGER r1 BEFORE INSERT ON t1 BEGIN
UPDATE t1 SET x=x FROM (SELECT*);
END;
ALTER TABLE t1 RENAME TO x;
} {1 {error in trigger r1: no tables specified}}
+
+# 2023-04-13 https://sqlite.org/forum/forumpost/ff3840145a
+#
+reset_db
+do_execsql_test 33.0 {
+ CREATE TABLE t1(a TEXT);
+ INSERT INTO t1(a) VALUES('abc'),('def'),(NULL);
+ CREATE TABLE t2(b TEXT);
+ CREATE TRIGGER r3 AFTER INSERT ON t1 BEGIN
+ UPDATE t2 SET (b,a)=(SELECT 1) FROM t1 JOIN t2 ON (SELECT * FROM (SELECT a));
+ END;
+}
+do_catchsql_test 33.1 {
+ ALTER TABLE t1 RENAME COLUMN a TO b;
+} {1 {error in trigger r3 after rename: no such column: a}}
+do_execsql_test 33.2 {
+ SELECT quote(a) FROM t1 ORDER BY +a;
+} {NULL 'abc' 'def'}
finish_test
Index: test/analyze3.test
==================================================================
--- test/analyze3.test
+++ test/analyze3.test
@@ -733,7 +733,20 @@
SELECT * FROM sqlite_stat1;
INSERT INTO sqlite_stat1(tbl,idx,stat) VALUES('t1','t1a','12000');
INSERT INTO sqlite_stat1(tbl,idx,stat) VALUES('t1','t1a','12000');
ANALYZE sqlite_master;
}
+
+# 2023-04-22 https://sqlite.org/forum/info/6c118daad0f1f5ef
+# Case differences in the sqlite_stat4.idx field should not matter.
+#
+reset_db
+do_execsql_test 8.0 {
+ CREATE TABLE t1(a PRIMARY KEY, v) WITHOUT ROWID;
+ ANALYZE sqlite_schema;
+ INSERT INTO sqlite_stat1 VALUES('t1','t1','1 1');
+ INSERT INTO sqlite_stat4 VALUES('t1','t1','1','0','0',X'021b76657273696f6e');
+ INSERT INTO sqlite_stat4 VALUES('T1','T1','1','0','0',X'021b76657273696f6e');
+ ANALYZE sqlite_schema;
+} {}
finish_test
Index: test/analyzeE.test
==================================================================
--- test/analyzeE.test
+++ test/analyzeE.test
@@ -236,7 +236,59 @@
} {/SEARCH t1 USING INDEX t1ca/}
do_execsql_test analyzeE-4.11 {
EXPLAIN QUERY PLAN
SELECT * FROM t1 WHERE a<1900 AND c=123
} {/SCAN t1/}
+
+# 2023-03-23 https://sqlite.org/forum/forumpost/dc4854437b
+#
+reset_db
+do_execsql_test analyzeE-5.0 {
+ PRAGMA encoding = 'UTF-16';
+ CREATE TABLE t0 (c1 TEXT);
+ INSERT INTO t0 VALUES ('');
+ CREATE INDEX i0 ON t0(c1);
+ ANALYZE;
+ SELECT * FROM t0 WHERE t0.c1 BETWEEN '' AND (ABS(''));
+} {{}}
+
+# 2023-03-24 https://sqlite.org/forum/forumpost/bc39e531e5
+#
+reset_db
+do_execsql_test analyzeE-6.0 {
+ CREATE TABLE t1(x);
+ CREATE INDEX i1 ON t1(x,x,x,x,x||2);
+ CREATE INDEX i2 ON t1(1<2);
+ WITH RECURSIVE c(x) AS (VALUES(1) UNION ALL SELECT x+1 FROM c WHERE x<1000)
+ INSERT INTO t1(x) SELECT x FROM c;
+ ANALYZE;
+} {}
+do_execsql_test analyzeE-6.1 {
+ SELECT count(*)>1 FROM sqlite_stat4 WHERE idx='i2' AND neq='1000 1';
+} 1
+do_execsql_test analyzeE-6.2 {
+ SELECT count(*) FROM sqlite_stat4 WHERE idx='i2' AND neq<>'1000 1';
+} 0
+do_execsql_test analyzeE-6.3 {
+ SELECT count(*)>1 FROM sqlite_stat4 WHERE idx='i1' AND neq='1 1 1 1 1 1';
+} 1
+do_execsql_test analyzeE-6.4 {
+ SELECT count(*) FROM sqlite_stat4 WHERE idx='i1' AND neq<>'1 1 1 1 1 1';
+} 0
+
+# 2023-03-25 https://sqlite.org/forum/forumpost/5275207102
+# Correctly expand zeroblobs while processing STAT4 information
+# during query planning.
+#
+reset_db
+do_execsql_test analyzeE-7.0 {
+ CREATE TABLE t1(a TEXT COLLATE binary);
+ CREATE INDEX t1x ON t1(a);
+ INSERT INTO t1(a) VALUES(0),('apple'),(NULL),(''),('banana');
+ ANALYZE;
+ SELECT format('(%s)',a) FROM t1 WHERE t1.a > CAST(zeroblob(5) AS TEXT);
+} {(0) (apple) (banana)}
+do_execsql_test analyzeE-7.1 {
+ SELECT format('(%s)',a) FROM t1 WHERE t1.a <= CAST(zeroblob(5) AS TEXT);
+} {()}
finish_test
Index: test/backup.test
==================================================================
--- test/backup.test
+++ test/backup.test
@@ -975,7 +975,30 @@
sqlite3 db1 :memory:
sqlite3 db2 :memory:
sqlite3_backup B db1 main db2 temp
B finish
} {SQLITE_OK}
+db1 close
+db2 close
+
+#-------------------------------------------------------------------------
+do_test backup-12.1 {
+ sqlite3 db1 :memory:
+ sqlite3 db2 :memory:
+ db1 eval {
+ PRAGMA page_size = 8192;
+ CREATE TABLE t1(x);
+ }
+ db2 eval {
+ PRAGMA page_size = 1024;
+ CREATE TABLE t2(x);
+ }
+
+ sqlite3_backup B db1 main db2 temp
+ B step 100
+ B finish
+} {SQLITE_READONLY}
+
+
+
finish_test
Index: test/bloom1.test
==================================================================
--- test/bloom1.test
+++ test/bloom1.test
@@ -97,8 +97,80 @@
| | `--SEARCH objs USING COVERING INDEX objs_cspo (o=? AND p=?)
| `--RECURSIVE STEP
| |--SCAN transit
| `--SEARCH objs USING COVERING INDEX objs_cspo (o=? AND p=?)
`--SCAN transit
-}
+}
+
+# 2023-02-28
+# https://sqlite.org/forum/forumpost/0846211821
+#
+# Bloom filter gives an incorrect result if the collating sequence is
+# anything other than binary.
+#
+reset_db
+do_execsql_test 3.1 {
+ CREATE TABLE t0(x TEXT COLLATE rtrim);
+ INSERT INTO t0(x) VALUES ('a'), ('b'), ('c');
+ CREATE VIEW v0(y) AS SELECT DISTINCT x FROM t0;
+ SELECT count(*) FROM t0, v0 WHERE x='b ';
+} 3
+do_eqp_test 3.2 {
+ SELECT count(*) FROM t0, v0 WHERE x='b ';
+} {
+ QUERY PLAN
+ |--CO-ROUTINE v0
+ | |--SCAN t0
+ | `--USE TEMP B-TREE FOR DISTINCT
+ |--SCAN v0
+ `--SEARCH t0 USING AUTOMATIC PARTIAL COVERING INDEX (x=?)
+}
+# ^^^^^--- The key feature in the previous result is that no Bloom filter
+# is used. In the following, a Bloom filter is used because the data type
+# is INT instead of TEXT.
+do_execsql_test 3.3 {
+ CREATE TABLE t1(x INT COLLATE rtrim);
+ INSERT INTO t1(x) VALUES ('a'), ('b'), ('c');
+ CREATE VIEW v1(y) AS SELECT DISTINCT x FROM t1;
+ SELECT count(*) FROM t1, v1 WHERE x='b ';
+} 3
+# 2023-03-14
+# https://sqlite.org/forum/forumpost/d47a0e8e3a
+# https://sqlite.org/forum/forumpost/2e427099d5
+#
+# Both reports are for the same problem - using a Bloom filter on an
+# expression index can cause issues.
+#
+reset_db
+do_execsql_test 4.1 {
+ CREATE TABLE t1(x TEXT, y INT, z TEXT);
+ INSERT INTO t1(rowid,x,y,z) VALUES(12,'aa','bb','aa');
+ CREATE INDEX i1x ON t1(1 IS true,z);
+ CREATE TABLE t0(x TEXT);
+ INSERT INTO t0(rowid,x) VALUES(4,'aa');
+ ANALYZE sqlite_schema;
+ INSERT INTO sqlite_stat1 VALUES('t0',NULL,'20');
+ INSERT INTO sqlite_stat1 VALUES('t1','i1x','18 18 2');
+ ANALYZE sqlite_schema;
+}
+do_execsql_test 4.2 {
+ SELECT * FROM t0 NATURAL JOIN t1 WHERE z=t1.x;
+} {aa bb aa}
+do_execsql_test 4.3 {
+ DROP TABLE t0;
+ CREATE TABLE t0(a TEXT);
+ INSERT INTO t0 VALUES ('xyz');
+ CREATE INDEX t0x ON t0(a IS FALSE) WHERE false;
+ DROP TABLE t1;
+ CREATE TABLE t1(b INT);
+ INSERT INTO t1 VALUES('aaa'),('bbb'),('ccc'),('ddd'),(NULL);
+ CREATE TABLE t2(c REAL);
+ INSERT INTO t2 VALUES(7);
+ ANALYZE;
+ CREATE INDEX t2x ON t2(true IN ());
+}
+do_execsql_test 4.4 {
+ SELECT * FROM t0 LEFT JOIN t1 LEFT JOIN t2 ON (b NOTNULL)==(c IN ()) WHERE c;
+} {xyz {} 7.0}
+
finish_test
Index: test/corruptI.test
==================================================================
--- test/corruptI.test
+++ test/corruptI.test
@@ -121,22 +121,17 @@
}
set root [db one {SELECT rootpage FROM sqlite_master}]
set offset [expr ($root-1) * 65536]
-ifcapable oversize_cell_check {
- set res {1 {database disk image is malformed}}
-} else {
- set res {0 {}}
-}
do_test 4.1 {
db close
hexio_write test.db [expr $offset + 8 + 2] 0000
hexio_write test.db [expr $offset + 5] 0000
sqlite3 db test.db
catchsql { DELETE FROM t1 WHERE a=0 }
-} $res
+} {1 {database disk image is malformed}}
#-------------------------------------------------------------------------
# Database properties:
#
Index: test/corruptL.test
==================================================================
--- test/corruptL.test
+++ test/corruptL.test
@@ -1267,15 +1267,18 @@
| 4080: 0e 01 04 11 13 17 63 31 63 31 63 37 20 31 00 00 ......c1c1c7 1..
| end crash-3afa1ca9e9c1bd.db
}]} {}
extra_schema_checks 0
-do_execsql_test 15.1 {
+do_catchsql_test 15.1 {
PRAGMA cell_size_check = 0;
UPDATE c1 SET c= NOT EXISTS(SELECT 1 FROM c1 ORDER BY (SELECT 1 FROM c1 ORDER BY a)) +10 WHERE d BETWEEN 4 AND 7;
-} {}
+} {1 {database disk image is malformed}}
extra_schema_checks 1
+do_execsql_test 15.2 {
+ PRAGMA integrity_check;
+} {/in database main/}
#-------------------------------------------------------------------------
reset_db
do_execsql_test 16.0 {
CREATE TABLE t1(w, x, y, z, UNIQUE(w, x), UNIQUE(y, z));
Index: test/countofview.test
==================================================================
--- test/countofview.test
+++ test/countofview.test
@@ -49,8 +49,59 @@
} {4}
do_execsql_test 2.1 {
SELECT count(*) FROM v1 GROUP BY y;
} {3 3}
+# 2023-03-01 dbsqlfuzz ef8623915d843b150c159166ee4548c78cc6895a
+# count-of-view should not apply to CTEs.
+#
+ifcapable progress {
+ proc progress_stop args {return 1}
+ db progress 1000 progress_stop
+ do_catchsql_test 3.1 {
+ WITH RECURSIVE c(x) AS (VALUES(1) UNION ALL SELECT x+1 FROM c)
+ SELECT count(*) FROM c;
+ } {1 interrupted}
+}
+
+# 2023-03-07 dbsqlfuzz 23d782160b71c3f8f535ccb2da313dfc8eb8c631
+#
+do_execsql_test 4.1 {
+ DROP TABLE t1;
+ DROP TABLE t2;
+ DROP TABLE t3;
+ CREATE TABLE t1(a INTEGER PRIMARY KEY, b TEXT);
+ INSERT INTO t1 VALUES(4,'four');
+ CREATE TABLE t2(c INTEGER PRIMARY KEY, d TEXT);
+ CREATE VIEW t3 AS SELECT a, b FROM t1 UNION ALL SELECT c, d FROM t2;
+ SELECT count(*) FROM t3 ORDER BY sum(a);
+} 1
+
+# 2023-03-31 dbsqlfuzz 6a107e3055bd22afab31cfddabc2d9d54fcbaf69
+# Having clauses should disqualify count-of-view
+#
+reset_db
+do_execsql_test 5.1 {
+ CREATE TABLE t1(a INTEGER PRIMARY KEY, b TEXT);
+ INSERT INTO t1 VALUES(1,'one'),(4,'four');
+ CREATE TABLE t2(c INTEGER PRIMARY KEY, d TEXT);
+ INSERT INTO t2 VALUES(2,'two'),(5,'five');
+ CREATE VIEW t3 AS SELECT a, b FROM t1 UNION ALL SELECT c, d FROM t2;
+ SELECT count(*) FROM t3 HAVING count(*)>0;
+} 4
+do_execsql_test 5.2 {
+ SELECT count(*) FROM t3 HAVING count(*)>5;
+} {}
+do_execsql_test 5.3 {
+ SELECT count(*) FROM t3 HAVING max(b)>'mmm';
+} 4
+do_execsql_test 5.4 {
+ SELECT count(*) FROM t3 HAVING min(b)>'mmm';
+} {}
+do_execsql_test 5.5 {
+ SELECT count(*) FROM (
+ SELECT a, max(b) FROM t1 HAVING a<100 UNION ALL SELECT c, d FROM t2
+ )
+} 3
finish_test
Index: test/cursorhint.test
==================================================================
--- test/cursorhint.test
+++ test/cursorhint.test
@@ -156,7 +156,16 @@
do_test 4.6desc {
p4_of_opcode db CursorHint {
SELECT rowid FROM t1 WHERE b=22 AND c>=10 AND c<=20 ORDER BY b,c DESC;
}
} {AND(AND(EQ(c0,22),GE(c1,10)),LE(c1,20))}
+
+# 2023-03-24 https://sqlite.org/forum/forumpost/591006b1cc
+#
+reset_db
+do_execsql_test 5.0 {
+ CREATE TABLE t1(x TEXT PRIMARY KEY) WITHOUT ROWID;
+ CREATE VIEW t2 AS SELECT 0 FROM t1 WHERE x>='a' OR x='1';
+ SELECT * FROM t2 RIGHT JOIN t1 ON true;
+}
finish_test
Index: test/delete.test
==================================================================
--- test/delete.test
+++ test/delete.test
@@ -413,7 +413,31 @@
DELETE FROM t11 AS xyz
WHERE EXISTS(SELECT 1 FROM t11 WHERE t11.a>xyz.a AND t11.b<=xyz.b);
SELECT * FROM t11;
} {6 2 12 4 18 6 19 23 20 40}
+
+# 2023-03-15
+# https://sqlite.org/forum/forumpost/e61252062c9d286d
+#
+# When the WHERE clause of a DELETE statement contains a subquery
+# which uses the table that is being deleted from and there is a
+# short-circuit operator of some kind in the WHERE clause such that
+# the subquery might not run right away, then the subquery might
+# run after one or more rows have been deleted, which can change
+# the result of the subquery, and result in the wrong answer.
+#
+# Similar problem for UPDATE tested by update-21.4
+# https://sqlite.org/forum/forumpost/0007d1fdb1
+#
+reset_db
+do_execsql_test delete-12.0 {
+ CREATE TABLE t0(vkey INTEGER, pkey INTEGER,c1 INTEGER);
+ INSERT INTO t0 VALUES(2,1,-20),(2,2,NULL),(2,3,0),(8,4,95);
+ DELETE FROM t0 WHERE NOT (
+ (t0.vkey <= t0.c1) AND
+ (t0.vkey <> (SELECT vkey FROM t0 ORDER BY vkey LIMIT 1 OFFSET 2))
+ );
+ SELECT * FROM t0;
+} {8 4 95}
finish_test
Index: test/distinct.test
==================================================================
--- test/distinct.test
+++ test/distinct.test
@@ -346,7 +346,63 @@
a a a b a c
b a b b b c
}
}
+# 2023-03-16
+# https://sqlite.org/forum/forumpost/16ce2bb7a639e29b
+# ticket c36cdb4afd504dc1
+# ticket 4051a7f931d9ba24
+# ticket d6fd512f50513ab7
+#
+do_execsql_test 10.1 {
+ SELECT DISTINCT
+ 1, 1, 1, 1, 1, 1, 1, 1, 1, 1,
+ 1, 1, 1, 1, 1, 1, 1, 1, 1, 1,
+ 1, 1, 1, 1, 1, 1, 1, 1, 1, 1,
+ 1, 1, 1, 1, 1, 1, 1, 1, 1, 1,
+ 1, 1, 1, 1, 1, 1, 1, 1, 1, 1,
+ 1, 1, 1, 1, 1, 1, 1, 1, 1, 1,
+ 1, 1, 1, 1, 1
+ ORDER BY
+ 'x','x','x','x','x','x','x','x','x','x',
+ 'x','x','x','x','x','x','x','x','x','x',
+ 'x','x','x','x','x','x','x','x','x','x',
+ 'x','x','x','x','x','x','x','x','x','x',
+ 'x','x','x','x','x','x','x','x','x','x',
+ 'x','x','x','x','x','x','x','x','x','x',
+ 'x','x','x','x';
+} {1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1}
+do_execsql_test 10.2 {
+ EXPLAIN
+ SELECT DISTINCT
+ 1, 1, 1, 1, 1, 1, 1, 1, 1, 1,
+ 1, 1, 1, 1, 1, 1, 1, 1, 1, 1,
+ 1, 1, 1, 1, 1, 1, 1, 1, 1, 1,
+ 1, 1, 1, 1, 1, 1, 1, 1, 1, 1,
+ 1, 1, 1, 1, 1, 1, 1, 1, 1, 1,
+ 1, 1, 1, 1, 1, 1, 1, 1, 1, 1,
+ 1, 1, 1, 1, 1
+ ORDER BY
+ 'x','x','x','x','x','x','x','x','x','x',
+ 'x','x','x','x','x','x','x','x','x','x',
+ 'x','x','x','x','x','x','x','x','x','x',
+ 'x','x','x','x','x','x','x','x','x','x',
+ 'x','x','x','x','x','x','x','x','x','x',
+ 'x','x','x','x','x','x','x','x','x','x',
+ 'x','x','x','x';
+} {/0 Init 0 /}
+do_execsql_test 10.3 {
+ EXPLAIN CREATE TABLE t2 AS SELECT DISTINCT ':memory:', 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7 ORDER BY '%J%j%w%s', 'unixepoch', 'unixepoch', 'unixepoch', 'unixepoch', 'unixepoch', 'unixepoch', 'unixepoch', 'unixepoch', '%J%j%w%s', 'unixepoch', 'unixepoch', 'unixepoch', 'unixepoch', 'unixepoch', 'unixepoch', 'unixepoch', 'unixepoch', 'unixepoch', 'unixepoch', 'unixepoch', 'unixepoch', 'unixepoch', 'unixepoch', 'unixepoch', 'unixepoch', 'unixepoch', 'unixepoch', 'unixepoch', 'unixepoch', 'unixepoch', 'unixepoch', 'unixepoch', 'unixepoch', 'unixepoch', 'unixepoch', 'unixepoch', 'unixepoch', 'unixepoch', 'unixepoch', 'unixepoch', 'unixepoch', 'unixepoch', 'unixepoch', 'unixepoch', 'unixepoch', 'unixepoch', 'unixepoch', 'unixepoch', 'unixepoch', 'unixepoch', 'unixepoch', 'unixepoch', 'unixepoch', 'unixepoch', 'unixepoch', 'unixepoch', 'unixepoch', 'unixepoch', 'unixepoch', 42e-300, 'unixepoch', 'unixepoch', 'unixepoch' LIMIT 0xda;
+} {/0 Init 0/}
+do_execsql_test 10.4 {
+ DROP TABLE IF EXISTS t0;
+ CREATE TABLE t0 AS SELECT DISTINCT 0xda, 'lit0', 'lit0', 'lit0', 'lit0', 'lit0', 'lit0', 'lit0', 'lit0', 'lit0', 'lit0', 'lit0', 'lit0', 'lit0', 'lit0', 'lit0', 'lit0', 'lit0', 'lit0', 'lit0', 'lit0', 'lit0', 'lit0', 'lit0', 'lit0', 'lit0', 'lit0', 'lit0', 'lit0', 'lit0', 'lit0', 'lit0', 'lit0', 'lit0', 'lit0', 'lit0', 'lit0', 'lit0', 'lit0', 'lit0', 'lit0', 'lit0', 'lit0', 'lit0', 'lit0', 0xda-0xda-42e-300, 'lit0', 'lit0', 'lit0', 'lit0', 'lit0', 'lit0', 'lit0', 'lit0', 'lit0', 'lit0', 'lit0', 'lit0', 'lit0', 'lit0', 'lit0', 'lit0', 'lit0', 'lit0', 'lit0' ORDER BY '%%', '%%', '%%', '%%', '%%', '%%', '%%', '%%', '%%', '%%', '%%', '%%', '%%', '%%', '%%', '%Y-%m-%d', '%%', '%%', '%%', '%%', '%%', '%%', '%%', '%%', '%%', '%%', 'lit0', '%%', '%%', '%%', '%%', '%%', '%%', '%%', '%%', '%%', '%%', '%%', '%%', '%%', 'auto', '%%', '%%', '%%', '%%', '%%', '%%', '%%', '%%', '%%', '%%', '%%', ':memory:', '%%', '%%', '%%', '%%', '%%', '%%', '%%', '%%', '%%', '%%', '';
+ SELECT count(*) FROM t0;
+} {1}
+do_execsql_test 10.5 {
+ DROP TABLE IF EXISTS t2;
+ CREATE TABLE t2 AS SELECT DISTINCT ':memory:', 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 0.0*7/0, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7 ORDER BY '%J%j%w%s', 'unixepoch', 'unixepoch', 'unixepoch', 'unixepoch', 'unixepoch', 'unixepoch', 'unixepoch', 'unixepoch', '%J%j%w%s', 'unixepoch', 'unixepoch', 'unixepoch', 'unixepoch', 'unixepoch', 'unixepoch', 'unixepoch', 'unixepoch', 'unixepoch', 'unixepoch', 'unixepoch', 'unixepoch', 'unixepoch', 'unixepoch', 'unixepoch', 'unixepoch', 'unixepoch', 'unixepoch', 'unixepoch', 'unixepoch', 'unixepoch', 'unixepoch', 'unixepoch', 'unixepoch', 'unixepoch', 'unixepoch', 'unixepoch', 'unixepoch', 'unixepoch', 'unixepoch', 'unixepoch', 'unixepoch', 'unixepoch', 'unixepoch', 'unixepoch', 'unixepoch', 'unixepoch', 'unixepoch', 'unixepoch', 'unixepoch', 'unixepoch', 'unixepoch', 'unixepoch', 'unixepoch', 'unixepoch', 'unixepoch', 'unixepoch', 'unixepoch', 'unixepoch', 'unixepoch', 42e-300, 'unixepoch', 'unixepoch', 'unixepoch' LIMIT 0xda;
+ SELECT count(*) FROM t2;
+} {1}
finish_test
Index: test/distinct2.test
==================================================================
--- test/distinct2.test
+++ test/distinct2.test
@@ -298,7 +298,19 @@
SELECT DISTINCT * FROM t0 WHERE NULL IS c0;
} {
{} 1 {}
{} 1 a
}
+
+#-------------------------------------------------------------------------
+#
+reset_db
+
+do_execsql_test 4010 {
+ CREATE TABLE t1(a, b COLLATE RTRIM);
+ INSERT INTO t1 VALUES(1, ''), (2, ' '), (3, ' ');
+}
+do_execsql_test 4020 {
+ SELECT b FROM t1 UNION SELECT 1;
+} {1 { }}
finish_test
Index: test/fkey1.test
==================================================================
--- test/fkey1.test
+++ test/fkey1.test
@@ -269,7 +269,18 @@
PRAGMA writable_schema=RESET;
} {}
do_catchsql_test 8.3 {
REINDEX;
} {1 {database disk image is malformed}}
+
+# 2023-04-13 https://bugs.chromium.org/p/chromium/issues/detail?id=1405220
+# Avoid double-de-quoting of table names when processing foreign keys.
+#
+reset_db
+do_execsql_test 9.1 {
+ PRAGMA foreign_keys = ON;
+ CREATE TABLE """1"("""2", """3" PRIMARY KEY);
+ CREATE TABLE """4"("""5" REFERENCES """1" ON DELETE RESTRICT);
+ DELETE FROM """1";
+}
finish_test
Index: test/gencol1.test
==================================================================
--- test/gencol1.test
+++ test/gencol1.test
@@ -613,6 +613,60 @@
SELECT * FROM t0 AS x JOIN t0 AS y
WHERE x.b='2'
AND (y.a=2 OR (x.b LIKE '2*' AND y.a=x.b));
} {2 2 2 2}
+
+# 2023-03-02 dbsqlfuzz 65f5eb57f8859344d5f1f33e08c77ee12960ed83
+#
+set typelist {ANY INT REAL BLOB TEXT {}}
+set cnt 0
+foreach t1 $typelist {
+ foreach t2 $typelist {
+ incr cnt
+ db eval "
+ DROP TABLE IF EXISTS t1;
+ CREATE TABLE t1(
+ x $t1,
+ a $t2 AS (x) VIRTUAL,
+ b BLOB AS (x) VIRTUAL
+ );
+ CREATE INDEX x2 ON t1(a);
+ INSERT INTO t1(x) VALUES(NULL),('1'),(2),(3.5),('xyz');
+ "
+ set x1 [lsort [db eval {SELECT typeof(b) FROM t1}]]
+ do_test gencol1-23.1.$cnt {
+ lsort [db eval {SELECT typeof(b) FROM t1 INDEXED BY x2}]
+ } $x1
+ }
+}
+do_execsql_test gencol1-23.2 {
+ DROP TABLE t1;
+ CREATE TABLE t1(
+ x,
+ a INT AS (x) VIRTUAL,
+ b BLOB AS (x) VIRTUAL
+ );
+ CREATE INDEX x2 ON t1(a);
+ INSERT INTO t1(x) VALUES(NULL),('1'),('xyz'),(2),(3.5);
+ SELECT quote(a) FROM t1 INDEXED BY x2;
+} {NULL 1 2 3.5 'xyz'}
+do_execsql_test gencol1-23.3 {
+ EXPLAIN SELECT a FROM t1 INDEXED BY x2;
+} {~/Column 0/}
+# ^^^^^^^^---- verfies that x2 acts like a covering index
+do_execsql_test gencol1-23.4 {
+ EXPLAIN SELECT b FROM t1 INDEXED BY x2;
+} {/Column 0/}
+# ^^^^^^^^^^--- Must reference the original table in this case because
+# of the different datatype on column b.
+
+# 2023-03-07 https://sqlite.org/forum/forumpost/b312e075b5
+#
+do_execsql_test gencol1-23.5 {
+ CREATE TABLE v0(c1 INT, c2 AS (RAISE(IGNORE)));
+}
+do_catchsql_test gencol1-23.6 {
+ SELECT * FROM v0;
+} {1 {RAISE() may only be used within a trigger-program}}
+
finish_test
Index: test/in.test
==================================================================
--- test/in.test
+++ test/in.test
@@ -827,9 +827,37 @@
} {2 200 4 400 6 600}
do_execsql_test in-22.4 {
SELECT * FROM t1 WHERE x IN ((((((SELECT a FROM t2))))));
} {2 200 4 400 6 600}
-
-
+# 2023-04-04 https://sqlite.org/forum/forumpost/dc16ec63d3
+# Faulty assert() statement in the IN optimization.
+#
+do_execsql_test in-23.0 {
+ DROP TABLE IF EXISTS t4;
+ CREATE TABLE t4(a TEXT, b INT);
+ INSERT INTO t4(a,b) VALUES('abc',0),('ABC',1),('def',2);
+ CREATE INDEX t4x ON t4(a, +a COLLATE NOCASE);
+ SELECT a0.a, group_concat(a1.a) AS b
+ FROM t4 AS a0 JOIN t4 AS a1
+ GROUP BY a0.a
+ HAVING (SELECT sum( (a1.a == +a0.a COLLATE NOCASE) IN (SELECT b FROM t4)));
+} {ABC abc,ABC,def abc abc,ABC,def def abc,ABC,def}
+do_execsql_test in-23.0-b {
+ SELECT a0.a, group_concat(a1.a) AS b
+ FROM t4 AS a0 JOIN t4 AS a1
+ GROUP BY a0.a
+ HAVING (SELECT sum( (a1.a GLOB +a0.a COLLATE NOCASE) IN (SELECT b FROM t4)));
+} {ABC abc,ABC,def abc abc,ABC,def def abc,ABC,def}
+#
+# Follow-up forum/forumpost/0713a16a44
+#
+do_execsql_test in-23.1 {
+ CREATE VIEW t5 AS
+ SELECT 1 AS b
+ WHERE (SELECT count(0=NOT+a COLLATE NOCASE IN (SELECT 0))
+ FROM t4
+ GROUP BY a);
+ SELECT * FROM t5;
+} 1
finish_test
Index: test/indexexpr1.test
==================================================================
--- test/indexexpr1.test
+++ test/indexexpr1.test
@@ -589,8 +589,31 @@
do_execsql_test indexexpr1-2140 {
UPDATE t1 SET b=400 WHERE (SELECT 'y') GLOB "y*";
SELECT b FROM t1;
} 400
-
+# 2023-04-18 Forum post https://sqlite.org/forum/forumpost/f34e32d120 from
+# Alexis King.
+#
+# This problem originates at check-in b9190d3da70c4171 (2022-11-25).
+# A similar problem arose on 2023-03-04 at
+# https://sqlite.org/forum/forumpost/a68313d054 and was fixed at
+# check-in e06973876993926f. See the test case tkt-99378-400.
+#
+reset_db
+do_execsql_test indexexpr1-2200 {
+ CREATE TABLE t1(id INTEGER PRIMARY KEY, tag INT);
+ INSERT INTO t1 VALUES (0, 7), (1, 8);
+ CREATE TABLE t2(type INT, t1_id INT, value INT);
+ INSERT INTO t2 VALUES (0, 0, 100), (0, 1, 101);
+ CREATE INDEX t1x ON t1(-tag);
+ SELECT u.tag, v.max_value
+ FROM (SELECT tag FROM t1 GROUP BY -tag) u
+ JOIN (SELECT t1.tag AS "tag", t2.type AS "type",
+ MAX(t2.value) AS "max_value"
+ FROM t1
+ JOIN t2 ON t2.t1_id = t1.id
+ GROUP BY t2.type, t1.tag
+ ) v ON v.type = 0 AND v.tag = u.tag;
+} {7 100 8 101}
finish_test
Index: test/indexexpr2.test
==================================================================
--- test/indexexpr2.test
+++ test/indexexpr2.test
@@ -369,7 +369,60 @@
do_execsql_test 8.5.$tn.2 "
SELECT ($expr) IS TRUE FROM t1 LEFT JOIN t2
" {1 1}
}
+
+# 2023-03-24 https://sqlite.org/forum/forumpost/79cf371080
+#
+reset_db
+do_execsql_test 9.0 {
+ CREATE TABLE t1(a INT, b INT);
+ CREATE INDEX t1x ON t1(a, abs(b));
+ CREATE TABLE t2(c INT, d INT);
+ INSERT INTO t1(a,b) VALUES(4,4),(5,-5),(5,20),(6,6);
+ INSERT INTO t2(c,d) VALUES(100,1),(200,1),(300,2);
+ SELECT *,
+ (SELECT max(c+abs(b)) FROM t2 GROUP BY d ORDER BY d LIMIT 1) AS subq
+ FROM t1 WHERE a=5;
+} {5 -5 205 5 20 220}
+
+# 2023-04-03 https://sqlite.org/forum/forumpost/44270909bb
+# and https://sqlite.org/forum/forumpost/e45108732c which are the
+# same problem, namely the failure to omit the EP_Collate property
+# from an expression node when changing it from TK_COLLATE into
+# TK_AGG_COLUMN because it resolves to an indexed expression.
+#
+reset_db
+do_execsql_test 10.0 {
+ CREATE TABLE t1(a INTEGER PRIMARY KEY, b TEXT);
+ CREATE INDEX t1x ON t1 (b, +b COLLATE NOCASE);
+ INSERT INTO t1(a,b) VALUES(1,'abcde');
+ SELECT * FROM t1 AS a0
+ WHERE (SELECT count(a0.b=+a0.b COLLATE NOCASE IN (b)) FROM t1 GROUP BY 2.5)
+ ORDER BY a0.b;
+} {1 abcde}
+do_execsql_test 10.1 {
+ CREATE TABLE t2(a TEXT);
+ INSERT INTO t2 VALUES('alice'),('bob'),('cindy'),('david');
+ CREATE INDEX t2x ON t2 (+a COLLATE NOCASE);
+ SELECT count(+a COLLATE NOCASE IN (SELECT 1)) AS x
+ FROM t2
+ GROUP BY SUBSTR(0,0);
+} 4
+
+# 2023-04-03 https://sqlite.org/forum/forumpost/409ebc7368
+# When a generated column appears in both an outer and an inner loop
+# (that is to say, the same table is used in both loops) and the
+# generated column is indexed and it is used inside an aggregate function,
+# make sure that the terms resolve to the correct aggregate.
+#
+do_execsql_test 11.0 {
+ CREATE TABLE t3 (a INT, b AS (-a));
+ CREATE INDEX t3x ON t3(b, a);
+ INSERT INTO t3(a) VALUES(44);
+ SELECT * FROM t3 AS a0
+ WHERE (SELECT sum(-a0.a=b) FROM t3 GROUP BY b)
+ GROUP BY b;
+} {44 -44}
finish_test
Index: test/join2.test
==================================================================
--- test/join2.test
+++ test/join2.test
@@ -352,7 +352,80 @@
optimization_control db query-flattener 0
do_execsql_test 9.11 {
SELECT ccc, ccc IS NULL AS ddd FROM t1 LEFT JOIN v2;
} {{} 1}
+# 2023-03-01 https://sqlite.org/forum/forumpost/26387ea7ef
+# When flattening a VIEW which is the RHS of a LEFT JOIN, always put
+# an TK_IF_NULL_ROW operator on all accesses, even TK_COLUMN nodes, since
+# the TK_COLUMN might reference an outer subquery.
+#
+reset_db
+db null NULL
+do_execsql_test 10.1 {
+ CREATE TABLE t1 (x INTEGER);
+ INSERT INTO t1 VALUES(1); -- Some true value
+ CREATE TABLE t2 (z TEXT);
+ INSERT INTO t2 VALUES('some value');
+ CREATE TABLE t3(w TEXT);
+ INSERT INTO t3 VALUES('some other value');
+}
+do_execsql_test 10.2 {
+ SELECT (
+ SELECT 1 FROM t2 LEFT JOIN (SELECT x AS v FROM t3) ON 500=v WHERE (v OR FALSE)
+ ) FROM t1;
+} NULL
+do_execsql_test 10.3 {
+ SELECT (
+ SELECT 1 FROM t2 LEFT JOIN (SELECT x AS v FROM t3) ON 500=v WHERE (v)
+ ) FROM t1;
+} NULL
+optimization_control db all 0
+do_execsql_test 10.4 {
+ SELECT (
+ SELECT 1 FROM t2 LEFT JOIN (SELECT x AS v FROM t3) ON 500=v WHERE (v OR FALSE)
+ ) FROM t1;
+} NULL
+
+# 2023-03-02 https://sqlite.org/forum/forumpost/402f05296d
+#
+# The TK_IF_NULL_ROW expression node must ensure that it does not overwrite
+# the result register of an OP_Once subroutine.
+#
+optimization_control db all 1
+do_execsql_test 11.1 {
+ DROP TABLE t1;
+ DROP TABLE t2;
+ DROP TABLE t3;
+ CREATE TABLE t1(x TEXT, y INTEGER);
+ INSERT INTO t1(x,y) VALUES(NULL,-2),(NULL,1),('0',2);
+ CREATE TABLE t2(z INTEGER);
+ INSERT INTO t2(z) VALUES(2),(-2);
+ CREATE VIEW t3 AS SELECT z, (SELECT count(*) FROM t1) AS w FROM t2;
+ SELECT * FROM t1 LEFT JOIN t3 ON y=z;
+} {NULL -2 -2 3 NULL 1 NULL NULL 0 2 2 3}
+
+# 2023-03-11 https://sqlite.org/forum/forumpost/b405033490fa56d9
+# The fix that test 11.1 above checks also caused a performance regression.
+# This test case verifies that the performance regression has been resolved.
+#
+do_execsql_test 12.1 {
+ DROP TABLE t1;
+ DROP TABLE t2;
+ DROP VIEW t3;
+ CREATE TABLE t1(a INTEGER PRIMARY KEY);
+ WITH RECURSIVE c(n) AS (VALUES(1) UNION ALL SELECT n+1 FROM c WHERE n<100)
+ INSERT INTO t1(a) SELECT n FROM c;
+ CREATE VIEW t2(b) AS SELECT a FROM t1;
+}
+do_vmstep_test 12.2 {
+ SELECT * FROM t1 LEFT JOIN t2 ON a=b LIMIT 10 OFFSET 98;
+} 2000 {99 99 100 100}
+do_eqp_test 12.3 {
+ SELECT * FROM t1 LEFT JOIN t2 ON a=b LIMIT 10 OFFSET 98;
+} {
+ QUERY PLAN
+ |--SCAN t1
+ `--SEARCH t1 USING INTEGER PRIMARY KEY (rowid=?) LEFT-JOIN
+}
finish_test
Index: test/joinH.test
==================================================================
--- test/joinH.test
+++ test/joinH.test
@@ -87,7 +87,37 @@
do_execsql_test 4.4 {
SELECT (d IS NULL) FROM t1 RIGHT JOIN t2 ON (j=33);
} {1}
+#-------------------------------------------------------------------------
+#
+reset_db
+do_execsql_test 5.0 {
+ CREATE TABLE t0(w);
+ CREATE TABLE t1(x);
+ CREATE TABLE t2(y);
+ CREATE TABLE t3(z);
+ INSERT INTO t3 VALUES('t3val');
+}
+
+do_execsql_test 5.1 {
+ SELECT * FROM t1 INNER JOIN t2 ON (0) RIGHT OUTER JOIN t3;
+} {{} {} t3val}
+
+do_execsql_test 5.2 {
+ SELECT * FROM t1 INNER JOIN t2 ON (0) FULL OUTER JOIN t3;
+} {{} {} t3val}
+
+do_execsql_test 5.3 {
+ SELECT * FROM t3 LEFT JOIN t2 ON (0);
+} {t3val {}}
+
+do_execsql_test 5.4 {
+ SELECT * FROM t0 RIGHT JOIN t1 INNER JOIN t2 ON (0) RIGHT JOIN t3
+} {{} {} {} t3val}
+
+do_execsql_test 5.5 {
+ SELECT * FROM t0 RIGHT JOIN t1 INNER JOIN t2 ON (0)
+} {}
finish_test
Index: test/notnull2.test
==================================================================
--- test/notnull2.test
+++ test/notnull2.test
@@ -26,28 +26,10 @@
)
INSERT INTO t1 SELECT i, i FROM x;
INSERT INTO t2 SELECT * FROM t1;
}
-proc do_vmstep_test {tn sql nstep {res {}}} {
- uplevel [list do_execsql_test $tn.0 $sql $res]
-
- set vmstep [db status vmstep]
- if {[string range $nstep 0 0]=="+"} {
- set body "if {$vmstep<$nstep} {
- error \"got $vmstep, expected more than [string range $nstep 1 end]\"
- }"
- } else {
- set body "if {$vmstep>$nstep} {
- error \"got $vmstep, expected less than $nstep\"
- }"
- }
-
- # set name "$tn.vmstep=$vmstep,expect=$nstep"
- set name "$tn.1"
- uplevel [list do_test $name $body {}]
-}
do_vmstep_test 1.1.1 {
SELECT * FROM t1 LEFT JOIN t2 WHERE a=c AND d IS NULL;
} 100 {}
do_vmstep_test 1.1.2 {
Index: test/pragma.test
==================================================================
--- test/pragma.test
+++ test/pragma.test
@@ -2058,6 +2058,19 @@
do_catchsql_test 24.2 {
PRAGMA integrity_check;
} {0 {{database disk image is malformed}}}
}
database_never_corrupt
+
+# 2023-03-27. Register allocation issue in integrity_check discovered
+# by new assert() statements added in [6f8b97f31a4c8552].
+# dbsqlfuzz dc9ab26037cf5ef797d28cd1ae0855ade584216d
+# tag-20230327-1
+#
+reset_db
+do_execsql_test 25.0 {
+ CREATE TABLE t1(a INT, b AS (a*2) NOT NULL);
+ CREATE TEMP TABLE t2(a PRIMARY KEY, b, c UNIQUE) WITHOUT ROWID;
+ CREATE UNIQUE INDEX t2x ON t2(c,b);
+ PRAGMA integrity_check;
+} ok
finish_test
Index: test/returning1.test
==================================================================
--- test/returning1.test
+++ test/returning1.test
@@ -210,17 +210,17 @@
CREATE TRIGGER tr2 INSTEAD OF UPDATE ON t1 BEGIN
INSERT INTO log VALUES('update', new.rowid, new.a, new.b);
END;
}
-do_catchsql_test 10.3 {
+do_catchsql_test 10.3a {
INSERT INTO t1(a, b) VALUES(1234, 5678) RETURNING rowid;
-} {1 {no such column: rowid}}
+} {1 {no such column: new.rowid}}
-do_catchsql_test 10.3 {
+do_catchsql_test 10.3b {
UPDATE t1 SET a='z' WHERE b='y' RETURNING rowid;
-} {1 {no such column: rowid}}
+} {1 {no such column: new.rowid}}
do_execsql_test 10.4 {
SELECT * FROM log;
} {}
@@ -405,7 +405,38 @@
do_execsql_test 17.0 {
CREATE TABLE bug(id INTEGER PRIMARY KEY NOT NULL, x);
INSERT INTO bug(id,x) VALUES(20, NULL);
UPDATE bug SET x=NULL WHERE id = 20 RETURNING quote(x), x IS NULL;
} {NULL 1}
+
+# 2023-03-08 https://sqlite.org/forum/forumpost/f5a2b1db87
+# NULL pointer dereference following an error.
+#
+do_execsql_test 18.0 {
+ CREATE TABLE v0(c1 INT);
+ CREATE VIEW view_2(c1) AS SELECT CASE WHEN c1 COLLATE TRUE THEN TRUE ELSE TRUE END FROM v0;
+ CREATE TRIGGER x1 INSTEAD OF INSERT ON view_2 BEGIN SELECT true; END;
+}
+do_catchsql_test 18.1 {
+ INSERT INTO view_2 DEFAULT VALUES RETURNING *;
+} {1 {no such collation sequence: TRUE}}
+
+# 2023-03-16
+# https://sqlite.org/forum/forumpost/c99d6e0329
+# ticket d15b3a4ea901ef0d
+# ticket 89d259d45b855a0d
+#
+# A RETURNING clause on an IF NOT EXISTS trigger does not generate
+# an error if the trigger already exists.
+#
+do_execsql_test 19.0 {
+ DROP TABLE IF EXISTS t1;CREATE TABLE t1(a);
+ CREATE TRIGGER r1 AFTER UPDATE ON t1 BEGIN VALUES(0); END;
+} {}
+do_catchsql_test 19.1 {
+ CREATE TRIGGER IF NOT EXISTS r1 AFTER DELETE ON t1 BEGIN
+ INSERT INTO t1(a) VALUES (1) RETURNING FALSE;
+ INSERT INTO t1(a) VALUES (2) RETURNING TRUE;
+ END;
+} {0 {}}
finish_test
Index: test/scanstatus2.test
==================================================================
--- test/scanstatus2.test
+++ test/scanstatus2.test
@@ -229,9 +229,37 @@
----USE TEMP B-TREE FOR GROUP BY
--SCAN rt1 (nCycle=nnn)
--CREATE AUTOMATIC INDEX ON v1(x1, cnt) (nCycle=nnn)
--SEARCH v1 USING AUTOMATIC COVERING INDEX (x1=?) (nCycle=nnn)
}
+
+#-------------------------------------------------------------------------
+reset_db
+
+ifcapable trace {
+ do_execsql_test 5.0 {
+ CREATE TABLE t1(x, y);
+ CREATE TRIGGER tr1 AFTER DELETE ON t1 BEGIN
+ SELECT 1;
+ END;
+ INSERT INTO t1 VALUES(1, 2);
+ }
+
+ proc trace {stmt sql} {
+ array set A [sqlite3_stmt_scanstatus -flags complex [format %x $stmt] 0]
+ lappend ::trace_explain $A(zExplain)
+ }
+ db trace_v2 trace
+
+ set ::trace_explain [list]
+ do_execsql_test 5.1 {
+ DELETE FROM t1 WHERE x=1;
+ }
+
+ do_test 5.2 {
+ set ::trace_explain
+ } {{SCAN t1} {SCAN t1} {SCAN t1}}
+}
finish_test
Index: test/seekscan1.test
==================================================================
--- test/seekscan1.test
+++ test/seekscan1.test
@@ -21,10 +21,11 @@
INSERT INTO t1(a,b,c) SELECT printf('xyz%d',x/10),x/6,x FROM c;
INSERT INTO t1 VALUES('abc',234,6);
INSERT INTO t1 VALUES('abc',345,7);
ANALYZE;
}
+
do_execsql_test 1.1 {
SELECT a,b,c FROM t1
WHERE b IN (234, 345) AND c BETWEEN 6 AND 6.5 AND a='abc'
ORDER BY a, b;
@@ -56,8 +57,12 @@
ORDER BY a, b;
} {
abc 234 6
abc 345 7
}
+
+do_execsql_test 1.5 {
+ SELECT a,b,c FROM t1 WHERE b IN (235, 345) AND c<=3 AND a='abc' ORDER BY a, b;
+}
finish_test
Index: test/skipscan1.test
==================================================================
--- test/skipscan1.test
+++ test/skipscan1.test
@@ -416,7 +416,17 @@
AND a = 3
AND b IN (1,3,2,4)
AND b >= 0
AND a <= 10;
} {3}
+
+# 2023-03-24 https://sqlite.org/forum/forumpost/8cc1dc0fe9
+#
+reset_db
+do_execsql_test skipscan1-5.0 {
+ CREATE TABLE t1(a TEXT, UNIQUE(a,a,a));
+ INSERT INTO t1 VALUES (hex(zeroblob(241))),(1),(2),(3);
+ ANALYZE;
+ SELECT max(a) FROM t1 WHERE a IN t1;
+} {3}
finish_test
Index: test/subtype1.test
==================================================================
--- test/subtype1.test
+++ test/subtype1.test
@@ -51,9 +51,27 @@
} {0}
do_execsql_test subtype1-231 {
WITH t4(a) AS NOT MATERIALIZED (SELECT json(1)) SELECT subtype(a) FROM t4;
} {0}
-
+# 2023-03-01
+# https://sqlite.org/forum/forumpost/37dd14a538
+#
+# Additional tests to show that subtypes do not traverse subquery boundaries.
+#
+do_execsql_test subtype1-300 {
+ CREATE TABLE t0(c0);
+ INSERT INTO t0 VALUES ('1');
+ CREATE VIEW v0(c0) AS SELECT CASE WHEN 1 THEN json_patch('1', '1') END
+ FROM t0 GROUP BY t0.c0;
+ SELECT * FROM v0 WHERE json_quote(v0.c0) != '1';
+} {1}
+do_execsql_test subtype1-310 {
+ SELECT *, json_quote(y) FROM (SELECT +json('1') AS y);
+} {1 {"1"}}
+do_execsql_test subtype1-320 {
+ SELECT *, json_quote(y) FROM (SELECT +json('1') AS y)
+ WHERE json_quote(y)='"1"';
+} {1 {"1"}}
finish_test
Index: test/tester.tcl
==================================================================
--- test/tester.tcl
+++ test/tester.tcl
@@ -946,10 +946,33 @@
proc normalize_list {L} {
set L2 [list]
foreach l $L {lappend L2 $l}
set L2
}
+
+# Run SQL and verify that the number of "vmsteps" required is greater
+# than or less than some constant.
+#
+proc do_vmstep_test {tn sql nstep {res {}}} {
+ uplevel [list do_execsql_test $tn.0 $sql $res]
+
+ set vmstep [db status vmstep]
+ if {[string range $nstep 0 0]=="+"} {
+ set body "if {$vmstep<$nstep} {
+ error \"got $vmstep, expected more than [string range $nstep 1 end]\"
+ }"
+ } else {
+ set body "if {$vmstep>$nstep} {
+ error \"got $vmstep, expected less than $nstep\"
+ }"
+ }
+
+ # set name "$tn.vmstep=$vmstep,expect=$nstep"
+ set name "$tn.1"
+ uplevel [list do_test $name $body {}]
+}
+
# Either:
#
# do_execsql_test TESTNAME SQL ?RES?
# do_execsql_test -db DB TESTNAME SQL ?RES?
Index: test/tkt-99378177930f87bd.test
==================================================================
--- test/tkt-99378177930f87bd.test
+++ test/tkt-99378177930f87bd.test
@@ -173,7 +173,24 @@
(SELECT sum((SELECT 1 FROM t1 NATURAL RIGHT JOIN t1 WHERE a=a)))) AS xyz
)
AND a==2
);
} {1 2}
+
+# 2023-03-04 https://sqlite.org/forum/forumpost/a68313d054
+#
+# See also indexexpr1-2200 added on 2023-03-18.
+#
+do_execsql_test tkt-99378-400 {
+ DROP TABLE t1;
+ CREATE TABLE t0(w);
+ INSERT INTO t0(w) VALUES(1);
+ CREATE TABLE t1(x);
+ INSERT INTO t1(x) VALUES(1);
+ CREATE INDEX t1x ON t1(x > 0);
+ CREATE VIEW t2(y) AS SELECT avg(w) FROM t0 GROUP BY w>1;
+ CREATE VIEW t3(z) AS SELECT count(*) FROM t2 WHERE y BETWEEN 0 and 0;
+ SELECT count(*) FROM t1 NOT INDEXED WHERE (SELECT z FROM t3);
+ SELECT count(*) FROM t1 INDEXED BY t1x WHERE (SELECT z FROM t3);
+} {0 0}
finish_test
Index: test/update.test
==================================================================
--- test/update.test
+++ test/update.test
@@ -729,7 +729,40 @@
UPDATE t1 SET a=0;
} {1 {constraint failed}}
do_execsql_test update-20.30 {
PRAGMA integrity_check;
} {ok}
+
+# 2023-03-16 https://sqlite.org/forum/forumpost/0007d1fdb1
+# A subquery in the WHERE clause of an UPDATE and behind a
+# short-circuit evaluation caused problems because multi-row
+# single-pass was selected.
+#
+# Similar problem for DELETE tested by delete-12.0.
+# https://sqlite.org/src/info/73f0036f045bf371
+#
+reset_db
+do_execsql_test update-21.1 {
+ CREATE TABLE t1 (vkey INTEGER, c5 INTEGER);
+ INSERT INTO t1 VALUES(3,NULL),(6,-54);
+}
+db null NULL
+do_execsql_test update-21.2 {
+ BEGIN;
+ UPDATE t1 SET vkey = 100 WHERE c5 is null;
+ SELECT * FROM t1 ORDER BY vkey, c5;
+ ROLLBACK;
+} {6 -54 100 NULL}
+do_execsql_test update-21.3 {
+ BEGIN;
+ UPDATE t1 SET vkey = 100 WHERE NOT (-10*(select min(vkey) from t1) >= c5);
+ SELECT * FROM t1 ORDER BY vkey, c5;
+ ROLLBACK;
+} {3 NULL 6 -54}
+do_execsql_test update-21.4 {
+ BEGIN;
+ UPDATE t1 SET vkey = 100 WHERE c5 is null OR NOT (-10*(select min(vkey) from t1) >= c5);
+ SELECT * FROM t1 ORDER BY vkey, c5;
+ ROLLBACK;
+} {6 -54 100 NULL}
finish_test
Index: test/window1.test
==================================================================
--- test/window1.test
+++ test/window1.test
@@ -2207,7 +2207,158 @@
do_execsql_test 72.1 {
CREATE TABLE dual(dummy); INSERT INTO dual VALUES('X');
CREATE VIEW v1(x,y) AS SELECT RANK() OVER (PARTITION BY 0), SUM(0) FROM dual;
SELECT * FROM v1 WHERE true;
} {1 0}
+
+#-------------------------------------------------------------------------
+reset_db
+
+do_execsql_test 72.0 {
+ CREATE TABLE t0(c0);
+ INSERT INTO t0(c0) VALUES (0);
+ CREATE VIEW v0(c0) AS SELECT TOTAL(0) OVER (PARTITION BY t0.c0) FROM t0;
+}
+do_execsql_test 72.1 {
+ SELECT COUNT(*) FROM (
+ SELECT TOTAL(0) OVER (PARTITION BY t0.c0) FROM t0
+ )
+ WHERE ('1' IS NOT ('abcde' NOTNULL));
+} {1}
+
+# 2023-03-28 https://sqlite.org/forum/forumpost/dc3b92cfa0 (Song Liu)
+#
+reset_db
+do_execsql_test 73.0 {
+ CREATE TABLE t1(a INT);
+ INSERT INTO t1(a) VALUES(1),(2),(4);
+ CREATE VIEW t2(b,c) AS SELECT * FROM t1 JOIN t1 A ORDER BY sum(0) OVER(PARTITION BY 0);
+ CREATE TRIGGER x1 INSTEAD OF UPDATE ON t2 BEGIN SELECT true; END;
+}
+do_execsql_test 73.1 {
+ SELECT * FROM t2;
+} {1 1 1 2 1 4 2 1 2 2 2 4 4 1 4 2 4 4}
+do_execsql_test 73.2 {
+ UPDATE t2 SET c=99 WHERE b=4 RETURNING *;
+} {4 99 4 99 4 99}
+do_execsql_test 73.3 {
+ SELECT *, nth_value(15,2) OVER() FROM t2, t1 WHERE b=4;
+} {
+ 4 1 1 15
+ 4 2 1 15
+ 4 4 1 15
+ 4 1 2 15
+ 4 2 2 15
+ 4 4 2 15
+ 4 1 4 15
+ 4 2 4 15
+ 4 4 4 15
+}
+do_execsql_test 73.4 {
+ UPDATE t2 SET c=nth_value(15,2) OVER() FROM (SELECT * FROM t1) WHERE b=4 RETURNING *;
+} {
+ 4 15
+ 4 15
+ 4 15
+ 4 15
+ 4 15
+ 4 15
+ 4 15
+ 4 15
+ 4 15
+}
+do_execsql_test 73.5 {
+ DROP TRIGGER x1;
+}
+do_catchsql_test 73.6 {
+ UPDATE t2 SET c=99 WHERE b=4 RETURNING *;
+} {1 {cannot modify t2 because it is a view}}
+do_catchsql_test 73.7 {
+ UPDATE t2 SET c=nth_value(15,2) OVER() FROM (SELECT * FROM t1) WHERE b=4 RETURNING *;
+} {1 {cannot modify t2 because it is a view}}
+
+# 2023-03-28 https://sqlite.org/forum/forumpost/bad532820c
+#
+reset_db
+do_execsql_test 74.0 {
+ CREATE TABLE t1 (a INT, b INT);
+ CREATE TABLE t2 (c INT, d INT);
+ CREATE INDEX idx ON t1(abs(a));
+ INSERT INTO t1 VALUES(1,2),(3,4);
+ INSERT INTO t2 VALUES(5,6),(7,8);
+}
+do_execsql_test 74.1 {
+ SELECT (
+ SELECT count( a ) FROM t2 LIMIT 1
+ )
+ FROM t1;
+} {2} ;# Verified using PG 14.2
+do_execsql_test 74.2 {
+ SELECT (
+ SELECT count( a+c ) FROM t2 LIMIT 1
+ )
+ FROM t1;
+} {2 2} ;# verified on PG 14.2. Crashes PG 9.6!
+do_execsql_test 74.3 {
+ SELECT (
+ SELECT count( ( SELECT(sum(0) OVER(ORDER BY c, abs(a))) ) )
+ FROM t2 GROUP BY c LIMIT 1
+ )
+ FROM t1;
+} {1 1} ;# verified on PG 14.2
+do_execsql_test 74.4 {
+ /* Original test case reported in https://sqlite.org/forum/forumpost/bad532820c
+ CREATE TABLE v0 (c1);
+ CREATE INDEX i ON v0 (c1, c1=1);
+ SELECT 0 FROM v0 AS a1
+ WHERE (SELECT count((SELECT(sum(0) OVER(PARTITION BY(c1), (a1.c1=1) ))))
+ FROM v0
+ GROUP BY hex(0))
+ AND a1.c1=0;
+} {}
+
+# 2023-04-11 https://sqlite.org/forum/forumpost/6c5678e3da
+# An ALWAYS() turns out to be sometimes false.
+#
+do_execsql_test 75.0 {
+ DROP TABLE t1;
+ CREATE TABLE t1(a INT, b INT);
+ CREATE INDEX t1x ON t1(a+b);
+}
+do_catchsql_test 75.1 {
+ SELECT count((SELECT count(a0.a+a0.b) ORDER BY sum(0) OVER (PARTITION BY 0)))
+ FROM t1 AS a0 JOIN t1 AS a1
+ GROUP BY a1.a;
+} {1 {misuse of aggregate: count()}}
+
+# 2023-04-11 https://sqlite.org/forum/forumpost/6c5678e3da
+# An ALWAYS() turns out to be sometimes false.
+#
+do_execsql_test 75.0 {
+ DROP TABLE t1;
+ CREATE TABLE t1(a INT, b INT);
+ CREATE INDEX t1x ON t1(a+b);
+}
+do_catchsql_test 75.1 {
+ SELECT count((SELECT count(a0.a+a0.b) ORDER BY sum(0) OVER (PARTITION BY 0)))
+ FROM t1 AS a0 JOIN t1 AS a1
+ GROUP BY a1.a;
+} {1 {misuse of aggregate: count()}}
+
+# 2023-04-13 https://sqlite.org/forum/forumpost/0d48347967
+reset_db
+do_execsql_test 76.0 {
+ CREATE TABLE t1(a INT, b INT);
+ INSERT INTO t1(a,b) VALUES (111,222),(111,223),(118,229);
+ CREATE INDEX t1a ON t1(a);
+ CREATE TABLE t2(x INT);
+ INSERT INTO t2 VALUES (333),(444),(555);
+}
+do_execsql_test 76.1 {
+ SELECT c, (SELECT c + sum(1) OVER ()) AS "res"
+ FROM t2 LEFT JOIN (SELECT +a AS c FROM t1) AS v1 ON true
+ GROUP BY c
+ ORDER by c;
+} {111 112 118 119}
+# ^^^^^^^^^^^^^^^^^-- results verified against PG 14.2
finish_test