/ Check-in [a871d69c6d]
Login

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

Overview
Comment:Disable the skip-scan optimization for DISTINCT queries. Fix for ticket [ced41c7c7d6b4d36]
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA3-256: a871d69c6de65038360aa6142fbad22689fb347e526cca56bb83e695c1441fbe
User & Date: drh 2019-07-30 01:17:03
References
2019-07-30
14:22
Improved fix for ticket [ced41c7c7d6b4d36] that keeps skip-scan enabled, but avoids identifying a skip-scan as order-distinct (because it is not) and thus forces a separate b-tree to implement the DISTINCT clause of a query. This undoes check-in [a871d69c6de65038] and substitutes a new fix. check-in: 89bf0399e8 user: drh tags: trunk
Context
2019-07-30
14:22
Improved fix for ticket [ced41c7c7d6b4d36] that keeps skip-scan enabled, but avoids identifying a skip-scan as order-distinct (because it is not) and thus forces a separate b-tree to implement the DISTINCT clause of a query. This undoes check-in [a871d69c6de65038] and substitutes a new fix. check-in: 89bf0399e8 user: drh tags: trunk
01:17
Disable the skip-scan optimization for DISTINCT queries. Fix for ticket [ced41c7c7d6b4d36] check-in: a871d69c6d user: drh tags: trunk
2019-07-29
17:18
Convert an assert() in R-Tree into a database corruption detection case. check-in: 48b518eb6f user: drh tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/where.c.

  2719   2719     ** more expensive.  */
  2720   2720     assert( 42==sqlite3LogEst(18) );
  2721   2721     if( saved_nEq==saved_nSkip
  2722   2722      && saved_nEq+1<pProbe->nKeyCol
  2723   2723      && pProbe->noSkipScan==0
  2724   2724      && OptimizationEnabled(db, SQLITE_SkipScan)
  2725   2725      && pProbe->aiRowLogEst[saved_nEq+1]>=42  /* TUNING: Minimum for skip-scan */
         2726  +   && (pWInfo->wctrlFlags & (WHERE_WANT_DISTINCT|WHERE_DISTINCTBY))==0
  2726   2727      && (rc = whereLoopResize(db, pNew, pNew->nLTerm+1))==SQLITE_OK
  2727   2728     ){
  2728   2729       LogEst nIter;
  2729   2730       pNew->u.btree.nEq++;
  2730   2731       pNew->nSkip++;
  2731   2732       pNew->aLTerm[pNew->nLTerm++] = 0;
  2732   2733       pNew->wsFlags |= WHERE_SKIPSCAN;

Changes to test/skipscan1.test.

   369    369     EXPLAIN QUERY PLAN
   370    370     SELECT a,b,c,d,'|' FROM t6 WHERE d<>99 AND b=345 ORDER BY a DESC;
   371    371   } {/* USING INDEX t6abc (ANY(a) AND b=?)*/}
   372    372   do_execsql_test skipscan1-2.3 {
   373    373     SELECT a,b,c,d,'|' FROM t6 WHERE d<>99 AND b=345 ORDER BY a DESC;
   374    374   } {}
   375    375   
          376  +# 2019-07-29 Ticket ced41c7c7d6b4d36
          377  +# Skip-scan is disabled for DISTINCT queries
          378  +#
          379  +db close
          380  +sqlite3 db :memory:
          381  +do_execsql_test skipscan1-3.1 {
          382  +  CREATE TABLE t1 (c1, c2, c3, c4, PRIMARY KEY(c4, c3));
          383  +  INSERT INTO t1 VALUES(NULL,0,1,NULL);
          384  +  INSERT INTO t1 VALUES(0,NULL,1,NULL);
          385  +  INSERT INTO t1 VALUES(NULL,NULL,1,NULL);
          386  +  ANALYZE sqlite_master;
          387  +  INSERT INTO sqlite_stat1 VALUES('t1','sqlite_autoindex_t1_1','18 18 6');
          388  +  ANALYZE sqlite_master;
          389  +  SELECT DISTINCT quote(c1), quote(c2), quote(c3), quote(c4), '|'
          390  +    FROM t1 WHERE t1.c3 = 1;
          391  +} {NULL 0 1 NULL | 0 NULL 1 NULL | NULL NULL 1 NULL |}
          392  +
   376    393   finish_test