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: |
a871d69c6de65038360aa6142fbad226 |
| 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
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
|