Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
Comment: | 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. |
---|---|
Downloads: | Tarball | ZIP archive |
Timelines: | family | ancestors | descendants | both | trunk |
Files: | files | file ages | folders |
SHA3-256: |
89bf0399e87be76e7b264e0a35e8a6d3 |
User & Date: | drh 2019-07-30 14:22:10.200 |
Context
2019-07-30
| ||
21:00 | Fix the build for when -DSQLITE_SMALL_STACK is used. (check-in: b9eda2249a user: drh tags: trunk) | |
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) | |
Changes
Changes to src/where.c.
︙ | ︙ | |||
2719 2720 2721 2722 2723 2724 2725 | ** 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 */ | < | 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; |
︙ | ︙ | |||
3741 3742 3743 3744 3745 3746 3747 | return 0; }else{ nKeyCol = pIndex->nKeyCol; nColumn = pIndex->nColumn; assert( nColumn==nKeyCol+1 || !HasRowid(pIndex->pTable) ); assert( pIndex->aiColumn[nColumn-1]==XN_ROWID || !HasRowid(pIndex->pTable)); | | > | 3740 3741 3742 3743 3744 3745 3746 3747 3748 3749 3750 3751 3752 3753 3754 3755 | return 0; }else{ nKeyCol = pIndex->nKeyCol; nColumn = pIndex->nColumn; assert( nColumn==nKeyCol+1 || !HasRowid(pIndex->pTable) ); assert( pIndex->aiColumn[nColumn-1]==XN_ROWID || !HasRowid(pIndex->pTable)); isOrderDistinct = IsUniqueIndex(pIndex) && (pLoop->wsFlags & WHERE_SKIPSCAN)==0; } /* Loop through all columns of the index and deal with the ones ** that are not constrained by == or IN. */ rev = revSet = 0; distinctColumns = 0; |
︙ | ︙ |
Changes to test/skipscan1.test.
︙ | ︙ | |||
370 371 372 373 374 375 376 | 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 | | | | | > | > > > > > > > > | 370 371 372 373 374 375 376 377 378 379 380 381 382 383 384 385 386 387 388 389 390 391 392 393 394 395 396 397 398 399 400 401 402 | 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 # A skipscan query is not order-distinct # 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(3,0,1,NULL); INSERT INTO t1 VALUES(0,4,1,NULL); INSERT INTO t1 VALUES(5,6,1,NULL); INSERT INTO t1 VALUES(0,4,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; } {3 0 1 NULL | 0 4 1 NULL | 5 6 1 NULL |} do_eqp_test skipscan1-3.2 { SELECT DISTINCT quote(c1), quote(c2), quote(c3), quote(c4), '|' FROM t1 WHERE t1.c3 = 1; } { QUERY PLAN |--SEARCH TABLE t1 USING INDEX sqlite_autoindex_t1_1 (ANY(c4) AND c3=?) `--USE TEMP B-TREE FOR DISTINCT } finish_test |