| 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 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.
Description: Sometimes exactly the same VIEW displays differnt in MySQL 8 compared to previous versions. How to repeat: -- Issue: Sometimes excatly the same VIEW displays differnt in MySQL 8 compared to previous versions -- How to reproduce: -- Requirement: have a fresh (structure-only is enough) sakila database and execute the 3 statements below in both MySQL 5.7 and 8.0 (8.0.11+ is required). USE sakila; CREATE VIEW `sakila`.`vw_cust` AS (SELECT CASE WHEN (`c`.`customer_id` = 1) THEN 'new' ELSE 'other' END AS `random`,c.* FROM customer c JOIN address a ON c.address_id=a.address_id WHERE c.customer_id >100); SHOW CREATE VIEW viewname: -- and notice that the use of paranthesis'es in the SHOW statements returned is not identical between the 2 server versions). There is a paranthesis around the SELECT defining the content of the VIEW in MySQL 8, that did not occur earlier. In the example it is triggered by a JOIN, but there may be more cases. Both SHOW CREATE VIEW statements, however, are valid and execute on any version. -- but tools,scripts etc. that compare VIEWs using SHOW CREATE will see that the VIEWS are different. Such tools exist (included with several GUI tools for instance) and they will most often "normalize whitespace" only before comparing. They cannot handle this paranthesis problem well, as this would require some kind of SQL-parser functionality/capability. -- I cannot see any reason for this (except for carelessness! :-) ) Suggested fix: Please revert how VIEW definition is disaplayed to what it was before 8.0.11.