executor: LIKE ignores the ESCAPE clause, and has no default escape character [CLAUDE]

[jbylund]

Two related defects in _like, one of them unanimous across engines and one a genuine dialect split. Filing them together because a fix for the first is the mechanism the second would need.

Measured against PostgreSQL 18.4, MySQL 8.0.46, DuckDB 1.5.5 and SQLite 3.51, over the rows 'a%b', 'axb', 'a_b', 'a\b'.

pattern	postgres	mysql	duckdb	sqlite	sqlglot
LIKE 'a\%b'	a%b	a%b	a\b	a\b	a\b
LIKE 'a\_b'	a_b	a_b	—	—	—
LIKE 'a!%b' ESCAPE '!'	a%b	a%b	a%b	a%b	raises

1. The ESCAPE clause raises — unanimous, and sqlglot is wrong

from sqlglot.executor import execute

execute(
    "SELECT s FROM q WHERE s LIKE 'a!%b' ESCAPE '!'",
    schema={"q": {"s": "TEXT"}},
    tables={"q": [{"s": "a%b"}, {"s": "axb"}]},
    dialect="postgres",
)

ExecuteError: Step 'Join: q' failed: name 'ESCAPE' is not defined

All four engines support the clause and agree on the answer. It is in every SQL standard since SQL-92 and is the portable way to match a literal % or _, so there is no dialect question here — the executor simply cannot run it.

The Python generator emits exp.Escape as a bare call wrapping the comparison, and ENV has no entry for it:

SELECT scope["q"]["s"] FROM "q" AS "q" WHERE ESCAPE(LIKE(scope["q"]["s"], 'a!%b'), '!')

Wrapping LIKE's result is also the wrong shape — the escape character has to reach the pattern compilation, not the boolean it produced.

2. No default escape character — not unanimous, so probably leave it

_like in sqlglot/executor/env.py compiles the pattern with no notion of an escape character at all:

def _like(this, e, flags=0):
    return bool(
        re.fullmatch(re.escape(e).replace("_", ".").replace("%", ".*"), this, re.DOTALL | flags)
    )

re.escape turns a backslash in the pattern into a literal backslash, and % is then replaced unconditionally, so 'a\%b' compiles to the regex a\\.*b. That does not merely fail to match a%b — it matches a\b instead, so the query answers a different row rather than none.

But the engines split 2–2 on whether backslash is the default escape: postgres and mysql say yes, duckdb and sqlite say no, and sqlglot's current behaviour is duckdb's and sqlite's. ENV is shared by every dialect and has no dialect to key on, so changing the default globally would trade postgres conformance for duckdb conformance rather than fixing anything. Same situation as the CAST(... AS INT) rows in #13, and probably the same conclusion: record it, do not change it without a way to express a per-dialect LIKE.

Worth knowing that the consequence is a silent wrong row rather than a refusal, though, which is why it is written up here rather than left implicit.

Proposed fix, section 1 only

Give _like an escape parameter and build the regex in one pass over the pattern, so an escaped %, _ or escape character becomes a literal and an unescaped one stays a wildcard. Then either add an ESCAPE entry to ENV that re-dispatches into the comparison, or — cleaner — teach the Python generator to fold exp.Escape into the LIKE/ILIKE call it wraps, so the escape character arrives as a third argument instead of being applied to a boolean.

That also leaves the mechanism in place if section 2 ever gets a dialect signal to key on: a dialect default is just an escape character supplied when the query did not name one.

Happy to open a PR for section 1 if that split looks right.

Disclosure: investigated with Claude.