bk commit into 5.0 tree (jimw:1.1945) BUG#6903

[Jim Winstead]

Below is the list of changes that have just been committed into a local
5.0 repository of jimw. When jimw does a push these changes will
be propagated to the main repository and, within 24 hours after the
push, to the public repository.
For information on how to access the public repository
see http://dev.mysql.com/doc/mysql/en/installing-source-tree.html

ChangeSet
  1.1945 05/06/15 16:27:41 jimw@stripped +4 -0
  Fix SHOW CREATE VIEW to handle ANSI_QUOTES mode. (Bug #6903)

  sql/sql_show.cc
    1.251 05/06/15 16:27:37 jimw@stripped +10 -1
    Fix SHOW CREATE VIEW to honor ANSI_QUOTES mode.

  mysql-test/t/sql_mode.test
    1.11 05/06/15 16:27:37 jimw@stripped +34 -0
    Add new regression tests

  mysql-test/r/view.result
    1.82 05/06/15 16:27:37 jimw@stripped +2 -2
    Update results

  mysql-test/r/sql_mode.result
    1.20 05/06/15 16:27:37 jimw@stripped +37 -0
    Update results

# This is a BitKeeper patch.  What follows are the unified diffs for the
# set of deltas contained in the patch.  The rest of the patch, the part
# that BitKeeper cares about, is below these diffs.
# User:	jimw
# Host:	rama.(none)
# Root:	/home/jimw/my/mysql-5.0-6903

--- 1.250/sql/sql_show.cc	2005-06-10 05:34:41 -07:00
+++ 1.251/sql/sql_show.cc	2005-06-15 16:27:37 -07:00
@@ -347,6 +347,9 @@
   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 @@
   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)

--- 1.81/mysql-test/r/view.result	2005-05-17 08:05:06 -07:00
+++ 1.82/mysql-test/r/view.result	2005-06-15 16:27:37 -07:00
@@ -550,7 +550,7 @@
 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 @@
 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;

--- 1.19/mysql-test/r/sql_mode.result	2005-06-09 09:03:12 -07:00
+++ 1.20/mysql-test/r/sql_mode.result	2005-06-15 16:27:37 -07:00
@@ -402,4 +402,41 @@
 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;

--- 1.10/mysql-test/t/sql_mode.test	2005-06-09 09:03:35 -07:00
+++ 1.11/mysql-test/t/sql_mode.test	2005-06-15 16:27:37 -07:00
@@ -189,4 +189,38 @@
 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;