Hi there, I was trying to upgrade SQLite version in my C++ application from 3.8.0.2 to 3.8.4.3. Unfortunately after upgrade my application became experience major problems. I tracked down the bug and got an SQL script which gives different result on different versions of SQLite.
Original script is rather big, so I tried to omit unessential things. That's the minimal script: CREATE TABLE items (id INTEGER PRIMARY KEY, key INTEGER NOT NULL, status INTEGER NOT NULL); CREATE UNIQUE INDEX items_key_status ON items (key, status); CREATE INDEX items_status_partial ON items (status) WHERE status = 1; INSERT INTO items (key, status) VALUES (123, 1); UPDATE OR REPLACE items SET status = 0 WHERE id = 1; SELECT COUNT(*) FROM items WHERE status = 1; The last SELECT query should correctly return one number 0. Here the results of different versions of official sqlite3.exe binaries from sqlite.org: sqlite_3.8.2.0.exe <test.sql 0 sqlite_3.8.3.1.exe <test.sql 0 sqlite_3.8.4.0.exe <test.sql 1 sqlite_3.8.4.1.exe <test.sql 1 sqlite_3.8.4.2.exe <test.sql 1 sqlite_3.8.4.3.exe <test.sql 1 So the difference was introduced in 3.8.4.0. Looks like combination of unique index, partial index and UPDATE OR REPLACE (instead of just UPDATE) is essential for this bug. Removing any of these makes sqlite return correct answer. My application uses queries like this, and wrong SELECT causes it to go into infinite loop. After some experimentation on real database I become getting SQLITE_CORRUPT error. `PRAGMA integrity_check` said "wrong number of entries in index". Through I wasn't able to reproduce this corruption again. Hope this helps. Thanks for the great product. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
