SQLite Forum

Apparent bug in ALTER TABLE
Login
IMHO this is definitely a bug.

It introduced an unnecessary and undocumented interaction between processes working in entirely different parts of the database. Suppose for example that process A is doing the sequence described in section 5 of the ALTER TABLE documentation on page <https://www.sqlite.org/lang_altertable.html>. Between steps 6 and 7 any view that referenced table X now refers to a nonexistent table. If process B (perhaps belonging to a different user) attempts to do an ALTER TABLE in another part of the database at this point, it will get an error.

In particular if process A is actually being driven by a human running through the sequence (rather than a program), there may be quite a long time between steps 6 and 7.

Of course it is possible for process A to use an EXCLUSIVE transaction, but this is not *required* and would adversely affect throughput since it prevents other database connections from reading the database if not in WAL mode. It would also be possible for process A to drop all views that reference table X before doing step 6, but this is not specified, and the possibility does not change the fact that this is a perfectly valid sequence of sqlite statements which, after the change to ALTER TABLE, has an unexpected effect on another otherwise valid statement.

Why was it considered necessary to do this test? Such a test is not performed for DROP TABLE (where it might be more logical) or CREATE TABLE. If a test was needed for the existence of views referencing nonexistent tables, why not make a PRAGMA view_check by analogy with PRAGMA foreign_key_check instead of introducing it into ALTER TABLE where IMHO it does not belong? Of course ALTER TABLE has to scan the schema in order to detect whether a table or a view with the new name already exists, but it only *needs* to fail if a table or a view with the new name does already exist.

Incidentally dan's comment is not quite correct. The error is only generated if the ALTER TABLE statement renames a column or a table, not if it adds a column.

For example:-<p>
`-- Script started`<p>
`PRAGMA legacy_alter_table = 0 ;`<p>
`-- No error`<p>
`--`<p>
`DROP TABLE IF EXISTS table1 ;`<p>
`-- No error`<p>
`--`<p>
`DROP TABLE IF EXISTS table2 ;`<p>
`-- No error`<p>
`--`<p>
`CREATE TABLE table1 (t1column1 t1column2) ;`<p>
`-- No error`<p>
`--`<p>
`CREATE TABLE table2 (t2column1 t2column2) ;`<p>
`-- No error`<p>
`--`<p>
`CREATE VIEW view1 AS SELECT t1column1 FROM table1 ;`<p>
`-- No error`<p>
`--`<p>
`DROP TABLE table1 ;`<p>
`-- No error`<p>
`--`<p>
`ALTER TABLE table2 ADD COLUMN newcolumn3 ;`<p>
`-- No error`<p>
`--`<p>
`ALTER TABLE table2 RENAME COLUMN newcolumn3 TO t2column3 ;`<p>
`-- Error: error in view view1: no such table: main.table1 Unable to fetch row.`<p>