diff options
| author | Davi Arnaut <Davi.Arnaut@Sun.COM> | 2010-03-09 07:36:26 -0300 |
|---|---|---|
| committer | Davi Arnaut <Davi.Arnaut@Sun.COM> | 2010-03-09 07:36:26 -0300 |
| commit | f502deac11a36bd070c551725bb0bbb29829e01f (patch) | |
| tree | 97b0315e4505cd5a4fa82e3fc213165b7b71e020 /mysql-test/r/view.result | |
| parent | 02ac873ccfb5deb46533aefd35640a95f51d1c67 (diff) | |
| download | mariadb-git-f502deac11a36bd070c551725bb0bbb29829e01f.tar.gz | |
Bug#40277: SHOW CREATE VIEW returns invalid SQL
The problem is that not all column names retrieved from a SELECT
statement can be used as view column names due to length and format
restrictions. The server failed to properly check the conformity
of those automatically generated column names before storing the
final view definition on disk.
Since columns retrieved from a SELECT statement can be anything
ranging from functions to constants values of any format and length,
the solution is to rewrite to a pre-defined format any names that
are not acceptable as a view column name.
The name is rewritten to "Name_exp_%u" where %u translates to the
position of the column. To avoid this conversion scheme, define
explict names for the view columns via the column_list clause.
Also, aliases are now only generated for top level statements.
mysql-test/include/view_alias.inc:
Add test case for Bug#40277
mysql-test/r/compare.result:
Bug#40277: SHOW CREATE VIEW returns invalid SQL
mysql-test/r/group_by.result:
Bug#40277: SHOW CREATE VIEW returns invalid SQL
mysql-test/r/ps.result:
Bug#40277: SHOW CREATE VIEW returns invalid SQL
mysql-test/r/subselect.result:
Bug#40277: SHOW CREATE VIEW returns invalid SQL
mysql-test/r/subselect3.result:
Bug#40277: SHOW CREATE VIEW returns invalid SQL
mysql-test/r/type_datetime.result:
Bug#40277: SHOW CREATE VIEW returns invalid SQL
mysql-test/r/union.result:
Bug#40277: SHOW CREATE VIEW returns invalid SQL
mysql-test/r/view.result:
Add test case result for Bug#40277
mysql-test/r/view_alias.result:
Add test case result for Bug#40277
mysql-test/t/view_alias.test:
Add test case for Bug#40277
sql/sql_view.cc:
Check if auto generated column names are conforming. Also, the
make_unique_view_field_name function is not used as it uses the
original name to construct a new one, which does not work if the
name is invalid.
Diffstat (limited to 'mysql-test/r/view.result')
| -rw-r--r-- | mysql-test/r/view.result | 4 |
1 files changed, 2 insertions, 2 deletions
diff --git a/mysql-test/r/view.result b/mysql-test/r/view.result index f12c9d6a31d..fa6f1939592 100644 --- a/mysql-test/r/view.result +++ b/mysql-test/r/view.result @@ -717,7 +717,7 @@ create view v1 as select a from t1; create view v2 as select a from t2 where a in (select a from v1); show create view v2; View Create View character_set_client collation_connection -v2 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v2` AS select `t2`.`a` AS `a` from `t2` where `t2`.`a` in (select `v1`.`a` AS `a` from `v1`) latin1 latin1_swedish_ci +v2 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v2` AS select `t2`.`a` AS `a` from `t2` where `t2`.`a` in (select `v1`.`a` from `v1`) latin1 latin1_swedish_ci drop view v2, v1; drop table t1, t2; CREATE VIEW `v 1` AS select 5 AS `5`; @@ -2982,7 +2982,7 @@ SHOW WARNINGS; Level Code Message SHOW CREATE VIEW v1; View Create View character_set_client collation_connection -v1 CREATE ALGORITHM=MERGE DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `t1`.`pk` AS `pk` from (`t1` join `t2` on(((`t2`.`fk` = `t1`.`pk`) and (`t2`.`ver` = (select max(`t`.`ver`) AS `MAX(t.ver)` from `t2` `t` where (`t`.`org` = `t2`.`org`)))))) latin1 latin1_swedish_ci +v1 CREATE ALGORITHM=MERGE DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `t1`.`pk` AS `pk` from (`t1` join `t2` on(((`t2`.`fk` = `t1`.`pk`) and (`t2`.`ver` = (select max(`t`.`ver`) from `t2` `t` where (`t`.`org` = `t2`.`org`)))))) latin1 latin1_swedish_ci DROP VIEW v1; DROP TABLE t1, t2; DROP FUNCTION IF EXISTS f1; |