SQLite
Check-in [40567fddd4]
Not logged in
Overview
SHA1 Hash:40567fddd468d00295275af8df09a7a1785e684a
Date: 2013-06-12 03:48:41
User: drh
Comment:Continue refining the NGQP
Tags And Properties
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to src/where.c

70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
....
4244
4245
4246
4247
4248
4249
4250
4251
4252
4253
4254
4255
4256
4257
4258
....
4268
4269
4270
4271
4272
4273
4274
4275
4276
4277
4278
4279
4280
4281
4282
....
4299
4300
4301
4302
4303
4304
4305
4306
4307
4308
4309
4310
4311
4312
4313
4314


4315
4316
4317
4318
4319
4320
4321
....
5247
5248
5249
5250
5251
5252
5253
5254
5255
5256
5257
5258
5259
5260
5261
....
5568
5569
5570
5571
5572
5573
5574
5575
5576
5577
5578
5579


5580
5581
5582
5583
5584
5585
5586
  int iLeftJoin;        /* Memory cell used to implement LEFT OUTER JOIN */
  int iTabCur;          /* The VDBE cursor used to access the table */
  int iIdxCur;          /* The VDBE cursor used to access pIdx */
  int addrBrk;          /* Jump here to break out of the loop */
  int addrNxt;          /* Jump here to start the next IN combination */
  int addrCont;         /* Jump here to continue with the next loop cycle */
  int addrFirst;        /* First instruction of interior of the loop */
  u8 iFrom;       /* FIXME: Which entry in the FROM clause */
  u8 op, p5;            /* Opcode and P5 of the opcode that ends the loop */
  int p1, p2;           /* Operands of the opcode used to ends the loop */
  union {               /* Information that depends on plan.wsFlags */
    struct {
      int nIn;              /* Number of entries in aInLoop[] */
      struct InLoop {
        int iCur;              /* The VDBE cursor used by this IN operator */
................................................................................
    pNew->prereq = (saved_prereq | pTerm->prereqRight) & ~pNew->maskSelf;
    pNew->rRun = rLogSize;
    if( pTerm->eOperator & WO_IN ){
      Expr *pExpr = pTerm->pExpr;
      pNew->wsFlags |= WHERE_COLUMN_IN;
      if( ExprHasProperty(pExpr, EP_xIsSelect) ){
        /* "x IN (SELECT ...)":  Assume the SELECT returns 25 rows */
        nIn = 46;  /* whereCostFromInt(25) */
      }else if( ALWAYS(pExpr->x.pList && pExpr->x.pList->nExpr) ){
        /* "x IN (value, value, ...)" */
        nIn = whereCostFromInt(pExpr->x.pList->nExpr);
      }
      pNew->rRun += nIn;
      pNew->u.btree.nEq++;
      pNew->nOut = nRowEst + nInMul + nIn;
................................................................................
        pNew->wsFlags |= WHERE_ONEROW;
      }
      pNew->u.btree.nEq++;
      pNew->nOut = nRowEst + nInMul;
    }else if( pTerm->eOperator & (WO_ISNULL) ){
      pNew->wsFlags |= WHERE_COLUMN_NULL;
      pNew->u.btree.nEq++;
      nIn = 10;  /* Assume IS NULL matches two rows */
      pNew->nOut = nRowEst + nInMul + nIn;
    }else if( pTerm->eOperator & (WO_GT|WO_GE) ){
      pNew->wsFlags |= WHERE_COLUMN_RANGE|WHERE_BTM_LIMIT;
      pBtm = pTerm;
      pTop = 0;
    }else if( pTerm->eOperator & (WO_LT|WO_LE) ){
      pNew->wsFlags |= WHERE_COLUMN_RANGE|WHERE_TOP_LIMIT;
................................................................................
      }else if( (pTerm->eOperator & WO_IN)
             &&  !ExprHasProperty(pTerm->pExpr, EP_xIsSelect)  ){
        rc = whereInScanEst(pParse, pProbe, pTerm->pExpr->x.pList, &nOut);
      }
      pNew->nOut = whereCostFromInt(nOut);
    }
#endif
    if( pNew->wsFlags & (WHERE_IDX_ONLY|WHERE_IPK) ){
      /* Step cost for each output row */
      pNew->rRun = whereCostAdd(pNew->rRun, pNew->nOut);
    }else{
      /* Each row involves a step of the index, then a binary search of
      ** the main table */
      WhereCost rStepAndSearch = whereCostAdd(10, rLogSize>17 ? rLogSize-17 : 1);
      pNew->rRun =  whereCostAdd(pNew->rRun, rStepAndSearch);
    }


    /* TBD: Adjust nOut for additional constraints */
    rc = whereLoopInsert(pBuilder, pNew);
    if( (pNew->wsFlags & WHERE_TOP_LIMIT)==0
     && pNew->u.btree.nEq<=pProbe->nColumn
     && pProbe->zName!=0
    ){
      whereLoopAddBtreeIndex(pBuilder, pSrc, pProbe, nInMul+nIn);
................................................................................
    if( pFrom->rCost>aFrom[ii].rCost ) pFrom = &aFrom[ii];
  }
  assert( pWInfo->nLevel==nLoop );
  /* Load the lowest cost path into pWInfo */
  for(iLoop=0; iLoop<nLoop; iLoop++){
    WhereLevel *pLevel = pWInfo->a + iLoop;
    pLevel->pWLoop = pWLoop = pFrom->aLoop[iLoop];
    pLevel->iFrom = pWLoop->iTab; /* FIXME: Omit the iFrom field */
    pLevel->iTabCur = pWInfo->pTabList->a[pLevel->iFrom].iCursor;
  }
  if( (pWInfo->wctrlFlags & WHERE_DISTINCTBY)==0 
   && pWInfo->pDistinct
   && nRowEst
  ){
    Bitmask notUsed;
................................................................................
    goto whereBeginError;
  }

  /* If the ORDER BY (or GROUP BY) clause contains references to general
  ** expressions, then we won't be able to satisfy it using indices, so
  ** go ahead and disable it now.
  */
  if( pOrderBy ){
    for(ii=0; ii<pOrderBy->nExpr; ii++){
      Expr *pExpr = sqlite3ExprSkipCollate(pOrderBy->a[ii].pExpr);
      if( pExpr->op!=TK_COLUMN ){
        pWInfo->pOrderBy = pOrderBy = 0;


        break;
      }
    }
  }

  /* Check if the DISTINCT qualifier, if there is one, is redundant. 
  ** If it is, then set pDistinct to NULL and WhereInfo.eDistinct to







|







 







|







 







|







 







|
<
<
<


<
|

>
>







 







|







 







|




>
>







70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
....
4244
4245
4246
4247
4248
4249
4250
4251
4252
4253
4254
4255
4256
4257
4258
....
4268
4269
4270
4271
4272
4273
4274
4275
4276
4277
4278
4279
4280
4281
4282
....
4299
4300
4301
4302
4303
4304
4305
4306



4307
4308

4309
4310
4311
4312
4313
4314
4315
4316
4317
4318
4319
....
5245
5246
5247
5248
5249
5250
5251
5252
5253
5254
5255
5256
5257
5258
5259
....
5566
5567
5568
5569
5570
5571
5572
5573
5574
5575
5576
5577
5578
5579
5580
5581
5582
5583
5584
5585
5586
  int iLeftJoin;        /* Memory cell used to implement LEFT OUTER JOIN */
  int iTabCur;          /* The VDBE cursor used to access the table */
  int iIdxCur;          /* The VDBE cursor used to access pIdx */
  int addrBrk;          /* Jump here to break out of the loop */
  int addrNxt;          /* Jump here to start the next IN combination */
  int addrCont;         /* Jump here to continue with the next loop cycle */
  int addrFirst;        /* First instruction of interior of the loop */
  u8 iFrom;             /* Which entry in the FROM clause */
  u8 op, p5;            /* Opcode and P5 of the opcode that ends the loop */
  int p1, p2;           /* Operands of the opcode used to ends the loop */
  union {               /* Information that depends on plan.wsFlags */
    struct {
      int nIn;              /* Number of entries in aInLoop[] */
      struct InLoop {
        int iCur;              /* The VDBE cursor used by this IN operator */
................................................................................
    pNew->prereq = (saved_prereq | pTerm->prereqRight) & ~pNew->maskSelf;
    pNew->rRun = rLogSize;
    if( pTerm->eOperator & WO_IN ){
      Expr *pExpr = pTerm->pExpr;
      pNew->wsFlags |= WHERE_COLUMN_IN;
      if( ExprHasProperty(pExpr, EP_xIsSelect) ){
        /* "x IN (SELECT ...)":  Assume the SELECT returns 25 rows */
        nIn = 46;  assert( 46==whereCostFromInt(25) );
      }else if( ALWAYS(pExpr->x.pList && pExpr->x.pList->nExpr) ){
        /* "x IN (value, value, ...)" */
        nIn = whereCostFromInt(pExpr->x.pList->nExpr);
      }
      pNew->rRun += nIn;
      pNew->u.btree.nEq++;
      pNew->nOut = nRowEst + nInMul + nIn;
................................................................................
        pNew->wsFlags |= WHERE_ONEROW;
      }
      pNew->u.btree.nEq++;
      pNew->nOut = nRowEst + nInMul;
    }else if( pTerm->eOperator & (WO_ISNULL) ){
      pNew->wsFlags |= WHERE_COLUMN_NULL;
      pNew->u.btree.nEq++;
      nIn = 10;  assert( 10==whereCostFromInt(2) );
      pNew->nOut = nRowEst + nInMul + nIn;
    }else if( pTerm->eOperator & (WO_GT|WO_GE) ){
      pNew->wsFlags |= WHERE_COLUMN_RANGE|WHERE_BTM_LIMIT;
      pBtm = pTerm;
      pTop = 0;
    }else if( pTerm->eOperator & (WO_LT|WO_LE) ){
      pNew->wsFlags |= WHERE_COLUMN_RANGE|WHERE_TOP_LIMIT;
................................................................................
      }else if( (pTerm->eOperator & WO_IN)
             &&  !ExprHasProperty(pTerm->pExpr, EP_xIsSelect)  ){
        rc = whereInScanEst(pParse, pProbe, pTerm->pExpr->x.pList, &nOut);
      }
      pNew->nOut = whereCostFromInt(nOut);
    }
#endif
    if( (pNew->wsFlags & (WHERE_IDX_ONLY|WHERE_IPK))==0 ){



      /* Each row involves a step of the index, then a binary search of
      ** the main table */

      pNew->rRun =  whereCostAdd(pNew->rRun, rLogSize>27 ? rLogSize-17 : 10);
    }
    /* Step cost for each output row */
    pNew->rRun = whereCostAdd(pNew->rRun, pNew->nOut);
    /* TBD: Adjust nOut for additional constraints */
    rc = whereLoopInsert(pBuilder, pNew);
    if( (pNew->wsFlags & WHERE_TOP_LIMIT)==0
     && pNew->u.btree.nEq<=pProbe->nColumn
     && pProbe->zName!=0
    ){
      whereLoopAddBtreeIndex(pBuilder, pSrc, pProbe, nInMul+nIn);
................................................................................
    if( pFrom->rCost>aFrom[ii].rCost ) pFrom = &aFrom[ii];
  }
  assert( pWInfo->nLevel==nLoop );
  /* Load the lowest cost path into pWInfo */
  for(iLoop=0; iLoop<nLoop; iLoop++){
    WhereLevel *pLevel = pWInfo->a + iLoop;
    pLevel->pWLoop = pWLoop = pFrom->aLoop[iLoop];
    pLevel->iFrom = pWLoop->iTab;
    pLevel->iTabCur = pWInfo->pTabList->a[pLevel->iFrom].iCursor;
  }
  if( (pWInfo->wctrlFlags & WHERE_DISTINCTBY)==0 
   && pWInfo->pDistinct
   && nRowEst
  ){
    Bitmask notUsed;
................................................................................
    goto whereBeginError;
  }

  /* If the ORDER BY (or GROUP BY) clause contains references to general
  ** expressions, then we won't be able to satisfy it using indices, so
  ** go ahead and disable it now.
  */
  if( pOrderBy && pDistinct ){
    for(ii=0; ii<pOrderBy->nExpr; ii++){
      Expr *pExpr = sqlite3ExprSkipCollate(pOrderBy->a[ii].pExpr);
      if( pExpr->op!=TK_COLUMN ){
        pWInfo->pOrderBy = pOrderBy = 0;
        break;
      }else if( pExpr->iColumn<0 ){
        break;
      }
    }
  }

  /* Check if the DISTINCT qualifier, if there is one, is redundant. 
  ** If it is, then set pDistinct to NULL and WhereInfo.eDistinct to