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 | SQL archive |
| Timelines: | family | ancestors | descendants | both | trunk |
| Files: | files | file ages | folders |
| SHA3-256: |
89bf0399e87be76e7b264e0a35e8a6d3 |
| User & Date: | drh 2019-07-30 14:22:10 |
Context
|
2019-07-30
| ||
| 21:00 | Fix the build for when -DSQLITE_SMALL_STACK is used. check-in: b9eda224 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: 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 | |
Changes
Changes to src/where.c.
2719
2720
2721
2722
2723
2724
2725
2726
2727
2728
2729
2730
2731
2732
2733
....
3741
3742
3743
3744
3745
3746
3747
3748
3749
3750
3751
3752
3753
3754
3755
|
** 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; ................................................................................ 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); } /* Loop through all columns of the index and deal with the ones ** that are not constrained by == or IN. */ rev = revSet = 0; distinctColumns = 0; |
<
|
>
|
2719
2720
2721
2722
2723
2724
2725
2726
2727
2728
2729
2730
2731
2732
....
3740
3741
3742
3743
3744
3745
3746
3747
3748
3749
3750
3751
3752
3753
3754
3755
|
** 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;
................................................................................
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 377 378 379 380 381 382 383 384 385 386 387 388 389 390 391 392 393 |
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
|
| | | | > | > > > > > > > | > |
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
|