This is an automated email from the ASF dual-hosted git repository.
MaxGekk 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 8a0b0f4d5f57 [SPARK-57585][SQL] Resolve a common TIME(p) type for
mixed-precision operands in set and conditional operations
8a0b0f4d5f57 is described below
commit 8a0b0f4d5f57551412f5dc44a72ea1699090e2d0
Author: Maxim Gekk <[email protected]>
AuthorDate: Fri Jun 26 15:09:37 2026 +0200
[SPARK-57585][SQL] Resolve a common TIME(p) type for mixed-precision
operands in set and conditional operations
### What changes were proposed in this pull request?
Extend common-type resolution so two `TIME(p)` operands of differing
fractional-seconds precision resolve to `TIME(max(p1, p2))`:
- `TypeCoercionHelper.findWiderDateTimeType` now widens two `TIME` operands
to the larger precision. Because both the default (`TypeCoercion`) and ANSI
(`AnsiTypeCoercion`) `findTightestCommonType` implementations delegate to this
single method, the change covers UNION/INTERSECT/EXCEPT,
COALESCE/CASE/IF/NULLIF, GREATEST/LEAST, IN lists/subqueries, and array/map
literals.
- `Cast.canANSIStoreAssign` allows lossless TIME widening (smaller ->
larger precision) as a silent store assignment, while narrowing stays
explicit-CAST-only. `UpCastRule.canUpCast` already keeps cross-precision TIME
explicit-only, mirroring the nanosecond-timestamp precedent.
Cross-family pairs (TIME vs DATE / TIMESTAMP) remain incomparable.
### Why are the changes needed?
ANSI SQL (ISO/IEC 9075-2, result-type rule for a set of comparable datetime
types) requires that across datetime operands of differing fractional-seconds
precision the result type is the datetime type with the largest
fractional-seconds precision. Previously `findWiderDateTimeType` returned
`None` for any pair involving `TIME`, so set and conditional operations over
mixed `TIME(p)` raised an analysis error and required an explicit `CAST`. The
widening cast (`TIME(p1) -> TIME(p2)` via [...]
### Does this PR introduce _any_ user-facing change?
Yes, within the unreleased TIME type (`Unstable`). Set and conditional
operations over mixed `TIME(p)` now resolve `TIME(max(p))` without an explicit
cast; values from the smaller-precision side are widened losslessly. Inserting
a smaller-precision TIME value into a larger-precision TIME column now
succeeds, while the reverse (narrowing) still requires an explicit cast.
For example:
```sql
SELECT typeof(c), c FROM (
SELECT '12:34:56.789' :: TIME(3) AS c
UNION ALL SELECT '01:02:03.456789' :: TIME(6)) ORDER BY c;
-- time(6) 01:02:03.456789
-- time(6) 12:34:56.789
```
### How was this patch tested?
- Added type-coercion unit tests in `TypeCoercionSuite` and
`AnsiTypeCoercionSuite` (widening to max precision, equal precision, and
TIME-vs-DATE/TIMESTAMP returning `None`).
- Added a store-assignment / up-cast contract test in `CastSuiteBase`.
- Added golden-file coverage in `time.sql` for UNION, COALESCE, CASE,
NULLIF, GREATEST/LEAST, array/map, IN, and store-assignment INSERTs (widening
succeeds, narrowing fails without an explicit cast).
- Ran the affected catalyst suites and the `time.sql` `SQLQueryTestSuite`.
### Was this patch authored or co-authored using generative AI tooling?
Generated-by: Cursor (Claude Opus 4.8)
Closes #56805 from MaxGekk/time-common-type.
Authored-by: Maxim Gekk <[email protected]>
Signed-off-by: Max Gekk <[email protected]>
(cherry picked from commit 0e1563a7bc43a0680c64315fb1a1fe7481f4092e)
Signed-off-by: Max Gekk <[email protected]>
---
.../sql/catalyst/analysis/TypeCoercionHelper.scala | 6 +
.../spark/sql/catalyst/expressions/Cast.scala | 4 +
.../catalyst/analysis/AnsiTypeCoercionSuite.scala | 14 ++
.../sql/catalyst/analysis/TypeCoercionSuite.scala | 11 ++
.../sql/catalyst/expressions/CastSuiteBase.scala | 15 ++
.../sql-tests/analyzer-results/time.sql.out | 219 +++++++++++++++++++++
.../src/test/resources/sql-tests/inputs/time.sql | 60 ++++++
.../test/resources/sql-tests/results/time.sql.out | 208 +++++++++++++++++++
8 files changed, 537 insertions(+)
diff --git
a/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/analysis/TypeCoercionHelper.scala
b/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/analysis/TypeCoercionHelper.scala
index c70a3fdd62f6..e6329e465e00 100644
---
a/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/analysis/TypeCoercionHelper.scala
+++
b/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/analysis/TypeCoercionHelper.scala
@@ -245,6 +245,12 @@ abstract class TypeCoercionHelper {
protected def findWiderDateTimeType(d1: DatetimeType, d2: DatetimeType):
Option[DatetimeType] =
(d1, d2) match {
+ // Two TIME operands of differing fractional-seconds precision widen to
the larger precision
+ // (ANSI SQL result type for a set of comparable datetime types).
Widening from a smaller to a
+ // larger precision is a lossless up-cast via truncateTimeToPrecision.
Cross-family pairs
+ // (TIME vs DATE / TIMESTAMP) remain incomparable and fall through to
the None arms below.
+ case (t1: TimeType, t2: TimeType) =>
+ Some(TimeType(math.max(t1.precision, t2.precision)))
case (_, _: TimeType) => None
case (_: TimeType, _) => None
diff --git
a/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/expressions/Cast.scala
b/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/expressions/Cast.scala
index 9ae34f94424c..346047a8ba82 100644
---
a/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/expressions/Cast.scala
+++
b/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/expressions/Cast.scala
@@ -524,6 +524,10 @@ object Cast extends QueryErrorsBase {
case (TimestampType, _: TimestampNTZNanosType) => false
case (TimestampNTZType, _: TimestampLTZNanosType) => false
case (_: AnyTimestampNanoType, _: AnyTimestampNanoType) => false
+ // SPARK-57585: widening a TIME(p) to a larger precision is lossless and
allowed as a silent
+ // store assignment, while narrowing (e.g. TIME(6) -> TIME(3)) drops
fractional-seconds digits
+ // and stays explicit-CAST-only. Equal precision is handled by the `from
== to` short-circuit.
+ case (f: TimeType, t: TimeType) => f.precision <= t.precision
case (_: DatetimeType, _: DatetimeType) => true
case (ArrayType(fromType, fn), ArrayType(toType, tn)) =>
diff --git
a/sql/catalyst/src/test/scala/org/apache/spark/sql/catalyst/analysis/AnsiTypeCoercionSuite.scala
b/sql/catalyst/src/test/scala/org/apache/spark/sql/catalyst/analysis/AnsiTypeCoercionSuite.scala
index dbbce0888640..80d94cc3760d 100644
---
a/sql/catalyst/src/test/scala/org/apache/spark/sql/catalyst/analysis/AnsiTypeCoercionSuite.scala
+++
b/sql/catalyst/src/test/scala/org/apache/spark/sql/catalyst/analysis/AnsiTypeCoercionSuite.scala
@@ -153,6 +153,9 @@ class AnsiTypeCoercionSuite extends TypeCoercionSuiteBase {
widenTest(TimestampType, TimestampLTZNanosType(9),
Some(TimestampLTZNanosType(9)))
widenTest(TimestampLTZNanosType(7), TimestampNTZNanosType(9),
Some(TimestampLTZNanosType(9)))
widenTest(DateType, TimestampNTZNanosType(7),
Some(TimestampNTZNanosType(7)))
+ // Two TIME operands widen to the larger fractional-seconds precision
(SPARK-57585).
+ widenTest(TimeType(3), TimeType(6), Some(TimeType(6)))
+ widenTest(TimeType(0), TimeType(9), Some(TimeType(9)))
}
test("tightest common bound for types") {
@@ -247,6 +250,17 @@ class AnsiTypeCoercionSuite extends TypeCoercionSuiteBase {
// nanos <-> TIME has no common datetime type.
widenTest(TimestampLTZNanosType(9), TimeType(6), None)
widenTest(TimestampNTZNanosType(9), TimeType(6), None)
+
+ // TIME(p) types (SPARK-57585).
+ // Two TIME operands widen to the larger fractional-seconds precision.
+ widenTest(TimeType(3), TimeType(6), Some(TimeType(6)))
+ widenTest(TimeType(6), TimeType(3), Some(TimeType(6)))
+ widenTest(TimeType(0), TimeType(9), Some(TimeType(9)))
+ widenTest(TimeType(6), TimeType(6), Some(TimeType(6)))
+ // TIME has no common datetime type with DATE or the TIMESTAMP families.
+ widenTest(TimeType(6), DateType, None)
+ widenTest(TimeType(6), TimestampType, None)
+ widenTest(TimeType(6), TimestampNTZType, None)
// No common type with non-datetime types.
widenTest(IntegerType, TimestampLTZNanosType(9), None)
widenTest(StringType, TimestampNTZNanosType(9), None)
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 de0f3207bebc..d990a393ff65 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
@@ -670,6 +670,17 @@ class TypeCoercionSuite extends TypeCoercionSuiteBase {
// nanos <-> TIME has no common datetime type.
widenTest(TimestampLTZNanosType(9), TimeType(6), None)
widenTest(TimestampNTZNanosType(9), TimeType(6), None)
+
+ // TIME(p) types (SPARK-57585).
+ // Two TIME operands widen to the larger fractional-seconds precision.
+ widenTest(TimeType(3), TimeType(6), Some(TimeType(6)))
+ widenTest(TimeType(6), TimeType(3), Some(TimeType(6)))
+ widenTest(TimeType(0), TimeType(9), Some(TimeType(9)))
+ widenTest(TimeType(6), TimeType(6), Some(TimeType(6)))
+ // TIME has no common datetime type with DATE or the TIMESTAMP families.
+ widenTest(TimeType(6), DateType, None)
+ widenTest(TimeType(6), TimestampType, None)
+ widenTest(TimeType(6), TimestampNTZType, None)
// No common type with non-datetime types.
widenTest(IntegerType, TimestampLTZNanosType(9), None)
widenTest(StringType, TimestampNTZNanosType(9), None)
diff --git
a/sql/catalyst/src/test/scala/org/apache/spark/sql/catalyst/expressions/CastSuiteBase.scala
b/sql/catalyst/src/test/scala/org/apache/spark/sql/catalyst/expressions/CastSuiteBase.scala
index 8cc38996d3f8..23d5783155fd 100644
---
a/sql/catalyst/src/test/scala/org/apache/spark/sql/catalyst/expressions/CastSuiteBase.scala
+++
b/sql/catalyst/src/test/scala/org/apache/spark/sql/catalyst/expressions/CastSuiteBase.scala
@@ -802,6 +802,21 @@ abstract class CastSuiteBase extends SparkFunSuite with
ExpressionEvalHelper {
}
}
+ test("SPARK-57585: cross-precision TIME store-assignment and up-cast
contract") {
+ for {
+ p1 <- TimeType.MIN_PRECISION to TimeType.MAX_PRECISION
+ p2 <- TimeType.MIN_PRECISION to TimeType.MAX_PRECISION
+ } {
+ // Cross-precision TIME casts are never up-casts (only equal precision
is, via from == to),
+ // matching the nanos precedent; STRICT store assignment rejects them.
The both-direction
+ // assertions also guard against a future blanket datetime arm in
UpCastRule.
+ assert(Cast.canUpCast(TimeType(p1), TimeType(p2)) == (p1 == p2))
+ // ANSI store assignment allows lossless widening (p1 <= p2) and equal
precision, but blocks
+ // lossy narrowing (p1 > p2) to avoid silently dropping
fractional-seconds digits.
+ assert(Cast.canANSIStoreAssign(TimeType(p1), TimeType(p2)) == (p1 <= p2))
+ }
+ }
+
test("cross-family nanos cast: admissibility, store-assignment and up-cast
contract") {
for {
p <- TimestampLTZNanosType.MIN_PRECISION to
TimestampLTZNanosType.MAX_PRECISION
diff --git
a/sql/core/src/test/resources/sql-tests/analyzer-results/time.sql.out
b/sql/core/src/test/resources/sql-tests/analyzer-results/time.sql.out
index d69a6df9eb5e..d5d5700316f8 100644
--- a/sql/core/src/test/resources/sql-tests/analyzer-results/time.sql.out
+++ b/sql/core/src/test/resources/sql-tests/analyzer-results/time.sql.out
@@ -2294,3 +2294,222 @@ SELECT
time_from_micros(time_to_micros(TIME'14:30:00.5'))
-- !query analysis
Project [time_from_micros(time_to_micros(14:30:00.5)) AS
time_from_micros(time_to_micros(TIME '14:30:00.5'))#x]
+- OneRowRelation
+
+
+-- !query
+SELECT typeof(c), c FROM (
+ SELECT '12:34:56.789' :: TIME(3) AS c
+ UNION ALL SELECT '01:02:03.456789' :: TIME(6)) ORDER BY c
+-- !query analysis
+Sort [c#x ASC NULLS FIRST], true
++- Project [typeof(c#x) AS typeof(c)#x, c#x]
+ +- SubqueryAlias __auto_generated_subquery_name
+ +- Union false, false
+ :- Project [cast(c#x as time(6)) AS c#x]
+ : +- Project [cast(12:34:56.789 as time(3)) AS c#x]
+ : +- OneRowRelation
+ +- Project [cast(01:02:03.456789 as time(6)) AS CAST(01:02:03.456789
AS TIME(6))#x]
+ +- OneRowRelation
+
+
+-- !query
+SELECT typeof(c), c FROM (
+ SELECT '00:00:00' :: TIME(0) AS c
+ UNION ALL SELECT '23:59:59.123456789' :: TIME(9)) ORDER BY c
+-- !query analysis
+Sort [c#x ASC NULLS FIRST], true
++- Project [typeof(c#x) AS typeof(c)#x, c#x]
+ +- SubqueryAlias __auto_generated_subquery_name
+ +- Union false, false
+ :- Project [cast(c#x as time(9)) AS c#x]
+ : +- Project [cast(00:00:00 as time(0)) AS c#x]
+ : +- OneRowRelation
+ +- Project [cast(23:59:59.123456789 as time(9)) AS
CAST(23:59:59.123456789 AS TIME(9))#x]
+ +- OneRowRelation
+
+
+-- !query
+SELECT typeof(c), c FROM (
+ SELECT '01:02:03.456' :: TIME(3) AS c
+ INTERSECT SELECT '01:02:03.456000' :: TIME(6))
+-- !query analysis
+Project [typeof(c#x) AS typeof(c)#x, c#x]
++- SubqueryAlias __auto_generated_subquery_name
+ +- Intersect false
+ :- Project [cast(c#x as time(6)) AS c#x]
+ : +- Project [cast(01:02:03.456 as time(3)) AS c#x]
+ : +- OneRowRelation
+ +- Project [cast(01:02:03.456000 as time(6)) AS CAST(01:02:03.456000 AS
TIME(6))#x]
+ +- OneRowRelation
+
+
+-- !query
+SELECT typeof(c), c FROM (
+ SELECT '12:34:56.789' :: TIME(3) AS c
+ EXCEPT SELECT '01:02:03.456789' :: TIME(6)) ORDER BY c
+-- !query analysis
+Sort [c#x ASC NULLS FIRST], true
++- Project [typeof(c#x) AS typeof(c)#x, c#x]
+ +- SubqueryAlias __auto_generated_subquery_name
+ +- Except false
+ :- Project [cast(c#x as time(6)) AS c#x]
+ : +- Project [cast(12:34:56.789 as time(3)) AS c#x]
+ : +- OneRowRelation
+ +- Project [cast(01:02:03.456789 as time(6)) AS CAST(01:02:03.456789
AS TIME(6))#x]
+ +- OneRowRelation
+
+
+-- !query
+SELECT typeof(v), v FROM (SELECT coalesce(
+ CAST(NULL AS TIME(3)), '01:02:03.456789' :: TIME(6)) AS v)
+-- !query analysis
+Project [typeof(v#x) AS typeof(v)#x, v#x]
++- SubqueryAlias __auto_generated_subquery_name
+ +- Project [coalesce(cast(cast(null as time(3)) as time(6)),
cast(01:02:03.456789 as time(6))) AS v#x]
+ +- OneRowRelation
+
+
+-- !query
+SELECT typeof(v), v FROM (SELECT CASE WHEN true
+ THEN '12:34:56.789' :: TIME(3) ELSE '01:02:03.456789' :: TIME(6) END AS v)
+-- !query analysis
+Project [typeof(v#x) AS typeof(v)#x, v#x]
++- SubqueryAlias __auto_generated_subquery_name
+ +- Project [CASE WHEN true THEN cast(cast(12:34:56.789 as time(3)) as
time(6)) ELSE cast(01:02:03.456789 as time(6)) END AS v#x]
+ +- OneRowRelation
+
+
+-- !query
+SELECT typeof(v), v FROM (SELECT nullif(
+ '12:34:56.789' :: TIME(3), '01:02:03.456789' :: TIME(6)) AS v)
+-- !query analysis
+Project [typeof(v#x) AS typeof(v)#x, v#x]
++- SubqueryAlias __auto_generated_subquery_name
+ +- Project [nullif(cast(12:34:56.789 as time(3)), cast(01:02:03.456789 as
time(6))) AS v#x]
+ +- OneRowRelation
+
+
+-- !query
+SELECT typeof(greatest('12:34:56.789' :: TIME(3), '01:02:03.456789' ::
TIME(6)))
+-- !query analysis
+Project [typeof(greatest(cast(cast(12:34:56.789 as time(3)) as time(6)),
cast(01:02:03.456789 as time(6)))) AS typeof(greatest(CAST(12:34:56.789 AS
TIME(3)), CAST(01:02:03.456789 AS TIME(6))))#x]
++- OneRowRelation
+
+
+-- !query
+SELECT greatest('12:34:56.789' :: TIME(3), '01:02:03.456789' :: TIME(6))
+-- !query analysis
+Project [greatest(cast(cast(12:34:56.789 as time(3)) as time(6)),
cast(01:02:03.456789 as time(6))) AS greatest(CAST(12:34:56.789 AS TIME(3)),
CAST(01:02:03.456789 AS TIME(6)))#x]
++- OneRowRelation
+
+
+-- !query
+SELECT least('12:34:56.789' :: TIME(3), '01:02:03.456789' :: TIME(6))
+-- !query analysis
+Project [least(cast(cast(12:34:56.789 as time(3)) as time(6)),
cast(01:02:03.456789 as time(6))) AS least(CAST(12:34:56.789 AS TIME(3)),
CAST(01:02:03.456789 AS TIME(6)))#x]
++- OneRowRelation
+
+
+-- !query
+SELECT typeof(array('12:34:56.789' :: TIME(3), '01:02:03.456789' :: TIME(6)))
+-- !query analysis
+Project [typeof(array(cast(cast(12:34:56.789 as time(3)) as time(6)),
cast(01:02:03.456789 as time(6)))) AS typeof(array(CAST(12:34:56.789 AS
TIME(3)), CAST(01:02:03.456789 AS TIME(6))))#x]
++- OneRowRelation
+
+
+-- !query
+SELECT typeof(map('a', '12:34:56.789' :: TIME(3), 'b', '01:02:03.456789' ::
TIME(6)))
+-- !query analysis
+Project [typeof(map(a, cast(cast(12:34:56.789 as time(3)) as time(6)), b,
cast(01:02:03.456789 as time(6)))) AS typeof(map(a, CAST(12:34:56.789 AS
TIME(3)), b, CAST(01:02:03.456789 AS TIME(6))))#x]
++- OneRowRelation
+
+
+-- !query
+SELECT '01:02:03.456789' :: TIME(6) IN ('12:34:56.789' :: TIME(3),
'01:02:03.456789' :: TIME(6))
+-- !query analysis
+Project [cast(cast(01:02:03.456789 as time(6)) as time(6)) IN
(cast(cast(12:34:56.789 as time(3)) as time(6)),cast(cast(01:02:03.456789 as
time(6)) as time(6))) AS (CAST(01:02:03.456789 AS TIME(6)) IN
(CAST(12:34:56.789 AS TIME(3)), CAST(01:02:03.456789 AS TIME(6))))#x]
++- OneRowRelation
+
+
+-- !query
+SELECT '01:02:03.456789123' :: TIME(9) IN (
+ '12:34:56.789' :: TIME(3), '01:02:03.456789' :: TIME(6),
'01:02:03.456789123' :: TIME(9))
+-- !query analysis
+Project [cast(cast(01:02:03.456789123 as time(9)) as time(9)) IN
(cast(cast(12:34:56.789 as time(3)) as time(9)),cast(cast(01:02:03.456789 as
time(6)) as time(9)),cast(cast(01:02:03.456789123 as time(9)) as time(9))) AS
(CAST(01:02:03.456789123 AS TIME(9)) IN (CAST(12:34:56.789 AS TIME(3)),
CAST(01:02:03.456789 AS TIME(6)), CAST(01:02:03.456789123 AS TIME(9))))#x]
++- OneRowRelation
+
+
+-- !query
+CREATE TABLE time_widen_tbl (t6 TIME(6)) USING parquet
+-- !query analysis
+CreateDataSourceTableCommand `spark_catalog`.`default`.`time_widen_tbl`, false
+
+
+-- !query
+INSERT INTO time_widen_tbl SELECT '01:02:03.456' :: TIME(3)
+-- !query analysis
+InsertIntoHadoopFsRelationCommand file:[not included in
comparison]/{warehouse_dir}/time_widen_tbl, false, Parquet, [path=file:[not
included in comparison]/{warehouse_dir}/time_widen_tbl], Append,
`spark_catalog`.`default`.`time_widen_tbl`,
org.apache.spark.sql.execution.datasources.InMemoryFileIndex(file:[not included
in comparison]/{warehouse_dir}/time_widen_tbl), [t6]
++- Project [cast(CAST(01:02:03.456 AS TIME(3))#x as time(6)) AS t6#x]
+ +- Project [cast(01:02:03.456 as time(3)) AS CAST(01:02:03.456 AS
TIME(3))#x]
+ +- OneRowRelation
+
+
+-- !query
+SELECT typeof(t6), t6 FROM time_widen_tbl
+-- !query analysis
+Project [typeof(t6#x) AS typeof(t6)#x, t6#x]
++- SubqueryAlias spark_catalog.default.time_widen_tbl
+ +- Relation spark_catalog.default.time_widen_tbl[t6#x] parquet
+
+
+-- !query
+DROP TABLE time_widen_tbl
+-- !query analysis
+DropTable false, false
++- ResolvedIdentifier V2SessionCatalog(spark_catalog), default.time_widen_tbl
+
+
+-- !query
+CREATE TABLE time_narrow_tbl (t3 TIME(3)) USING parquet
+-- !query analysis
+CreateDataSourceTableCommand `spark_catalog`.`default`.`time_narrow_tbl`, false
+
+
+-- !query
+INSERT INTO time_narrow_tbl SELECT '01:02:03.456789' :: TIME(6)
+-- !query analysis
+org.apache.spark.sql.AnalysisException
+{
+ "errorClass" : "INCOMPATIBLE_DATA_FOR_TABLE.CANNOT_SAFELY_CAST",
+ "sqlState" : "KD000",
+ "messageParameters" : {
+ "colName" : "`t3`",
+ "srcType" : "\"TIME(6)\"",
+ "tableName" : "`spark_catalog`.`default`.`time_narrow_tbl`",
+ "targetType" : "\"TIME(3)\""
+ }
+}
+
+
+-- !query
+INSERT INTO time_narrow_tbl SELECT CAST('01:02:03.456789' :: TIME(6) AS
TIME(3))
+-- !query analysis
+InsertIntoHadoopFsRelationCommand file:[not included in
comparison]/{warehouse_dir}/time_narrow_tbl, false, Parquet, [path=file:[not
included in comparison]/{warehouse_dir}/time_narrow_tbl], Append,
`spark_catalog`.`default`.`time_narrow_tbl`,
org.apache.spark.sql.execution.datasources.InMemoryFileIndex(file:[not included
in comparison]/{warehouse_dir}/time_narrow_tbl), [t3]
++- Project [CAST(CAST(01:02:03.456789 AS TIME(6)) AS TIME(3))#x AS t3#x]
+ +- Project [cast(cast(01:02:03.456789 as time(6)) as time(3)) AS
CAST(CAST(01:02:03.456789 AS TIME(6)) AS TIME(3))#x]
+ +- OneRowRelation
+
+
+-- !query
+SELECT typeof(t3), t3 FROM time_narrow_tbl
+-- !query analysis
+Project [typeof(t3#x) AS typeof(t3)#x, t3#x]
++- SubqueryAlias spark_catalog.default.time_narrow_tbl
+ +- Relation spark_catalog.default.time_narrow_tbl[t3#x] parquet
+
+
+-- !query
+DROP TABLE time_narrow_tbl
+-- !query analysis
+DropTable false, false
++- ResolvedIdentifier V2SessionCatalog(spark_catalog), default.time_narrow_tbl
diff --git a/sql/core/src/test/resources/sql-tests/inputs/time.sql
b/sql/core/src/test/resources/sql-tests/inputs/time.sql
index f81944716881..1a836b270edc 100644
--- a/sql/core/src/test/resources/sql-tests/inputs/time.sql
+++ b/sql/core/src/test/resources/sql-tests/inputs/time.sql
@@ -366,3 +366,63 @@ SELECT time_to_millis(time_from_millis(52200500));
SELECT time_from_millis(time_to_millis(TIME'14:30:00.5'));
SELECT time_to_micros(time_from_micros(52200500000));
SELECT time_from_micros(time_to_micros(TIME'14:30:00.5'));
+
+-- SPARK-57585: common TIME(p) type for mixed-precision operands in set and
conditional operations.
+-- The result type is the TIME with the largest fractional-seconds precision;
values from the
+-- smaller-precision side are widened losslessly.
+
+-- UNION widens TIME(3) and TIME(6) to TIME(6).
+SELECT typeof(c), c FROM (
+ SELECT '12:34:56.789' :: TIME(3) AS c
+ UNION ALL SELECT '01:02:03.456789' :: TIME(6)) ORDER BY c;
+-- UNION over the precision extremes widens TIME(0) and TIME(9) to TIME(9).
+SELECT typeof(c), c FROM (
+ SELECT '00:00:00' :: TIME(0) AS c
+ UNION ALL SELECT '23:59:59.123456789' :: TIME(9)) ORDER BY c;
+-- INTERSECT widens TIME(3) and TIME(6) to TIME(6); the matching value is
compared at TIME(6).
+SELECT typeof(c), c FROM (
+ SELECT '01:02:03.456' :: TIME(3) AS c
+ INTERSECT SELECT '01:02:03.456000' :: TIME(6));
+-- EXCEPT widens TIME(3) and TIME(6) to TIME(6).
+SELECT typeof(c), c FROM (
+ SELECT '12:34:56.789' :: TIME(3) AS c
+ EXCEPT SELECT '01:02:03.456789' :: TIME(6)) ORDER BY c;
+
+-- coalesce keeps the first non-null, widened to the wider precision.
+SELECT typeof(v), v FROM (SELECT coalesce(
+ CAST(NULL AS TIME(3)), '01:02:03.456789' :: TIME(6)) AS v);
+-- CASE WHEN unifies its branches to TIME(6).
+SELECT typeof(v), v FROM (SELECT CASE WHEN true
+ THEN '12:34:56.789' :: TIME(3) ELSE '01:02:03.456789' :: TIME(6) END AS v);
+-- NULLIF resolves the common type of its arguments.
+SELECT typeof(v), v FROM (SELECT nullif(
+ '12:34:56.789' :: TIME(3), '01:02:03.456789' :: TIME(6)) AS v);
+
+-- greatest / least widen their arguments to the common TIME type and pick the
extreme value.
+SELECT typeof(greatest('12:34:56.789' :: TIME(3), '01:02:03.456789' ::
TIME(6)));
+SELECT greatest('12:34:56.789' :: TIME(3), '01:02:03.456789' :: TIME(6));
+SELECT least('12:34:56.789' :: TIME(3), '01:02:03.456789' :: TIME(6));
+
+-- array() unifies element types and map() value types to the common TIME type.
+SELECT typeof(array('12:34:56.789' :: TIME(3), '01:02:03.456789' :: TIME(6)));
+SELECT typeof(map('a', '12:34:56.789' :: TIME(3), 'b', '01:02:03.456789' ::
TIME(6)));
+
+-- IN list resolves the common type across mixed TIME(p) elements.
+SELECT '01:02:03.456789' :: TIME(6) IN ('12:34:56.789' :: TIME(3),
'01:02:03.456789' :: TIME(6));
+-- A 3-way mix folds across all precisions in findWiderCommonType, resolving
the comparison at
+-- TIME(9).
+SELECT '01:02:03.456789123' :: TIME(9) IN (
+ '12:34:56.789' :: TIME(3), '01:02:03.456789' :: TIME(6),
'01:02:03.456789123' :: TIME(9));
+
+-- Store assignment: widening TIME(3) -> TIME(6) succeeds, narrowing TIME(6)
-> TIME(3) requires
+-- an explicit cast.
+CREATE TABLE time_widen_tbl (t6 TIME(6)) USING parquet;
+INSERT INTO time_widen_tbl SELECT '01:02:03.456' :: TIME(3);
+SELECT typeof(t6), t6 FROM time_widen_tbl;
+DROP TABLE time_widen_tbl;
+
+CREATE TABLE time_narrow_tbl (t3 TIME(3)) USING parquet;
+INSERT INTO time_narrow_tbl SELECT '01:02:03.456789' :: TIME(6);
+INSERT INTO time_narrow_tbl SELECT CAST('01:02:03.456789' :: TIME(6) AS
TIME(3));
+SELECT typeof(t3), t3 FROM time_narrow_tbl;
+DROP TABLE time_narrow_tbl;
diff --git a/sql/core/src/test/resources/sql-tests/results/time.sql.out
b/sql/core/src/test/resources/sql-tests/results/time.sql.out
index c61c37f27cd3..dace78ab938d 100644
--- a/sql/core/src/test/resources/sql-tests/results/time.sql.out
+++ b/sql/core/src/test/resources/sql-tests/results/time.sql.out
@@ -2810,3 +2810,211 @@ SELECT
time_from_micros(time_to_micros(TIME'14:30:00.5'))
struct<time_from_micros(time_to_micros(TIME '14:30:00.5')):time(6)>
-- !query output
14:30:00.5
+
+
+-- !query
+SELECT typeof(c), c FROM (
+ SELECT '12:34:56.789' :: TIME(3) AS c
+ UNION ALL SELECT '01:02:03.456789' :: TIME(6)) ORDER BY c
+-- !query schema
+struct<typeof(c):string,c:time(6)>
+-- !query output
+time(6) 01:02:03.456789
+time(6) 12:34:56.789
+
+
+-- !query
+SELECT typeof(c), c FROM (
+ SELECT '00:00:00' :: TIME(0) AS c
+ UNION ALL SELECT '23:59:59.123456789' :: TIME(9)) ORDER BY c
+-- !query schema
+struct<typeof(c):string,c:time(9)>
+-- !query output
+time(9) 00:00:00
+time(9) 23:59:59.123456789
+
+
+-- !query
+SELECT typeof(c), c FROM (
+ SELECT '01:02:03.456' :: TIME(3) AS c
+ INTERSECT SELECT '01:02:03.456000' :: TIME(6))
+-- !query schema
+struct<typeof(c):string,c:time(6)>
+-- !query output
+time(6) 01:02:03.456
+
+
+-- !query
+SELECT typeof(c), c FROM (
+ SELECT '12:34:56.789' :: TIME(3) AS c
+ EXCEPT SELECT '01:02:03.456789' :: TIME(6)) ORDER BY c
+-- !query schema
+struct<typeof(c):string,c:time(6)>
+-- !query output
+time(6) 12:34:56.789
+
+
+-- !query
+SELECT typeof(v), v FROM (SELECT coalesce(
+ CAST(NULL AS TIME(3)), '01:02:03.456789' :: TIME(6)) AS v)
+-- !query schema
+struct<typeof(v):string,v:time(6)>
+-- !query output
+time(6) 01:02:03.456789
+
+
+-- !query
+SELECT typeof(v), v FROM (SELECT CASE WHEN true
+ THEN '12:34:56.789' :: TIME(3) ELSE '01:02:03.456789' :: TIME(6) END AS v)
+-- !query schema
+struct<typeof(v):string,v:time(6)>
+-- !query output
+time(6) 12:34:56.789
+
+
+-- !query
+SELECT typeof(v), v FROM (SELECT nullif(
+ '12:34:56.789' :: TIME(3), '01:02:03.456789' :: TIME(6)) AS v)
+-- !query schema
+struct<typeof(v):string,v:time(3)>
+-- !query output
+time(3) 12:34:56.789
+
+
+-- !query
+SELECT typeof(greatest('12:34:56.789' :: TIME(3), '01:02:03.456789' ::
TIME(6)))
+-- !query schema
+struct<typeof(greatest(CAST(12:34:56.789 AS TIME(3)), CAST(01:02:03.456789 AS
TIME(6)))):string>
+-- !query output
+time(6)
+
+
+-- !query
+SELECT greatest('12:34:56.789' :: TIME(3), '01:02:03.456789' :: TIME(6))
+-- !query schema
+struct<greatest(CAST(12:34:56.789 AS TIME(3)), CAST(01:02:03.456789 AS
TIME(6))):time(6)>
+-- !query output
+12:34:56.789
+
+
+-- !query
+SELECT least('12:34:56.789' :: TIME(3), '01:02:03.456789' :: TIME(6))
+-- !query schema
+struct<least(CAST(12:34:56.789 AS TIME(3)), CAST(01:02:03.456789 AS
TIME(6))):time(6)>
+-- !query output
+01:02:03.456789
+
+
+-- !query
+SELECT typeof(array('12:34:56.789' :: TIME(3), '01:02:03.456789' :: TIME(6)))
+-- !query schema
+struct<typeof(array(CAST(12:34:56.789 AS TIME(3)), CAST(01:02:03.456789 AS
TIME(6)))):string>
+-- !query output
+array<time(6)>
+
+
+-- !query
+SELECT typeof(map('a', '12:34:56.789' :: TIME(3), 'b', '01:02:03.456789' ::
TIME(6)))
+-- !query schema
+struct<typeof(map(a, CAST(12:34:56.789 AS TIME(3)), b, CAST(01:02:03.456789 AS
TIME(6)))):string>
+-- !query output
+map<string,time(6)>
+
+
+-- !query
+SELECT '01:02:03.456789' :: TIME(6) IN ('12:34:56.789' :: TIME(3),
'01:02:03.456789' :: TIME(6))
+-- !query schema
+struct<(CAST(01:02:03.456789 AS TIME(6)) IN (CAST(12:34:56.789 AS TIME(3)),
CAST(01:02:03.456789 AS TIME(6)))):boolean>
+-- !query output
+true
+
+
+-- !query
+SELECT '01:02:03.456789123' :: TIME(9) IN (
+ '12:34:56.789' :: TIME(3), '01:02:03.456789' :: TIME(6),
'01:02:03.456789123' :: TIME(9))
+-- !query schema
+struct<(CAST(01:02:03.456789123 AS TIME(9)) IN (CAST(12:34:56.789 AS TIME(3)),
CAST(01:02:03.456789 AS TIME(6)), CAST(01:02:03.456789123 AS TIME(9)))):boolean>
+-- !query output
+true
+
+
+-- !query
+CREATE TABLE time_widen_tbl (t6 TIME(6)) USING parquet
+-- !query schema
+struct<>
+-- !query output
+
+
+
+-- !query
+INSERT INTO time_widen_tbl SELECT '01:02:03.456' :: TIME(3)
+-- !query schema
+struct<>
+-- !query output
+
+
+
+-- !query
+SELECT typeof(t6), t6 FROM time_widen_tbl
+-- !query schema
+struct<typeof(t6):string,t6:time(6)>
+-- !query output
+time(6) 01:02:03.456
+
+
+-- !query
+DROP TABLE time_widen_tbl
+-- !query schema
+struct<>
+-- !query output
+
+
+
+-- !query
+CREATE TABLE time_narrow_tbl (t3 TIME(3)) USING parquet
+-- !query schema
+struct<>
+-- !query output
+
+
+
+-- !query
+INSERT INTO time_narrow_tbl SELECT '01:02:03.456789' :: TIME(6)
+-- !query schema
+struct<>
+-- !query output
+org.apache.spark.sql.AnalysisException
+{
+ "errorClass" : "INCOMPATIBLE_DATA_FOR_TABLE.CANNOT_SAFELY_CAST",
+ "sqlState" : "KD000",
+ "messageParameters" : {
+ "colName" : "`t3`",
+ "srcType" : "\"TIME(6)\"",
+ "tableName" : "`spark_catalog`.`default`.`time_narrow_tbl`",
+ "targetType" : "\"TIME(3)\""
+ }
+}
+
+
+-- !query
+INSERT INTO time_narrow_tbl SELECT CAST('01:02:03.456789' :: TIME(6) AS
TIME(3))
+-- !query schema
+struct<>
+-- !query output
+
+
+
+-- !query
+SELECT typeof(t3), t3 FROM time_narrow_tbl
+-- !query schema
+struct<typeof(t3):string,t3:time(3)>
+-- !query output
+time(3) 01:02:03.456
+
+
+-- !query
+DROP TABLE time_narrow_tbl
+-- !query schema
+struct<>
+-- !query output
+
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
