From 1dbd3856b5cee452c739e3d624d481df93310e0d Mon Sep 17 00:00:00 2001
From: "D. Richard Hipp" <drh@hwaci.com>
Date: Tue, 19 Mar 2024 16:34:32 +0000
Subject: [PATCH] In the name resolver when SQLITE_ALLOW_ROWID_IN_INDEX is
 enabled, if there are multiple views that might resolve to the "rowid" but
 only one real table, then use that one real table and ignore the views.

---
 src/ctime.c        |  3 ---
 src/resolve.c      | 29 +++++++++++++++++++++++++++++
 test/unionall.test |  2 +-
 3 files changed, 30 insertions(+), 4 deletions(-)

diff --git a/src/ctime.c b/src/ctime.c
index 0ffe2a5bdf..cf761299fe 100644
--- a/src/ctime.c
+++ b/src/ctime.c
@@ -65,9 +65,6 @@ static const char * const sqlite3azCompileOpt[] = {
   "ALLOW_COVERING_INDEX_SCAN=" CTIMEOPT_VAL(SQLITE_ALLOW_COVERING_INDEX_SCAN),
 # endif
 #endif
-#ifdef SQLITE_ALLOW_ROWID_IN_VIEW
-  "ALLOW_ROWID_IN_VIEW",
-#endif
 #ifdef SQLITE_ALLOW_URI_AUTHORITY
   "ALLOW_URI_AUTHORITY",
 #endif
diff --git a/src/resolve.c b/src/resolve.c
index c2957a870a..2971936105 100644
--- a/src/resolve.c
+++ b/src/resolve.c
@@ -469,8 +469,37 @@ static int lookupName(
           }
         }
         if( 0==cnt && VisibleRowid(pTab) ){
+          /* pTab is a potential ROWID match.  Keep track of it and match
+          ** the ROWID later if that seems appropriate.  (Search for "cntTab"
+          ** to find related code.)  Only allow a ROWID match if there is
+          ** a single ROWID match candidate.
+          */
+#ifdef SQLITE_ALLOW_ROWID_IN_VIEW
+          /* In SQLITE_ALLOW_ROWID_IN_VIEW mode, allow a ROWID match
+          ** if there is a single VIEW candidate or if there is a single
+          ** non-VIEW candidate plus multiple VIEW candidates.  In other
+          ** words non-VIEW candidate terms take precedence over VIEWs.
+          */
+          if( cntTab==0
+           || (cntTab==1
+               && ALWAYS(pMatch!=0)
+               && ALWAYS(pMatch->pTab!=0)
+               && (pMatch->pTab->tabFlags & TF_Ephemeral)!=0
+               && (pTab->tabFlags & TF_Ephemeral)==0)
+          ){
+            cntTab = 1;
+            pMatch = pItem;
+          }else{
+            cntTab++;
+          }
+#else
+          /* The (much more common) non-SQLITE_ALLOW_ROWID_IN_VIEW case is
+          ** simpler since we require exactly one candidate, which will
+          ** always be a non-VIEW
+          */
           cntTab++;
           pMatch = pItem;
+#endif
         }
       }
       if( pMatch ){
diff --git a/test/unionall.test b/test/unionall.test
index 99cb48a259..ae1a271bfc 100644
--- a/test/unionall.test
+++ b/test/unionall.test
@@ -351,7 +351,7 @@ do_catchsql_test 5.30 {
   SELECT * FROM (t1 NATURAL JOIN pragma_table_xinfo('t1_a') NATURAL JOIN t3) t1
                 NATURAL JOIN t2 NATURAL JOIN t3
    WHERE rowid ISNULL>0 AND 0%y;
-} {1 {no such column: rowid}}
+} {/1 {no such column: [a-z]+}/}
 }
 
 reset_db
-- 
2.11.4.GIT