Fix SHOW CREATE VIEW to handle ANSI_QUOTES mode. (Bug #6903)

mysql-test/r/sql_mode.result:
  Update results
mysql-test/r/view.result:
  Update results
mysql-test/t/sql_mode.test:
  Add new regression tests
sql/sql_show.cc:
  Fix SHOW CREATE VIEW to honor ANSI_QUOTES mode.

3 files changed, 73 insertions, 2 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;