/ Check-in [bcb4f262]
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 | SQL archive
Timelines: family | ancestors | descendants | both | qp-enhancements
Files: files | file ages | folders
SHA1: bcb4f262476193cfb17818d8c62bab528dddeef9
User & Date: drh 2012-10-08 20:27:35
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: 8314fd60 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: bcb4f262 user: drh tags: qp-enhancements
19:41
Bug fixes in the ORDER BY optimizer. check-in: 301bbee4 user: drh tags: qp-enhancements
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/where.c.

  2904   2904       ** if there are any X= or X IS NULL constraints in the WHERE clause. */
  2905   2905       pConstraint = findTerm(p->pWC, base, iColumn, p->notReady,
  2906   2906                              WO_EQ|WO_ISNULL|WO_IN, pIdx);
  2907   2907       if( pConstraint==0 ){
  2908   2908         isEq = 0;
  2909   2909       }else if( pConstraint->eOperator==WO_IN ){
  2910   2910         break;
  2911         -    }else if( pConstraint->prereqRight==0 ){
  2912         -      isEq = 1;
  2913   2911       }else if( pConstraint->eOperator==WO_ISNULL ){
  2914   2912         uniqueNotNull = 0;
         2913  +      isEq = 1;
         2914  +    }else if( pConstraint->prereqRight==0 ){
  2915   2915         isEq = 1;
  2916   2916       }else{
  2917   2917         Expr *pRight = pConstraint->pExpr->pRight;
  2918   2918         if( pRight->op==TK_COLUMN ){
  2919   2919           WHERETRACE(("       .. isOrderedColumn(tab=%d,col=%d)",
  2920   2920                       pRight->iTable, pRight->iColumn));
  2921   2921           isEq = isOrderedColumn(p, pRight->iTable, pRight->iColumn);
................................................................................
  2931   2931       assert( iSortOrder==0 || iSortOrder==1 );
  2932   2932       if( !isMatch ){
  2933   2933         if( isEq==0 ){
  2934   2934           break;
  2935   2935         }else{
  2936   2936           continue;
  2937   2937         }
  2938         -    }else if( sortOrder==2 ){
  2939         -      sortOrder = termSortOrder;
  2940         -    }else if( termSortOrder!=sortOrder ){
  2941         -      break;
         2938  +    }else if( isEq!=1 ){
         2939  +      if( sortOrder==2 ){
         2940  +        sortOrder = termSortOrder;
         2941  +      }else if( termSortOrder!=sortOrder ){
         2942  +        break;
         2943  +      }
  2942   2944       }
  2943   2945       j++;
  2944   2946       pOBItem++;
  2945   2947       if( iColumn<0 ){
  2946   2948         seenRowid = 1;
  2947   2949         break;
  2948         -    }else if( pTab->aCol[iColumn].notNull==0 ){
         2950  +    }else if( pTab->aCol[iColumn].notNull==0 && isEq==0 ){
  2949   2951         uniqueNotNull = 0;
  2950   2952       }
  2951   2953     }
  2952   2954   
  2953   2955     /* If we have not found at least one ORDER BY term that matches the
  2954   2956     ** index, then show no progress. */
  2955   2957     if( pOBItem==&pOrderBy->a[nPriorSat] ) return nPriorSat;