| Bug #120496 | MySQL Workbench 8.0 reformat does not support WITH SQL 8.0 WITH (Common Table Expressions) | ||
|---|---|---|---|
| Submitted: | 18 May 19:22 | Modified: | 19 May 13:27 |
| Reporter: | ALAIN delevingne | Email Updates: | |
| Status: | Open | Impact on me: | |
| Category: | MySQL Workbench: SQL Editor | Severity: | S2 (Serious) |
| Version: | OS: | Windows | |
| Assigned to: | CPU Architecture: | Any | |
[19 May 13:27]
ALAIN delevingne
a script below allowing to manage the case .. copy sqlglot folder is necessary to run it C:\Users\xxxx\AppData\Roaming\MySQL\Workbench\modules\sqlglot
# import the wb module
from wb import *
# import the grt module
import grt
# import the mforms module for GUI stuff
import mforms
import sys
import os
sys.path.append(os.path.join(
os.environ["APPDATA"],
"MySQL",
"Workbench",
"modules"
))
import sqlglot
from workbench.log import log_error
ModuleInfo = DefineModule(
name="DAL_FormatSQL",
author="Alain",
version="1.0"
)
@ModuleInfo.plugin(
"dal.format.sql",
caption="Format SQL (pretty)",
input=[wbinputs.currentQueryEditor()],
pluginMenu="SQL/Editor"
)
@ModuleInfo.export(grt.INT, grt.classes.db_query_Editor)
def format_sql(editor):
text = editor.selectedText
selectionOnly = True
if not text:
selectionOnly = False
text = editor.currentStatement
ok_count = 0
bad_count = 0
prev_end = 0
new_text = []
ranges = grt.modules.MysqlSqlFacade.getSqlStatementRanges(text)
for begin, end in ranges:
end = begin + end
if begin > prev_end:
new_text.append(text[prev_end:begin])
statement = text[begin:end]
#
stripped = statement.lstrip(" \t\r\n")
leading = statement[:len(statement) - len(stripped)]
statement = stripped
stripped = statement.rstrip(" \t\r\n")
if stripped != statement:
trailing = statement[-(len(statement) - len(stripped)):]
else:
trailing = ""
statement = stripped
# if there's a comment at the start, then skip the comment until its end
while True:
if statement.startswith("-- "):
comment, _, rest = statement.partition("\n")
leading += comment+"\n"
statement = rest
elif statement.startswith("/*"):
pos = statement.find("*/")
if pos >= 0:
leading += statement[:pos+2]
statement = statement[pos+2:]
else:
break
else:
break
stripped = statement.lstrip(" \t\r\n")
leading += statement[:len(statement) - len(stripped)]
statement = stripped
stripped = statement.rstrip(" \t\r\n")
if stripped != statement:
trailing += statement[-(len(statement) - len(stripped)):]
statement = stripped
try:
result = sqlglot.transpile(text, read="mysql", pretty=True)[0]
except:
import traceback
log_error("Error reformating SQL: %s\n%s\n" % (statement, traceback.format_exc()))
result = None
if result:
ok_count += 1
if leading:
new_text.append(leading.strip(" "))
new_text.append(result)
if trailing:
new_text.append(trailing.strip(" "))
else:
bad_count += 1
new_text.append(text[begin:end])
prev_end = end
new_text.append(text[prev_end:])
new_text = "".join(new_text)
if selectionOnly:
editor.replaceSelection(new_text)
else:
editor.replaceCurrentStatement(new_text)
if bad_count > 0:
mforms.App.get().set_status_text("Formatted %i statements, %i unsupported statement types skipped."%(ok_count, bad_count))
else:
mforms.App.get().set_status_text("Formatted %i statements."%ok_count)
return 0
Description: MySQL Workbench 8.0 reformat does not support WITH SQL 8.0 WITH The following valid SQL requests is not formated : WITH cte1 AS (SELECT bdd_id, bdd_prj_id_fk FROM bdd), cte2 AS (SELECT alerts_user_id, alerts_prj_id_fk FROM alerts) SELECT bdd_id, alerts_user_id FROM cte1 JOIN cte2 WHERE cte1.bdd_prj_id_fk = cte2.alerts_prj_id_fk; A view is displayed in a signe line : CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `new_view` AS with `cte1` as (select `bdd`.`bdd_id` AS `bdd_id`,`bdd`.`bdd_prj_id_fk` AS `bdd_prj_id_fk` from `bdd`), `cte2` as (select `alerts`.`alerts_user_id` AS `alerts_user_id`,`alerts`.`alerts_prj_id_fk` AS `alerts_prj_id_fk` from `alerts`) select `cte1`.`bdd_id` AS `bdd_id`,`cte2`.`alerts_user_id` AS `alerts_user_id` from (`cte1` join `cte2`) where (`cte1`.`bdd_prj_id_fk` = `cte2`.`alerts_prj_id_fk`) Please update the format to allow to support mysql 8.0 syntax. Below the displayed error 1:11:56 [ERR][sqlide_grt.py:enbeautificate:333]: Error reformating SQL: WITH cte1 AS (SELECT bdd_id, bdd_prj_id_fk FROM bdd), cte2 AS (SELECT alerts_user_id, alerts_prj_id_fk FROM alerts) SELECT bdd_id, alerts_user_id FROM cte1 JOIN cte2 WHERE cte1.bdd_prj_id_fk = cte2.alerts_prj_id_fk Traceback (most recent call last): File "C:\Program Files\MySQL\MySQL Workbench 8.0 CE\modules\sqlide_grt.py", line 330, in enbeautificate result = doReformatSQLStatement(statement, True) ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ File "C:\Program Files\MySQL\MySQL Workbench 8.0 CE\modules\sqlide_grt.py", line 233, in doReformatSQLStatement raise Exception("Error parsing statement: %s" % ast_list[0]) Exception: Error parsing statement: syntax error, unexpected WITH How to repeat: try to format a simple request like WITH cte1 AS (SELECT bdd_id, bdd_prj_id_fk FROM bdd), cte2 AS (SELECT alerts_user_id, alerts_prj_id_fk FROM alerts) SELECT bdd_id, alerts_user_id FROM cte1 JOIN cte2 WHERE cte1.bdd_prj_id_fk = cte2.alerts_prj_id_fk; Suggested fix: Please update the format to allow to support mysql 8.0 syntax.