Bug #20699 '*' in create view as select * from ... is not dynamic
Submitted: 26 Jun 2006 16:02 Modified: 26 Jun 2006 17:28
Reporter: Andre Timmer Email Updates:
Status: Not a Bug Impact on me:
Category:MySQL Server Severity:S4 (Feature request)
Version:5.0.18 OS:
Assigned to: CPU Architecture:Any

[26 Jun 2006 16:02] Andre Timmer
'*' in create view as select * from ... is not dynamic

How to repeat:
create table test(col1 int);
create or replace view v_test as select * from test;

alter table test add column col2 int;

select col2 from v_test;

ERROR 1054 (42S22): Unknown column 'col2' in 'field list'

Suggested fix:
Make it dynamic.

Or when a runtime error occurs recreate the view using it's original definition.
[26 Jun 2006 17:01] MySQL Verification Team
Thank you for the bug report. Sorry but this isn't a bug in the create
view the column_list is compound up with the all columns existing in
the base table when using '*' in the SQL statement and won't includes
new columns added to the base table after this:

mysql> show create view v_test\G
*************************** 1. row ***************************
       View: v_test
SECURITY DEFINER VIEW `v_test` AS select `test`.`col1` AS `col1` from `test`
[26 Jun 2006 17:13] Andre Timmer
Yes i also noted that the columns are listed when using 'show create view ..'.
It probably is debatable whether MySQL should try at least one recompile when situation described occurs. For this programmer the 'principle of least astonishment' was not met.

Should we make this a feature request?
[26 Jun 2006 17:28] Andre Timmer
Changed severity to S4.