The trouble ticket is here: https://www.sqlite.org/src/tktview/f2ad7de056ab1dc9200d5c364952ac29d7fb035f
On 5/20/15, Richard Hipp <drh at sqlite.org> wrote: > On 5/19/15, Adam Podstawczy?ski <adam at podstawczynski.com> wrote: >> sqlite> select * from list_of_numbers where astart < 7169319380 and aend >> > >> 7169319380; >> >> Now, the above query is expected to return one record only ? and it does: >> >> But when I add index to the very same table: >> >> CREATE INDEX startingnumber ON list_of_numbers(astart); >> CREATE INDEX endingnumber ON list_of_numbers(aend); >> >> The behavior of the same query becomes unpredictable: >> > > Status report: > > Adam sent me his database file via a private channel and I was able to > determine that the file was corrupt in a way that PRAGMA > integrity_check will not detect. In particular, a column of type INT > contained TEXT values that looked like integers. That should not be > possible. We are still trying to figure out how that happened. > > Adam cleared his immediate problem by doing: > > sqlite3 old.db .dump | sqlite3 new.db > > The script below demonstrates the problem. Notice that this script > intentionally corrupts the schema of the database file using the > "PRAGMA writable_schema=ON" statement followed by an UPDATE on the > sqlite_master table. > > --------------------------------------- > -- CAUTION: Destroys table T1 in database file test.db > -- > .open test.db > DROP TABLE IF EXISTS t1; > CREATE TABLE t1(a, b); > WITH RECURSIVE > c(x) AS (VALUES(10) UNION ALL SELECT x+1 FROM c WHERE x<50) > INSERT INTO t1 SELECT CAST(x AS text), CAST(x+3 AS text) FROM c; > PRAGMA writable_schema=ON; > UPDATE sqlite_master SET sql='CREATE TABLE t1(a INT, b INT)' WHERE > name='t1'; > .open test.db > .print ---- without index ---- > SELECT * FROM t1 WHERE a<40 AND b>40; > .print ---- with index ---- > CREATE INDEX t1a ON t1(a); > CREATE INDEX t1b ON t1(b); > SELECT * FROM t1 WHERE a<40 AND b>40; > ----------------------------------- > > SQLite version 3.8.10.2 is an emergency patch release that fixes the > index corruption problem detected yesterday. See > http://www.sqlite.org/draft for additional information. We are > holding up the release of 3.8.10.2 until we understand this problem as > well. If additional fixes are needed to address the problem seen here > we will add those fixes to the 3.8.10.2 release. > > -- > D. Richard Hipp > drh at sqlite.org > -- D. Richard Hipp drh at sqlite.org
