/ Check-in [d2f0b2e8]
Login

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

Overview
Comment:Avoid creating an automatic index on the RHS of a LEFT JOIN to optimize an IS term. Fix for [ce68383b].
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA3-256: d2f0b2e859a8a413ee4e4bd7040e450ccd604069e4ea8dbc9ff924718c2b14d7
User & Date: dan 2017-07-18 17:13:41
Context
2017-07-18
17:34
Do not use the flattening optimization for a sub-query on the RHS of a LEFT JOIN if that subquery reads data from a virtual table. Because it prevents the planner from creating an automatic index on the results of the sub-query, the flattening optimization sometimes slows things down in this case. check-in: 9dbae1df user: dan tags: trunk
17:13
Avoid creating an automatic index on the RHS of a LEFT JOIN to optimize an IS term. Fix for [ce68383b]. check-in: d2f0b2e8 user: dan tags: trunk
14:41
Change the default command-line shell history depth to 2000 lines. check-in: 0b69aa7e user: drh tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/where.c.

   604    604     WhereTerm *pTerm,              /* WHERE clause term to check */
   605    605     struct SrcList_item *pSrc,     /* Table we are trying to access */
   606    606     Bitmask notReady               /* Tables in outer loops of the join */
   607    607   ){
   608    608     char aff;
   609    609     if( pTerm->leftCursor!=pSrc->iCursor ) return 0;
   610    610     if( (pTerm->eOperator & (WO_EQ|WO_IS))==0 ) return 0;
          611  +  if( (pSrc->fg.jointype & JT_LEFT) 
          612  +   && !ExprHasProperty(pTerm->pExpr, EP_FromJoin)
          613  +   && (pTerm->eOperator & WO_IS)
          614  +  ){
          615  +    /* Cannot use an IS term from the WHERE clause as an index driver for
          616  +    ** the RHS of a LEFT JOIN. Such a term can only be used if it is from
          617  +    ** the ON clause.  */
          618  +    return 0;
          619  +  }
   611    620     if( (pTerm->prereqRight & notReady)!=0 ) return 0;
   612    621     if( pTerm->u.leftColumn<0 ) return 0;
   613    622     aff = pSrc->pTab->aCol[pTerm->u.leftColumn].affinity;
   614    623     if( !sqlite3IndexAffinityOk(pTerm->pExpr, aff) ) return 0;
   615    624     testcase( pTerm->pExpr->op==TK_IS );
   616    625     return 1;
   617    626   }

Changes to test/autoindex1.test.

   515    515   # 2015-04-15:  A NULL CollSeq pointer in automatic index creation.
   516    516   #
   517    517   do_execsql_test autoindex1-920 {
   518    518     CREATE TABLE t920(x);
   519    519     INSERT INTO t920 VALUES(3),(4),(5);
   520    520     SELECT * FROM t920,(SELECT 0 FROM t920),(VALUES(9)) WHERE 5 IN (x);
   521    521   } {5 0 9 5 0 9 5 0 9}
          522  +
          523  +#-------------------------------------------------------------------------
          524  +# An IS term from the WHERE clause of a LEFT JOIN cannot be used as an
          525  +# index driver for the RHS of a LEFT JOIN. Prior to this being fixed,
          526  +# the following SELECT count(*) would incorrectly return 1.
          527  +#
          528  +do_execsql_test autoindex1-1010 {
          529  +  CREATE TABLE t11(w);
          530  +  CREATE TABLE t12(y);
          531  +  INSERT INTO t11 VALUES(NULL);
          532  +  INSERT INTO t12 VALUES('notnull');
          533  +}
          534  +do_execsql_test autoindex1-1020 {
          535  +  SELECT count(*) FROM t11 LEFT JOIN t12 WHERE t12.y IS t11.w;
          536  +} 0
          537  +
          538  +
          539  +
   522    540   
   523    541   finish_test