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

Changes to src/where.c.

  4577   4577     sqlite3ExprCacheClear(pParse);
  4578   4578     for(i=pWInfo->nLevel-1; i>=0; i--){
  4579   4579       int addr;
  4580   4580       pLevel = &pWInfo->a[i];
  4581   4581       pLoop = pLevel->pWLoop;
  4582   4582       sqlite3VdbeResolveLabel(v, pLevel->addrCont);
  4583   4583       if( pLevel->op!=OP_Noop ){
  4584         -      if( pWInfo->eDistinct==WHERE_DISTINCT_ORDERED ){
         4584  +      if( pWInfo->eDistinct==WHERE_DISTINCT_ORDERED
         4585  +       && (pLoop->wsFlags & WHERE_INDEXED)!=0
         4586  +      ){
  4585   4587           int j, k, op;
  4586   4588           int r1 = pParse->nMem+1;
  4587   4589           int n = 0;
  4588   4590           ExprList *pX = pWInfo->pDistinctSet;
  4589   4591           for(j=0; j<pX->nExpr; j++){
  4590   4592             Expr *pE = sqlite3ExprSkipCollate(pX->a[j].pExpr);
  4591   4593             if( pE->op==TK_COLUMN && pE->iTable==pLevel->iTabCur ) n++;
  4592   4594           }
  4593   4595           for(j=0; j<n; j++){
  4594   4596             sqlite3VdbeAddOp3(v, OP_Column, pLevel->iIdxCur, j, r1+j);
  4595   4597           }
  4596   4598           pParse->nMem += n;
  4597         -        op = pLevel->op==OP_Prev ? OP_SeekLT : OP_SeekGT;
  4598         -        k = sqlite3VdbeAddOp4Int(v, op, pLevel->iIdxCur, 0, r1, n);
  4599         -        VdbeCoverageIf(v, op==OP_SeekLT);
  4600         -        VdbeCoverageIf(v, op==OP_SeekGT);
  4601         -        sqlite3VdbeAddOp2(v, OP_Goto, 1, pLevel->p2);
  4602         -        sqlite3VdbeJumpHere(v, k);
         4599  +        if( n>0 ){
         4600  +          op = pLevel->op==OP_Prev ? OP_SeekLT : OP_SeekGT;
         4601  +          k = sqlite3VdbeAddOp4Int(v, op, pLevel->iIdxCur, 0, r1, n);
         4602  +          VdbeCoverageIf(v, op==OP_SeekLT);
         4603  +          VdbeCoverageIf(v, op==OP_SeekGT);
         4604  +          sqlite3VdbeAddOp2(v, OP_Goto, 1, pLevel->p2);
         4605  +          sqlite3VdbeJumpHere(v, k);
         4606  +        }
  4603   4607         }else{
  4604   4608           sqlite3VdbeAddOp3(v, pLevel->op, pLevel->p1, pLevel->p2, pLevel->p3);
  4605   4609           sqlite3VdbeChangeP5(v, pLevel->p5);
  4606   4610           VdbeCoverage(v);
  4607   4611           VdbeCoverageIf(v, pLevel->op==OP_Next);
  4608   4612           VdbeCoverageIf(v, pLevel->op==OP_Prev);
  4609   4613           VdbeCoverageIf(v, pLevel->op==OP_VNext);

Added test/distinct2.test.

            1  +# 2016-04-15
            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  +# This file implements regression tests for SQLite library.  The
           12  +# focus of this script is DISTINCT queries using the skip-ahead 
           13  +# optimization.
           14  +#
           15  +
           16  +set testdir [file dirname $argv0]
           17  +source $testdir/tester.tcl
           18  +
           19  +set testprefix distinct2
           20  +
           21  +do_execsql_test 100 {
           22  +  CREATE TABLE t1(x INTEGER PRIMARY KEY);
           23  +  INSERT INTO t1 VALUES(0),(1),(2);
           24  +  CREATE TABLE t2 AS
           25  +     SELECT DISTINCT a.x AS aa, b.x AS bb
           26  +      FROM t1 a, t1 b;
           27  +  SELECT *, '|' FROM t2 ORDER BY aa, bb;
           28  +} {0 0 | 0 1 | 0 2 | 1 0 | 1 1 | 1 2 | 2 0 | 2 1 | 2 2 |}
           29  +do_execsql_test 110 {
           30  +  DROP TABLE t2;
           31  +  CREATE TABLE t2 AS
           32  +     SELECT DISTINCT a.x AS aa, b.x AS bb
           33  +       FROM t1 a, t1 b
           34  +      WHERE a.x IN t1 AND b.x IN t1;
           35  +  SELECT *, '|' FROM t2 ORDER BY aa, bb;
           36  +} {0 0 | 0 1 | 0 2 | 1 0 | 1 1 | 1 2 | 2 0 | 2 1 | 2 2 |}
           37  +do_execsql_test 120 {
           38  +  CREATE TABLE t102 (i0 TEXT UNIQUE NOT NULL);
           39  +  INSERT INTO t102 VALUES ('0'),('1'),('2');
           40  +  DROP TABLE t2;
           41  +  CREATE TABLE t2 AS
           42  +    SELECT DISTINCT * 
           43  +    FROM t102 AS t0 
           44  +    JOIN t102 AS t4 ON (t2.i0 IN t102)
           45  +    NATURAL JOIN t102 AS t3
           46  +    JOIN t102 AS t1 ON (t0.i0 IN t102)
           47  +    JOIN t102 AS t2 ON (t2.i0=+t0.i0 OR (t0.i0<>500 AND t2.i0=t1.i0));
           48  +  SELECT *, '|' FROM t2 ORDER BY 1, 2, 3, 4, 5;
           49  +} {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 |}
           50  +
           51  +
           52  +finish_test