On 11/25/18, Balaji Ramanathan <balaji.ramanat...@gmail.com> wrote: > I expected the Alter Table command to find and replace all occurrences of > that column name in my schema with the new name.
Here is simplified SQL that illustrates the problem: CREATE TABLE t1(x); CREATE VIEW v1 AS SELECT x FROM t1; CREATE VIEW v2 AS SELECT x FROM v1; ALTER TABLE t1 RENAME x TO y; The ALTER TABLE fails because after changing the name of t1.x to t1.y, the definition of the view v2 is no longer valid. This is not something we intend to "fix" in SQLite. The root of the problem is that the column names for the v1 view are not specified. And since they are not specified, that means SQLite is free to pick whatever arbitrary names it wants for those columns. The definition of view v2 depends on one particular algorithm for picking the column names of view v1, but there are no guarantees that every version of SQLite will use that particular algorithm. Hence, the definition of view v2 is under-specified and prone to failure, such as in this case. If you are careful to defined the names of all columns within your views, then the problem goes away. For example: CREATE TABLE t1(x); CREATE VIEW v1(y) AS SELECT x FROM t1; CREATE VIEW v2(z) AS SELECT y FROM v1; ALTER TABLE t1 RENAME x TO y; Or: CREATE TABLE t1(x); CREATE VIEW v1 AS SELECT x AS y FROM t1; CREATE VIEW v2 AS SELECT y AS z FROM v1; ALTER TABLE t1 RENAME x TO y; If you do not specify the names of columns in views, then SQLite is free to choose whatever names it wants for those columns, and the choices might shift after an ALTER TABLE, which could then break queries and/or downstream views. So it is best not to do that. Admittedly, this is not well-documented. I will strive to improve the documentation for the next release. Perhaps I will also add a "warning" mechanism to alert programmers to gotchas like this in some subsequent release, though there probably is not time to get warnings in to the forthcoming 3.26.0 release. -- D. Richard Hipp d...@sqlite.org _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
