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 2025/03/06 06:24:39 UTC
(spark) branch branch-4.0 updated: [SPARK-49488][SQL][FOLLOWUP] Use correct MySQL datetime functions when pushing down EXTRACT
This is an automated email from the ASF dual-hosted git repository.
wenchen pushed a commit to branch branch-4.0
in repository https://gitbox.apache.org/repos/asf/spark.git
The following commit(s) were added to refs/heads/branch-4.0 by this push:
new aa344175d465 [SPARK-49488][SQL][FOLLOWUP] Use correct MySQL datetime functions when pushing down EXTRACT
aa344175d465 is described below
commit aa344175d465fba6f84d1bf7a3f8538054573ed3
Author: beliefer <be...@163.com>
AuthorDate: Thu Mar 6 14:24:01 2025 +0800
[SPARK-49488][SQL][FOLLOWUP] Use correct MySQL datetime functions when pushing down EXTRACT
### What changes were proposed in this pull request?
This PR proposes to use correct MySQL datetime functions when pushing down `EXTRACT`.
### Why are the changes needed?
bug fix
### Does this PR introduce _any_ user-facing change?
Yes, query result is corrected, but this bug is not released yet.
### How was this patch tested?
updated test
### Was this patch authored or co-authored using generative AI tooling?
'No'.
Closes #50112 from beliefer/SPARK-49488_followup.
Authored-by: beliefer <be...@163.com>
Signed-off-by: Wenchen Fan <we...@databricks.com>
(cherry picked from commit 7b39d2409dc6031caef98c84edf21945b3a6cb41)
Signed-off-by: Wenchen Fan <we...@databricks.com>
---
.../spark/sql/jdbc/v2/MySQLIntegrationSuite.scala | 45 ++++++++++++++++++----
.../sql/connector/util/V2ExpressionSQLBuilder.java | 6 ++-
.../org/apache/spark/sql/jdbc/MySQLDialect.scala | 17 +++++---
3 files changed, 53 insertions(+), 15 deletions(-)
diff --git a/connector/docker-integration-tests/src/test/scala/org/apache/spark/sql/jdbc/v2/MySQLIntegrationSuite.scala b/connector/docker-integration-tests/src/test/scala/org/apache/spark/sql/jdbc/v2/MySQLIntegrationSuite.scala
index 9c07ce939fe3..3c63e17a953c 100644
--- a/connector/docker-integration-tests/src/test/scala/org/apache/spark/sql/jdbc/v2/MySQLIntegrationSuite.scala
+++ b/connector/docker-integration-tests/src/test/scala/org/apache/spark/sql/jdbc/v2/MySQLIntegrationSuite.scala
@@ -90,6 +90,9 @@ class MySQLIntegrationSuite extends DockerJDBCIntegrationV2Suite with V2JDBCTest
"('amy', '2022-05-19', '2022-05-19 00:00:00')").executeUpdate()
connection.prepareStatement("INSERT INTO datetime VALUES " +
"('alex', '2022-05-18', '2022-05-18 00:00:00')").executeUpdate()
+ // '2022-01-01' is Saturday and is in ISO year 2021.
+ connection.prepareStatement("INSERT INTO datetime VALUES " +
+ "('tom', '2022-01-01', '2022-01-01 00:00:00')").executeUpdate()
}
override def testUpdateColumnType(tbl: String): Unit = {
@@ -195,17 +198,19 @@ class MySQLIntegrationSuite extends DockerJDBCIntegrationV2Suite with V2JDBCTest
val df4 = sql(s"SELECT name FROM $tbl WHERE hour(time1) = 0 AND minute(time1) = 0")
checkFilterPushed(df4)
val rows4 = df4.collect()
- assert(rows4.length === 2)
+ assert(rows4.length === 3)
assert(rows4(0).getString(0) === "amy")
assert(rows4(1).getString(0) === "alex")
+ assert(rows4(2).getString(0) === "tom")
val df5 = sql(s"SELECT name FROM $tbl WHERE " +
- "extract(WEEk from date1) > 10 AND extract(YEAROFWEEK from date1) = 2022")
+ "extract(WEEK from date1) > 10 AND extract(YEAR from date1) = 2022")
checkFilterPushed(df5)
val rows5 = df5.collect()
- assert(rows5.length === 2)
+ assert(rows5.length === 3)
assert(rows5(0).getString(0) === "amy")
assert(rows5(1).getString(0) === "alex")
+ assert(rows5(2).getString(0) === "tom")
val df6 = sql(s"SELECT name FROM $tbl WHERE date_add(date1, 1) = date'2022-05-20' " +
"AND datediff(date1, '2022-05-10') > 0")
@@ -220,11 +225,35 @@ class MySQLIntegrationSuite extends DockerJDBCIntegrationV2Suite with V2JDBCTest
assert(rows7.length === 1)
assert(rows7(0).getString(0) === "alex")
- val df8 = sql(s"SELECT name FROM $tbl WHERE dayofweek(date1) = 4")
- checkFilterPushed(df8)
- val rows8 = df8.collect()
- assert(rows8.length === 1)
- assert(rows8(0).getString(0) === "alex")
+ withClue("weekofyear") {
+ val woy = sql(s"SELECT weekofyear(date1) FROM $tbl WHERE name = 'tom'")
+ .collect().head.getInt(0)
+ val df = sql(s"SELECT name FROM $tbl WHERE weekofyear(date1) = $woy")
+ checkFilterPushed(df)
+ val rows = df.collect()
+ assert(rows.length === 1)
+ assert(rows(0).getString(0) === "tom")
+ }
+
+ withClue("dayofweek") {
+ val dow = sql(s"SELECT dayofweek(date1) FROM $tbl WHERE name = 'alex'")
+ .collect().head.getInt(0)
+ val df = sql(s"SELECT name FROM $tbl WHERE dayofweek(date1) = $dow")
+ checkFilterPushed(df)
+ val rows = df.collect()
+ assert(rows.length === 1)
+ assert(rows(0).getString(0) === "alex")
+ }
+
+ withClue("yearofweek") {
+ val yow = sql(s"SELECT extract(YEAROFWEEK from date1) FROM $tbl WHERE name = 'tom'")
+ .collect().head.getInt(0)
+ val df = sql(s"SELECT name FROM $tbl WHERE extract(YEAROFWEEK from date1) = $yow")
+ checkFilterPushed(df, false)
+ val rows = df.collect()
+ assert(rows.length === 1)
+ assert(rows(0).getString(0) === "tom")
+ }
val df9 = sql(s"SELECT name FROM $tbl WHERE " +
"dayofyear(date1) > 100 order by dayofyear(date1) limit 1")
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 cb132ab11326..4fd605b0f200 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
@@ -80,7 +80,7 @@ public class V2ExpressionSQLBuilder {
} else if (expr instanceof Cast cast) {
return visitCast(build(cast.expression()), cast.expressionDataType(), cast.dataType());
} else if (expr instanceof Extract extract) {
- return visitExtract(extract.field(), build(extract.source()));
+ return visitExtract(extract);
} else if (expr instanceof SortOrder sortOrder) {
return visitSortOrder(
build(sortOrder.expression()), sortOrder.direction(), sortOrder.nullOrdering());
@@ -333,6 +333,10 @@ public class V2ExpressionSQLBuilder {
}
}
+ protected String visitExtract(Extract extract) {
+ return visitExtract(extract.field(), build(extract.source()));
+ }
+
protected String visitExtract(String field, String source) {
return "EXTRACT(" + field + " FROM " + source + ")";
}
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 77f0d19fc930..5dec15b0fbcd 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
@@ -29,7 +29,7 @@ import org.apache.spark.sql.catalyst.SQLConfHelper
import org.apache.spark.sql.catalyst.analysis.{IndexAlreadyExistsException, NoSuchIndexException}
import org.apache.spark.sql.connector.catalog.Identifier
import org.apache.spark.sql.connector.catalog.index.TableIndex
-import org.apache.spark.sql.connector.expressions.{Expression, FieldReference, NamedReference, NullOrdering, SortDirection}
+import org.apache.spark.sql.connector.expressions.{Expression, Extract, FieldReference, NamedReference, NullOrdering, SortDirection}
import org.apache.spark.sql.errors.{QueryCompilationErrors, QueryExecutionErrors}
import org.apache.spark.sql.execution.datasources.jdbc.{JDBCOptions, JdbcUtils}
import org.apache.spark.sql.types._
@@ -51,14 +51,19 @@ private case class MySQLDialect() extends JdbcDialect with SQLConfHelper with No
supportedFunctions.contains(funcName)
class MySQLSQLBuilder extends JDBCSQLBuilder {
- override def visitExtract(field: String, source: String): String = {
+
+ override def visitExtract(extract: Extract): String = {
+ val field = extract.field
field match {
- case "DAY_OF_YEAR" => s"DAYOFYEAR($source)"
- case "YEAR_OF_WEEK" => s"EXTRACT(YEAR FROM $source)"
+ case "DAY_OF_YEAR" => s"DAYOFYEAR(${build(extract.source())})"
+ case "WEEK" => s"WEEKOFYEAR(${build(extract.source())})"
+ case "YEAR_OF_WEEK" => visitUnexpectedExpr(extract)
// WEEKDAY uses Monday = 0, Tuesday = 1, ... and ISO standard is Monday = 1, ...,
// so we use the formula (WEEKDAY + 1) to follow the ISO standard.
- case "DAY_OF_WEEK" => s"(WEEKDAY($source) + 1)"
- case _ => super.visitExtract(field, source)
+ case "DAY_OF_WEEK" => s"(WEEKDAY(${build(extract.source())}) + 1)"
+ // SECOND, MINUTE, HOUR, DAY, MONTH, QUARTER, YEAR are identical on MySQL and Spark for
+ // both datetime and interval types.
+ case _ => super.visitExtract(field, build(extract.source()))
}
}
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@spark.apache.org
For additional commands, e-mail: commits-help@spark.apache.org