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
