From 8bc112e3e453a9e574a800e7edab3afecb064449 Mon Sep 17 00:00:00 2001 From: drh <> Date: Sat, 5 Jul 2025 23:33:21 +0000 Subject: [PATCH] Enhance the EXISTS-to-JOIN optimization so that it works on EXISTS subqueries that do not have a WHERE clause, and so that it works on nested EXISTS subqueries. FossilOrigin-Name: c1d5295724f9cf7f49e0786d28016eff2d268a2b670f934d24c76787626089db --- manifest | 14 +++++++------- manifest.uuid | 2 +- src/select.c | 22 +++++++++++++++------- test/existsexpr.test | 2 +- 4 files changed, 24 insertions(+), 16 deletions(-) diff --git a/manifest b/manifest index 3afc7c8322..700cfd8a47 100644 --- a/manifest +++ b/manifest @@ -1,5 +1,5 @@ -C Merge\sfixes\sfrom\strunk\sinto\sthe\sempty-table-optimizations\sbranch -D 2025-07-04T14:24:18.375 +C Enhance\sthe\sEXISTS-to-JOIN\soptimization\sso\sthat\sit\sworks\son\sEXISTS\ssubqueries\nthat\sdo\snot\shave\sa\sWHERE\sclause,\sand\sso\sthat\sit\sworks\son\snested\sEXISTS\nsubqueries. +D 2025-07-05T23:33:21.216 F .fossil-settings/binary-glob 61195414528fb3ea9693577e1980230d78a1f8b0a54c78cf1b9b24d0a409ed6a x F .fossil-settings/empty-dirs dbb81e8fc0401ac46a1491ab34a7f2c7c0452f2f06b54ebb845d024ca8283ef1 F .fossil-settings/ignore-glob 35175cdfcf539b2318cb04a9901442804be81cd677d8b889fcc9149c21f239ea @@ -785,7 +785,7 @@ F src/printf.c 71b6d3a0093bf23f473e25480ca0024e8962681506c75f4ffd3d343a3f0ab113 F src/random.c 606b00941a1d7dd09c381d3279a058d771f406c5213c9932bbd93d5587be4b9c F src/resolve.c 5b14cad58bc21341fbaea76d7e781187559627a461745ece00c2655ba7c083ec F src/rowset.c 8432130e6c344b3401a8874c3cb49fefe6873fec593294de077afea2dce5ec97 -F src/select.c 6ce33294314f05898a35205566a7d7d5034f8a6f7ad9cb9e3804f4765a100290 +F src/select.c b1d4e1f21a32b4f33d3bf02c4167ac8da5aafb97210a0b13a9119d68183db37d F src/shell.c.in 73c0eeb7c265d59b99219d5aa055f412f07842088d8036b6d259927d85dd1bbf F src/sqlite.h.in 5c54f2461a1ea529bab8499148a2b238e2d4bb571d59e8ea5322d0c190abb693 F src/sqlite3.rc 015537e6ac1eec6c7050e17b616c2ffe6f70fca241835a84a4f0d5937383c479 @@ -1140,7 +1140,7 @@ F test/exclusive.test 7ff63be7503990921838d5c9f77f6e33e68e48ed1a9d48cd28745bf650 F test/exclusive2.test cd70b1d9c6fffd336f9795b711dcc5d9ceba133ad3f7001da3fda63615bdc91e F test/exec.test e949714dc127eaa5ecc7d723efec1ec27118fdd7 F test/exists.test 79a75323c78f02bbe9c251ea502a092f9ef63dac -F test/existsexpr.test d87e7ee394935f9b4a9a1a488f3faa55abd20dd85152efaf2034881a079c7ba1 +F test/existsexpr.test cefe49ffc2295281908e0e1dd6d289381d2c3bcbbabcf36cd799cedb0708c7e9 F test/existsexpr2.test dc23e76389eff3d29f6488ff733012a3560cd67ec8cfaecbecd52cced5d5af11 F test/existsfault.test ff41c11f3052c1bbd4f8dd557802310026253d67d7c4e3a180c16d2f0862973e F test/expr.test 4ada8eb822c45ef27a36851a258004d43c1e95e7c82585a1217e732084e4482c @@ -2211,8 +2211,8 @@ F tool/version-info.c 3b36468a90faf1bbd59c65fd0eb66522d9f941eedd364fabccd7227350 F tool/warnings-clang.sh bbf6a1e685e534c92ec2bfba5b1745f34fb6f0bc2a362850723a9ee87c1b31a7 F tool/warnings.sh 1ad0169b022b280bcaaf94a7fa231591be96b514230ab5c98fbf15cd7df842dd F tool/win/sqlite.vsix deb315d026cc8400325c5863eef847784a219a2f -P 872c41feddafcc21a02f1229ce017ceea9f8e309a4dd5b6e323477d67f975947 13af4acebe09b047756c22b800136cffaba532e7fcaa448a4edf4fedb94e9bbc -R bc9dd605b1b6086398bb04180450d2c6 +P 6f98b16d210a9f5b6ca4b4599e3dab3263eddbae7c70ddbcabf988f4a1014e8b +R 1a0b4833b41c9eafcebd2713b9567447 U drh -Z 9c89d6634e2f24fe30079554a53be2d7 +Z d76421db80a68ce3b2346aa4278aa4db # Remove this line to create a well-formed Fossil manifest. diff --git a/manifest.uuid b/manifest.uuid index 7864b0e031..24edc66d6f 100644 --- a/manifest.uuid +++ b/manifest.uuid @@ -1 +1 @@ -6f98b16d210a9f5b6ca4b4599e3dab3263eddbae7c70ddbcabf988f4a1014e8b +c1d5295724f9cf7f49e0786d28016eff2d268a2b670f934d24c76787626089db diff --git a/src/select.c b/src/select.c index 750de5e8ff..b15300237a 100644 --- a/src/select.c +++ b/src/select.c @@ -7418,11 +7418,17 @@ static int fromClauseTermCanBeCoroutine( ** ** SELECT name FROM sailors AS S, reserves AS R ** WHERE S.sid = R.sid AND R.day = '2022-10-25'; +** +** **Approximately**. Really, we have to ensure that the FROM-clause term +** that was formerly inside the EXISTS is only executed once. This is handled +** by setting the SrcItem.fg.fromExists flag, which then causes code in +** the where.c file to exit the corresponding loop after the first successful +** match (if any). */ static SQLITE_NOINLINE void existsToJoin( - Parse *pParse, - Select *p, - Expr *pWhere + Parse *pParse, /* Parsing context */ + Select *p, /* The SELECT statement being optimized */ + Expr *pWhere /* part of the WHERE clause currently being examined */ ){ if( pWhere && !ExprHasProperty(pWhere, EP_OuterON|EP_InnerON) @@ -7437,9 +7443,9 @@ static SQLITE_NOINLINE void existsToJoin( } else if( pWhere->op==TK_EXISTS ){ Select *pSub = pWhere->x.pSelect; + Expr *pSubWhere = pSub->pWhere; if( pSub->pSrc->nSrc==1 && (pSub->selFlags & SF_Aggregate)==0 - && pSub->pWhere && !pSub->pSrc->a[0].fg.isSubquery ){ memset(pWhere, 0, sizeof(*pWhere)); @@ -7451,9 +7457,10 @@ static SQLITE_NOINLINE void existsToJoin( pSub->pSrc->a[0].fg.fromExists = 1; pSub->pSrc->a[0].fg.jointype |= JT_CROSS; p->pSrc = sqlite3SrcListAppendList(pParse, p->pSrc, pSub->pSrc); - p->pWhere = sqlite3PExpr(pParse, TK_AND, p->pWhere, pSub->pWhere); - - pSub->pWhere = 0; + if( pSubWhere ){ + p->pWhere = sqlite3PExpr(pParse, TK_AND, p->pWhere, pSubWhere); + pSub->pWhere = 0; + } pSub->pSrc = 0; sqlite3ParserAddCleanup(pParse, sqlite3SelectDeleteGeneric, pSub); #if TREETRACE_ENABLED @@ -7463,6 +7470,7 @@ static SQLITE_NOINLINE void existsToJoin( sqlite3TreeViewSelect(0, p, 0); } #endif + existsToJoin(pParse, p, pSubWhere); } } } diff --git a/test/existsexpr.test b/test/existsexpr.test index 51b9234b7f..c28955b672 100644 --- a/test/existsexpr.test +++ b/test/existsexpr.test @@ -193,7 +193,7 @@ do_subquery_test 3.7 1 { 4 4 4 } -do_subquery_test 3.8 1 { +do_subquery_test 3.8 0 { SELECT * FROM y1 WHERE EXISTS ( SELECT a+1 FROM y2 ) } { 1 1 1 -- 2.39.5