Bug #81377 | Specifying a name for view's column in CREATE VIEW makes SELECT fail | ||
---|---|---|---|
Submitted: | 11 May 2016 8:50 | Modified: | 1 Dec 2016 14:55 |
Reporter: | Guilhem Bichot | Email Updates: | |
Status: | Closed | Impact on me: | |
Category: | MySQL Server: DDL | Severity: | S3 (Non-critical) |
Version: | 5.7 | OS: | Any |
Assigned to: | CPU Architecture: | Any |
[11 May 2016 8:50]
Guilhem Bichot
[1 Dec 2016 8:39]
Guilhem Bichot
Posted by developer: Here is a testcase for 5.7: create view v1 (toto) as select 1 as foo union select 2 from dual order by foo; show create view v1; | v1 | CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select 1 AS `toto` union select 2 AS `2` order by `foo` | utf8 | utf8_general_ci | select * from v1; ERROR 1054 (42S22): Unknown column 'foo' in 'order clause'
[1 Dec 2016 14:55]
Paul DuBois
Posted by developer: Noted in 8.0.1 changelog. Specifying a name for a view column could cause the stored view definition to be invalid.
[27 Jan 2017 15:52]
Paul DuBois
Posted by developer: Revised changelog entry: The stored definition of a view for which an explicit column name list was provided could be invalid. For example, for this statement: CREATE VIEW v1 (name2) AS SELECT 1 AS name1 UNION SELECT 2 ORDER BY name1; The stored definition looked like this, which is invalid: CREATE VIEW v1 AS SELECT 1 AS name2 UNION SELECT 2 AS 2 ORDER BY name1; Now column names within the stored definition are not replaced by the column name list: CREATE VIEW v1 (name2) AS SELECT 1 AS name1 UNION SELECT 2 AS 2 ORDER BY name1; An incompatibility resulting from this change is that the CREATE VIEW statement can no longer be recreated solely from the INFORMATION_SCHEMA VIEWS table, because the VIEW_DEFINITION value does not show the column name list. Instead, you must also consult the COLUMNS table to get the column name list. Alternatively (and more simply), use SHOW CREATE VIEW.