From ed0c34857ae209d2272e9f625359196fd4342e58 Mon Sep 17 00:00:00 2001 From: drh Date: Fri, 20 Dec 2019 22:46:41 +0000 Subject: Do not try to access a generated column through an index if the collating sequence for the generated column is non-standard. Part 2 of ticket [e0a8120553f4b082] FossilOrigin-Name: 056bb8dcbdc45989c5c6e86d2966200062e3c01c382ec52aae37c828104b4496 --- src/wherecode.c | 12 +++++++++++- 1 file changed, 11 insertions(+), 1 deletion(-) (limited to 'src/wherecode.c') diff --git a/src/wherecode.c b/src/wherecode.c index 59546c43c..a054a930f 100644 --- a/src/wherecode.c +++ b/src/wherecode.c @@ -1190,7 +1190,17 @@ static void whereIndexExprTrans( x.pIdxExpr = aColExpr->a[iIdxCol].pExpr; w.xExprCallback = whereIndexExprTransNode; #ifndef SQLITE_OMIT_GENERATED_COLUMNS - }else if( iRef>=0 && (pTab->aCol[iRef].colFlags & COLFLAG_VIRTUAL)!=0 ){ + }else if( iRef>=0 + && (pTab->aCol[iRef].colFlags & COLFLAG_VIRTUAL)!=0 + && (pTab->aCol[iRef].zColl==0 + || sqlite3StrICmp(pTab->aCol[iRef].zColl, sqlite3StrBINARY)==0) + ){ + /* Check to see if there are direct references to generated columns + ** that are contained in the index. Pulling the generated column + ** out of the index is an optimization only - the main table is always + ** available if the index cannot be used. To avoid unnecessary + ** complication, omit this optimization if the collating sequence for + ** the column is non-standard */ x.iTabCol = iRef; w.xExprCallback = whereIndexExprTransColumn; #endif /* SQLITE_OMIT_GENERATED_COLUMNS */ -- cgit v1.2.3 From 69843342de6612381c4388fa94c1e87909ec57f0 Mon Sep 17 00:00:00 2001 From: dan Date: Sun, 22 Dec 2019 17:32:25 +0000 Subject: Ensure sqlite3WindowRewrite() is called on a SELECT statement before any terms aremoved from it as part of IN() clause processing. Fix for [f00d096ca]. FossilOrigin-Name: 8c856404b4e98d295449a4e89a41495dc007319a8e9c35c1a763718d7c5f67e8 --- src/wherecode.c | 20 +++++++++++++++++++- 1 file changed, 19 insertions(+), 1 deletion(-) (limited to 'src/wherecode.c') diff --git a/src/wherecode.c b/src/wherecode.c index a054a930f..a8ef581da 100644 --- a/src/wherecode.c +++ b/src/wherecode.c @@ -415,7 +415,25 @@ static Expr *removeUnindexableInClauseTerms( Expr *pX /* The IN expression to be reduced */ ){ sqlite3 *db = pParse->db; - Expr *pNew = sqlite3ExprDup(db, pX, 0); + Expr *pNew; +#ifndef SQLITE_OMIT_WINDOWFUNC + /* The SELECT statement at pX->x.pSelect has already been resolved. This + ** means that its window functions have already been identified and + ** linked into the Select.pWin list. However, if there are multiple + ** window functions and they do not all use the same window frame, only + ** those that use the same window frame as the first resolved are listed + ** in Select.pWin. Any others are delegated to sub-selects created by the + ** call to sqlite3WindowRewrite() made when coding the SELECT statement. + ** But - sqlite3WindowRewrite() is a no-op if Select.pWin==0. And if + ** removing the unindexable terms happens to remove all window functions + ** in the Select.pWin list, then Select.pWin ends up set to NULL - meaning + ** that the other window functions are never processed. To work around + ** this, ensure that sqlite3WindowRewrite() has been called to create the + ** required sub-selects before the unindexable terms are removed. See + ** ticket #f00d096ca. */ + if( sqlite3WindowRewrite(pParse, pX->x.pSelect) ) return 0; +#endif + pNew = sqlite3ExprDup(db, pX, 0); if( db->mallocFailed==0 ){ ExprList *pOrigRhs = pNew->x.pSelect->pEList; /* Original unmodified RHS */ ExprList *pOrigLhs = pNew->pLeft->x.pList; /* Original unmodified LHS */ -- cgit v1.2.3 From 6c1c85ca483154aafa41883253a941f196fa6d03 Mon Sep 17 00:00:00 2001 From: drh Date: Sun, 22 Dec 2019 18:55:04 +0000 Subject: Fix to the optimization of check-in [a47efb7c8520a011] that reads the values of expressions used in an index-on-expression directly from the index rather than recomputing the value. If the expression has a top-level COLLATE or unlikely() operator, be sure to clear the corresponding flags prior to converting it into a TK_COLUMN expression. Failure to do this is most likely harmless in production, but might cause an assertion fault in debugging builds. Ticket [b0cb8aff561a6dcd]. Test cases in TH3. FossilOrigin-Name: 56539e1c132632c075efc217ad5951a35e4459605ac128914aec3a7be1e25718 --- src/wherecode.c | 3 +++ 1 file changed, 3 insertions(+) (limited to 'src/wherecode.c') diff --git a/src/wherecode.c b/src/wherecode.c index a8ef581da..2f2a27271 100644 --- a/src/wherecode.c +++ b/src/wherecode.c @@ -1145,6 +1145,9 @@ static int whereIndexExprTransNode(Walker *p, Expr *pExpr){ pExpr->iTable = pX->iIdxCur; pExpr->iColumn = pX->iIdxCol; pExpr->y.pTab = 0; + testcase( ExprHasProperty(pExpr, EP_Skip) ); + testcase( ExprHasProperty(pExpr, EP_Unlikely) ); + ExprClearProperty(pExpr, EP_Skip|EP_Unlikely); return WRC_Prune; }else{ return WRC_Continue; -- cgit v1.2.3 From 2811ea6be7f4d63d5cc0992c78db3b1b73112a93 Mon Sep 17 00:00:00 2001 From: dan Date: Mon, 23 Dec 2019 14:20:46 +0000 Subject: For expressions like (x, y) IN (SELECT ...) where the SELECT uses window-functions, require that all columns on the LHS be indexed before an index can be used. Fix for [d9ed4ebe]. FossilOrigin-Name: 0b1dbd60f5db3abe2097dbc0b6de9671685ca5eaf7d3fc8e3f87ff5065a9d114 --- src/wherecode.c | 17 ----------------- 1 file changed, 17 deletions(-) (limited to 'src/wherecode.c') diff --git a/src/wherecode.c b/src/wherecode.c index 2f2a27271..d5d340374 100644 --- a/src/wherecode.c +++ b/src/wherecode.c @@ -416,23 +416,6 @@ static Expr *removeUnindexableInClauseTerms( ){ sqlite3 *db = pParse->db; Expr *pNew; -#ifndef SQLITE_OMIT_WINDOWFUNC - /* The SELECT statement at pX->x.pSelect has already been resolved. This - ** means that its window functions have already been identified and - ** linked into the Select.pWin list. However, if there are multiple - ** window functions and they do not all use the same window frame, only - ** those that use the same window frame as the first resolved are listed - ** in Select.pWin. Any others are delegated to sub-selects created by the - ** call to sqlite3WindowRewrite() made when coding the SELECT statement. - ** But - sqlite3WindowRewrite() is a no-op if Select.pWin==0. And if - ** removing the unindexable terms happens to remove all window functions - ** in the Select.pWin list, then Select.pWin ends up set to NULL - meaning - ** that the other window functions are never processed. To work around - ** this, ensure that sqlite3WindowRewrite() has been called to create the - ** required sub-selects before the unindexable terms are removed. See - ** ticket #f00d096ca. */ - if( sqlite3WindowRewrite(pParse, pX->x.pSelect) ) return 0; -#endif pNew = sqlite3ExprDup(db, pX, 0); if( db->mallocFailed==0 ){ ExprList *pOrigRhs = pNew->x.pSelect->pEList; /* Original unmodified RHS */ -- cgit v1.2.3 From e86f3402ac8a1fc965a57749467bc87e199aa793 Mon Sep 17 00:00:00 2001 From: drh Date: Thu, 26 Dec 2019 00:20:56 +0000 Subject: Disables the optimization that tries to pull the value of an expression from an index on that expression if the expression is a constant. FossilOrigin-Name: e5fd8b50500f9225e435ef5afee98e3c2cccd22785f99c718d7e6c9b5b653c56 --- src/wherecode.c | 1 + 1 file changed, 1 insertion(+) (limited to 'src/wherecode.c') diff --git a/src/wherecode.c b/src/wherecode.c index d5d340374..a24dea82e 100644 --- a/src/wherecode.c +++ b/src/wherecode.c @@ -1192,6 +1192,7 @@ static void whereIndexExprTrans( if( iRef==XN_EXPR ){ assert( aColExpr->a[iIdxCol].pExpr!=0 ); x.pIdxExpr = aColExpr->a[iIdxCol].pExpr; + if( sqlite3ExprIsConstant(x.pIdxExpr) ) continue; w.xExprCallback = whereIndexExprTransNode; #ifndef SQLITE_OMIT_GENERATED_COLUMNS }else if( iRef>=0 -- cgit v1.2.3 From 118efd162632298bccba21b71934f666e556f594 Mon Sep 17 00:00:00 2001 From: drh Date: Sat, 28 Dec 2019 14:07:22 +0000 Subject: New enhancements to .wheretrace. The 0x20000 flag shows the WHERE clause before and after coding each loop. The 0x800 flag shows status at the start and at the end of each loop. An extra "C" tag is shown on coded terms. FossilOrigin-Name: 59cc46e5a6d8dbb030f27716ad5446ecccf81cf0cfff95338b9133777f2059e7 --- src/wherecode.c | 31 +++++++++++++++++++++++++++++++ 1 file changed, 31 insertions(+) (limited to 'src/wherecode.c') diff --git a/src/wherecode.c b/src/wherecode.c index a24dea82e..8495de378 100644 --- a/src/wherecode.c +++ b/src/wherecode.c @@ -1284,6 +1284,17 @@ Bitmask sqlite3WhereCodeOneLoopStart( pLevel->notReady = notReady & ~sqlite3WhereGetMask(&pWInfo->sMaskSet, iCur); bRev = (pWInfo->revMask>>iLevel)&1; VdbeModuleComment((v, "Begin WHERE-loop%d: %s",iLevel,pTabItem->pTab->zName)); +#if WHERETRACE_ENABLED /* 0x20800 */ + if( sqlite3WhereTrace & 0x800 ){ + sqlite3DebugPrintf("Coding level %d: notReady=%llx\n", + iLevel, (u64)notReady); + sqlite3WhereLoopPrint(pLoop, pWC); + } + if( sqlite3WhereTrace & 0x20000 ){ + sqlite3DebugPrintf("Complete WHERE clause before coding:\n"); + sqlite3WhereClausePrint(pWC); + } +#endif /* Create labels for the "break" and "continue" instructions ** for the current loop. Jump to addrBrk to break out of a loop. @@ -2339,6 +2350,10 @@ Bitmask sqlite3WhereCodeOneLoopStart( VdbeNoopComment((v, "WhereTerm[%d] (%p) priority=%d", pWC->nTerm-j, pTerm, iLoop)); } + if( sqlite3WhereTrace & 0x800 ){ + sqlite3DebugPrintf("Coding auxiliary constraint:\n"); + sqlite3WhereTermPrint(pTerm, pWC->nTerm-j); + } #endif sqlite3ExprIfFalse(pParse, pE, addrCont, SQLITE_JUMPIFNULL); if( skipLikeAddr ) sqlite3VdbeJumpHere(v, skipLikeAddr); @@ -2365,6 +2380,12 @@ Bitmask sqlite3WhereCodeOneLoopStart( if( pLevel->iLeftJoin ) continue; pE = pTerm->pExpr; assert( !ExprHasProperty(pE, EP_FromJoin) ); +#ifdef WHERETRACE_ENABLED /* 0x800 */ + if( sqlite3WhereTrace & 0x800 ){ + sqlite3DebugPrintf("Coding transitive constraint:\n"); + sqlite3WhereTermPrint(pTerm, pWC->nTerm-j); + } +#endif assert( (pTerm->prereqRight & pLevel->notReady)!=0 ); pAlt = sqlite3WhereFindTerm(pWC, iCur, pTerm->u.leftColumn, notReady, WO_EQ|WO_IN|WO_IS, 0); @@ -2406,5 +2427,15 @@ Bitmask sqlite3WhereCodeOneLoopStart( } } +#if WHERETRACE_ENABLED /* 0x20800 */ + if( sqlite3WhereTrace & 0x20000 ){ + sqlite3DebugPrintf("Complete WHERE clause after coding level %d:\n",iLevel); + sqlite3WhereClausePrint(pWC); + } + if( sqlite3WhereTrace & 0x800 ){ + sqlite3DebugPrintf("End Coding level %d: notReady=%llx\n", + iLevel, (u64)pLevel->notReady); + } +#endif return pLevel->notReady; } -- cgit v1.2.3 From f1bb31e21950314818d86df25a67457c2a4fa1cf Mon Sep 17 00:00:00 2001 From: drh Date: Sat, 28 Dec 2019 14:33:26 +0000 Subject: Further improvements to .wheretrace during loop code generation. FossilOrigin-Name: c4d5b75c9381255ec8d9a284eedb6b27be46ca868cae5985cf8a5769b15290c3 --- src/wherecode.c | 15 ++++++++++----- 1 file changed, 10 insertions(+), 5 deletions(-) (limited to 'src/wherecode.c') diff --git a/src/wherecode.c b/src/wherecode.c index 8495de378..e9cdc1919 100644 --- a/src/wherecode.c +++ b/src/wherecode.c @@ -1286,12 +1286,16 @@ Bitmask sqlite3WhereCodeOneLoopStart( VdbeModuleComment((v, "Begin WHERE-loop%d: %s",iLevel,pTabItem->pTab->zName)); #if WHERETRACE_ENABLED /* 0x20800 */ if( sqlite3WhereTrace & 0x800 ){ - sqlite3DebugPrintf("Coding level %d: notReady=%llx\n", - iLevel, (u64)notReady); + sqlite3DebugPrintf("Coding level %d of %d: notReady=%llx\n", + iLevel, pWInfo->nLevel, (u64)notReady); sqlite3WhereLoopPrint(pLoop, pWC); } if( sqlite3WhereTrace & 0x20000 ){ - sqlite3DebugPrintf("Complete WHERE clause before coding:\n"); + if( iLevel==0 ){ + sqlite3DebugPrintf("WHERE clause being coded:\n"); + sqlite3TreeViewExpr(0, pWInfo->pWhere, 0); + } + sqlite3DebugPrintf("All WHERE-clause terms before coding:\n"); sqlite3WhereClausePrint(pWC); } #endif @@ -2379,13 +2383,13 @@ Bitmask sqlite3WhereCodeOneLoopStart( if( pTerm->leftCursor!=iCur ) continue; if( pLevel->iLeftJoin ) continue; pE = pTerm->pExpr; - assert( !ExprHasProperty(pE, EP_FromJoin) ); #ifdef WHERETRACE_ENABLED /* 0x800 */ if( sqlite3WhereTrace & 0x800 ){ sqlite3DebugPrintf("Coding transitive constraint:\n"); sqlite3WhereTermPrint(pTerm, pWC->nTerm-j); } #endif + assert( !ExprHasProperty(pE, EP_FromJoin) ); assert( (pTerm->prereqRight & pLevel->notReady)!=0 ); pAlt = sqlite3WhereFindTerm(pWC, iCur, pTerm->u.leftColumn, notReady, WO_EQ|WO_IN|WO_IS, 0); @@ -2429,7 +2433,8 @@ Bitmask sqlite3WhereCodeOneLoopStart( #if WHERETRACE_ENABLED /* 0x20800 */ if( sqlite3WhereTrace & 0x20000 ){ - sqlite3DebugPrintf("Complete WHERE clause after coding level %d:\n",iLevel); + sqlite3DebugPrintf("All WHERE-clause terms after coding level %d:\n", + iLevel); sqlite3WhereClausePrint(pWC); } if( sqlite3WhereTrace & 0x800 ){ -- cgit v1.2.3 From a4b2df5ce277b5bfb28ceeb1e8d9d052e870b367 Mon Sep 17 00:00:00 2001 From: drh Date: Sat, 28 Dec 2019 16:20:23 +0000 Subject: Disable early coding of transitive constraints at the end of each loop in the WHERE clause processing if the loop being coded is for a LEFT JOIN, even if the loop is part of an OR-clause optimization for virtual tables. Test cases in TH3. FossilOrigin-Name: 9421b442cad9858ec21050c106aa935a1c0723ab08ef9a867a79638b040b6e68 --- src/wherecode.c | 6 +++--- 1 file changed, 3 insertions(+), 3 deletions(-) (limited to 'src/wherecode.c') diff --git a/src/wherecode.c b/src/wherecode.c index e9cdc1919..289eecb30 100644 --- a/src/wherecode.c +++ b/src/wherecode.c @@ -1286,8 +1286,8 @@ Bitmask sqlite3WhereCodeOneLoopStart( VdbeModuleComment((v, "Begin WHERE-loop%d: %s",iLevel,pTabItem->pTab->zName)); #if WHERETRACE_ENABLED /* 0x20800 */ if( sqlite3WhereTrace & 0x800 ){ - sqlite3DebugPrintf("Coding level %d of %d: notReady=%llx\n", - iLevel, pWInfo->nLevel, (u64)notReady); + sqlite3DebugPrintf("Coding level %d of %d: notReady=%llx iFrom=%d\n", + iLevel, pWInfo->nLevel, (u64)notReady, pLevel->iFrom); sqlite3WhereLoopPrint(pLoop, pWC); } if( sqlite3WhereTrace & 0x20000 ){ @@ -2381,7 +2381,7 @@ Bitmask sqlite3WhereCodeOneLoopStart( if( (pTerm->eOperator & (WO_EQ|WO_IS))==0 ) continue; if( (pTerm->eOperator & WO_EQUIV)==0 ) continue; if( pTerm->leftCursor!=iCur ) continue; - if( pLevel->iLeftJoin ) continue; + if( pTabItem->fg.jointype & JT_LEFT ) continue; pE = pTerm->pExpr; #ifdef WHERETRACE_ENABLED /* 0x800 */ if( sqlite3WhereTrace & 0x800 ){ -- cgit v1.2.3 From 4adb1d0000c8bd3d194518bf3e164d90cdecb93b Mon Sep 17 00:00:00 2001 From: dan Date: Sat, 28 Dec 2019 18:08:39 +0000 Subject: Change an assert() in where.c to a testcase() macro, since the condition may be false. This was a problem with the assert() only, there is no bug in release builds that omit assert(). FossilOrigin-Name: 82be135dee7ccfde5f8a67f3621b7ced449dce89bae9cadf025154a4de848c11 --- src/wherecode.c | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) (limited to 'src/wherecode.c') diff --git a/src/wherecode.c b/src/wherecode.c index 289eecb30..9c51b5669 100644 --- a/src/wherecode.c +++ b/src/wherecode.c @@ -1685,7 +1685,7 @@ Bitmask sqlite3WhereCodeOneLoopStart( ){ assert( bSeekPastNull==0 && nExtraReg==0 && nBtm==0 && nTop==0 ); assert( pRangeEnd==0 && pRangeStart==0 ); - assert( pLoop->nSkip==0 ); + testcase( pLoop->nSkip>0 ); nExtraReg = 1; bSeekPastNull = 1; pLevel->regBignull = regBignull = ++pParse->nMem; -- cgit v1.2.3 From be3da24134f53a0c8f10291611af758dc0ced611 Mon Sep 17 00:00:00 2001 From: drh Date: Sun, 29 Dec 2019 00:52:41 +0000 Subject: Add the OP_FinishSeek opcode which completes an OP_DeferredSeek if the seek has not already completed. Also add the sqlite3WhereUsesDeferredSeek() interface to the query planner. The UPDATE implementation adds an OP_FinishSeek before running the final OP_Insert if one is needed. Ticket [ec8abb025e78f40c] and also an assertion fault reported by Yongheng. FossilOrigin-Name: 21ef6e99331210b80fa7c71b4f02e8f768a748d01aef884368af2f6b51a067e0 --- src/wherecode.c | 1 + 1 file changed, 1 insertion(+) (limited to 'src/wherecode.c') diff --git a/src/wherecode.c b/src/wherecode.c index 9c51b5669..96c2971a5 100644 --- a/src/wherecode.c +++ b/src/wherecode.c @@ -1045,6 +1045,7 @@ static void codeDeferredSeek( assert( iIdxCur>0 ); assert( pIdx->aiColumn[pIdx->nColumn-1]==-1 ); + pWInfo->bDeferredSeek = 1; sqlite3VdbeAddOp3(v, OP_DeferredSeek, iIdxCur, 0, iCur); if( (pWInfo->wctrlFlags & WHERE_OR_SUBCLAUSE) && DbMaskAllZero(sqlite3ParseToplevel(pParse)->writeMask) -- cgit v1.2.3