Bug #85176 Database name in SHOW CREATE VIEW

• Submitted: 24 Feb 2017 16:17
• Modified: 28 Jul 2017 2:04
• Reporter: Power Gamer
• Status: Closed
• Category: MySQL Server: DDL
• Severity: S2 (Serious)
• Version: 5.7.11, 5.7.17, 5.7.18
• OS: Any
• CPU Architecture: Any
• Tags: VIEW

[24 Feb 2017 16:17] Power Gamer

Description:
SHOW CREATE VIEW in some situations adds a database name prefix before a table name that was NOT present in the original view definition specified in CREATE VIEW. As a result recreating a database with a new name using mysqldump is not possible.

How to repeat:
Example:
CREATE DATABASE db1;
USE db1;
CREATE TABLE tbl1(c1 INT NOT NULL);
CREATE VIEW v1 AS SELECT * FROM tbl1;
CREATE VIEW v2 AS SELECT * FROM (SELECT c1 FROM tbl1) dummy;

SHOW CREATE VIEW v1;
Result for v1 is fine (no database name is added):
CREATE VIEW "v1" AS select "tbl1"."c1" AS "c1" from "tbl1"

SHOW CREATE VIEW v2;
Result for v2 is incorrect (database name "db1" is added before table name "tbl1" in the subquery):
CREATE VIEW "v2" AS select "dummy"."c1" AS "c1" from (select "db1"."tbl1"."c1" AS "c1" from "db1"."tbl1") "dummy"

Trying to recreate db1 database with different name (db2) using mysqldump: 
1) mysqldump.exe db1 > dump.sql
2) DROP DATABASE db1;
3) CREATE DATABASE db2;
4) mysql.exe -D db2 < dump.sql
Step 4 produces error: "ERROR 1146 (42S02) at line 92: Table 'db1.tbl1' doesn't exist".

Suggested fix:
If a table name was not prefixed with database name in CREATE VIEW definition of a view and current database is the same as the one where the view was created then SHOW CREATE VIEW should NOT append database name before such table name.

In case of the example database the statement "SHOW CREATE VIEW v2" should produce:
CREATE VIEW "v2" AS select "dummy"."c1" AS "c1" from (select "tbl1"."c1" AS "c1" from "tbl1") "dummy"

[27 Feb 2017 9:14] MySQL Verification Team

Hello!

Thank you for the report and test case.
Observed this with 5.7.17 build.

Thanks,
Umesh

[6 Jul 2017 7:15] MySQL Verification Team

Bug #86962 marked as duplicate of this one

[28 Jul 2017 2:04] Paul DuBois

Posted by developer:
 
Fixed in 8.0.3.

SHOW CREATE VIEW sometimes added a database name prefix to table
names that was not present in the original view definition.

[14 Apr 2018 5:46] MySQL Verification Team

Bug #90430 marked as duplicate of this one

[12 Dec 2018 9:19] Yuanjun Li

Could you port back to 5.7.x?