SQLite

Check-in [84d2a09eab]
Login

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
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.800
Context
2016-04-08
15:09
Version 3.12.1 (check-in: fe7d3b75fe 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: 84d2a09eab 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: 300f1b61a0 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: c648539b52 user: drh tags: trunk)
Changes
Side-by-Side Diff Ignore Whitespace Patch
Changes to src/where.c.
︙
2197
2198
2199
2200
2201
2202
2203
2204
2205
2206
2207
2208
2209
2210
2211
2212

2197
2198
2199
2200
2201
2202
2203


2204
2205
2206
2207
2208
2209
2210








-
-








  pNew = pBuilder->pNew;
  if( db->mallocFailed ) return SQLITE_NOMEM_BKPT;

  assert( (pNew->wsFlags & WHERE_VIRTUALTABLE)==0 );
  assert( (pNew->wsFlags & WHERE_TOP_LIMIT)==0 );
  if( pNew->wsFlags & WHERE_BTM_LIMIT ){
    opMask = WO_LT|WO_LE;
  }else if( /*pProbe->tnum<=0 ||*/ (pSrc->fg.jointype & JT_LEFT)!=0 ){
    opMask = WO_EQ|WO_IN|WO_GT|WO_GE|WO_LT|WO_LE;
  }else{
    opMask = WO_EQ|WO_IN|WO_GT|WO_GE|WO_LT|WO_LE|WO_ISNULL|WO_IS;
  }
  if( pProbe->bUnordered ) opMask &= ~(WO_GT|WO_GE|WO_LT|WO_LE);

  assert( pNew->u.btree.nEq<pProbe->nColumn );
︙
2235
2236
2237
2238
2239
2240
2241












2242
2243
2244
2245
2246
2247
2248

2233
2234
2235
2236
2237
2238
2239
2240
2241
2242
2243
2244
2245
2246
2247
2248
2249
2250
2251
2252
2253
2254
2255
2256
2257
2258








+
+
+
+
+
+
+
+
+
+
+
+








      continue; /* ignore IS [NOT] NULL constraints on NOT NULL columns */
    }
    if( pTerm->prereqRight & pNew->maskSelf ) continue;

    /* Do not allow the upper bound of a LIKE optimization range constraint
    ** to mix with a lower range bound from some other source */
    if( pTerm->wtFlags & TERM_LIKEOPT && pTerm->eOperator==WO_LT ) continue;

    /* Do not allow IS constraints from the WHERE clause to be used by the
    ** right table of a LEFT JOIN.  Only constraints in the ON clause are
    ** allowed */
    if( (pSrc->fg.jointype & JT_LEFT)!=0
     && !ExprHasProperty(pTerm->pExpr, EP_FromJoin)
     && (eOp & (WO_IS|WO_ISNULL))!=0
    ){
      testcase( eOp & WO_IS );
      testcase( eOp & WO_ISNULL );
      continue;
    }

    pNew->wsFlags = saved_wsFlags;
    pNew->u.btree.nEq = saved_nEq;
    pNew->nLTerm = saved_nLTerm;
    if( whereLoopResize(db, pNew, pNew->nLTerm+1) ) break; /* OOM */
    pNew->aLTerm[pNew->nLTerm++] = pTerm;
    pNew->prereq = (saved_prereq | pTerm->prereqRight) & ~pNew->maskSelf;
︙