SQLite

Check-in [c29538f9b1]
Login

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

Overview
Comment:Enable ORDER BY clauses that span joins to be optimized out.
Downloads: Tarball | ZIP archive
Timelines: family | ancestors | descendants | both | qp-enhancements
Files: files | file ages | folders
SHA1: c29538f9b1ee4d4869999570604c9618ca0d08ac
User & Date: drh 2012-09-27 14:11:36.514
Context
2012-09-27
15:05
Add more bits to the bit vector that is used to disable optimizations for built-in test. Add specific bit patterns to disable ORDER BY using an index in general and for joins. Use macros to test for bits in the disabled-optimization bit vector, in order to make the code clearer. (check-in: d2fcba1e14 user: drh tags: qp-enhancements)
14:11
Enable ORDER BY clauses that span joins to be optimized out. (check-in: c29538f9b1 user: drh tags: qp-enhancements)
12:05
Further tweaks to the query planner logic in preparation for adding ORDER BY opt-out for joins. (check-in: 53efc10af9 user: drh tags: qp-enhancements)
Changes
Unified Diff Ignore Whitespace Patch
Changes to src/where.c.
2858
2859
2860
2861
2862
2863
2864








































2865
2866
2867
2868
2869
2870
2871




2872
2873
2874


2875




2876
2877
2878
2879
2880
2881
2882
2883
    if( nRowEst > p->aiRowEst[0] ) nRowEst = p->aiRowEst[0];
    *pnRow = nRowEst;
    WHERETRACE(("IN row estimate: est=%g\n", nRowEst));
  }
  return rc;
}
#endif /* defined(SQLITE_ENABLE_STAT3) */









































/*
** pTerm is an == constraint.  Check to see if the other side of
** the == is a constant or a value that is guaranteed to be ordered
** by outer loops.  Return 1 if pTerm is ordered, and 0 if not.
*/
static int isOrderedTerm(WhereBestIdx *p, WhereTerm *pTerm, int *pbRev){




  if( p->i==0 ){
    return 1;  /* All == are ordered in the outer loop */
  }









  /* If we cannot prove that the constraint is ordered, assume it is not */
  return 0;
}


/*







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







>
>
>
>



>
>
|
>
>
>
>
|







2858
2859
2860
2861
2862
2863
2864
2865
2866
2867
2868
2869
2870
2871
2872
2873
2874
2875
2876
2877
2878
2879
2880
2881
2882
2883
2884
2885
2886
2887
2888
2889
2890
2891
2892
2893
2894
2895
2896
2897
2898
2899
2900
2901
2902
2903
2904
2905
2906
2907
2908
2909
2910
2911
2912
2913
2914
2915
2916
2917
2918
2919
2920
2921
2922
2923
2924
2925
2926
2927
2928
2929
2930
2931
2932
2933
    if( nRowEst > p->aiRowEst[0] ) nRowEst = p->aiRowEst[0];
    *pnRow = nRowEst;
    WHERETRACE(("IN row estimate: est=%g\n", nRowEst));
  }
  return rc;
}
#endif /* defined(SQLITE_ENABLE_STAT3) */

/*
** Check to see if column iCol of the table with cursor iTab will appear
** in sorted order according to the current query plan.  Return true if
** it will and false if not.  
**
** If *pbRev is initially 2 (meaning "unknown") then set *pbRev to the
** sort order of iTab.iCol.  If *pbRev is 0 or 1 but does not match
** the sort order of iTab.iCol, then consider the column to be unordered.
*/
static int isOrderedColumn(WhereBestIdx *p, int iTab, int iCol, int *pbRev){
  int i, j;
  WhereLevel *pLevel = &p->aLevel[p->i-1];
  Index *pIdx;
  u8 sortOrder;
  for(i=p->i-1; i>=0; i--, pLevel--){
    if( pLevel->iTabCur!=iTab ) continue;
    if( (pLevel->plan.wsFlags & (WHERE_ROWID_EQ|WHERE_ROWID_RANGE))!=0 ){
      if( iCol!=(-1) ) return 0;
      sortOrder = 0;
    }else if( (pLevel->plan.wsFlags & WHERE_INDEXED)!=0 ){
      pIdx = pLevel->plan.u.pIdx;
      for(j=0; j<pIdx->nColumn; j++){
        if( iCol==pIdx->aiColumn[j] ) break;
      }
      if( j>=pIdx->nColumn ) return 0;
      sortOrder = pIdx->aSortOrder[j];
    }else{
      if( iCol!=(-1) ) return 0;
      sortOrder = 0;
    }
    if( (pLevel->plan.wsFlags & WHERE_REVERSE)!=0 ) sortOrder = 1 - sortOrder;
    if( *pbRev==2 ){
      *pbRev = sortOrder;
      return 1;
    }
    return (*pbRev==sortOrder);
  }
  return 0;
}

/*
** pTerm is an == constraint.  Check to see if the other side of
** the == is a constant or a value that is guaranteed to be ordered
** by outer loops.  Return 1 if pTerm is ordered, and 0 if not.
*/
static int isOrderedTerm(WhereBestIdx *p, WhereTerm *pTerm, int *pbRev){
  Expr *pExpr = pTerm->pExpr;
  assert( pExpr->op==TK_EQ );
  assert( pExpr->pLeft!=0 && pExpr->pLeft->op==TK_COLUMN );
  assert( pExpr->pRight!=0 );
  if( p->i==0 ){
    return 1;  /* All == are ordered in the outer loop */
  }
  if( pTerm->prereqRight==0 ){
    return 1;  /* RHS of the == is a constant */
  }
  if( pExpr->pRight->op==TK_COLUMN 
   && isOrderedColumn(p, pExpr->pRight->iTable, pExpr->pRight->iColumn, pbRev)
  ){
    return 1;
  }

  /* If we cannot prove that the constraint is ordered, assume it is not */
  return 0;
}


/*