From 7a98937db2414e60a75991e637c0da6b87dd6336 Mon Sep 17 00:00:00 2001 From: drh <> Date: Sat, 15 Oct 2022 11:27:01 +0000 Subject: [PATCH] Only extract an expression from an index when the index is not a null row in an outer join. FossilOrigin-Name: 08b033c737d1a84859291f50e2985c9dad8d660a50185d55d3171165a8e08d4c --- manifest | 16 ++++++++-------- manifest.uuid | 2 +- src/expr.c | 22 +++++++++++++++++++++- src/sqliteInt.h | 1 + src/where.c | 16 ++++++++-------- 5 files changed, 39 insertions(+), 18 deletions(-) diff --git a/manifest b/manifest index e6e98f6967..697406e18c 100644 --- a/manifest +++ b/manifest @@ -1,5 +1,5 @@ -C Merge\sfixes\sfrom\strunk. -D 2022-10-14T19:56:58.085 +C Only\sextract\san\sexpression\sfrom\san\sindex\swhen\sthe\sindex\sis\snot\sa\snull\srow\nin\san\souter\sjoin. +D 2022-10-15T11:27:01.598 F .fossil-settings/empty-dirs dbb81e8fc0401ac46a1491ab34a7f2c7c0452f2f06b54ebb845d024ca8283ef1 F .fossil-settings/ignore-glob 35175cdfcf539b2318cb04a9901442804be81cd677d8b889fcc9149c21f239ea F LICENSE.md df5091916dbb40e6e9686186587125e1b2ff51f022cc334e886c19a0e9982724 @@ -567,7 +567,7 @@ F src/date.c 94ce83b4cd848a387680a5f920c9018c16655db778c4d36525af0a0f34679ac5 F src/dbpage.c 5808e91bc27fa3981b028000f8fadfdc10ce9e59a34ce7dc4e035a69be3906ec F src/dbstat.c 861e08690fcb0f2ee1165eff0060ea8d4f3e2ea10f80dab7d32ad70443a6ff2d F src/delete.c 86573edae75e3d3e9a8b590d87db8e47222103029df4f3e11fa56044459b514e -F src/expr.c b60036e4bb9080c741a970df3b3cfffc6292aadec3670b7b04bfae6f662ff245 +F src/expr.c 8e260b0082700cb009238eb41cb28a8fce0290356b9fd25a72017c7fb48cb463 F src/fault.c 460f3e55994363812d9d60844b2a6de88826e007 F src/fkey.c 722f20779f5342a787922deded3628d8c74b5249cab04098cf17ee2f2aaff002 F src/func.c fe2a795ad647ce42054873ac91c43beb7b5d204892903a67f6e7e314379b9d4a @@ -622,7 +622,7 @@ F src/shell.c.in 2915eaf22bda89ad6533851a051de4773c249185360fe1fc7b4477b8f9063b2 F src/sqlite.h.in d9c8a6243fc0a1c270d69db33758e34b810af3462f9bc5b4af113b347e07c69d F src/sqlite3.rc 5121c9e10c3964d5755191c80dd1180c122fc3a8 F src/sqlite3ext.h 5336beea1868d99d2f62e628dbea55e97267dbff8193291ab175e960c5df9141 -F src/sqliteInt.h a856afa8ff20886d1b0c668b9e3c62daf7875989a1db79e60f121778d48015e1 +F src/sqliteInt.h 30e6fdc957f09dbdbe1bcf73874263427bbd9a321cd2a583de74bdb030c9fa78 F src/sqliteLimit.h d7323ffea5208c6af2734574bae933ca8ed2ab728083caa117c9738581a31657 F src/status.c 160c445d7d28c984a0eae38c144f6419311ed3eace59b44ac6dafc20db4af749 F src/table.c 0f141b58a16de7e2fbe81c308379e7279f4c6b50eb08efeec5892794a0ba30d1 @@ -704,7 +704,7 @@ F src/vxworks.h d2988f4e5a61a4dfe82c6524dd3d6e4f2ce3cdb9 F src/wal.c b9df133a705093da8977da5eb202eaadb844839f1c7297c08d33471f5491843d F src/wal.h c3aa7825bfa2fe0d85bef2db94655f99870a285778baa36307c0a16da32b226a F src/walker.c f890a3298418d7cba3b69b8803594fdc484ea241206a8dfa99db6dd36f8cbb3b -F src/where.c 39889861616d7751efacb9e5aa84ec5635dbf12a137725f57aa25fc6b622e15c +F src/where.c 8517a0ee4a3169368176fda649e2693ee013d7bdca72d485050e91cd640735f7 F src/whereInt.h 70cd30de9ed784aa33fa6bd1245f060617de7a00d992469b6d8e419eed915743 F src/wherecode.c bb88be457df3a6a01c844074ab79a9ba74279e73185f1362383aa697e3cae5dc F src/whereexpr.c bf8c155212c886621d71c951053660de6fcc4ee907b17aa02da0a96a39aa9405 @@ -2031,8 +2031,8 @@ F vsixtest/vsixtest.tcl 6a9a6ab600c25a91a7acc6293828957a386a8a93 F vsixtest/vsixtest.vcxproj.data 2ed517e100c66dc455b492e1a33350c1b20fbcdc F vsixtest/vsixtest.vcxproj.filters 37e51ffedcdb064aad6ff33b6148725226cd608e F vsixtest/vsixtest_TemporaryKey.pfx e5b1b036facdb453873e7084e1cae9102ccc67a0 -P 0963519371bb7ac02adb323430855760fb9b82a23745e47c504aaf393d22ac34 ed14863dd72e35fa3a23320c3d5a8166515faea39a555c28a27b2d35e701eac4 -R a9b714ce8ec9ca5df691ed405bb47472 +P 1cb65f36c3539302767f551ed53082f054666a7cb2696cef0990ab6747edbc52 +R b4967a409c773c44f7c239bcdd51f4f2 U drh -Z 5b8c2826f1d75c8b25b3831a35d10fbe +Z 10f741f9b8a389bcee87379894e97dc0 # Remove this line to create a well-formed Fossil manifest. diff --git a/manifest.uuid b/manifest.uuid index 2169956f70..8ced3b3665 100644 --- a/manifest.uuid +++ b/manifest.uuid @@ -1 +1 @@ -1cb65f36c3539302767f551ed53082f054666a7cb2696cef0990ab6747edbc52 \ No newline at end of file +08b033c737d1a84859291f50e2985c9dad8d660a50185d55d3171165a8e08d4c \ No newline at end of file diff --git a/src/expr.c b/src/expr.c index 3630b47aa8..0ff364ddca 100644 --- a/src/expr.c +++ b/src/expr.c @@ -4055,9 +4055,29 @@ static SQLITE_NOINLINE int sqlite3ExprIndexLookup( int target /* Where to store the result of the expression */ ){ IndexExpr *p; + Vdbe *v; for(p=pParse->pIdxExpr; p; p=p->pIENext){ + if( p->iDataCur<0 ) continue; if( sqlite3ExprCompare(0, pExpr, p->pExpr, p->iDataCur)!=0 ) continue; - sqlite3VdbeAddOp3(pParse->pVdbe, OP_Column, p->iIdxCur, p->iIdxCol, target); + 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 + ** computed using the original expression. */ + int addr = sqlite3VdbeCurrentAddr(v); + sqlite3VdbeAddOp3(v, OP_IfNullRow, p->iIdxCur, addr+3, target); + VdbeCoverage(v); + sqlite3VdbeAddOp3(v, OP_Column, p->iIdxCur, p->iIdxCol, target); + sqlite3VdbeGoto(v, 0); + p = pParse->pIdxExpr; + pParse->pIdxExpr = 0; + sqlite3ExprCode(pParse, pExpr, target); + pParse->pIdxExpr = p; + sqlite3VdbeJumpHere(v, addr+2); + }else{ + sqlite3VdbeAddOp3(v, OP_Column, p->iIdxCur, p->iIdxCol, target); + } return target; } return -1; /* Not found */ diff --git a/src/sqliteInt.h b/src/sqliteInt.h index caf7610adb..fffe981488 100644 --- a/src/sqliteInt.h +++ b/src/sqliteInt.h @@ -3579,6 +3579,7 @@ struct IndexExpr { int iDataCur; /* The data cursor associated with the index */ int iIdxCur; /* The index cursor */ int iIdxCol; /* The column of the index that contains pExpr */ + u8 bMaybeNullRow; /* True if we need an OP_IfNullRow check */ IndexExpr *pIENext; /* Next in a list of all indexed expressions */ }; diff --git a/src/where.c b/src/where.c index 0bba48883e..d43c2b2050 100644 --- a/src/where.c +++ b/src/where.c @@ -5409,22 +5409,23 @@ static void whereIndexExprCleanup(sqlite3 *db, void *pObject){ ** references to the corresponding column of the index. */ static SQLITE_NOINLINE void whereAddIndexExpr( - Parse *pParse, /* Add IndexExpr entries to pParse->pIdxExpr */ - Index *pIdx, /* The index-on-expression that contains the expressions */ - int iIdxCur, /* Cursor number for pIdx */ - int iDataCur /* Cursor for the corresponding table */ + Parse *pParse, /* Add IndexExpr entries to pParse->pIdxExpr */ + Index *pIdx, /* The index-on-expression that contains the expressions */ + int iIdxCur, /* Cursor number for pIdx */ + SrcItem *pTabItem /* The FROM clause entry for the table */ ){ int i; IndexExpr *p; for(i=0; inColumn; i++){ if( pIdx->aiColumn[i]!=XN_EXPR ) continue; - p = sqlite3DbMallocRaw(pParse->db, sizeof(IndexExpr)); + p = sqlite3DbMallocRaw(pParse->db, sizeof(IndexExpr)); if( p==0 ) break; p->pIENext = pParse->pIdxExpr; p->pExpr = sqlite3ExprDup(pParse->db, pIdx->aColExpr->a[i].pExpr, 0); - p->iDataCur = iDataCur; + p->iDataCur = pTabItem->iCursor; p->iIdxCur = iIdxCur; p->iIdxCol = i; + p->bMaybeNullRow = (pTabItem->fg.jointype & (JT_LEFT|JT_LTORJ))!=0; pParse->pIdxExpr = p; if( p->pIENext==0 ){ sqlite3ParserAddCleanup(pParse, whereIndexExprCleanup, pParse); @@ -5978,7 +5979,7 @@ WhereInfo *sqlite3WhereBegin( }else{ iIndexCur = pParse->nTab++; if( pIx->bHasExpr ){ - whereAddIndexExpr(pParse, pIx, iIndexCur, pTabItem->iCursor); + whereAddIndexExpr(pParse, pIx, iIndexCur, pTabItem); } } pLevel->iIdxCur = iIndexCur; @@ -6382,7 +6383,6 @@ void sqlite3WhereEnd(WhereInfo *pWInfo){ if( p->iIdxCur==pLevel->iIdxCur ){ p->iDataCur = -1; p->iIdxCur = -1; - p->pExpr->op = TK_ILLEGAL; } p = p->pIENext; } -- 2.47.2