Index: src/expr.c ================================================================== --- src/expr.c +++ src/expr.c @@ -900,10 +900,11 @@ pNewItem->zName = sqlite3DbStrDup(db, pOldItem->zName); pNewItem->zAlias = sqlite3DbStrDup(db, pOldItem->zAlias); 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; pTab = pNewItem->pTab = pOldItem->pTab; if( pTab ){ Index: src/resolve.c ================================================================== --- src/resolve.c +++ src/resolve.c @@ -994,15 +994,29 @@ /* Recursively resolve names in all subqueries */ for(i=0; ipSrc->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); } } /* If there are no aggregate functions in the result-set, and no GROUP BY ** expression, do not allow aggregates in any of the other expressions. Index: src/sqliteInt.h ================================================================== --- src/sqliteInt.h +++ src/sqliteInt.h @@ -1846,10 +1846,11 @@ Table *pTab; /* An SQL table corresponding to zName */ Select *pSelect; /* A SELECT statement used in place of a table name */ 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 int iCursor; /* The VDBE cursor number used to access this table */ Expr *pOn; /* The ON clause of a join */ Index: src/where.c ================================================================== --- src/where.c +++ src/where.c @@ -1916,10 +1916,14 @@ return; } if( pSrc->notIndexed ){ /* 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; nTableRow = pTable->nRowEst; ADDED test/tkt-54844eea3f.test Index: test/tkt-54844eea3f.test ================================================================== --- /dev/null +++ test/tkt-54844eea3f.test @@ -0,0 +1,67 @@ +# 2011 July 8 +# +# 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. The +# focus of this file is testing that bug [54844eea3f] has been fixed. +# + +set testdir [file dirname $argv0] +source $testdir/tester.tcl + +set ::testprefix tkt-54844eea3f + +do_test 1.0 { + execsql { + CREATE TABLE t1(a INTEGER PRIMARY KEY); + INSERT INTO t1 VALUES(1); + INSERT INTO t1 VALUES(4); + + CREATE TABLE t2(b INTEGER PRIMARY KEY); + INSERT INTO t2 VALUES(1); + INSERT INTO t2 VALUES(2); + INSERT INTO t2 SELECT b+2 FROM t2; + INSERT INTO t2 SELECT b+4 FROM t2; + INSERT INTO t2 SELECT b+8 FROM t2; + INSERT INTO t2 SELECT b+16 FROM t2; + + CREATE TABLE t3(c INTEGER PRIMARY KEY); + INSERT INTO t3 VALUES(1); + INSERT INTO t3 VALUES(2); + INSERT INTO t3 VALUES(3); + } +} {} + +do_test 1.1 { + execsql { + SELECT 'test-2', t3.c, ( + SELECT count(*) + FROM t1 JOIN (SELECT DISTINCT t3.c AS p FROM t2) AS x ON t1.a=x.p + ) + FROM t3; + } +} {test-2 1 1 test-2 2 0 test-2 3 0} + +do_test 1.2 { + execsql { + CREATE TABLE t4(a, b, c); + INSERT INTO t4 VALUES('a', 1, 'one'); + INSERT INTO t4 VALUES('a', 2, 'two'); + INSERT INTO t4 VALUES('b', 1, 'three'); + INSERT INTO t4 VALUES('b', 2, 'four'); + SELECT ( + SELECT c FROM ( + SELECT * FROM t4 WHERE a=out.a ORDER BY b LIMIT 10 OFFSET 1 + ) WHERE b=out.b + ) FROM t4 AS out; + } +} {{} two {} four} + + +finish_test