Bug #92655 | The returns of SHOW CREATE VIEW has changed. | ||
---|---|---|---|
Submitted: | 3 Oct 2018 12:45 | Modified: | 3 Jan 2019 12:08 |
Reporter: | Peter Laursen (Basic Quality Contributor) | Email Updates: | |
Status: | Won't fix | Impact on me: | |
Category: | MySQL Server: Information schema | Severity: | S3 (Non-critical) |
Version: | 8.0.11+, 8.0.0-dmr, 8.0.12 | OS: | Any |
Assigned to: | CPU Architecture: | Any |
[3 Oct 2018 12:45]
Peter Laursen
[3 Oct 2018 14:25]
MySQL Verification Team
Hello Peter, Thank you for the report. I'm not sure whether this change was intended but I can replay both without any issues but as you mentioned there is a change when compared to 5.7 and 8.0. -- Import sakila schema only before attempting to create view from report -- 5.7.23 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `vw_cust` AS (select (case when (`c`.`customer_id` = 1) then 'new' else 'other' end) AS `random`,`c`.`customer_id` AS `customer_id`,`c`.`store_id` AS `store_id`,`c`.`first_name` AS `first_name`,`c`.`last_name` AS `last_name`,`c`.`email` AS `email`,`c`.`address_id` AS `address_id`,`c`.`active` AS `active`,`c`.`create_date` AS `create_date`,`c`.`last_update` AS `last_update` from (`customer` `c` join `address` `a` on((`c`.`address_id` = `a`.`address_id`))) where (`c`.`customer_id` > 100)) -- 8.0.12 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `vw_cust` AS select (case when (`c`.`customer_id` = 1) then 'new' else 'other' end) AS `random`,`c`.`customer_id` AS `customer_id`,`c`.`store_id` AS `store_id`,`c`.`first_name` AS `first_name`,`c`.`last_name` AS `last_name`,`c`.`email` AS `email`,`c`.`address_id` AS `address_id`,`c`.`active` AS `active`,`c`.`create_date` AS `create_date`,`c`.`last_update` AS `last_update` from (`customer` `c` join `address` `a` on((`c`.`address_id` = `a`.`address_id`))) where (`c`.`customer_id` > 100) -- 8.0.4 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `vw_cust` AS select (case when (`c`.`customer_id` = 1) then 'new' else 'other' end) AS `random`,`c`.`customer_id` AS `customer_id`,`c`.`store_id` AS `store_id`,`c`.`first_name` AS `first_name`,`c`.`last_name` AS `last_name`,`c`.`email` AS `email`,`c`.`address_id` AS `address_id`,`c`.`active` AS `active`,`c`.`create_date` AS `create_date`,`c`.`last_update` AS `last_update` from (`customer` `c` join `address` `a` on((`c`.`address_id` = `a`.`address_id`))) where (`c`.`customer_id` > 100) -- 8.0.0-dmr CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `vw_cust` AS select (case when (`c`.`customer_id` = 1) then 'new' else 'other' end) AS `random`,`c`.`customer_id` AS `customer_id`,`c`.`store_id` AS `store_id`,`c`.`first_name` AS `first_name`,`c`.`last_name` AS `last_name`,`c`.`email` AS `email`,`c`.`address_id` AS `address_id`,`c`.`active` AS `active`,`c`.`create_date` AS `create_date`,`c`.`last_update` AS `last_update` from (`customer` `c` join `address` `a` on((`c`.`address_id` = `a`.`address_id`))) where (`c`.`customer_id` > 100) regards, Umesh
[7 Nov 2018 0:04]
Dag Wanvik
Posted by developer: This is probably a side effect of WL#8083, which changed the parser w.r.t. representing select/union syntax. The extra parentheses seen in 5.7 formatting is no longer present in 8.x, since the select is no longer wrapped in a PT_select_paren class.
[3 Jan 2019 12:08]
Erlend Dahl
Posted by developer (Martin Hansson): We are doing a lot of continuous work on the parser. The output of SHOW CREATE cannot be expected to possess this level of stability between releases, unfortunately.