Bug #90430 mysqldump exports fully qualified tables in views that have a subquery join
Submitted: 13 Apr 2018 13:39 Modified: 14 Apr 2018 5:46
Reporter: Josh X Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server: DDL Severity:S3 (Non-critical)
Version:server 5.7.21 OS:Linux
Assigned to: CPU Architecture:Any

[13 Apr 2018 13:39] Josh X 
Description:
mysqldump of views that have a subquery join have the table names fully qualified even if not created as such

How to repeat:
Run the following queries;

CREATE TABLE IF NOT EXISTS tmp1 (
  id     INT(11) NOT NULL AUTO_INCREMENT,
  field1 VARCHAR(10)      DEFAULT NULL,
  PRIMARY KEY (id)
);

DROP VIEW IF EXISTS view1;
CREATE VIEW view1 AS
  SELECT *
  FROM tmp1;

DROP VIEW IF EXISTS view2;
CREATE VIEW view2 AS
  SELECT tmp1.*
  FROM tmp1
    INNER JOIN (
                 SELECT *
                 FROM tmp1
                 WHERE id < 10
               ) AS tmp1_x ON tmp1.id = tmp1_x.id;

Then do a mysqldump including create schema, in the dump.sql view2 is fully qualified with the schema whereas view1 is not.
[14 Apr 2018 5:46] MySQL Verification Team 
Hello Josh X,

Thank you for the report and test case.
This is duplicate of Bug #85176, please see Bug #85176

Thanks,
Umesh
[14 Apr 2018 5:46] MySQL Verification Team 
Also - Bug #86962