sql: Support view queries with star expansions #10028
Description
Copied over from #9921 (comment)
We currently don't expand out star selectors before persisting a new view's query. If a dependent table has columns added to it, that can break later queries from the view depending on how that dependent table was used.
It's kind of a fluke, but a simple star select works just fine as columns are added because the view descriptor only lists the original columns as its ResultColumns:
root@:26257> create table star (a int primary key, b int);
CREATE TABLE
root@:26257> create view v as select * from star;
CREATE VIEW
root@:26257> insert into star values (1, 1), (2, 2);
INSERT 2
root@:26257> select * from star;
+---+---+
| a | b |
+---+---+
| 1 | 1 |
| 2 | 2 |
+---+---+
(2 rows)
root@:26257> SELECT * FROM v;
+---+---+
| a | b |
+---+---+
| 1 | 1 |
| 2 | 2 |
+---+---+
(2 rows)
root@:26257> alter table star add column c int;
ALTER TABLE
root@:26257> SELECT * FROM star;
+---+---+------+
| a | b | c |
+---+---+------+
| 1 | 1 | NULL |
| 2 | 2 | NULL |
+---+---+------+
(2 rows)
root@:26257> SELECT * FROM v;
+---+---+
| a | b |
+---+---+
| 1 | 1 |
| 2 | 2 |
+---+---+
(2 rows)
On the other hand, if the * isn't at the end of the query, it can break the view as columns are added:
root@:26257> create table t (a int primary key, b int);
CREATE TABLE
root@:26257> insert into t values (1, 1), (2, 2);
INSERT 2
root@:26257> CREATE VIEW tv AS SELECT t1.*, t2.b AS t2b FROM t AS t1 JOIN t AS t2 ON t1.a = t2.a;
CREATE VIEW
root@:26257> select * from tv;
+---+---+-----+
| a | b | t2b |
+---+---+-----+
| 1 | 1 | 1 |
| 2 | 2 | 2 |
+---+---+-----+
(2 rows)
root@:26257> ALTER TABLE t ADD COLUMN c INT;
ALTER TABLE
root@:26257> SELECT t1.*, t2.b AS t2b FROM t AS t1 JOIN t AS t2 ON t1.a = t2.a;
+---+---+------+-----+
| a | b | c | t2b |
+---+---+------+-----+
| 1 | 1 | NULL | 1 |
| 2 | 2 | NULL | 2 |
+---+---+------+-----+
(2 rows)
root@:26257> SELECT * FROM tv;
+---+---+------+
| a | b | t2b |
+---+---+------+
| 1 | 1 | NULL |
| 2 | 2 | NULL |
+---+---+------+
(2 rows)
The logic for expanding stars appears to be a little broader in scope than the logic for normalizing table names, so I'm not sure if there's as well-contained of a fix for it as for #9921 (beyond moving to a semantic representation, of course).
Is this the sort of thing that we should block the rollout of views for? Is it ok to leave as a known issue? Is there a fix that could reasonably be put in place for it?
Epic CRDB-2410
Jira issue: CRDB-6144
Metadata
Metadata
Assignees
Labels
Type
Projects
Status