Removing Escapes due to DROP_UNKNOWN_ESCAPES affecting regex behavior #8635

SQLGlot removes escaped regex characters inside Snowflake RLIKE pattern

Description

SQLGlot is removing escaped regex characters from a Snowflake SQL RLIKE expression when parsing and rendering the SQL.

This is still occurring in SQLGlot 30.22.0.

The issue appears in the reg(...)salary portion of the regex pattern below. The original Snowflake regex pattern contains escaped regex characters:

reg(\s*|-|\.|\*)salary

After parsing with SQLGlot and rendering back to Snowflake SQL, the backslashes are removed:

reg(s*|-|.|*)salary

This changes the meaning of the regex and can produce invalid or incorrect regex behavior. In particular:

• \s* becomes s*
• \. becomes .
• \* becomes *

The final one is especially problematic because \* is intended to match a literal asterisk, but * is a regex quantifier.

SQLGlot version

30.22.0

Dialect

Snowflake

Steps to reproduce

from sqlglot import parse_one

sql = r"""
SELECT *
FROM transactions
WHERE merchant_description RLIKE '.*(PAYRO?LL|SALARY|PPDPAYROLL|FED SAL|reg(\s*|-|\.|\*)salary|Payrol)'
"""

expression = parse_one(sql, read="snowflake")

print(expression.sql(dialect="snowflake"))

Input SQL

SELECT *
FROM transactions
WHERE merchant_description RLIKE '.*(PAYRO?LL|SALARY|PPDPAYROLL|FED SAL|reg(\s*|-|\.|\*)salary|Payrol)'

Expected output

SQLGlot should preserve the escaped regex characters when parsing and rendering Snowflake SQL:

SELECT *
FROM transactions
WHERE merchant_description RLIKE '.*(PAYRO?LL|SALARY|PPDPAYROLL|FED SAL|reg(\s*|-|\.|\*)salary|Payrol)'

Specifically, this part of the regex should remain unchanged:

reg(\s*|-|\.|\*)salary

Actual output

SQLGlot removes the backslashes from the escaped regex characters:

SELECT *
FROM transactions
WHERE merchant_description RLIKE '.*(PAYRO?LL|SALARY|PPDPAYROLL|FED SAL|reg(\s*|-|\.|\*)salary|Payrol)'

Specifically, this part changes from:

reg(\s*|-|\.|\*)salary

to:

reg(s*|-|.|*)salary

Source context

This was found in a dbt macro where the source pattern is defined as:

"rlike": "'.*(PAYRO?LL|SALARY|PPDPAYROLL|FED SAL|reg(\\s*|-|\\.|\\*)salary|Payrol)",

When this dbt macro is compiled into Snowflake SQL after being parsed by Jinja and then parsed with SQLGlot using parse_one(sql, read="snowflake"), the escaped regex characters in the RLIKE pattern are stripped. This changes the regex semantics and can cause downstream SQL/regex errors.

For further context, when running our datapulls using DBT, the following error gets thrown as a result of the update to SQLglot made in version 30.21.0

Invalid regular expression: '.*(PAYRO?LL|SALARY|PPDPAYROLL|FED SAL|reg(s*|-|.|*)salary|Payrol)', no argument for repetition operator: *