andygrove opened a new issue, #212:
URL: https://github.com/apache/datafusion-spark/issues/212
Part of #178 and apache/datafusion-comet#7300.
### What the function does
`make_time(hour, minute, second)` builds a TIME value from its fields.
`hour` and `minute` are INT and
`second` is `DECIMAL(16, 6)`; Spark's analyzer (`ImplicitCastInputTypes`)
casts the arguments to those types,
so integer seconds and decimal literals such as `45.887` are accepted and
the fractional part keeps
microsecond precision. The result type is `TIME(6)`. Any NULL argument gives
NULL.
Valid ranges are hour 0-23, minute 0-59, second 0 to 59.999999. Out-of-range
values always raise
`DATETIME_FIELD_OUT_OF_BOUNDS.WITHOUT_SUGGESTION` (SQLSTATE `22023`),
independent of
`spark.sql.ansi.enabled`; the message parameter `rangeMessage` is the
`java.time.LocalTime.of` message,
e.g. `Invalid value for HourOfDay (valid values 0 - 23): 25`. There is no
`try_make_time`.
Added in Spark 4.1.0. Requires `spark.sql.timeType.enabled` (internal,
default `false` outside Spark's own
tests); otherwise analysis fails with `UNSUPPORTED_TIME_TYPE`. No aliases.
### Examples
```sql
SELECT make_time(6, 30, 45.887);
-- 06:30:45.887
SELECT make_time(1, 18, 19.87);
-- 01:18:19.87
SELECT make_time(NULL, 30, 0);
-- NULL
SELECT make_time(1, 18, null);
-- NULL
SELECT make_time(-1, 18, 19.87);
-- error DATETIME_FIELD_OUT_OF_BOUNDS.WITHOUT_SUGGESTION,
-- rangeMessage: Invalid value for HourOfDay (valid values 0 - 23): -1
SELECT make_time(1, 18, 60.0);
-- error ... Invalid value for SecondOfMinute (valid values 0 - 59): 60
SELECT make_time(1, 18, 9999999999.999999);
-- error DATETIME_FIELD_OUT_OF_BOUNDS.WITHOUT_SUGGESTION
SELECT make_time(1, 18, -999999999.999999);
-- error ... Invalid value for SecondOfMinute (valid values 0 - 59):
-999999999
SELECT make_time(1, 18, 4294967297.999999);
-- error ... Invalid value for SecondOfMinute (valid values 0 - 59):
4294967297
```
### Edge cases to test
- NULL in each argument (literal and column).
- `make_time(13, 2, 23.5)` -> `13:02:23.5`; boundaries `make_time(0, 0, 0)`
-> `00:00:00` and
`make_time(23, 59, 59.999999)` -> `23:59:59.999999`.
- Out of range: hour `25` / `-1` (`HourOfDay`), minute `60` / `-1`
(`MinuteOfHour`), second `60.0` / `100.5`
(`SecondOfMinute ... : 100`). The messages come from `LocalTime.of`; Spark
checks seconds itself first
(see next bullet), so match the text exactly.
- Seconds outside `[0, Int.MaxValue]` are rejected before the `Int`
conversion so they cannot wrap into a
valid value: `4294967297.999999` must error, not produce `00:00:01`. For
negative seconds the reported
value is the decimal truncated toward zero (`-999999999.999999` reports
`-999999999`), so a
value like `-0.5` reports `0` (derived from `DateTimeUtils.makeTime`).
- Fractional digits beyond 6: the implicit cast to `DECIMAL(16, 6)` rounds
(HALF_UP) before
`make_time` sees the value; a seconds literal that does not fit
`DECIMAL(16, 6)` (more than 10 integer
digits) is subject to cast overflow rules, which do depend on ANSI mode.
- `second` given as INT, BIGINT, DECIMAL with other precision/scale, and
DOUBLE columns, to pin down which
coercions the Spark-side analyzer inserts versus what the kernel must
accept.
- No dependence on session time zone.
### Differences across Spark versions
Not present before 4.1.0. `MakeTime` and `DateTimeUtils.makeTime` are
identical in 4.1.3 and 4.2.0, and
the golden results match.
### Implementation notes
- Spark:
`sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/expressions/timeExpressions.scala`
(`MakeTime`, `RuntimeReplaceable` calling `DateTimeUtils.makeTime` in
`sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/util/DateTimeUtils.scala`).
Error builder:
`QueryExecutionErrors.ansiDateTimeArgumentOutOfRangeWithoutSuggestion`.
- Seconds handling: `unscaled = second.toUnscaledLong` (scale 6), `fullSecs
= floorDiv(unscaled, 1_000_000)`,
error if `fullSecs < 0 || fullSecs > Int.MaxValue`, `nanos =
floorMod(unscaled, 1_000_000) * 1000`, then
`LocalTime.of(hour, minute, fullSecs, nanos)` converted to nanoseconds
since midnight.
- Output: Spark's Arrow type for TIME is `Time64(Nanosecond)`
(`ArrowUtils`); return that.
- DataFusion core has `make_time`
(`datafusion/functions/src/datetime/make_time.rs`) but it differs:
all three arguments are coerced to Int32 (strings accepted), so fractional
seconds are not supported;
it returns `Time32(Second)`; and its errors ("Hour value ... is out of
range") do not match Spark's
condition or message. Write a dedicated kernel taking `Int32, Int32,
Decimal128(16, 6)`.
- Belongs in the `datetime` module of datafusion-spark (next to
`time_trunc.rs`).
- Tests: add `crates/sqllogictest/test_files/datetime/make_time.slt` using
the cases above (from
`sql/core/src/test/resources/sql-tests/results/time.sql.out` and
`TimeExpressionsSuite`
"creating values of TimeType via make_time"), and document with
`#[user_doc]`.
---
_This issue was generated by an LLM (Claude) from the Apache Spark source
code and tests. Examples come from Spark's `ExpressionDescription` examples,
test suites and `.sql.out` golden files, but please verify them against Spark
before relying on them._
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]