SQLite

    }else{
      Expr *pColExpr = p;  /* The expression that is the result column name */
      Table *pTab;         /* Table associated with this expression */
      while( pColExpr->op==TK_DOT ){
        pColExpr = pColExpr->pRight;
        assert( pColExpr!=0 );
      }
      if( pColExpr->op==TK_COLUMN && pColExpr->pTab!=0 ){
        /* For columns use the column name name */
        int iCol = pColExpr->iColumn;
        pTab = pColExpr->pTab;
        if( iCol<0 ) iCol = pTab->iPKey;
        zName = iCol>=0 ? pTab->aCol[iCol].zName : "rowid";
      }else if( pColExpr->op==TK_ID ){
        assert( !ExprHasProperty(pColExpr, EP_IntValue) );

#define SQLITE_DistinctOpt    0x0020   /* DISTINCT using indexes */
#define SQLITE_CoverIdxScan   0x0040   /* Covering index scans */
#define SQLITE_OrderByIdxJoin 0x0080   /* ORDER BY of joins via index */
#define SQLITE_SubqCoroutine  0x0100   /* Evaluate subqueries as coroutines */
#define SQLITE_Transitive     0x0200   /* Transitive constraints */
#define SQLITE_OmitNoopJoin   0x0400   /* Omit unused tables in joins */
#define SQLITE_Stat34         0x0800   /* Use STAT3 or STAT4 data */
#define SQLITE_SkipAhead      0x1000   /* Skip ahead on DISTINCT */
#define SQLITE_CursorHints    0x2000   /* Add OP_CursorHint opcodes */
#define SQLITE_AllOpts        0xffff   /* All optimizations */

/*
** Macros for testing whether or not optimizations are enabled or disabled.
*/
#define OptimizationDisabled(db, mask)  (((db)->dbOptFlags&(mask))!=0)

      assert( iIndexCur>=0 );
      if( op ){
        sqlite3VdbeAddOp3(v, op, iIndexCur, pIx->tnum, iDb);
        sqlite3VdbeSetP4KeyInfo(pParse, pIx);
        if( (pLoop->wsFlags & WHERE_CONSTRAINT)!=0
         && (pLoop->wsFlags & (WHERE_COLUMN_RANGE|WHERE_SKIPSCAN))==0
         && (pWInfo->wctrlFlags&WHERE_ORDERBY_MIN)==0
         && pWInfo->eDistinct!=WHERE_DISTINCT_ORDERED
        ){
          sqlite3VdbeChangeP5(v, OPFLAG_SEEKEQ); /* Hint to COMDB2 */
        }
        VdbeComment((v, "%s", pIx->zName));
#ifdef SQLITE_ENABLE_COLUMN_USED_MASK
        {
          u64 colUsed = 0;

  sqlite3ExprCacheClear(pParse);
  for(i=pWInfo->nLevel-1; i>=0; i--){
    int addr;
    pLevel = &pWInfo->a[i];
    pLoop = pLevel->pWLoop;
    sqlite3VdbeResolveLabel(v, pLevel->addrCont);
    if( pLevel->op!=OP_Noop ){
#ifndef SQLITE_DISABLE_SKIPAHEAD_DISTINCT
      int n = -1;
      int j, k, op;
      int r1 = pParse->nMem+1;
      if( pWInfo->eDistinct==WHERE_DISTINCT_ORDERED
       && (pLoop->wsFlags & WHERE_INDEXED)!=0
       && OptimizationEnabled(db, SQLITE_SkipAhead)
      ){
        /* This is the Skip-ahead optimization.  When doing a DISTINCT query
        ** that has WHERE_DISTINCT_ORDERED, use OP_SkipGT/OP_SkipLT to skip
        ** over all duplicate entries, rather than visiting all duplicates
        ** using OP_Next/OP_Prev. */
        ExprList *pX = pWInfo->pResultSet;
        Index *pIdx = pLoop->u.btree.pIndex;
        for(j=0; j<pX->nExpr; j++){
          Expr *pE = sqlite3ExprSkipCollate(pX->a[j].pExpr);
          if( pE->op==TK_COLUMN ){
            if( pE->iTable!=pLevel->iTabCur ) continue;
            k = 1+sqlite3ColumnOfIndex(pIdx, pE->iColumn);
            if( k>n ) n = k;
          }else if( pIdx->aColExpr ){
            for(k=n+1; k<pIdx->nKeyCol; k++){
              Expr *pI = pIdx->aColExpr->a[k].pExpr;
              if( pI && sqlite3ExprCompare(pE,pI,0)<2 ){
                n = k+1;
                break;
              }
            }
          }
        }
      }
      if( n>0 ){
        for(j=0; j<n; j++){
          sqlite3VdbeAddOp3(v, OP_Column, pLevel->iIdxCur, j, r1+j);
        }
        pParse->nMem += n;
        op = pLevel->op==OP_Prev ? OP_SeekLT : OP_SeekGT;
        k = sqlite3VdbeAddOp4Int(v, op, pLevel->iIdxCur, 0, r1, n);
        VdbeCoverageIf(v, op==OP_SeekLT);
        VdbeCoverageIf(v, op==OP_SeekGT);
        sqlite3VdbeAddOp2(v, OP_Goto, 1, pLevel->p2);
        sqlite3VdbeJumpHere(v, k);
      }else
#endif /* SQLITE_DISABLE_SKIPAHEAD_DISTINCT */
      {
        /* The common case: Advance to the next row */
        sqlite3VdbeAddOp3(v, pLevel->op, pLevel->p1, pLevel->p2, pLevel->p3);
        sqlite3VdbeChangeP5(v, pLevel->p5);
        VdbeCoverage(v);
        VdbeCoverageIf(v, pLevel->op==OP_Next);
        VdbeCoverageIf(v, pLevel->op==OP_Prev);
        VdbeCoverageIf(v, pLevel->op==OP_VNext);
      }
    }
    if( pLoop->wsFlags & WHERE_IN_ABLE && pLevel->u.in.nIn>0 ){
      struct InLoop *pIn;
      int j;
      sqlite3VdbeResolveLabel(v, pLevel->addrNxt);
      for(j=pLevel->u.in.nIn, pIn=&pLevel->u.in.aInLoop[j-1]; j>0; j--, pIn--){
        sqlite3VdbeJumpHere(v, pIn->addrInTop+1);

# 2016-04-15
#
# The author disclaims copyright to this source code.  In place of
# a legal notice, here is a blessing:
#
#    May you do good and not evil.
#    May you find forgiveness for yourself and forgive others.
#    May you share freely, never taking more than you give.
#
#***********************************************************************
# This file implements regression tests for SQLite library.  The
# focus of this script is DISTINCT queries using the skip-ahead 
# optimization.
#

set testdir [file dirname $argv0]
source $testdir/tester.tcl

set testprefix distinct2

do_execsql_test 100 {
  CREATE TABLE t1(x INTEGER PRIMARY KEY);
  INSERT INTO t1 VALUES(0),(1),(2);
  CREATE TABLE t2 AS
     SELECT DISTINCT a.x AS aa, b.x AS bb
      FROM t1 a, t1 b;
  SELECT *, '|' FROM t2 ORDER BY aa, bb;
} {0 0 | 0 1 | 0 2 | 1 0 | 1 1 | 1 2 | 2 0 | 2 1 | 2 2 |}
do_execsql_test 110 {
  DROP TABLE t2;
  CREATE TABLE t2 AS
     SELECT DISTINCT a.x AS aa, b.x AS bb
       FROM t1 a, t1 b
      WHERE a.x IN t1 AND b.x IN t1;
  SELECT *, '|' FROM t2 ORDER BY aa, bb;
} {0 0 | 0 1 | 0 2 | 1 0 | 1 1 | 1 2 | 2 0 | 2 1 | 2 2 |}
do_execsql_test 120 {
  CREATE TABLE t102 (i0 TEXT UNIQUE NOT NULL);
  INSERT INTO t102 VALUES ('0'),('1'),('2');
  DROP TABLE t2;
  CREATE TABLE t2 AS
    SELECT DISTINCT * 
    FROM t102 AS t0 
    JOIN t102 AS t4 ON (t2.i0 IN t102)
    NATURAL JOIN t102 AS t3
    JOIN t102 AS t1 ON (t0.i0 IN t102)
    JOIN t102 AS t2 ON (t2.i0=+t0.i0 OR (t0.i0<>500 AND t2.i0=t1.i0));
  SELECT *, '|' FROM t2 ORDER BY 1, 2, 3, 4, 5;
} {0 0 0 0 | 0 0 1 0 | 0 0 1 1 | 0 0 2 0 | 0 0 2 2 | 0 1 0 0 | 0 1 1 0 | 0 1 1 1 | 0 1 2 0 | 0 1 2 2 | 0 2 0 0 | 0 2 1 0 | 0 2 1 1 | 0 2 2 0 | 0 2 2 2 | 1 0 0 0 | 1 0 0 1 | 1 0 1 1 | 1 0 2 1 | 1 0 2 2 | 1 1 0 0 | 1 1 0 1 | 1 1 1 1 | 1 1 2 1 | 1 1 2 2 | 1 2 0 0 | 1 2 0 1 | 1 2 1 1 | 1 2 2 1 | 1 2 2 2 | 2 0 0 0 | 2 0 0 2 | 2 0 1 1 | 2 0 1 2 | 2 0 2 2 | 2 1 0 0 | 2 1 0 2 | 2 1 1 1 | 2 1 1 2 | 2 1 2 2 | 2 2 0 0 | 2 2 0 2 | 2 2 1 1 | 2 2 1 2 | 2 2 2 2 |}

do_execsql_test 400 {
  CREATE TABLE t4(a,b,c,d,e,f,g,h,i,j);
  INSERT INTO t4 VALUES(0,1,2,3,4,5,6,7,8,9);
  INSERT INTO t4 SELECT * FROM t4;
  INSERT INTO t4 SELECT * FROM t4;
  CREATE INDEX t4x ON t4(c,d,e);
  SELECT DISTINCT a,b,c FROM t4 WHERE a=0 AND b=1;
} {0 1 2}
do_execsql_test 410 {
  SELECT DISTINCT a,b,c,d FROM t4 WHERE a=0 AND b=1;
} {0 1 2 3}
do_execsql_test 411 {
  SELECT DISTINCT d,a,b,c FROM t4 WHERE a=0 AND b=1;
} {3 0 1 2}
do_execsql_test 420 {
  SELECT DISTINCT a,b,c,d,e FROM t4 WHERE a=0 AND b=1;
} {0 1 2 3 4}
do_execsql_test 430 {
  SELECT DISTINCT a,b,c,d,e,f FROM t4 WHERE a=0 AND b=1;
} {0 1 2 3 4 5}

do_execsql_test 500 {
  CREATE TABLE t5(a INT, b INT);
  CREATE UNIQUE INDEX t5x ON t5(a+b);
  INSERT INTO t5(a,b) VALUES(0,0),(1,0),(1,1),(0,3);
  CREATE TEMP TABLE out AS SELECT DISTINCT a+b FROM t5;
  SELECT * FROM out ORDER BY 1;
} {0 1 2 3}

do_execsql_test 600 {
  CREATE TABLE t6a(x INTEGER PRIMARY KEY);
  INSERT INTO t6a VALUES(1);
  CREATE TABLE t6b(y INTEGER PRIMARY KEY);
  INSERT INTO t6b VALUES(2),(3);
  SELECT DISTINCT x, x FROM t6a, t6b;
} {1 1}

finish_test