Compute WhereLevel.addrBrk and .addrHalt early so that those labels can be

used to abort loops early.  Use this to improve performance on two more
of the cases described by [forum:/forumpost/52651713ac|forum post 52651713ac].

FossilOrigin-Name: 6fc0b9ac23be6840542982de4bb282ebca1db8b5ab3baefdde95a997c1506e81

5 files changed, 39 insertions, 21 deletions

diff --git a/manifest b/manifest
index 055652fcb..a99c60863 100644
--- a/manifest
+++ b/manifest
@@ -1,5 +1,5 @@
-C Strive\sto\sskip\sthe\sevaluation\sof\sscalar\ssubqueries\sthat\sare\spart\sof\sa\nlarger\sexpression\sif\sthe\sresult\sfrom\sthe\sscalar\ssubquery\sdoes\snot\schange\sthe\nresult\sof\sthe\soverall\sexpression.
-D 2025-06-30T16:41:40.414
+C Compute\sWhereLevel.addrBrk\sand\s.addrHalt\searly\sso\sthat\sthose\slabels\scan\sbe\nused\sto\sabort\sloops\searly.\s\sUse\sthis\sto\simprove\sperformance\son\stwo\smore\nof\sthe\scases\sdescribed\sby\s[forum:/forumpost/52651713ac|forum\spost\s52651713ac].
+D 2025-06-30T20:19:19.423
 F .fossil-settings/binary-glob 61195414528fb3ea9693577e1980230d78a1f8b0a54c78cf1b9b24d0a409ed6a x
 F .fossil-settings/empty-dirs dbb81e8fc0401ac46a1491ab34a7f2c7c0452f2f06b54ebb845d024ca8283ef1
 F .fossil-settings/ignore-glob 35175cdfcf539b2318cb04a9901442804be81cd677d8b889fcc9149c21f239ea
@@ -867,9 +867,9 @@ F src/vxworks.h d2988f4e5a61a4dfe82c6524dd3d6e4f2ce3cdb9
 F src/wal.c 20be6f0a25a80b7897cf2a5369bfd37ef198e6f0b6cdef16d83eee856056b159
 F src/wal.h ba252daaa94f889f4b2c17c027e823d9be47ce39da1d3799886bbd51f0490452
 F src/walker.c d5006d6b005e4ea7302ad390957a8d41ed83faa177e412f89bc5600a7462a014
-F src/where.c a99fa3061a0155d2cb0e2c91df76dbf834750272a8d79ec5e2dce3ed4e6abad6
-F src/whereInt.h 02b646ea41a8342815b3628f8064c32618ea2e0f20b83216ea08cad11f0ac5aa
-F src/wherecode.c 9710e62379c000189476404f923d4d1b192d0def222fdd287b820cc085a0d555
+F src/where.c baedbf818b67006033204fd9814bf1dc80bec728ed0340cf49146be36ddd5f40
+F src/whereInt.h 8d94cb116c9e06205c3d5ac87af065fc044f8cf08bfdccd94b6ea1c1308e65da
+F src/wherecode.c 0ee8afb68c04adaba81329f5c358010b2d8943d00a55161a34081acf830daa11
 F src/whereexpr.c 566ca4382e07a4ba1fd86c97ae0781cdf84004c7d9c59466bf5db75733548807
 F src/window.c d01227141f622f24fbe36ca105fbe6ef023f9fd98f1ccd65da95f88886565db5
 F test/8_3_names.test ebbb5cd36741350040fd28b432ceadf495be25b2
@@ -2208,9 +2208,11 @@ F tool/version-info.c 3b36468a90faf1bbd59c65fd0eb66522d9f941eedd364fabccd7227350
 F tool/warnings-clang.sh bbf6a1e685e534c92ec2bfba5b1745f34fb6f0bc2a362850723a9ee87c1b31a7
 F tool/warnings.sh 1ad0169b022b280bcaaf94a7fa231591be96b514230ab5c98fbf15cd7df842dd
 F tool/win/sqlite.vsix deb315d026cc8400325c5863eef847784a219a2f
-P b48d95191662e09659b5b55ae65cd462c9e1700c4f92dd9d40b59548f0797c02 d86eb16283c4b573c506d4faa422d5d9aeb6abc279d8e6a8e2104737162d417f
-R 9194e4789912b64a71b73947ca782430
-T +closed d86eb16283c4b573c506d4faa422d5d9aeb6abc279d8e6a8e2104737162d417f
+P 0083d5169a46104a25355bdd9d5a2f4027b049191ebda571dd228477ec217296
+R d7249f5cf7b6d480f743946f8ed64b97
+T *branch * empty-table-optimizations
+T *sym-empty-table-optimizations *
+T -sym-trunk *
 U drh
-Z 98e7e495521ce39084e96bf3212efc13
+Z 7f9eb68d1bb4b376d2d8134bae280e6b
 # Remove this line to create a well-formed Fossil manifest.
diff --git a/manifest.uuid b/manifest.uuid
index 2bedf7c1b..918ff018c 100644
--- a/manifest.uuid
+++ b/manifest.uuid
@@ -1 +1 @@
-0083d5169a46104a25355bdd9d5a2f4027b049191ebda571dd228477ec217296
+6fc0b9ac23be6840542982de4bb282ebca1db8b5ab3baefdde95a997c1506e81
diff --git a/src/where.c b/src/where.c
index 11e24a8d3..824cfcd8c 100644
--- a/src/where.c
+++ b/src/where.c
@@ -1201,7 +1201,9 @@ static SQLITE_NOINLINE void constructAutomaticIndex(
     VdbeCoverage(v);
     VdbeComment((v, "next row of %s", pSrc->pSTab->zName));
   }else{
-    addrTop = sqlite3VdbeAddOp1(v, OP_Rewind, pLevel->iTabCur); VdbeCoverage(v);
+    assert( pLevel->addrHalt );
+    addrTop = sqlite3VdbeAddOp2(v, OP_Rewind,pLevel->iTabCur,pLevel->addrHalt);
+    VdbeCoverage(v);
   }
   if( pPartial ){
     iContinue = sqlite3VdbeMakeLabel(pParse);
@@ -1229,11 +1231,14 @@ static SQLITE_NOINLINE void constructAutomaticIndex(
                           pSrc->u4.pSubq->regResult, pLevel->iIdxCur);
     sqlite3VdbeGoto(v, addrTop);
     pSrc->fg.viaCoroutine = 0;
+    sqlite3VdbeJumpHere(v, addrTop);
   }else{
     sqlite3VdbeAddOp2(v, OP_Next, pLevel->iTabCur, addrTop+1); VdbeCoverage(v);
     sqlite3VdbeChangeP5(v, SQLITE_STMTSTATUS_AUTOINDEX);
+    if( (pSrc->fg.jointype & JT_LEFT)!=0 ){
+      sqlite3VdbeJumpHere(v, addrTop);
+    }
   }
-  sqlite3VdbeJumpHere(v, addrTop);
   sqlite3ReleaseTempReg(pParse, regRecord);
  
   /* Jump here when skipping the initialization */
@@ -7070,6 +7075,12 @@ WhereInfo *sqlite3WhereBegin(
     pTab = pTabItem->pSTab;
     iDb = sqlite3SchemaToIndex(db, pTab->pSchema);
     pLoop = pLevel->pWLoop;
+    pLevel->addrBrk = sqlite3VdbeMakeLabel(pParse);
+    if( ii==0 || (pTabItem[0].fg.jointype & JT_LEFT)!=0 ){
+      pLevel->addrHalt = pLevel->addrBrk;
+    }else{
+      pLevel->addrHalt = pWInfo->a[ii-1].addrHalt;
+    }
     if( (pTab->tabFlags & TF_Ephemeral)!=0 || IsView(pTab) ){
       /* Do nothing */
     }else
@@ -7200,6 +7211,7 @@ WhereInfo *sqlite3WhereBegin(
      && (pLevel->pRJ = sqlite3WhereMalloc(pWInfo, sizeof(WhereRightJoin)))!=0
     ){
       WhereRightJoin *pRJ = pLevel->pRJ;
+      pLevel->addrHalt = pLevel->addrBrk;
       pRJ->iMatch = pParse->nTab++;
       pRJ->regBloom = ++pParse->nMem;
       sqlite3VdbeAddOp2(v, OP_Blob, 65536, pRJ->regBloom);
diff --git a/src/whereInt.h b/src/whereInt.h
index 3a9353e07..09e02c8c7 100644
--- a/src/whereInt.h
+++ b/src/whereInt.h
@@ -75,6 +75,7 @@ struct WhereLevel {
   int iTabCur;          /* The VDBE cursor used to access the table */
   int iIdxCur;          /* The VDBE cursor used to access pIdx */
   int addrBrk;          /* Jump here to break out of the loop */
+  int addrHalt;         /* Abort the query due to empty table or similar */
   int addrNxt;          /* Jump here to start the next IN combination */
   int addrSkip;         /* Jump here for next iteration of skip-scan */
   int addrCont;         /* Jump here to continue with the next loop cycle */
diff --git a/src/wherecode.c b/src/wherecode.c
index 9581ac389..c5fa7a213 100644
--- a/src/wherecode.c
+++ b/src/wherecode.c
@@ -1400,6 +1400,7 @@ static SQLITE_NOINLINE void filterPullDown(
   int addrNxt,         /* Jump here to bypass inner loops */
   Bitmask notReady     /* Loops that are not ready */
 ){
+  int saved_addrBrk;
   while( ++iLevel < pWInfo->nLevel ){
     WhereLevel *pLevel = &pWInfo->a[iLevel];
     WhereLoop *pLoop = pLevel->pWLoop;
@@ -1408,7 +1409,7 @@ static SQLITE_NOINLINE void filterPullDown(
     /*         ,--- Because sqlite3ConstructBloomFilter() has will not have set
     **  vvvvv--'    pLevel->regFilter if this were true. */
     if( NEVER(pLoop->prereq & notReady) ) continue;
-    assert( pLevel->addrBrk==0 );
+    saved_addrBrk = pLevel->addrBrk;
     pLevel->addrBrk = addrNxt;
     if( pLoop->wsFlags & WHERE_IPK ){
       WhereTerm *pTerm = pLoop->aLTerm[0];
@@ -1438,7 +1439,7 @@ static SQLITE_NOINLINE void filterPullDown(
       VdbeCoverage(pParse->pVdbe);
     }
     pLevel->regFilter = 0;
-    pLevel->addrBrk = 0;
+    pLevel->addrBrk = saved_addrBrk;
   }
 }
 
@@ -1485,7 +1486,6 @@ Bitmask sqlite3WhereCodeOneLoopStart(
   sqlite3 *db;                    /* Database connection */
   SrcItem *pTabItem;              /* FROM clause term being coded */
   int addrBrk;                    /* Jump here to break out of the loop */
-  int addrHalt;                   /* addrBrk for the outermost loop */
   int addrCont;                   /* Jump here to continue with next cycle */
   int iRowidReg = 0;        /* Rowid is stored in this register, if not zero */
   int iReleaseReg = 0;      /* Temp register to free before returning */
@@ -1529,7 +1529,7 @@ Bitmask sqlite3WhereCodeOneLoopStart(
   ** there are no IN operators in the constraints, the "addrNxt" label
   ** is the same as "addrBrk".
   */
-  addrBrk = pLevel->addrBrk = pLevel->addrNxt = sqlite3VdbeMakeLabel(pParse);
+  addrBrk = pLevel->addrNxt = pLevel->addrBrk;
   addrCont = pLevel->addrCont = sqlite3VdbeMakeLabel(pParse);
 
   /* If this is the right table of a LEFT OUTER JOIN, allocate and
@@ -1545,13 +1545,16 @@ Bitmask sqlite3WhereCodeOneLoopStart(
     VdbeComment((v, "init LEFT JOIN match flag"));
   }
 
-  /* Compute a safe address to jump to if we discover that the table for
-  ** this loop is empty and can never contribute content. */
+#ifdef SQLITE_DEBUG
+  /* Re-compute the address to jump to if we discover that the table for
+  ** this loop is empty and can never contribute content.  Verify that the
+  ** computation here agrees with the one in sqlite3WhereBegin(). */
   for(j=iLevel; j>0; j--){
     if( pWInfo->a[j].iLeftJoin ) break;
     if( pWInfo->a[j].pRJ ) break;
   }
-  addrHalt = pWInfo->a[j].addrBrk;
+  assert( pWInfo->a[j].addrBrk==pLevel->addrHalt );
+#endif
 
   /* Special case of a FROM clause subquery implemented as a co-routine */
   if( pTabItem->fg.viaCoroutine ){
@@ -1791,7 +1794,7 @@ Bitmask sqlite3WhereCodeOneLoopStart(
       VdbeCoverageIf(v, pX->op==TK_GE);
       sqlite3ReleaseTempReg(pParse, rTemp);
     }else{
-      sqlite3VdbeAddOp2(v, bRev ? OP_Last : OP_Rewind, iCur, addrHalt);
+      sqlite3VdbeAddOp2(v, bRev ? OP_Last : OP_Rewind, iCur, pLevel->addrHalt);
       VdbeCoverageIf(v, bRev==0);
       VdbeCoverageIf(v, bRev!=0);
     }
@@ -2586,7 +2589,7 @@ Bitmask sqlite3WhereCodeOneLoopStart(
       codeCursorHint(pTabItem, pWInfo, pLevel, 0);
       pLevel->op = aStep[bRev];
       pLevel->p1 = iCur;
-      pLevel->p2 = 1 + sqlite3VdbeAddOp2(v, aStart[bRev], iCur, addrHalt);
+      pLevel->p2 = 1 + sqlite3VdbeAddOp2(v, aStart[bRev],iCur,pLevel->addrHalt);
       VdbeCoverageIf(v, bRev==0);
       VdbeCoverageIf(v, bRev!=0);
       pLevel->p5 = SQLITE_STMTSTATUS_FULLSCAN_STEP;