/ Check-in [b7595cf2]
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 | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA3-256: b7595cf2cadcba486e60b3c230ccc412a7cf449c1d56cbe65869e6d5d9f1374d
User & Date: drh 2017-11-23 00:45:21
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: b016c28f 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: b7595cf2 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: 2dcef5a9 user: drh tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Show Whitespace Changes Patch

Changes to src/where.c.

  4958   4958       pLoop = pLevel->pWLoop;
  4959   4959       if( pLevel->op!=OP_Noop ){
  4960   4960   #ifndef SQLITE_DISABLE_SKIPAHEAD_DISTINCT
  4961   4961         int addrSeek = 0;
  4962   4962         Index *pIdx;
  4963   4963         int n;
  4964   4964         if( pWInfo->eDistinct==WHERE_DISTINCT_ORDERED
         4965  +       && i==pWInfo->nLevel-1  /* Ticket [ef9318757b152e3] 2017-10-21 */
  4965   4966          && (pLoop->wsFlags & WHERE_INDEXED)!=0
  4966   4967          && (pIdx = pLoop->u.btree.pIndex)->hasStat1
  4967   4968          && (n = pLoop->u.btree.nIdxCol)>0
  4968   4969          && pIdx->aiRowLogEst[n]>=36
  4969   4970         ){
  4970   4971           int r1 = pParse->nMem+1;
  4971   4972           int j, op;
................................................................................
  4974   4975           }
  4975   4976           pParse->nMem += n+1;
  4976   4977           op = pLevel->op==OP_Prev ? OP_SeekLT : OP_SeekGT;
  4977   4978           addrSeek = sqlite3VdbeAddOp4Int(v, op, pLevel->iIdxCur, 0, r1, n);
  4978   4979           VdbeCoverageIf(v, op==OP_SeekLT);
  4979   4980           VdbeCoverageIf(v, op==OP_SeekGT);
  4980   4981           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         -        }
  4988   4982         }
  4989   4983   #endif /* SQLITE_DISABLE_SKIPAHEAD_DISTINCT */
  4990   4984         /* The common case: Advance to the next row */
  4991   4985         sqlite3VdbeResolveLabel(v, pLevel->addrCont);
  4992   4986         sqlite3VdbeAddOp3(v, pLevel->op, pLevel->p1, pLevel->p2, pLevel->p3);
  4993   4987         sqlite3VdbeChangeP5(v, pLevel->p5);
  4994   4988         VdbeCoverage(v);

Changes to test/distinct2.test.

   194    194       INSERT INTO t1(b) SELECT x/10 - 1 FROM c;
   195    195     WITH RECURSIVE c(x) AS (VALUES(-1) UNION ALL SELECT x+1 FROM c WHERE x<19)
   196    196       INSERT INTO t2(x,y) SELECT x, 1 FROM c;
   197    197     SELECT DISTINCT y FROM t1, t2 WHERE b=x AND b<>-1;
   198    198     ANALYZE;
   199    199     SELECT DISTINCT y FROM t1, t2 WHERE b=x AND b<>-1;
   200    200   } {1 1}
          201  +db close
          202  +sqlite3 db :memory:
          203  +do_execsql_test 1010 {
          204  +  CREATE TABLE t1(a INTEGER PRIMARY KEY, b INTEGER);
          205  +  CREATE INDEX t1b ON t1(b);
          206  +  CREATE TABLE t2(x INTEGER PRIMARY KEY, y INTEGER);
          207  +  CREATE INDEX t2y ON t2(y);
          208  +  WITH RECURSIVE c(x) AS (VALUES(0) UNION ALL SELECT x+1 FROM c WHERE x<49)
          209  +    INSERT INTO t1(b) SELECT -(x/10 - 1) FROM c;
          210  +  WITH RECURSIVE c(x) AS (VALUES(-1) UNION ALL SELECT x+1 FROM c WHERE x<19)
          211  +    INSERT INTO t2(x,y) SELECT -x, 1 FROM c;
          212  +  SELECT DISTINCT y FROM t1, t2 WHERE b=x AND b<>1 ORDER BY y DESC;
          213  +  ANALYZE;
          214  +  SELECT DISTINCT y FROM t1, t2 WHERE b=x AND b<>1 ORDER BY y DESC;
          215  +} {1 1}
          216  +db close
          217  +sqlite3 db :memory:
          218  +do_execsql_test 1020 {
          219  +  CREATE TABLE t1(a, b);
          220  +  CREATE INDEX t1a ON t1(a, b);
          221  +  -- Lots of rows of (1, 'no'), followed by a single (1, 'yes').
          222  +  WITH RECURSIVE c(x) AS (VALUES(1) UNION ALL SELECT x+1 FROM c WHERE x<100)
          223  +    INSERT INTO t1(a, b) SELECT 1, 'no' FROM c;
          224  +  INSERT INTO t1(a, b) VALUES(1, 'yes');
          225  +  CREATE TABLE t2(x PRIMARY KEY);
          226  +  INSERT INTO t2 VALUES('yes');
          227  +  SELECT DISTINCT a FROM t1, t2 WHERE x=b;
          228  +  ANALYZE;
          229  +  SELECT DISTINCT a FROM t1, t2 WHERE x=b;
          230  +} {1 1}
          231  +
   201    232   
   202    233   finish_test