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 |
| Timelines: | family | ancestors | descendants | both | trunk |
| Files: | files | file ages | folders |
| SHA3-256: |
2dcef5a9ae7f347da65207bf6bf612fb |
| User & Date: | drh 2017-11-21 23:38:48.349 |
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: b7595cf2ca 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: a33f88acd7 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: 2dcef5a9ae user: drh tags: trunk) | |
| 21:15 | Fix typo in the Makefiles for MSVC. (check-in: 17dd2f7314 user: mistachkin tags: trunk) | |
Changes
Changes to src/where.c.
| ︙ | ︙ | |||
4974 4975 4976 4977 4978 4979 4980 4981 4982 4983 4984 4985 4986 4987 |
}
pParse->nMem += n+1;
op = pLevel->op==OP_Prev ? OP_SeekLT : OP_SeekGT;
addrSeek = 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);
}
#endif /* SQLITE_DISABLE_SKIPAHEAD_DISTINCT */
/* The common case: Advance to the next row */
sqlite3VdbeResolveLabel(v, pLevel->addrCont);
sqlite3VdbeAddOp3(v, pLevel->op, pLevel->p1, pLevel->p2, pLevel->p3);
sqlite3VdbeChangeP5(v, pLevel->p5);
VdbeCoverage(v);
| > > > > > > > | 4974 4975 4976 4977 4978 4979 4980 4981 4982 4983 4984 4985 4986 4987 4988 4989 4990 4991 4992 4993 4994 |
}
pParse->nMem += n+1;
op = pLevel->op==OP_Prev ? OP_SeekLT : OP_SeekGT;
addrSeek = 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);
if( i<pWInfo->nLevel-1 ){
/* Ticket https://sqlite.org/src/info/ef9318757b152e3 2017-11-21
** The break location for the next inner loop is above the code
** generated here, but it should be afterwards. So call re-resolve
** the break location to be afterwards. */
sqlite3VdbeResolveLabel(v, pWInfo->a[i+1].addrBrk);
}
}
#endif /* SQLITE_DISABLE_SKIPAHEAD_DISTINCT */
/* The common case: Advance to the next row */
sqlite3VdbeResolveLabel(v, pLevel->addrCont);
sqlite3VdbeAddOp3(v, pLevel->op, pLevel->p1, pLevel->p2, pLevel->p3);
sqlite3VdbeChangeP5(v, pLevel->p5);
VdbeCoverage(v);
|
| ︙ | ︙ |
Changes to test/distinct2.test.
| ︙ | ︙ | |||
175 176 177 178 179 180 181 182 183 | WxYZ WxYz WxYz WxyZ WxyZ Wxyz Wxyz aBCD aBCD aBCd aBCd aBcD aBcD aBcd aBcd abCD abCD abCd abCd abcD abcD abcd abcd wXYZ wXYZ wXYz wXYz wXyZ wXyZ wXyz wXyz wxYZ wxYZ wxYz wxYz wxyZ wxyZ wxyz wxyz } finish_test | > > > > > > > > > > > > > > > > > > > | 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 |
WxYZ WxYz WxYz WxyZ WxyZ Wxyz Wxyz
aBCD aBCD aBCd aBCd aBcD aBcD aBcd aBcd abCD
abCD abCd abCd abcD abcD abcd abcd
wXYZ wXYZ wXYz wXYz wXyZ wXyZ wXyz wXyz wxYZ
wxYZ wxYz wxYz wxyZ wxyZ wxyz wxyz
}
# Ticket https://sqlite.org/src/info/ef9318757b152e3a on 2017-11-21
# Incorrect result due to a skip-ahead-distinct optimization on a
# join where no rows of the inner loop appear in the result set.
#
db close
sqlite3 db :memory:
do_execsql_test 1000 {
CREATE TABLE t1(a INTEGER PRIMARY KEY, b INTEGER);
CREATE INDEX t1b ON t1(b);
CREATE TABLE t2(x INTEGER PRIMARY KEY, y INTEGER);
CREATE INDEX t2y ON t2(y);
WITH RECURSIVE c(x) AS (VALUES(0) UNION ALL SELECT x+1 FROM c WHERE x<49)
INSERT INTO t1(b) SELECT x/10 - 1 FROM c;
WITH RECURSIVE c(x) AS (VALUES(-1) UNION ALL SELECT x+1 FROM c WHERE x<19)
INSERT INTO t2(x,y) SELECT x, 1 FROM c;
SELECT DISTINCT y FROM t1, t2 WHERE b=x AND b<>-1;
ANALYZE;
SELECT DISTINCT y FROM t1, t2 WHERE b=x AND b<>-1;
} {1 1}
finish_test
|