/ Check-in [8314fd60]
Login

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

Overview
Comment:All test cases (veryquick.tcl and min.rc) pass. A few branch operations in ORDER BY optimization logic are untested by min.rc.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | qp-enhancements
Files: files | file ages | folders
SHA1: 8314fd6078a3a71aea9d6386ff277cfeb592b4ba
User & Date: drh 2012-10-08 21:01:15
Context
2012-10-08
21:51
Merge ORDER BY optimization refactoring and repair into trunk. check-in: c027a9af user: drh tags: trunk
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
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/where.c.

  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);
  2922   2922           WHERETRACE((" -> isEq=%d\n", isEq));
  2923         -        if( isEq>=2 && isEq!=pOBItem->sortOrder+2 ){
         2923  +        if( isMatch && isEq>=2 && isEq!=pOBItem->sortOrder+2 ){
  2924   2924             break;
  2925   2925           }
  2926   2926         }else{
  2927   2927           isEq = 0;
  2928   2928         }
  2929   2929       }
  2930   2930       assert( pOBItem->sortOrder==0 || pOBItem->sortOrder==1 );

Changes to test/orderby1.test.

   110    110     }
   111    111   } {three-a three-c two-a two-b one-a one-c}  ;# verify same order after sorting
   112    112   do_test 1.4c {
   113    113     db eval {
   114    114       EXPLAIN QUERY PLAN
   115    115       SELECT name FROM album JOIN track USING (aid) ORDER BY title DESC, tn
   116    116     }
   117         -} {/ORDER BY/}  ;# separate sorting pass due to mixed DESC/ASC
          117  +} {~/ORDER BY/}  ;# optimized out
   118    118   
   119    119   
   120    120   do_test 1.5a {
   121    121     db eval {
   122    122       SELECT name FROM album JOIN track USING (aid) ORDER BY title, tn DESC
   123    123     }
   124    124   } {one-c one-a two-b two-a three-c three-a}
................................................................................
   128    128     }
   129    129   } {one-c one-a two-b two-a three-c three-a}  ;# verify same order after sorting
   130    130   do_test 1.5c {
   131    131     db eval {
   132    132       EXPLAIN QUERY PLAN
   133    133       SELECT name FROM album JOIN track USING (aid) ORDER BY title, tn DESC
   134    134     }
   135         -} {/ORDER BY/}  ;# separate sorting pass due to mixed DESC/ASC
          135  +} {~/ORDER BY/}  ;# optimized out
   136    136   
   137    137   do_test 1.6a {
   138    138     db eval {
   139    139       SELECT name FROM album JOIN track USING (aid) ORDER BY title DESC, tn DESC
   140    140     }
   141    141   } {three-c three-a two-b two-a one-c one-a}
   142    142   do_test 1.6b {
................................................................................
   257    257     }
   258    258   } {three-a three-c two-a two-b one-a one-c}  ;# verify same order after sorting
   259    259   do_test 2.4c {
   260    260     db eval {
   261    261       EXPLAIN QUERY PLAN
   262    262       SELECT name FROM album JOIN track USING (aid) ORDER BY title DESC, tn
   263    263     }
   264         -} {/ORDER BY/}  ;# separate sorting pass due to mixed DESC/ASC
          264  +} {~/ORDER BY/}  ;# optimized out
   265    265   
   266    266   
   267    267   do_test 2.5a {
   268    268     db eval {
   269    269       SELECT name FROM album JOIN track USING (aid) ORDER BY title, tn DESC
   270    270     }
   271    271   } {one-c one-a two-b two-a three-c three-a}
................................................................................
   275    275     }
   276    276   } {one-c one-a two-b two-a three-c three-a}  ;# verify same order after sorting
   277    277   do_test 2.5c {
   278    278     db eval {
   279    279       EXPLAIN QUERY PLAN
   280    280       SELECT name FROM album JOIN track USING (aid) ORDER BY title, tn DESC
   281    281     }
   282         -} {/ORDER BY/}  ;# separate sorting pass due to mixed ASC/DESC
          282  +} {~/ORDER BY/}  ;# optimized out
   283    283   
   284    284   do_test 2.6a {
   285    285     db eval {
   286    286       SELECT name FROM album JOIN track USING (aid) ORDER BY title DESC, tn DESC
   287    287     }
   288    288   } {three-c three-a two-b two-a one-c one-a}
   289    289   do_test 2.6b {
................................................................................
   392    392     }
   393    393   } {one-a one-c two-a two-b three-a three-c}  ;# verify same order after sorting
   394    394   do_test 3.4c {
   395    395     db eval {
   396    396       EXPLAIN QUERY PLAN
   397    397       SELECT name FROM album JOIN track USING (aid) ORDER BY title, tn
   398    398     }
   399         -} {/ORDER BY/}  ;# separate sorting pass due to mismatched DESC/ASC
          399  +} {~/ORDER BY/}  ;# optimized out
   400    400   
   401    401   
   402    402   do_test 3.5a {
   403    403     db eval {
   404    404       SELECT name FROM album JOIN track USING (aid) ORDER BY title DESC, tn DESC
   405    405     }
   406    406   } {three-c three-a two-b two-a one-c one-a}
................................................................................
   410    410     }
   411    411   } {three-c three-a two-b two-a one-c one-a}  ;# verify same order after sorting
   412    412   do_test 3.5c {
   413    413     db eval {
   414    414       EXPLAIN QUERY PLAN
   415    415       SELECT name FROM album JOIN track USING (aid) ORDER BY title DESC, tn DESC
   416    416     }
   417         -} {/ORDER BY/}  ;# separate sorting pass due to mismatched ASC/DESC
          417  +} {~/ORDER BY/}  ;# optimzed out
   418    418   
   419    419   
   420    420   do_test 3.6a {
   421    421     db eval {
   422    422       SELECT name FROM album JOIN track USING (aid) ORDER BY title DESC, tn
   423    423     }
   424    424   } {three-a three-c two-a two-b one-a one-c}