This is an automated email from the ASF dual-hosted git repository.
HyukjinKwon 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 9021ba7ad32a [SPARK-60572][SQL] Preserve NULL string day counts in
date arithmetic
9021ba7ad32a is described below
commit 9021ba7ad32a3956a0263ed2c2d2471ed120a9e8
Author: Linhong Liu <[email protected]>
AuthorDate: Wed Oct 28 10:22:57 2026 -0700
[SPARK-60572][SQL] Preserve NULL string day counts in date arithmetic
### What changes were proposed in this pull request?
Fix `date_add` and `date_sub` to preserve a foldable NULL string day count
during
type coercion. Keep the cast result boxed and construct an INT literal with
`Literal.create`, so NULL does not become zero.
### Why are the changes needed?
With ANSI mode disabled, string day counts are evaluated during analysis.
Unboxing
a NULL cast result with `asInstanceOf[Int]` produces zero, causing these
functions
to return the input date instead of NULL.
### Does this PR introduce _any_ user-facing change?
Yes, a bug fix. For example:
```sql
SET spark.sql.ansi.enabled=false;
SELECT date_add(DATE '2020-01-01', CAST(NULL AS STRING)),
date_sub(DATE '2020-01-01', CAST(NULL AS STRING));
```
Previously this returned `2020-01-01, 2020-01-01`; it now returns `NULL,
NULL`.
Valid non-null string day counts and invalid-string errors are unchanged.
### How was this patch tested?
Added coercion and SQL regression coverage for direct NULL strings, foldable
conditional/cast expressions, ANSI mode on and off, valid string day
counts, and
invalid-string errors. The new coercion test fails without the fix because
the
day-count literal is `0` instead of NULL.
Passed on JDK 17:
```bash
build/sbt 'catalyst/testOnly
org.apache.spark.sql.catalyst.analysis.TypeCoercionSuite'
build/sbt 'sql/testOnly org.apache.spark.sql.DateFunctionsSuite -- -z "date
arithmetic" -z "function date_add" -z "function date_sub"'
```
The commands passed 63 Catalyst tests and five SQL tests. Catalyst
main/test and
SQL test Scalastyle checks also passed, along with `git diff --check`.
### Was this patch authored or co-authored using generative AI tooling?
Generated-by: OpenAI Codex (GPT-6)
Closes #59806 from linhongliu-db/fix-null-date-day-counts.
Authored-by: Linhong Liu <[email protected]>
Signed-off-by: Hyukjin Kwon <[email protected]>
(cherry picked from commit 576c2dbeb5d0498f8c1afd6c6b6b9eff2711b6bb)
Signed-off-by: Hyukjin Kwon <[email protected]>
---
.../analysis/StringLiteralTypeCoercion.scala | 10 ++++---
.../sql/catalyst/analysis/TypeCoercionSuite.scala | 17 ++++++++++++
.../org/apache/spark/sql/DateFunctionsSuite.scala | 31 ++++++++++++++++++++++
3 files changed, 54 insertions(+), 4 deletions(-)
diff --git
a/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/analysis/StringLiteralTypeCoercion.scala
b/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/analysis/StringLiteralTypeCoercion.scala
index d1b63dc90b06..c6a3de0617d2 100644
---
a/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/analysis/StringLiteralTypeCoercion.scala
+++
b/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/analysis/StringLiteralTypeCoercion.scala
@@ -26,23 +26,25 @@ import org.apache.spark.sql.types.{IntegerType, StringType}
* type coerce the second argument to [[IntegerType]], if necessary.
*/
object StringLiteralTypeCoercion {
+ // Keep cast results boxed: unboxing a NULL as Int yields zero, which would
make
+ // date_add/date_sub return the input date instead of NULL.
def apply(expression: Expression): Expression = expression match {
case DateAdd(l, r) if r.dataType.isInstanceOf[StringType] && r.foldable =>
val days = try {
- Cast(r, IntegerType, ansiEnabled = true).eval().asInstanceOf[Int]
+ Cast(r, IntegerType, ansiEnabled = true).eval()
} catch {
case e: NumberFormatException =>
throw
QueryCompilationErrors.secondArgumentOfFunctionIsNotIntegerError("date_add", e)
}
- DateAdd(l, Literal(days))
+ DateAdd(l, Literal.create(days, IntegerType))
case DateSub(l, r) if r.dataType.isInstanceOf[StringType] && r.foldable =>
val days = try {
- Cast(r, IntegerType, ansiEnabled = true).eval().asInstanceOf[Int]
+ Cast(r, IntegerType, ansiEnabled = true).eval()
} catch {
case e: NumberFormatException =>
throw
QueryCompilationErrors.secondArgumentOfFunctionIsNotIntegerError("date_sub", e)
}
- DateSub(l, Literal(days))
+ DateSub(l, Literal.create(days, IntegerType))
case other => other
}
}
diff --git
a/sql/catalyst/src/test/scala/org/apache/spark/sql/catalyst/analysis/TypeCoercionSuite.scala
b/sql/catalyst/src/test/scala/org/apache/spark/sql/catalyst/analysis/TypeCoercionSuite.scala
index d2d0823ccea8..df3e58da673f 100644
---
a/sql/catalyst/src/test/scala/org/apache/spark/sql/catalyst/analysis/TypeCoercionSuite.scala
+++
b/sql/catalyst/src/test/scala/org/apache/spark/sql/catalyst/analysis/TypeCoercionSuite.scala
@@ -1938,6 +1938,23 @@ class TypeCoercionSuite extends TypeCoercionSuiteBase {
ruleTest(rules, Divide(nullLit, 1L), Divide(Cast(nullLit, DoubleType),
Cast(1L, DoubleType)))
}
+ test("date arithmetic preserves foldable NULL string day counts") {
+ val date = Literal(0, DateType)
+ val nullString = Literal.create(null, StringType)
+ for (days <- Seq(
+ nullString,
+ If(Literal(true), nullString, Literal("2")),
+ Cast(Literal.create(null, IntegerType), StringType))) {
+ for (expression <- Seq(DateAdd(date, days), DateSub(date, days))) {
+ val coerced = StringLiteralTypeCoercion(expression)
+ assert(coerced.children(1) == Literal.create(null, IntegerType))
+ assert(coerced.eval() == null)
+ }
+ }
+ assert(StringLiteralTypeCoercion(DateAdd(date, Literal("2"))).eval() == 2)
+ assert(StringLiteralTypeCoercion(DateSub(date, Literal("2"))).eval() == -2)
+ }
+
test("Do not promote strings in binary arithmetic with intervals") {
val rule = TypeCoercion.PromoteStrings
// Verify String literal is not promoted in binary arithmetic operations
with
diff --git
a/sql/core/src/test/scala/org/apache/spark/sql/DateFunctionsSuite.scala
b/sql/core/src/test/scala/org/apache/spark/sql/DateFunctionsSuite.scala
index 693eb3b86b0c..c46ca8ffcb8d 100644
--- a/sql/core/src/test/scala/org/apache/spark/sql/DateFunctionsSuite.scala
+++ b/sql/core/src/test/scala/org/apache/spark/sql/DateFunctionsSuite.scala
@@ -445,6 +445,37 @@ class DateFunctionsSuite extends SharedSparkSession {
Seq(Row(Date.valueOf("2015-05-31")), Row(Date.valueOf("2015-06-01"))))
}
+ gridTest("date arithmetic preserves NULL string day counts")(Seq(false,
true)) { ansiEnabled =>
+ withSQLConf(SQLConf.ANSI_ENABLED.key -> ansiEnabled.toString) {
+ for (days <- Seq(
+ "CAST(NULL AS STRING)",
+ "IF(true, CAST(NULL AS STRING), '2')",
+ "CAST(CAST(NULL AS INT) AS STRING)")) {
+ checkAnswer(
+ sql(s"SELECT date_add(DATE '2020-01-01', $days), " +
+ s"date_sub(DATE '2020-01-01', $days)"),
+ Row(null, null))
+ }
+ checkAnswer(
+ sql("SELECT date_add(DATE '2020-01-01', '2'), date_sub(DATE
'2020-01-01', '2')"),
+ Row(Date.valueOf("2020-01-03"), Date.valueOf("2019-12-30")))
+ }
+ }
+
+ test("date arithmetic rejects invalid string day counts in non-ANSI mode") {
+ withSQLConf(SQLConf.ANSI_ENABLED.key -> "false") {
+ for (function <- Seq("date_add", "date_sub"); days <- Seq("x", "1.5")) {
+ checkError(
+ exception = intercept[AnalysisException] {
+ sql(s"SELECT $function(DATE '2020-01-01', '$days')").collect()
+ },
+ condition = "SECOND_FUNCTION_ARGUMENT_NOT_INTEGER",
+ parameters = Map("functionName" -> function),
+ sqlState = "22023")
+ }
+ }
+ }
+
test("time_add") {
val t1 = Timestamp.valueOf("2015-07-31 23:59:59")
val t2 = Timestamp.valueOf("2015-12-31 00:00:00")
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
