/ Check-in [2dcef5a9]
Login

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

Overview
Comment:Fix the skip-ahead-distinct optimization on joins for cases there the table in the inner loop of the join does not contribute any columns to the result set. Proposed fix for ticket [ef9318757b152e3a2]
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA3-256: 2dcef5a9ae7f347da65207bf6bf612fb12e18e1a6704799322f0cf2a86154cfd
User & Date: drh 2017-11-21 23:38:48
Context
2017-11-23
00:45
Completely disable the skip-ahead-distinct optimization for all but the inner-most loop of a DISTINCT join. Fix for ticket [ef9318757b152e3a26e9592] check-in: b7595cf2 user: drh tags: trunk
2017-11-21
23:47
An experimental optimization to DISTINCT that causes an immediate exit of the inner loop of a join following each output row if the inner loop does not contribute any columns to the result set. Leaf check-in: a33f88ac user: drh tags: distinct-early-out
23:38
Fix the skip-ahead-distinct optimization on joins for cases there the table in the inner loop of the join does not contribute any columns to the result set. Proposed fix for ticket [ef9318757b152e3a2] check-in: 2dcef5a9 user: drh tags: trunk
21:15
Fix typo in the Makefiles for MSVC. check-in: 17dd2f73 user: mistachkin tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/where.c.

  4974   4974           }
  4975   4975           pParse->nMem += n+1;
  4976   4976           op = pLevel->op==OP_Prev ? OP_SeekLT : OP_SeekGT;
  4977   4977           addrSeek = sqlite3VdbeAddOp4Int(v, op, pLevel->iIdxCur, 0, r1, n);
  4978   4978           VdbeCoverageIf(v, op==OP_SeekLT);
  4979   4979           VdbeCoverageIf(v, op==OP_SeekGT);
  4980   4980           sqlite3VdbeAddOp2(v, OP_Goto, 1, pLevel->p2);
         4981  +        if( i<pWInfo->nLevel-1 ){
         4982  +          /* Ticket https://sqlite.org/src/info/ef9318757b152e3 2017-11-21
         4983  +          ** The break location for the next inner loop is above the code
         4984  +          ** generated here, but it should be afterwards.  So call re-resolve
         4985  +          ** the break location to be afterwards. */
         4986  +          sqlite3VdbeResolveLabel(v, pWInfo->a[i+1].addrBrk);
         4987  +        }
  4981   4988         }
  4982   4989   #endif /* SQLITE_DISABLE_SKIPAHEAD_DISTINCT */
  4983   4990         /* The common case: Advance to the next row */
  4984   4991         sqlite3VdbeResolveLabel(v, pLevel->addrCont);
  4985   4992         sqlite3VdbeAddOp3(v, pLevel->op, pLevel->p1, pLevel->p2, pLevel->p3);
  4986   4993         sqlite3VdbeChangeP5(v, pLevel->p5);
  4987   4994         VdbeCoverage(v);

Changes to test/distinct2.test.

   175    175     WxYZ WxYz WxYz WxyZ WxyZ Wxyz Wxyz
   176    176     aBCD aBCD aBCd aBCd aBcD aBcD aBcd aBcd abCD
   177    177     abCD abCd abCd abcD abcD abcd abcd
   178    178     wXYZ wXYZ wXYz wXYz wXyZ wXyZ wXyz wXyz wxYZ
   179    179     wxYZ wxYz wxYz wxyZ wxyZ wxyz wxyz
   180    180   }
   181    181   
          182  +# Ticket https://sqlite.org/src/info/ef9318757b152e3a on 2017-11-21
          183  +# Incorrect result due to a skip-ahead-distinct optimization on a
          184  +# join where no rows of the inner loop appear in the result set.
          185  +#
          186  +db close
          187  +sqlite3 db :memory:
          188  +do_execsql_test 1000 {
          189  +  CREATE TABLE t1(a INTEGER PRIMARY KEY, b INTEGER);
          190  +  CREATE INDEX t1b ON t1(b);
          191  +  CREATE TABLE t2(x INTEGER PRIMARY KEY, y INTEGER);
          192  +  CREATE INDEX t2y ON t2(y);
          193  +  WITH RECURSIVE c(x) AS (VALUES(0) UNION ALL SELECT x+1 FROM c WHERE x<49)
          194  +    INSERT INTO t1(b) SELECT x/10 - 1 FROM c;
          195  +  WITH RECURSIVE c(x) AS (VALUES(-1) UNION ALL SELECT x+1 FROM c WHERE x<19)
          196  +    INSERT INTO t2(x,y) SELECT x, 1 FROM c;
          197  +  SELECT DISTINCT y FROM t1, t2 WHERE b=x AND b<>-1;
          198  +  ANALYZE;
          199  +  SELECT DISTINCT y FROM t1, t2 WHERE b=x AND b<>-1;
          200  +} {1 1}
   182    201   
   183    202   finish_test