SQLite

View Ticket
Login
2021-03-15
14:58 Fixed ticket [1c24a659]: DROP COLUMN leaves behind an index plus 5 other changes (artifact: eccc1437 user: drh)
14:58
Improved detection of conflicting indexes when doing DROP COLUMN on a column whose name is quoted in the index definition. Fix for ticket [1c24a659e6d7f3a1]. (check-in: c450d0ad user: drh tags: trunk)
2021-03-14
19:55
An alternative approach for fixing ticket [1c24a659e6d7f3a1]. (check-in: a2adae90 user: drh tags: tkt-1c24a659-b)
12:01 Ticket [1c24a659] DROP COLUMN leaves behind an index status still Open with 6 other changes (artifact: 1d3d0461 user: drh)
01:40
Never allow a double-quoted identifier that appears by itself in the argument list of an index to be interpreted as a string literal. Proposed fix for ticket [1c24a659e6d7f3a1]. (Closed-Leaf check-in: 7c8aa381 user: drh tags: tkt-1c24a659)
01:20 New ticket [1c24a659] DROP COLUMN leaves behind an index. (artifact: e3f3b036 user: drh)

Ticket Hash: 1c24a659e6d7f3a1b2289bf229ff8e9a1e463f90
Title: DROP COLUMN leaves behind an index
Status: Fixed Type: Code_Defect
Severity: Important Priority: High
Subsystem: Code_Generator Resolution: Fixed
Last Modified: 2021-03-15 14:58:51
Version Found In: 3.35.0
User Comments:
drh added on 2021-03-14 01:20:48:

The DROP COLUMN statement below works, even though the column is indexed:

CREATE TABLE t1(a,b);
INSERT INTO t1 VALUES(1,2);
CREATE INDEX x1 on t1("b");
ALTER TABLE t1 DROP COLUMN b;

Subsequently reopening the database and running "PRAGMA integrity_check" reports index corruption. The "index corruption" problem can be resolved by dropping the (now superfluous) index, so this is perhaps not a serious concern, but it does need to be fixed.

The problem was reported by Forum post e37dcf456a. It is perhaps related to ticket [9b78184be266fd70].


drh added on 2021-03-14 12:01:09:

A slightly different example of this problem that is not resolved by check-in 7c8aa3812599d58a.

CREATE TABLE t1(a, b, c);
CREATE UNIQUE INDEX x1 ON t1("a"||"b");
INSERT INTO t1 VALUES(1,2,3),(1,4,5);
ALTER TABLE t1 DROP COLUMN b;
PRAGMA integrity_check;