SQLite

Check-in [f783e8e6b1]
Login

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

Overview
Comment:Use the NGQP plan for EXPLAIN QUERY PLAN output. This change causes 207 errors in veryquick.test, many of which are benign.
Downloads: Tarball | ZIP archive
Timelines: family | ancestors | descendants | both | nextgen-query-plan-exp
Files: files | file ages | folders
SHA1: f783e8e6b10de44029c7c5f57e4648a4a677ca1b
User & Date: drh 2013-05-22 20:49:02.170
Context
2013-05-24
13:55
Merge the latest trunk changes into the NGQP branch. (check-in: 7c8f992c04 user: drh tags: nextgen-query-plan-exp)
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: f783e8e6b1 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: 9bf0524df7 user: drh tags: nextgen-query-plan-exp)
Changes
Side-by-Side Diff Ignore Whitespace Patch
Changes to src/where.c.
︙
4164
4165
4166
4167
4168
4169
4170
4171

4172
4173
4174


4175
4176
4177
4178
4179

4180

4181
4182
4183
4184
4185
4186
4187
4188
4189
4190
4191

4192
4193
4194
4195

4196
4197
4198
4199
4200
4201
4202

4164
4165
4166
4167
4168
4169
4170

4171



4172
4173
4174
4175
4176
4177
4178
4179

4180
4181
4182
4183
4184
4185
4186
4187
4188
4189
4190

4191
4192
4193
4194

4195
4196
4197
4198
4199
4200
4201
4202








-
+
-
-
-
+
+





+
-
+










-
+



-
+








**
**   "a=? AND b>?"
**
** The returned pointer points to memory obtained from sqlite3DbMalloc().
** It is the responsibility of the caller to free the buffer when it is
** no longer required.
*/
static char *explainIndexRange(sqlite3 *db, WhereLevel *pLevel, Table *pTab){
static char *explainIndexRange(sqlite3 *db, WhereLoop *pLoop, Table *pTab){
  WherePlan *pPlan = &pLevel->plan;
  Index *pIndex = pPlan->u.pIdx;
  int nEq = pPlan->nEq;
  Index *pIndex = pLoop->u.btree.pIndex;
  int nEq = pLoop->u.btree.nEq;
  int i, j;
  Column *aCol = pTab->aCol;
  int *aiColumn = pIndex->aiColumn;
  StrAccum txt;

  if( pIndex==0 ) return 0;
  if( nEq==0 && (pPlan->wsFlags & (WHERE_BTM_LIMIT|WHERE_TOP_LIMIT))==0 ){
  if( nEq==0 && (pLoop->wsFlags & (WHERE_BTM_LIMIT|WHERE_TOP_LIMIT))==0 ){
    return 0;
  }
  sqlite3StrAccumInit(&txt, 0, 0, SQLITE_MAX_LENGTH);
  txt.db = db;
  sqlite3StrAccumAppend(&txt, " (", 2);
  for(i=0; i<nEq; i++){
    explainAppendTerm(&txt, i, aCol[aiColumn[i]].zName, "=");
  }

  j = i;
  if( pPlan->wsFlags&WHERE_BTM_LIMIT ){
  if( pLoop->wsFlags&WHERE_BTM_LIMIT ){
    char *z = (j==pIndex->nColumn ) ? "rowid" : aCol[aiColumn[j]].zName;
    explainAppendTerm(&txt, i++, z, ">");
  }
  if( pPlan->wsFlags&WHERE_TOP_LIMIT ){
  if( pLoop->wsFlags&WHERE_TOP_LIMIT ){
    char *z = (j==pIndex->nColumn ) ? "rowid" : aCol[aiColumn[j]].zName;
    explainAppendTerm(&txt, i, z, "<");
  }
  sqlite3StrAccumAppend(&txt, ")", 1);
  return sqlite3StrAccumFinish(&txt);
}
︙
4211
4212
4213
4214
4215
4216
4217
4218
4219
4220
4221
4222
4223
4224
4225


4226


4227
4228
4229
4230
4231



4232
4233
4234
4235
4236
4237
4238
4239
4240
4241
4242
4243
4244




4245
4246
4247
4248
4249

4250
4251
4252
4253

4254
4255
4256

4257
4258
4259
4260
4261
4262
4263
4264
4265
4266
4267
4268
4269
4270

4271
4272
4273
4274
4275
4276
4277

4278
4279
4280
4281
4282
4283
4284

4211
4212
4213
4214
4215
4216
4217

4218
4219
4220
4221
4222
4223
4224
4225
4226
4227
4228
4229
4230
4231



4232
4233
4234
4235
4236
4237
4238
4239
4240
4241
4242
4243
4244
4245


4246
4247
4248
4249
4250
4251
4252
4253

4254
4255
4256
4257

4258
4259
4260

4261
4262
4263
4264
4265
4266
4267
4268
4269
4270
4271
4272

4273

4274
4275
4276
4277
4278
4279
4280

4281
4282
4283
4284
4285
4286
4287
4288








-







+
+

+
+


-
-
-
+
+
+











-
-
+
+
+
+




-
+



-
+


-
+











-

-
+






-
+








  SrcList *pTabList,              /* Table list this loop refers to */
  WhereLevel *pLevel,             /* Scan to write OP_Explain opcode for */
  int iLevel,                     /* Value for "level" column of output */
  int iFrom,                      /* Value for "from" column of output */
  u16 wctrlFlags                  /* Flags passed to sqlite3WhereBegin() */
){
  if( pParse->explain==2 ){
    u32 flags = pLevel->plan.wsFlags;
    struct SrcList_item *pItem = &pTabList->a[pLevel->iFrom];
    Vdbe *v = pParse->pVdbe;      /* VM being constructed */
    sqlite3 *db = pParse->db;     /* Database handle */
    char *zMsg;                   /* Text to add to EQP output */
    sqlite3_int64 nRow;           /* Expected number of rows visited by scan */
    int iId = pParse->iSelectId;  /* Select id (left-most output column) */
    int isSearch;                 /* True for a SEARCH. False for SCAN. */
    WhereLoop *pLoop;             /* The controlling WhereLoop object */
    u32 flags;                    /* Flags that describe this loop */

    pLoop = pLevel->pWLoop;
    flags = pLoop->wsFlags;
    if( (flags&WHERE_MULTI_OR) || (wctrlFlags&WHERE_ONETABLE_ONLY) ) return;

    isSearch = (pLevel->plan.nEq>0)
             || (flags&(WHERE_BTM_LIMIT|WHERE_TOP_LIMIT))!=0
             || (wctrlFlags&(WHERE_ORDERBY_MIN|WHERE_ORDERBY_MAX));
    isSearch = (flags&(WHERE_BTM_LIMIT|WHERE_TOP_LIMIT))!=0
            || ((flags&WHERE_VIRTUALTABLE)==0 && (pLoop->u.btree.nEq>0))
            || (wctrlFlags&(WHERE_ORDERBY_MIN|WHERE_ORDERBY_MAX));

    zMsg = sqlite3MPrintf(db, "%s", isSearch?"SEARCH":"SCAN");
    if( pItem->pSelect ){
      zMsg = sqlite3MAppendf(db, zMsg, "%s SUBQUERY %d", zMsg,pItem->iSelectId);
    }else{
      zMsg = sqlite3MAppendf(db, zMsg, "%s TABLE %s", zMsg, pItem->zName);
    }

    if( pItem->zAlias ){
      zMsg = sqlite3MAppendf(db, zMsg, "%s AS %s", zMsg, pItem->zAlias);
    }
    if( (flags & WHERE_INDEXED)!=0 ){
      char *zWhere = explainIndexRange(db, pLevel, pItem->pTab);
    if( (flags & (WHERE_IPK|WHERE_VIRTUALTABLE))==0
     && pLoop->u.btree.pIndex!=0
    ){
      char *zWhere = explainIndexRange(db, pLoop, pItem->pTab);
      zMsg = sqlite3MAppendf(db, zMsg, "%s USING %s%sINDEX%s%s%s", zMsg, 
          ((flags & WHERE_TEMP_INDEX)?"AUTOMATIC ":""),
          ((flags & WHERE_IDX_ONLY)?"COVERING ":""),
          ((flags & WHERE_TEMP_INDEX)?"":" "),
          ((flags & WHERE_TEMP_INDEX)?"": pLevel->plan.u.pIdx->zName),
          ((flags & WHERE_TEMP_INDEX)?"": pLoop->u.btree.pIndex->zName),
          zWhere
      );
      sqlite3DbFree(db, zWhere);
    }else if( flags & (WHERE_ROWID_EQ|WHERE_ROWID_RANGE) ){
    }else if( (flags & WHERE_IPK)!=0 && (flags & WHERE_INDEXED)!=0 ){
      zMsg = sqlite3MAppendf(db, zMsg, "%s USING INTEGER PRIMARY KEY", zMsg);

      if( flags&WHERE_ROWID_EQ ){
      if( flags&WHERE_COLUMN_EQ ){
        zMsg = sqlite3MAppendf(db, zMsg, "%s (rowid=?)", zMsg);
      }else if( (flags&WHERE_BOTH_LIMIT)==WHERE_BOTH_LIMIT ){
        zMsg = sqlite3MAppendf(db, zMsg, "%s (rowid>? AND rowid<?)", zMsg);
      }else if( flags&WHERE_BTM_LIMIT ){
        zMsg = sqlite3MAppendf(db, zMsg, "%s (rowid>?)", zMsg);
      }else if( flags&WHERE_TOP_LIMIT ){
        zMsg = sqlite3MAppendf(db, zMsg, "%s (rowid<?)", zMsg);
      }
    }
#ifndef SQLITE_OMIT_VIRTUALTABLE
    else if( (flags & WHERE_VIRTUALTABLE)!=0 ){
      sqlite3_index_info *pVtabIdx = pLevel->plan.u.pVtabIdx;
      zMsg = sqlite3MAppendf(db, zMsg, "%s VIRTUAL TABLE INDEX %d:%s", zMsg,
                  pVtabIdx->idxNum, pVtabIdx->idxStr);
                  pLoop->u.vtab.idxNum, pLoop->u.vtab.idxStr);
    }
#endif
    if( wctrlFlags&(WHERE_ORDERBY_MIN|WHERE_ORDERBY_MAX) ){
      testcase( wctrlFlags & WHERE_ORDERBY_MIN );
      nRow = 1;
    }else{
      nRow = (sqlite3_int64)pLevel->plan.nRow;
      nRow = (sqlite3_int64)pLoop->nOut;
    }
    zMsg = sqlite3MAppendf(db, zMsg, "%s (~%lld rows)", zMsg, nRow);
    sqlite3VdbeAddOp4(v, OP_Explain, iId, iLevel, iFrom, zMsg, P4_DYNAMIC);
  }
}
#else
# define explainOneScan(u,v,w,x,y,z)
︙
5259
5260
5261
5262
5263
5264
5265
5266


5267
5268
5269
5270
5271
5272
5273

5263
5264
5265
5266
5267
5268
5269

5270
5271
5272
5273
5274
5275
5276
5277
5278








-
+
+








  p->pNextLoop = pNext;
  *ppPrev = p;
  p->aTerm = paTerm;
  if( p->nTerm ){
    memcpy(p->aTerm, pTemplate->aTerm, p->nTerm*sizeof(p->aTerm[0]));
  }
  if( (p->wsFlags & WHERE_VIRTUALTABLE)==0 ){
    if( p->u.btree.pIndex && p->u.btree.pIndex && p->u.btree.pIndex->tnum==0 ){
    Index *pIndex = p->u.btree.pIndex;
    if( pIndex && pIndex->tnum==0 ){
      p->u.btree.pIndex = 0;
    }
  }else{
    pTemplate->u.vtab.needFree = 0;
  }
  return SQLITE_OK;
}
︙
5306
5307
5308
5309
5310
5311
5312

5313
5314
5315
5316
5317
5318
5319

5311
5312
5313
5314
5315
5316
5317
5318
5319
5320
5321
5322
5323
5324
5325








+








  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( pProbe->bUnordered ) opMask &= ~(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;
︙
5468
5469
5470
5471
5472
5473
5474

5475
5476
5477
5478
5479
5480
5481

5474
5475
5476
5477
5478
5479
5480
5481
5482
5483
5484
5485
5486
5487
5488








+








    /* Generate auto-index WhereLoops */
    WhereClause *pWC = pBuilder->pWC;
    WhereTerm *pTerm;
    WhereTerm *pWCEnd = pWC->a + pWC->nTerm;
    for(pTerm=pWC->a; rc==SQLITE_OK && pTerm<pWCEnd; pTerm++){
      if( termCanDriveIndex(pTerm, pSrc, 0) ){
        pNew->u.btree.nEq = 1;
        pNew->u.btree.pIndex = 0;
        pNew->nTerm = 1;
        pNew->aTerm[0] = pTerm;
        pNew->rSetup = 2*rLogSize*pSrc->pTab->nRowEst;
        pNew->nOut = (double)10;
        pNew->rRun = rLogSize + pNew->nOut;
        pNew->wsFlags = WHERE_TEMP_INDEX;
        pNew->prereq = mExtra | pTerm->prereqRight;
︙
5511
5512
5513
5514
5515
5516
5517
5518

5519
5520
5521
5522
5523
5524
5525

5518
5519
5520
5521
5522
5523
5524

5525
5526
5527
5528
5529
5530
5531
5532








-
+








        if( x<BMS-1 ){
          m &= ~(((Bitmask)1)<<x);
        }
      }
      pNew->wsFlags = (m==0) ? WHERE_IDX_ONLY : 0;

      /* Full scan via index */
      if( m==0 || b ){
      if( (m==0 || b) && pProbe->bUnordered==0 ){
        pNew->iSortIdx = b ? iSortIdx : 0;
        pNew->nOut = rSize;
        pNew->rRun = (m==0) ? (rSize + rLogSize)*(1+b) : (rSize*rLogSize);
        rc = whereLoopInsert(pBuilder, pNew);
        if( rc ) break;
      }
    }
︙
5888
5889
5890
5891
5892
5893
5894
5895

5896
5897
5898
5899
5900
5901
5902
5903
5904
5905

5895
5896
5897
5898
5899
5900
5901

5902
5903
5904

5905
5906
5907
5908
5909
5910
5911








-
+


-








    assert( (pLoop->wsFlags & WHERE_VIRTUALTABLE)==0 );
    isUnique = 1;
    if( pLoop->wsFlags & WHERE_IPK ){
      if( (pLoop->wsFlags & WHERE_COLUMN_IN)!=0 ) isUnique = 0;
      if( pLoop->u.btree.nEq!=1 ) isUnique = 0;
      pIndex = 0;
      nColumn = 1;
    }else if( pLoop->u.btree.pIndex==0 ){
    }else if( (pIndex = pLoop->u.btree.pIndex)==0 || pIndex->bUnordered ){
      return 0;
    }else{
      pIndex = pLoop->u.btree.pIndex;
      nColumn = pIndex->nColumn;
      if( pIndex->onError==OE_None ){
        isUnique = 0;
      }else if( (pLoop->wsFlags & (WHERE_COLUMN_IN|WHERE_COLUMN_RANGE
                                   |WHERE_COLUMN_NULL))!=0 ){
        isUnique = 0;
      }else if( pLoop->u.btree.nEq < pIndex->nColumn ){
︙
Changes to test/tester.tcl.
︙
789
790
791
792
793
794
795














796
797
798








799
800
801
802
803
804
805

789
790
791
792
793
794
795
796
797
798
799
800
801
802
803
804
805
806
807
808
809



810
811
812
813
814
815
816
817
818
819
820
821
822
823
824








+
+
+
+
+
+
+
+
+
+
+
+
+
+
-
-
-
+
+
+
+
+
+
+
+








  db close
  sqlite3_reset_auto_extension

  sqlite3_soft_heap_limit 0
  set nTest [incr_ntest]
  set nErr [set_test_counter errors]

  set nKnown 0
  if {[file readable known-problems.txt]} {
    set fd [open known-problems.txt]
    set content [read $fd]
    close $fd
    foreach x $content {set known_error($x) 1}
    foreach x [set_test_counter fail_list] {
      if {[info exists known_error($x)]} {incr nKnown}
    }
  }
  if {$nKnown>0} {
    puts "[expr {$nErr-$nKnown}] new errors and $nKnown known errors\
         out of $nTest tests"
  } else {
  puts "$nErr errors out of $nTest tests"
  if {$nErr>0} {
    puts "Failures on these tests: [set_test_counter fail_list]"
    puts "$nErr errors out of $nTest tests"
  }
  if {$nErr>$nKnown} {
    puts -nonewline "Failures on these tests:"
    foreach x [set_test_counter fail_list] {
      if {![info exists known_error($x)]} {puts -nonewline " $x"}
    }
    puts ""
  }
  foreach warning [set_test_counter warn_list] {
    puts "Warning: $warning"
  }
  run_thread_tests 1
  if {[llength $omitList]>0} {
    puts "Omitted test cases:"
︙