Bug #102614 CTE beautify/format does not work
Submitted: 17 Feb 2:28 Modified: 17 Feb 6:09
Reporter: Devin Homan Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Workbench: SQL Editor Severity:S3 (Non-critical)
Version:8.0.23 OS:Linux (Debian 10.8 x86_64)
Assigned to: CPU Architecture:Any
Tags: WBBugReporter

[17 Feb 2:28] Devin Homan
Description:
MySQL Workbench does not format common table expressions.  This makes it difficult to use CTEs in views because the manual formatting is lost and the automatic formatting does not work.  In the editor, the beautify/format button does not work.  

How to repeat:
Enter a common table expression into the editor without formatting.  This is one from https://dev.mysql.com/doc/refman/8.0/en/with.html

WITH cte (col1, col2) AS(SELECT 1, 2 UNION ALL SELECT 3, 4)SELECT col1, col2 FROM cte;

Click the statement so that the cursor is over it or highlight it. Click the beautify/format button.  Nothing happens.

In the Scripting Shell we get an error when running the parser that runs when the button is clicked:

>>> simple_cte = 'WITH cte (col1, col2) AS(SELECT 1, 2 UNION ALL SELECT 3, 4)SELECT col1, col2 FROM cte;'

>>> sim_cte = grt.modules.MysqlSqlFacade.parseAstFromSqlScript(simple_cte)

>>> sim_cte
<grt.List object ...>

>>> sim_cte[0]
"SQL syntax error near 'WITH cte (col1, col2) AS(SELECT 1, 2 UNION ALL SELECT 3, 4)SELECT col1, col2 FRO'"

Suggested fix:
The Mysql_sql_parser_fe::parse_sql_script method in mysql_sql_parser_fe.cpp needs to be revised.
[17 Feb 6:09] MySQL Verification Team
Hello Devin Homan,

Thank you for the report and feedback.

regards,
Umesh