diff options
| -rw-r--r-- | mysql-test/r/sql_mode.result | 37 | ||||
| -rw-r--r-- | mysql-test/r/view.result | 4 | ||||
| -rw-r--r-- | mysql-test/t/sql_mode.test | 34 | ||||
| -rw-r--r-- | sql/sql_show.cc | 11 |
4 files changed, 83 insertions, 3 deletions
diff --git a/mysql-test/r/sql_mode.result b/mysql-test/r/sql_mode.result index c5ba4d15a50..320919198cd 100644 --- a/mysql-test/r/sql_mode.result +++ b/mysql-test/r/sql_mode.result @@ -402,4 +402,41 @@ a\b a\"b a'\b a'\"b SELECT "a\\b", "a\\\'b", "a""\\b", "a""\\\'b"; a\b a\'b a"\b a"\'b a\b a\'b a"\b a"\'b +SET @@SQL_MODE=''; +create function `foo` () returns int return 5; +show create function `foo`; +Function sql_mode Create Function +foo CREATE FUNCTION `test`.`foo`() RETURNS int(11) +return 5 +SET @@SQL_MODE='ANSI_QUOTES'; +show create function `foo`; +Function sql_mode Create Function +foo CREATE FUNCTION `test`.`foo`() RETURNS int(11) +return 5 +drop function `foo`; +create function `foo` () returns int return 5; +show create function `foo`; +Function sql_mode Create Function +foo ANSI_QUOTES CREATE FUNCTION "test"."foo"() RETURNS int(11) +return 5 +SET @@SQL_MODE=''; +show create function `foo`; +Function sql_mode Create Function +foo ANSI_QUOTES CREATE FUNCTION "test"."foo"() RETURNS int(11) +return 5 +drop function `foo`; +SET @@SQL_MODE=''; +create table t1 (a int); +create table t2 (a int); +create view v1 as select a from t1; +show create view v1; +View Create View +v1 CREATE ALGORITHM=UNDEFINED VIEW `test`.`v1` AS select `test`.`t1`.`a` AS `a` from `test`.`t1` +SET @@SQL_MODE='ANSI_QUOTES'; +show create view v1; +View Create View +v1 CREATE ALGORITHM=UNDEFINED VIEW "test"."v1" AS select "test"."t1"."a" AS "a" from "test"."t1" +create view v2 as select a from t2 where a in (select a from v1); +drop view v2, v1; +drop table t1, t2; SET @@SQL_MODE=@OLD_SQL_MODE; diff --git a/mysql-test/r/view.result b/mysql-test/r/view.result index 84be086ae37..cc93449bd1f 100644 --- a/mysql-test/r/view.result +++ b/mysql-test/r/view.result @@ -550,7 +550,7 @@ create table t1 ("a*b" int); create view v1 as select "a*b" from t1; show create view v1; View Create View -v1 CREATE VIEW "test"."v1" AS select `test`.`t1`.`a*b` AS `a*b` from `test`.`t1` +v1 CREATE VIEW "test"."v1" AS select "test"."t1"."a*b" AS "a*b" from "test"."t1" drop view v1; drop table t1; set sql_mode=default; @@ -760,7 +760,7 @@ a b 1 1 show create view v3; View Create View -v3 CREATE ALGORITHM=UNDEFINED VIEW `test`.`v3` AS select `v1`.`col1` AS `a`,`v2`.`col1` AS `b` from `test`.`v1` join `test`.`v2` where (`v1`.`col1` = `v2`.`col1`) +v3 CREATE ALGORITHM=UNDEFINED VIEW `test`.`v3` AS select `v1`.`col1` AS `a`,`v2`.`col1` AS `b` from (`test`.`v1` join `test`.`v2`) where (`v1`.`col1` = `v2`.`col1`) drop view v3, v2, v1; drop table t2, t1; create function `f``1` () returns int return 5; diff --git a/mysql-test/t/sql_mode.test b/mysql-test/t/sql_mode.test index 6a0c4aecada..53e9339b73e 100644 --- a/mysql-test/t/sql_mode.test +++ b/mysql-test/t/sql_mode.test @@ -189,4 +189,38 @@ SET @@SQL_MODE=''; SELECT 'a\\b', 'a\\\"b', 'a''\\b', 'a''\\\"b'; SELECT "a\\b", "a\\\'b", "a""\\b", "a""\\\'b"; +# +# Bug #6903: ANSI_QUOTES does not come into play with SHOW CREATE FUNCTION +# or PROCEDURE because it displays the SQL_MODE used to create the routine. +# +SET @@SQL_MODE=''; +create function `foo` () returns int return 5; +show create function `foo`; +SET @@SQL_MODE='ANSI_QUOTES'; +show create function `foo`; +drop function `foo`; + +create function `foo` () returns int return 5; +show create function `foo`; +SET @@SQL_MODE=''; +show create function `foo`; +drop function `foo`; + +# +# Bug #6903: ANSI_QUOTES should have effect for SHOW CREATE VIEW (Bug #6903) +# +SET @@SQL_MODE=''; +create table t1 (a int); +create table t2 (a int); +create view v1 as select a from t1; +show create view v1; +SET @@SQL_MODE='ANSI_QUOTES'; +show create view v1; +# Test a view with a subselect, which will get shown incorrectly without +# thd->lex->view_prepare_mode set properly. +create view v2 as select a from t2 where a in (select a from v1); +drop view v2, v1; +drop table t1, t2; + + SET @@SQL_MODE=@OLD_SQL_MODE; diff --git a/sql/sql_show.cc b/sql/sql_show.cc index 68c6d1a8030..2b433981579 100644 --- a/sql/sql_show.cc +++ b/sql/sql_show.cc @@ -347,6 +347,9 @@ mysqld_show_create(THD *thd, TABLE_LIST *table_list) DBUG_PRINT("enter",("db: %s table: %s",table_list->db, table_list->table_name)); + /* We want to preserve the tree for views. */ + thd->lex->view_prepare_mode= TRUE; + /* Only one table for now, but VIEW can involve several tables */ if (open_normal_and_derived_tables(thd, table_list)) { @@ -1061,7 +1064,13 @@ view_store_create_info(THD *thd, TABLE_LIST *table, String *buff) buff->append('.'); append_identifier(thd, buff, table->view_name.str, table->view_name.length); buff->append(" AS ", 4); - buff->append(table->query.str, table->query.length); + + /* + We can't just use table->query, because our SQL_MODE may trigger + a different syntax, like when ANSI_QUOTES is defined. + */ + table->view->unit.print(buff); + if (table->with_check != VIEW_CHECK_NONE) { if (table->with_check == VIEW_CHECK_LOCAL) |