You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@spark.apache.org by we...@apache.org on 2026/06/10 23:04:18 UTC
(spark) branch master updated: [SPARK-57332][SQL] Fix MySQL backslash escaping in LIKE predicate pushdown via a dialect string-literal escaping hook
This is an automated email from the ASF dual-hosted git repository.
cloud-fan pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/spark.git
The following commit(s) were added to refs/heads/master by this push:
new f5eabcbd170b [SPARK-57332][SQL] Fix MySQL backslash escaping in LIKE predicate pushdown via a dialect string-literal escaping hook
f5eabcbd170b is described below
commit f5eabcbd170bdbfb2049b930957fa13b37260e49
Author: Wenchen Fan <we...@databricks.com>
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 <we...@databricks.com>
Signed-off-by: Wenchen Fan <we...@databricks.com>
---
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 422db17e1036..16ff9b40c16d 100644
--- a/project/MimaExcludes.scala
+++ b/project/MimaExcludes.scala
@@ -66,7 +66,13 @@ object MimaExcludes {
// [SPARK-34591][ML] Add pruneTree parameter to Strategy
ProblemFilters.exclude[DirectMissingMethodProblem]("org.apache.spark.mllib.tree.configuration.Strategy.this"),
// [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: commits-unsubscribe@spark.apache.org
For additional commands, e-mail: commits-help@spark.apache.org