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]

Reply via email to