SQLite

View Ticket
Login
2016-07-14
19:13 Fixed ticket [f68bf685]: ALTER TABLE corrupts DESC indexes plus 5 other changes (artifact: 50bb45fa user: drh)
19:13
Fix ALTER TABLE so that it does not promote the schema version past 3, as that will cause DESC indexes to go corrupt. Ticket [f68bf68513a1c]. (check-in: a7db6e45 user: drh tags: trunk)
18:46 New ticket [f68bf685] ALTER TABLE corrupts DESC indexes. (artifact: 2db52790 user: drh)

Ticket Hash: f68bf68513a1c15ff405c54e39e637a9f8156f7b
Title: ALTER TABLE corrupts DESC indexes
Status: Fixed Type: Code_Defect
Severity: Severe Priority: Immediate
Subsystem: Unknown Resolution: Fixed
Last Modified: 2016-07-14 19:13:34
Version Found In: 3.13.0
User Comments:
drh added on 2016-07-14 18:46:40:

The change to ALTER TABLE in [e1d8ec85546c] can cause indexes to go corrupt. Sample code:

PRAGMA legacy_file_format=ON;
CREATE TABLE t1(a,b,c);
CREATE INDEX t1a ON t1(a DESC);
INSERT INTO t1 VALUES(1,2,3);
INSERT INTO t1 VALUES(2,3,4);
ALTER TABLE t1 ADD COLUMN d;
PRAGMA integrity_check;

If the schema version is initially less than 4, then the DESC keyword on indexes is ignored. If ALTER TABLE then changes the schema version to 4 from a smaller value, then preexisting DESC indexes will be in the wrong order.

Corruption caused by this bug can be fixed by running the REINDEX command.