SQLite

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.

Further testing shows that this problem is much older than originally thought. If the column passed to the GLOB operator has type TEXT, like this:

CREATE TABLE t1(a INTEGER PRIMARY KEY, b TEXT);
CREATE INDEX t1b ON t1(b);
SELECT a FROM t1 WHERE b GLOB ?1;

Then the trouble dates back to check-in [50136840d5] circa 2009-10-16 which first landed on version 3.6.20.

This is the test script used for bisecting:

sqlite3 db :memory:
db eval {
  CREATE TABLE t1(a INTEGER PRIMARY KEY, b TEXT);
  CREATE INDEX t1b ON t1(b);
}
set x "abc*"
set orig [db eval {explain SELECT a FROM t1 WHERE b GLOB $x}]
set x "*abc"
set new [db eval {explain SELECT a FROM t1 WHERE b GLOB $x}]
if {$new!=$orig} {
  puts "different plan on reprepare"
} else {
  puts "same plan!"
}