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

[17 Feb 2021 2:28] Devin Homan
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 2021 6:09] MySQL Verification Team
Hello Devin Homan,

Thank you for the report and feedback.

[22 Jan 2022 9:17] Graham Charlton
I'm pleased to see that I'm not alone in suffering this problem. Once you press 'Apply' the view becomes a one line string and working with it becomes very difficult. The only solution I have is to place the code in an external editor, where at least you can keep readable copy.
[27 Sep 2022 19:32] Chris A
This should be more critical in terms of MySQL Workbench's other bugs. It basically makes it impossible to use Workbench if your stored procedures or views use WITH statements.
[12 Jul 2023 7:43] IGG t
we're up to 8.0.33, and this still doesn't work.