benesch
commented
4 years ago Dis-nominating as a good second issue. There's a conversation to be had first about what the goal of SHOW CREATE VIEW is. I agree it's not exactly easy for humans to consume at the moment, but take a look at what MySQL does:
mysql> create view v1 as select 1;
Query OK, 0 rows affected (0.00 sec)
mysql> create view v2 as select 2;
Query OK, 0 rows affected (0.00 sec)
mysql> create view v as select * from v1 union all select * from v2;
ERROR 1146 (42S02): Table 'd.v1' doesn't exist
mysql> create view v as select * from v1 union all select * from v2;
Query OK, 0 rows affected (0.00 sec)
mysql> show create view v;
+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
| View | Create View | character_set_client | collation_connection |
+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
| v | CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v` AS select `v1`.`1` AS `1` from `v1` union all select `v2`.`2` AS `2` from `v2` | utf8mb4 | utf8mb4_general_ci |
+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
1 row in set (0.00 sec)
MySQL doesn't support schemas, and uses backticks instead of double quotes to quote identifiers, but it is fundamentally doing the same thing we are: canonicalizing the SQL statement. As it stands, the canonicalization makes the statement resilient to new keywords being added to the SQL parser and independent of the setting of the database and search_path session variables. It is going to be very difficult to preserve those properties and be concise.
Currently,
SHOW CREATE VIEWspits out the canonicalized SQL, something like:which is not very useful and can probably be made more readable. At the very least, we could strip out most of the quotes, and the database.schema qualifications.