This is an automated email from the ASF dual-hosted git repository.
philo pushed a commit to branch main
in repository https://gitbox.apache.org/repos/asf/incubator-gluten.git
The following commit(s) were added to refs/heads/main by this push:
new 61e627b5ec [GLUTEN-10781][VL] Add support for `months_between`
function (#10782)
61e627b5ec is described below
commit 61e627b5ec00c149256b1b3fd2397e835c7bd681
Author: Mingliang Zhu <[email protected]>
AuthorDate: Fri Oct 10 15:55:29 2025 +0800
[GLUTEN-10781][VL] Add support for `months_between` function (#10782)
---
.../clickhouse/CHSparkPlanExecApi.scala | 9 +++
.../expression/CHExpressionTransformer.scala | 13 ++++
.../backendsapi/velox/VeloxSparkPlanExecApi.scala | 9 +++
.../functions/DateFunctionsValidateSuite.scala | 17 +++++
.../gluten/backendsapi/SparkPlanExecApi.scala | 7 ++
.../DateTimeExpressionsTransformer.scala | 3 +-
.../gluten/expression/ExpressionConverter.scala | 2 +-
.../utils/clickhouse/ClickHouseTestSettings.scala | 1 +
.../gluten/utils/velox/VeloxTestSettings.scala | 2 +
.../expressions/GlutenDateExpressionsSuite.scala | 81 +++++++++++++++++++++-
.../utils/clickhouse/ClickHouseTestSettings.scala | 1 +
.../gluten/utils/velox/VeloxTestSettings.scala | 2 +
.../expressions/GlutenDateExpressionsSuite.scala | 79 +++++++++++++++++++++
.../utils/clickhouse/ClickHouseTestSettings.scala | 1 +
.../gluten/utils/velox/VeloxTestSettings.scala | 2 +
.../expressions/GlutenDateExpressionsSuite.scala | 79 +++++++++++++++++++++
.../utils/clickhouse/ClickHouseTestSettings.scala | 1 +
.../gluten/utils/velox/VeloxTestSettings.scala | 2 +
.../expressions/GlutenDateExpressionsSuite.scala | 79 +++++++++++++++++++++
19 files changed, 386 insertions(+), 4 deletions(-)
diff --git
a/backends-clickhouse/src/main/scala/org/apache/gluten/backendsapi/clickhouse/CHSparkPlanExecApi.scala
b/backends-clickhouse/src/main/scala/org/apache/gluten/backendsapi/clickhouse/CHSparkPlanExecApi.scala
index 1291b4d257..dbaccb16c8 100644
---
a/backends-clickhouse/src/main/scala/org/apache/gluten/backendsapi/clickhouse/CHSparkPlanExecApi.scala
+++
b/backends-clickhouse/src/main/scala/org/apache/gluten/backendsapi/clickhouse/CHSparkPlanExecApi.scala
@@ -1043,4 +1043,13 @@ class CHSparkPlanExecApi extends SparkPlanExecApi with
Logging {
extract.get.last,
original)
}
+
+ override def genMonthsBetweenTransformer(
+ substraitExprName: String,
+ date1: ExpressionTransformer,
+ date2: ExpressionTransformer,
+ roundOff: ExpressionTransformer,
+ original: MonthsBetween): ExpressionTransformer = {
+ CHMonthsBetweenTransformer(substraitExprName, date1, date2, roundOff,
original)
+ }
}
diff --git
a/backends-clickhouse/src/main/scala/org/apache/gluten/expression/CHExpressionTransformer.scala
b/backends-clickhouse/src/main/scala/org/apache/gluten/expression/CHExpressionTransformer.scala
index 984fb2f790..d0137b9fd2 100644
---
a/backends-clickhouse/src/main/scala/org/apache/gluten/expression/CHExpressionTransformer.scala
+++
b/backends-clickhouse/src/main/scala/org/apache/gluten/expression/CHExpressionTransformer.scala
@@ -307,3 +307,16 @@ case class CHTimestampAddTransformer(
Seq(LiteralTransformer(unit), left, right, LiteralTransformer(timeZoneId))
}
}
+
+case class CHMonthsBetweenTransformer(
+ substraitExprName: String,
+ date1: ExpressionTransformer,
+ date2: ExpressionTransformer,
+ roundOff: ExpressionTransformer,
+ original: MonthsBetween)
+ extends ExpressionTransformer {
+ override def children: Seq[ExpressionTransformer] = {
+ val timeZoneId = original.timeZoneId.map(timeZoneId =>
LiteralTransformer(timeZoneId))
+ Seq(date1, date2, roundOff) ++ timeZoneId
+ }
+}
diff --git
a/backends-velox/src/main/scala/org/apache/gluten/backendsapi/velox/VeloxSparkPlanExecApi.scala
b/backends-velox/src/main/scala/org/apache/gluten/backendsapi/velox/VeloxSparkPlanExecApi.scala
index 6d46d09c1f..20f52997ab 100644
---
a/backends-velox/src/main/scala/org/apache/gluten/backendsapi/velox/VeloxSparkPlanExecApi.scala
+++
b/backends-velox/src/main/scala/org/apache/gluten/backendsapi/velox/VeloxSparkPlanExecApi.scala
@@ -1064,4 +1064,13 @@ class VeloxSparkPlanExecApi extends SparkPlanExecApi {
original: Expression): ExpressionTransformer = {
ToUnixTimestampTransformer(substraitExprName, timeExp, format, original)
}
+
+ override def genMonthsBetweenTransformer(
+ substraitExprName: String,
+ date1: ExpressionTransformer,
+ date2: ExpressionTransformer,
+ roundOff: ExpressionTransformer,
+ original: MonthsBetween): ExpressionTransformer = {
+ MonthsBetweenTransformer(substraitExprName, date1, date2, roundOff,
original)
+ }
}
diff --git
a/backends-velox/src/test/scala/org/apache/gluten/functions/DateFunctionsValidateSuite.scala
b/backends-velox/src/test/scala/org/apache/gluten/functions/DateFunctionsValidateSuite.scala
index 81e1457021..c3e0165873 100644
---
a/backends-velox/src/test/scala/org/apache/gluten/functions/DateFunctionsValidateSuite.scala
+++
b/backends-velox/src/test/scala/org/apache/gluten/functions/DateFunctionsValidateSuite.scala
@@ -488,4 +488,21 @@ abstract class DateFunctionsValidateSuite extends
FunctionsValidateSuite {
}
}
}
+
+ test("months_between") {
+ withTempPath {
+ path =>
+ val t1 = Timestamp.valueOf("1997-02-28 10:30:00")
+ val t2 = Timestamp.valueOf("1996-10-30 00:00:00")
+ Seq((t1, t2)).toDF("t1", "t2").write.parquet(path.getCanonicalPath)
+
+
spark.read.parquet(path.getCanonicalPath).createOrReplaceTempView("time")
+ runQueryAndCompare("select months_between(t1, t2) from time") {
+ checkGlutenOperatorMatch[ProjectExecTransformer]
+ }
+ runQueryAndCompare("select months_between(t1, t2, false) from time") {
+ checkGlutenOperatorMatch[ProjectExecTransformer]
+ }
+ }
+ }
}
diff --git
a/gluten-substrait/src/main/scala/org/apache/gluten/backendsapi/SparkPlanExecApi.scala
b/gluten-substrait/src/main/scala/org/apache/gluten/backendsapi/SparkPlanExecApi.scala
index fc53cbf6a3..2ee41f0d8d 100644
---
a/gluten-substrait/src/main/scala/org/apache/gluten/backendsapi/SparkPlanExecApi.scala
+++
b/gluten-substrait/src/main/scala/org/apache/gluten/backendsapi/SparkPlanExecApi.scala
@@ -802,6 +802,13 @@ trait SparkPlanExecApi {
throw new GlutenNotSupportException("timestampdiff is not supported")
}
+ def genMonthsBetweenTransformer(
+ substraitExprName: String,
+ date1: ExpressionTransformer,
+ date2: ExpressionTransformer,
+ roundOff: ExpressionTransformer,
+ original: MonthsBetween): ExpressionTransformer
+
def isRowIndexMetadataColumn(columnName: String): Boolean = {
SparkShimLoader.getSparkShims.isRowIndexMetadataColumn(columnName)
}
diff --git
a/gluten-substrait/src/main/scala/org/apache/gluten/expression/DateTimeExpressionsTransformer.scala
b/gluten-substrait/src/main/scala/org/apache/gluten/expression/DateTimeExpressionsTransformer.scala
index 2e2611d5ab..4385419b02 100644
---
a/gluten-substrait/src/main/scala/org/apache/gluten/expression/DateTimeExpressionsTransformer.scala
+++
b/gluten-substrait/src/main/scala/org/apache/gluten/expression/DateTimeExpressionsTransformer.scala
@@ -56,8 +56,7 @@ case class MonthsBetweenTransformer(
original: MonthsBetween)
extends ExpressionTransformer {
override def children: Seq[ExpressionTransformer] = {
- val timeZoneId = original.timeZoneId.map(timeZoneId =>
LiteralTransformer(timeZoneId))
- Seq(date1, date2, roundOff) ++ timeZoneId
+ Seq(date1, date2, roundOff)
}
}
diff --git
a/gluten-substrait/src/main/scala/org/apache/gluten/expression/ExpressionConverter.scala
b/gluten-substrait/src/main/scala/org/apache/gluten/expression/ExpressionConverter.scala
index 37f90d512e..43837274a2 100644
---
a/gluten-substrait/src/main/scala/org/apache/gluten/expression/ExpressionConverter.scala
+++
b/gluten-substrait/src/main/scala/org/apache/gluten/expression/ExpressionConverter.scala
@@ -328,7 +328,7 @@ object ExpressionConverter extends SQLConfHelper with
Logging {
t
)
case m: MonthsBetween =>
- MonthsBetweenTransformer(
+
BackendsApiManager.getSparkPlanExecApiInstance.genMonthsBetweenTransformer(
substraitExprName,
replaceWithExpressionTransformer0(m.date1, attributeSeq,
expressionsMap),
replaceWithExpressionTransformer0(m.date2, attributeSeq,
expressionsMap),
diff --git
a/gluten-ut/spark32/src/test/scala/org/apache/gluten/utils/clickhouse/ClickHouseTestSettings.scala
b/gluten-ut/spark32/src/test/scala/org/apache/gluten/utils/clickhouse/ClickHouseTestSettings.scala
index 2d867a3226..7f9bdba52b 100644
---
a/gluten-ut/spark32/src/test/scala/org/apache/gluten/utils/clickhouse/ClickHouseTestSettings.scala
+++
b/gluten-ut/spark32/src/test/scala/org/apache/gluten/utils/clickhouse/ClickHouseTestSettings.scala
@@ -695,6 +695,7 @@ class ClickHouseTestSettings extends BackendTestSettings {
.exclude("add_months")
.exclude("SPARK-34721: add a year-month interval to a date")
.exclude("months_between")
+ .excludeGlutenTest("months_between")
.exclude("next_day")
.exclude("TruncDate")
.exclude("TruncTimestamp")
diff --git
a/gluten-ut/spark32/src/test/scala/org/apache/gluten/utils/velox/VeloxTestSettings.scala
b/gluten-ut/spark32/src/test/scala/org/apache/gluten/utils/velox/VeloxTestSettings.scala
index dcf598887d..43a3250fc9 100644
---
a/gluten-ut/spark32/src/test/scala/org/apache/gluten/utils/velox/VeloxTestSettings.scala
+++
b/gluten-ut/spark32/src/test/scala/org/apache/gluten/utils/velox/VeloxTestSettings.scala
@@ -219,6 +219,8 @@ class VeloxTestSettings extends BackendTestSettings {
.exclude("to_timestamp exception mode")
// Replaced by a gluten test to pass timezone through config.
.exclude("from_unixtime")
+ // Replaced by a gluten test to pass timezone through config.
+ .exclude("months_between")
//
https://github.com/facebookincubator/velox/pull/10563/files#diff-140dc50e6dac735f72d29014da44b045509df0dd1737f458de1fe8cfd33d8145
.excludeGlutenTest("from_unixtime")
enableSuite[GlutenDecimalExpressionSuite]
diff --git
a/gluten-ut/spark32/src/test/scala/org/apache/spark/sql/catalyst/expressions/GlutenDateExpressionsSuite.scala
b/gluten-ut/spark32/src/test/scala/org/apache/spark/sql/catalyst/expressions/GlutenDateExpressionsSuite.scala
index dfa3640679..9c215f13bd 100644
---
a/gluten-ut/spark32/src/test/scala/org/apache/spark/sql/catalyst/expressions/GlutenDateExpressionsSuite.scala
+++
b/gluten-ut/spark32/src/test/scala/org/apache/spark/sql/catalyst/expressions/GlutenDateExpressionsSuite.scala
@@ -23,7 +23,7 @@ import org.apache.spark.sql.catalyst.util.DateTimeTestUtils._
import org.apache.spark.sql.catalyst.util.DateTimeUtils
import org.apache.spark.sql.catalyst.util.DateTimeUtils.{getZoneId,
TimeZoneUTC}
import org.apache.spark.sql.internal.SQLConf
-import org.apache.spark.sql.types.{DateType, IntegerType, LongType,
StringType, TimestampNTZType, TimestampType}
+import org.apache.spark.sql.types._
import org.apache.spark.unsafe.types.UTF8String
import java.sql.{Date, Timestamp}
@@ -476,4 +476,83 @@ class GlutenDateExpressionsSuite extends
DateExpressionsSuite with GlutenTestsTr
}
}
}
+
+ testGluten("months_between") {
+ val sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss", Locale.US)
+ for (zid <- outstandingZoneIds) {
+ withSQLConf(
+ SQLConf.SESSION_LOCAL_TIMEZONE.key -> zid.getId
+ ) {
+ val timeZoneId = Option(zid.getId)
+ sdf.setTimeZone(TimeZone.getTimeZone(zid))
+
+ checkEvaluation(
+ MonthsBetween(
+ Literal(new Timestamp(sdf.parse("1997-02-28 10:30:00").getTime)),
+ Literal(new Timestamp(sdf.parse("1996-10-30 00:00:00").getTime)),
+ Literal.TrueLiteral,
+ timeZoneId = timeZoneId
+ ),
+ 3.94959677
+ )
+ checkEvaluation(
+ MonthsBetween(
+ Literal(new Timestamp(sdf.parse("1997-02-28 10:30:00").getTime)),
+ Literal(new Timestamp(sdf.parse("1996-10-30 00:00:00").getTime)),
+ Literal.FalseLiteral,
+ timeZoneId = timeZoneId
+ ),
+ 3.9495967741935485
+ )
+
+ Seq(Literal.FalseLiteral, Literal.TrueLiteral).foreach {
+ roundOff =>
+ checkEvaluation(
+ MonthsBetween(
+ Literal(new Timestamp(sdf.parse("2015-01-30
11:52:00").getTime)),
+ Literal(new Timestamp(sdf.parse("2015-01-30
11:50:00").getTime)),
+ roundOff,
+ timeZoneId = timeZoneId
+ ),
+ 0.0
+ )
+ checkEvaluation(
+ MonthsBetween(
+ Literal(new Timestamp(sdf.parse("2015-01-31
00:00:00").getTime)),
+ Literal(new Timestamp(sdf.parse("2015-03-31
22:00:00").getTime)),
+ roundOff,
+ timeZoneId = timeZoneId
+ ),
+ -2.0
+ )
+ checkEvaluation(
+ MonthsBetween(
+ Literal(new Timestamp(sdf.parse("2015-03-31
22:00:00").getTime)),
+ Literal(new Timestamp(sdf.parse("2015-02-28
00:00:00").getTime)),
+ roundOff,
+ timeZoneId = timeZoneId
+ ),
+ 1.0
+ )
+ }
+ val t = Literal(Timestamp.valueOf("2015-03-31 22:00:00"))
+ val tnull = Literal.create(null, TimestampType)
+ checkEvaluation(MonthsBetween(t, tnull, Literal.TrueLiteral,
timeZoneId = timeZoneId), null)
+ checkEvaluation(MonthsBetween(tnull, t, Literal.TrueLiteral,
timeZoneId = timeZoneId), null)
+ checkEvaluation(
+ MonthsBetween(tnull, tnull, Literal.TrueLiteral, timeZoneId =
timeZoneId),
+ null)
+ checkEvaluation(
+ MonthsBetween(t, t, Literal.create(null, BooleanType), timeZoneId =
timeZoneId),
+ null)
+ checkConsistencyBetweenInterpretedAndCodegen(
+ (time1: Expression, time2: Expression, roundOff: Expression) =>
+ MonthsBetween(time1, time2, roundOff, timeZoneId = timeZoneId),
+ TimestampType,
+ TimestampType,
+ BooleanType
+ )
+ }
+ }
+ }
}
diff --git
a/gluten-ut/spark33/src/test/scala/org/apache/gluten/utils/clickhouse/ClickHouseTestSettings.scala
b/gluten-ut/spark33/src/test/scala/org/apache/gluten/utils/clickhouse/ClickHouseTestSettings.scala
index d0979b5fea..6ce7d1e325 100644
---
a/gluten-ut/spark33/src/test/scala/org/apache/gluten/utils/clickhouse/ClickHouseTestSettings.scala
+++
b/gluten-ut/spark33/src/test/scala/org/apache/gluten/utils/clickhouse/ClickHouseTestSettings.scala
@@ -717,6 +717,7 @@ class ClickHouseTestSettings extends BackendTestSettings {
.exclude("add_months")
.exclude("SPARK-34721: add a year-month interval to a date")
.exclude("months_between")
+ .excludeGlutenTest("months_between")
.exclude("next_day")
.exclude("TruncDate")
.exclude("TruncTimestamp")
diff --git
a/gluten-ut/spark33/src/test/scala/org/apache/gluten/utils/velox/VeloxTestSettings.scala
b/gluten-ut/spark33/src/test/scala/org/apache/gluten/utils/velox/VeloxTestSettings.scala
index 2b0c748339..52ce14bda3 100644
---
a/gluten-ut/spark33/src/test/scala/org/apache/gluten/utils/velox/VeloxTestSettings.scala
+++
b/gluten-ut/spark33/src/test/scala/org/apache/gluten/utils/velox/VeloxTestSettings.scala
@@ -141,6 +141,8 @@ class VeloxTestSettings extends BackendTestSettings {
.exclude("to_timestamp exception mode")
// Replaced by a gluten test to pass timezone through config.
.exclude("from_unixtime")
+ // Replaced by a gluten test to pass timezone through config.
+ .exclude("months_between")
.exclude("test timestamp add")
//
https://github.com/facebookincubator/velox/pull/10563/files#diff-140dc50e6dac735f72d29014da44b045509df0dd1737f458de1fe8cfd33d8145
.excludeGlutenTest("from_unixtime")
diff --git
a/gluten-ut/spark33/src/test/scala/org/apache/spark/sql/catalyst/expressions/GlutenDateExpressionsSuite.scala
b/gluten-ut/spark33/src/test/scala/org/apache/spark/sql/catalyst/expressions/GlutenDateExpressionsSuite.scala
index 34c2358fe7..8c494699c1 100644
---
a/gluten-ut/spark33/src/test/scala/org/apache/spark/sql/catalyst/expressions/GlutenDateExpressionsSuite.scala
+++
b/gluten-ut/spark33/src/test/scala/org/apache/spark/sql/catalyst/expressions/GlutenDateExpressionsSuite.scala
@@ -496,4 +496,83 @@ class GlutenDateExpressionsSuite extends
DateExpressionsSuite with GlutenTestsTr
TimestampAdd("YEAR", Literal(1), Literal(Timestamp.valueOf("2022-02-15
12:57:00"))),
Timestamp.valueOf("2023-02-15 12:57:00"))
}
+
+ testGluten("months_between") {
+ val sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss", Locale.US)
+ for (zid <- outstandingZoneIds) {
+ withSQLConf(
+ SQLConf.SESSION_LOCAL_TIMEZONE.key -> zid.getId
+ ) {
+ val timeZoneId = Option(zid.getId)
+ sdf.setTimeZone(TimeZone.getTimeZone(zid))
+
+ checkEvaluation(
+ MonthsBetween(
+ Literal(new Timestamp(sdf.parse("1997-02-28 10:30:00").getTime)),
+ Literal(new Timestamp(sdf.parse("1996-10-30 00:00:00").getTime)),
+ Literal.TrueLiteral,
+ timeZoneId = timeZoneId
+ ),
+ 3.94959677
+ )
+ checkEvaluation(
+ MonthsBetween(
+ Literal(new Timestamp(sdf.parse("1997-02-28 10:30:00").getTime)),
+ Literal(new Timestamp(sdf.parse("1996-10-30 00:00:00").getTime)),
+ Literal.FalseLiteral,
+ timeZoneId = timeZoneId
+ ),
+ 3.9495967741935485
+ )
+
+ Seq(Literal.FalseLiteral, Literal.TrueLiteral).foreach {
+ roundOff =>
+ checkEvaluation(
+ MonthsBetween(
+ Literal(new Timestamp(sdf.parse("2015-01-30
11:52:00").getTime)),
+ Literal(new Timestamp(sdf.parse("2015-01-30
11:50:00").getTime)),
+ roundOff,
+ timeZoneId = timeZoneId
+ ),
+ 0.0
+ )
+ checkEvaluation(
+ MonthsBetween(
+ Literal(new Timestamp(sdf.parse("2015-01-31
00:00:00").getTime)),
+ Literal(new Timestamp(sdf.parse("2015-03-31
22:00:00").getTime)),
+ roundOff,
+ timeZoneId = timeZoneId
+ ),
+ -2.0
+ )
+ checkEvaluation(
+ MonthsBetween(
+ Literal(new Timestamp(sdf.parse("2015-03-31
22:00:00").getTime)),
+ Literal(new Timestamp(sdf.parse("2015-02-28
00:00:00").getTime)),
+ roundOff,
+ timeZoneId = timeZoneId
+ ),
+ 1.0
+ )
+ }
+ val t = Literal(Timestamp.valueOf("2015-03-31 22:00:00"))
+ val tnull = Literal.create(null, TimestampType)
+ checkEvaluation(MonthsBetween(t, tnull, Literal.TrueLiteral,
timeZoneId = timeZoneId), null)
+ checkEvaluation(MonthsBetween(tnull, t, Literal.TrueLiteral,
timeZoneId = timeZoneId), null)
+ checkEvaluation(
+ MonthsBetween(tnull, tnull, Literal.TrueLiteral, timeZoneId =
timeZoneId),
+ null)
+ checkEvaluation(
+ MonthsBetween(t, t, Literal.create(null, BooleanType), timeZoneId =
timeZoneId),
+ null)
+ checkConsistencyBetweenInterpretedAndCodegen(
+ (time1: Expression, time2: Expression, roundOff: Expression) =>
+ MonthsBetween(time1, time2, roundOff, timeZoneId = timeZoneId),
+ TimestampType,
+ TimestampType,
+ BooleanType
+ )
+ }
+ }
+ }
}
diff --git
a/gluten-ut/spark34/src/test/scala/org/apache/gluten/utils/clickhouse/ClickHouseTestSettings.scala
b/gluten-ut/spark34/src/test/scala/org/apache/gluten/utils/clickhouse/ClickHouseTestSettings.scala
index 36beac1eb4..21cf94a61e 100644
---
a/gluten-ut/spark34/src/test/scala/org/apache/gluten/utils/clickhouse/ClickHouseTestSettings.scala
+++
b/gluten-ut/spark34/src/test/scala/org/apache/gluten/utils/clickhouse/ClickHouseTestSettings.scala
@@ -601,6 +601,7 @@ class ClickHouseTestSettings extends BackendTestSettings {
.exclude("add_months")
.exclude("SPARK-34721: add a year-month interval to a date")
.exclude("months_between")
+ .excludeGlutenTest("months_between")
.exclude("next_day")
.exclude("TruncDate")
.exclude("TruncTimestamp")
diff --git
a/gluten-ut/spark34/src/test/scala/org/apache/gluten/utils/velox/VeloxTestSettings.scala
b/gluten-ut/spark34/src/test/scala/org/apache/gluten/utils/velox/VeloxTestSettings.scala
index 3cc38a2e98..519cef5b76 100644
---
a/gluten-ut/spark34/src/test/scala/org/apache/gluten/utils/velox/VeloxTestSettings.scala
+++
b/gluten-ut/spark34/src/test/scala/org/apache/gluten/utils/velox/VeloxTestSettings.scala
@@ -137,6 +137,8 @@ class VeloxTestSettings extends BackendTestSettings {
.exclude("to_timestamp exception mode")
// Replaced by a gluten test to pass timezone through config.
.exclude("from_unixtime")
+ // Replaced by a gluten test to pass timezone through config.
+ .exclude("months_between")
// Vanilla Spark does not have a unified DST Timestamp fastTime.
1320570000000L and
// 1320566400000L both represent 2011-11-06 01:00:00
.exclude("SPARK-42635: timestampadd near daylight saving transition")
diff --git
a/gluten-ut/spark34/src/test/scala/org/apache/spark/sql/catalyst/expressions/GlutenDateExpressionsSuite.scala
b/gluten-ut/spark34/src/test/scala/org/apache/spark/sql/catalyst/expressions/GlutenDateExpressionsSuite.scala
index 794db27c12..c290322850 100644
---
a/gluten-ut/spark34/src/test/scala/org/apache/spark/sql/catalyst/expressions/GlutenDateExpressionsSuite.scala
+++
b/gluten-ut/spark34/src/test/scala/org/apache/spark/sql/catalyst/expressions/GlutenDateExpressionsSuite.scala
@@ -578,4 +578,83 @@ class GlutenDateExpressionsSuite extends
DateExpressionsSuite with GlutenTestsTr
repeatedTime)
}
}
+
+ testGluten("months_between") {
+ val sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss", Locale.US)
+ for (zid <- outstandingZoneIds) {
+ withSQLConf(
+ SQLConf.SESSION_LOCAL_TIMEZONE.key -> zid.getId
+ ) {
+ val timeZoneId = Option(zid.getId)
+ sdf.setTimeZone(TimeZone.getTimeZone(zid))
+
+ checkEvaluation(
+ MonthsBetween(
+ Literal(new Timestamp(sdf.parse("1997-02-28 10:30:00").getTime)),
+ Literal(new Timestamp(sdf.parse("1996-10-30 00:00:00").getTime)),
+ Literal.TrueLiteral,
+ timeZoneId = timeZoneId
+ ),
+ 3.94959677
+ )
+ checkEvaluation(
+ MonthsBetween(
+ Literal(new Timestamp(sdf.parse("1997-02-28 10:30:00").getTime)),
+ Literal(new Timestamp(sdf.parse("1996-10-30 00:00:00").getTime)),
+ Literal.FalseLiteral,
+ timeZoneId = timeZoneId
+ ),
+ 3.9495967741935485
+ )
+
+ Seq(Literal.FalseLiteral, Literal.TrueLiteral).foreach {
+ roundOff =>
+ checkEvaluation(
+ MonthsBetween(
+ Literal(new Timestamp(sdf.parse("2015-01-30
11:52:00").getTime)),
+ Literal(new Timestamp(sdf.parse("2015-01-30
11:50:00").getTime)),
+ roundOff,
+ timeZoneId = timeZoneId
+ ),
+ 0.0
+ )
+ checkEvaluation(
+ MonthsBetween(
+ Literal(new Timestamp(sdf.parse("2015-01-31
00:00:00").getTime)),
+ Literal(new Timestamp(sdf.parse("2015-03-31
22:00:00").getTime)),
+ roundOff,
+ timeZoneId = timeZoneId
+ ),
+ -2.0
+ )
+ checkEvaluation(
+ MonthsBetween(
+ Literal(new Timestamp(sdf.parse("2015-03-31
22:00:00").getTime)),
+ Literal(new Timestamp(sdf.parse("2015-02-28
00:00:00").getTime)),
+ roundOff,
+ timeZoneId = timeZoneId
+ ),
+ 1.0
+ )
+ }
+ val t = Literal(Timestamp.valueOf("2015-03-31 22:00:00"))
+ val tnull = Literal.create(null, TimestampType)
+ checkEvaluation(MonthsBetween(t, tnull, Literal.TrueLiteral,
timeZoneId = timeZoneId), null)
+ checkEvaluation(MonthsBetween(tnull, t, Literal.TrueLiteral,
timeZoneId = timeZoneId), null)
+ checkEvaluation(
+ MonthsBetween(tnull, tnull, Literal.TrueLiteral, timeZoneId =
timeZoneId),
+ null)
+ checkEvaluation(
+ MonthsBetween(t, t, Literal.create(null, BooleanType), timeZoneId =
timeZoneId),
+ null)
+ checkConsistencyBetweenInterpretedAndCodegen(
+ (time1: Expression, time2: Expression, roundOff: Expression) =>
+ MonthsBetween(time1, time2, roundOff, timeZoneId = timeZoneId),
+ TimestampType,
+ TimestampType,
+ BooleanType
+ )
+ }
+ }
+ }
}
diff --git
a/gluten-ut/spark35/src/test/scala/org/apache/gluten/utils/clickhouse/ClickHouseTestSettings.scala
b/gluten-ut/spark35/src/test/scala/org/apache/gluten/utils/clickhouse/ClickHouseTestSettings.scala
index f30f1543a4..e7e3ddf8a0 100644
---
a/gluten-ut/spark35/src/test/scala/org/apache/gluten/utils/clickhouse/ClickHouseTestSettings.scala
+++
b/gluten-ut/spark35/src/test/scala/org/apache/gluten/utils/clickhouse/ClickHouseTestSettings.scala
@@ -696,6 +696,7 @@ class ClickHouseTestSettings extends BackendTestSettings {
.excludeCH("WeekOfYear")
.excludeCH("add_months")
.excludeCH("months_between")
+ .excludeGlutenTest("months_between")
.excludeCH("TruncDate")
.excludeCH("unsupported fmt fields for trunc/date_trunc results null")
.excludeCH("to_utc_timestamp")
diff --git
a/gluten-ut/spark35/src/test/scala/org/apache/gluten/utils/velox/VeloxTestSettings.scala
b/gluten-ut/spark35/src/test/scala/org/apache/gluten/utils/velox/VeloxTestSettings.scala
index 4088e08ab0..27af909029 100644
---
a/gluten-ut/spark35/src/test/scala/org/apache/gluten/utils/velox/VeloxTestSettings.scala
+++
b/gluten-ut/spark35/src/test/scala/org/apache/gluten/utils/velox/VeloxTestSettings.scala
@@ -137,6 +137,8 @@ class VeloxTestSettings extends BackendTestSettings {
.exclude("to_timestamp exception mode")
// Replaced by a gluten test to pass timezone through config.
.exclude("from_unixtime")
+ // Replaced by a gluten test to pass timezone through config.
+ .exclude("months_between")
// Vanilla Spark does not have a unified DST Timestamp fastTime.
1320570000000L and
// 1320566400000L both represent 2011-11-06 01:00:00.
.exclude("SPARK-42635: timestampadd near daylight saving transition")
diff --git
a/gluten-ut/spark35/src/test/scala/org/apache/spark/sql/catalyst/expressions/GlutenDateExpressionsSuite.scala
b/gluten-ut/spark35/src/test/scala/org/apache/spark/sql/catalyst/expressions/GlutenDateExpressionsSuite.scala
index d53c9187d3..da68989a33 100644
---
a/gluten-ut/spark35/src/test/scala/org/apache/spark/sql/catalyst/expressions/GlutenDateExpressionsSuite.scala
+++
b/gluten-ut/spark35/src/test/scala/org/apache/spark/sql/catalyst/expressions/GlutenDateExpressionsSuite.scala
@@ -578,4 +578,83 @@ class GlutenDateExpressionsSuite extends
DateExpressionsSuite with GlutenTestsTr
repeatedTime)
}
}
+
+ testGluten("months_between") {
+ val sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss", Locale.US)
+ for (zid <- outstandingZoneIds) {
+ withSQLConf(
+ SQLConf.SESSION_LOCAL_TIMEZONE.key -> zid.getId
+ ) {
+ val timeZoneId = Option(zid.getId)
+ sdf.setTimeZone(TimeZone.getTimeZone(zid))
+
+ checkEvaluation(
+ MonthsBetween(
+ Literal(new Timestamp(sdf.parse("1997-02-28 10:30:00").getTime)),
+ Literal(new Timestamp(sdf.parse("1996-10-30 00:00:00").getTime)),
+ Literal.TrueLiteral,
+ timeZoneId = timeZoneId
+ ),
+ 3.94959677
+ )
+ checkEvaluation(
+ MonthsBetween(
+ Literal(new Timestamp(sdf.parse("1997-02-28 10:30:00").getTime)),
+ Literal(new Timestamp(sdf.parse("1996-10-30 00:00:00").getTime)),
+ Literal.FalseLiteral,
+ timeZoneId = timeZoneId
+ ),
+ 3.9495967741935485
+ )
+
+ Seq(Literal.FalseLiteral, Literal.TrueLiteral).foreach {
+ roundOff =>
+ checkEvaluation(
+ MonthsBetween(
+ Literal(new Timestamp(sdf.parse("2015-01-30
11:52:00").getTime)),
+ Literal(new Timestamp(sdf.parse("2015-01-30
11:50:00").getTime)),
+ roundOff,
+ timeZoneId = timeZoneId
+ ),
+ 0.0
+ )
+ checkEvaluation(
+ MonthsBetween(
+ Literal(new Timestamp(sdf.parse("2015-01-31
00:00:00").getTime)),
+ Literal(new Timestamp(sdf.parse("2015-03-31
22:00:00").getTime)),
+ roundOff,
+ timeZoneId = timeZoneId
+ ),
+ -2.0
+ )
+ checkEvaluation(
+ MonthsBetween(
+ Literal(new Timestamp(sdf.parse("2015-03-31
22:00:00").getTime)),
+ Literal(new Timestamp(sdf.parse("2015-02-28
00:00:00").getTime)),
+ roundOff,
+ timeZoneId = timeZoneId
+ ),
+ 1.0
+ )
+ }
+ val t = Literal(Timestamp.valueOf("2015-03-31 22:00:00"))
+ val tnull = Literal.create(null, TimestampType)
+ checkEvaluation(MonthsBetween(t, tnull, Literal.TrueLiteral,
timeZoneId = timeZoneId), null)
+ checkEvaluation(MonthsBetween(tnull, t, Literal.TrueLiteral,
timeZoneId = timeZoneId), null)
+ checkEvaluation(
+ MonthsBetween(tnull, tnull, Literal.TrueLiteral, timeZoneId =
timeZoneId),
+ null)
+ checkEvaluation(
+ MonthsBetween(t, t, Literal.create(null, BooleanType), timeZoneId =
timeZoneId),
+ null)
+ checkConsistencyBetweenInterpretedAndCodegen(
+ (time1: Expression, time2: Expression, roundOff: Expression) =>
+ MonthsBetween(time1, time2, roundOff, timeZoneId = timeZoneId),
+ TimestampType,
+ TimestampType,
+ BooleanType
+ )
+ }
+ }
+ }
}
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
