You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@spark.apache.org by be...@apache.org on 2023/12/01 06:59:12 UTC
(spark) branch branch-3.3 updated: [SPARK-46029][SQL][3.3] Escape the single quote, _ and % for DS V2 pushdown
This is an automated email from the ASF dual-hosted git repository.
beliefer pushed a commit to branch branch-3.3
in repository https://gitbox.apache.org/repos/asf/spark.git
The following commit(s) were added to refs/heads/branch-3.3 by this push:
new 6ad00b7b2e34 [SPARK-46029][SQL][3.3] Escape the single quote, _ and % for DS V2 pushdown
6ad00b7b2e34 is described below
commit 6ad00b7b2e34c8b6868b4d5042c6972a8fa9f869
Author: Jiaan Geng <be...@163.com>
AuthorDate: Fri Dec 1 14:58:52 2023 +0800
[SPARK-46029][SQL][3.3] Escape the single quote, _ and % for DS V2 pushdown
### What changes were proposed in this pull request?
This PR used to back port https://github.com/apache/spark/pull/43801 to branch-3.3
### Why are the changes needed?
Escape the single quote, _ and % for DS V2 pushdown
### Does this PR introduce _any_ user-facing change?
'No'.
### How was this patch tested?
New test cases.
### Was this patch authored or co-authored using generative AI tooling?
'No'.
Closes #44089 from beliefer/SPARK-46029_3.3.
Authored-by: Jiaan Geng <be...@163.com>
Signed-off-by: Jiaan Geng <be...@163.com>
---
.../sql/connector/util/V2ExpressionSQLBuilder.java | 35 ++++++-
.../org/apache/spark/sql/jdbc/H2Dialect.scala | 24 +++++
.../datasources/v2/V2PredicateSuite.scala | 6 +-
.../org/apache/spark/sql/jdbc/JDBCV2Suite.scala | 112 ++++++++++++++++++++-
4 files changed, 166 insertions(+), 11 deletions(-)
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 3fa648002879..29414377f668 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
@@ -35,6 +35,35 @@ import org.apache.spark.sql.types.DataType;
*/
public class V2ExpressionSQLBuilder {
+ /**
+ * Escape the special chars for like pattern.
+ *
+ * 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 %.
+ */
+ protected String escapeSpecialCharsForLikePattern(String str) {
+ StringBuilder builder = new StringBuilder();
+
+ for (char c : str.toCharArray()) {
+ switch (c) {
+ case '_':
+ builder.append("\\_");
+ break;
+ case '%':
+ builder.append("\\%");
+ break;
+ case '\'':
+ builder.append("\\\'");
+ break;
+ default:
+ builder.append(c);
+ }
+ }
+
+ return builder.toString();
+ }
+
public String build(Expression expr) {
if (expr instanceof Literal) {
return visitLiteral((Literal) expr);
@@ -147,21 +176,21 @@ 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 '" + value + "%'";
+ return l + " LIKE '" + escapeSpecialCharsForLikePattern(value) + "%' ESCAPE '\\'";
}
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 '%" + value + "'";
+ return l + " LIKE '%" + escapeSpecialCharsForLikePattern(value) + "' ESCAPE '\\'";
}
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 '%" + value + "%'";
+ return l + " LIKE '%" + escapeSpecialCharsForLikePattern(value) + "%' ESCAPE '\\'";
}
private String inputToSQL(Expression input) {
diff --git a/sql/core/src/main/scala/org/apache/spark/sql/jdbc/H2Dialect.scala b/sql/core/src/main/scala/org/apache/spark/sql/jdbc/H2Dialect.scala
index 967df112af22..eb759144b6c7 100644
--- a/sql/core/src/main/scala/org/apache/spark/sql/jdbc/H2Dialect.scala
+++ b/sql/core/src/main/scala/org/apache/spark/sql/jdbc/H2Dialect.scala
@@ -20,8 +20,11 @@ package org.apache.spark.sql.jdbc
import java.sql.{SQLException, Types}
import java.util.Locale
+import scala.util.control.NonFatal
+
import org.apache.spark.sql.AnalysisException
import org.apache.spark.sql.catalyst.analysis.{NoSuchNamespaceException, NoSuchTableException, TableAlreadyExistsException}
+import org.apache.spark.sql.connector.expressions.Expression
import org.apache.spark.sql.connector.expressions.aggregate.{AggregateFunc, GeneralAggregateFunc}
import org.apache.spark.sql.execution.datasources.jdbc.JdbcUtils
import org.apache.spark.sql.types.{BooleanType, ByteType, DataType, DecimalType, ShortType, StringType}
@@ -98,4 +101,25 @@ private object H2Dialect extends JdbcDialect {
}
super.classifyException(message, e)
}
+
+ override def compileExpression(expr: Expression): Option[String] = {
+ val h2SQLBuilder = new H2SQLBuilder()
+ try {
+ Some(h2SQLBuilder.build(expr))
+ } catch {
+ case NonFatal(e) =>
+ logWarning("Error occurs while compiling V2 expression", e)
+ None
+ }
+ }
+
+ class H2SQLBuilder extends JDBCSQLBuilder {
+ override def escapeSpecialCharsForLikePattern(str: String): String = {
+ str.map {
+ case '_' => "\\_"
+ case '%' => "\\%"
+ case c => c.toString
+ }.mkString
+ }
+ }
}
diff --git a/sql/core/src/test/scala/org/apache/spark/sql/execution/datasources/v2/V2PredicateSuite.scala b/sql/core/src/test/scala/org/apache/spark/sql/execution/datasources/v2/V2PredicateSuite.scala
index 2df8b8e56c44..e80cc0000439 100644
--- a/sql/core/src/test/scala/org/apache/spark/sql/execution/datasources/v2/V2PredicateSuite.scala
+++ b/sql/core/src/test/scala/org/apache/spark/sql/execution/datasources/v2/V2PredicateSuite.scala
@@ -239,7 +239,7 @@ class V2PredicateSuite extends SparkFunSuite {
Array[Expression](ref("a"), literal))
assert(predicate1.equals(predicate2))
assert(predicate1.references.map(_.describe()).toSeq == Seq("a"))
- assert(predicate1.describe.equals("a LIKE 'str%'"))
+ assert(predicate1.describe.equals(raw"a LIKE 'str%' ESCAPE '\'"))
val v1Filter = StringStartsWith("a", "str")
assert(v1Filter.toV2.equals(predicate1))
@@ -253,7 +253,7 @@ class V2PredicateSuite extends SparkFunSuite {
Array[Expression](ref("a"), literal))
assert(predicate1.equals(predicate2))
assert(predicate1.references.map(_.describe()).toSeq == Seq("a"))
- assert(predicate1.describe.equals("a LIKE '%str'"))
+ assert(predicate1.describe.equals(raw"a LIKE '%str' ESCAPE '\'"))
val v1Filter = StringEndsWith("a", "str")
assert(v1Filter.toV2.equals(predicate1))
@@ -267,7 +267,7 @@ class V2PredicateSuite extends SparkFunSuite {
Array[Expression](ref("a"), literal))
assert(predicate1.equals(predicate2))
assert(predicate1.references.map(_.describe()).toSeq == Seq("a"))
- assert(predicate1.describe.equals("a LIKE '%str%'"))
+ assert(predicate1.describe.equals(raw"a LIKE '%str%' ESCAPE '\'"))
val v1Filter = StringContains("a", "str")
assert(v1Filter.toV2.equals(predicate1))
diff --git a/sql/core/src/test/scala/org/apache/spark/sql/jdbc/JDBCV2Suite.scala b/sql/core/src/test/scala/org/apache/spark/sql/jdbc/JDBCV2Suite.scala
index 293334084af2..0929915af058 100644
--- a/sql/core/src/test/scala/org/apache/spark/sql/jdbc/JDBCV2Suite.scala
+++ b/sql/core/src/test/scala/org/apache/spark/sql/jdbc/JDBCV2Suite.scala
@@ -104,6 +104,19 @@ class JDBCV2Suite extends QueryTest with SharedSparkSession with ExplainSuiteHel
"(1, 'bottle', 11111111111111111111.123)").executeUpdate()
conn.prepareStatement("INSERT INTO \"test\".\"item\" VALUES " +
"(1, 'bottle', 99999999999999999999.123)").executeUpdate()
+
+ conn.prepareStatement(
+ "CREATE TABLE \"test\".\"address\" (email TEXT(32) NOT NULL)").executeUpdate()
+ conn.prepareStatement("INSERT INTO \"test\".\"address\" VALUES " +
+ "('abc_def@gmail.com')").executeUpdate()
+ conn.prepareStatement("INSERT INTO \"test\".\"address\" VALUES " +
+ "('abc%def@gmail.com')").executeUpdate()
+ conn.prepareStatement("INSERT INTO \"test\".\"address\" VALUES " +
+ "('abc%_def@gmail.com')").executeUpdate()
+ conn.prepareStatement("INSERT INTO \"test\".\"address\" VALUES " +
+ "('abc_%def@gmail.com')").executeUpdate()
+ conn.prepareStatement("INSERT INTO \"test\".\"address\" VALUES " +
+ "('abc_''%def@gmail.com')").executeUpdate()
}
}
@@ -320,7 +333,7 @@ class JDBCV2Suite extends QueryTest with SharedSparkSession with ExplainSuiteHel
val df3 = spark.table("h2.test.employee").filter($"name".startsWith("a"))
checkFiltersRemoved(df3)
- checkPushedInfo(df3, "PushedFilters: [NAME IS NOT NULL, NAME LIKE 'a%']")
+ checkPushedInfo(df3, raw"PushedFilters: [NAME IS NOT NULL, NAME LIKE 'a%' ESCAPE '\']")
checkAnswer(df3, Seq(Row(1, "amy", 10000, 1000, true), Row(2, "alex", 12000, 1200, false)))
val df4 = spark.table("h2.test.employee").filter($"is_manager")
@@ -368,6 +381,94 @@ class JDBCV2Suite extends QueryTest with SharedSparkSession with ExplainSuiteHel
checkAnswer(df10, Row("mary", 2))
}
+ test("SPARK-38432: escape the single quote, _ and % for DS V2 pushdown") {
+ val df1 = spark.table("h2.test.address").filter($"email".startsWith("abc_"))
+ checkFiltersRemoved(df1)
+ checkPushedInfo(df1, raw"PushedFilters: [EMAIL IS NOT NULL, EMAIL LIKE 'abc\_%' ESCAPE '\']")
+ checkAnswer(df1,
+ Seq(Row("abc_%def@gmail.com"), Row("abc_'%def@gmail.com"), Row("abc_def@gmail.com")))
+
+ val df2 = spark.table("h2.test.address").filter($"email".startsWith("abc%"))
+ checkFiltersRemoved(df2)
+ checkPushedInfo(df2, raw"PushedFilters: [EMAIL IS NOT NULL, EMAIL LIKE 'abc\%%' ESCAPE '\']")
+ checkAnswer(df2, Seq(Row("abc%_def@gmail.com"), Row("abc%def@gmail.com")))
+
+ val df3 = spark.table("h2.test.address").filter($"email".startsWith("abc%_"))
+ checkFiltersRemoved(df3)
+ checkPushedInfo(df3, raw"PushedFilters: [EMAIL IS NOT NULL, EMAIL LIKE 'abc\%\_%' ESCAPE '\']")
+ checkAnswer(df3, Seq(Row("abc%_def@gmail.com")))
+
+ val df4 = spark.table("h2.test.address").filter($"email".startsWith("abc_%"))
+ checkFiltersRemoved(df4)
+ checkPushedInfo(df4, raw"PushedFilters: [EMAIL IS NOT NULL, EMAIL LIKE 'abc\_\%%' ESCAPE '\']")
+ checkAnswer(df4, Seq(Row("abc_%def@gmail.com")))
+
+ val df5 = spark.table("h2.test.address").filter($"email".startsWith("abc_'%"))
+ checkFiltersRemoved(df5)
+ checkPushedInfo(df5,
+ raw"PushedFilters: [EMAIL IS NOT NULL, EMAIL LIKE 'abc\_\'\%%' ESCAPE '\']")
+ checkAnswer(df5, Seq(Row("abc_'%def@gmail.com")))
+
+ val df6 = spark.table("h2.test.address").filter($"email".endsWith("_def@gmail.com"))
+ checkFiltersRemoved(df6)
+ checkPushedInfo(df6,
+ raw"PushedFilters: [EMAIL IS NOT NULL, EMAIL LIKE '%\_def@gmail.com' ESCAPE '\']")
+ checkAnswer(df6, Seq(Row("abc%_def@gmail.com"), Row("abc_def@gmail.com")))
+
+ val df7 = spark.table("h2.test.address").filter($"email".endsWith("%def@gmail.com"))
+ checkFiltersRemoved(df7)
+ checkPushedInfo(df7,
+ raw"PushedFilters: [EMAIL IS NOT NULL, EMAIL LIKE '%\%def@gmail.com' ESCAPE '\']")
+ checkAnswer(df7,
+ Seq(Row("abc%def@gmail.com"), Row("abc_%def@gmail.com"), Row("abc_'%def@gmail.com")))
+
+ val df8 = spark.table("h2.test.address").filter($"email".endsWith("%_def@gmail.com"))
+ checkFiltersRemoved(df8)
+ checkPushedInfo(df8,
+ raw"PushedFilters: [EMAIL IS NOT NULL, EMAIL LIKE '%\%\_def@gmail.com' ESCAPE '\']")
+ checkAnswer(df8, Seq(Row("abc%_def@gmail.com")))
+
+ val df9 = spark.table("h2.test.address").filter($"email".endsWith("_%def@gmail.com"))
+ checkFiltersRemoved(df9)
+ checkPushedInfo(df9,
+ raw"PushedFilters: [EMAIL IS NOT NULL, EMAIL LIKE '%\_\%def@gmail.com' ESCAPE '\']")
+ checkAnswer(df9, Seq(Row("abc_%def@gmail.com")))
+
+ val df10 = spark.table("h2.test.address").filter($"email".endsWith("_'%def@gmail.com"))
+ checkFiltersRemoved(df10)
+ checkPushedInfo(df10,
+ raw"PushedFilters: [EMAIL IS NOT NULL, EMAIL LIKE '%\_\'\%def@gmail.com' ESCAPE '\']")
+ checkAnswer(df10, Seq(Row("abc_'%def@gmail.com")))
+
+ val df11 = spark.table("h2.test.address").filter($"email".contains("c_d"))
+ checkFiltersRemoved(df11)
+ checkPushedInfo(df11, raw"PushedFilters: [EMAIL IS NOT NULL, EMAIL LIKE '%c\_d%' ESCAPE '\']")
+ checkAnswer(df11, Seq(Row("abc_def@gmail.com")))
+
+ val df12 = spark.table("h2.test.address").filter($"email".contains("c%d"))
+ checkFiltersRemoved(df12)
+ checkPushedInfo(df12, raw"PushedFilters: [EMAIL IS NOT NULL, EMAIL LIKE '%c\%d%' ESCAPE '\']")
+ checkAnswer(df12, Seq(Row("abc%def@gmail.com")))
+
+ val df13 = spark.table("h2.test.address").filter($"email".contains("c%_d"))
+ checkFiltersRemoved(df13)
+ checkPushedInfo(df13,
+ raw"PushedFilters: [EMAIL IS NOT NULL, EMAIL LIKE '%c\%\_d%' ESCAPE '\']")
+ checkAnswer(df13, Seq(Row("abc%_def@gmail.com")))
+
+ val df14 = spark.table("h2.test.address").filter($"email".contains("c_%d"))
+ checkFiltersRemoved(df14)
+ checkPushedInfo(df14,
+ raw"PushedFilters: [EMAIL IS NOT NULL, EMAIL LIKE '%c\_\%d%' ESCAPE '\']")
+ checkAnswer(df14, Seq(Row("abc_%def@gmail.com")))
+
+ val df15 = spark.table("h2.test.address").filter($"email".contains("c_'%d"))
+ checkFiltersRemoved(df15)
+ checkPushedInfo(df15,
+ raw"PushedFilters: [EMAIL IS NOT NULL, EMAIL LIKE '%c\_\'\%d%' ESCAPE '\']")
+ checkAnswer(df15, Seq(Row("abc_'%def@gmail.com")))
+ }
+
test("scan with filter push-down with ansi mode") {
Seq(false, true).foreach { ansiMode =>
withSQLConf(SQLConf.ANSI_ENABLED.key -> ansiMode.toString) {
@@ -478,7 +579,7 @@ class JDBCV2Suite extends QueryTest with SharedSparkSession with ExplainSuiteHel
checkFiltersRemoved(df8, ansiMode)
val expectedPlanFragment8 = if (ansiMode) {
"PushedFilters: [BONUS IS NOT NULL, DEPT IS NOT NULL, " +
- "CAST(BONUS AS string) LIKE '%30%', CAST(DEPT AS byte) > 1, ...,"
+ raw"CAST(BONUS AS string) LIKE '%30%' ESCAPE '\', CAST(DEPT AS ...,"
} else {
"PushedFilters: [BONUS IS NOT NULL, DEPT IS NOT NULL],"
}
@@ -537,9 +638,10 @@ class JDBCV2Suite extends QueryTest with SharedSparkSession with ExplainSuiteHel
test("show tables") {
checkAnswer(sql("SHOW TABLES IN h2.test"),
- Seq(Row("test", "people", false), Row("test", "empty_table", false),
- Row("test", "employee", false), Row("test", "item", false), Row("test", "dept", false),
- Row("test", "person", false), Row("test", "view1", false), Row("test", "view2", false)))
+ Seq(Row("test", "address", false), Row("test", "people", false),
+ Row("test", "empty_table", false), Row("test", "employee", false),
+ Row("test", "item", false), Row("test", "dept", false), Row("test", "person", false),
+ Row("test", "view1", false), Row("test", "view2", false)))
}
test("SQL API: create table as select") {
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@spark.apache.org
For additional commands, e-mail: commits-help@spark.apache.org