SQLite

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
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA3-256: a871d69c6de65038360aa6142fbad22689fb347e526cca56bb83e695c1441fbe
User & Date: drh 2019-07-30 01:17:03.473
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
Side-by-Side Diff Ignore Whitespace Patch
Changes to src/where.c.
2719
2720
2721
2722
2723
2724
2725

2726
2727
2728
2729
2730
2731
2732
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
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