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

1 files changed, 38 insertions, 0 deletions

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