Description:
The Beautify action in the mySQL Workbench Editor removes double backslashes (one backslash for escape, one backslash as the regexp token prefix)
This only happens with some queries, where some retain their backslash escapes and others lose them on successive CTRL-B (Beautify) actions.
How to repeat:
1. ENter following query into a blank window in Workbench
SELECT 'ART sending ^V[12]\\d{5}$ id for V9 ID', COUNT(allX.PID) -- u.LOGIN, allX.*
FROM xls.`all2` allX
LEFT OUTER JOIN `art`.`pts_data_extract` sep ON sep.`PID` = allX.`PID`
AND sep.`End` = '2899-12-31'
LEFT OUTER JOIN `s`.`users_all_output` u ON u.PID = allx.`PID`
WHERE `ART_UID` REGEXP('^V[12]\\d{5}')
AND `NEW_UID` REGEXP('V9\\d{5}$')
AND `TYPE` NOT REGEXP('DELETE')
2. Press CTRL+B to beautify the query
3. Observe that the double backslash in the REGEXP portion of the WHERE clause has been reduced to one.
SELECT
'ART sending ^V[12]\d{5}$ id for V9 ID', COUNT(allX.PID)
FROM
xls.`all2` allX
LEFT OUTER JOIN
`art`.`pts_data_extract` sep ON sep.`PID` = allX.`PID`
AND sep.`End` = '2899-12-31'
LEFT OUTER JOIN
`s`.`users_all_output` u ON u.PID = allx.`PID`
WHERE
`ART_UID` REGEXP ('^V[12]\d{5}')
AND `NEW_UID` REGEXP ('V9\d{5}$')
AND `TYPE` NOT REGEXP ('DELETE')
4. CTRL+B again to wipe out the remaining backslash.
Suggested fix:
The backslash is an essential part of the regular expression and must not be deleted upon reformatting the query.
Proposed fix would be to maintain integrity of regular expressions during beautify reformats in mySQL.
Description: The Beautify action in the mySQL Workbench Editor removes double backslashes (one backslash for escape, one backslash as the regexp token prefix) This only happens with some queries, where some retain their backslash escapes and others lose them on successive CTRL-B (Beautify) actions. How to repeat: 1. ENter following query into a blank window in Workbench SELECT 'ART sending ^V[12]\\d{5}$ id for V9 ID', COUNT(allX.PID) -- u.LOGIN, allX.* FROM xls.`all2` allX LEFT OUTER JOIN `art`.`pts_data_extract` sep ON sep.`PID` = allX.`PID` AND sep.`End` = '2899-12-31' LEFT OUTER JOIN `s`.`users_all_output` u ON u.PID = allx.`PID` WHERE `ART_UID` REGEXP('^V[12]\\d{5}') AND `NEW_UID` REGEXP('V9\\d{5}$') AND `TYPE` NOT REGEXP('DELETE') 2. Press CTRL+B to beautify the query 3. Observe that the double backslash in the REGEXP portion of the WHERE clause has been reduced to one. SELECT 'ART sending ^V[12]\d{5}$ id for V9 ID', COUNT(allX.PID) FROM xls.`all2` allX LEFT OUTER JOIN `art`.`pts_data_extract` sep ON sep.`PID` = allX.`PID` AND sep.`End` = '2899-12-31' LEFT OUTER JOIN `s`.`users_all_output` u ON u.PID = allx.`PID` WHERE `ART_UID` REGEXP ('^V[12]\d{5}') AND `NEW_UID` REGEXP ('V9\d{5}$') AND `TYPE` NOT REGEXP ('DELETE') 4. CTRL+B again to wipe out the remaining backslash. Suggested fix: The backslash is an essential part of the regular expression and must not be deleted upon reformatting the query. Proposed fix would be to maintain integrity of regular expressions during beautify reformats in mySQL.