sql: views do not track their dependencies properly

[knz]

It is possible to break a view by removing a column from the table it depends on:

CREATE TABLE kv(k INT, v INT);
CREATE VIEW v1 AS SELECT k FROM (SELECT k,v FROM kv) AS t;
ALTER TABLE kv DROP COLUMN v;
SELECT * FROM v1; -- fails with `pq: column 2 does not exist`

The problem is that the view tracks dependencies based on "needed columns", which is a result of logical plan optimization. A column that is named by the query but not used by the view results becomes "unneeded" and is thus not tracked as dependency. When it is deleted from the source table, the view plan cannot be constructed any more.

cc @cockroachdb/sql-planning @cockroachdb/sql-async

[knz]

After some brainstorm with @RaduBerinde and @jordanlewis there are multiple solutions. To compare them we need first to establish what the solution must solve:

1. the problem at hand above (columns being dropped)
2. not break views when columns are added
3. enable views using star expansion (e.g. create view v as select * from t) sql: Support view queries with star expansions #10028
4. enable renaming columns/tables safely sql: Support renaming objects depended on by views #10083 sql: make view descriptors depend on table/columns by IDs #15388

Here are the solutions we found so far, I am giving them "small names" for the table below.

• blunt-nochange: stop tracking individual column dependencies. If a view b depends on table a, forbid any changes to a's schema (either dropping, adding or renaming columns)
• blunt-nodrop: ditto blunt-nochange, but allow adding or renaming columns (not dropping). Use table references in view queries to allow renames. This is a subset of the changes in sql: make view descriptors depend on table/columns by IDs #15388.
• recompile-views-sc. Whenever a table's schema is updated, iterate over all the views that depend on it and try to compile their view query. If that fails, reject the schema change.
• plan-to-sql: create a way to generate valid SQL text from a logical plan. Use that in views: after the view plan is prepared during CREATE VIEW, store the plan in the descriptor. Convert the plan back to SQL only in SHOW CREATE VIEW.
• table-refs: replace the table names in the view query by numeric table references. Allow drops by creating fake null columns in stored view queries. This is the approach that would fix the bug in sql: make view descriptors depend on table/columns by IDs #15388
• track-named-columns: during planning, mark columns that are named in the view query with all simplifications disabled - partial evaluation of expressions, render expression nullification etc must be disabled - to ensure that all the names present in the original SQL text of the view are also recognized/marked as being used by this new algorithm. Then use this information to construct the view dependencies.
• edit-ast: during plan simplification, any time an expression is elided from the plan also edit the AST to remove it from there. Whenever a star is expanded, edit the AST with the expansion.

solution \ problem solved	1	2	3	4	Notes	Eng effort
blunt-nochange	yes	yes	yes	yes	Not pleasant for users!	S
recompile-views-sc	yes	yes	no	no		S
blunt-nodrop	yes	yes	no	yes		M
table-refs	yes	yes	yes	yes	Adds complexity in logical plan semantics	M
track-named-columns	yes	yes	yes	yes	Will be less "precise" wrt dep tracking than the current code.	L
edit-ast	yes	yes	yes	yes		XL
plan-to-sql	yes	yes	yes	yes	Some plans may no have any valid SQL repr	XL

I had started as part of the work in #15388 to implement the solution "table-refs" however @jordanlewis is of the opinion we should invest the extra time into solution "track-named-columns" instead because it brings us closer to what pg does without making our logical planning / optimizations more complicated.

[knz]

From a strategic perspective:

• I doubt I can implement the solution "track-named-columns" within the 1.1 timeframe.
• both 1.0 and 1.1 are affected by the bug described above, even if we do not plan to solve sql: Support view queries with star expansions #10028 sql: Support renaming objects depended on by views #10083 sql: make view descriptors depend on table/columns by IDs #15388 in 1.1

Would this suggest we need to implement blunt-nochange or blunt-nodrop in the time being?

[knz]

Note that "not solving this bug" is rather unfortunately not an option: this is a path to make the entire schema of a database unusable (broken views / no way to fix it without cluster downtime).

[knz]

(For reference: "What would postgres do?" - postgres has a plan-to-sql solution.)

[vivekmenezes]

as a work around the user can drop the view and recreate.

DROP view t;
CREATE VIEW v1 AS SELECT k FROM (SELECT k FROM kv) AS t;

The application will see errors until the new view is created.

[knz]

Moving to 1.2 since #17280 alleviates / works around the issue.

[knz]

The original problem mentioned in this issue was actually durably solved by #17280.

What remains now is the opposite problem, filed separately: #29021.