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