/ Check-in [c1c735a8]
Login
SQLite training in Houston TX on 2019-11-05 (details)
Part of the 2019 Tcl Conference

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 | branch-3.26
Files: files | file ages | folders
SHA3-256: c1c735a8c6eb4b53edba1130cc7bc7bd0499e8752155caf405eb5aa6f09dcd37
User & Date: drh 2018-12-12 11:50:28
Context
2018-12-12
11:54
Fix a problem with using "<db>-vacuum" (the default) as the state database when resuming an RBU vacuum. check-in: 6b7567fc user: drh tags: branch-3.26
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-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
02:01
Fix the sqlite3ExprDup() function so that it correctly duplicates the Window object list on a Select that contains window functions. Fix for ticket [f09fcd17810f65f717]. check-in: 65aafb55 user: drh tags: branch-3.26
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to src/expr.c.

2447
2448
2449
2450
2451
2452
2453

2454
2455
2456
2457
2458
2459
2460

      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







>







2447
2448
2449
2450
2451
2452
2453
2454
2455
2456
2457
2458
2459
2460
2461

      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
392



393














394
  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/}
  


















finish_test







|
>
>
>
|
>
>
>
>
>
>
>
>
>
>
>
>
>
>

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