From 90310f1b2cd9a8620a9f62c44c36490a3c43ed59 Mon Sep 17 00:00:00 2001
From: drh <>
Date: Fri, 14 Jun 2024 17:16:33 +0000
Subject: [PATCH] Do not omit the ORDER BY clause from a recursive CTE just
 because the outer query that uses the CTE also contains an ORDER BY clause.

FossilOrigin-Name: 5e700f2cc22bafa51928084454b374858eee3e2b00b58454cb97948d1691d276
---
 manifest      | 17 ++++++++++-------
 manifest.uuid |  2 +-
 src/select.c  |  5 ++++-
 3 files changed, 15 insertions(+), 9 deletions(-)

diff --git a/manifest b/manifest
index beca64a81a..80c30461e1 100644
--- a/manifest
+++ b/manifest
@@ -1,5 +1,5 @@
-C Do\snot\sattempt\sthe\sexists-to-join\soptimization\sfor\sexpressions\sfrom\sthe\sON\sclause\sof\sjoins.
-D 2024-06-12T17:01:00.926
+C Do\snot\somit\sthe\sORDER\sBY\sclause\sfrom\sa\srecursive\sCTE\sjust\sbecause\sthe\nouter\squery\sthat\suses\sthe\sCTE\salso\scontains\san\sORDER\sBY\sclause.
+D 2024-06-14T17:16:33.927
 F .fossil-settings/empty-dirs dbb81e8fc0401ac46a1491ab34a7f2c7c0452f2f06b54ebb845d024ca8283ef1
 F .fossil-settings/ignore-glob 35175cdfcf539b2318cb04a9901442804be81cd677d8b889fcc9149c21f239ea
 F LICENSE.md df5091916dbb40e6e9686186587125e1b2ff51f022cc334e886c19a0e9982724
@@ -755,7 +755,7 @@ F src/printf.c 8b250972305e14b365561be5117ed0fd364e4fd58968776df1ce64c6280b90f9
 F src/random.c 606b00941a1d7dd09c381d3279a058d771f406c5213c9932bbd93d5587be4b9c
 F src/resolve.c 9c7786f032dea81487e7d94cb17849936f0e9b8891bfc91a6ac24ab193762804
 F src/rowset.c 8432130e6c344b3401a8874c3cb49fefe6873fec593294de077afea2dce5ec97
-F src/select.c 757ea788e20f608d09bfef209d17bba87104cab920f80de77c92b38588ef64c5
+F src/select.c 501d7fece03f674bf071f3ad813fc670b1ed4e1ec36e646e4cc2b9289d8ab5db
 F src/shell.c.in ad27d1d990e9e5fb7ae8fc38a717e91f55233714f59723e5618baf4a2a3d2b65
 F src/sqlite.h.in cbd3e4177791a61c056fd81e37a5b21bb6c8cb2ea8cac558c625974673f50acf
 F src/sqlite3.rc 5121c9e10c3964d5755191c80dd1180c122fc3a8
@@ -2197,8 +2197,11 @@ F vsixtest/vsixtest.tcl 6a9a6ab600c25a91a7acc6293828957a386a8a93
 F vsixtest/vsixtest.vcxproj.data 2ed517e100c66dc455b492e1a33350c1b20fbcdc
 F vsixtest/vsixtest.vcxproj.filters 37e51ffedcdb064aad6ff33b6148725226cd608e
 F vsixtest/vsixtest_TemporaryKey.pfx e5b1b036facdb453873e7084e1cae9102ccc67a0
-P 33a3f327855b427ae6ba0057218d043a1417bc9d780728f47f23acdd836e1686
-R 25697789cb2a6d5a3b4b0fd17d4887cd
-U dan
-Z c7e8e1ad09260243da52b623b6dff2b7
+P 4666433cbd9af21c2e0440b10bcb39878624a39485e2bb514553b276acb8a401
+R 21b71d50ef4d42488cd0668a5b8420f0
+T *branch * recursive-cte-orderby-fix
+T *sym-recursive-cte-orderby-fix *
+T -sym-exists-to-join *
+U drh
+Z 7296e8a4128951f77ca981105e7dae8b
 # Remove this line to create a well-formed Fossil manifest.
diff --git a/manifest.uuid b/manifest.uuid
index 26bcc7370b..5947ef3279 100644
--- a/manifest.uuid
+++ b/manifest.uuid
@@ -1 +1 @@
-4666433cbd9af21c2e0440b10bcb39878624a39485e2bb514553b276acb8a401
\ No newline at end of file
+5e700f2cc22bafa51928084454b374858eee3e2b00b58454cb97948d1691d276
\ No newline at end of file
diff --git a/src/select.c b/src/select.c
index 7a789196b6..f1d116ccd9 100644
--- a/src/select.c
+++ b/src/select.c
@@ -7691,13 +7691,16 @@ int sqlite3Select(
     **            (a)  The outer query has a different ORDER BY clause
     **            (b)  The subquery is part of a join
     **          See forum post 062d576715d277c8
+    **    (6)   The subquery is not a recursive CTE.  ORDER BY has a different
+    **          meaning for recursive CTEs and this optimization does not
+    **          apply.
     **
     ** Also retain the ORDER BY if the OmitOrderBy optimization is disabled.
     */
     if( pSub->pOrderBy!=0
      && (p->pOrderBy!=0 || pTabList->nSrc>1)      /* Condition (5) */
      && pSub->pLimit==0                           /* Condition (1) */
-     && (pSub->selFlags & SF_OrderByReqd)==0      /* Condition (2) */
+     && (pSub->selFlags & (SF_OrderByReqd|SF_Recursive))==0  /* (2) and (6) */
      && (p->selFlags & SF_OrderByReqd)==0         /* Condition (3) and (4) */
      && OptimizationEnabled(db, SQLITE_OmitOrderBy)
     ){
-- 
2.47.2