From 450a90097fabf4eac7568a9688b34278b4d72122 Mon Sep 17 00:00:00 2001 From: drh <> Date: Thu, 5 Feb 2026 21:14:34 +0000 Subject: [PATCH] Do a better job of not generating unnecessary Bloom filters associated with IN operators. FossilOrigin-Name: 3861df5c127f82becea80c48c547de8ed0d01eebcf6a6a9d7f234dde31f50394 --- manifest | 22 +++++++++++----------- manifest.uuid | 2 +- src/expr.c | 17 ++++++++++++++--- src/select.c | 10 ++++++++++ src/sqliteInt.h | 4 ++-- src/wherecode.c | 2 +- test/autoindex1.test | 3 +-- test/rowvalue4.test | 3 +-- 8 files changed, 41 insertions(+), 22 deletions(-) diff --git a/manifest b/manifest index 17ffe57dd9..53bcfe6937 100644 --- a/manifest +++ b/manifest @@ -1,5 +1,5 @@ -C Fix\sharmless\scompiler\swarning\swhen\sthe\sCLI\sis\scompiled\swith\nSQLITE_OMIT_PROGRESS_CALLBACK. -D 2026-02-05T13:03:04.505 +C Do\sa\sbetter\sjob\sof\snot\sgenerating\sunnecessary\sBloom\sfilters\sassociated\nwith\sIN\soperators. +D 2026-02-05T21:14:34.271 F .fossil-settings/binary-glob 61195414528fb3ea9693577e1980230d78a1f8b0a54c78cf1b9b24d0a409ed6a x F .fossil-settings/empty-dirs dbb81e8fc0401ac46a1491ab34a7f2c7c0452f2f06b54ebb845d024ca8283ef1 F .fossil-settings/ignore-glob 35175cdfcf539b2318cb04a9901442804be81cd677d8b889fcc9149c21f239ea @@ -689,7 +689,7 @@ F src/date.c e19e0cfff9a41bfdd884c655755f6f00bca4c1a22272b56e0dd6667b7ea893a2 F src/dbpage.c c9ea81c11727f27e02874611e92773e68e2a90a875ef2404b084564c235fd91f F src/dbstat.c 73362c0df0f40ad5523a6f5501224959d0976757b511299bf892313e79d14f5c F src/delete.c 901499bed747c3b4b2be45be1abe912ba50a3f6a40ba88cc006ccf279f2d0e97 -F src/expr.c 1ca95a1f8d0ef5113ca948ffac815183e30d754403f871e91d9ebb94ec92ee0d +F src/expr.c c4ff8dcacbc8962fb670fc7c9723c8346398795b16ce2f78439234769baee2e6 F src/fault.c 460f3e55994363812d9d60844b2a6de88826e007 F src/fkey.c c065da737307a29e4d240ac727758dbf4102cb3218a1f651eb689b6a6fa12531 F src/func.c efbcfe7cb7fc92fe5299c9aaa141075eb60d2108253e99bc235384ed6a90d937 @@ -738,12 +738,12 @@ F src/printf.c b1b29b5e58e1530d5daeee5963d3c318d8ab2d7e38437580e28755753e0c1ded F src/random.c 606b00941a1d7dd09c381d3279a058d771f406c5213c9932bbd93d5587be4b9c F src/resolve.c 47aa7fdc9ec4c19b103ac5e79d7887d30119b5675309facf5eed1118391c868b F src/rowset.c 8432130e6c344b3401a8874c3cb49fefe6873fec593294de077afea2dce5ec97 -F src/select.c 098caf322d17cd480a5ebd81b9252b9ad90c72be7d5cdc5875e4a0a43e0ba965 +F src/select.c af5f50443de19c071396f8c572e6b7e1f359a7e0a0f3c705a2ec10a151312380 F src/shell.c.in b944a21d98cc4c6107bfd1ec702440579cb4bf86435125b67ff661180e9453b5 F src/sqlite.h.in 8bcbaecfe2cbecf8c5c1381354fcdd7d307443e88b4953fccb222456c1267b61 F src/sqlite3.rc 015537e6ac1eec6c7050e17b616c2ffe6f70fca241835a84a4f0d5937383c479 F src/sqlite3ext.h 1b7a0ee438bb5c2896d0609c537e917d8057b3340f6ad004d2de44f03e3d3cca -F src/sqliteInt.h a1c9e55b677379987ebf7294737df387e5fe4017b2b365272ed4108c2218d3c8 +F src/sqliteInt.h 7f42e42286634092758864472ea445feb45e654c1659b7f5e723ea70939b34dc F src/sqliteLimit.h 904a3f520362c7065c18165aaabd504fb13cc1b76cb411f38bd41ac219e4af1e F src/status.c 7565d63a79aa2f326339a24a0461a60096d0bd2bce711fefb50b5c89335f3592 F src/table.c 0f141b58a16de7e2fbe81c308379e7279f4c6b50eb08efeec5892794a0ba30d1 @@ -822,7 +822,7 @@ F src/wal.h ba252daaa94f889f4b2c17c027e823d9be47ce39da1d3799886bbd51f0490452 F src/walker.c d5006d6b005e4ea7302ad390957a8d41ed83faa177e412f89bc5600a7462a014 F src/where.c f1237550a01ccbc70141a523661173ddf802f48f7fe94feab9151a66f80c0d9d F src/whereInt.h 8d94cb116c9e06205c3d5ac87af065fc044f8cf08bfdccd94b6ea1c1308e65da -F src/wherecode.c 71c5c6804b7f882dec8ec858758accae02fcfca13df3cc720f1f258e663ec7c5 +F src/wherecode.c 783ecd30061c875c919a5163e4b55f9a0eccdaf7c9b17ad2908a1668a8766bc4 F src/whereexpr.c bb649ce81bd6dc0eabfa2533ff5656fc7a16411e520a6c59be43e73e51503cce F src/window.c c0a38cd32473e8e8e7bc435039f914a36ca42465506dc491c65870c01ddac9fb F test/8_3_names.test ebbb5cd36741350040fd28b432ceadf495be25b2 @@ -885,7 +885,7 @@ F test/auth2.test 9eb7fce9f34bf1f50d3f366fb3e606be5a2000a1 F test/auth3.test 76d20a7fa136d63bcfcf8bcb65c0b1455ed71078d81f22bcd0550d3eb18594ab F test/autoanalyze1.test b9cc3f32a990fa56669b668d237c6d53e983554ae80c0604992e18869a0b2dec F test/autoinc.test 9df9930966dbe92c55ef37a4d89112cfd537be0d0596d397177c12db9e581be0 -F test/autoindex1.test 65931519206bbec71948b11e125af0656435a0937973fe5fed70d776a712911f +F test/autoindex1.test 2523a76f30734742c3f4d948d0cbf3b6627f775e7833814f425a2e289ba58b22 F test/autoindex2.test 12ef578928102baaa0dc23ad397601a2f4ecb0df F test/autoindex3.test ca502c8050166ac6107a7b4fe4e951f4d3270a23a958af02b14f1b962b83c4b6 F test/autoindex4.test 3c2105e9172920e26f950ba3c5823e4972190e022c1e6f260ba476b0af24c593 @@ -1556,7 +1556,7 @@ F test/rowid.test d27191b5ce794c05bf61081e8b2c546a1844c1641321dcaf7fb785234256cc F test/rowvalue.test 93474d8e1c496e970bdcc3a7f54ac835adda667d2fd971957b4bce0c0b11707b F test/rowvalue2.test 060d238b7e5639a7c5630cb5e63e311b44efef2b F test/rowvalue3.test 103e9a224ca0548dd0d67e439f39c5dd16de4200221a333927372408c025324c -F test/rowvalue4.test bac9326d1e886656650f67c0ec484eb5f452244a8209c6af508e9a862ace08ed +F test/rowvalue4.test 6e160977d44ee715e142f63ec0e339586c61f12bbbffacee369b1cdc0b7390f0 F test/rowvalue5.test 00740304ea6a53a8704640c7405690f0045d5d2a6b4b04dde7bccc14c3068ea7 F test/rowvalue6.test d19b54feb604d5601f8614b15e214e0774c01087 F test/rowvalue7.test 06ec0aca725bf683313d03793aa2943bc7f45a901848c7056a9665b769c8fc38 @@ -2194,8 +2194,8 @@ F tool/warnings-clang.sh bbf6a1e685e534c92ec2bfba5b1745f34fb6f0bc2a362850723a9ee F tool/warnings.sh d924598cf2f55a4ecbc2aeb055c10bd5f48114793e7ba25f9585435da29e7e98 F tool/win/sqlite.vsix deb315d026cc8400325c5863eef847784a219a2f F tool/winmain.c 00c8fb88e365c9017db14c73d3c78af62194d9644feaf60e220ab0f411f3604c -P c476d956d0bd3065cf894de6f9d393b999ff7d2268a35f01a6d88804789ab58f -R 39afed9578b15cff4dc3299503d822ef +P d6150c813ce43e15285a07d24135b10a7c254b3c175dee4f61c3b6a6ce91c29f +R 4cfdd7681ac2e26f4d04032b5b7ca3e4 U drh -Z 0eb77c9d708daad390b936c51814f869 +Z 1d69fead23474cce92304dcd4ae165da # Remove this line to create a well-formed Fossil manifest. diff --git a/manifest.uuid b/manifest.uuid index 16d411d198..01179a3146 100644 --- a/manifest.uuid +++ b/manifest.uuid @@ -1 +1 @@ -d6150c813ce43e15285a07d24135b10a7c254b3c175dee4f61c3b6a6ce91c29f +3861df5c127f82becea80c48c547de8ed0d01eebcf6a6a9d7f234dde31f50394 diff --git a/src/expr.c b/src/expr.c index 972dc14d2c..b635b98910 100644 --- a/src/expr.c +++ b/src/expr.c @@ -3420,6 +3420,7 @@ int sqlite3FindInIndex( */ u32 savedNQueryLoop = pParse->nQueryLoop; int rMayHaveNull = 0; + int bloomOk = (inFlags & IN_INDEX_MEMBERSHIP)!=0; eType = IN_INDEX_EPH; if( inFlags & IN_INDEX_LOOP ){ pParse->nQueryLoop = 0; @@ -3427,7 +3428,13 @@ int sqlite3FindInIndex( *prRhsHasNull = rMayHaveNull = ++pParse->nMem; } assert( pX->op==TK_IN ); - sqlite3CodeRhsOfIN(pParse, pX, iTab); + if( !bloomOk + && ExprUseXSelect(pX) + && (pX->x.pSelect->selFlags & SF_ClonedRhsIn)!=0 + ){ + bloomOk = 1; + } + sqlite3CodeRhsOfIN(pParse, pX, iTab, bloomOk); if( rMayHaveNull ){ sqlite3SetHasNullFlag(v, iTab, rMayHaveNull); } @@ -3585,7 +3592,8 @@ static int findCompatibleInRhsSubrtn( void sqlite3CodeRhsOfIN( Parse *pParse, /* Parsing context */ Expr *pExpr, /* The IN operator */ - int iTab /* Use this cursor number */ + int iTab, /* Use this cursor number */ + int allowBloom /* True to allow the use of a Bloom filter */ ){ int addrOnce = 0; /* Address of the OP_Once instruction at top */ int addr; /* Address of OP_OpenEphemeral instruction */ @@ -3707,7 +3715,10 @@ void sqlite3CodeRhsOfIN( sqlite3SelectDestInit(&dest, SRT_Set, iTab); dest.zAffSdst = exprINAffinity(pParse, pExpr); pSelect->iLimit = 0; - if( addrOnce && OptimizationEnabled(pParse->db, SQLITE_BloomFilter) ){ + if( addrOnce + && allowBloom + && OptimizationEnabled(pParse->db, SQLITE_BloomFilter) + ){ int regBloom = ++pParse->nMem; addrBloom = sqlite3VdbeAddOp2(v, OP_Blob, 10000, regBloom); VdbeComment((v, "Bloom filter")); diff --git a/src/select.c b/src/select.c index 73dc8655ca..1679c8772c 100644 --- a/src/select.c +++ b/src/select.c @@ -5270,6 +5270,16 @@ static int pushDownWhereTerms( x.pEList = pSubq->pEList; x.pCList = findLeftmostExprlist(pSubq); pNew = substExpr(&x, pNew); + assert( pNew!=0 || pParse->nErr!=0 ); + if( pParse->nErr==0 && pNew->op==TK_IN && ExprUseXSelect(pNew) ){ + assert( pNew->x.pSelect!=0 ); + pNew->x.pSelect->selFlags |= SF_ClonedRhsIn; + assert( pWhere!=0 ); + assert( pWhere->op==TK_IN ); + assert( ExprUseXSelect(pWhere) ); + assert( pWhere->x.pSelect!=0 ); + pWhere->x.pSelect->selFlags |= SF_ClonedRhsIn; + } #ifndef SQLITE_OMIT_WINDOWFUNC if( pSubq->pWin && 0==pushDownWindowCheck(pParse, pSubq, pNew) ){ /* Restriction 6c has prevented push-down in this case */ diff --git a/src/sqliteInt.h b/src/sqliteInt.h index d4eda8d435..664b8afd95 100644 --- a/src/sqliteInt.h +++ b/src/sqliteInt.h @@ -3623,7 +3623,7 @@ struct Select { #define SF_Resolved 0x0000004 /* Identifiers have been resolved */ #define SF_Aggregate 0x0000008 /* Contains agg functions or a GROUP BY */ #define SF_HasAgg 0x0000010 /* Contains aggregate functions */ -/* 0x0000020 // available for reuse */ +#define SF_ClonedRhsIn 0x0000020 /* Cloned RHS of an IN operator */ #define SF_Expanded 0x0000040 /* sqlite3SelectExpand() called on this */ #define SF_HasTypeInfo 0x0000080 /* FROM subqueries have Table metadata */ #define SF_Compound 0x0000100 /* Part of a compound query */ @@ -5438,7 +5438,7 @@ void sqlite3AlterSetNotNull(Parse*, SrcList*, Token*, Token*); i64 sqlite3GetToken(const unsigned char *, int *); void sqlite3NestedParse(Parse*, const char*, ...); void sqlite3ExpirePreparedStatements(sqlite3*, int); -void sqlite3CodeRhsOfIN(Parse*, Expr*, int); +void sqlite3CodeRhsOfIN(Parse*, Expr*, int, int); int sqlite3CodeSubselect(Parse*, Expr*); void sqlite3SelectPrep(Parse*, Select*, NameContext*); int sqlite3ExpandSubquery(Parse*, SrcItem*); diff --git a/src/wherecode.c b/src/wherecode.c index 1efa34a5da..31d7990adc 100644 --- a/src/wherecode.c +++ b/src/wherecode.c @@ -1573,7 +1573,7 @@ Bitmask sqlite3WhereCodeOneLoopStart( if( SMASKBIT32(j) & pLoop->u.vtab.mHandleIn ){ int iTab = pParse->nTab++; int iCache = ++pParse->nMem; - sqlite3CodeRhsOfIN(pParse, pTerm->pExpr, iTab); + sqlite3CodeRhsOfIN(pParse, pTerm->pExpr, iTab, 0); sqlite3VdbeAddOp3(v, OP_VInitIn, iTab, iTarget, iCache); }else{ codeEqualityTerm(pParse, pTerm, pLevel, j, bRev, iTarget); diff --git a/test/autoindex1.test b/test/autoindex1.test index 1c8ce007f0..be41d702ca 100644 --- a/test/autoindex1.test +++ b/test/autoindex1.test @@ -185,8 +185,7 @@ do_eqp_test autoindex1-500.1 { QUERY PLAN |--SEARCH t501 USING INTEGER PRIMARY KEY (rowid=?) `--LIST SUBQUERY xxxxxx - |--SCAN t502 - `--CREATE BLOOM FILTER + `--SCAN t502 } do_eqp_test autoindex1-501 { SELECT b FROM t501 diff --git a/test/rowvalue4.test b/test/rowvalue4.test index 1ef5fc2920..5e02f0fc28 100644 --- a/test/rowvalue4.test +++ b/test/rowvalue4.test @@ -236,8 +236,7 @@ do_eqp_test 5.1 { QUERY PLAN |--SEARCH d2 USING INDEX d2ab (a=? AND b=?) |--LIST SUBQUERY xxxxxx - | |--SCAN d1 - | `--CREATE BLOOM FILTER + | `--SCAN d1 `--LIST SUBQUERY xxxxxx |--SCAN d1 `--CREATE BLOOM FILTER -- 2.47.3