/ Check-in [a871d69c]
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: 89bf0399 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: 89bf0399 user: drh tags: trunk
01:17
Disable the skip-scan optimization for DISTINCT queries. Fix for ticket [ced41c7c7d6b4d36] check-in: a871d69c user: drh tags: trunk
2019-07-29
17:18
Convert an assert() in R-Tree into a database corruption detection case. check-in: 48b518eb user: drh tags: trunk
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to src/where.c.

2719
2720
2721
2722
2723
2724
2725

2726
2727
2728
2729
2730
2731
2732
  ** more expensive.  */
  assert( 42==sqlite3LogEst(18) );
  if( saved_nEq==saved_nSkip
   && saved_nEq+1<pProbe->nKeyCol
   && pProbe->noSkipScan==0
   && OptimizationEnabled(db, SQLITE_SkipScan)
   && pProbe->aiRowLogEst[saved_nEq+1]>=42  /* TUNING: Minimum for skip-scan */

   && (rc = whereLoopResize(db, pNew, pNew->nLTerm+1))==SQLITE_OK
  ){
    LogEst nIter;
    pNew->u.btree.nEq++;
    pNew->nSkip++;
    pNew->aLTerm[pNew->nLTerm++] = 0;
    pNew->wsFlags |= WHERE_SKIPSCAN;







>







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

Changes to test/skipscan1.test.

369
370
371
372
373
374
375

















376
  EXPLAIN QUERY PLAN
  SELECT a,b,c,d,'|' FROM t6 WHERE d<>99 AND b=345 ORDER BY a DESC;
} {/* USING INDEX t6abc (ANY(a) AND b=?)*/}
do_execsql_test skipscan1-2.3 {
  SELECT a,b,c,d,'|' FROM t6 WHERE d<>99 AND b=345 ORDER BY a DESC;
} {}


















finish_test







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

369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
  EXPLAIN QUERY PLAN
  SELECT a,b,c,d,'|' FROM t6 WHERE d<>99 AND b=345 ORDER BY a DESC;
} {/* USING INDEX t6abc (ANY(a) AND b=?)*/}
do_execsql_test skipscan1-2.3 {
  SELECT a,b,c,d,'|' FROM t6 WHERE d<>99 AND b=345 ORDER BY a DESC;
} {}

# 2019-07-29 Ticket ced41c7c7d6b4d36
# Skip-scan is disabled for DISTINCT queries
#
db close
sqlite3 db :memory:
do_execsql_test skipscan1-3.1 {
  CREATE TABLE t1 (c1, c2, c3, c4, PRIMARY KEY(c4, c3));
  INSERT INTO t1 VALUES(NULL,0,1,NULL);
  INSERT INTO t1 VALUES(0,NULL,1,NULL);
  INSERT INTO t1 VALUES(NULL,NULL,1,NULL);
  ANALYZE sqlite_master;
  INSERT INTO sqlite_stat1 VALUES('t1','sqlite_autoindex_t1_1','18 18 6');
  ANALYZE sqlite_master;
  SELECT DISTINCT quote(c1), quote(c2), quote(c3), quote(c4), '|'
    FROM t1 WHERE t1.c3 = 1;
} {NULL 0 1 NULL | 0 NULL 1 NULL | NULL NULL 1 NULL |}

finish_test