/ Check-in [9f14fa56]
Login

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
Comment:Fix for [54844eea3f]: Do not create automatic indexes on correlated sub-queries.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 9f14fa56ba31afe3de8b0cf26ed09573a2cc2283
User & Date: dan 2011-07-08 16:10:54
References
2011-07-12
14:48
Cherrypick change [9f14fa56ba] (the fix for [54844eea3f]) from the trunk. check-in: 7afb2354 user: dan tags: branch-3.7.2
Context
2011-07-08
17:02
Change the default chunk size on test_multiplex.c to 2147418112 bytes (formerly 1073741824 bytes) and make the default configurable at compile-time using SQLITE_MULTIPLEX_CHUNK_SIZE and at run-time using the "chunksize" URI query parameter. Add support fo test_multiplex to the shell. check-in: e305b5a9 user: drh tags: trunk
16:10
Fix for [54844eea3f]: Do not create automatic indexes on correlated sub-queries. check-in: 9f14fa56 user: dan tags: trunk
13:07
Extend the SQLITE_TESTCTRL_OPTIMIZATIONS option to disable DISTINCT optimizations. check-in: 18501dd1 user: drh tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/expr.c.

   898    898       Table *pTab;
   899    899       pNewItem->zDatabase = sqlite3DbStrDup(db, pOldItem->zDatabase);
   900    900       pNewItem->zName = sqlite3DbStrDup(db, pOldItem->zName);
   901    901       pNewItem->zAlias = sqlite3DbStrDup(db, pOldItem->zAlias);
   902    902       pNewItem->jointype = pOldItem->jointype;
   903    903       pNewItem->iCursor = pOldItem->iCursor;
   904    904       pNewItem->isPopulated = pOldItem->isPopulated;
          905  +    pNewItem->isCorrelated = pOldItem->isCorrelated;
   905    906       pNewItem->zIndex = sqlite3DbStrDup(db, pOldItem->zIndex);
   906    907       pNewItem->notIndexed = pOldItem->notIndexed;
   907    908       pNewItem->pIndex = pOldItem->pIndex;
   908    909       pTab = pNewItem->pTab = pOldItem->pTab;
   909    910       if( pTab ){
   910    911         pTab->nRef++;
   911    912       }

Changes to src/resolve.c.

   992    992       }
   993    993     
   994    994       /* Recursively resolve names in all subqueries
   995    995       */
   996    996       for(i=0; i<p->pSrc->nSrc; i++){
   997    997         struct SrcList_item *pItem = &p->pSrc->a[i];
   998    998         if( pItem->pSelect ){
          999  +        NameContext *pNC;         /* Used to iterate name contexts */
         1000  +        int nRef = 0;             /* Refcount for pOuterNC and outer contexts */
   999   1001           const char *zSavedContext = pParse->zAuthContext;
         1002  +
         1003  +        /* Count the total number of references to pOuterNC and all of its
         1004  +        ** parent contexts. After resolving references to expressions in
         1005  +        ** pItem->pSelect, check if this value has changed. If so, then
         1006  +        ** SELECT statement pItem->pSelect must be correlated. Set the
         1007  +        ** pItem->isCorrelated flag if this is the case. */
         1008  +        for(pNC=pOuterNC; pNC; pNC=pNC->pNext) nRef += pNC->nRef;
         1009  +
  1000   1010           if( pItem->zName ) pParse->zAuthContext = pItem->zName;
  1001   1011           sqlite3ResolveSelectNames(pParse, pItem->pSelect, pOuterNC);
  1002   1012           pParse->zAuthContext = zSavedContext;
  1003   1013           if( pParse->nErr || db->mallocFailed ) return WRC_Abort;
         1014  +
         1015  +        for(pNC=pOuterNC; pNC; pNC=pNC->pNext) nRef -= pNC->nRef;
         1016  +        assert( pItem->isCorrelated==0 && nRef<=0 );
         1017  +        pItem->isCorrelated = (nRef!=0);
  1004   1018         }
  1005   1019       }
  1006   1020     
  1007   1021       /* If there are no aggregate functions in the result-set, and no GROUP BY 
  1008   1022       ** expression, do not allow aggregates in any of the other expressions.
  1009   1023       */
  1010   1024       assert( (p->selFlags & SF_Aggregate)==0 );

Changes to src/sqliteInt.h.

  1844   1844       char *zName;      /* Name of the table */
  1845   1845       char *zAlias;     /* The "B" part of a "A AS B" phrase.  zName is the "A" */
  1846   1846       Table *pTab;      /* An SQL table corresponding to zName */
  1847   1847       Select *pSelect;  /* A SELECT statement used in place of a table name */
  1848   1848       u8 isPopulated;   /* Temporary table associated with SELECT is populated */
  1849   1849       u8 jointype;      /* Type of join between this able and the previous */
  1850   1850       u8 notIndexed;    /* True if there is a NOT INDEXED clause */
         1851  +    u8 isCorrelated;  /* True if sub-query is correlated */
  1851   1852   #ifndef SQLITE_OMIT_EXPLAIN
  1852   1853       u8 iSelectId;     /* If pSelect!=0, the id of the sub-select in EQP */
  1853   1854   #endif
  1854   1855       int iCursor;      /* The VDBE cursor number used to access this table */
  1855   1856       Expr *pOn;        /* The ON clause of a join */
  1856   1857       IdList *pUsing;   /* The USING clause of a join */
  1857   1858       Bitmask colUsed;  /* Bit N (1<<N) set if column N of pTab is used */

Changes to src/where.c.

  1914   1914     if( (pCost->plan.wsFlags & WHERE_NOT_FULLSCAN)!=0 ){
  1915   1915       /* We already have some kind of index in use for this query. */
  1916   1916       return;
  1917   1917     }
  1918   1918     if( pSrc->notIndexed ){
  1919   1919       /* The NOT INDEXED clause appears in the SQL. */
  1920   1920       return;
         1921  +  }
         1922  +  if( pSrc->isCorrelated ){
         1923  +    /* The source is a correlated sub-query. No point in indexing it. */
         1924  +    return;
  1921   1925     }
  1922   1926   
  1923   1927     assert( pParse->nQueryLoop >= (double)1 );
  1924   1928     pTable = pSrc->pTab;
  1925   1929     nTableRow = pTable->nRowEst;
  1926   1930     logN = estLog(nTableRow);
  1927   1931     costTempIdx = 2*logN*(nTableRow/pParse->nQueryLoop + 1);

Added test/tkt-54844eea3f.test.

            1  +# 2011 July 8
            2  +#
            3  +# The author disclaims copyright to this source code.  In place of
            4  +# a legal notice, here is a blessing:
            5  +#
            6  +#    May you do good and not evil.
            7  +#    May you find forgiveness for yourself and forgive others.
            8  +#    May you share freely, never taking more than you give.
            9  +#
           10  +#***********************************************************************
           11  +# This file implements regression tests for SQLite library.  The
           12  +# focus of this file is testing that bug [54844eea3f] has been fixed.
           13  +#
           14  +
           15  +set testdir [file dirname $argv0]
           16  +source $testdir/tester.tcl
           17  +
           18  +set ::testprefix tkt-54844eea3f
           19  +
           20  +do_test 1.0 {
           21  +  execsql {
           22  +    CREATE TABLE t1(a INTEGER PRIMARY KEY);
           23  +    INSERT INTO t1 VALUES(1);
           24  +    INSERT INTO t1 VALUES(4);
           25  +
           26  +    CREATE TABLE t2(b INTEGER PRIMARY KEY);
           27  +    INSERT INTO t2 VALUES(1);
           28  +    INSERT INTO t2 VALUES(2);
           29  +    INSERT INTO t2 SELECT b+2 FROM t2;
           30  +    INSERT INTO t2 SELECT b+4 FROM t2;
           31  +    INSERT INTO t2 SELECT b+8 FROM t2;
           32  +    INSERT INTO t2 SELECT b+16 FROM t2;
           33  +
           34  +    CREATE TABLE t3(c INTEGER PRIMARY KEY);
           35  +    INSERT INTO t3 VALUES(1);
           36  +    INSERT INTO t3 VALUES(2);
           37  +    INSERT INTO t3 VALUES(3);
           38  +  }
           39  +} {}
           40  +
           41  +do_test 1.1 {
           42  +  execsql {
           43  +    SELECT 'test-2', t3.c, (
           44  +          SELECT count(*) 
           45  +          FROM t1 JOIN (SELECT DISTINCT t3.c AS p FROM t2) AS x ON t1.a=x.p
           46  +    )
           47  +    FROM t3;
           48  +  }
           49  +} {test-2 1 1 test-2 2 0 test-2 3 0}
           50  +
           51  +do_test 1.2 {
           52  +  execsql {
           53  +    CREATE TABLE t4(a, b, c);
           54  +    INSERT INTO t4 VALUES('a', 1, 'one');
           55  +    INSERT INTO t4 VALUES('a', 2, 'two');
           56  +    INSERT INTO t4 VALUES('b', 1, 'three');
           57  +    INSERT INTO t4 VALUES('b', 2, 'four');
           58  +    SELECT ( 
           59  +      SELECT c FROM (
           60  +        SELECT * FROM t4 WHERE a=out.a ORDER BY b LIMIT 10 OFFSET 1
           61  +      ) WHERE b=out.b
           62  +    ) FROM t4 AS out;
           63  +  }
           64  +} {{} two {} four}
           65  +
           66  +
           67  +finish_test