SQLite: Check-in [84d2a09e]

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

Overview

Comment:	Enhance the query planner so that IS and IS NULL operators are able to drive an index on a LEFT OUTER JOIN.
Downloads:	Tarball \| ZIP archive \| SQL archive
Timelines:	family \| ancestors \| descendants \| both \| branch-3.12.0
Files:	files \| file ages \| folders
SHA1:	84d2a09eab28b65ab7015e89c2057851c7894842
User & Date:	drh 2016-04-07 23:18:44

Context

2016-04-08
15:09		Version 3.12.1 (check-in: fe7d3b75 user: drh tags: release, branch-3.12.0, version-3.12.1)
2016-04-07
23:18		Enhance the query planner so that IS and IS NULL operators are able to drive an index on a LEFT OUTER JOIN. (check-in: 84d2a09e user: drh tags: branch-3.12.0)
21:29		Update the version number to 3.12.1. This is the first release candidate. (check-in: 300f1b61 user: drh tags: branch-3.12.0)
2016-03-31
20:40		Enhance the query planner so that IS and IS NULL operators are able to drive an index on a LEFT OUTER JOIN. (check-in: c648539b user: drh tags: trunk)

Changes

Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/where.c.

  2197   2197     pNew = pBuilder->pNew;
  2198   2198     if( db->mallocFailed ) return SQLITE_NOMEM_BKPT;
  2199   2199   
  2200   2200     assert( (pNew->wsFlags & WHERE_VIRTUALTABLE)==0 );
  2201   2201     assert( (pNew->wsFlags & WHERE_TOP_LIMIT)==0 );
  2202   2202     if( pNew->wsFlags & WHERE_BTM_LIMIT ){
  2203   2203       opMask = WO_LT|WO_LE;
  2204         -  }else if( /*pProbe->tnum<=0 ||*/ (pSrc->fg.jointype & JT_LEFT)!=0 ){
  2205         -    opMask = WO_EQ|WO_IN|WO_GT|WO_GE|WO_LT|WO_LE;
  2206   2204     }else{
  2207   2205       opMask = WO_EQ|WO_IN|WO_GT|WO_GE|WO_LT|WO_LE|WO_ISNULL|WO_IS;
  2208   2206     }
  2209   2207     if( pProbe->bUnordered ) opMask &= ~(WO_GT|WO_GE|WO_LT|WO_LE);
  2210   2208   
  2211   2209     assert( pNew->u.btree.nEq<pProbe->nColumn );
  2212   2210   
................................................................................
  2235   2233         continue; /* ignore IS [NOT] NULL constraints on NOT NULL columns */
  2236   2234       }
  2237   2235       if( pTerm->prereqRight & pNew->maskSelf ) continue;
  2238   2236   
  2239   2237       /* Do not allow the upper bound of a LIKE optimization range constraint
  2240   2238       ** to mix with a lower range bound from some other source */
  2241   2239       if( pTerm->wtFlags & TERM_LIKEOPT && pTerm->eOperator==WO_LT ) continue;
         2240  +
         2241  +    /* Do not allow IS constraints from the WHERE clause to be used by the
         2242  +    ** right table of a LEFT JOIN.  Only constraints in the ON clause are
         2243  +    ** allowed */
         2244  +    if( (pSrc->fg.jointype & JT_LEFT)!=0
         2245  +     && !ExprHasProperty(pTerm->pExpr, EP_FromJoin)
         2246  +     && (eOp & (WO_IS|WO_ISNULL))!=0
         2247  +    ){
         2248  +      testcase( eOp & WO_IS );
         2249  +      testcase( eOp & WO_ISNULL );
         2250  +      continue;
         2251  +    }
  2242   2252   
  2243   2253       pNew->wsFlags = saved_wsFlags;
  2244   2254       pNew->u.btree.nEq = saved_nEq;
  2245   2255       pNew->nLTerm = saved_nLTerm;
  2246   2256       if( whereLoopResize(db, pNew, pNew->nLTerm+1) ) break; /* OOM */
  2247   2257       pNew->aLTerm[pNew->nLTerm++] = pTerm;
  2248   2258       pNew->prereq = (saved_prereq | pTerm->prereqRight) & ~pNew->maskSelf;