DROP COLUMN on indexed column doesn't raise an error
(1) By ylecuyer on 2021-03-13 22:14:02 [source]
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 [link] [source]
I can reproduce your bug even with this less perplexing DDL:
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";
At that point, .schema shows:
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");
, which is clearly nonsensical.