MySQL Bugs: #43056: View won't be restored correct

Bug #43056	View won't be restored correct
Submitted:	20 Feb 2009 13:11	Modified:	23 Mar 2009 13:30
Reporter:	Susanne Ebrecht	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Administrator	Severity:	S3 (Non-critical)
Version:		OS:	Any
Assigned to:	Mike Lischke	CPU Architecture:	Any

Description:
CREATE VIEW v_t AS SELECT t1.i as t1i, t2.i as t2i, t1.j as t1, t2.j as t2 FROM t1, t2 where t1.i=t2.t1;

Backup of MySQL Administrator make this to:
CREATE ... VIEW `v_t` AS select `t1`.`i` AS `t1i`,`t2`.`i` AS `t2i`,`t1`.`j` AS `t1`,`t2`.`j` AS `t2` from (`t1` join `t2`) where (`t1`.`i` = `t2`.`t1`);

The join syntax is incorrect.
Should be FROM t1 join t2 on (t1.i=t2.t1)

How to repeat:
DROP DATABASE IF EXISTS newbug;
CREATE DATABASE newbug;
USE DATABASE newbug;

DROP TABLE IF EXISTS t1;
DROP TABLE IF EXISTS t2;
DROP VIEW IF EXISTS v_t;

CREATE TABLE t1(i serial, j integer, primary key(i));
CREATE TABLE t2(i serial, j integer, t1 bigint unsigned, primary key(i), foreign key(t1)
references t1(i));

INSERT INTO t1(j) VALUES(rand()*100), (rand()*100), (rand()*100), (rand()*100),
(rand()*100);
INSERT INTO t2(j, t1) VALUES(rand()*100,3), (rand()*100,3), (rand()*100,2),
(rand()*100,5), (rand()*100,1);

CREATE VIEW v_t AS SELECT t1.i as t1i, t2.i as t2i, t1.j as t1, t2.j as t2 FROM t1, t2
where t1.i=t2.t1;

Open Administrator:
Backup database newbug

Open Editor and look into SQL Script close to the end.

Sorry, what's the bug here? The written SQL is exactly what the server returns:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 81
Server version: 5.1.28-rc-community-log MySQL Community Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> create schema newbug;
Query OK, 1 row affected (0.01 sec)

mysql> use newbug;
Database changed
mysql> CREATE TABLE t1(i serial, j integer, primary key(i));
Query OK, 0 rows affected (0.07 sec)

mysql> CREATE TABLE t2(i serial, j integer, t1 bigint unsigned, primary key(i), foreign key(t1)
    -> references t1(i));
Query OK, 0 rows affected (0.03 sec)

mysql> INSERT INTO t1(j) VALUES(rand()*100), (rand()*100), (rand()*100), (rand()*100),
    -> (rand()*100);
Query OK, 5 rows affected (0.00 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> INSERT INTO t2(j, t1) VALUES(rand()*100,3), (rand()*100,3), (rand()*100,2),
    -> (rand()*100,5), (rand()*100,1);
Query OK, 5 rows affected (0.00 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> CREATE VIEW v_t AS SELECT t1.i as t1i, t2.i as t2i, t1.j as t1, t2.j as t2 FROM t1, t2
    -> where t1.i=t2.t1;
Query OK, 0 rows affected (0.00 sec)

mysql> show create table v_t;
+------+----------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------+-------------
---------+----------------------+
| View | Create View
                                                                                                          | character_se
t_client | collation_connection |
+------+----------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------+-------------
---------+----------------------+
| v_t  | CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v_t` AS select `t1`.`i` AS `t1
i`,`t2`.`i` AS `t2i`,`t1`.`j` AS `t1`,`t2`.`j` AS `t2` from (`t1` join `t2`) where (`t1`.`i` = `t2`.`t1`) | utf8
         | utf8_general_ci      |
+------+----------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------+-------------
---------+----------------------+
1 row in set (0.00 sec)

mysql>

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".