From d9b8237b74ab2168bf8f62824cae8ede753c8ebc Mon Sep 17 00:00:00 2001
From: drh <>
Date: Fri, 22 Aug 2025 22:02:11 +0000
Subject: [PATCH] Disable the query flattener if the subquery is a join and the
 subquery is anywhere to the right of a RIGHT JOIN and if there is an ON
 clause on the subquery.

FossilOrigin-Name: 7776ab3f5a2ae2b25686f57500ea6045f3b38df938a4261b918400b3dabad7e8
---
 manifest        | 16 +++++----
 manifest.uuid   |  2 +-
 src/select.c    |  9 +++++
 test/joinI.test | 96 +++++++++++++++++++++++++++++++++++++++++++++++++
 4 files changed, 116 insertions(+), 7 deletions(-)
 create mode 100644 test/joinI.test

diff --git a/manifest b/manifest
index 06ba95ff12..f3b60c846e 100644
--- a/manifest
+++ b/manifest
@@ -1,5 +1,5 @@
-C Fix\sthe\ssqlite3SetJoinExpr()\sroutine\sso\sthat\sit\scorrectly\ssets\sthe\njoin\sflags\son\sCASE\sexpressions.
-D 2025-08-22T21:46:22.222
+C Disable\sthe\squery\sflattener\sif\sthe\ssubquery\sis\sa\sjoin\sand\sthe\ssubquery\nis\sanywhere\sto\sthe\sright\sof\sa\sRIGHT\sJOIN\sand\sif\sthere\sis\san\sON\sclause\non\sthe\ssubquery.
+D 2025-08-22T22:02:11.709
 F .fossil-settings/binary-glob 61195414528fb3ea9693577e1980230d78a1f8b0a54c78cf1b9b24d0a409ed6a x
 F .fossil-settings/empty-dirs dbb81e8fc0401ac46a1491ab34a7f2c7c0452f2f06b54ebb845d024ca8283ef1
 F .fossil-settings/ignore-glob 35175cdfcf539b2318cb04a9901442804be81cd677d8b889fcc9149c21f239ea
@@ -741,7 +741,7 @@ F src/printf.c 5f0c957af9699e849d786e8fbaa3baab648ca5612230dc17916434c14bc8698f
 F src/random.c 606b00941a1d7dd09c381d3279a058d771f406c5213c9932bbd93d5587be4b9c
 F src/resolve.c f8d1d011aba0964ff1bdccd049d4d2c2fec217efd90d202a4bb775e926b2c25d
 F src/rowset.c 8432130e6c344b3401a8874c3cb49fefe6873fec593294de077afea2dce5ec97
-F src/select.c 5cf61d7633a0f94772f8b06484f23745d7dcd58ef8eabe944d0cc4c8bd06d235
+F src/select.c 71ac1e6f0e25f52627acd5f76e775cc58e6d150ff46e0e1bbd4a8c934b3e7c62
 F src/shell.c.in 0636915df0dbac6c780f04959f5d1055f206fb281b2c8fc8b113fe7bfc7d44ef
 F src/sqlite.h.in ebfc0358de0e18aabee7fa918f2f846894e23bebc74160fbe265c99046ee61b8
 F src/sqlite3.rc 015537e6ac1eec6c7050e17b616c2ffe6f70fca241835a84a4f0d5937383c479
@@ -1335,6 +1335,7 @@ F test/joinD.test 2ce62e7353a0702ca5e70008faf319c1d4686aa19fba34275c6d1da0e960be
 F test/joinE.test d5d182f3812771e2c0d97c9dcf5dbe4c41c8e21c82560e59358731c4a3981d6b
 F test/joinF.test 53dd66158806823ea680dd7543b5406af151b5aafa5cd06a7f3231cd94938127
 F test/joinH.test fd76024ff104baec16417db5cafc0894ad4e0863e70803e63c1bba0322706339
+F test/joinI.test 78cae6f746a78f32d8aedb8cf7b93bcd802c245db0986330844dfb1a6700ebab
 F test/journal1.test bc61a4228db11bffca118bd358ba4b868524bf080f3532749de6c539656e20fa
 F test/journal2.test 9dac6b4ba0ca79c3b21446bbae993a462c2397c4
 F test/journal3.test e5aeff93a7776cf644dbc48dec277655cff80a1cd24689036abc87869b120ea6
@@ -2169,8 +2170,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 3e285bc117bcbfafe083709d27cca776d6ba418a9f0f58a6d4178035fba2d6ca
-R 3080879caa875309d9d32881ae580b9c
+P a4107c947c03ed48de3ab3f516cec8a3c092cec8ee19145fb564152e5cd16c85
+R 710203d1160026cd91e17ea747025dc3
+T *branch * on-clause-option-4
+T *sym-on-clause-option-4 *
+T -sym-trunk *
 U drh
-Z 336a8d61927c5ac03876bfa813bc54f6
+Z 74f7441c4400484a4d62f492ad0af3ec
 # Remove this line to create a well-formed Fossil manifest.
diff --git a/manifest.uuid b/manifest.uuid
index 00a3c6ab7e..d3181e97ab 100644
--- a/manifest.uuid
+++ b/manifest.uuid
@@ -1 +1 @@
-a4107c947c03ed48de3ab3f516cec8a3c092cec8ee19145fb564152e5cd16c85
+7776ab3f5a2ae2b25686f57500ea6045f3b38df938a4261b918400b3dabad7e8
diff --git a/src/select.c b/src/select.c
index 606bac98a4..6a9faea3f5 100644
--- a/src/select.c
+++ b/src/select.c
@@ -4401,6 +4401,9 @@ static int compoundHasDifferentAffinities(Select *p){
 **  (28)  The subquery is not a MATERIALIZED CTE.  (This is handled
 **        in the caller before ever reaching this routine.)
 **
+**  (29)  The subquery may not be to the right of a RIGHT JOIN if the
+**        subquery is a join and if there is an ON clause on the subquery.
+**
 **
 ** In this routine, the "p" parameter is a pointer to the outer query.
 ** The subquery is p->pSrc->a[iFrom].  isAgg is true if the outer query
@@ -4464,6 +4467,12 @@ static int flattenSubquery(
     return 0;                                            /* Restriction (15) */
   }
   if( pSubSrc->nSrc==0 ) return 0;                       /* Restriction (7)  */
+  if( pSubSrc->nSrc>1
+   && pSubitem->fg.isOn
+   && (pSrc->a[0].fg.jointype & JT_LTORJ)!=0
+  ){
+    return 0;                                            /* Restriction (29) */
+  }
   if( pSub->selFlags & SF_Distinct ) return 0;           /* Restriction (4)  */
   if( pSub->pLimit && (pSrc->nSrc>1 || isAgg) ){
      return 0;         /* Restrictions (8)(9) */
diff --git a/test/joinI.test b/test/joinI.test
new file mode 100644
index 0000000000..a422f19470
--- /dev/null
+++ b/test/joinI.test
@@ -0,0 +1,96 @@
+# 2022 May 17
+#
+# The author disclaims copyright to this source code.  In place of
+# a legal notice, here is a blessing:
+#
+#    May you do good and not evil.
+#    May you find forgiveness for yourself and forgive others.
+#    May you share freely, never taking more than you give.
+#
+#***********************************************************************
+# This file implements regression tests for SQLite library.
+#
+
+set testdir [file dirname $argv0]
+source $testdir/tester.tcl
+set testprefix joinI
+
+do_execsql_test 1.0 {
+  CREATE TABLE t1(a INT);
+  CREATE TABLE t2(b INT);
+  CREATE TABLE t3(c INT);
+}
+
+foreach {tn sql} {
+  1 "SELECT * FROM t1 RIGHT JOIN t2 ON t2.b=t3.c CROSS JOIN t3"
+  2 "SELECT * FROM t1 RIGHT JOIN t2 ON t2.b=(SELECT t3.c) CROSS JOIN t3"
+  3 "SELECT * FROM t1 RIGHT JOIN t2 ON CASE WHEN t2.b THEN t3.c ELSE 1 END CROSS JOIN t3"
+} {
+  do_catchsql_test 1.1.$tn $sql {1 {ON clause references tables to its right}}
+}
+
+
+#-------------------------------------------------------------------------
+reset_db
+
+do_execsql_test 2.0 {
+  CREATE TABLE t0(c0 INT, c1 INT);
+  CREATE TABLE t1 (c0 INT);
+
+  CREATE VIEW v1(c0) AS SELECT t0.c0 FROM t0 NATURAL RIGHT JOIN t1;
+  CREATE VIEW v2(c0) AS SELECT 0 FROM v1;
+
+  INSERT INTO t0(c0, c1) VALUES (-1, 0);
+  INSERT INTO t1(c0) VALUES (NULL);
+
+  SELECT * FROM v1 INNER JOIN (v2 CROSS JOIN t0) ON (t0.c0 < t0.c1);
+} {{} 0 -1 0}
+
+#-------------------------------------------------------------------------
+reset_db
+do_execsql_test 3.0 {
+  CREATE TABLE t0(c0, c1);
+  CREATE TABLE t1(v);
+  CREATE TABLE t2(w);
+  CREATE TABLE t3(x);
+  CREATE TABLE t4(y);
+  CREATE TABLE t5(z);
+}
+
+do_execsql_test 3.1 {
+  SELECT 1234 FROM t4 
+  RIGHT JOIN t5 
+  CROSS JOIN (t2 CROSS JOIN t0) AS a1 ON (a1.c0 < a1.c1);
+}
+
+do_execsql_test 3.2 {
+  SELECT 1234 FROM t4 
+  RIGHT JOIN t5 
+  CROSS JOIN (t2 CROSS JOIN t1 CROSS JOIN t0) AS a1 ON (a1.c0 < a1.c1);
+}
+
+do_execsql_test 3.3 {
+  SELECT 5678 FROM t0 RIGHT JOIN t1 ON (
+    SELECT 1 FROM t2 RIGHT JOIN t3 ON t2.w
+  ) CROSS JOIN t4;
+}
+
+do_catchsql_test 3.4 {
+  SELECT 5678 FROM t0 RIGHT JOIN t1 ON (
+    SELECT 1 FROM t2 RIGHT JOIN t3 ON t4.y
+  ) CROSS JOIN t4;
+} {1 {ON clause references tables to its right}}
+
+do_execsql_test 3.5 {
+  SELECT 5678 FROM t0 RIGHT JOIN t1 ON (
+    SELECT 1 FROM t2 RIGHT JOIN t3 ON 0=t1.v
+  ) CROSS JOIN t4;
+}
+
+do_catchsql_test 3.6 {
+  SELECT 5678 FROM t0 RIGHT JOIN t1 ON (
+    SELECT 1 FROM t2 RIGHT JOIN t3 ON max(0,t5.z) CROSS JOIN t5
+  ) CROSS JOIN t4;
+} {1 {ON clause references tables to its right}}
+
+finish_test
-- 
2.47.3