Fix for [54844eea3f]: Do not create automatic indexes on correlated sub-queries.

FossilOrigin-Name: 9f14fa56ba31afe3de8b0cf26ed09573a2cc2283

4 files changed, 20 insertions, 0 deletions

diff --git a/src/expr.c b/src/expr.c
index be2f4d7c6..ab4547db9 100644
--- a/src/expr.c
+++ b/src/expr.c
@@ -902,6 +902,7 @@ SrcList *sqlite3SrcListDup(sqlite3 *db, SrcList *p, int flags){
     pNewItem->jointype = pOldItem->jointype;
     pNewItem->iCursor = pOldItem->iCursor;
     pNewItem->isPopulated = pOldItem->isPopulated;
+    pNewItem->isCorrelated = pOldItem->isCorrelated;
     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 74d6aaef9..d29d2a834 100644
--- a/src/resolve.c
+++ b/src/resolve.c
@@ -996,11 +996,25 @@ static int resolveSelectStep(Walker *pWalker, Select *p){
     for(i=0; i<p->pSrc->nSrc; i++){
       struct SrcList_item *pItem = &p->pSrc->a[i];
       if( pItem->pSelect ){
+        NameContext *pNC;         /* Used to iterate name contexts */
+        int nRef = 0;             /* Refcount for pOuterNC and outer contexts */
         const char *zSavedContext = pParse->zAuthContext;
+
+        /* Count the total number of references to pOuterNC and all of its
+        ** parent contexts. After resolving references to expressions in
+        ** pItem->pSelect, check if this value has changed. If so, then
+        ** SELECT statement pItem->pSelect must be correlated. Set the
+        ** pItem->isCorrelated flag if this is the case. */
+        for(pNC=pOuterNC; pNC; pNC=pNC->pNext) nRef += pNC->nRef;
+
         if( pItem->zName ) pParse->zAuthContext = pItem->zName;
         sqlite3ResolveSelectNames(pParse, pItem->pSelect, pOuterNC);
         pParse->zAuthContext = zSavedContext;
         if( pParse->nErr || db->mallocFailed ) return WRC_Abort;
+
+        for(pNC=pOuterNC; pNC; pNC=pNC->pNext) nRef -= pNC->nRef;
+        assert( pItem->isCorrelated==0 && nRef<=0 );
+        pItem->isCorrelated = (nRef!=0);
       }
     }
   
diff --git a/src/sqliteInt.h b/src/sqliteInt.h
index 9f47ffdde..bcf6a591a 100644
--- a/src/sqliteInt.h
+++ b/src/sqliteInt.h
@@ -1848,6 +1848,7 @@ struct SrcList {
     u8 isPopulated;   /* Temporary table associated with SELECT is populated */
     u8 jointype;      /* Type of join between this able and the previous */
     u8 notIndexed;    /* True if there is a NOT INDEXED clause */
+    u8 isCorrelated;  /* True if sub-query is correlated */
 #ifndef SQLITE_OMIT_EXPLAIN
     u8 iSelectId;     /* If pSelect!=0, the id of the sub-select in EQP */
 #endif
diff --git a/src/where.c b/src/where.c
index 05d955bad..918a3df5d 100644
--- a/src/where.c
+++ b/src/where.c
@@ -1919,6 +1919,10 @@ static void bestAutomaticIndex(
     /* The NOT INDEXED clause appears in the SQL. */
     return;
   }
+  if( pSrc->isCorrelated ){
+    /* The source is a correlated sub-query. No point in indexing it. */
+    return;
+  }
 
   assert( pParse->nQueryLoop >= (double)1 );
   pTable = pSrc->pTab;