/ Check-in [c648539b]
Login
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 | trunk
Files: files | file ages | folders
SHA1:c648539b52ca28c0b2cb61208e2c32b1d29626a1
User & Date: drh 2016-03-31 20:40:28
Context
2016-03-31
22:53
Update the clean target in the MSVC makefile. check-in: 69f4d832 user: mistachkin tags: trunk
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
2016-03-30
16:23
Updates for the MSVC makefiles. check-in: 7cf0cab7 user: mistachkin tags: trunk
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to src/where.c.

2197
2198
2199
2200
2201
2202
2203
2204
2205
2206
2207
2208
2209
2210
2211
2212
....
2235
2236
2237
2238
2239
2240
2241












2242
2243
2244
2245
2246
2247
2248
  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 );

................................................................................
      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;













    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;







<
<







 







>
>
>
>
>
>
>
>
>
>
>
>







2197
2198
2199
2200
2201
2202
2203


2204
2205
2206
2207
2208
2209
2210
....
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
  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{
    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 );

................................................................................
      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;