SQLite User Forum

DROP COLUMN on indexed column doesn't raise an error
Login

DROP COLUMN on indexed column doesn't raise an error

(1) By ylecuyer on 2021-03-13 22:14:02 [link]

Hello,

I was trying the new drop column introduced in 3.35.00 to have the native way in rails active records: https://github.com/rails/rails/pull/41671

One of the test is failing (one that should be passing) and here is a minimal repro just using sqlite:

```
ylecuyer@inwin:~/Projects/sqlite-src$ ./sqlite-src-3350000/sqlite3 --version
3.35.0 2021-03-12 15:10:09 acd63062eb06748bfe9e4886639e4f2b54ea6a496a83f10716abbaba4115500b

```

```
ylecuyer@inwin:~/Projects/sqlite-src$ ./sqlite-src-3350000/sqlite3 
SQLite version 3.35.0 2021-03-12 15:10:09
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> CREATE TABLE 'test_models' ('x y', 'z 1', 'a b');
sqlite> ALTER TABLE 'test_models' ADD 'hat_name' varchar;
sqlite> CREATE INDEX "index_test_models_on_hat_name" ON "test_models" ('hat_name');
sqlite> .schema test_models
CREATE TABLE IF NOT EXISTS 'test_models' ('x y', 'z 1', 'a b', 'hat_name' varchar);
CREATE INDEX "index_test_models_on_hat_name" ON "test_models" ('hat_name');
sqlite> ALTER TABLE "test_models" DROP COLUMN 'hat_name';
Error: error in index index_test_models_on_hat_name after drop column: no such column: hat_name
sqlite> 
```

```
ylecuyer@inwin:~/Projects/sqlite-src$ ./sqlite-src-3350000/sqlite3 
SQLite version 3.35.0 2021-03-12 15:10:09
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> CREATE TABLE 'test_models' ('x y', 'z 1', 'a b');
sqlite> ALTER TABLE 'test_models' ADD 'hat_name' varchar;
sqlite> CREATE INDEX "index_test_models_on_hat_name" ON 'test_models' ("hat_name");
sqlite> .schema test_models
CREATE TABLE IF NOT EXISTS 'test_models' ('x y', 'z 1', 'a b', 'hat_name' varchar);
CREATE INDEX "index_test_models_on_hat_name" ON 'test_models' ("hat_name");
sqlite> ALTER TABLE "test_models" DROP COLUMN "hat_name";
sqlite>
```

the first one is raising whereas the second one is not

Please tell me if you need more examples

Regards

(2) By Larry Brasfield (larrybr) on 2021-03-13 23:00:14 in reply to 1

I can reproduce your bug even with this less perplexing DDL:<code>
  CREATE TABLE "test_models" ("x y", "z 1", "a b");
  ALTER TABLE "test_models" ADD "hat_name" varchar;
  CREATE INDEX "index_test_models_on_hat_name" ON "test_models" ("hat_name");
  ALTER TABLE "test_models" DROP COLUMN "hat_name";
</code>At that point, .schema shows:<code>
CREATE TABLE IF NOT EXISTS "test_models" ("x y", "z 1", "a b");
CREATE INDEX "index_test_models_on_hat_name" ON "test_models" ("hat_name");
</code>, which is clearly nonsensical.

(3) By Richard Hipp (drh) on 2021-03-14 01:21:39 in reply to 1 [link]

See [ticket 1c24a659e6d7f3a1](src:/info/1c24a659e6d7f3a1).