/ Ticket Change Details
Login
Overview

Artifact ID: 81c613f2a86f62a4c250a5c76c6105ca36c2faa3d338fe8b003f205613aebb96
Ticket: b9f010107724ca997fbfd583696b354b322ecb12
Violation of the Query Planner Stability Guarantee
User & Date: drh 2017-06-23 19:21:59
Changes

  1. Change foundin to "3.19.3"
  2. 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.

  3. Change login to "drh"
  4. Change mimetype to "text/x-fossil-wiki"
  5. Change severity to "Important"
  6. Change status to "Open"
  7. Change title to "Violation of the Query Planner Stability Guarantee"
  8. Change type to "Code_Defect"