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:
None 
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
Description:
It likely exists in older versions.

create view v1 (toto) as select 1 as foo group by foo union select 2 order by foo;
show create view v1;
| v1   | CREATE ALGORITHM=UNDEFINED DEFINER=`skip-grants user`@`skip-grants host` SQL SECURITY DEFINER VIEW `v1` AS select 1 AS `toto` group by `toto` union select 2 AS `2` order by `foo` 

Observe how "toto" has overwritten "foo" in the AS clause of "1" (1 AS toto); but then the "ORDER BY foo" won't know what "foo" is, and:

select * from v1;
ERROR 1054 (42S22): Unknown column 'foo' in 'order clause'

How to repeat:
create view v1 (toto) as select 1 as foo group by foo union select 2 order by foo;
select * from v1;

Suggested fix:
I have a fix in my wl#883 tree: explicit column names as "toto" musn't overwrite expression aliases; the latter must be kept; explicit column names must be in a separate structure, and be stored in a new column in the new Data Dictionary of 5.8.
[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.