/ Check-in [0d573320]
Login

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

Overview
Comment:More test cases an bug fixes for the ORDER BY optimization of joins. All veryquick tests now pass.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | qp-enhancements
Files: files | file ages | folders
SHA1: 0d573320057b0903a5589cabfb1b1ece1c57958e
User & Date: drh 2012-09-27 19:53:38
Context
2012-09-27
23:27
Fix some corner case behavior in the new ORDER BY optimization logic. Remove the SQLITE_OrderByIdx bit from the SQLITE_TESTCTRL_OPTIMIZATIONS mask, since enabling it caused many TH3 tests to fail when the NO_OPT configuration parameter was engaged, and since there really isn't any need to turn that optimization off. The SQLITE_OrderByIdxJoin bit remains. Closed-Leaf check-in: 98b63371 user: drh tags: qp-enhancements
19:53
More test cases an bug fixes for the ORDER BY optimization of joins. All veryquick tests now pass. check-in: 0d573320 user: drh tags: qp-enhancements
17:31
Test cases and bug fixes applied to the ORDER BY optimization for joins. Some test cases fail, but except for the new orderby1.test failures, all failures appear to be issues with the tests, not with the core code. check-in: 75cda864 user: drh tags: qp-enhancements
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/where.c.

  1641   1641     int nTerm;                    /* Number of ORDER BY terms */
  1642   1642     struct ExprList_item *pTerm;  /* A term of the ORDER BY clause */
  1643   1643     ExprList *pOrderBy;           /* The ORDER BY clause */
  1644   1644     Parse *pParse = p->pParse;    /* Parser context */
  1645   1645     sqlite3 *db = pParse->db;     /* Database connection */
  1646   1646     int nPriorSat;                /* ORDER BY terms satisfied by outer loops */
  1647   1647     int seenRowid = 0;            /* True if an ORDER BY rowid term is seen */
         1648  +  int nEqOneRow;                /* Idx columns that ref unique values */
  1648   1649   
  1649   1650     if( OptimizationDisabled(db, SQLITE_OrderByIdx) ) return 0;
  1650   1651     if( p->i==0 ){
  1651   1652       nPriorSat = 0;
         1653  +    nEqOneRow = nEqCol;
  1652   1654     }else{
  1653   1655       if( OptimizationDisabled(db, SQLITE_OrderByIdxJoin) ) return 0;
  1654   1656       nPriorSat = p->aLevel[p->i-1].plan.nOBSat;
         1657  +    sortOrder = bOuterRev;
         1658  +    nEqOneRow = 0;
  1655   1659     }
  1656   1660     if( p->i>0 && nEqCol==0 /*&& !allOuterLoopsUnique(p)*/ ) return nPriorSat;
  1657   1661     pOrderBy = p->pOrderBy;
  1658   1662     if( !pOrderBy ) return nPriorSat;
  1659   1663     if( wsFlags & WHERE_COLUMN_IN ) return nPriorSat;
  1660   1664     if( pIdx->bUnordered ) return nPriorSat;
  1661   1665     nTerm = pOrderBy->nExpr;
................................................................................
  1721   1725           return nPriorSat;
  1722   1726         }
  1723   1727       }
  1724   1728       assert( pIdx->aSortOrder!=0 || iColumn==-1 );
  1725   1729       assert( pTerm->sortOrder==0 || pTerm->sortOrder==1 );
  1726   1730       assert( iSortOrder==0 || iSortOrder==1 );
  1727   1731       termSortOrder = iSortOrder ^ pTerm->sortOrder;
  1728         -    if( i>nEqCol ){
         1732  +    if( i>nEqOneRow ){
  1729   1733         if( termSortOrder!=sortOrder ){
  1730   1734           /* Indices can only be used if all ORDER BY terms past the
  1731   1735           ** equality constraints are all either DESC or ASC. */
  1732   1736           break;
  1733   1737         }
  1734   1738       }else{
  1735   1739         sortOrder = termSortOrder;
................................................................................
  1737   1741       j++;
  1738   1742       pTerm++;
  1739   1743       if( iColumn<0 ){
  1740   1744         seenRowid = 1;
  1741   1745         break;
  1742   1746       }
  1743   1747     }
  1744         -  *pbRev = bOuterRev ^ sortOrder;
         1748  +  *pbRev = sortOrder;
  1745   1749   
  1746   1750     /* If there was an "ORDER BY rowid" term that matched, or it is only
  1747   1751     ** possible for a single row from this table to match, then skip over
  1748   1752     ** any additional ORDER BY terms dealing with this table.
  1749   1753     */
  1750   1754     if( seenRowid ||
  1751   1755        (   (wsFlags & WHERE_COLUMN_NULL)==0
................................................................................
  3292   3296       ** on one page and hence more pages have to be fetched.
  3293   3297       **
  3294   3298       ** The ANALYZE command and the sqlite_stat1 and sqlite_stat3 tables do
  3295   3299       ** not give us data on the relative sizes of table and index records.
  3296   3300       ** So this computation assumes table records are about twice as big
  3297   3301       ** as index records
  3298   3302       */
  3299         -    if( wsFlags==WHERE_IDX_ONLY
         3303  +    if( (wsFlags&~WHERE_REVERSE)==WHERE_IDX_ONLY
  3300   3304        && (pWC->wctrlFlags & WHERE_ONEPASS_DESIRED)==0
  3301   3305        && sqlite3GlobalConfig.bUseCis
  3302   3306        && OptimizationEnabled(pParse->db, SQLITE_CoverIdxScan)
  3303   3307       ){
  3304   3308         /* This index is not useful for indexing, but it is a covering index.
  3305   3309         ** A full-scan of the index might be a little faster than a full-scan
  3306   3310         ** of the table, so give this case a cost slightly less than a table
................................................................................
  3417   3421           }
  3418   3422         }
  3419   3423         if( nRow<2 ) nRow = 2;
  3420   3424       }
  3421   3425   
  3422   3426   
  3423   3427       WHERETRACE((
  3424         -      "%s(%s): nEq=%d nInMul=%d rangeDiv=%d bSort=%d bLookup=%d wsFlags=0x%x\n"
  3425         -      "         notReady=0x%llx log10N=%.1f nRow=%.1f cost=%.1f\n"
  3426         -      "         used=0x%llx nOrdered=%d nOBSat=%d\n",
         3428  +      "%s(%s):\n"
         3429  +      "    nEq=%d nInMul=%d rangeDiv=%d bSort=%d bLookup=%d wsFlags=0x%08x\n"
         3430  +      "    notReady=0x%llx log10N=%.1f nRow=%.1f cost=%.1f\n"
         3431  +      "    used=0x%llx nOrdered=%d nOBSat=%d\n",
  3427   3432         pSrc->pTab->zName, (pIdx ? pIdx->zName : "ipk"), 
  3428   3433         nEq, nInMul, (int)rangeDiv, bSort, bLookup, wsFlags,
  3429   3434         p->notReady, log10N, nRow, cost, used, nOrdered, nOBSat
  3430   3435       ));
  3431   3436   
  3432   3437       /* If this index is the best we have seen so far, then record this
  3433   3438       ** index and its cost in the pCost structure.
................................................................................
  5084   5089             bestJ = j;
  5085   5090           }
  5086   5091           if( doNotReorder ) break;
  5087   5092         }
  5088   5093       }
  5089   5094       assert( bestJ>=0 );
  5090   5095       assert( sWBI.notValid & getMask(pMaskSet, pTabList->a[bestJ].iCursor) );
  5091         -    WHERETRACE(("*** Optimizer selects table %d for loop %d"
  5092         -                " with cost=%.1f, nRow=%.1f, nOBSat=%d\n",
         5096  +    WHERETRACE(("*** Optimizer selects table %d for loop %d with:\n"
         5097  +                "    cost=%.1f, nRow=%.1f, nOBSat=%d wsFlags=0x%08x\n",
  5093   5098                   bestJ, pLevel-pWInfo->a, bestPlan.rCost, bestPlan.plan.nRow,
  5094         -                bestPlan.plan.nOBSat));
         5099  +                bestPlan.plan.nOBSat, bestPlan.plan.wsFlags));
  5095   5100       if( (bestPlan.plan.wsFlags & WHERE_ORDERBY)!=0 ){
  5096   5101         pWInfo->nOBSat = pOrderBy->nExpr;
  5097   5102       }
  5098   5103       if( (bestPlan.plan.wsFlags & WHERE_DISTINCT)!=0 ){
  5099   5104         assert( pWInfo->eDistinct==0 );
  5100   5105         pWInfo->eDistinct = WHERE_DISTINCT_ORDERED;
  5101   5106       }

Changes to test/e_select.test.

  1019   1019   #   These tests also show that the following is not untrue:
  1020   1020   #
  1021   1021   # EVIDENCE-OF: R-25883-55063 The expressions in the GROUP BY clause do
  1022   1022   # not have to be expressions that appear in the result.
  1023   1023   #
  1024   1024   do_select_tests e_select-4.9 {
  1025   1025     1  "SELECT group_concat(one), two FROM b1 GROUP BY two" {
  1026         -    4,5 f   1 o   7,6   s 3,2 t
         1026  +    /#,# f   1 o   #,#   s #,# t/
  1027   1027     }
  1028   1028     2  "SELECT group_concat(one), sum(one) FROM b1 GROUP BY (one>4)" {
  1029   1029       1,2,3,4 10    5,6,7 18
  1030   1030     }
  1031   1031     3  "SELECT group_concat(one) FROM b1 GROUP BY (two>'o'), one%2" {
  1032   1032       4  1,5    2,6   3,7
  1033   1033     }
................................................................................
  1036   1036     }
  1037   1037   }
  1038   1038   
  1039   1039   # EVIDENCE-OF: R-14926-50129 For the purposes of grouping rows, NULL
  1040   1040   # values are considered equal.
  1041   1041   #
  1042   1042   do_select_tests e_select-4.10 {
  1043         -  1  "SELECT group_concat(y) FROM b2 GROUP BY x" {0,1   3   2,4}
         1043  +  1  "SELECT group_concat(y) FROM b2 GROUP BY x" {/#,#   3   #,#/}
  1044   1044     2  "SELECT count(*) FROM b2 GROUP BY CASE WHEN y<4 THEN NULL ELSE 0 END" {4 1}
  1045   1045   } 
  1046   1046   
  1047   1047   # EVIDENCE-OF: R-10470-30318 The usual rules for selecting a collation
  1048   1048   # sequence with which to compare text values apply when evaluating
  1049   1049   # expressions in a GROUP BY clause.
  1050   1050   #
................................................................................
  1741   1741        1 2 3    1 2 -20    1 4  93    1 5 -1   
  1742   1742     }
  1743   1743     7  "SELECT * FROM d1 ORDER BY 1 DESC, 2, 3" {
  1744   1744        2 4 93   2 5 -1     1 2 -20    1 2 3    
  1745   1745        1 2 7    1 2 8      1 4  93    1 5 -1   
  1746   1746     }
  1747   1747     8  "SELECT z, x FROM d1 ORDER BY 2" {
  1748         -     3 1     8 1    7 1   -20 1 
  1749         -     93 1   -1 1   -1 2   93 2
         1748  +     /# 1    # 1    # 1   # 1 
         1749  +      # 1    # 1    # 2   # 2/
  1750   1750     }
  1751   1751     9  "SELECT z, x FROM d1 ORDER BY 1" {
  1752         -     -20 1  -1 2   -1 1   3 1     
  1753         -     7 1     8 1   93 2   93 1   
         1752  +     /-20 1  -1 #   -1 #   3 1
         1753  +     7 1     8 1   93 #   93 #/   
  1754   1754     }
  1755   1755   }
  1756   1756   
  1757   1757   # EVIDENCE-OF: R-63286-51977 If the ORDER BY expression is an identifier
  1758   1758   # that corresponds to the alias of one of the output columns, then the
  1759   1759   # expression is considered an alias for that column.
  1760   1760   #
................................................................................
  1762   1762     1   "SELECT z+1 AS abc FROM d1 ORDER BY abc" {
  1763   1763       -19 0 0 4 8 9 94 94
  1764   1764     }
  1765   1765     2   "SELECT z+1 AS abc FROM d1 ORDER BY abc DESC" {
  1766   1766       94 94 9 8 4 0 0 -19
  1767   1767     }
  1768   1768     3  "SELECT z AS x, x AS z FROM d1 ORDER BY z" {
  1769         -    3 1    8 1    7 1    -20 1    93 1    -1 1    -1 2    93 2
         1769  +    /# 1    # 1    # 1    # 1    # 1    # 1    # 2    # 2/
  1770   1770     }
  1771   1771     4  "SELECT z AS x, x AS z FROM d1 ORDER BY x" {
  1772         -    -20 1    -1 2    -1 1    3 1    7 1    8 1    93 2    93 1
         1772  +    /-20 1    -1 #    -1 #    3 1    7 1    8 1    93 #    93 #/
  1773   1773     }
  1774   1774   }
  1775   1775   
  1776   1776   # EVIDENCE-OF: R-65068-27207 Otherwise, if the ORDER BY expression is
  1777   1777   # any other expression, it is evaluated and the returned value used to
  1778   1778   # order the output rows.
  1779   1779   #

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/}  ;# ORDER BY optimized-out
          117  +} {/ORDER BY/}  ;# separate sorting pass due to mixed DESC/ASC
   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}
................................................................................
   126    126     db eval {
   127    127       SELECT name FROM album JOIN track USING (aid) ORDER BY +title, +tn DESC
   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         -    SELECT name FROM album JOIN track USING (aid) ORDER BY title DESC, tn
          133  +    SELECT name FROM album JOIN track USING (aid) ORDER BY title, tn DESC
   134    134     }
   135         -} {~/ORDER BY/}  ;# ORDER BY optimized-out
          135  +} {/ORDER BY/}  ;# separate sorting pass due to mixed DESC/ASC
   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 {
................................................................................
   147    147   do_test 1.6c {
   148    148     db eval {
   149    149       EXPLAIN QUERY PLAN
   150    150       SELECT name FROM album JOIN track USING (aid) ORDER BY title DESC, tn DESC
   151    151     }
   152    152   } {~/ORDER BY/}  ;# ORDER BY optimized-out
   153    153   
          154  +
          155  +# Reconstruct the test data to use indices rather than integer primary keys.
          156  +#
          157  +do_test 2.0 {
          158  +  db eval {
          159  +    BEGIN;
          160  +    DROP TABLE album;
          161  +    DROP TABLE track;
          162  +    CREATE TABLE album(
          163  +      aid INT PRIMARY KEY,
          164  +      title TEXT NOT NULL
          165  +    );
          166  +    CREATE INDEX album_i1 ON album(title, aid);
          167  +    CREATE TABLE track(
          168  +      aid INTEGER NOT NULL REFERENCES album,
          169  +      tn INTEGER NOT NULL,
          170  +      name TEXT,
          171  +      UNIQUE(aid, tn)
          172  +    );
          173  +    INSERT INTO album VALUES(1, '1-one'), (2, '2-two'), (3, '3-three');
          174  +    INSERT INTO track VALUES
          175  +        (1, 1, 'one-a'),
          176  +        (2, 2, 'two-b'),
          177  +        (3, 3, 'three-c'),
          178  +        (1, 3, 'one-c'),
          179  +        (2, 1, 'two-a'),
          180  +        (3, 1, 'three-a');
          181  +    COMMIT;
          182  +  }
          183  +} {}
          184  +do_test 2.1a {
          185  +  db eval {
          186  +    SELECT name FROM album JOIN track USING (aid) ORDER BY title, tn
          187  +  }
          188  +} {one-a one-c two-a two-b three-a three-c}
          189  +
          190  +# Verify that the ORDER BY clause is optimized out
          191  +#
          192  +do_test 2.1b {
          193  +  db eval {
          194  +    EXPLAIN QUERY PLAN
          195  +    SELECT name FROM album JOIN track USING (aid) ORDER BY title, tn
          196  +  }
          197  +} {~/ORDER BY/}  ;# ORDER BY optimized out
          198  +
          199  +# The same query with ORDER BY clause optimization disabled via + operators
          200  +# should give exactly the same answer.
          201  +#
          202  +do_test 2.2a {
          203  +  db eval {
          204  +    SELECT name FROM album JOIN track USING (aid) ORDER BY +title, +tn
          205  +  }
          206  +} {one-a one-c two-a two-b three-a three-c}
          207  +
          208  +# The output is sorted manually in this case.
          209  +#
          210  +do_test 2.2b {
          211  +  db eval {
          212  +    EXPLAIN QUERY PLAN
          213  +    SELECT name FROM album JOIN track USING (aid) ORDER BY +title, +tn
          214  +  }
          215  +} {/ORDER BY/}   ;# separate sorting pass due to "+" on ORDER BY terms
          216  +
          217  +# The same query with ORDER BY optimizations turned off via built-in test.
          218  +#
          219  +do_test 2.3a {
          220  +  optimization_control db order-by-idx-join 0
          221  +  db cache flush
          222  +  db eval {
          223  +    SELECT name FROM album JOIN track USING (aid) ORDER BY title, tn
          224  +  }
          225  +} {one-a one-c two-a two-b three-a three-c}
          226  +do_test 2.3b {
          227  +  db eval {
          228  +    EXPLAIN QUERY PLAN
          229  +    SELECT name FROM album JOIN track USING (aid) ORDER BY title, tn
          230  +  }
          231  +} {/ORDER BY/}   ;# separate sorting pass due to disabled optimization
          232  +optimization_control db all 1
          233  +db cache flush
          234  +
          235  +# Reverse order sorts
          236  +#
          237  +do_test 2.4a {
          238  +  db eval {
          239  +    SELECT name FROM album JOIN track USING (aid) ORDER BY title DESC, tn
          240  +  }
          241  +} {three-a three-c two-a two-b one-a one-c}
          242  +do_test 2.4b {
          243  +  db eval {
          244  +    SELECT name FROM album JOIN track USING (aid) ORDER BY +title DESC, +tn
          245  +  }
          246  +} {three-a three-c two-a two-b one-a one-c}  ;# verify same order after sorting
          247  +do_test 2.4c {
          248  +  db eval {
          249  +    EXPLAIN QUERY PLAN
          250  +    SELECT name FROM album JOIN track USING (aid) ORDER BY title DESC, tn
          251  +  }
          252  +} {/ORDER BY/}  ;# separate sorting pass due to mixed DESC/ASC
          253  +
          254  +
          255  +do_test 2.5a {
          256  +  db eval {
          257  +    SELECT name FROM album JOIN track USING (aid) ORDER BY title, tn DESC
          258  +  }
          259  +} {one-c one-a two-b two-a three-c three-a}
          260  +do_test 2.5b {
          261  +  db eval {
          262  +    SELECT name FROM album JOIN track USING (aid) ORDER BY +title, +tn DESC
          263  +  }
          264  +} {one-c one-a two-b two-a three-c three-a}  ;# verify same order after sorting
          265  +do_test 2.5c {
          266  +  db eval {
          267  +    EXPLAIN QUERY PLAN
          268  +    SELECT name FROM album JOIN track USING (aid) ORDER BY title, tn DESC
          269  +  }
          270  +} {/ORDER BY/}  ;# separate sorting pass due to mixed ASC/DESC
          271  +
          272  +do_test 2.6a {
          273  +  db eval {
          274  +    SELECT name FROM album JOIN track USING (aid) ORDER BY title DESC, tn DESC
          275  +  }
          276  +} {three-c three-a two-b two-a one-c one-a}
          277  +do_test 2.6b {
          278  +  db eval {
          279  +    SELECT name FROM album JOIN track USING (aid) ORDER BY +title DESC, +tn DESC
          280  +  }
          281  +} {three-c three-a two-b two-a one-c one-a}  ;# verify same order after sorting
          282  +do_test 2.6c {
          283  +  db eval {
          284  +    EXPLAIN QUERY PLAN
          285  +    SELECT name FROM album JOIN track USING (aid) ORDER BY title DESC, tn DESC
          286  +  }
          287  +} {~/ORDER BY/}  ;# ORDER BY optimized-out
          288  +
          289  +
          290  +# Generate another test dataset, but this time using mixed ASC/DESC indices.
          291  +#
          292  +do_test 3.0 {
          293  +  db eval {
          294  +    BEGIN;
          295  +    DROP TABLE album;
          296  +    DROP TABLE track;
          297  +    CREATE TABLE album(
          298  +      aid INTEGER PRIMARY KEY,
          299  +      title TEXT UNIQUE NOT NULL
          300  +    );
          301  +    CREATE TABLE track(
          302  +      tid INTEGER PRIMARY KEY,
          303  +      aid INTEGER NOT NULL REFERENCES album,
          304  +      tn INTEGER NOT NULL,
          305  +      name TEXT,
          306  +      UNIQUE(aid ASC, tn DESC)
          307  +    );
          308  +    INSERT INTO album VALUES(1, '1-one'), (2, '2-two'), (3, '3-three');
          309  +    INSERT INTO track VALUES
          310  +        (NULL, 1, 1, 'one-a'),
          311  +        (NULL, 2, 2, 'two-b'),
          312  +        (NULL, 3, 3, 'three-c'),
          313  +        (NULL, 1, 3, 'one-c'),
          314  +        (NULL, 2, 1, 'two-a'),
          315  +        (NULL, 3, 1, 'three-a');
          316  +    COMMIT;
          317  +  }
          318  +} {}
          319  +do_test 3.1a {
          320  +  db eval {
          321  +    SELECT name FROM album JOIN track USING (aid) ORDER BY title, tn DESC
          322  +  }
          323  +} {one-c one-a two-b two-a three-c three-a}
          324  +
          325  +# Verify that the ORDER BY clause is optimized out
          326  +#
          327  +do_test 3.1b {
          328  +  db eval {
          329  +    EXPLAIN QUERY PLAN
          330  +    SELECT name FROM album JOIN track USING (aid) ORDER BY title, tn DESC
          331  +  }
          332  +} {~/ORDER BY/}  ;# ORDER BY optimized out
          333  +
          334  +# The same query with ORDER BY clause optimization disabled via + operators
          335  +# should give exactly the same answer.
          336  +#
          337  +do_test 3.2a {
          338  +  db eval {
          339  +    SELECT name FROM album JOIN track USING (aid) ORDER BY +title, +tn DESC
          340  +  }
          341  +} {one-c one-a two-b two-a three-c three-a}
          342  +
          343  +# The output is sorted manually in this case.
          344  +#
          345  +do_test 3.2b {
          346  +  db eval {
          347  +    EXPLAIN QUERY PLAN
          348  +    SELECT name FROM album JOIN track USING (aid) ORDER BY +title, +tn DESC
          349  +  }
          350  +} {/ORDER BY/}   ;# separate sorting pass due to "+" on ORDER BY terms
          351  +
          352  +# The same query with ORDER BY optimizations turned off via built-in test.
          353  +#
          354  +do_test 3.3a {
          355  +  optimization_control db order-by-idx-join 0
          356  +  db cache flush
          357  +  db eval {
          358  +    SELECT name FROM album JOIN track USING (aid) ORDER BY title, tn DESC
          359  +  }
          360  +} {one-c one-a two-b two-a three-c three-a}
          361  +do_test 3.3b {
          362  +  db eval {
          363  +    EXPLAIN QUERY PLAN
          364  +    SELECT name FROM album JOIN track USING (aid) ORDER BY title, tn DESC
          365  +  }
          366  +} {/ORDER BY/}   ;# separate sorting pass due to disabled optimization
          367  +optimization_control db all 1
          368  +db cache flush
          369  +
          370  +# Without the mixed ASC/DESC on ORDER BY
          371  +#
          372  +do_test 3.4a {
          373  +  db eval {
          374  +    SELECT name FROM album JOIN track USING (aid) ORDER BY title, tn
          375  +  }
          376  +} {one-a one-c two-a two-b three-a three-c}
          377  +do_test 3.4b {
          378  +  db eval {
          379  +    SELECT name FROM album JOIN track USING (aid) ORDER BY +title, +tn
          380  +  }
          381  +} {one-a one-c two-a two-b three-a three-c}  ;# verify same order after sorting
          382  +do_test 3.4c {
          383  +  db eval {
          384  +    EXPLAIN QUERY PLAN
          385  +    SELECT name FROM album JOIN track USING (aid) ORDER BY title, tn
          386  +  }
          387  +} {/ORDER BY/}  ;# separate sorting pass due to mismatched DESC/ASC
          388  +
          389  +
          390  +do_test 3.5a {
          391  +  db eval {
          392  +    SELECT name FROM album JOIN track USING (aid) ORDER BY title DESC, tn DESC
          393  +  }
          394  +} {three-c three-a two-b two-a one-c one-a}
          395  +do_test 3.5b {
          396  +  db eval {
          397  +    SELECT name FROM album JOIN track USING (aid) ORDER BY +title DESC, +tn DESC
          398  +  }
          399  +} {three-c three-a two-b two-a one-c one-a}  ;# verify same order after sorting
          400  +do_test 3.5c {
          401  +  db eval {
          402  +    EXPLAIN QUERY PLAN
          403  +    SELECT name FROM album JOIN track USING (aid) ORDER BY title DESC, tn DESC
          404  +  }
          405  +} {/ORDER BY/}  ;# separate sorting pass due to mismatched ASC/DESC
          406  +
          407  +
          408  +do_test 3.6a {
          409  +  db eval {
          410  +    SELECT name FROM album JOIN track USING (aid) ORDER BY title DESC, tn
          411  +  }
          412  +} {three-a three-c two-a two-b one-a one-c}
          413  +do_test 3.6b {
          414  +  db eval {
          415  +    SELECT name FROM album JOIN track USING (aid) ORDER BY +title DESC, +tn
          416  +  }
          417  +} {three-a three-c two-a two-b one-a one-c}  ;# verify same order after sorting
          418  +do_test 3.6c {
          419  +  db eval {
          420  +    EXPLAIN QUERY PLAN
          421  +    SELECT name FROM album JOIN track USING (aid) ORDER BY title DESC, tn
          422  +  }
          423  +} {~/ORDER BY/}  ;# inverted ASC/DESC is optimized out
          424  +
   154    425   
   155    426   finish_test

Changes to test/tester.tcl.

   534    534     if {![info exists ::G(match)] || [string match $::G(match) $name]} {
   535    535       if {[catch {uplevel #0 "$cmd;\n"} result]} {
   536    536         puts "\nError: $result"
   537    537         fail_test $name
   538    538       } else {
   539    539         if {[regexp {^~?/.*/$} $expected]} {
   540    540           if {[string index $expected 0]=="~"} {
   541         -          set re [string range $expected 2 end-1]
          541  +          set re [string map {# {[-0-9.]+}} [string range $expected 2 end-1]]
   542    542             set ok [expr {![regexp $re $result]}]
   543    543           } else {
   544         -          set re [string range $expected 1 end-1]
          544  +          set re [string map {# {[-0-9.]+}} [string range $expected 1 end-1]]
   545    545             set ok [regexp $re $result]
   546    546           }
   547    547         } else {
   548    548           set ok [expr {[string compare $result $expected]==0}]
   549    549         }
   550    550         if {!$ok} {
   551    551           # if {![info exists ::testprefix] || $::testprefix eq ""} {

Changes to test/tkt-cbd054fa6b.test.

    46     46   do_test tkt-cbd05-1.3 {
    47     47     execsql { 
    48     48       SELECT tbl,idx,group_concat(sample,' ') 
    49     49       FROM sqlite_stat3 
    50     50       WHERE idx = 't1_x' 
    51     51       GROUP BY tbl,idx
    52     52     }
    53         -} {t1 t1_x { A B C D E F G H I}}
           53  +} {/t1 t1_x .[ ABCDEFGHI]{10}./}
    54     54   
    55     55   do_test tkt-cbd05-2.1 {
    56     56     db eval {
    57     57       DROP TABLE t1;
    58     58       CREATE TABLE t1(a INTEGER PRIMARY KEY, b BLOB UNIQUE NOT NULL);
    59     59       CREATE INDEX t1_x ON t1(b);
    60     60       INSERT INTO t1 VALUES(NULL, X'');
................................................................................
    78     78   do_test tkt-cbd05-2.3 {
    79     79     execsql { 
    80     80       SELECT tbl,idx,group_concat(sample,' ') 
    81     81       FROM sqlite_stat3 
    82     82       WHERE idx = 't1_x' 
    83     83       GROUP BY tbl,idx
    84     84     }
    85         -} {t1 t1_x { A B C D E F G H I}}
           85  +} {/t1 t1_x .[ ABCDEFGHI]{10}./}
    86     86   
    87     87   finish_test

Changes to test/where.test.

   379    379         SELECT * FROM t1 WHERE rowid+0 IN (1,2,3,1234) order by 1;
   380    380       }
   381    381     } {1 0 4 2 1 9 3 1 16 102}
   382    382     do_test where-5.3 {
   383    383       count {
   384    384         SELECT * FROM t1 WHERE w IN (-1,1,2,3) order by 1;
   385    385       }
   386         -  } {1 0 4 2 1 9 3 1 16 14}
          386  +  } {1 0 4 2 1 9 3 1 16 13}
   387    387     do_test where-5.4 {
   388    388       count {
   389    389         SELECT * FROM t1 WHERE w+0 IN (-1,1,2,3) order by 1;
   390    390       }
   391    391     } {1 0 4 2 1 9 3 1 16 102}
   392    392     do_test where-5.5 {
   393    393       count {
................................................................................
  1105   1105     } 
  1106   1106   } {1/4 1/1 4/4 4/1 nosort}
  1107   1107   do_test where-14.5 {
  1108   1108     # This test case changed from "nosort" to "sort". See ticket 2a5629202f.
  1109   1109     cksort {
  1110   1110       SELECT x.a || '/' || y.a FROM t8 x, t8 y ORDER BY x.b, x.a||x.b
  1111   1111     } 
  1112         -} {4/4 4/1 1/4 1/1 sort}
         1112  +} {/4/[14] 4/[14] 1/[14] 1/[14] sort/}
  1113   1113   do_test where-14.6 {
  1114   1114     # This test case changed from "nosort" to "sort". See ticket 2a5629202f.
  1115   1115     cksort {
  1116   1116       SELECT x.a || '/' || y.a FROM t8 x, t8 y ORDER BY x.b, x.a||x.b DESC
  1117   1117     } 
  1118         -} {4/4 4/1 1/4 1/1 sort}
         1118  +} {/4/[14] 4/[14] 1/[14] 1/[14] sort/}
  1119   1119   do_test where-14.7 {
  1120   1120     cksort {
  1121   1121       SELECT x.a || '/' || y.a FROM t8 x, t8 y ORDER BY x.b, y.a||y.b
  1122   1122     } 
  1123   1123   } {4/1 4/4 1/1 1/4 sort}
  1124   1124   do_test where-14.7.1 {
  1125   1125     cksort {