This is an automated email from the ASF dual-hosted git repository.
cloud-fan pushed a commit to branch branch-4.2
in repository https://gitbox.apache.org/repos/asf/spark.git
The following commit(s) were added to refs/heads/branch-4.2 by this push:
new 4c70294e3b00 [SPARK-57332][SQL] Fix MySQL backslash escaping in LIKE
predicate pushdown via a dialect string-literal escaping hook
4c70294e3b00 is described below
commit 4c70294e3b000cc247f5074c7192f72216852cae
Author: Wenchen Fan <[email protected]>
AuthorDate: Wed Jun 10 16:03:52 2026 -0700
[SPARK-57332][SQL] Fix MySQL backslash escaping in LIKE predicate pushdown
via a dialect string-literal escaping hook
### What changes were proposed in this pull request?
Building a pushed-down `LIKE` predicate for `STARTS_WITH` / `ENDS_WITH` /
`CONTAINS` involves two distinct escaping layers:
1. **LIKE meta-char escaping** -- escape `_`, `%`, and the escape char `\`
itself, using `\` as the LIKE escape character. This is what
`V2ExpressionSQLBuilder.escapeSpecialCharsForLikePattern` does, and it is
dialect-independent.
2. **SQL string-literal escaping** -- make the resulting pattern text
survive the target database's string-literal parser. This is dialect-specific:
standard SQL only doubles `'`, but MySQL also processes `\` inside string
literals.
The base builder only handled layer 1 and assumed a trivial layer 2
(hand-wrapping the value in `'...'`). MySQL is the one dialect whose layer 2
differs, so it had to override all three
`visitStartsWith`/`visitEndsWith`/`visitContains` methods purely to emit
`ESCAPE '\\'`.
This PR separates the two layers:
- Adds a protected, overridable hook
`escapeStringLiteralForLikePattern(String)` to `V2ExpressionSQLBuilder`. It
defaults to the identity function, so the generated SQL for every standard-SQL
dialect is **byte-for-byte unchanged**.
- The shared `visitStartsWith`/`visitEndsWith`/`visitContains` now route
both the LIKE pattern and the `\` escape character through this hook (via a
small `likeWithEscape` helper), so the LIKE escape character is defined in one
place.
- `MySQLDialect` now overrides **only** `escapeStringLiteralForLikePattern`
(doubling backslashes for MySQL's string-literal layer) and **deletes** its
three duplicated visit-method overrides.
This is a follow-up to the design issue on #56350 (SPARK-57287).
### Why are the changes needed?
SPARK-57287 fixed backslash escaping in `escapeSpecialCharsForLikePattern`
(layer 1), which is correct for standard-SQL dialects. But MySQL reuses that
base method and adds an extra string-literal unescaping layer: it treats `\` as
an escape character inside string literals (this is exactly why `MySQLDialect`
already wrote `ESCAPE '\\'` rather than `ESCAPE '\'`, per SPARK-48172). MySQL's
string-literal parser collapses the single backslash doubling back to one `\`,
so the pushed-down [...]
Concretely, before this PR, against MySQL:
```
spark.table("mysql_catalog.db.t").filter($"c".startsWith("abc\\"))
// pushed: c LIKE 'abc\\%' ESCAPE '\\' -> after MySQL string-literal
parsing: c LIKE 'abc\%' ESCAPE '\'
// matches "abc" + literal "%", NOT values starting with "abc\"
```
The current coarse override surface (reimplement the whole visit method) is
also what let SPARK-48172 fix the `ESCAPE` clause but miss the pattern body.
Decoupling the two layers fixes the MySQL backslash case and removes the
duplicated visit methods, so a dialect only needs to override the visit methods
when its `LIKE` matching semantics genuinely differ -- never just for escaping.
### Does this PR introduce _any_ user-facing change?
Yes. For the MySQL JDBC dialect, `startsWith` / `endsWith` / `contains`
predicates on values containing a backslash now push down a correct `LIKE`
pattern and return correct results instead of silently wrong results. There is
no change for any other dialect (the new hook is identity by default, and the
generated SQL is unchanged).
### How was this patch tested?
Added a unit test in `JDBCSuite` that compiles `STARTS_WITH` / `ENDS_WITH`
/ `CONTAINS` predicates with both the default dialect and `MySQLDialect`,
asserting the generated SQL for backslash values (and that wildcard escaping is
preserved). The default-dialect output is unchanged; the MySQL output now
doubles backslashes through the string-literal layer. The existing H2-based
coverage in `JDBCV2Suite` (added by SPARK-57287) continues to exercise the
standard-SQL path end-to-end.
### Was this patch authored or co-authored using generative AI tooling?
Generated-by: Claude Code (Opus 4.8)
Closes #56384 from cloud-fan/decouple-like-pattern-escaping.
Authored-by: Wenchen Fan <[email protected]>
Signed-off-by: Wenchen Fan <[email protected]>
(cherry picked from commit f5eabcbd170bdbfb2049b930957fa13b37260e49)
Signed-off-by: Wenchen Fan <[email protected]>
---
project/MimaExcludes.scala | 8 +++-
.../sql/connector/util/V2ExpressionSQLBuilder.java | 44 +++++++++++++++++++---
.../org/apache/spark/sql/jdbc/MySQLDialect.scala | 19 +++-------
.../org/apache/spark/sql/jdbc/JDBCSuite.scala | 32 ++++++++++++++++
4 files changed, 83 insertions(+), 20 deletions(-)
diff --git a/project/MimaExcludes.scala b/project/MimaExcludes.scala
index 57ce887b0a98..78416ae9b511 100644
--- a/project/MimaExcludes.scala
+++ b/project/MimaExcludes.scala
@@ -59,7 +59,13 @@ object MimaExcludes {
// [SPARK-56700][SS] Make DataStreamReader.name public
ProblemFilters.exclude[ReversedMissingMethodProblem]("org.apache.spark.sql.streaming.DataStreamReader.name"),
// [SPARK-56395][SQL] Add NEAREST BY top-K ranking join
-
ProblemFilters.exclude[ReversedMissingMethodProblem]("org.apache.spark.sql.Dataset.nearestByJoin")
+
ProblemFilters.exclude[ReversedMissingMethodProblem]("org.apache.spark.sql.Dataset.nearestByJoin"),
+ // [SPARK-57332][SQL] MySQLDialect no longer overrides the visit methods
below; the public
+ // Scala overrides on its private SQL builder are replaced by the
inherited protected Java
+ // methods of V2ExpressionSQLBuilder. MySQLDialect is private, so this is
not a public API.
+
ProblemFilters.exclude[InaccessibleMethodProblem]("org.apache.spark.sql.connector.util.V2ExpressionSQLBuilder.visitStartsWith"),
+
ProblemFilters.exclude[InaccessibleMethodProblem]("org.apache.spark.sql.connector.util.V2ExpressionSQLBuilder.visitEndsWith"),
+
ProblemFilters.exclude[InaccessibleMethodProblem]("org.apache.spark.sql.connector.util.V2ExpressionSQLBuilder.visitContains")
)
// Exclude rules for 4.1.x from 4.0.0
diff --git
a/sql/catalyst/src/main/java/org/apache/spark/sql/connector/util/V2ExpressionSQLBuilder.java
b/sql/catalyst/src/main/java/org/apache/spark/sql/connector/util/V2ExpressionSQLBuilder.java
index 8ff75973558c..121324edbd2b 100644
---
a/sql/catalyst/src/main/java/org/apache/spark/sql/connector/util/V2ExpressionSQLBuilder.java
+++
b/sql/catalyst/src/main/java/org/apache/spark/sql/connector/util/V2ExpressionSQLBuilder.java
@@ -54,11 +54,16 @@ import org.apache.spark.sql.types.DataType;
public class V2ExpressionSQLBuilder {
/**
- * Escape the special chars for like pattern.
+ * Escape the LIKE pattern special chars, using {@code \} as the escape
character. The LIKE
+ * patterns produced by {@link #visitStartsWith}, {@link #visitEndsWith} and
{@link #visitContains}
+ * declare {@code ESCAPE '\'}, so the wildcards {@code _} and {@code %} and
the escape character
+ * {@code \} itself must each be prefixed with {@code \} to be matched
literally.
*
* Note: This method adopts the escape representation within Spark and is
not bound to any JDBC
- * dialect. JDBC dialect should overwrite this API if the underlying
database have more special
- * chars other than _ and %.
+ * dialect. A JDBC dialect should overwrite this API if the underlying
database has more LIKE
+ * special chars than {@code _}, {@code %} and {@code \}. Escaping that is
instead needed because
+ * the database treats a character specially inside a SQL <em>string
literal</em> belongs in
+ * {@link #escapeStringLiteralForLikePattern}.
*/
protected String escapeSpecialCharsForLikePattern(String str) {
StringBuilder builder = new StringBuilder();
@@ -75,6 +80,22 @@ public class V2ExpressionSQLBuilder {
return builder.toString();
}
+ /**
+ * Escape the characters that the target database treats specially inside a
SQL string literal,
+ * applied to a LIKE pattern (and its ESCAPE character) when embedding it
into a {@code '...'}
+ * literal for predicate pushdown.
+ *
+ * The default returns the input unchanged: a standard SQL string literal is
taken verbatim (the
+ * single-quote doubling is already applied when the literal is rendered),
so the {@code \} that
+ * {@link #escapeSpecialCharsForLikePattern} uses as the LIKE escape
character reaches the LIKE
+ * engine intact. A dialect whose string-literal syntax gives {@code \} a
special meaning (e.g.
+ * MySQL, which treats {@code \} as an escape character inside string
literals) must override this
+ * to double the backslash, so the LIKE pattern survives string-literal
parsing unchanged.
+ */
+ protected String escapeStringLiteralForLikePattern(String str) {
+ return str;
+ }
+
public String build(Expression expr) {
if (expr instanceof Literal literal) {
return visitLiteral(literal);
@@ -197,21 +218,32 @@ public class V2ExpressionSQLBuilder {
// Remove quotes at the beginning and end.
// e.g. converts "'str'" to "str".
String value = r.substring(1, r.length() - 1);
- return l + " LIKE '" + escapeSpecialCharsForLikePattern(value) + "%'
ESCAPE '\\'";
+ return likeWithEscape(l, escapeSpecialCharsForLikePattern(value) + "%");
}
protected String visitEndsWith(String l, String r) {
// Remove quotes at the beginning and end.
// e.g. converts "'str'" to "str".
String value = r.substring(1, r.length() - 1);
- return l + " LIKE '%" + escapeSpecialCharsForLikePattern(value) + "'
ESCAPE '\\'";
+ return likeWithEscape(l, "%" + escapeSpecialCharsForLikePattern(value));
}
protected String visitContains(String l, String r) {
// Remove quotes at the beginning and end.
// e.g. converts "'str'" to "str".
String value = r.substring(1, r.length() - 1);
- return l + " LIKE '%" + escapeSpecialCharsForLikePattern(value) + "%'
ESCAPE '\\'";
+ return likeWithEscape(l, "%" + escapeSpecialCharsForLikePattern(value) +
"%");
+ }
+
+ /**
+ * Build a {@code <left> LIKE '<pattern>' ESCAPE '\'} predicate. The pattern
already has its LIKE
+ * special chars escaped (via {@link #escapeSpecialCharsForLikePattern});
both the pattern and the
+ * {@code \} escape character are then passed through {@link
#escapeStringLiteralForLikePattern} so
+ * a dialect can add any string-literal escaping its SQL syntax requires.
+ */
+ private String likeWithEscape(String l, String pattern) {
+ return l + " LIKE '" + escapeStringLiteralForLikePattern(pattern)
+ + "' ESCAPE '" + escapeStringLiteralForLikePattern("\\") + "'";
}
protected String inputToSQL(Expression input) {
diff --git
a/sql/core/src/main/scala/org/apache/spark/sql/jdbc/MySQLDialect.scala
b/sql/core/src/main/scala/org/apache/spark/sql/jdbc/MySQLDialect.scala
index 41452ca45057..a047085a3537 100644
--- a/sql/core/src/main/scala/org/apache/spark/sql/jdbc/MySQLDialect.scala
+++ b/sql/core/src/main/scala/org/apache/spark/sql/jdbc/MySQLDialect.scala
@@ -99,19 +99,12 @@ private case class MySQLDialect() extends JdbcDialect with
SQLConfHelper with No
}
}
- override def visitStartsWith(l: String, r: String): String = {
- val value = r.substring(1, r.length() - 1)
- s"$l LIKE '${escapeSpecialCharsForLikePattern(value)}%' ESCAPE '\\\\'"
- }
-
- override def visitEndsWith(l: String, r: String): String = {
- val value = r.substring(1, r.length() - 1)
- s"$l LIKE '%${escapeSpecialCharsForLikePattern(value)}' ESCAPE '\\\\'"
- }
-
- override def visitContains(l: String, r: String): String = {
- val value = r.substring(1, r.length() - 1)
- s"$l LIKE '%${escapeSpecialCharsForLikePattern(value)}%' ESCAPE '\\\\'"
+ // MySQL treats backslash as an escape character inside string literals,
so every backslash in
+ // a LIKE pattern (and the ESCAPE character) must be doubled to survive
string-literal parsing
+ // before the LIKE engine applies its own escaping. The base
STARTS_WITH/ENDS_WITH/CONTAINS
+ // pattern building is otherwise shared, so only this hook is overridden.
+ override def escapeStringLiteralForLikePattern(str: String): String = {
+ str.replace("\\", "\\\\")
}
override def visitAggregateFunction(
diff --git a/sql/core/src/test/scala/org/apache/spark/sql/jdbc/JDBCSuite.scala
b/sql/core/src/test/scala/org/apache/spark/sql/jdbc/JDBCSuite.scala
index 4a7771305994..426d22a9a64f 100644
--- a/sql/core/src/test/scala/org/apache/spark/sql/jdbc/JDBCSuite.scala
+++ b/sql/core/src/test/scala/org/apache/spark/sql/jdbc/JDBCSuite.scala
@@ -910,6 +910,38 @@ class JDBCSuite extends SharedSparkSession {
assert(dialect.compileExpression(eqFalse).get === "\"a\" = (1 = 0)")
}
+ test("SPARK-57332: escape backslash in LIKE pattern for
STARTS_WITH/ENDS_WITH/CONTAINS") {
+ // Default dialect: standard SQL string literals take backslash verbatim,
so the LIKE escape
+ // character `\` appears once in the ESCAPE clause and a literal backslash
in the value is
+ // doubled once (by escapeSpecialCharsForLikePattern) to be matched
literally.
+ val defaultDialect = JdbcDialects.get("jdbc:")
+ def defaultSQL(f: Filter): String =
defaultDialect.compileExpression(f.toV2).getOrElse("")
+ // "c" LIKE 'ab\\%' ESCAPE '\'
+ assert(defaultSQL(StringStartsWith("c", "ab\\")) === """"c" LIKE 'ab\\%'
ESCAPE '\'""")
+ // "c" LIKE '%\\ab' ESCAPE '\'
+ assert(defaultSQL(StringEndsWith("c", "\\ab")) === """"c" LIKE '%\\ab'
ESCAPE '\'""")
+ // "c" LIKE '%a\\b%' ESCAPE '\'
+ assert(defaultSQL(StringContains("c", "a\\b")) === """"c" LIKE '%a\\b%'
ESCAPE '\'""")
+
+ // MySQL treats backslash as an escape character inside string literals,
so every backslash is
+ // doubled again: the ESCAPE clause uses `\\` and a literal backslash in
the value becomes four
+ // backslashes (escapeSpecialCharsForLikePattern doubles it, then
escapeStringLiteralForLikePattern
+ // doubles each of those). The wildcard escaping for `%`/`_` is unchanged
from the default.
+ val mySQLDialect = JdbcDialects.get("jdbc:mysql://127.0.0.1/db")
+ def mySQLSQL(f: Filter): String =
mySQLDialect.compileExpression(f.toV2).getOrElse("")
+ // `c` LIKE 'ab\\\\%' ESCAPE '\\'
+ assert(mySQLSQL(StringStartsWith("c", "ab\\")) === """`c` LIKE 'ab\\\\%'
ESCAPE '\\'""")
+ // `c` LIKE '%\\\\ab' ESCAPE '\\'
+ assert(mySQLSQL(StringEndsWith("c", "\\ab")) === """`c` LIKE '%\\\\ab'
ESCAPE '\\'""")
+ // `c` LIKE '%a\\\\b%' ESCAPE '\\'
+ assert(mySQLSQL(StringContains("c", "a\\b")) === """`c` LIKE '%a\\\\b%'
ESCAPE '\\'""")
+ // Wildcards stay escaped: the `\` that escapeSpecialCharsForLikePattern
puts before `%`/`_` is
+ // itself doubled for MySQL's string-literal layer, so it parses back to
`\%`/`\_` (literal
+ // wildcards) before the LIKE engine, matching the default dialect's
semantics.
+ // `c` LIKE 'a\\%b\\_%' ESCAPE '\\'
+ assert(mySQLSQL(StringStartsWith("c", "a%b_")) === """`c` LIKE 'a\\%b\\_%'
ESCAPE '\\'""")
+ }
+
test("Dialect unregister") {
JdbcDialects.unregisterDialect(H2Dialect())
try {
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
