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

[20 Feb 2009 13:11] Susanne Ebrecht
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.
[23 Feb 2009 13:30] Mike Lischke
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>
[24 Mar 2009 0:00] Bugs System
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".

Portions of this website are copyright © 2001, 2002 The PHP Group

Page generated in 0.051 sec. using MySQL 8.4.7-u6-cloud

Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.