SQLite

Check-in [bcb4f26247]
Login

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

Overview
Comment:Further tweaks to the ORDER BY optimizer, to fix a bug and to get the optimizer to recognize some additional cases.
Downloads: Tarball | ZIP archive
Timelines: family | ancestors | descendants | both | qp-enhancements
Files: files | file ages | folders
SHA1: bcb4f262476193cfb17818d8c62bab528dddeef9
User & Date: drh 2012-10-08 20:27:35.617
Context
2012-10-08
21:01
All test cases (veryquick.tcl and min.rc) pass. A few branch operations in ORDER BY optimization logic are untested by min.rc. (Closed-Leaf check-in: 8314fd6078 user: drh tags: qp-enhancements)
20:27
Further tweaks to the ORDER BY optimizer, to fix a bug and to get the optimizer to recognize some additional cases. (check-in: bcb4f26247 user: drh tags: qp-enhancements)
19:41
Bug fixes in the ORDER BY optimizer. (check-in: 301bbee404 user: drh tags: qp-enhancements)
Changes
Unified Diff Ignore Whitespace Patch
Changes to src/where.c.
︙︙
2904
2905
2906
2907
2908
2909
2910
2911

2912
2913
2914
2915
2916
2917
2918
2919
2920
2921

    ** if there are any X= or X IS NULL constraints in the WHERE clause. */
    pConstraint = findTerm(p->pWC, base, iColumn, p->notReady,
                           WO_EQ|WO_ISNULL|WO_IN, pIdx);
    if( pConstraint==0 ){
      isEq = 0;
    }else if( pConstraint->eOperator==WO_IN ){
      break;
    }else if( pConstraint->prereqRight==0 ){

      isEq = 1;
    }else if( pConstraint->eOperator==WO_ISNULL ){
      uniqueNotNull = 0;
      isEq = 1;
    }else{
      Expr *pRight = pConstraint->pExpr->pRight;
      if( pRight->op==TK_COLUMN ){
        WHERETRACE(("       .. isOrderedColumn(tab=%d,col=%d)",
                    pRight->iTable, pRight->iColumn));
        isEq = isOrderedColumn(p, pRight->iTable, pRight->iColumn);








|
>

|
<








2904
2905
2906
2907
2908
2909
2910
2911
2912
2913
2914

2915
2916
2917
2918
2919
2920
2921

    ** if there are any X= or X IS NULL constraints in the WHERE clause. */
    pConstraint = findTerm(p->pWC, base, iColumn, p->notReady,
                           WO_EQ|WO_ISNULL|WO_IN, pIdx);
    if( pConstraint==0 ){
      isEq = 0;
    }else if( pConstraint->eOperator==WO_IN ){
      break;
    }else if( pConstraint->eOperator==WO_ISNULL ){
      uniqueNotNull = 0;
      isEq = 1;
    }else if( pConstraint->prereqRight==0 ){

      isEq = 1;
    }else{
      Expr *pRight = pConstraint->pExpr->pRight;
      if( pRight->op==TK_COLUMN ){
        WHERETRACE(("       .. isOrderedColumn(tab=%d,col=%d)",
                    pRight->iTable, pRight->iColumn));
        isEq = isOrderedColumn(p, pRight->iTable, pRight->iColumn);
︙︙
2931
2932
2933
2934
2935
2936
2937

2938
2939
2940
2941

2942
2943
2944
2945
2946
2947
2948
2949
2950
2951
2952
2953
2954
2955

    assert( iSortOrder==0 || iSortOrder==1 );
    if( !isMatch ){
      if( isEq==0 ){
        break;
      }else{
        continue;
      }

    }else if( sortOrder==2 ){
      sortOrder = termSortOrder;
    }else if( termSortOrder!=sortOrder ){
      break;

    }
    j++;
    pOBItem++;
    if( iColumn<0 ){
      seenRowid = 1;
      break;
    }else if( pTab->aCol[iColumn].notNull==0 ){
      uniqueNotNull = 0;
    }
  }

  /* If we have not found at least one ORDER BY term that matches the
  ** index, then show no progress. */
  if( pOBItem==&pOrderBy->a[nPriorSat] ) return nPriorSat;








>
|
|
|
|
>






|








2931
2932
2933
2934
2935
2936
2937
2938
2939
2940
2941
2942
2943
2944
2945
2946
2947
2948
2949
2950
2951
2952
2953
2954
2955
2956
2957

    assert( iSortOrder==0 || iSortOrder==1 );
    if( !isMatch ){
      if( isEq==0 ){
        break;
      }else{
        continue;
      }
    }else if( isEq!=1 ){
      if( sortOrder==2 ){
        sortOrder = termSortOrder;
      }else if( termSortOrder!=sortOrder ){
        break;
      }
    }
    j++;
    pOBItem++;
    if( iColumn<0 ){
      seenRowid = 1;
      break;
    }else if( pTab->aCol[iColumn].notNull==0 && isEq==0 ){
      uniqueNotNull = 0;
    }
  }

  /* If we have not found at least one ORDER BY term that matches the
  ** index, then show no progress. */
  if( pOBItem==&pOrderBy->a[nPriorSat] ) return nPriorSat;
︙︙