This is an automated email from the ASF dual-hosted git repository.
cloud-fan pushed a commit to branch branch-4.x
in repository https://gitbox.apache.org/repos/asf/spark.git
The following commit(s) were added to refs/heads/branch-4.x by this push:
new 3964a48b049b [SPARK-57364][SQL] Fix Oracle TRUNC pushdown to map Spark
format strings to Oracle format
3964a48b049b is described below
commit 3964a48b049b91f391350b8992dd054b2fc217a5
Author: Shrirang Mhalgi <[email protected]>
AuthorDate: Wed Jun 17 21:31:37 2026 -0700
[SPARK-57364][SQL] Fix Oracle TRUNC pushdown to map Spark format strings to
Oracle format
### What changes were proposed in this pull request?
Fix the Oracle TRUNC pushdown to correctly map Spark's date truncation
format strings to their Oracle equivalents, instead of hardcoding `'IW'` (ISO
week) for all levels.
### Why are the changes needed?
`OracleDialect.visitSQLFunction` hardcodes `'IW'` for all TRUNC pushdowns
regardless of the requested truncation level. When a user writes `trunc(col,
'MONTH')`, `trunc(col, 'YEAR')`, or `trunc(col, 'QUARTER')`, the generated SQL
sent to Oracle is always `TRUNC(col, 'IW')` - silently returning week-truncated
dates instead of the correct truncation. Introduced in SPARK-51585.
### Does this PR introduce _any_ user-facing change?
Yes. `trunc()` predicates pushed down to Oracle V2 data sources now produce
correct results for MONTH, YEAR, and QUARTER truncation levels (previously all
returned week-truncated dates).
### How was this patch tested?
Added new unit test in `JDBCSuite` that constructs TRUNC V2 expressions for
each format level (WEEK, MONTH, YEAR, QUARTER), compiles them via
`OracleDialect.compileExpression`, and asserts the correct Oracle format string
appears in the output. The test fails without the fix (produces `'IW'` for all
levels) and passes with the fix.
### Was this patch authored or co-authored using generative AI tooling?
Yes. Authored using Claude-Opus 4.6
Closes #56425 from shrirangmhalgi/SPARK-57364-oracle-trunc-format.
Authored-by: Shrirang Mhalgi <[email protected]>
Signed-off-by: Wenchen Fan <[email protected]>
(cherry picked from commit 80ac691b713f7a58f81f9fcf7201e1cb28337452)
Signed-off-by: Wenchen Fan <[email protected]>
---
.../spark/sql/jdbc/v2/OracleIntegrationSuite.scala | 8 ++++
.../org/apache/spark/sql/jdbc/OracleDialect.scala | 17 +++++++-
.../org/apache/spark/sql/jdbc/JDBCSuite.scala | 46 +++++++++++++++++++++-
3 files changed, 69 insertions(+), 2 deletions(-)
diff --git
a/connector/docker-integration-tests/src/test/scala/org/apache/spark/sql/jdbc/v2/OracleIntegrationSuite.scala
b/connector/docker-integration-tests/src/test/scala/org/apache/spark/sql/jdbc/v2/OracleIntegrationSuite.scala
index c71f9ae7688f..594819689e6f 100644
---
a/connector/docker-integration-tests/src/test/scala/org/apache/spark/sql/jdbc/v2/OracleIntegrationSuite.scala
+++
b/connector/docker-integration-tests/src/test/scala/org/apache/spark/sql/jdbc/v2/OracleIntegrationSuite.scala
@@ -340,5 +340,13 @@ class OracleIntegrationSuite extends
DockerJDBCIntegrationV2Suite with V2JDBCTes
assert(rows10.length === 2)
assert(rows10(0).getString(0) === "amy")
assert(rows10(1).getString(0) === "alex")
+
+ // SPARK-57364: verify MONTH truncation pushes down correctly (not as 'IW')
+ val df11 = sql(s"SELECT name FROM $tbl WHERE trunc(date1, 'MONTH') =
date'2022-05-01'")
+ checkFilterPushed(df11)
+ val rows11 = df11.collect()
+ assert(rows11.length === 2)
+ assert(rows11(0).getString(0) === "amy")
+ assert(rows11(1).getString(0) === "alex")
}
}
diff --git
a/sql/core/src/main/scala/org/apache/spark/sql/jdbc/OracleDialect.scala
b/sql/core/src/main/scala/org/apache/spark/sql/jdbc/OracleDialect.scala
index 6caf1f4b1ff6..d3ef79fdf3f9 100644
--- a/sql/core/src/main/scala/org/apache/spark/sql/jdbc/OracleDialect.scala
+++ b/sql/core/src/main/scala/org/apache/spark/sql/jdbc/OracleDialect.scala
@@ -75,7 +75,22 @@ private case class OracleDialect() extends JdbcDialect with
SQLConfHelper with N
override def visitSQLFunction(funcName: String, inputs: Array[String]):
String = {
funcName match {
case "TRUNC" =>
- s"TRUNC(${inputs(0)}, 'IW')"
+ // Map Spark's trunc format strings to Oracle equivalents.
+ // inputs(1) arrives quoted, e.g. "'MONTH'" (see
JDBCSQLBuilder.visitLiteral).
+ // Case-insensitive: Spark's parseTruncLevel uppercases before
matching.
+ val fmt = inputs(1).toUpperCase(Locale.ROOT)
+ val oracleFormat = fmt match {
+ case "'WEEK'" => "'IW'"
+ case "'MONTH'" | "'MM'" | "'MON'" => "'MM'"
+ case "'QUARTER'" => "'Q'"
+ case "'YEAR'" | "'YYYY'" | "'YY'" => "'YYYY'"
+ case _ =>
+ // Unmapped formats: don't push down. compileExpression catches
the
+ // exception and returns None, so Spark evaluates trunc locally.
+ throw new IllegalArgumentException(
+ s"Unsupported Oracle TRUNC format: ${inputs(1)}")
+ }
+ s"TRUNC(${inputs(0)}, $oracleFormat)"
case _ => super.visitSQLFunction(funcName, inputs)
}
}
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 3c04c517a670..bcd4bd81321a 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
@@ -37,7 +37,7 @@ import org.apache.spark.sql.catalyst.parser.CatalystSqlParser
import org.apache.spark.sql.catalyst.plans.logical.ShowCreateTable
import org.apache.spark.sql.catalyst.util.{CaseInsensitiveMap,
CharVarcharUtils, DateTimeTestUtils}
import org.apache.spark.sql.connector.catalog.Identifier
-import org.apache.spark.sql.connector.expressions.{Expression => V2Expression,
FieldReference}
+import org.apache.spark.sql.connector.expressions.{Expression => V2Expression,
FieldReference, GeneralScalarExpression, LiteralValue}
import org.apache.spark.sql.connector.expressions.filter.{AlwaysFalse,
AlwaysTrue, Predicate}
import org.apache.spark.sql.execution.{DataSourceScanExec, ExtendedMode,
ProjectExec}
import org.apache.spark.sql.execution.command.{ExplainCommand,
ShowCreateTableCommand}
@@ -1532,6 +1532,50 @@ class JDBCSuite extends SharedSparkSession {
assert(getJdbcType(oracleDialect, TimestampNTZType) == "TIMESTAMP")
}
+ test("Oracle TRUNC pushdown should map Spark format strings to Oracle
format") {
+ val oracleDialect = JdbcDialects.get("jdbc:oracle://127.0.0.1/db")
+ val dateRef = FieldReference("d")
+
+ // LiteralValue for StringType must use UTF8String (Spark's internal
string type)
+ // to match what V2ExpressionBuilder produces in the real pushdown path.
+ import org.apache.spark.unsafe.types.UTF8String
+ def truncExpr(fmt: String): GeneralScalarExpression = new
GeneralScalarExpression("TRUNC",
+ Array[V2Expression](dateRef, LiteralValue(UTF8String.fromString(fmt),
StringType)))
+
+ val monthSql = oracleDialect.compileExpression(truncExpr("MONTH")).get
+ assert(monthSql.contains("'MM'"),
+ s"trunc(d, 'MONTH') should produce Oracle 'MM', got: $monthSql")
+ assert(!monthSql.contains("'IW'"),
+ s"trunc(d, 'MONTH') should NOT produce 'IW', got: $monthSql")
+
+ val weekSql = oracleDialect.compileExpression(truncExpr("WEEK")).get
+ assert(weekSql.contains("'IW'"),
+ s"trunc(d, 'WEEK') should produce Oracle 'IW', got: $weekSql")
+
+ val yearSql = oracleDialect.compileExpression(truncExpr("YEAR")).get
+ assert(yearSql.contains("'YYYY'"),
+ s"trunc(d, 'YEAR') should produce Oracle 'YYYY', got: $yearSql")
+
+ val quarterSql = oracleDialect.compileExpression(truncExpr("QUARTER")).get
+ assert(quarterSql.contains("'Q'"),
+ s"trunc(d, 'QUARTER') should produce Oracle 'Q', got: $quarterSql")
+
+ // Case-insensitive: lowercase formats must also map correctly
+ val weekLowerSql = oracleDialect.compileExpression(truncExpr("week")).get
+ assert(weekLowerSql.contains("'IW'"),
+ s"trunc(d, 'week') (lowercase) should produce Oracle 'IW', got:
$weekLowerSql")
+
+ // Unmapped formats should NOT be pushed down (compileExpression returns
None)
+ assert(oracleDialect.compileExpression(truncExpr("DAY")).isEmpty,
+ "Unmapped format 'DAY' should not be pushed down (compileExpression
should return None)")
+
+ // Alias formats (MM, MON, YYYY, YY) should also map correctly
+ val mmSql = oracleDialect.compileExpression(truncExpr("MM")).get
+ assert(mmSql.contains("'MM'"), s"trunc(d, 'MM') should produce Oracle
'MM', got: $mmSql")
+ val yySql = oracleDialect.compileExpression(truncExpr("YY")).get
+ assert(yySql.contains("'YYYY'"), s"trunc(d, 'YY') should produce Oracle
'YYYY', got: $yySql")
+ }
+
private def assertEmptyQuery(sqlString: String): Unit = {
assert(sql(sqlString).collect().isEmpty)
}
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
