/ Check-in [e2c9f71a]
Login

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

Overview
Comment:Merge all recent trunk changes into the threads branch.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | threads
Files: files | file ages | folders
SHA1: e2c9f71a451e44040624b9f255b4510743513019
User & Date: drh 2014-04-23 12:57:55
Context
2014-04-24
12:28
Improvements to comments. Store some extra information in SqliteThread that is useful for debugging. check-in: 9fb5e212 user: drh tags: threads
2014-04-23
12:57
Merge all recent trunk changes into the threads branch. check-in: e2c9f71a user: drh tags: threads
2014-04-21
13:36
Comment tweaks on the test case for the [b75a9ca6b0] bug fix. check-in: 65d2544a user: drh tags: trunk
2014-04-18
13:57
Add to speedtest1.c the --threads option for setting the SQLITE_CONFIG_WORKER_THREADS configuration. check-in: 5fce40c4 user: drh tags: threads
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/select.c.

  4747   4747     if( p->pPrior ){
  4748   4748       rc = multiSelect(pParse, p, pDest);
  4749   4749       explainSetInteger(pParse->iSelectId, iRestoreSelectId);
  4750   4750       return rc;
  4751   4751     }
  4752   4752   #endif
  4753   4753   
  4754         -  /* If there is both a GROUP BY and an ORDER BY clause and they are
  4755         -  ** identical, then disable the ORDER BY clause since the GROUP BY
  4756         -  ** will cause elements to come out in the correct order.  This is
  4757         -  ** an optimization - the correct answer should result regardless.
  4758         -  ** Use the SQLITE_GroupByOrder flag with SQLITE_TESTCTRL_OPTIMIZER
  4759         -  ** to disable this optimization for testing purposes.
  4760         -  */
  4761         -  if( sqlite3ExprListCompare(p->pGroupBy, sSort.pOrderBy, -1)==0
  4762         -         && OptimizationEnabled(db, SQLITE_GroupByOrder) ){
  4763         -    sSort.pOrderBy = 0;
  4764         -  }
  4765         -
  4766   4754     /* If the query is DISTINCT with an ORDER BY but is not an aggregate, and 
  4767   4755     ** if the select-list is the same as the ORDER BY list, then this query
  4768   4756     ** can be rewritten as a GROUP BY. In other words, this:
  4769   4757     **
  4770   4758     **     SELECT DISTINCT xyz FROM ... ORDER BY xyz
  4771   4759     **
  4772   4760     ** is transformed to:
................................................................................
  4888   4876                           ** one row of the input to the aggregator has been
  4889   4877                           ** processed */
  4890   4878       int iAbortFlag;     /* Mem address which causes query abort if positive */
  4891   4879       int groupBySort;    /* Rows come from source in GROUP BY order */
  4892   4880       int addrEnd;        /* End of processing for this SELECT */
  4893   4881       int sortPTab = 0;   /* Pseudotable used to decode sorting results */
  4894   4882       int sortOut = 0;    /* Output register from the sorter */
         4883  +    int orderByGrp = 0; /* True if the GROUP BY and ORDER BY are the same */
  4895   4884   
  4896   4885       /* Remove any and all aliases between the result set and the
  4897   4886       ** GROUP BY clause.
  4898   4887       */
  4899   4888       if( pGroupBy ){
  4900   4889         int k;                        /* Loop counter */
  4901   4890         struct ExprList_item *pItem;  /* For looping over expression in a list */
................................................................................
  4907   4896           pItem->u.x.iAlias = 0;
  4908   4897         }
  4909   4898         if( p->nSelectRow>100 ) p->nSelectRow = 100;
  4910   4899       }else{
  4911   4900         p->nSelectRow = 1;
  4912   4901       }
  4913   4902   
         4903  +
         4904  +    /* If there is both a GROUP BY and an ORDER BY clause and they are
         4905  +    ** identical, then it may be possible to disable the ORDER BY clause 
         4906  +    ** on the grounds that the GROUP BY will cause elements to come out 
         4907  +    ** in the correct order. It also may not - the GROUP BY may use a
         4908  +    ** database index that causes rows to be grouped together as required
         4909  +    ** but not actually sorted. Either way, record the fact that the
         4910  +    ** ORDER BY and GROUP BY clauses are the same by setting the orderByGrp
         4911  +    ** variable.  */
         4912  +    if( sqlite3ExprListCompare(pGroupBy, sSort.pOrderBy, -1)==0 ){
         4913  +      orderByGrp = 1;
         4914  +    }
  4914   4915    
  4915   4916       /* Create a label to jump to when we want to abort the query */
  4916   4917       addrEnd = sqlite3VdbeMakeLabel(v);
  4917   4918   
  4918   4919       /* Convert TK_COLUMN nodes into TK_AGG_COLUMN and make entries in
  4919   4920       ** sAggInfo for all TK_AGG_FUNCTION nodes in expressions of the
  4920   4921       ** SELECT statement.
................................................................................
  4987   4988         /* Begin a loop that will extract all source rows in GROUP BY order.
  4988   4989         ** This might involve two separate loops with an OP_Sort in between, or
  4989   4990         ** it might be a single loop that uses an index to extract information
  4990   4991         ** in the right order to begin with.
  4991   4992         */
  4992   4993         sqlite3VdbeAddOp2(v, OP_Gosub, regReset, addrReset);
  4993   4994         pWInfo = sqlite3WhereBegin(pParse, pTabList, pWhere, pGroupBy, 0,
  4994         -                                 WHERE_GROUPBY, 0);
         4995  +          WHERE_GROUPBY | (orderByGrp ? WHERE_SORTBYGROUP : 0), 0
         4996  +      );
  4995   4997         if( pWInfo==0 ) goto select_end;
  4996   4998         if( sqlite3WhereIsOrdered(pWInfo)==pGroupBy->nExpr ){
  4997   4999           /* The optimizer is able to deliver rows in group by order so
  4998   5000           ** we do not have to sort.  The OP_OpenEphemeral table will be
  4999   5001           ** cancelled later because we still need to use the pKeyInfo
  5000   5002           */
  5001   5003           groupBySort = 0;
................................................................................
  5051   5053           sAggInfo.sortingIdxPTab = sortPTab = pParse->nTab++;
  5052   5054           sortOut = sqlite3GetTempReg(pParse);
  5053   5055           sqlite3VdbeAddOp3(v, OP_OpenPseudo, sortPTab, sortOut, nCol);
  5054   5056           sqlite3VdbeAddOp2(v, OP_SorterSort, sAggInfo.sortingIdx, addrEnd);
  5055   5057           VdbeComment((v, "GROUP BY sort")); VdbeCoverage(v);
  5056   5058           sAggInfo.useSortingIdx = 1;
  5057   5059           sqlite3ExprCacheClear(pParse);
         5060  +
         5061  +      }
         5062  +
         5063  +      /* If the index or temporary table used by the GROUP BY sort
         5064  +      ** will naturally deliver rows in the order required by the ORDER BY
         5065  +      ** clause, cancel the ephemeral table open coded earlier.
         5066  +      **
         5067  +      ** This is an optimization - the correct answer should result regardless.
         5068  +      ** Use the SQLITE_GroupByOrder flag with SQLITE_TESTCTRL_OPTIMIZER to 
         5069  +      ** disable this optimization for testing purposes.  */
         5070  +      if( orderByGrp && OptimizationEnabled(db, SQLITE_GroupByOrder) 
         5071  +       && (groupBySort || sqlite3WhereIsSorted(pWInfo))
         5072  +      ){
         5073  +        sSort.pOrderBy = 0;
         5074  +        sqlite3VdbeChangeToNoop(v, sSort.addrSortIndex);
  5058   5075         }
  5059   5076   
  5060   5077         /* Evaluate the current GROUP BY terms and store in b0, b1, b2...
  5061   5078         ** (b0 is memory location iBMem+0, b1 is iBMem+1, and so forth)
  5062   5079         ** Then compare the current GROUP BY terms against the GROUP BY terms
  5063   5080         ** from the previous row currently stored in a0, a1, a2...
  5064   5081         */

Changes to src/sqliteInt.h.

  2136   2136   #define WHERE_OMIT_OPEN_CLOSE  0x0010 /* Table cursors are already open */
  2137   2137   #define WHERE_FORCE_TABLE      0x0020 /* Do not use an index-only search */
  2138   2138   #define WHERE_ONETABLE_ONLY    0x0040 /* Only code the 1st table in pTabList */
  2139   2139   #define WHERE_AND_ONLY         0x0080 /* Don't use indices for OR terms */
  2140   2140   #define WHERE_GROUPBY          0x0100 /* pOrderBy is really a GROUP BY */
  2141   2141   #define WHERE_DISTINCTBY       0x0200 /* pOrderby is really a DISTINCT clause */
  2142   2142   #define WHERE_WANT_DISTINCT    0x0400 /* All output needs to be distinct */
         2143  +#define WHERE_SORTBYGROUP      0x0800 /* Support sqlite3WhereIsSorted() */
  2143   2144   
  2144   2145   /* Allowed return values from sqlite3WhereIsDistinct()
  2145   2146   */
  2146   2147   #define WHERE_DISTINCT_NOOP      0  /* DISTINCT keyword not used */
  2147   2148   #define WHERE_DISTINCT_UNIQUE    1  /* No duplicates */
  2148   2149   #define WHERE_DISTINCT_ORDERED   2  /* All duplicates are adjacent */
  2149   2150   #define WHERE_DISTINCT_UNORDERED 3  /* Duplicates are scattered */
................................................................................
  3104   3105   void sqlite3DeleteFrom(Parse*, SrcList*, Expr*);
  3105   3106   void sqlite3Update(Parse*, SrcList*, ExprList*, Expr*, int);
  3106   3107   WhereInfo *sqlite3WhereBegin(Parse*,SrcList*,Expr*,ExprList*,ExprList*,u16,int);
  3107   3108   void sqlite3WhereEnd(WhereInfo*);
  3108   3109   u64 sqlite3WhereOutputRowCount(WhereInfo*);
  3109   3110   int sqlite3WhereIsDistinct(WhereInfo*);
  3110   3111   int sqlite3WhereIsOrdered(WhereInfo*);
         3112  +int sqlite3WhereIsSorted(WhereInfo*);
  3111   3113   int sqlite3WhereContinueLabel(WhereInfo*);
  3112   3114   int sqlite3WhereBreakLabel(WhereInfo*);
  3113   3115   int sqlite3WhereOkOnePass(WhereInfo*, int*);
  3114   3116   int sqlite3ExprCodeGetColumn(Parse*, Table*, int, int, int, u8);
  3115   3117   void sqlite3ExprCodeGetColumnOfTable(Vdbe*, Table*, int, int, int);
  3116   3118   void sqlite3ExprCodeMove(Parse*, int, int, int);
  3117   3119   void sqlite3ExprCacheStore(Parse*, int, int, int);

Changes to src/where.c.

  3708   3708         whereLoopDelete(db, p);
  3709   3709       }
  3710   3710       sqlite3DbFree(db, pWInfo);
  3711   3711     }
  3712   3712   }
  3713   3713   
  3714   3714   /*
  3715         -** Return TRUE if the set of WHERE clause terms used by pA is a proper
  3716         -** subset of the WHERE clause terms used by pB.
         3715  +** Return TRUE if both of the following are true:
         3716  +**
         3717  +**   (1)  X has the same or lower cost that Y
         3718  +**   (2)  X is a proper subset of Y
         3719  +**
         3720  +** By "proper subset" we mean that X uses fewer WHERE clause terms
         3721  +** than Y and that every WHERE clause term used by X is also used
         3722  +** by Y.
         3723  +**
         3724  +** If X is a proper subset of Y then Y is a better choice and ought
         3725  +** to have a lower cost.  This routine returns TRUE when that cost 
         3726  +** relationship is inverted and needs to be adjusted.
  3717   3727   */
  3718         -static int whereLoopProperSubset(const WhereLoop *pA, const WhereLoop *pB){
         3728  +static int whereLoopCheaperProperSubset(
         3729  +  const WhereLoop *pX,       /* First WhereLoop to compare */
         3730  +  const WhereLoop *pY        /* Compare against this WhereLoop */
         3731  +){
  3719   3732     int i, j;
  3720         -  assert( pA->nLTerm<pB->nLTerm );  /* Checked by calling function */
  3721         -  for(j=0, i=pA->nLTerm-1; i>=0 && j>=0; i--){
  3722         -    for(j=pB->nLTerm-1; j>=0; j--){
  3723         -      if( pB->aLTerm[j]==pA->aLTerm[i] ) break;
         3733  +  if( pX->nLTerm >= pY->nLTerm ) return 0; /* X is not a subset of Y */
         3734  +  if( pX->rRun >= pY->rRun ){
         3735  +    if( pX->rRun > pY->rRun ) return 0;    /* X costs more than Y */
         3736  +    if( pX->nOut > pY->nOut ) return 0;    /* X costs more than Y */
         3737  +  }
         3738  +  for(j=0, i=pX->nLTerm-1; i>=0; i--){
         3739  +    for(j=pY->nLTerm-1; j>=0; j--){
         3740  +      if( pY->aLTerm[j]==pX->aLTerm[i] ) break;
  3724   3741       }
         3742  +    if( j<0 ) return 0;  /* X not a subset of Y since term X[i] not used by Y */
  3725   3743     }
  3726         -  return j>=0;
         3744  +  return 1;  /* All conditions meet */
  3727   3745   }
  3728   3746   
  3729   3747   /*
  3730   3748   ** Try to adjust the cost of WhereLoop pTemplate upwards or downwards so
  3731   3749   ** that:
  3732   3750   **
  3733   3751   **   (1) pTemplate costs less than any other WhereLoops that are a proper
................................................................................
  3741   3759   ** also used by Y.
  3742   3760   */
  3743   3761   static void whereLoopAdjustCost(const WhereLoop *p, WhereLoop *pTemplate){
  3744   3762     if( (pTemplate->wsFlags & WHERE_INDEXED)==0 ) return;
  3745   3763     for(; p; p=p->pNextLoop){
  3746   3764       if( p->iTab!=pTemplate->iTab ) continue;
  3747   3765       if( (p->wsFlags & WHERE_INDEXED)==0 ) continue;
  3748         -    if( p->nLTerm<pTemplate->nLTerm
  3749         -     && (p->rRun<pTemplate->rRun || (p->rRun==pTemplate->rRun &&
  3750         -                                     p->nOut<=pTemplate->nOut))
  3751         -     && whereLoopProperSubset(p, pTemplate)
  3752         -    ){
         3766  +    if( whereLoopCheaperProperSubset(p, pTemplate) ){
         3767  +      /* Adjust pTemplate cost downward so that it is cheaper than its 
         3768  +      ** subset p */
  3753   3769         pTemplate->rRun = p->rRun;
  3754   3770         pTemplate->nOut = p->nOut - 1;
  3755         -    }else
  3756         -    if( p->nLTerm>pTemplate->nLTerm
  3757         -     && (p->rRun>pTemplate->rRun || (p->rRun==pTemplate->rRun &&
  3758         -                                     p->nOut>=pTemplate->nOut))
  3759         -     && whereLoopProperSubset(pTemplate, p)
  3760         -    ){
         3771  +    }else if( whereLoopCheaperProperSubset(pTemplate, p) ){
         3772  +      /* Adjust pTemplate cost upward so that it is costlier than p since
         3773  +      ** pTemplate is a proper subset of p */
  3761   3774         pTemplate->rRun = p->rRun;
  3762   3775         pTemplate->nOut = p->nOut + 1;
  3763   3776       }
  3764   3777     }
  3765   3778   }
  3766   3779   
  3767   3780   /*
................................................................................
  4779   4792   **   N>0:   N terms of the ORDER BY clause are satisfied
  4780   4793   **   N==0:  No terms of the ORDER BY clause are satisfied
  4781   4794   **   N<0:   Unknown yet how many terms of ORDER BY might be satisfied.   
  4782   4795   **
  4783   4796   ** Note that processing for WHERE_GROUPBY and WHERE_DISTINCTBY is not as
  4784   4797   ** strict.  With GROUP BY and DISTINCT the only requirement is that
  4785   4798   ** equivalent rows appear immediately adjacent to one another.  GROUP BY
  4786         -** and DISTINT do not require rows to appear in any particular order as long
         4799  +** and DISTINCT do not require rows to appear in any particular order as long
  4787   4800   ** as equivelent rows are grouped together.  Thus for GROUP BY and DISTINCT
  4788   4801   ** the pOrderBy terms can be matched in any order.  With ORDER BY, the 
  4789   4802   ** pOrderBy terms must be matched in strict left-to-right order.
  4790   4803   */
  4791   4804   static i8 wherePathSatisfiesOrderBy(
  4792   4805     WhereInfo *pWInfo,    /* The WHERE clause */
  4793   4806     ExprList *pOrderBy,   /* ORDER BY or GROUP BY or DISTINCT clause to check */
................................................................................
  4948   4961            && j>=pLoop->u.btree.nEq
  4949   4962            && pIndex->pTable->aCol[iColumn].notNull==0
  4950   4963           ){
  4951   4964             isOrderDistinct = 0;
  4952   4965           }
  4953   4966   
  4954   4967           /* Find the ORDER BY term that corresponds to the j-th column
  4955         -        ** of the index and and mark that ORDER BY term off 
         4968  +        ** of the index and mark that ORDER BY term off 
  4956   4969           */
  4957   4970           bOnce = 1;
  4958   4971           isMatch = 0;
  4959   4972           for(i=0; bOnce && i<nOrderBy; i++){
  4960   4973             if( MASKBIT(i) & obSat ) continue;
  4961   4974             pOBExpr = sqlite3ExprSkipCollate(pOrderBy->a[i].pExpr);
  4962   4975             testcase( wctrlFlags & WHERE_GROUPBY );
................................................................................
  5028   5041         if( (obSat&m)==m ) return i;
  5029   5042       }
  5030   5043       return 0;
  5031   5044     }
  5032   5045     return -1;
  5033   5046   }
  5034   5047   
         5048  +
         5049  +/*
         5050  +** If the WHERE_GROUPBY flag is set in the mask passed to sqlite3WhereBegin(),
         5051  +** the planner assumes that the specified pOrderBy list is actually a GROUP
         5052  +** BY clause - and so any order that groups rows as required satisfies the
         5053  +** request.
         5054  +**
         5055  +** Normally, in this case it is not possible for the caller to determine
         5056  +** whether or not the rows are really being delivered in sorted order, or
         5057  +** just in some other order that provides the required grouping. However,
         5058  +** if the WHERE_SORTBYGROUP flag is also passed to sqlite3WhereBegin(), then
         5059  +** this function may be called on the returned WhereInfo object. It returns
         5060  +** true if the rows really will be sorted in the specified order, or false
         5061  +** otherwise.
         5062  +**
         5063  +** For example, assuming:
         5064  +**
         5065  +**   CREATE INDEX i1 ON t1(x, Y);
         5066  +**
         5067  +** then
         5068  +**
         5069  +**   SELECT * FROM t1 GROUP BY x,y ORDER BY x,y;   -- IsSorted()==1
         5070  +**   SELECT * FROM t1 GROUP BY y,x ORDER BY y,x;   -- IsSorted()==0
         5071  +*/
         5072  +int sqlite3WhereIsSorted(WhereInfo *pWInfo){
         5073  +  assert( pWInfo->wctrlFlags & WHERE_GROUPBY );
         5074  +  assert( pWInfo->wctrlFlags & WHERE_SORTBYGROUP );
         5075  +  return pWInfo->sorted;
         5076  +}
         5077  +
  5035   5078   #ifdef WHERETRACE_ENABLED
  5036   5079   /* For debugging use only: */
  5037   5080   static const char *wherePathName(WherePath *pPath, int nLoop, WhereLoop *pLast){
  5038   5081     static char zName[65];
  5039   5082     int i;
  5040   5083     for(i=0; i<nLoop; i++){ zName[i] = pPath->aLoop[i]->cId; }
  5041   5084     if( pLast ) zName[i++] = pLast->cId;
  5042   5085     zName[i] = 0;
  5043   5086     return zName;
  5044   5087   }
  5045   5088   #endif
  5046         -
  5047   5089   
  5048   5090   /*
  5049   5091   ** Given the list of WhereLoop objects at pWInfo->pLoops, this routine
  5050   5092   ** attempts to find the lowest cost path that visits each WhereLoop
  5051   5093   ** once.  This path is then loaded into the pWInfo->a[].pWLoop fields.
  5052   5094   **
  5053   5095   ** Assume that the total number of output rows that will need to be sorted
................................................................................
  5321   5363           pWInfo->eDistinct = WHERE_DISTINCT_ORDERED;
  5322   5364         }
  5323   5365       }else{
  5324   5366         pWInfo->nOBSat = pFrom->isOrdered;
  5325   5367         if( pWInfo->nOBSat<0 ) pWInfo->nOBSat = 0;
  5326   5368         pWInfo->revMask = pFrom->revLoop;
  5327   5369       }
         5370  +    if( (pWInfo->wctrlFlags & WHERE_SORTBYGROUP)
         5371  +        && pWInfo->nOBSat==pWInfo->pOrderBy->nExpr
         5372  +    ){
         5373  +      Bitmask notUsed = 0;
         5374  +      int nOrder = wherePathSatisfiesOrderBy(pWInfo, pWInfo->pOrderBy, 
         5375  +          pFrom, 0, nLoop-1, pFrom->aLoop[nLoop-1], &notUsed
         5376  +      );
         5377  +      assert( pWInfo->sorted==0 );
         5378  +      pWInfo->sorted = (nOrder==pWInfo->pOrderBy->nExpr);
         5379  +    }
  5328   5380     }
         5381  +
         5382  +
  5329   5383     pWInfo->nRowOut = pFrom->nRow;
  5330   5384   
  5331   5385     /* Free temporary memory and return success */
  5332   5386     sqlite3DbFree(db, pSpace);
  5333   5387     return SQLITE_OK;
  5334   5388   }
  5335   5389   

Changes to src/whereInt.h.

   394    394     ExprList *pOrderBy;       /* The ORDER BY clause or NULL */
   395    395     ExprList *pResultSet;     /* Result set. DISTINCT operates on these */
   396    396     WhereLoop *pLoops;        /* List of all WhereLoop objects */
   397    397     Bitmask revMask;          /* Mask of ORDER BY terms that need reversing */
   398    398     LogEst nRowOut;           /* Estimated number of output rows */
   399    399     u16 wctrlFlags;           /* Flags originally passed to sqlite3WhereBegin() */
   400    400     i8 nOBSat;                /* Number of ORDER BY terms satisfied by indices */
          401  +  u8 sorted;                /* True if really sorted (not just grouped) */
   401    402     u8 okOnePass;             /* Ok to use one-pass algorithm for UPDATE/DELETE */
   402    403     u8 untestedTerms;         /* Not all WHERE terms resolved by outer loop */
   403    404     u8 eDistinct;             /* One of the WHERE_DISTINCT_* values below */
   404    405     u8 nLevel;                /* Number of nested loop */
   405    406     int iTop;                 /* The very beginning of the WHERE loop */
   406    407     int iContinue;            /* Jump here to continue with next record */
   407    408     int iBreak;               /* Jump here to break out of the loop */

Added test/tkt-b75a9ca6b0.test.

            1  +# 2014-04-21
            2  +#
            3  +# The author disclaims copyright to this source code.  In place of
            4  +# a legal notice, here is a blessing:
            5  +#
            6  +#    May you do good and not evil.
            7  +#    May you find forgiveness for yourself and forgive others.
            8  +#    May you share freely, never taking more than you give.
            9  +#
           10  +#*************************************************************************
           11  +#
           12  +# Test that ticket [b75a9ca6b0] has been fixed.
           13  +#
           14  +# Ticket [b75a9ca6b0] concerns queries that have both a GROUP BY
           15  +# and an ORDER BY.  This code verifies that SQLite is able to
           16  +# optimize out the ORDER BY in some circumstances, but retains the
           17  +# ORDER BY when necessary.
           18  +#
           19  +
           20  +set testdir [file dirname $argv0]
           21  +source $testdir/tester.tcl
           22  +set testprefix tkt-b75a9ca6b0
           23  +
           24  +do_execsql_test 1 {
           25  +  CREATE TABLE t1 (x, y);
           26  +  INSERT INTO t1 VALUES (1, 3); 
           27  +  INSERT INTO t1 VALUES (2, 2);
           28  +  INSERT INTO t1 VALUES (3, 1);
           29  +}
           30  +
           31  +do_execsql_test 1.1 {
           32  +  CREATE INDEX i1 ON t1(x, y);
           33  +} 
           34  +
           35  +set idxscan {0 0 0 {SCAN TABLE t1 USING COVERING INDEX i1}}
           36  +set tblscan {0 0 0 {SCAN TABLE t1}}
           37  +set grpsort {0 0 0 {USE TEMP B-TREE FOR GROUP BY}}
           38  +set sort    {0 0 0 {USE TEMP B-TREE FOR ORDER BY}}
           39  +
           40  +foreach {tn q res eqp} [subst -nocommands {
           41  +  1 "SELECT * FROM t1 GROUP BY x, y ORDER BY x,y"
           42  +  {1 3  2 2  3 1} {$idxscan}
           43  +
           44  +  2 "SELECT * FROM t1 GROUP BY x, y ORDER BY x"
           45  +  {1 3  2 2  3 1} {$idxscan $sort}
           46  +
           47  +  3 "SELECT * FROM t1 GROUP BY y, x ORDER BY y, x"
           48  +  {3 1  2 2  1 3} {$idxscan $sort}
           49  +  
           50  +  4 "SELECT * FROM t1 GROUP BY x ORDER BY x"
           51  +  {1 3  2 2  3 1} {$idxscan}
           52  +
           53  +  5 "SELECT * FROM t1 GROUP BY y ORDER BY y"
           54  +  {3 1  2 2  1 3} {$tblscan $grpsort}
           55  +
           56  +  6 "SELECT * FROM t1 GROUP BY y ORDER BY x"
           57  +  {1 3  2 2  3 1} {$tblscan $grpsort $sort}
           58  +
           59  +  7 "SELECT * FROM t1 GROUP BY x, y ORDER BY x, y DESC"
           60  +  {1 3  2 2  3 1} {$idxscan $sort}
           61  +
           62  +  8 "SELECT * FROM t1 GROUP BY x, y ORDER BY x DESC, y DESC"
           63  +  {3 1  2 2  1 3} {$idxscan $sort}
           64  +
           65  +  9 "SELECT * FROM t1 GROUP BY x, y ORDER BY x ASC, y ASC"
           66  +  {1 3  2 2  3 1} {$idxscan}
           67  +
           68  +  10 "SELECT * FROM t1 GROUP BY x, y ORDER BY x COLLATE nocase, y"
           69  +  {1 3  2 2  3 1} {$idxscan $sort}
           70  +
           71  +}] {
           72  +  do_execsql_test 1.$tn.1 $q $res
           73  +  do_eqp_test     1.$tn.2 $q $eqp
           74  +}
           75  +
           76  +
           77  +finish_test