/ Check-in [ba897100]
Login

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

Overview
Comment:Improved processing of DISTINCT.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | nextgen-query-plan-exp
Files: files | file ages | folders
SHA1:ba897100fed291d2025f68d09334f9985312298b
User & Date: drh 2013-06-11 18:59:38
Context
2013-06-12
03:48
Continue refining the NGQP check-in: 40567fdd user: drh tags: nextgen-query-plan-exp
2013-06-11
18:59
Improved processing of DISTINCT. check-in: ba897100 user: drh tags: nextgen-query-plan-exp
13:30
Fix the Parse.nQueryLoop state variable to work with NGQP. check-in: f1cac24f user: drh tags: nextgen-query-plan-exp
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to ext/misc/fuzzer.c.

  1075   1075   static int fuzzerBestIndex(sqlite3_vtab *tab, sqlite3_index_info *pIdxInfo){
  1076   1076     int iPlan = 0;
  1077   1077     int iDistTerm = -1;
  1078   1078     int iRulesetTerm = -1;
  1079   1079     int i;
  1080   1080     int seenMatch = 0;
  1081   1081     const struct sqlite3_index_constraint *pConstraint;
  1082         -  double rCost = 100000;
         1082  +  double rCost = 1e12;
  1083   1083   
  1084   1084     pConstraint = pIdxInfo->aConstraint;
  1085   1085     for(i=0; i<pIdxInfo->nConstraint; i++, pConstraint++){
  1086   1086       if( pConstraint->iColumn==0
  1087   1087        && pConstraint->op==SQLITE_INDEX_CONSTRAINT_MATCH ){
  1088   1088         seenMatch = 1;
  1089   1089       }
................................................................................
  1091   1091       if( (iPlan & 1)==0 
  1092   1092        && pConstraint->iColumn==0
  1093   1093        && pConstraint->op==SQLITE_INDEX_CONSTRAINT_MATCH
  1094   1094       ){
  1095   1095         iPlan |= 1;
  1096   1096         pIdxInfo->aConstraintUsage[i].argvIndex = 1;
  1097   1097         pIdxInfo->aConstraintUsage[i].omit = 1;
  1098         -      rCost /= 1000000.0;
         1098  +      rCost /= 1e6;
  1099   1099       }
  1100   1100       if( (iPlan & 2)==0
  1101   1101        && pConstraint->iColumn==1
  1102   1102        && (pConstraint->op==SQLITE_INDEX_CONSTRAINT_LT
  1103   1103              || pConstraint->op==SQLITE_INDEX_CONSTRAINT_LE)
  1104   1104       ){
  1105   1105         iPlan |= 2;
................................................................................
  1128   1128     pIdxInfo->idxNum = iPlan;
  1129   1129     if( pIdxInfo->nOrderBy==1
  1130   1130      && pIdxInfo->aOrderBy[0].iColumn==1
  1131   1131      && pIdxInfo->aOrderBy[0].desc==0
  1132   1132     ){
  1133   1133       pIdxInfo->orderByConsumed = 1;
  1134   1134     }
  1135         -  if( seenMatch && (iPlan&1)==0 ) rCost *= 1e30;
         1135  +  if( seenMatch && (iPlan&1)==0 ) rCost = 1e99;
  1136   1136     pIdxInfo->estimatedCost = rCost;
  1137   1137      
  1138   1138     return SQLITE_OK;
  1139   1139   }
  1140   1140   
  1141   1141   /*
  1142   1142   ** A virtual table module that implements the "fuzzer".

Changes to src/sqliteInt.h.

  1963   1963   #define WHERE_ONEPASS_DESIRED  0x0004 /* Want to do one-pass UPDATE/DELETE */
  1964   1964   #define WHERE_DUPLICATES_OK    0x0008 /* Ok to return a row more than once */
  1965   1965   #define WHERE_OMIT_OPEN_CLOSE  0x0010 /* Table cursors are already open */
  1966   1966   #define WHERE_FORCE_TABLE      0x0020 /* Do not use an index-only search */
  1967   1967   #define WHERE_ONETABLE_ONLY    0x0040 /* Only code the 1st table in pTabList */
  1968   1968   #define WHERE_AND_ONLY         0x0080 /* Don't use indices for OR terms */
  1969   1969   #define WHERE_GROUPBY          0x0100 /* pOrderBy is really a GROUP BY */
         1970  +#define WHERE_DISTINCTBY       0x0200 /* pOrderby is really a DISTINCT clause */
  1970   1971   
  1971         -/* Allowed values for WhereInfo.eDistinct and DistinctCtx.eTnctType */
         1972  +/* Allowed return values from sqlite3WhereIsDistinct()
         1973  +*/
  1972   1974   #define WHERE_DISTINCT_NOOP      0  /* DISTINCT keyword not used */
  1973   1975   #define WHERE_DISTINCT_UNIQUE    1  /* No duplicates */
  1974   1976   #define WHERE_DISTINCT_ORDERED   2  /* All duplicates are adjacent */
  1975   1977   #define WHERE_DISTINCT_UNORDERED 3  /* Duplicates are scattered */
  1976   1978   
  1977   1979   /*
  1978   1980   ** A NameContext defines a context in which to resolve table and column
................................................................................
  2222   2224     TableLock *aTableLock; /* Required table locks for shared-cache mode */
  2223   2225   #endif
  2224   2226     AutoincInfo *pAinc;  /* Information about AUTOINCREMENT counters */
  2225   2227   
  2226   2228     /* Information used while coding trigger programs. */
  2227   2229     Parse *pToplevel;    /* Parse structure for main program (or NULL) */
  2228   2230     Table *pTriggerTab;  /* Table triggers are being coded for */
  2229         -  u32 grep nQueryLoop; /* Est number of iterations of a query (10*log2(N)) */
         2231  +  u32 nQueryLoop;      /* Est number of iterations of a query (10*log2(N)) */
  2230   2232     u32 oldmask;         /* Mask of old.* columns referenced */
  2231   2233     u32 newmask;         /* Mask of new.* columns referenced */
  2232   2234     u8 eTriggerOp;       /* TK_UPDATE, TK_INSERT or TK_DELETE */
  2233   2235     u8 eOrconf;          /* Default ON CONFLICT policy for trigger steps */
  2234   2236     u8 disableTriggers;  /* True to disable triggers */
  2235   2237   
  2236   2238     /* Above is constant between recursions.  Below is reset before and after

Changes to src/where.c.

   341    341   ** into the second half to give some continuity.
   342    342   */
   343    343   struct WhereInfo {
   344    344     Parse *pParse;            /* Parsing and code generating context */
   345    345     SrcList *pTabList;        /* List of tables in the join */
   346    346     ExprList *pOrderBy;       /* The ORDER BY clause or NULL */
   347    347     ExprList *pDistinct;      /* DISTINCT ON values, or NULL */
          348  +  WhereLoop *pLoops;        /* List of all WhereLoop objects */
   348    349     Bitmask revMask;          /* Mask of ORDER BY terms that need reversing */
   349         -  u16 nOBSat;               /* Number of ORDER BY terms satisfied by indices */
          350  +  WhereCost nRowOut;        /* Estimated number of output rows */
   350    351     u16 wctrlFlags;           /* Flags originally passed to sqlite3WhereBegin() */
          352  +  u8 bOBSat;                /* ORDER BY satisfied by indices */
   351    353     u8 okOnePass;             /* Ok to use one-pass algorithm for UPDATE/DELETE */
   352    354     u8 untestedTerms;         /* Not all WHERE terms resolved by outer loop */
   353    355     u8 eDistinct;             /* One of the WHERE_DISTINCT_* values below */
   354    356     int iTop;                 /* The very beginning of the WHERE loop */
   355    357     int iContinue;            /* Jump here to continue with next record */
   356    358     int iBreak;               /* Jump here to break out of the loop */
   357    359     int nLevel;               /* Number of nested loop */
          360  +  int savedNQueryLoop;      /* pParse->nQueryLoop outside the WHERE loop */
   358    361     WhereMaskSet sMaskSet;    /* Map cursor numbers to bitmasks */
   359    362     WhereClause sWC;          /* Decomposition of the WHERE clause */
   360         -  WhereLoop *pLoops;        /* List of all WhereLoop objects */
   361         -  int savedNQueryLoop;      /* pParse->nQueryLoop outside the WHERE loop */
   362         -  WhereCost nRowOut;        /* Estimated number of output rows */
   363    363     WhereLevel a[1];          /* Information about each nest loop in WHERE */
   364    364   };
   365    365   
   366    366   /*
   367    367   ** Bitmasks for the operators that indices are able to exploit.  An
   368    368   ** OR-ed combination of these values can be used when searching for
   369    369   ** terms in the where clause.
................................................................................
   437    437   }
   438    438   
   439    439   /*
   440    440   ** Return TRUE if the WHERE clause returns rows in ORDER BY order.
   441    441   ** Return FALSE if the output needs to be sorted.
   442    442   */
   443    443   int sqlite3WhereIsOrdered(WhereInfo *pWInfo){
   444         -  return pWInfo->nOBSat>0;
          444  +  return pWInfo->bOBSat!=0;
   445    445   }
   446    446   
   447    447   /*
   448    448   ** Return the VDBE address or label to jump to in order to continue
   449    449   ** immediately with the next row of a WHERE clause.
   450    450   */
   451    451   int sqlite3WhereContinueLabel(WhereInfo *pWInfo){
................................................................................
  1772   1772     }
  1773   1773   
  1774   1774     return -1;
  1775   1775   }
  1776   1776   
  1777   1777   /*
  1778   1778   ** Return true if the DISTINCT expression-list passed as the third argument
  1779         -** is redundant. A DISTINCT list is redundant if the database contains a
  1780         -** UNIQUE index that guarantees that the result of the query will be distinct
  1781         -** anyway.
         1779  +** is redundant.
         1780  +**
         1781  +** A DISTINCT list is redundant if the database contains some set of
         1782  +** columns that are unique and non-null.
  1782   1783   */
  1783   1784   static int isDistinctRedundant(
  1784         -  Parse *pParse,
  1785         -  SrcList *pTabList,
  1786         -  WhereClause *pWC,
  1787         -  ExprList *pDistinct
         1785  +  Parse *pParse,            /* Parsing context */
         1786  +  SrcList *pTabList,        /* The FROM clause */
         1787  +  WhereClause *pWC,         /* The WHERE clause */
         1788  +  ExprList *pDistinct       /* The result set that needs to be DISTINCT */
  1788   1789   ){
  1789   1790     Table *pTab;
  1790   1791     Index *pIdx;
  1791   1792     int i;                          
  1792   1793     int iBase;
  1793   1794   
  1794   1795     /* If there is more than one table or sub-select in the FROM clause of
................................................................................
  3414   3415       ** query, then the caller will only allow the loop to run for
  3415   3416       ** a single iteration. This means that the first row returned
  3416   3417       ** should not have a NULL value stored in 'x'. If column 'x' is
  3417   3418       ** the first one after the nEq equality constraints in the index,
  3418   3419       ** this requires some special handling.
  3419   3420       */
  3420   3421       if( (pWInfo->wctrlFlags&WHERE_ORDERBY_MIN)!=0
  3421         -     && (pWInfo->nOBSat>0)
         3422  +     && (pWInfo->bOBSat!=0)
  3422   3423        && (pIdx->nColumn>nEq)
  3423   3424       ){
  3424   3425         /* assert( pOrderBy->nExpr==1 ); */
  3425   3426         /* assert( pOrderBy->a[0].pExpr->iColumn==pIdx->aiColumn[nEq] ); */
  3426   3427         isMinQuery = 1;
  3427   3428         nExtraReg = 1;
  3428   3429       }
................................................................................
  4489   4490          && pProbe->bUnordered==0
  4490   4491          && (pWInfo->wctrlFlags & WHERE_ONEPASS_DESIRED)==0
  4491   4492          && sqlite3GlobalConfig.bUseCis
  4492   4493          && OptimizationEnabled(pWInfo->pParse->db, SQLITE_CoverIdxScan)
  4493   4494         ){
  4494   4495           pNew->iSortIdx = b ? iSortIdx : 0;
  4495   4496           pNew->nOut = rSize;
  4496         -        pNew->rRun = whereCostAdd(rSize,rLogSize) + ((m==0 && b) ? 10 : 0);
         4497  +        pNew->rRun = whereCostAdd(rSize,rLogSize);
         4498  +        if( m!=0 ) pNew->rRun += rLogSize;
         4499  +        if( b ) pNew->rRun--;
  4497   4500           rc = whereLoopInsert(pBuilder, pNew);
  4498   4501           if( rc ) break;
  4499   4502         }
  4500   4503       }
  4501   4504       rc = whereLoopAddBtreeIndex(pBuilder, pSrc, pProbe, 0);
  4502   4505   
  4503   4506       /* If there was an INDEXED BY clause, then only that one index is
................................................................................
  4800   4803   **    0:  ORDER BY is not satisfied.  Sorting required
  4801   4804   **    1:  ORDER BY is satisfied.      Omit sorting
  4802   4805   **   -1:  Unknown at this time
  4803   4806   **
  4804   4807   */
  4805   4808   static int wherePathSatisfiesOrderBy(
  4806   4809     WhereInfo *pWInfo,    /* The WHERE clause */
         4810  +  ExprList *pOrderBy,   /* ORDER BY or GROUP BY or DISTINCT clause to check */
  4807   4811     WherePath *pPath,     /* The WherePath to check */
  4808         -  int nLoop,            /* Number of entries in pPath->aLoop[] */
  4809         -  int isLastLoop,       /* True if pLast is the inner-most loop */
         4812  +  u16 wctrlFlags,       /* Might contain WHERE_GROUPBY or WHERE_DISTINCTBY */
         4813  +  u16 nLoop,            /* Number of entries in pPath->aLoop[] */
         4814  +  u8 isLastLoop,        /* True if pLast is the inner-most loop */
  4810   4815     WhereLoop *pLast,     /* Add this WhereLoop to the end of pPath->aLoop[] */
  4811         -  Bitmask *pRevMask     /* Mask of WhereLoops to run in reverse order */
         4816  +  Bitmask *pRevMask     /* OUT: Mask of WhereLoops to run in reverse order */
  4812   4817   ){
  4813   4818     u8 revSet;            /* True if rev is known */
  4814   4819     u8 rev;               /* Composite sort order */
  4815   4820     u8 revIdx;            /* Index sort order */
  4816   4821     u8 isOrderDistinct;   /* All prior WhereLoops are order-distinct */
  4817   4822     u8 distinctColumns;   /* True if the loop has UNIQUE NOT NULL columns */
  4818   4823     u8 isMatch;           /* iColumn matches a term of the ORDER BY clause */
................................................................................
  4819   4824     u16 nColumn;          /* Number of columns in pIndex */
  4820   4825     u16 nOrderBy;         /* Number terms in the ORDER BY clause */
  4821   4826     int iLoop;            /* Index of WhereLoop in pPath being processed */
  4822   4827     int i, j;             /* Loop counters */
  4823   4828     int iCur;             /* Cursor number for current WhereLoop */
  4824   4829     int iColumn;          /* A column number within table iCur */
  4825   4830     WhereLoop *pLoop;     /* Current WhereLoop being processed. */
  4826         -  ExprList *pOrderBy = pWInfo->pOrderBy;  /* the ORDER BY clause */
  4827   4831     WhereTerm *pTerm;     /* A single term of the WHERE clause */
  4828   4832     Expr *pOBExpr;        /* An expression from the ORDER BY clause */
  4829   4833     CollSeq *pColl;       /* COLLATE function from an ORDER BY clause term */
  4830   4834     Index *pIndex;        /* The index associated with pLoop */
  4831   4835     sqlite3 *db = pWInfo->pParse->db;  /* Database connection */
  4832   4836     Bitmask obSat = 0;    /* Mask of ORDER BY terms satisfied so far */
  4833   4837     Bitmask obDone;       /* Mask of all ORDER BY terms */
................................................................................
  4956   4960           ** of the index and and mark that ORDER BY term off 
  4957   4961           */
  4958   4962           bOnce = 1;
  4959   4963           isMatch = 0;
  4960   4964           for(i=0; bOnce && i<nOrderBy; i++){
  4961   4965             if( MASKBIT(i) & obSat ) continue;
  4962   4966             pOBExpr = sqlite3ExprSkipCollate(pOrderBy->a[i].pExpr);
  4963         -          if( (pWInfo->wctrlFlags & WHERE_GROUPBY)==0 ) bOnce = 0;
         4967  +          if( (wctrlFlags & (WHERE_GROUPBY|WHERE_DISTINCTBY))==0 ) bOnce = 0;
  4964   4968             if( pOBExpr->op!=TK_COLUMN ) continue;
  4965   4969             if( pOBExpr->iTable!=iCur ) continue;
  4966   4970             if( pOBExpr->iColumn!=iColumn ) continue;
  4967   4971             if( iColumn>=0 ){
  4968   4972               pColl = sqlite3ExprCollSeq(pWInfo->pParse, pOrderBy->a[i].pExpr);
  4969   4973               if( !pColl ) pColl = db->pDfltColl;
  4970   4974               if( sqlite3StrICmp(pColl->zName, pIndex->azColl[j])!=0 ) continue;
................................................................................
  5107   5111           if( (pWLoop->maskSelf & pFrom->maskLoop)!=0 ) continue;
  5108   5112           /* At this point, pWLoop is a candidate to be the next loop. 
  5109   5113           ** Compute its cost */
  5110   5114           rCost = whereCostAdd(pWLoop->rSetup,pWLoop->rRun + pFrom->nRow);
  5111   5115           rCost = whereCostAdd(rCost, pFrom->rCost);
  5112   5116           maskNew = pFrom->maskLoop | pWLoop->maskSelf;
  5113   5117           if( !isOrderedValid ){
  5114         -          switch( wherePathSatisfiesOrderBy(pWInfo, pFrom, iLoop, iLoop==nLoop-1,
  5115         -                                            pWLoop, &revMask) ){
         5118  +          switch( wherePathSatisfiesOrderBy(pWInfo,
         5119  +                       pWInfo->pOrderBy, pFrom, pWInfo->wctrlFlags,
         5120  +                       iLoop, iLoop==nLoop-1, pWLoop, &revMask) ){
  5116   5121               case 1:  /* Yes.  pFrom+pWLoop does satisfy the ORDER BY clause */
  5117   5122                 isOrdered = 1;
  5118   5123                 isOrderedValid = 1;
  5119   5124                 break;
  5120   5125               case 0:  /* No.  pFrom+pWLoop will require a separate sort */
  5121   5126                 isOrdered = 0;
  5122   5127                 isOrderedValid = 1;
................................................................................
  5245   5250     /* Load the lowest cost path into pWInfo */
  5246   5251     for(iLoop=0; iLoop<nLoop; iLoop++){
  5247   5252       WhereLevel *pLevel = pWInfo->a + iLoop;
  5248   5253       pLevel->pWLoop = pWLoop = pFrom->aLoop[iLoop];
  5249   5254       pLevel->iFrom = pWLoop->iTab; /* FIXME: Omit the iFrom field */
  5250   5255       pLevel->iTabCur = pWInfo->pTabList->a[pLevel->iFrom].iCursor;
  5251   5256     }
         5257  +  if( (pWInfo->wctrlFlags & WHERE_DISTINCTBY)==0 
         5258  +   && pWInfo->pDistinct
         5259  +   && nRowEst
         5260  +  ){
         5261  +    Bitmask notUsed;
         5262  +    int rc = wherePathSatisfiesOrderBy(pWInfo, pWInfo->pDistinct, pFrom,
         5263  +                 WHERE_DISTINCTBY, nLoop-1, 1, pFrom->aLoop[nLoop-1], &notUsed);
         5264  +    if( rc==1 ) pWInfo->eDistinct = WHERE_DISTINCT_ORDERED;
         5265  +  }
  5252   5266     if( pFrom->isOrdered ){
  5253         -    pWInfo->nOBSat = pWInfo->pOrderBy->nExpr;
  5254         -    pWInfo->revMask = pFrom->revLoop;
         5267  +    if( pWInfo->wctrlFlags & WHERE_DISTINCTBY ){
         5268  +      pWInfo->eDistinct = WHERE_DISTINCT_ORDERED;
         5269  +    }else{
         5270  +      pWInfo->bOBSat = 1;
         5271  +      pWInfo->revMask = pFrom->revLoop;
         5272  +    }
  5255   5273     }
  5256   5274     pWInfo->nRowOut = pFrom->nRow;
  5257   5275   
  5258   5276     /* Free temporary memory and return success */
  5259   5277     sqlite3DbFree(db, pSpace);
  5260   5278     return SQLITE_OK;
  5261   5279   }
................................................................................
  5323   5341     }
  5324   5342     if( pLoop->wsFlags ){
  5325   5343       pLoop->nOut = (WhereCost)1;
  5326   5344       pWInfo->a[0].pWLoop = pLoop;
  5327   5345       pLoop->maskSelf = getMask(&pWInfo->sMaskSet, iCur);
  5328   5346       pWInfo->a[0].iTabCur = iCur;
  5329   5347       pWInfo->nRowOut = 1;
  5330         -    pWInfo->nOBSat =  pWInfo->pOrderBy ? pWInfo->pOrderBy->nExpr : 0;
         5348  +    if( pWInfo->pOrderBy ) pWInfo->bOBSat =  1;
         5349  +    if( pWInfo->pDistinct ) pWInfo->eDistinct = WHERE_DISTINCT_UNIQUE;
  5331   5350   #ifdef SQLITE_DEBUG
  5332   5351       pLoop->cId = '0';
  5333   5352   #endif
  5334   5353       return 1;
  5335   5354     }
  5336   5355     return 0;
  5337   5356   }
................................................................................
  5410   5429   **    end
  5411   5430   **
  5412   5431   ** ORDER BY CLAUSE PROCESSING
  5413   5432   **
  5414   5433   ** pOrderBy is a pointer to the ORDER BY clause of a SELECT statement,
  5415   5434   ** if there is one.  If there is no ORDER BY clause or if this routine
  5416   5435   ** is called from an UPDATE or DELETE statement, then pOrderBy is NULL.
  5417         -**
  5418         -** If an index can be used so that the natural output order of the table
  5419         -** scan is correct for the ORDER BY clause, then that index is used and
  5420         -** the returned WhereInfo.nOBSat field is set to pOrderBy->nExpr.  This
  5421         -** is an optimization that prevents an unnecessary sort of the result set
  5422         -** if an index appropriate for the ORDER BY clause already exists.
  5423         -**
  5424         -** If the where clause loops cannot be arranged to provide the correct
  5425         -** output order, then WhereInfo.nOBSat is 0.
  5426   5436   */
  5427   5437   WhereInfo *sqlite3WhereBegin(
  5428   5438     Parse *pParse,        /* The parser context */
  5429   5439     SrcList *pTabList,    /* A list of all tables to be scanned */
  5430   5440     Expr *pWhere,         /* The WHERE clause */
  5431   5441     ExprList *pOrderBy,   /* An ORDER BY clause, or NULL */
  5432   5442     ExprList *pDistinct,  /* The select-list for DISTINCT queries - or NULL */
................................................................................
  5553   5563     ** want to analyze these virtual terms, so start analyzing at the end
  5554   5564     ** and work forward so that the added virtual terms are never processed.
  5555   5565     */
  5556   5566     exprAnalyzeAll(pTabList, &pWInfo->sWC);
  5557   5567     if( db->mallocFailed ){
  5558   5568       goto whereBeginError;
  5559   5569     }
         5570  +
         5571  +  /* If the ORDER BY (or GROUP BY) clause contains references to general
         5572  +  ** expressions, then we won't be able to satisfy it using indices, so
         5573  +  ** go ahead and disable it now.
         5574  +  */
         5575  +  if( pOrderBy ){
         5576  +    for(ii=0; ii<pOrderBy->nExpr; ii++){
         5577  +      Expr *pExpr = sqlite3ExprSkipCollate(pOrderBy->a[ii].pExpr);
         5578  +      if( pExpr->op!=TK_COLUMN ){
         5579  +        pWInfo->pOrderBy = pOrderBy = 0;
         5580  +        break;
         5581  +      }
         5582  +    }
         5583  +  }
  5560   5584   
  5561   5585     /* Check if the DISTINCT qualifier, if there is one, is redundant. 
  5562   5586     ** If it is, then set pDistinct to NULL and WhereInfo.eDistinct to
  5563   5587     ** WHERE_DISTINCT_UNIQUE to tell the caller to ignore the DISTINCT.
  5564   5588     */
  5565         -  if( pDistinct && isDistinctRedundant(pParse,pTabList,&pWInfo->sWC,pDistinct) ){
  5566         -    pDistinct = 0;
  5567         -    pWInfo->eDistinct = WHERE_DISTINCT_UNIQUE;
         5589  +  if( pDistinct ){
         5590  +    if( isDistinctRedundant(pParse,pTabList,&pWInfo->sWC,pDistinct) ){
         5591  +      pDistinct = 0;
         5592  +      pWInfo->eDistinct = WHERE_DISTINCT_UNIQUE;
         5593  +    }else if( pOrderBy==0 ){
         5594  +      pWInfo->wctrlFlags |= WHERE_DISTINCTBY;
         5595  +      pWInfo->pOrderBy = pDistinct;
         5596  +    }
  5568   5597     }
  5569   5598   
  5570   5599     /* Construct the WhereLoop objects */
  5571   5600     WHERETRACE(("*** Optimizer Start ***\n"));
  5572   5601     if( nTabList!=1 || whereShortCut(&sWLB)==0 ){
  5573   5602       rc = whereLoopAddAll(&sWLB);
  5574   5603       if( rc ) goto whereBeginError;
................................................................................
  5583   5612         for(p=pWInfo->pLoops; p; p=p->pNextLoop){
  5584   5613           p->cId = zLabel[(i++)%sizeof(zLabel)];
  5585   5614           whereLoopPrint(p, pTabList);
  5586   5615         }
  5587   5616       }
  5588   5617   #endif
  5589   5618     
  5590         -    wherePathSolver(pWInfo, -1);
         5619  +    wherePathSolver(pWInfo, 0);
  5591   5620       if( db->mallocFailed ) goto whereBeginError;
  5592   5621       if( pWInfo->pOrderBy ){
  5593   5622          wherePathSolver(pWInfo, pWInfo->nRowOut);
  5594   5623          if( db->mallocFailed ) goto whereBeginError;
  5595   5624       }
  5596   5625     }
  5597   5626     if( pWInfo->pOrderBy==0 && (db->flags & SQLITE_ReverseOrder)!=0 ){
................................................................................
  5599   5628     }
  5600   5629     if( pParse->nErr || db->mallocFailed ){
  5601   5630       goto whereBeginError;
  5602   5631     }
  5603   5632   #ifdef WHERETRACE_ENABLED
  5604   5633     if( sqlite3WhereTrace ){
  5605   5634       int ii;
  5606         -    sqlite3DebugPrintf("---- Solution");
  5607         -    if( pWInfo->nOBSat ){
  5608         -      sqlite3DebugPrintf(" ORDER BY omitted rev=0x%llx\n", pWInfo->revMask);
  5609         -    }else{
  5610         -      sqlite3DebugPrintf("\n");
         5635  +    sqlite3DebugPrintf("---- Solution nRow=%d", pWInfo->nRowOut);
         5636  +    if( pWInfo->bOBSat ){
         5637  +      sqlite3DebugPrintf(" ORDERBY=0x%llx", pWInfo->revMask);
         5638  +    }
         5639  +    switch( pWInfo->eDistinct ){
         5640  +      case WHERE_DISTINCT_UNIQUE: {
         5641  +        sqlite3DebugPrintf("  DISTINCT=unique");
         5642  +        break;
         5643  +      }
         5644  +      case WHERE_DISTINCT_ORDERED: {
         5645  +        sqlite3DebugPrintf("  DISTINCT=ordered");
         5646  +        break;
         5647  +      }
         5648  +      case WHERE_DISTINCT_UNORDERED: {
         5649  +        sqlite3DebugPrintf("  DISTINCT=unordered");
         5650  +        break;
         5651  +      }
  5611   5652       }
         5653  +    sqlite3DebugPrintf("\n");
  5612   5654       for(ii=0; ii<nTabList; ii++){
  5613   5655         whereLoopPrint(pWInfo->a[ii].pWLoop, pTabList);
  5614   5656       }
  5615   5657     }
  5616   5658   #endif
  5617   5659     WHERETRACE(("*** Optimizer Finished ***\n"));
  5618   5660     pWInfo->pParse->nQueryLoop += pWInfo->nRowOut;