SQLite

Check-in [b7595cf2ca]
Login

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

Overview
Comment:Completely disable the skip-ahead-distinct optimization for all but the inner-most loop of a DISTINCT join. Fix for ticket [ef9318757b152e3a26e9592]
Downloads: Tarball | ZIP archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA3-256: b7595cf2cadcba486e60b3c230ccc412a7cf449c1d56cbe65869e6d5d9f1374d
User & Date: drh 2017-11-23 00:45:21.475
Context
2017-11-23
04:45
Fix a problem in the omit-table-from-left-join optimization from check-in [0cd82ee9a8413cf] that was discovered by OSSFuzz. (check-in: b016c28fa5 user: drh tags: trunk)
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: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)
Changes
Unified Diff Ignore Whitespace Patch
Changes to src/where.c.
︙︙
4958
4959
4960
4961
4962
4963
4964

4965
4966
4967
4968
4969
4970
4971
4972
4973
4974
4975
4976
4977
4978
4979
4980
4981
4982
4983
4984
4985
4986
4987
4988
4989
4990
4991
4992
4993
4994

    pLoop = pLevel->pWLoop;
    if( pLevel->op!=OP_Noop ){
#ifndef SQLITE_DISABLE_SKIPAHEAD_DISTINCT
      int addrSeek = 0;
      Index *pIdx;
      int n;
      if( pWInfo->eDistinct==WHERE_DISTINCT_ORDERED

       && (pLoop->wsFlags & WHERE_INDEXED)!=0
       && (pIdx = pLoop->u.btree.pIndex)->hasStat1
       && (n = pLoop->u.btree.nIdxCol)>0
       && pIdx->aiRowLogEst[n]>=36
      ){
        int r1 = pParse->nMem+1;
        int j, op;
        for(j=0; j<n; j++){
          sqlite3VdbeAddOp3(v, OP_Column, pLevel->iIdxCur, j, r1+j);
        }
        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);








>
















<
<
<
<
<
<
<








4958
4959
4960
4961
4962
4963
4964
4965
4966
4967
4968
4969
4970
4971
4972
4973
4974
4975
4976
4977
4978
4979
4980
4981







4982
4983
4984
4985
4986
4987
4988

    pLoop = pLevel->pWLoop;
    if( pLevel->op!=OP_Noop ){
#ifndef SQLITE_DISABLE_SKIPAHEAD_DISTINCT
      int addrSeek = 0;
      Index *pIdx;
      int n;
      if( pWInfo->eDistinct==WHERE_DISTINCT_ORDERED
       && i==pWInfo->nLevel-1  /* Ticket [ef9318757b152e3] 2017-10-21 */
       && (pLoop->wsFlags & WHERE_INDEXED)!=0
       && (pIdx = pLoop->u.btree.pIndex)->hasStat1
       && (n = pLoop->u.btree.nIdxCol)>0
       && pIdx->aiRowLogEst[n]>=36
      ){
        int r1 = pParse->nMem+1;
        int j, op;
        for(j=0; j<n; j++){
          sqlite3VdbeAddOp3(v, OP_Column, pLevel->iIdxCur, j, r1+j);
        }
        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);
︙︙
Changes to test/distinct2.test.
︙︙
194
195
196
197
198
199
200































201
202

    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








>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>



194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233

    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}
db close
sqlite3 db :memory:
do_execsql_test 1010 {
  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 ORDER BY y DESC;
  ANALYZE;
  SELECT DISTINCT y FROM t1, t2 WHERE b=x AND b<>1 ORDER BY y DESC;
} {1 1}
db close
sqlite3 db :memory:
do_execsql_test 1020 {
  CREATE TABLE t1(a, b);
  CREATE INDEX t1a ON t1(a, b);
  -- Lots of rows of (1, 'no'), followed by a single (1, 'yes').
  WITH RECURSIVE c(x) AS (VALUES(1) UNION ALL SELECT x+1 FROM c WHERE x<100)
    INSERT INTO t1(a, b) SELECT 1, 'no' FROM c;
  INSERT INTO t1(a, b) VALUES(1, 'yes');
  CREATE TABLE t2(x PRIMARY KEY);
  INSERT INTO t2 VALUES('yes');
  SELECT DISTINCT a FROM t1, t2 WHERE x=b;
  ANALYZE;
  SELECT DISTINCT a FROM t1, t2 WHERE x=b;
} {1 1}


finish_test