SQLite

Check-in [7058d93b]
Login

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
Comment:Do not allow the WHERE_IDX_ONLY query planner result in cases where a partial index is used on an UPDATE or a DELETE, since the code might still need to access the original table due to parameterized terms in the WHERE clause of the partial index.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | partial-index-terms-patch
Files: files | file ages | folders
SHA3-256: 7058d93b097aeb461412537a747e0aabf641e184add37cc4f7cfbe75404060f9
User & Date: drh 2024-08-08 19:45:30
Context
2024-08-08
19:45
Do not allow the WHERE_IDX_ONLY query planner result in cases where a partial index is used on an UPDATE or a DELETE, since the code might still need to access the original table due to parameterized terms in the WHERE clause of the partial index. (Closed-Leaf check-in: 7058d93b user: drh tags: partial-index-terms-patch)
15:26
Ensure sqlite3expert.c unregisters any SQL user-functions it registers with the database handle before returning. (check-in: 123b154c user: dan tags: trunk)
Changes
Unified Diff Ignore Whitespace Patch
Changes to src/where.c.
︙︙
4043
4044
4045
4046
4047
4048
4049
4050




4051
4052
4053
4054
4055
4056
4057
4058
4059
4060

            }else{
              assert( isCov==WHERE_EXPRIDX );
              WHERETRACE(0x200,
                 ("-> %s might be a covering expression index"
                  " according to whereIsCoveringIndex()\n", pProbe->zName));
            }
          }
        }else if( m==0 ){




          WHERETRACE(0x200,
             ("-> %s a covering index according to bitmasks\n",
             pProbe->zName, m==0 ? "is" : "is not"));
          pNew->wsFlags = WHERE_IDX_ONLY | WHERE_INDEXED;
        }
      }

      /* Full scan via index */
      if( b
       || !HasRowid(pTab)








|
>
>
>
>

|
|








4043
4044
4045
4046
4047
4048
4049
4050
4051
4052
4053
4054
4055
4056
4057
4058
4059
4060
4061
4062
4063
4064

            }else{
              assert( isCov==WHERE_EXPRIDX );
              WHERETRACE(0x200,
                 ("-> %s might be a covering expression index"
                  " according to whereIsCoveringIndex()\n", pProbe->zName));
            }
          }
        }else if( m==0 && (pProbe->pPartIdxWhere==0 || pWInfo->pSelect!=0) ){
          /*              ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
          ** Do not use WHERE_IDX_ONLY based purely on bitmaps for a
          ** partial indexes in an UPDATE or DELETE.  Solution to the bug
          ** reported in Forum thread e60e4c295d22f8ce (2024-08-08). */
          WHERETRACE(0x200,
             ("-> %s is a covering index according to bitmasks\n",
             pProbe->zName));
          pNew->wsFlags = WHERE_IDX_ONLY | WHERE_INDEXED;
        }
      }

      /* Full scan via index */
      if( b
       || !HasRowid(pTab)
︙︙
Changes to test/indexA.test.
︙︙
343
344
345
346
347
348
349
















350

}
do_execsql_test 8.1 {
  SELECT * FROM t1 WHERE b=4;
} {
  1 4 1  2 4 2
}

















finish_test








>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>


343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366

}
do_execsql_test 8.1 {
  SELECT * FROM t1 WHERE b=4;
} {
  1 4 1  2 4 2
}


#-------------------------------------------------------------------------
# 2024-08-08
# https://sqlite.org/forum/forumpost/e60e4c295d22f8ce
#
unset -nocomplain int_1
set int_1 1
do_execsql_test 9.0 {
  DROP TABLE IF EXISTS t1;
  CREATE TABLE t1(a TEXT PRIMARY KEY, b INT, c INT) WITHOUT ROWID;
  CREATE UNIQUE INDEX t1c ON t1(c) WHERE b=1;
  INSERT INTO t1(a,b,c) VALUES('a',1,2);
  UPDATE t1 SET a='z' WHERE b=$int_1 AND c=2;
  SELECT * FROM t1;
} {z 1 2}

finish_test