Bug #92655 The returns of SHOW CREATE VIEW has changed.
Submitted: 3 Oct 2018 12:45 Modified: 3 Jan 12:08
Reporter: Peter Laursen (Basic Quality Contributor) Email Updates:
Status: Won't fix Impact on me:
None 
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
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.
[3 Oct 2018 14:25] Umesh Shastry
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 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.