Bug #86962 Diverging show create view with subquery

• Submitted: 5 Jul 2017 20:23
• Modified: 6 Jul 2017 7:14
• Reporter: Rafael S. Suguiura drock
• Status: Duplicate
• Category: MySQL Server: DDL
• Severity: S3 (Non-critical)
• Version: 5.7.18
• OS: Linux
• CPU Architecture: Any
• Tags: mysqldump, subquery, VIEW

[5 Jul 2017 20:23] Rafael S. Suguiura drock

Description:
If a view doesn't use a subquery, its identifier in dump (using mysqldump) is composed of the view name only.

However, if a view does use a subquery, its identifies is composed of the schema name AND the view name.

How to repeat:
use test;

-- base views
create view a as select 1 as a_id;
create view b as select 1 as b_id;
create view c as select * from b;

-- case views
create view v as select * from a join b;
create view u as select * from a join (select * from b) bb;
create view w as select * from a join c;

-- results (simplified for reading)
-- I suppose mysqldump uses "show create view"
show create view v; -- CREATE VIEW `v` AS select `a`.`a_id` AS `a_id`,`b`.`b_id` AS `b_id` from (`a` join `b`)
show create view u; -- CREATE VIEW `u` AS select `a`.`a_id` AS `a_id`,`bb`.`b_id` AS `b_id` from (`test`.`a` join (select `b`.`b_id` AS `b_id` from `test`.`b`) `bb`)
show create view w; -- CREATE VIEW `w` AS select `a`.`a_id` AS `a_id`,`c`.`b_id` AS `b_id` from (`a` join `c`)

Suggested fix:
As opposed to mysqlpump, which always dumps identifiers with schema name, I think mysqldump should NOT contain the schema name.

[6 Jul 2017 7:14] MySQL Verification Team

Hello Rafael S,

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

Thanks,
Umesh