Improved estimate on the number of output rows in a recursive common table expression

that uses the UNION operator.  Follow-up to [f911f1c4977fbcae] and it's output row
estimate changes associated with DISTINCT queries.

FossilOrigin-Name: 92513f6bce7ecc778e78af3a77bc66ff65585fffa2e3138f8acb9e617121eeb0

5 files changed, 56 insertions, 11 deletions

diff --git a/manifest b/manifest
index 5b3894978..8dba5dbc7 100644
--- a/manifest
+++ b/manifest
@@ -1,5 +1,5 @@
-C JNI:\spart\s3\s(of\s3)\sof\stypos\sand\sJava\sstyle\stweaks\ssuggested\sin\s[forum:99ac7961d82f57f3|forum\spost\s99ac7961d82f57f3].\sTested\swith\sjdk\sv8\sand\sv21.
-D 2025-04-14T13:31:18.787
+C Improved\sestimate\son\sthe\snumber\sof\soutput\srows\sin\sa\srecursive\scommon\stable\sexpression\nthat\suses\sthe\sUNION\soperator.\s\sFollow-up\sto\s[f911f1c4977fbcae]\sand\sit's\soutput\srow\nestimate\schanges\sassociated\swith\sDISTINCT\squeries.
+D 2025-04-14T19:43:30.136
 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 33fc0d7643c848a098afdcb6e1db6de12379d47084b1cd0912cfce1d09345e44
 F src/random.c 606b00941a1d7dd09c381d3279a058d771f406c5213c9932bbd93d5587be4b9c
 F src/resolve.c 20e1fbe8f840ffc0cd835e33f68a802a22e34faa918d7a269f3de242fda02f99
 F src/rowset.c 8432130e6c344b3401a8874c3cb49fefe6873fec593294de077afea2dce5ec97
-F src/select.c 716a49f7f6348771442b8dae47863bad8ddbd8d5ae9a0d82d0eeb1852aea1cf7
+F src/select.c 1a5956231f7c57571288eaad61e5c37aaf0f3acb5c8a5ea0b896938166b62fa2
 F src/shell.c.in 1e8b9bf369e80cdf9b029142e773038bc12bd38aea1c56df4af6bf7b46cae955
 F src/sqlite.h.in 22882ddd3a70751aa8864c81993ee4562ed54c2c508b6270f75e223ffee38e1b
 F src/sqlite3.rc 015537e6ac1eec6c7050e17b616c2ffe6f70fca241835a84a4f0d5937383c479
@@ -869,7 +869,7 @@ F src/vxworks.h d2988f4e5a61a4dfe82c6524dd3d6e4f2ce3cdb9
 F src/wal.c bcf40795a09b699ad7e42624dd6282b13335164fbabcd5a98a717758cebef451
 F src/wal.h ba252daaa94f889f4b2c17c027e823d9be47ce39da1d3799886bbd51f0490452
 F src/walker.c d5006d6b005e4ea7302ad390957a8d41ed83faa177e412f89bc5600a7462a014
-F src/where.c e80177e452b4e436abc6ece0cb0249631000434f2a7425cc1df709015fce74ad
+F src/where.c 85690096a475ace0a2f5154be5341e3367f4ad8fda349a213702d2aff2d41d88
 F src/whereInt.h ecdbfb5551cf394f04ec7f0bc7ad963146d80eee3071405ac29aa84950128b8e
 F src/wherecode.c 6a3749575bcd02c7243ed89bde8a222525a60b56ae12ab78d997d64de346465c
 F src/whereexpr.c 2415c8eee5ff89a8b709d7d83d71c1ff986cd720d0520057e1d8a5371339012a
@@ -1705,7 +1705,7 @@ F test/stmtvtab1.test 6873dfb24f8e79cbb5b799b95c2e4349060eb7a3b811982749a84b3594
 F test/strict1.test 4d2b492152b984fd7e8196d23eb88e2ccb0ef9e46ca2f96c2ce7147ceef9d168
 F test/strict2.test b22c7a98b5000aef937f1990776497f0e979b1a23bc4f63e2d53b00e59b20070
 F test/subjournal.test 8d4e2572c0ee9a15549f0d8e40863161295107e52f07a3e8012a2e1fdd093c49
-F test/subquery.test 903abf41049f8404256f7be24b3151328304a5b25162e17ab0079460237382fc
+F test/subquery.test 23087f9b1c15ab9cc5231d04946bdebc51db527c95eb9d7434a2222127e17a84
 F test/subquery2.test 5f06ec2dbce42a3f595ab1b73b146592f9ce001cd4ff023d887d643d3560c281
 F test/subselect.test 0966aa8e720224dbd6a5e769a3ec2a723e332303
 F test/substr.test a673e3763e247e9b5e497a6cacbaf3da2bd8ec8921c0677145c109f2e633f36b
@@ -2216,8 +2216,8 @@ F tool/version-info.c 3b36468a90faf1bbd59c65fd0eb66522d9f941eedd364fabccd7227350
 F tool/warnings-clang.sh bbf6a1e685e534c92ec2bfba5b1745f34fb6f0bc2a362850723a9ee87c1b31a7
 F tool/warnings.sh 49a486c5069de041aedcbde4de178293e0463ae9918ecad7539eedf0ec77a139
 F tool/win/sqlite.vsix deb315d026cc8400325c5863eef847784a219a2f
-P 5e6e9aee5b43e6ae98e78293a0da30e82ad18c3c9c03fc6298240233ddadb2a2
-R 78446db09ad20d0a658adecb52899e38
-U stephan
-Z 221b4fcdf4c37393a995132b1f7d661d
+P 914d4c9d6c26536ca14be80eee6c54af4311eac6bf88b327738075275f4b77da
+R fee16bedc2936fcf762a590910cd0015
+U drh
+Z cac2b67ac54b69b59bd81ffa4eaf41eb
 # Remove this line to create a well-formed Fossil manifest.
diff --git a/manifest.uuid b/manifest.uuid
index 300d1235d..d4ba1305b 100644
--- a/manifest.uuid
+++ b/manifest.uuid
@@ -1 +1 @@
-914d4c9d6c26536ca14be80eee6c54af4311eac6bf88b327738075275f4b77da
+92513f6bce7ecc778e78af3a77bc66ff65585fffa2e3138f8acb9e617121eeb0
diff --git a/src/select.c b/src/select.c
index 91690c327..6e8ee58bc 100644
--- a/src/select.c
+++ b/src/select.c
@@ -8119,6 +8119,12 @@ int sqlite3Select(
     if( pWInfo==0 ) goto select_end;
     if( sqlite3WhereOutputRowCount(pWInfo) < p->nSelectRow ){
       p->nSelectRow = sqlite3WhereOutputRowCount(pWInfo);
+      if( pDest->eDest<=SRT_DistQueue && pDest->eDest>=SRT_DistFifo ){
+        /* TUNING: For a UNION CTE, because UNION is implies DISTINCT,
+        ** reduce the estimated output row count by 8 (LogEst 30). 
+        ** Search for tag-20250414a to see other cases */
+        p->nSelectRow -= 30;
+      }
     }
     if( sDistinct.isTnct && sqlite3WhereIsDistinct(pWInfo) ){
       sDistinct.eTnctType = sqlite3WhereIsDistinct(pWInfo);
diff --git a/src/where.c b/src/where.c
index ed6320349..3320f6855 100644
--- a/src/where.c
+++ b/src/where.c
@@ -6934,7 +6934,8 @@ WhereInfo *sqlite3WhereBegin(
     }
 
     /* TUNING:  Assume that a DISTINCT clause on a subquery reduces
-    ** the output size by a factor of 8 (LogEst -30).
+    ** the output size by a factor of 8 (LogEst -30).  Search for
+    ** tag-20250414a to see other cases.
     */
     if( (pWInfo->wctrlFlags & WHERE_WANT_DISTINCT)!=0 ){
       WHERETRACE(0x0080,("nRowOut reduced from %d to %d due to DISTINCT\n",
diff --git a/test/subquery.test b/test/subquery.test
index 17061d4b6..898c8f756 100644
--- a/test/subquery.test
+++ b/test/subquery.test
@@ -650,6 +650,44 @@ do_eqp_test subquery-10.2 {
 # |--SCAN v2
 # `--SEARCH t1 USING INDEX x12 (aa=?)
 #
+#---------------------------------------------------------------------------
+# Follow-up on 2025-04-14.  Performance issue found while working
+# on Fossil (Fossil check-in 2025-04-13T19:54).
+#
+do_execsql_test 10.3 {
+  CREATE TABLE blob(
+    rid INTEGER PRIMARY KEY,
+    size INT,
+    uuid TEXT
+  );
+  CREATE TABLE delta(
+    rid INTEGER PRIMARY KEY,
+    srcid INT
+  );
+  CREATE INDEX delta_i1 ON delta(srcid);
+}
+do_eqp_test subquery-10.4 {
+  WITH RECURSIVE deltasof(rid) AS (
+    SELECT rid FROM delta WHERE srcid=125020
+    UNION
+    SELECT delta.rid FROM deltasof, delta
+     WHERE delta.srcid=deltasof.rid)
+  SELECT deltasof.rid, blob.uuid FROM deltasof, blob
+   WHERE blob.rid=deltasof.rid;
+} {
+  QUERY PLAN
+  |--CO-ROUTINE deltasof
+  |  |--SETUP
+  |  |  `--SEARCH delta USING COVERING INDEX delta_i1 (srcid=?)
+  |  `--RECURSIVE STEP
+  |     |--SCAN deltasof
+  |     `--SEARCH delta USING COVERING INDEX delta_i1 (srcid=?)
+  |--SCAN deltasof
+  `--SEARCH blob USING INTEGER PRIMARY KEY (rowid=?)
+}
+# ^^^^^^^^^^^^^^^^^^
+# deltasof should be the outer loop and blob the inner loop
+# Prior to the fix, SQLite was doing it the other way around.
 
 #-----------------------------------------------------------------------------
 # 2024-04-25 Column affinities for columns of compound subqueries