/ Check-in [9bf0524d]
Login

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

Overview
Comment:Allow the rowid at the end of an index to be used in a constraint on that index.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | nextgen-query-plan-exp
Files: files | file ages | folders
SHA1:9bf0524df7ca2e7fcd92b2878a8457264b3c7f6e
User & Date: drh 2013-05-22 17:01:17
Context
2013-05-22
20:49
Use the NGQP plan for EXPLAIN QUERY PLAN output. This change causes 207 errors in veryquick.test, many of which are benign. check-in: f783e8e6 user: drh tags: nextgen-query-plan-exp
17:01
Allow the rowid at the end of an index to be used in a constraint on that index. check-in: 9bf0524d user: drh tags: nextgen-query-plan-exp
02:06
Improvements to ORDER BY handling in the NGQP. Fix an "exit" mistakenly left in a test script during the previous check-in. check-in: 12c709b4 user: drh tags: nextgen-query-plan-exp
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to src/where.c.

5100
5101
5102
5103
5104
5105
5106
5107
5108
5109
5110
5111
5112
5113
5114
....
5289
5290
5291
5292
5293
5294
5295

5296
5297
5298
5299
5300
5301
5302
5303
5304
5305
5306
5307
5308
5309
5310
5311
5312

5313





5314
5315

5316
5317
5318
5319
5320
5321
5322
....
5330
5331
5332
5333
5334
5335
5336
5337
5338
5339
5340
5341
5342
5343
5344
5345
5346
5347
5348
....
5354
5355
5356
5357
5358
5359
5360
5361

5362
5363
5364
5365
5366
5367
5368
      z = sqlite3_mprintf("(%d,\"%s\")", p->u.vtab.idxNum,p->u.vtab.idxStr);
    }else{
      z = sqlite3_mprintf("(%d)", p->u.vtab.idxNum);
    }
    sqlite3DebugPrintf(" %-15s", z);
    sqlite3_free(z);
  }
  sqlite3DebugPrintf(" fg %08x N %2d", p->wsFlags, p->nTerm);
  sqlite3DebugPrintf(" cost %.2g,%.2g,%.2g\n",
                     p->prereq, p->rSetup, p->rRun, p->nOut);
}
#endif

/*
** Deallocate internal memory used by a WhereLoop object
................................................................................
  WhereLoop *pNew;                /* Template WhereLoop under construction */
  WhereTerm *pTerm;               /* A WhereTerm under consideration */
  int opMask;                     /* Valid operators for constraints */
  WhereScan scan;                 /* Iterator for WHERE terms */
  WhereLoop savedLoop;            /* Saved original content of pNew[] */
  int iCol;                       /* Index of the column in the table */
  int rc = SQLITE_OK;             /* Return code */

  double rLogSize;                /* Logarithm of table size */

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

  assert( (pNew->wsFlags & WHERE_VIRTUALTABLE)==0 );
  assert( pNew->u.btree.nEq<pProbe->nColumn );
  assert( (pNew->wsFlags & WHERE_TOP_LIMIT)==0 );
  if( pNew->wsFlags & WHERE_BTM_LIMIT ){
    opMask = WO_LT|WO_LE;
  }else if( pProbe->tnum<=0 || (pSrc->jointype & JT_LEFT)!=0 ){
    opMask = WO_EQ|WO_IN|WO_GT|WO_GE|WO_LT|WO_LE;
  }else{
    opMask = WO_EQ|WO_IN|WO_ISNULL|WO_GT|WO_GE|WO_LT|WO_LE;
  }


  iCol = pProbe->aiColumn[pNew->u.btree.nEq];





  pTerm = whereScanInit(&scan, pBuilder->pWC, pSrc->iCursor, iCol,
                        opMask, iCol>=0 ? pProbe : 0);

  savedLoop = *pNew;
  pNew->rSetup = (double)0;
  rLogSize = estLog(pProbe->aiRowEst[0]);
  for(; rc==SQLITE_OK && pTerm!=0; pTerm = whereScanNext(&scan)){
    int nIn = 1;
    pNew->u.btree.nEq = savedLoop.u.btree.nEq;
    pNew->nTerm = savedLoop.nTerm;
................................................................................
        /* "x IN (SELECT ...)":  Assume the SELECT returns 25 rows */
        nIn = 25;
      }else if( ALWAYS(pExpr->x.pList && pExpr->x.pList->nExpr) ){
        /* "x IN (value, value, ...)" */
        nIn = pExpr->x.pList->nExpr;
      }
      pNew->u.btree.nEq++;
      pNew->nOut = (double)pProbe->aiRowEst[pNew->u.btree.nEq] * nInMul * nIn;
    }else if( pTerm->eOperator & (WO_EQ|WO_ISNULL) ){
      pNew->wsFlags |= WHERE_COLUMN_EQ;
      pNew->u.btree.nEq++;
      pNew->nOut = (double)pProbe->aiRowEst[pNew->u.btree.nEq] * nInMul;
    }else if( pTerm->eOperator & (WO_GT|WO_GE) ){
      pNew->wsFlags |= WHERE_COLUMN_RANGE|WHERE_BTM_LIMIT;
      pNew->nOut = savedLoop.nOut/3;
    }else if( pTerm->eOperator & (WO_LT|WO_LE) ){
      pNew->wsFlags |= WHERE_COLUMN_RANGE|WHERE_TOP_LIMIT;
      pNew->nOut = savedLoop.nOut/3;
    }
................................................................................
      ** the main table */
      pNew->rRun += pNew->nOut*(1 + rLogSize);
    }
    /* TBD: Adjust nOut and rRun for STAT3 range values */
    /* TBD: Adjust nOut for additional constraints */
    rc = whereLoopInsert(pBuilder, pNew);
    if( (pNew->wsFlags & WHERE_TOP_LIMIT)==0
     && pNew->u.btree.nEq<pProbe->nColumn

    ){
      whereLoopAddBtreeIndex(pBuilder, pSrc, pProbe, nInMul*nIn);
    }
  }
  *pNew = savedLoop;
  return rc;
}







|







 







>







|









>
|
>
>
>
>
>

<
>







 







|



|







 







|
>







5100
5101
5102
5103
5104
5105
5106
5107
5108
5109
5110
5111
5112
5113
5114
....
5289
5290
5291
5292
5293
5294
5295
5296
5297
5298
5299
5300
5301
5302
5303
5304
5305
5306
5307
5308
5309
5310
5311
5312
5313
5314
5315
5316
5317
5318
5319
5320
5321

5322
5323
5324
5325
5326
5327
5328
5329
....
5337
5338
5339
5340
5341
5342
5343
5344
5345
5346
5347
5348
5349
5350
5351
5352
5353
5354
5355
....
5361
5362
5363
5364
5365
5366
5367
5368
5369
5370
5371
5372
5373
5374
5375
5376
      z = sqlite3_mprintf("(%d,\"%s\")", p->u.vtab.idxNum,p->u.vtab.idxStr);
    }else{
      z = sqlite3_mprintf("(%d)", p->u.vtab.idxNum);
    }
    sqlite3DebugPrintf(" %-15s", z);
    sqlite3_free(z);
  }
  sqlite3DebugPrintf(" fg %08x N %d", p->wsFlags, p->nTerm);
  sqlite3DebugPrintf(" cost %.2g,%.2g,%.2g\n",
                     p->prereq, p->rSetup, p->rRun, p->nOut);
}
#endif

/*
** Deallocate internal memory used by a WhereLoop object
................................................................................
  WhereLoop *pNew;                /* Template WhereLoop under construction */
  WhereTerm *pTerm;               /* A WhereTerm under consideration */
  int opMask;                     /* Valid operators for constraints */
  WhereScan scan;                 /* Iterator for WHERE terms */
  WhereLoop savedLoop;            /* Saved original content of pNew[] */
  int iCol;                       /* Index of the column in the table */
  int rc = SQLITE_OK;             /* Return code */
  tRowcnt iRowEst;                /* Estimated index selectivity */
  double rLogSize;                /* Logarithm of table size */

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

  assert( (pNew->wsFlags & WHERE_VIRTUALTABLE)==0 );
  assert( pNew->u.btree.nEq<=pProbe->nColumn );
  assert( (pNew->wsFlags & WHERE_TOP_LIMIT)==0 );
  if( pNew->wsFlags & WHERE_BTM_LIMIT ){
    opMask = WO_LT|WO_LE;
  }else if( pProbe->tnum<=0 || (pSrc->jointype & JT_LEFT)!=0 ){
    opMask = WO_EQ|WO_IN|WO_GT|WO_GE|WO_LT|WO_LE;
  }else{
    opMask = WO_EQ|WO_IN|WO_ISNULL|WO_GT|WO_GE|WO_LT|WO_LE;
  }

  if( pNew->u.btree.nEq < pProbe->nColumn ){
    iCol = pProbe->aiColumn[pNew->u.btree.nEq];
    iRowEst = pProbe->aiRowEst[pNew->u.btree.nEq+1];
  }else{
    iCol = -1;
    iRowEst = 1;
  }
  pTerm = whereScanInit(&scan, pBuilder->pWC, pSrc->iCursor, iCol,

                        opMask, pProbe);
  savedLoop = *pNew;
  pNew->rSetup = (double)0;
  rLogSize = estLog(pProbe->aiRowEst[0]);
  for(; rc==SQLITE_OK && pTerm!=0; pTerm = whereScanNext(&scan)){
    int nIn = 1;
    pNew->u.btree.nEq = savedLoop.u.btree.nEq;
    pNew->nTerm = savedLoop.nTerm;
................................................................................
        /* "x IN (SELECT ...)":  Assume the SELECT returns 25 rows */
        nIn = 25;
      }else if( ALWAYS(pExpr->x.pList && pExpr->x.pList->nExpr) ){
        /* "x IN (value, value, ...)" */
        nIn = pExpr->x.pList->nExpr;
      }
      pNew->u.btree.nEq++;
      pNew->nOut = (double)iRowEst * nInMul * nIn;
    }else if( pTerm->eOperator & (WO_EQ|WO_ISNULL) ){
      pNew->wsFlags |= WHERE_COLUMN_EQ;
      pNew->u.btree.nEq++;
      pNew->nOut = (double)iRowEst * nInMul;
    }else if( pTerm->eOperator & (WO_GT|WO_GE) ){
      pNew->wsFlags |= WHERE_COLUMN_RANGE|WHERE_BTM_LIMIT;
      pNew->nOut = savedLoop.nOut/3;
    }else if( pTerm->eOperator & (WO_LT|WO_LE) ){
      pNew->wsFlags |= WHERE_COLUMN_RANGE|WHERE_TOP_LIMIT;
      pNew->nOut = savedLoop.nOut/3;
    }
................................................................................
      ** the main table */
      pNew->rRun += pNew->nOut*(1 + rLogSize);
    }
    /* TBD: Adjust nOut and rRun for STAT3 range values */
    /* 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);
    }
  }
  *pNew = savedLoop;
  return rc;
}