MySQL Bugs: #20699: '*' in create view as select * from ... is not dynamic

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:	None
Category:	MySQL Server	Severity:	S4 (Feature request)
Version:	5.0.18	OS:
Assigned to:		CPU Architecture:	Any

Description:
'*' 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.

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
Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL 
SECURITY DEFINER VIEW `v_test` AS select `test`.`col1` AS `col1` from `test`

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?

Changed severity to S4.