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

Overview
Comment:Further updates to ngqp code.
Downloads: Tarball | ZIP archive
Timelines: family | ancestors | descendants | both | nextgen-query-planner
Files: files | file ages | folders
SHA1: 9844269b3d3729972ccd31c1cb61cb69ae693a9e
User & Date: dan 2013-07-17 18:49:27.929
Context
2013-07-17
20:03
Fix a bug preventing the planner from finding sorting indexes. check-in: 6f06ebee56 user: dan tags: nextgen-query-planner
18:49
Further updates to ngqp code. check-in: 9844269b3d user: dan tags: nextgen-query-planner
2013-07-16
20:01
Updates to use the next-generation-query-planner from the SQLite3 project. This branch is largely broken. check-in: bc9c9f73c5 user: dan tags: nextgen-query-planner
Changes
Unified Diff Show Whitespace Changes Patch
Changes to src/where.c.
933
934
935
936
937
938
939
















940
941
942
943
944
945
946
  if( iIdxCol<pIdx->nColumn ){
    iRet = pIdx->aiColumn[iIdxCol];
  }else if( pPk && iIdxCol<(pIdx->nColumn + pPk->nColumn) ){
    iRet = pPk->aiColumn[iIdxCol - pIdx->nColumn];
  }
  return iRet;
}

















/*
** Return the total number of fields in the index pIdx, including any
** trailing primary key fields.
*/
static int idxColumnCount(Index *pIdx, Index *pPk){
  return (pIdx->nColumn + (pIdx==pPk ? 0 : pPk->nColumn));







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







933
934
935
936
937
938
939
940
941
942
943
944
945
946
947
948
949
950
951
952
953
954
955
956
957
958
959
960
961
962
  if( iIdxCol<pIdx->nColumn ){
    iRet = pIdx->aiColumn[iIdxCol];
  }else if( pPk && iIdxCol<(pIdx->nColumn + pPk->nColumn) ){
    iRet = pPk->aiColumn[iIdxCol - pIdx->nColumn];
  }
  return iRet;
}

/*
** Return a pointer to a buffer containing the name of the collation 
** sequence used with the iIdxCol'th field in index pIdx, including any
** appended PRIMARY KEY fields.
*/
static char *idxColumnCollation(Index *pIdx, Index *pPk, int iIdxCol){
  char *zColl;
  assert( iIdxCol<(pIdx->nColumn + pPk->nColumn) );
  if( iIdxCol<pIdx->nColumn ){
    zColl = pIdx->azColl[iIdxCol];
  }else if( pPk && iIdxCol<(pIdx->nColumn + pPk->nColumn) ){
    zColl = pPk->azColl[iIdxCol - pIdx->nColumn];
  }
  return zColl;
}

/*
** Return the total number of fields in the index pIdx, including any
** trailing primary key fields.
*/
static int idxColumnCount(Index *pIdx, Index *pPk){
  return (pIdx->nColumn + (pIdx==pPk ? 0 : pPk->nColumn));
977
978
979
980
981
982
983
984
985
986
987
988
989
990
991
  pScan->pWC = pWC;
  if( pIdx && iColumn>=0 ){
    Index *pPk = sqlite4FindPrimaryKey(pIdx->pTable, 0);
    pScan->idxaff = pIdx->pTable->aCol[iColumn].affinity;
    for(j=0; idxColumnNumber(pIdx, pPk, j)!=iColumn; j++){
      if( NEVER(j>=idxColumnCount(pIdx, pPk)) ) return 0;
    }
    pScan->zCollName = pIdx->azColl[j];
  }else{
    pScan->idxaff = 0;
    pScan->zCollName = 0;
  }
  pScan->opMask = opMask;
  pScan->k = 0;
  pScan->aEquiv[0] = iCur;







|







993
994
995
996
997
998
999
1000
1001
1002
1003
1004
1005
1006
1007
  pScan->pWC = pWC;
  if( pIdx && iColumn>=0 ){
    Index *pPk = sqlite4FindPrimaryKey(pIdx->pTable, 0);
    pScan->idxaff = pIdx->pTable->aCol[iColumn].affinity;
    for(j=0; idxColumnNumber(pIdx, pPk, j)!=iColumn; j++){
      if( NEVER(j>=idxColumnCount(pIdx, pPk)) ) return 0;
    }
    pScan->zCollName = idxColumnCollation(pIdx, pPk, j);
  }else{
    pScan->idxaff = 0;
    pScan->zCollName = 0;
  }
  pScan->opMask = opMask;
  pScan->k = 0;
  pScan->aEquiv[0] = iCur;
3019
3020
3021
3022
3023
3024
3025
3026
3027
3028
3029
3030
3031
3032
3033
  zAff = sqlite4DbStrDup(pParse->db, sqlite4IndexAffinityStr(v, pIdx));
  if( !zAff ){
    pParse->db->mallocFailed = 1;
  }

  /* Evaluate the equality constraints
  */
  assert( pIdx->nColumn>=nEq );
  for(j=0; j<nEq; j++){
    int r1;
    pTerm = pLoop->aLTerm[j];
    assert( pTerm!=0 );
    /* The following true for indices with redundant columns. 
    ** Ex: CREATE INDEX i1 ON t1(a,b,a); SELECT * FROM t1 WHERE a=0 AND b=0; */
    testcase( (pTerm->wtFlags & TERM_CODED)!=0 );







|







3035
3036
3037
3038
3039
3040
3041
3042
3043
3044
3045
3046
3047
3048
3049
  zAff = sqlite4DbStrDup(pParse->db, sqlite4IndexAffinityStr(v, pIdx));
  if( !zAff ){
    pParse->db->mallocFailed = 1;
  }

  /* Evaluate the equality constraints
  */
  assert( idxColumnCount(pIdx, sqlite4FindPrimaryKey(pIdx->pTable, 0))>=nEq );
  for(j=0; j<nEq; j++){
    int r1;
    pTerm = pLoop->aLTerm[j];
    assert( pTerm!=0 );
    /* The following true for indices with redundant columns. 
    ** Ex: CREATE INDEX i1 ON t1(a,b,a); SELECT * FROM t1 WHERE a=0 AND b=0; */
    testcase( (pTerm->wtFlags & TERM_CODED)!=0 );
3750
3751
3752
3753
3754
3755
3756
3757
3758
3759
3760
3761
3762
3763
3764
3765
    */
    WhereClause *pOrWc;    /* The OR-clause broken out into subterms */
    SrcList *pOrTab;       /* Shortened table list or OR-clause generation */
    Index *pCov = 0;             /* Potential covering index (or NULL) */
    int iCovCur = pParse->nTab++;  /* Cursor used for index scans (if any) */

    int regReturn = ++pParse->nMem;           /* Register used with OP_Gosub */
    int regRowset = 0;                        /* Register for RowSet object */
    int regRowid = 0;                         /* Register holding rowid */
    int iLoopBody = sqlite4VdbeMakeLabel(v);  /* Start of loop body */
    int iRetInit;                             /* Address of regReturn init */
    int untestedTerms = 0;             /* Some terms not completely tested */
    int ii;                            /* Loop counter */
    Expr *pAndExpr = 0;                /* An ".. AND (...)" expression */
   
    pTerm = pLoop->aLTerm[0];







|
|







3766
3767
3768
3769
3770
3771
3772
3773
3774
3775
3776
3777
3778
3779
3780
3781
    */
    WhereClause *pOrWc;    /* The OR-clause broken out into subterms */
    SrcList *pOrTab;       /* Shortened table list or OR-clause generation */
    Index *pCov = 0;             /* Potential covering index (or NULL) */
    int iCovCur = pParse->nTab++;  /* Cursor used for index scans (if any) */

    int regReturn = ++pParse->nMem;           /* Register used with OP_Gosub */
    int regKeyset = 0;                        /* Register for RowSet object */
    int regKey = 0;                           /* Register holding key */
    int iLoopBody = sqlite4VdbeMakeLabel(v);  /* Start of loop body */
    int iRetInit;                             /* Address of regReturn init */
    int untestedTerms = 0;             /* Some terms not completely tested */
    int ii;                            /* Loop counter */
    Expr *pAndExpr = 0;                /* An ".. AND (...)" expression */
   
    pTerm = pLoop->aLTerm[0];
3788
3789
3790
3791
3792
3793
3794
3795
3796
3797
3798
3799
3800
3801
3802
3803
3804
3805
3806
3807
3808
3809
3810
3811
3812
3813
3814
3815
3816
      for(k=1; k<=nNotReady; k++){
        memcpy(&pOrTab->a[k], &origSrc[pLevel[k].iFrom], sizeof(pOrTab->a[k]));
      }
    }else{
      pOrTab = pWInfo->pTabList;
    }

    /* Initialize the rowset register to contain NULL. An SQL NULL is 
    ** equivalent to an empty rowset.
    **
    ** Also initialize regReturn to contain the address of the instruction 
    ** immediately following the OP_Return at the bottom of the loop. This
    ** is required in a few obscure LEFT JOIN cases where control jumps
    ** over the top of the loop into the body of it. In this case the 
    ** correct response for the end-of-loop code (the OP_Return) is to 
    ** fall through to the next instruction, just as an OP_Next does if
    ** called on an uninitialized cursor.
    */
    if( (pWInfo->wctrlFlags & WHERE_DUPLICATES_OK)==0 ){
      regRowset = ++pParse->nMem;
      regRowid = ++pParse->nMem;
      sqlite4VdbeAddOp2(v, OP_Null, 0, regRowset);
    }
    iRetInit = sqlite4VdbeAddOp2(v, OP_Integer, 0, regReturn);

    /* If the original WHERE clause is z of the form:  (x1 OR x2 OR ...) AND y
    ** Then for every term xN, evaluate as the subexpression: xN AND z
    ** That way, terms in y that are factored into the disjunction will
    ** be picked up by the recursive calls to sqlite4WhereBegin() below.







|
|










|
|
|







3804
3805
3806
3807
3808
3809
3810
3811
3812
3813
3814
3815
3816
3817
3818
3819
3820
3821
3822
3823
3824
3825
3826
3827
3828
3829
3830
3831
3832
      for(k=1; k<=nNotReady; k++){
        memcpy(&pOrTab->a[k], &origSrc[pLevel[k].iFrom], sizeof(pOrTab->a[k]));
      }
    }else{
      pOrTab = pWInfo->pTabList;
    }

    /* Initialize the keyset register to contain NULL. An SQL NULL is 
    ** equivalent to an empty keyset.
    **
    ** Also initialize regReturn to contain the address of the instruction 
    ** immediately following the OP_Return at the bottom of the loop. This
    ** is required in a few obscure LEFT JOIN cases where control jumps
    ** over the top of the loop into the body of it. In this case the 
    ** correct response for the end-of-loop code (the OP_Return) is to 
    ** fall through to the next instruction, just as an OP_Next does if
    ** called on an uninitialized cursor.
    */
    if( (pWInfo->wctrlFlags & WHERE_DUPLICATES_OK)==0 ){
      regKeyset = ++pParse->nMem;
      regKey = ++pParse->nMem;
      sqlite4VdbeAddOp2(v, OP_Null, 0, regKeyset);
    }
    iRetInit = sqlite4VdbeAddOp2(v, OP_Integer, 0, regReturn);

    /* If the original WHERE clause is z of the form:  (x1 OR x2 OR ...) AND y
    ** Then for every term xN, evaluate as the subexpression: xN AND z
    ** That way, terms in y that are factored into the disjunction will
    ** be picked up by the recursive calls to sqlite4WhereBegin() below.
3856
3857
3858
3859
3860
3861
3862
3863
3864
3865
3866
3867
3868
3869
3870
3871
3872
3873
3874
        if( pSubWInfo ){
          WhereLoop *pSubLoop;
          explainOneScan(
              pParse, pOrTab, &pSubWInfo->a[0], iLevel, pLevel->iFrom, 0
          );
          if( (pWInfo->wctrlFlags & WHERE_DUPLICATES_OK)==0 ){
            int iSet = ((ii==pOrWc->nTerm-1)?-1:ii);
            int r;
            r = sqlite4ExprCodeGetColumn(pParse, pTabItem->pTab, -1, iCur, 
                                         regRowid);
            sqlite4VdbeAddOp4Int(v, OP_RowSetTest, regRowset,
                                 sqlite4VdbeCurrentAddr(v)+2, r, iSet);
          }
          sqlite4VdbeAddOp2(v, OP_Gosub, regReturn, iLoopBody);

          /* The pSubWInfo->untestedTerms flag means that this OR term
          ** contained one or more AND term from a notReady table.  The
          ** terms from the notReady table could not be tested and will
          ** need to be tested later.







<
|
<
|
|







3872
3873
3874
3875
3876
3877
3878

3879

3880
3881
3882
3883
3884
3885
3886
3887
3888
        if( pSubWInfo ){
          WhereLoop *pSubLoop;
          explainOneScan(
              pParse, pOrTab, &pSubWInfo->a[0], iLevel, pLevel->iFrom, 0
          );
          if( (pWInfo->wctrlFlags & WHERE_DUPLICATES_OK)==0 ){
            int iSet = ((ii==pOrWc->nTerm-1)?-1:ii);

            sqlite4VdbeAddOp2(v, OP_RowKey, iCur, regKey);

            sqlite4VdbeAddOp4Int(v, OP_RowSetTest, regKeyset,
                                 sqlite4VdbeCurrentAddr(v)+2, regKey, iSet);
          }
          sqlite4VdbeAddOp2(v, OP_Gosub, regReturn, iLoopBody);

          /* The pSubWInfo->untestedTerms flag means that this OR term
          ** contained one or more AND term from a notReady table.  The
          ** terms from the notReady table could not be tested and will
          ** need to be tested later.
3883
3884
3885
3886
3887
3888
3889

3890
3891
3892
3893
3894
3895
3896
3897
3898
3899

3900
3901
3902
3903
3904
3905
3906
          ** If the call to sqlite4WhereBegin() above resulted in a scan that
          ** uses an index, and this is either the first OR-connected term
          ** processed or the index is the same as that used by all previous
          ** terms, set pCov to the candidate covering index. Otherwise, set 
          ** pCov to NULL to indicate that no candidate covering index will 
          ** be available.
          */

          pSubLoop = pSubWInfo->a[0].pWLoop;
          assert( (pSubLoop->wsFlags & WHERE_AUTO_INDEX)==0 );
          if( (pSubLoop->wsFlags & WHERE_INDEXED)!=0
           && (ii==0 || pSubLoop->u.btree.pIndex==pCov)
          ){
            assert( pSubWInfo->a[0].iIdxCur==iCovCur );
            pCov = pSubLoop->u.btree.pIndex;
          }else{
            pCov = 0;
          }


          /* Finish the loop through table entries that match term pOrTerm. */
          sqlite4WhereEnd(pSubWInfo);
        }
      }
    }
    pLevel->u.pCovidx = pCov;







>










>







3897
3898
3899
3900
3901
3902
3903
3904
3905
3906
3907
3908
3909
3910
3911
3912
3913
3914
3915
3916
3917
3918
3919
3920
3921
3922
          ** If the call to sqlite4WhereBegin() above resulted in a scan that
          ** uses an index, and this is either the first OR-connected term
          ** processed or the index is the same as that used by all previous
          ** terms, set pCov to the candidate covering index. Otherwise, set 
          ** pCov to NULL to indicate that no candidate covering index will 
          ** be available.
          */
#if 0
          pSubLoop = pSubWInfo->a[0].pWLoop;
          assert( (pSubLoop->wsFlags & WHERE_AUTO_INDEX)==0 );
          if( (pSubLoop->wsFlags & WHERE_INDEXED)!=0
           && (ii==0 || pSubLoop->u.btree.pIndex==pCov)
          ){
            assert( pSubWInfo->a[0].iIdxCur==iCovCur );
            pCov = pSubLoop->u.btree.pIndex;
          }else{
            pCov = 0;
          }
#endif

          /* Finish the loop through table entries that match term pOrTerm. */
          sqlite4WhereEnd(pSubWInfo);
        }
      }
    }
    pLevel->u.pCovidx = pCov;
4179
4180
4181
4182
4183
4184
4185


4186
4187
4188
4189
4190
4191
4192
**    (5)  The template uses more terms of the same index but has no additional
**         dependencies          
*/
static int whereLoopInsert(WhereLoopBuilder *pBuilder, WhereLoop *pTemplate){
  WhereLoop **ppPrev, *p, *pNext = 0;
  WhereInfo *pWInfo = pBuilder->pWInfo;
  sqlite4 *db = pWInfo->pParse->db;



  /* If pBuilder->pBest is defined, then only keep track of the single
  ** best WhereLoop.  pBuilder->pBest->maskSelf==0 indicates that no
  ** prior WhereLoops have been evaluated and that the current pTemplate
  ** is therefore the first and hence the best and should be retained.
  */
  if( (p = pBuilder->pBest)!=0 ){







>
>







4195
4196
4197
4198
4199
4200
4201
4202
4203
4204
4205
4206
4207
4208
4209
4210
**    (5)  The template uses more terms of the same index but has no additional
**         dependencies          
*/
static int whereLoopInsert(WhereLoopBuilder *pBuilder, WhereLoop *pTemplate){
  WhereLoop **ppPrev, *p, *pNext = 0;
  WhereInfo *pWInfo = pBuilder->pWInfo;
  sqlite4 *db = pWInfo->pParse->db;

  assert( pTemplate->u.btree.pIndex || !(pTemplate->wsFlags & WHERE_INDEXED) );

  /* If pBuilder->pBest is defined, then only keep track of the single
  ** best WhereLoop.  pBuilder->pBest->maskSelf==0 indicates that no
  ** prior WhereLoops have been evaluated and that the current pTemplate
  ** is therefore the first and hence the best and should be retained.
  */
  if( (p = pBuilder->pBest)!=0 ){
4611
4612
4613
4614
4615
4616
4617


















4618
4619
4620
4621
4622
4623
4624
        pNew->wsFlags = WHERE_AUTO_INDEX;
        pNew->prereq = mExtra | pTerm->prereqRight;
        rc = whereLoopInsert(pBuilder, pNew);
      }
    }
  }
#endif /* ifndef SQLITE4_OMIT_AUTOMATIC_INDEX */



















  /* Loop through the set of indices being considered. */
  for(; rc==SQLITE4_OK && pProbe; pProbe=pProbe->pNext, iSortIdx++){

    if( pProbe->eIndexType==SQLITE4_INDEX_FTS5 ) continue;

    pNew->u.btree.nEq = 0;







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







4629
4630
4631
4632
4633
4634
4635
4636
4637
4638
4639
4640
4641
4642
4643
4644
4645
4646
4647
4648
4649
4650
4651
4652
4653
4654
4655
4656
4657
4658
4659
4660
        pNew->wsFlags = WHERE_AUTO_INDEX;
        pNew->prereq = mExtra | pTerm->prereqRight;
        rc = whereLoopInsert(pBuilder, pNew);
      }
    }
  }
#endif /* ifndef SQLITE4_OMIT_AUTOMATIC_INDEX */

  /* If this table has no primary key, then it is either a materialized
  ** view or ephemeral table. Either way, add an entry for a full-scan 
  ** of it.  */
  if( pPk==0 ){
    assert( pSrc->pTab->pSelect || (pSrc->pTab->tabFlags & TF_Ephemeral) );
    pNew->u.btree.nEq = 0;
    pNew->nLTerm = 0;
    pNew->iSortIdx = 0;
    pNew->rSetup = 0;
    pNew->prereq = mExtra;
    pNew->nOut = rSize;
    pNew->u.btree.pIndex = 0;
    pNew->iSortIdx = 0;
    pNew->wsFlags = 0;
    pNew->rRun = whereCostAdd(rSize,rLogSize) + 16;
    rc = whereLoopInsert(pBuilder, pNew);
  }

  /* Loop through the set of indices being considered. */
  for(; rc==SQLITE4_OK && pProbe; pProbe=pProbe->pNext, iSortIdx++){

    if( pProbe->eIndexType==SQLITE4_INDEX_FTS5 ) continue;

    pNew->u.btree.nEq = 0;