Violation of the Query Planner Stability Guarantee
|User & Date:||drh 2017-06-23 19:21:59|
- Change foundin to "3.19.3"
- Change icomment to:
The Query Planner Stability Guarantee (QPSG) states that SQLite will always generate the same query plan given the same source SQL text, as long as certain conditions are met, such as that there are no changes in the database schema. The QPSG is important for many embedded systems.
Unfortunately the QPSG is violated in SQLite 3.19.3 by the query at the end of the following SQL:
CREATE TABLE t1(a INTEGER PRIMARY KEY, b); CREATE INDEX t1b ON t1(b); SELECT a FROM t1 WHERE b GLOB ?1;
If the ?1 parameter is bound with "xyz*" then the query uses the t1b index. But if the ?1 parameter is bound to "0xyz*" then a full table scan is run. This is a violation of the QPST.
The problem appears to have been introduced by the LIKE optimization enhancement of check-in [158290c0ab] which landed in SQLite 3.18.0.
- Change login to "drh"
- Change mimetype to "text/x-fossil-wiki"
- Change severity to "Important"
- Change status to "Open"
- Change title to "Violation of the Query Planner Stability Guarantee"
- Change type to "Code_Defect"