/ Check-in [912faf18]
Login

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

Overview
Comment:When optimizing out an ORDER BY clause due to uniqueness constraints, make sure unused terms to the right in the ORDER BY clause to not reference other tables in a join. Ticket #2211. Additional test cases needed before closing this ticket. (CVS 3629)
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 912faf18d86416b1a36660851f8a4554e6746875
User & Date: drh 2007-02-06 13:26:33
Context
2007-02-06
23:41
Additional tests to give full coverage testing to ticket #2211. (CVS 3630) check-in: ecb1f2fd user: drh tags: trunk
13:26
When optimizing out an ORDER BY clause due to uniqueness constraints, make sure unused terms to the right in the ORDER BY clause to not reference other tables in a join. Ticket #2211. Additional test cases needed before closing this ticket. (CVS 3629) check-in: 912faf18 user: drh tags: trunk
11:11
Check the return value of lseek() in os_unix.c to make sure it really worked. (CVS 3628) check-in: e4408dd1 user: drh tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/where.c.

    12     12   ** This module contains C code that generates VDBE code used to process
    13     13   ** the WHERE clause of SQL statements.  This module is reponsible for
    14     14   ** generating the code that loops through a table looking for applicable
    15     15   ** rows.  Indices are selected and used to speed the search when doing
    16     16   ** so is applicable.  Because this module is responsible for selecting
    17     17   ** indices, you might also think of this module as the "query optimizer".
    18     18   **
    19         -** $Id: where.c,v 1.236 2007/01/25 16:56:07 drh Exp $
           19  +** $Id: where.c,v 1.237 2007/02/06 13:26:33 drh Exp $
    20     20   */
    21     21   #include "sqliteInt.h"
    22     22   
    23     23   /*
    24     24   ** The number of bits in a Bitmask.  "BMS" means "BitMask Size".
    25     25   */
    26     26   #define BMS  (sizeof(Bitmask)*8)
................................................................................
    39     39   #else
    40     40   # define TRACE(X)
    41     41   #endif
    42     42   
    43     43   /* Forward reference
    44     44   */
    45     45   typedef struct WhereClause WhereClause;
           46  +typedef struct ExprMaskSet ExprMaskSet;
    46     47   
    47     48   /*
    48     49   ** The query generator uses an array of instances of this structure to
    49     50   ** help it analyze the subexpressions of the WHERE clause.  Each WHERE
    50     51   ** clause subexpression is separated from the others by an AND operator.
    51     52   **
    52     53   ** All WhereTerms are collected into a single WhereClause structure.  
................................................................................
   102    103   
   103    104   /*
   104    105   ** An instance of the following structure holds all information about a
   105    106   ** WHERE clause.  Mostly this is a container for one or more WhereTerms.
   106    107   */
   107    108   struct WhereClause {
   108    109     Parse *pParse;           /* The parser context */
          110  +  ExprMaskSet *pMaskSet;   /* Mapping of table indices to bitmasks */
   109    111     int nTerm;               /* Number of terms */
   110    112     int nSlot;               /* Number of entries in a[] */
   111    113     WhereTerm *a;            /* Each a[] describes a term of the WHERE cluase */
   112    114     WhereTerm aStatic[10];   /* Initial static space for a[] */
   113    115   };
   114    116   
   115    117   /*
................................................................................
   134    136   ** Note that the mapping is not necessarily ordered.  In the example
   135    137   ** above, the mapping might go like this:  4->3, 5->1, 8->2, 29->0,
   136    138   ** 57->5, 73->4.  Or one of 719 other combinations might be used. It
   137    139   ** does not really matter.  What is important is that sparse cursor
   138    140   ** numbers all get mapped into bit numbers that begin with 0 and contain
   139    141   ** no gaps.
   140    142   */
   141         -typedef struct ExprMaskSet ExprMaskSet;
   142    143   struct ExprMaskSet {
   143    144     int n;                        /* Number of assigned cursor values */
   144    145     int ix[sizeof(Bitmask)*8];    /* Cursor assigned to each bit */
   145    146   };
   146    147   
   147    148   
   148    149   /*
................................................................................
   182    183   #define WHERE_REVERSE      0x200000   /* Scan in reverse order */
   183    184   #define WHERE_UNIQUE       0x400000   /* Selects no more than one row */
   184    185   #define WHERE_VIRTUALTABLE 0x800000   /* Use virtual-table processing */
   185    186   
   186    187   /*
   187    188   ** Initialize a preallocated WhereClause structure.
   188    189   */
   189         -static void whereClauseInit(WhereClause *pWC, Parse *pParse){
          190  +static void whereClauseInit(
          191  +  WhereClause *pWC,        /* The WhereClause to be initialized */
          192  +  Parse *pParse,           /* The parsing context */
          193  +  ExprMaskSet *pMaskSet    /* Mapping from table indices to bitmasks */
          194  +){
   190    195     pWC->pParse = pParse;
          196  +  pWC->pMaskSet = pMaskSet;
   191    197     pWC->nTerm = 0;
   192    198     pWC->nSlot = ARRAYSIZE(pWC->aStatic);
   193    199     pWC->a = pWC->aStatic;
   194    200   }
   195    201   
   196    202   /*
   197    203   ** Deallocate a WhereClause structure.  The WhereClause structure
................................................................................
   459    465         return pTerm;
   460    466       }
   461    467     }
   462    468     return 0;
   463    469   }
   464    470   
   465    471   /* Forward reference */
   466         -static void exprAnalyze(SrcList*, ExprMaskSet*, WhereClause*, int);
          472  +static void exprAnalyze(SrcList*, WhereClause*, int);
   467    473   
   468    474   /*
   469    475   ** Call exprAnalyze on all terms in a WHERE clause.  
   470    476   **
   471    477   **
   472    478   */
   473    479   static void exprAnalyzeAll(
   474    480     SrcList *pTabList,       /* the FROM clause */
   475         -  ExprMaskSet *pMaskSet,   /* table masks */
   476    481     WhereClause *pWC         /* the WHERE clause to be analyzed */
   477    482   ){
   478    483     int i;
   479    484     for(i=pWC->nTerm-1; i>=0; i--){
   480         -    exprAnalyze(pTabList, pMaskSet, pWC, i);
          485  +    exprAnalyze(pTabList, pWC, i);
   481    486     }
   482    487   }
   483    488   
   484    489   #ifndef SQLITE_OMIT_LIKE_OPTIMIZATION
   485    490   /*
   486    491   ** Check to see if the given expression is a LIKE or GLOB operator that
   487    492   ** can be optimized using inequality constraints.  Return TRUE if it is
................................................................................
   588    593   ** to the standard form of "X <op> <expr>".  If the expression is of
   589    594   ** the form "X <op> Y" where both X and Y are columns, then the original
   590    595   ** expression is unchanged and a new virtual expression of the form
   591    596   ** "Y <op> X" is added to the WHERE clause and analyzed separately.
   592    597   */
   593    598   static void exprAnalyze(
   594    599     SrcList *pSrc,            /* the FROM clause */
   595         -  ExprMaskSet *pMaskSet,    /* table masks */
   596    600     WhereClause *pWC,         /* the WHERE clause */
   597    601     int idxTerm               /* Index of the term to be analyzed */
   598    602   ){
   599    603     WhereTerm *pTerm = &pWC->a[idxTerm];
          604  +  ExprMaskSet *pMaskSet = pWC->pMaskSet;
   600    605     Expr *pExpr = pTerm->pExpr;
   601    606     Bitmask prereqLeft;
   602    607     Bitmask prereqAll;
   603    608     int nPattern;
   604    609     int isComplete;
   605    610     int op;
   606    611   
................................................................................
   675    680       assert( pList->nExpr==2 );
   676    681       for(i=0; i<2; i++){
   677    682         Expr *pNewExpr;
   678    683         int idxNew;
   679    684         pNewExpr = sqlite3Expr(ops[i], sqlite3ExprDup(pExpr->pLeft),
   680    685                                sqlite3ExprDup(pList->a[i].pExpr), 0);
   681    686         idxNew = whereClauseInsert(pWC, pNewExpr, TERM_VIRTUAL|TERM_DYNAMIC);
   682         -      exprAnalyze(pSrc, pMaskSet, pWC, idxNew);
          687  +      exprAnalyze(pSrc, pWC, idxNew);
   683    688         pTerm = &pWC->a[idxTerm];
   684    689         pWC->a[idxNew].iParent = idxTerm;
   685    690       }
   686    691       pTerm->nChild = 2;
   687    692     }
   688    693   #endif /* SQLITE_OMIT_BETWEEN_OPTIMIZATION */
   689    694   
................................................................................
   704    709       int ok;
   705    710       int i, j;
   706    711       int iColumn, iCursor;
   707    712       WhereClause sOr;
   708    713       WhereTerm *pOrTerm;
   709    714   
   710    715       assert( (pTerm->flags & TERM_DYNAMIC)==0 );
   711         -    whereClauseInit(&sOr, pWC->pParse);
          716  +    whereClauseInit(&sOr, pWC->pParse, pMaskSet);
   712    717       whereSplit(&sOr, pExpr, TK_OR);
   713         -    exprAnalyzeAll(pSrc, pMaskSet, &sOr);
          718  +    exprAnalyzeAll(pSrc, &sOr);
   714    719       assert( sOr.nTerm>0 );
   715    720       j = 0;
   716    721       do{
   717    722         iColumn = sOr.a[j].leftColumn;
   718    723         iCursor = sOr.a[j].leftCursor;
   719    724         ok = iCursor>=0;
   720    725         for(i=sOr.nTerm-1, pOrTerm=sOr.a; i>=0 && ok; i--, pOrTerm++){
................................................................................
   746    751         pDup = sqlite3ExprDup(pLeft);
   747    752         pNew = sqlite3Expr(TK_IN, pDup, 0, 0);
   748    753         if( pNew ){
   749    754           int idxNew;
   750    755           transferJoinMarkings(pNew, pExpr);
   751    756           pNew->pList = pList;
   752    757           idxNew = whereClauseInsert(pWC, pNew, TERM_VIRTUAL|TERM_DYNAMIC);
   753         -        exprAnalyze(pSrc, pMaskSet, pWC, idxNew);
          758  +        exprAnalyze(pSrc, pWC, idxNew);
   754    759           pTerm = &pWC->a[idxTerm];
   755    760           pWC->a[idxNew].iParent = idxTerm;
   756    761           pTerm->nChild = 1;
   757    762         }else{
   758    763           sqlite3ExprListDelete(pList);
   759    764         }
   760    765       }
................................................................................
   783    788       pStr2 = sqlite3ExprDup(pStr1);
   784    789       if( pStr2 ){
   785    790         assert( pStr2->token.dyn );
   786    791         ++*(u8*)&pStr2->token.z[nPattern-1];
   787    792       }
   788    793       pNewExpr1 = sqlite3Expr(TK_GE, sqlite3ExprDup(pLeft), pStr1, 0);
   789    794       idxNew1 = whereClauseInsert(pWC, pNewExpr1, TERM_VIRTUAL|TERM_DYNAMIC);
   790         -    exprAnalyze(pSrc, pMaskSet, pWC, idxNew1);
          795  +    exprAnalyze(pSrc, pWC, idxNew1);
   791    796       pNewExpr2 = sqlite3Expr(TK_LT, sqlite3ExprDup(pLeft), pStr2, 0);
   792    797       idxNew2 = whereClauseInsert(pWC, pNewExpr2, TERM_VIRTUAL|TERM_DYNAMIC);
   793         -    exprAnalyze(pSrc, pMaskSet, pWC, idxNew2);
          798  +    exprAnalyze(pSrc, pWC, idxNew2);
   794    799       pTerm = &pWC->a[idxTerm];
   795    800       if( isComplete ){
   796    801         pWC->a[idxNew1].iParent = idxTerm;
   797    802         pWC->a[idxNew2].iParent = idxTerm;
   798    803         pTerm->nChild = 2;
   799    804       }
   800    805     }
................................................................................
   832    837         pTerm->flags |= TERM_COPIED;
   833    838         pNewTerm->prereqAll = pTerm->prereqAll;
   834    839       }
   835    840     }
   836    841   #endif /* SQLITE_OMIT_VIRTUALTABLE */
   837    842   }
   838    843   
          844  +/*
          845  +** Return TRUE if any of the expressions in pList->a[iFirst...] contain
          846  +** a reference to any table other than the iBase table.
          847  +*/
          848  +static int referencesOtherTables(
          849  +  ExprList *pList,          /* Search expressions in ths list */
          850  +  ExprMaskSet *pMaskSet,    /* Mapping from tables to bitmaps */
          851  +  int iFirst,               /* Be searching with the iFirst-th expression */
          852  +  int iBase                 /* Ignore references to this table */
          853  +){
          854  +  Bitmask allowed = ~getMask(pMaskSet, iBase);
          855  +  while( iFirst<pList->nExpr ){
          856  +    if( (exprTableUsage(pMaskSet, pList->a[iFirst++].pExpr)&allowed)!=0 ){
          857  +      return 1;
          858  +    }
          859  +  }
          860  +  return 0;
          861  +}
          862  +
   839    863   
   840    864   /*
   841    865   ** This routine decides if pIdx can be used to satisfy the ORDER BY
   842    866   ** clause.  If it can, it returns 1.  If pIdx cannot satisfy the
   843    867   ** ORDER BY clause, this routine returns 0.
   844    868   **
   845    869   ** pOrderBy is an ORDER BY clause from a SELECT statement.  pTab is the
................................................................................
   854    878   ** ASC or DESC.  (Terms of the ORDER BY clause past the end of a UNIQUE
   855    879   ** index do not need to satisfy this constraint.)  The *pbRev value is
   856    880   ** set to 1 if the ORDER BY clause is all DESC and it is set to 0 if
   857    881   ** the ORDER BY clause is all ASC.
   858    882   */
   859    883   static int isSortingIndex(
   860    884     Parse *pParse,          /* Parsing context */
          885  +  ExprMaskSet *pMaskSet,  /* Mapping from table indices to bitmaps */
   861    886     Index *pIdx,            /* The index we are testing */
   862    887     int base,               /* Cursor number for the table to be sorted */
   863    888     ExprList *pOrderBy,     /* The ORDER BY clause */
   864    889     int nEqCol,             /* Number of index columns with == constraints */
   865    890     int *pbRev              /* Set to 1 if ORDER BY is DESC */
   866    891   ){
   867    892     int i, j;                       /* Loop counters */
................................................................................
   890    915       int iSortOrder;    /* 1 for DESC, 0 for ASC on the i-th index term */
   891    916       const char *zColl; /* Name of the collating sequence for i-th index term */
   892    917   
   893    918       pExpr = pTerm->pExpr;
   894    919       if( pExpr->op!=TK_COLUMN || pExpr->iTable!=base ){
   895    920         /* Can not use an index sort on anything that is not a column in the
   896    921         ** left-most table of the FROM clause */
   897         -      return 0;
          922  +      break;
   898    923       }
   899    924       pColl = sqlite3ExprCollSeq(pParse, pExpr);
   900    925       if( !pColl ){
   901    926         pColl = db->pDfltColl;
   902    927       }
   903    928       if( i<pIdx->nColumn ){
   904    929         iColumn = pIdx->aiColumn[i];
................................................................................
   937    962           return 0;
   938    963         }
   939    964       }else{
   940    965         sortOrder = termSortOrder;
   941    966       }
   942    967       j++;
   943    968       pTerm++;
   944         -    if( iColumn<0 ){
          969  +    if( iColumn<0 && !referencesOtherTables(pOrderBy, pMaskSet, j, base) ){
   945    970         /* If the indexed column is the primary key and everything matches
   946         -      ** so far, then we are assured that the index can be used to sort
   947         -      ** because the primary key is unique and so none of the other columns
   948         -      ** will make any difference
          971  +      ** so far and none of the ORDER BY terms to the right reference other
          972  +      ** tables in the join, then we are assured that the index can be used 
          973  +      ** to sort because the primary key is unique and so none of the other
          974  +      ** columns will make any difference
   949    975         */
   950    976         j = nTerm;
   951    977       }
   952    978     }
   953    979   
   954    980     *pbRev = sortOrder!=0;
   955    981     if( j>=nTerm ){
   956    982       /* All terms of the ORDER BY clause are covered by this index so
   957    983       ** this index can be used for sorting. */
   958    984       return 1;
   959    985     }
   960         -  if( j==pIdx->nColumn && pIdx->onError!=OE_None ){
          986  +  if( pIdx->onError!=OE_None && i==pIdx->nColumn
          987  +      && !referencesOtherTables(pOrderBy, pMaskSet, j, base) ){
   961    988       /* All terms of this index match some prefix of the ORDER BY clause
   962         -    ** and this index is UNIQUE, so this index can be used for sorting. */
          989  +    ** and the index is UNIQUE and no terms on the tail of the ORDER BY
          990  +    ** clause reference other tables in a join.  If this is all true then
          991  +    ** the order by clause is superfluous. */
   963    992       return 1;
   964    993     }
   965    994     return 0;
   966    995   }
   967    996   
   968    997   /*
   969    998   ** Check table to see if the ORDER BY clause in pOrderBy can be satisfied
   970    999   ** by sorting in order of ROWID.  Return true if so and set *pbRev to be
   971   1000   ** true for reverse ROWID and false for forward ROWID order.
   972   1001   */
   973   1002   static int sortableByRowid(
   974   1003     int base,               /* Cursor number for table to be sorted */
   975   1004     ExprList *pOrderBy,     /* The ORDER BY clause */
         1005  +  ExprMaskSet *pMaskSet,  /* Mapping from tables to bitmaps */
   976   1006     int *pbRev              /* Set to 1 if ORDER BY is DESC */
   977   1007   ){
   978   1008     Expr *p;
   979   1009   
   980   1010     assert( pOrderBy!=0 );
   981   1011     assert( pOrderBy->nExpr>0 );
   982   1012     p = pOrderBy->a[0].pExpr;
   983         -  if( p->op==TK_COLUMN && p->iTable==base && p->iColumn==-1 ){
         1013  +  if( p->op==TK_COLUMN && p->iTable==base && p->iColumn==-1
         1014  +    && !referencesOtherTables(pOrderBy, pMaskSet, 1, base) ){
   984   1015       *pbRev = pOrderBy->a[0].sortOrder;
   985   1016       return 1;
   986   1017     }
   987   1018     return 0;
   988   1019   }
   989   1020   
   990   1021   /*
................................................................................
  1294   1325     ** clause that refer to the ROWID, then we will never be able to do
  1295   1326     ** anything other than a full table scan on this table.  We might as
  1296   1327     ** well put it first in the join order.  That way, perhaps it can be
  1297   1328     ** referenced by other tables in the join.
  1298   1329     */
  1299   1330     if( pProbe==0 &&
  1300   1331        findTerm(pWC, iCur, -1, 0, WO_EQ|WO_IN|WO_LT|WO_LE|WO_GT|WO_GE,0)==0 &&
  1301         -     (pOrderBy==0 || !sortableByRowid(iCur, pOrderBy, &rev)) ){
         1332  +     (pOrderBy==0 || !sortableByRowid(iCur, pOrderBy, pWC->pMaskSet, &rev)) ){
  1302   1333       *pFlags = 0;
  1303   1334       *ppIndex = 0;
  1304   1335       *pnEq = 0;
  1305   1336       return 0.0;
  1306   1337     }
  1307   1338   
  1308   1339     /* Check for a rowid=EXPR or rowid IN (...) constraints
................................................................................
  1356   1387     }else{
  1357   1388       flags = 0;
  1358   1389     }
  1359   1390   
  1360   1391     /* If the table scan does not satisfy the ORDER BY clause, increase
  1361   1392     ** the cost by NlogN to cover the expense of sorting. */
  1362   1393     if( pOrderBy ){
  1363         -    if( sortableByRowid(iCur, pOrderBy, &rev) ){
         1394  +    if( sortableByRowid(iCur, pOrderBy, pWC->pMaskSet, &rev) ){
  1364   1395         flags |= WHERE_ORDERBY|WHERE_ROWID_RANGE;
  1365   1396         if( rev ){
  1366   1397           flags |= WHERE_REVERSE;
  1367   1398         }
  1368   1399       }else{
  1369   1400         cost += cost*estLog(cost);
  1370   1401         TRACE(("... sorting increases cost to %.9g\n", cost));
................................................................................
  1440   1471         }
  1441   1472       }
  1442   1473   
  1443   1474       /* Add the additional cost of sorting if that is a factor.
  1444   1475       */
  1445   1476       if( pOrderBy ){
  1446   1477         if( (flags & WHERE_COLUMN_IN)==0 &&
  1447         -           isSortingIndex(pParse,pProbe,iCur,pOrderBy,nEq,&rev) ){
         1478  +           isSortingIndex(pParse,pWC->pMaskSet,pProbe,iCur,pOrderBy,nEq,&rev) ){
  1448   1479           if( flags==0 ){
  1449   1480             flags = WHERE_COLUMN_RANGE;
  1450   1481           }
  1451   1482           flags |= WHERE_ORDERBY;
  1452   1483           if( rev ){
  1453   1484             flags |= WHERE_REVERSE;
  1454   1485           }
................................................................................
  1828   1859       return 0;
  1829   1860     }
  1830   1861   
  1831   1862     /* Split the WHERE clause into separate subexpressions where each
  1832   1863     ** subexpression is separated by an AND operator.
  1833   1864     */
  1834   1865     initMaskSet(&maskSet);
  1835         -  whereClauseInit(&wc, pParse);
         1866  +  whereClauseInit(&wc, pParse, &maskSet);
  1836   1867     whereSplit(&wc, pWhere, TK_AND);
  1837   1868       
  1838   1869     /* Allocate and initialize the WhereInfo structure that will become the
  1839   1870     ** return value.
  1840   1871     */
  1841   1872     pWInfo = sqliteMalloc( sizeof(WhereInfo) + pTabList->nSrc*sizeof(WhereLevel));
  1842   1873     if( sqlite3MallocFailed() ){
................................................................................
  1859   1890     ** add new virtual terms onto the end of the WHERE clause.  We do not
  1860   1891     ** want to analyze these virtual terms, so start analyzing at the end
  1861   1892     ** and work forward so that the added virtual terms are never processed.
  1862   1893     */
  1863   1894     for(i=0; i<pTabList->nSrc; i++){
  1864   1895       createMask(&maskSet, pTabList->a[i].iCursor);
  1865   1896     }
  1866         -  exprAnalyzeAll(pTabList, &maskSet, &wc);
         1897  +  exprAnalyzeAll(pTabList, &wc);
  1867   1898     if( sqlite3MallocFailed() ){
  1868   1899       goto whereBeginNoMem;
  1869   1900     }
  1870   1901   
  1871   1902     /* Chose the best index to use for each table in the FROM clause.
  1872   1903     **
  1873   1904     ** This loop fills in the following fields:

Changes to test/where.test.

     7      7   #    May you find forgiveness for yourself and forgive others.
     8      8   #    May you share freely, never taking more than you give.
     9      9   #
    10     10   #***********************************************************************
    11     11   # This file implements regression tests for SQLite library.  The
    12     12   # focus of this file is testing the use of indices in WHERE clases.
    13     13   #
    14         -# $Id: where.test,v 1.39 2006/12/20 03:24:19 drh Exp $
           14  +# $Id: where.test,v 1.40 2007/02/06 13:26:34 drh Exp $
    15     15   
    16     16   set testdir [file dirname $argv0]
    17     17   source $testdir/tester.tcl
    18     18   
    19     19   # Build some test data
    20     20   #
    21     21   do_test where-1.0 {
................................................................................
  1033   1033   } {4 four 1 one nosort}
  1034   1034   do_test where-13.12 {
  1035   1035     cksort {
  1036   1036       SELECT * FROM t7 ORDER BY a ASC, b DESC;
  1037   1037     }
  1038   1038   } {1 one 4 four nosort}
  1039   1039   
         1040  +# Ticket #2211.
         1041  +#
         1042  +# When optimizing out ORDER BY clauses, make sure that trailing terms
         1043  +# of the ORDER BY clause do not reference other tables in a join.
         1044  +#
         1045  +do_test where-14.1 {
         1046  +  execsql {
         1047  +    CREATE TABLE t8(a INTEGER PRIMARY KEY, b TEXT UNIQUE);
         1048  +    INSERT INTO t8 VALUES(1,'one');
         1049  +    INSERT INTO t8 VALUES(4,'four');
         1050  +  }
         1051  +  cksort {
         1052  +    SELECT x.a || '/' || y.a FROM t8 x, t8 y ORDER BY x.a, y.b
         1053  +  } 
         1054  +} {1/4 1/1 4/4 4/1 sort}
         1055  +do_test where-14.2 {
         1056  +  cksort {
         1057  +    SELECT x.a || '/' || y.a FROM t8 x, t8 y ORDER BY x.a, y.b DESC
         1058  +  } 
         1059  +} {1/1 1/4 4/1 4/4 sort}
         1060  +do_test where-14.3 {
         1061  +  cksort {
         1062  +    SELECT x.a || '/' || y.a FROM t8 x, t8 y ORDER BY x.a, x.b
         1063  +  } 
         1064  +} {1/1 1/4 4/1 4/4 nosort}
         1065  +do_test where-14.4 {
         1066  +  cksort {
         1067  +    SELECT x.a || '/' || y.a FROM t8 x, t8 y ORDER BY x.a, x.b DESC
         1068  +  } 
         1069  +} {1/1 1/4 4/1 4/4 nosort}
         1070  +btree_breakpoint
         1071  +do_test where-14.5 {
         1072  +  cksort {
         1073  +    SELECT x.a || '/' || y.a FROM t8 x, t8 y ORDER BY x.b, x.a||x.b
         1074  +  } 
         1075  +} {4/1 4/4 1/1 1/4 nosort}
         1076  +do_test where-14.6 {
         1077  +  cksort {
         1078  +    SELECT x.a || '/' || y.a FROM t8 x, t8 y ORDER BY x.b, x.a||x.b DESC
         1079  +  } 
         1080  +} {4/1 4/4 1/1 1/4 nosort}
         1081  +do_test where-14.7 {
         1082  +  cksort {
         1083  +    SELECT x.a || '/' || y.a FROM t8 x, t8 y ORDER BY x.b, y.a||y.b
         1084  +  } 
         1085  +} {4/1 4/4 1/1 1/4 sort}
         1086  +do_test where-14.8 {
         1087  +  cksort {
         1088  +    SELECT x.a || '/' || y.a FROM t8 x, t8 y ORDER BY x.b, y.a||y.b DESC
         1089  +  } 
         1090  +} {4/4 4/1 1/4 1/1 sort}
         1091  +do_test where-14.9 {
         1092  +  cksort {
         1093  +    SELECT x.a || '/' || y.a FROM t8 x, t8 y ORDER BY x.b, x.a||y.b
         1094  +  } 
         1095  +} {4/4 4/1 1/4 1/1 sort}
         1096  +do_test where-14.10 {
         1097  +  cksort {
         1098  +    SELECT x.a || '/' || y.a FROM t8 x, t8 y ORDER BY x.b, x.a||y.b DESC
         1099  +  } 
         1100  +} {4/1 4/4 1/1 1/4 sort}
         1101  +do_test where-14.11 {
         1102  +  cksort {
         1103  +    SELECT x.a || '/' || y.a FROM t8 x, t8 y ORDER BY x.b, y.a||x.b
         1104  +  } 
         1105  +} {4/1 4/4 1/1 1/4 sort}
         1106  +do_test where-14.12 {
         1107  +  cksort {
         1108  +    SELECT x.a || '/' || y.a FROM t8 x, t8 y ORDER BY x.b, y.a||x.b DESC
         1109  +  } 
         1110  +} {4/4 4/1 1/4 1/1 sort}
  1040   1111   
  1041   1112   
  1042   1113   integrity_check {where-99.0}
  1043   1114   
  1044   1115   finish_test