bk commit into 5.0 tree (msvensson:1.1971) BUG#10927

[msvensson]

Below is the list of changes that have just been committed into a local
5.0 repository of msvensson. When msvensson 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.1971 05/06/16 15:58:17 msvensson@neptunus.(none) +3 -0
  BUG#10927 mysqldump: Can't reload dump with view that consist of other view
  - Create a small dummy table that will take care of the problem of creating a view
dependent of another view which hasn't yet been created.

  mysql-test/t/mysqldump.test
    1.39 05/06/16 15:58:13 msvensson@neptunus.(none) +34 -1
    Add tests for bug#10927

  mysql-test/r/mysqldump.result
    1.45 05/06/16 15:58:13 msvensson@neptunus.(none) +32 -1
    Add tests for bug#10927

  client/mysqldump.c
    1.182 05/06/16 15:58:13 msvensson@neptunus.(none) +49 -1
    Create a dummy table for the view. ie. a table  which has the
    same columns as the view should have. This table is dropped
    just before the view is created. The table is used to handle the
    case where a view references another view, which hasn't yet been
    created(during the load of the dump). 

# 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:	msvensson
# Host:	neptunus.(none)
# Root:	/home/msvensson/mysql/bug10927

--- 1.181/client/mysqldump.c	2005-05-26 12:19:36 +02:00
+++ 1.182/client/mysqldump.c	2005-06-16 15:58:13 +02:00
@@ -1212,7 +1212,54 @@
       if (strcmp(field->name, "View") == 0)
       {
         if (verbose)
-          fprintf(stderr, "-- It's a view, skipped\n");
+          fprintf(stderr, "-- It's a view, create dummy table for view\n");
+
+        mysql_free_result(tableRes);
+
+        /* Create a dummy table for the view. ie. a table  which has the
+           same columns as the view should have. This table is dropped
+           just before the view is created. The table is used to handle the
+           case where a view references another view, which hasn't yet been
+           created(during the load of the dump). BUG#10927 */
+
+        /* Create temp table by selecting from the view */
+        my_snprintf(query_buff, sizeof(query_buff),
+                    "create  temporary table %s select * from %s where 1=0",
+                    result_table, result_table);
+        if (mysql_query_with_error_report(sock, 0, query_buff))
+        {
+          safe_exit(EX_MYSQLERR);
+          DBUG_RETURN(0);
+        }
+
+        /* Get CREATE statement for the temp table */
+        my_snprintf(query_buff, sizeof(query_buff), "show create table %s",
+                    result_table);
+        if (mysql_query_with_error_report(sock, 0, query_buff))
+        {
+          safe_exit(EX_MYSQLERR);
+          DBUG_RETURN(0);
+        }
+        tableRes= mysql_store_result(sock);
+        row= mysql_fetch_row(tableRes);
+
+        if (opt_drop)
+          fprintf(sql_file, "DROP VIEW IF EXISTS %s;\n",opt_quoted_table);
+
+        /* Print CREATE statement but remove TEMPORARY */
+        fprintf(sql_file, "CREATE %s;\n", row[1]+17);
+        check_io(sql_file);
+
+        mysql_free_result(tableRes);
+
+        /* Drop the temp table */
+        my_snprintf(buff, sizeof(buff),
+                    "DROP TEMPORARY TABLE %s", result_table);
+        if (mysql_query_with_error_report(sock, 0, buff))
+        {
+          safe_exit(EX_MYSQLERR);
+          DBUG_RETURN(0);
+        }
         was_views= 1;
         DBUG_RETURN(0);
       }
@@ -2752,6 +2799,7 @@
   }
   if (opt_drop)
   {
+    fprintf(sql_file, "DROP TABLE IF EXISTS %s;\n", opt_quoted_table);
     fprintf(sql_file, "DROP VIEW IF EXISTS %s;\n", opt_quoted_table);
     check_io(sql_file);
   }

--- 1.44/mysql-test/r/mysqldump.result	2005-05-26 12:19:36 +02:00
+++ 1.45/mysql-test/r/mysqldump.result	2005-06-16 15:58:13 +02:00
@@ -1,6 +1,6 @@
 DROP TABLE IF EXISTS t1, `"t"1`, t1aa, t2, t2aa;
 drop database if exists mysqldump_test_db;
-drop view if exists v1;
+drop view if exists v1, v2, v3;
 CREATE TABLE t1(a int);
 INSERT INTO t1 VALUES (1), (2);
 <?xml version="1.0"?>
@@ -379,6 +379,11 @@
 /*!40000 ALTER TABLE `t1` ENABLE KEYS */;
 DROP TABLE IF EXISTS `v1`;
 DROP VIEW IF EXISTS `v1`;
+CREATE TABLE `v1` (
+  `a` bigint(11) default NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1;
+DROP TABLE IF EXISTS `v1`;
+DROP VIEW IF EXISTS `v1`;
 CREATE ALGORITHM=UNDEFINED VIEW `test`.`v1` AS select `test`.`t1`.`a` AS `a` from
`test`.`t1`;
 
 /*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
@@ -1422,3 +1427,29 @@
 /*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
 
 DROP TABLE t1;
+create table t1(a int, b int, c varchar(30));
+insert into t1 values(1, 2, "one"), (2, 4, "two"), (3, 6, "three");
+create view v3 as
+select * from t1;
+create  view v1 as
+select * from v3 where b in (1, 2, 3, 4, 5, 6, 7);
+create  view v2 as
+select v3.a from v3, v1 where v1.a=v3.a and v3.b=3 limit 1;
+drop view v1, v2, v3;
+drop table t1;
+show full tables;
+Tables_in_test	Table_type
+t1	BASE TABLE
+v1	VIEW
+v2	VIEW
+v3	VIEW
+show create view v1;
+View	Create View
+v1	CREATE ALGORITHM=UNDEFINED VIEW `test`.`v1` AS select `test`.`v3`.`a` AS
`a`,`test`.`v3`.`b` AS `b`,`test`.`v3`.`c` AS `c` from `test`.`v3` where (`test`.`v3`.`b`
in (1,2,3,4,5,6,7))
+select * from v1;
+a	b	c
+1	2	one
+2	4	two
+3	6	three
+drop view v1, v2, v3;
+drop table t1;

--- 1.38/mysql-test/t/mysqldump.test	2005-05-26 12:19:38 +02:00
+++ 1.39/mysql-test/t/mysqldump.test	2005-06-16 15:58:13 +02:00
@@ -4,7 +4,7 @@
 --disable_warnings
 DROP TABLE IF EXISTS t1, `"t"1`, t1aa, t2, t2aa;
 drop database if exists mysqldump_test_db;
-drop view if exists v1;
+drop view if exists v1, v2, v3;
 --enable_warnings
 
 # XML output
@@ -563,3 +563,36 @@
 INSERT INTO t1 VALUES (1),(2),(3);
 --exec $MYSQL_DUMP --add-drop-database --skip-comments --databases test
 DROP TABLE t1;
+
+
+#
+# Bug #10927 mysqldump: Can't reload dump with view that consist of other view
+#
+
+create table t1(a int, b int, c varchar(30));
+
+insert into t1 values(1, 2, "one"), (2, 4, "two"), (3, 6, "three");
+
+create view v3 as
+select * from t1;
+
+create  view v1 as
+select * from v3 where b in (1, 2, 3, 4, 5, 6, 7);
+
+create  view v2 as
+select v3.a from v3, v1 where v1.a=v3.a and v3.b=3 limit 1;
+
+--exec $MYSQL_DUMP test > var/tmp/bug10927.sql
+drop view v1, v2, v3;
+drop table t1;
+--exec $MYSQL test < var/tmp/bug10927.sql
+
+# Without dropping the original tables in between
+--exec $MYSQL_DUMP test > var/tmp/bug10927.sql
+--exec $MYSQL test < var/tmp/bug10927.sql
+show full tables;
+show create view v1;
+select * from v1;
+
+drop view v1, v2, v3;
+drop table t1;