When resolving names, consider a reference to a recursive CTE column as equivalent to a reference to the outermost name-context. This ensures that correlated sub-queries are correctly identified as such.

FossilOrigin-Name: 61be2da0ae623c1572819481508b044e9d32f294

4 files changed, 12 insertions, 0 deletions

diff --git a/src/expr.c b/src/expr.c
index fca03a8d9..9a288b73b 100644
--- a/src/expr.c
+++ b/src/expr.c
@@ -998,6 +998,7 @@ SrcList *sqlite3SrcListDup(sqlite3 *db, SrcList *p, int flags){
     pNewItem->regReturn = pOldItem->regReturn;
     pNewItem->isCorrelated = pOldItem->isCorrelated;
     pNewItem->viaCoroutine = pOldItem->viaCoroutine;
+    pNewItem->isRecursive = pOldItem->isRecursive;
     pNewItem->zIndex = sqlite3DbStrDup(db, pOldItem->zIndex);
     pNewItem->notIndexed = pOldItem->notIndexed;
     pNewItem->pIndex = pOldItem->pIndex;
diff --git a/src/resolve.c b/src/resolve.c
index b0adb8629..d54442e79 100644
--- a/src/resolve.c
+++ b/src/resolve.c
@@ -502,6 +502,14 @@ lookupname_end:
     if( pExpr->op!=TK_AS ){
       sqlite3AuthRead(pParse, pExpr, pSchema, pNC->pSrcList);
     }
+
+    /* If this expression reads a column value from a recursive CTE 
+    ** reference, then this is equivalent to reading from the outermost
+    ** available name-context.  */
+    if( pMatch && pMatch->isRecursive ){
+      while( pNC->pNext ) pNC = pNC->pNext;
+    }
+
     /* Increment the nRef value on all name contexts from TopNC up to
     ** the point where the name matched. */
     for(;;){
diff --git a/src/select.c b/src/select.c
index 34a2297c9..3f9f2de55 100644
--- a/src/select.c
+++ b/src/select.c
@@ -3551,7 +3551,9 @@ static int withExpand(
 
   if( pCte==pParse->pCte && (pTab = pCte->pTab) ){
     /* This is the recursive part of a recursive CTE */
+    assert( pFrom->pTab==0 && pFrom->isRecursive==0 );
     pFrom->pTab = pTab;
+    pFrom->isRecursive = 1;
     pTab->nRef++;
   }else{
     ExprList *pEList;
diff --git a/src/sqliteInt.h b/src/sqliteInt.h
index c7f0609ba..c52013f87 100644
--- a/src/sqliteInt.h
+++ b/src/sqliteInt.h
@@ -2019,6 +2019,7 @@ struct SrcList {
     unsigned notIndexed :1;    /* True if there is a NOT INDEXED clause */
     unsigned isCorrelated :1;  /* True if sub-query is correlated */
     unsigned viaCoroutine :1;  /* Implemented as a co-routine */
+    unsigned isRecursive :1;   /* True for recursive reference in WITH */
 #ifndef SQLITE_OMIT_EXPLAIN
     u8 iSelectId;     /* If pSelect!=0, the id of the sub-select in EQP */
 #endif