/ Check-in [e8881a8b]
Login
SQLite training in Houston TX on 2019-11-05 (details)
Part of the 2019 Tcl Conference

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

Overview
Comment:Inserting a few WhereLoop objects without leaking memory. Costs are not correct. Inequality and IN constraints are not implemented.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | nextgen-query-plan-exp
Files: files | file ages | folders
SHA1: e8881a8b2f25f38bc8ff77619f96f38fe530d13b
User & Date: drh 2013-05-07 19:44:38
Context
2013-05-07
23:06
Continued progress on generating good WhereLoop objects for the new query planner. check-in: 15cc8a16 user: drh tags: nextgen-query-plan-exp
19:44
Inserting a few WhereLoop objects without leaking memory. Costs are not correct. Inequality and IN constraints are not implemented. check-in: e8881a8b user: drh tags: nextgen-query-plan-exp
2013-05-04
20:25
In where.c, make findTerm() a wrapper around methods to a new WhereScan object which is capable of finding all suitable matching terms, not just the first. This check-in includes some prototype functions for building WhereLoop objects. check-in: dd92b8fa user: drh tags: nextgen-query-plan-exp
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to src/where.c.

5159
5160
5161
5162
5163
5164
5165

5166
5167
5168
5169
5170
5171
5172
....
5178
5179
5180
5181
5182
5183
5184

5185

5186
5187








5188



5189
5190
5191

5192
5193
5194
5195
5196
5197
5198
5199
5200
5201
5202
5203
5204
5205
5206
5207
5208
5209
5210
5211
5212
....
5239
5240
5241
5242
5243
5244
5245

5246
5247
5248
5249
5250
5251
5252

5253
5254
5255
5256
5257
5258
5259
....
5547
5548
5549
5550
5551
5552
5553
5554
5555
5556
5557
5558
5559
5560
5561
5562
5563
5564
5565
5566
5567
5568
5569
5570
5571
5572
5573
5574
5575
5576
5577
5578
5579
5580
5581
5582
  int nInMul                      /* Number of iterations due to IN */
){
  sqlite3 *db;                    /* Database connection malloc context */
  WhereLoop *pNew;                /* Template WhereLoop under construction */
  WhereTerm *pTerm;               /* A WhereTerm under consideration */
  int eqTermMask;                 /* Valid equality operators */
  WhereScan scan;                 /* Iterator for WHERE terms */


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

  if( pProbe->tnum<=0 || (pSrc->jointype & JT_LEFT)!=0 ){
    eqTermMask = WO_EQ|WO_IN;
................................................................................
  if( pNew->nEq<pProbe->nColumn ){
    int iCol;            /* Index of the column in the table */


    iCol = pProbe->aiColumn[pNew->nEq];
    pTerm = whereScanInit(&scan, pBuilder->pWC, pSrc->iCursor, iCol,
                          eqTermMask, iCol>=0 ? pProbe : 0);

    pNew->nEq++;

    for(; pTerm!=0; pTerm = whereScanNext(&scan)){
      pNew->aTerm[pNew->nEq-1] = pTerm;








      



    }
    pNew->nEq--;


  }
}

/*
** Add all WhereLoop objects for the iTab-th table of the join.  That
** table is guaranteed to be a b-tree table, not a virtual table.
*/
static void whereLoopAddBtree(
  WhereLoopBuilder *pBuilder, /* WHERE clause information */
  int iTab,                   /* The table to process */
  Bitmask mExtra              /* Extra prerequesites for using this table */
){
  Index *pProbe;              /* An index we are evaluating */
  int eqTermMask;             /* Current mask of valid equality operators */
  Index sPk;                  /* A fake index object for the primary key */
  tRowcnt aiRowEstPk[2];      /* The aiRowEst[] value for the sPk index */
  int aiColumnPk = -1;        /* The aColumn[] value for the sPk index */
  struct SrcList_item *pSrc;  /* The FROM clause btree term to add */
  sqlite3 *db;                /* The database connection */
  WhereLoop *pNew;            /* Template WhereLoop object */

................................................................................
    }
    pProbe = &sPk;
  }

  /* Loop over all indices
  */
  for(; pProbe; pProbe=pProbe->pNext){

    pNew->prereq = mExtra;
    pNew->iTab = iTab;
    pNew->nEq = 0;
    pNew->nTerm = 0;
    pNew->aTerm = sqlite3DbRealloc(db, pNew->aTerm,
                                   (pProbe->nColumn+1)*sizeof(pNew->aTerm[0]));
    if( pNew->aTerm==0 ) break;

    pNew->pIndex = pProbe;

    whereLoopAddBtreeIndex(pBuilder, pSrc, pProbe, 1);

    /* If there was an INDEXED BY clause, then only that one index is
    ** considered. */
    if( pSrc->pIndex ) break;
................................................................................
  if( pDistinct && isDistinctRedundant(pParse, pTabList, sWBI.pWC, pDistinct) ){
    pDistinct = 0;
    pWInfo->eDistinct = WHERE_DISTINCT_UNIQUE;
  }

  /* Construct the WhereLoop objects */
  WHERETRACE(("*** Optimizer Start ***\n"));
  /*whereLoopAddAll(&sWLB);*/

  /* Display all of the WhereLoop objects if wheretrace is enabled */
#if defined(SQLITE_DEBUG) \
    && (defined(SQLITE_TEST) || defined(SQLITE_ENABLE_WHERETRACE))
  if( sqlite3WhereTrace ){
    WhereLoop *p;
    int nb = 2*((nTabList+15)/16);
    for(p=pWInfo->pLoops; p; p=p->pNextLoop){
      struct SrcList_item *pItem = pTabList->a + p->iTab;
       Table *pTab = pItem->pTab;
      sqlite3DebugPrintf("%02d.%0*llx", p->iTab, nb, p->prereq);
      sqlite3DebugPrintf("     %s",
                         pItem->zAlias ? pItem->zAlias : pTab->zName);
      if( p->pIndex ){
        sqlite3DebugPrintf(" index %s nEq %d", p->pIndex->zName, p->nEq);
      }else{
        sqlite3DebugPrintf("\n");
      }
      sqlite3DebugPrintf("     wsFlags %08x OB %d,%d nTerm %d\n",
                         p->wsFlags, p->iOb, p->nOb, p->nTerm);
      sqlite3DebugPrintf("     cost %.2e + %.2e nOut %.2e\n",
                         p->prereq, p->rSetup, p->rRun, p->nOut);
    }
  }
#endif

  /* Chose the best index to use for each table in the FROM clause.
  **







>







 







>

>


>
>
>
>
>
>
>
>
|
>
>
>
|
<
|
>













<







 







>




|
|
|
>







 







|











|


|

|

|

|







5159
5160
5161
5162
5163
5164
5165
5166
5167
5168
5169
5170
5171
5172
5173
....
5179
5180
5181
5182
5183
5184
5185
5186
5187
5188
5189
5190
5191
5192
5193
5194
5195
5196
5197
5198
5199
5200
5201
5202
5203

5204
5205
5206
5207
5208
5209
5210
5211
5212
5213
5214
5215
5216
5217
5218

5219
5220
5221
5222
5223
5224
5225
....
5252
5253
5254
5255
5256
5257
5258
5259
5260
5261
5262
5263
5264
5265
5266
5267
5268
5269
5270
5271
5272
5273
5274
....
5562
5563
5564
5565
5566
5567
5568
5569
5570
5571
5572
5573
5574
5575
5576
5577
5578
5579
5580
5581
5582
5583
5584
5585
5586
5587
5588
5589
5590
5591
5592
5593
5594
5595
5596
5597
  int nInMul                      /* Number of iterations due to IN */
){
  sqlite3 *db;                    /* Database connection malloc context */
  WhereLoop *pNew;                /* Template WhereLoop under construction */
  WhereTerm *pTerm;               /* A WhereTerm under consideration */
  int eqTermMask;                 /* Valid equality operators */
  WhereScan scan;                 /* Iterator for WHERE terms */
  WhereLoop savedLoop;

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

  if( pProbe->tnum<=0 || (pSrc->jointype & JT_LEFT)!=0 ){
    eqTermMask = WO_EQ|WO_IN;
................................................................................
  if( pNew->nEq<pProbe->nColumn ){
    int iCol;            /* Index of the column in the table */


    iCol = pProbe->aiColumn[pNew->nEq];
    pTerm = whereScanInit(&scan, pBuilder->pWC, pSrc->iCursor, iCol,
                          eqTermMask, iCol>=0 ? pProbe : 0);
    savedLoop = *pNew;
    pNew->nEq++;
    pNew->nTerm++;
    for(; pTerm!=0; pTerm = whereScanNext(&scan)){
      pNew->aTerm[pNew->nEq-1] = pTerm;
      pNew->nOut = (double)(pProbe->aiRowEst[pNew->nEq] * nInMul);
      pNew->rSetup = (double)0;
      pNew->rRun = pNew->nOut;
      pNew->prereq = savedLoop.prereq | pTerm->prereqRight;
      if( pProbe->tnum<=0 ){
        pNew->wsFlags = savedLoop.wsFlags | WHERE_ROWID_EQ;
      }else{
        pNew->wsFlags = savedLoop.wsFlags | WHERE_COLUMN_EQ;
      }
      whereLoopInsert(pBuilder->pWInfo, pNew);
      if( pNew->nEq<pProbe->nColumn ){
        whereLoopAddBtreeIndex(pBuilder, pSrc, pProbe, nInMul);
      }

    }
    *pNew = savedLoop;
  }
}

/*
** Add all WhereLoop objects for the iTab-th table of the join.  That
** table is guaranteed to be a b-tree table, not a virtual table.
*/
static void whereLoopAddBtree(
  WhereLoopBuilder *pBuilder, /* WHERE clause information */
  int iTab,                   /* The table to process */
  Bitmask mExtra              /* Extra prerequesites for using this table */
){
  Index *pProbe;              /* An index we are evaluating */

  Index sPk;                  /* A fake index object for the primary key */
  tRowcnt aiRowEstPk[2];      /* The aiRowEst[] value for the sPk index */
  int aiColumnPk = -1;        /* The aColumn[] value for the sPk index */
  struct SrcList_item *pSrc;  /* The FROM clause btree term to add */
  sqlite3 *db;                /* The database connection */
  WhereLoop *pNew;            /* Template WhereLoop object */

................................................................................
    }
    pProbe = &sPk;
  }

  /* Loop over all indices
  */
  for(; pProbe; pProbe=pProbe->pNext){
    WhereTerm **paTerm;
    pNew->prereq = mExtra;
    pNew->iTab = iTab;
    pNew->nEq = 0;
    pNew->nTerm = 0;
    paTerm = sqlite3DbRealloc(db, pNew->aTerm,
                              (pProbe->nColumn+1)*sizeof(pNew->aTerm[0]));
    if( paTerm==0 ) break;
    pNew->aTerm = paTerm;
    pNew->pIndex = pProbe;

    whereLoopAddBtreeIndex(pBuilder, pSrc, pProbe, 1);

    /* If there was an INDEXED BY clause, then only that one index is
    ** considered. */
    if( pSrc->pIndex ) break;
................................................................................
  if( pDistinct && isDistinctRedundant(pParse, pTabList, sWBI.pWC, pDistinct) ){
    pDistinct = 0;
    pWInfo->eDistinct = WHERE_DISTINCT_UNIQUE;
  }

  /* Construct the WhereLoop objects */
  WHERETRACE(("*** Optimizer Start ***\n"));
  whereLoopAddAll(&sWLB);

  /* Display all of the WhereLoop objects if wheretrace is enabled */
#if defined(SQLITE_DEBUG) \
    && (defined(SQLITE_TEST) || defined(SQLITE_ENABLE_WHERETRACE))
  if( sqlite3WhereTrace ){
    WhereLoop *p;
    int nb = 2*((nTabList+15)/16);
    for(p=pWInfo->pLoops; p; p=p->pNextLoop){
      struct SrcList_item *pItem = pTabList->a + p->iTab;
       Table *pTab = pItem->pTab;
      sqlite3DebugPrintf("%02d.%0*llx", p->iTab, nb, p->prereq);
      sqlite3DebugPrintf(" %5s",
                         pItem->zAlias ? pItem->zAlias : pTab->zName);
      if( p->pIndex ){
        sqlite3DebugPrintf(".%-5s %2d", p->pIndex->zName, p->nEq);
      }else{
        sqlite3DebugPrintf("%9s","");
      }
      sqlite3DebugPrintf(" fg %08x OB %d,%d N %2d",
                         p->wsFlags, p->iOb, p->nOb, p->nTerm);
      sqlite3DebugPrintf(" cost %.2g+%.2g,%.2g\n",
                         p->prereq, p->rSetup, p->rRun, p->nOut);
    }
  }
#endif

  /* Chose the best index to use for each table in the FROM clause.
  **