Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
Comment: | Do not use a partial index as a table scan in an IN operator. Fix for ticket [1d958d90596593a77420e59]. |
---|---|
Downloads: | Tarball | ZIP archive | SQL archive |
Timelines: | family | ancestors | descendants | both | trunk |
Files: | files | file ages | folders |
SHA3-256: |
15bc915dd7ae25bfdfbe357c3792db36 |
User & Date: | drh 2018-12-08 20:30:31 |
Context
2018-12-12
| ||
11:50 | Do not use a partial index as a table scan in an IN operator. Fix for ticket [1d958d90596593a77420e59]. (check-in: c1c735a8 user: drh tags: branch-3.26) | |
2018-12-09
| ||
18:55 | New test case for ticket [1d958d90596593a77420e59]. (check-in: b7bf3c98 user: drh tags: trunk) | |
2018-12-08
| ||
20:30 | Do not use a partial index as a table scan in an IN operator. Fix for ticket [1d958d90596593a77420e59]. (check-in: 15bc915d user: drh tags: trunk) | |
2018-12-07
| ||
20:26 | Remove a line of code from VACUUM that is no longer needed, due to the change that allows ATTACH to run within a transaction. (check-in: a92c398f user: drh tags: trunk) | |
Changes
Changes to src/expr.c.
︙ | ︙ | |||
2439 2440 2441 2442 2443 2444 2445 2446 2447 2448 2449 2450 2451 2452 | if( affinity_ok ){ /* Search for an existing index that will work for this IN operator */ for(pIdx=pTab->pIndex; pIdx && eType==0; pIdx=pIdx->pNext){ Bitmask colUsed; /* Columns of the index used */ Bitmask mCol; /* Mask for the current column */ if( pIdx->nColumn<nExpr ) continue; /* Maximum nColumn is BMS-2, not BMS-1, so that we can compute ** BITMASK(nExpr) without overflowing */ testcase( pIdx->nColumn==BMS-2 ); testcase( pIdx->nColumn==BMS-1 ); if( pIdx->nColumn>=BMS-1 ) continue; if( mustBeUnique ){ if( pIdx->nKeyCol>nExpr | > | 2439 2440 2441 2442 2443 2444 2445 2446 2447 2448 2449 2450 2451 2452 2453 | if( affinity_ok ){ /* Search for an existing index that will work for this IN operator */ for(pIdx=pTab->pIndex; pIdx && eType==0; pIdx=pIdx->pNext){ Bitmask colUsed; /* Columns of the index used */ Bitmask mCol; /* Mask for the current column */ if( pIdx->nColumn<nExpr ) continue; if( pIdx->pPartIdxWhere!=0 ) continue; /* Maximum nColumn is BMS-2, not BMS-1, so that we can compute ** BITMASK(nExpr) without overflowing */ testcase( pIdx->nColumn==BMS-2 ); testcase( pIdx->nColumn==BMS-1 ); if( pIdx->nColumn>=BMS-1 ) continue; if( mustBeUnique ){ if( pIdx->nKeyCol>nExpr |
︙ | ︙ |
Changes to test/index6.test.
︙ | ︙ | |||
385 386 387 388 389 390 391 | CREATE TABLE t11(a,b,c); CREATE INDEX t11x ON t11(a) WHERE b<>99; EXPLAIN QUERY PLAN SELECT a FROM t11 WHERE b<>99; } {/USING INDEX t11x/} do_execsql_test index6-11.2 { EXPLAIN QUERY PLAN SELECT a FROM t11 WHERE b<>99 AND c<>98; } {/USING INDEX t11x/} | | > > > > > > > > > > > > > > > > > | 385 386 387 388 389 390 391 392 393 394 395 396 397 398 399 400 401 402 403 404 405 406 407 408 409 410 411 | CREATE TABLE t11(a,b,c); CREATE INDEX t11x ON t11(a) WHERE b<>99; EXPLAIN QUERY PLAN SELECT a FROM t11 WHERE b<>99; } {/USING INDEX t11x/} do_execsql_test index6-11.2 { EXPLAIN QUERY PLAN SELECT a FROM t11 WHERE b<>99 AND c<>98; } {/USING INDEX t11x/} # 2018-12-08 # Ticket https://www.sqlite.org/src/info/1d958d90596593a7 # NOT IN operator fails when using a partial index. # do_execsql_test index6-12.1 { DROP TABLE IF EXISTS t1; DROP TABLE IF EXISTS t2; CREATE TABLE t1(a,b); INSERT INTO t1 VALUES(1,1); INSERT INTO t1 VALUES(2,2); CREATE TABLE t2(x); INSERT INTO t2 VALUES(1); INSERT INTO t2 VALUES(2); SELECT 'one', * FROM t2 WHERE x NOT IN (SELECT a FROM t1); CREATE INDEX t1a ON t1(a) WHERE b=1; SELECT 'two', * FROM t2 WHERE x NOT IN (SELECT a FROM t1); } {} finish_test |