Bug #102354 mySQL Workbench wipes out escaped backslash in regular expression (regexp)
Submitted: 23 Jan 19:05 Modified: 9 Feb 7:24
Reporter: William Lob Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Workbench: SQL Editor Severity:S3 (Non-critical)
Version:8.0.22 OS:Microsoft Windows (10)
Assigned to: CPU Architecture:ARM
Tags: escape, REGEXP, workbench

[23 Jan 19:05] William Lob
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.
[9 Feb 7:24] MySQL Verification Team
Hello William Lob,

Thank you for the bug report.
Imho this is duplicate of Bug #84530, please see Bug #84530.

Regards,
Ashwini Patil