SQLite

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. foundin changed to: "3.19.3"
  2. icomment:
    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:
    
    <blockquote><verbatim>
    CREATE TABLE t1(a INTEGER PRIMARY KEY, b);
    CREATE INDEX t1b ON t1(b);
    SELECT a FROM t1 WHERE b GLOB ?1;
    </verbatim></blockquote>
    
    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. login: "drh"
  4. mimetype: "text/x-fossil-wiki"
  5. severity changed to: "Important"
  6. status changed to: "Open"
  7. title changed to: "Violation of the Query Planner Stability Guarantee"
  8. type changed to: "Code_Defect"