SQLite
View Ticket
Not logged in
Ticket UUID: 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: (text/x-fossil-wiki)
The change to ALTER TABLE in [e1d8ec85546c] can cause indexes to go corrupt.
Sample code:

<blockquote><verbatim>
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;
</verbatim></blockquote>

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.