andygrove commented on code in PR #72: URL: https://github.com/apache/datafusion-spark/pull/72#discussion_r4599932962
########## crates/sqllogictest/test_files/datetime/make_time.slt: ########## @@ -0,0 +1,416 @@ +# SPDX-License-Identifier: Apache-2.0 +# SPDX-FileCopyrightText: See the NOTICE file distributed with this work for additional information regarding copyright ownership +# SPDX-FileContributor: Licensed to the Apache Software Foundation (ASF) under one or more contributor license agreements + +# Tests for Spark-compatible make_time (Spark 4.1+) +# Reference: https://spark.apache.org/docs/latest/api/sql/index.html#make_time +# Expected values come from Spark's sql-tests/results/time.sql.out and the unit tests +# of DataFusion Comet's make_time, and were checked against pyspark==4.2.0 with +# spark.sql.timeType.enabled=true. +# +# DataFusion reads a literal such as 19.87 as DOUBLE and Spark reads it as DECIMAL +# (https://github.com/apache/datafusion-spark/issues/97), so the key cases are also +# run with an explicit DECIMAL to cover the decimal path. + +query D +SELECT make_time(1, 18, 19.87); +---- +01:18:19.870 + +query D +SELECT make_time(1, 18, 19.87::DECIMAL(4, 2)); +---- +01:18:19.870 + +query D +SELECT make_time(6, 30, 45.887); +---- +06:30:45.887 + +query D +SELECT make_time(6, 30, 45.887::DECIMAL(5, 3)); +---- +06:30:45.887 + +query D +SELECT make_time(13, 2, 23.5); +---- +13:02:23.500 + +query D +SELECT make_time(13, 2, 23.5::DECIMAL(3, 1)); +---- +13:02:23.500 + +query D +SELECT make_time(0, 0, 0); +---- +00:00:00 + +query D +SELECT make_time(1, 0, 0); +---- +01:00:00 + +query D +SELECT make_time(0, 1, 0); +---- +00:01:00 + +query D +SELECT make_time(0, 0, 1); +---- +00:00:01 + +query D +SELECT make_time(0, 0, 1.5); +---- +00:00:01.500 + +query D +SELECT make_time(23, 59, 59.999999); +---- +23:59:59.999999 + +query D +SELECT make_time(23, 59, 59.999999::DECIMAL(8, 6)); +---- +23:59:59.999999 + +query D +SELECT make_time(12, 30, 45.123456::DECIMAL(8, 6)); +---- +12:30:45.123456 + +# Seconds with scale above 6 are rounded half up to DECIMAL(16, 6) +query D +SELECT make_time(1, 1, 59.9999994::DECIMAL(10, 7)); +---- +01:01:59.999999 + +query D +SELECT make_time(1, 1, 3.1234565::DECIMAL(10, 7)); +---- +01:01:03.123457 + +query D +SELECT make_time(0, 0, -0.0000001::DECIMAL(10, 7)); +---- +00:00:00 + +# NULL in any argument gives NULL +query D +SELECT make_time(NULL, 18, 19.87); +---- +NULL + +query D +SELECT make_time(1, NULL, 19.87); +---- +NULL + +query D +SELECT make_time(1, 18, NULL); +---- +NULL + +# Strings are cast to the argument types, like Spark's implicit casts +query D +SELECT make_time('6', '30', '45.5'); +---- +06:30:45.500 + +query D +SELECT make_time(1, 1, '1.1234567'); +---- +01:01:01.123457 + +# Other numeric types are cast to INT, INT and DECIMAL(16, 6) +query D +SELECT make_time(1::BIGINT, 2::BIGINT, 3.5::DOUBLE); +---- +01:02:03.500 + +query D +SELECT make_time(1::TINYINT, 2::SMALLINT, 3::BIGINT); +---- +01:02:03 + +query D +SELECT make_time(1::BIGINT, 2, 3.25::FLOAT); +---- +01:02:03.250 + +# A fractional hour or minute is truncated by the cast to INT +query D +SELECT make_time(1.9::DOUBLE, 1, 1); +---- +01:01:01 + +query D +SELECT make_time(1, 1.9::DOUBLE, 1); +---- +01:01:01 + +# Non-numeric, non-string arguments are rejected +statement error make_time expects a numeric hour, got Boolean +SELECT make_time(true, 0, 0); + +# Result type +query T +SELECT arrow_typeof(make_time(1, 18, 19.87)); +---- +Time64(ns) + +# Out-of-range fields raise DATETIME_FIELD_OUT_OF_BOUNDS whatever the ANSI setting. +# The same errors are checked with ANSI mode on further down. +statement error \[DATETIME_FIELD_OUT_OF_BOUNDS\.WITHOUT_SUGGESTION\] Invalid value for HourOfDay \(valid values 0 - 23\): -1\.\s+SQLSTATE: 22023 +SELECT make_time(-1, 18, 19.87); + +statement error Invalid value for HourOfDay \(valid values 0 - 23\): 24\.\s+SQLSTATE: 22023 +SELECT make_time(24, 18, 19.87); + +statement error Invalid value for HourOfDay \(valid values 0 - 23\): 25\.\s+SQLSTATE: 22023 +SELECT make_time(25, 0, 0); + +statement error Invalid value for MinuteOfHour \(valid values 0 - 59\): 60\.\s+SQLSTATE: 22023 +SELECT make_time(1, 60, 19.87); + +statement error Invalid value for MinuteOfHour \(valid values 0 - 59\): 60\.\s+SQLSTATE: 22023 +SELECT make_time(0, 60, 0); + +statement error Invalid value for MinuteOfHour \(valid values 0 - 59\): -1\.\s+SQLSTATE: 22023 +SELECT make_time(0, -1, 0); + +statement error Invalid value for SecondOfMinute \(valid values 0 - 59\): 60\.\s+SQLSTATE: 22023 +SELECT make_time(1, 18, 60.0); + +statement error Invalid value for SecondOfMinute \(valid values 0 - 59\): 60\.\s+SQLSTATE: 22023 +SELECT make_time(1, 18, 60.0::DECIMAL(3, 1)); + +# 100.5 reports the whole seconds, 100 +statement error Invalid value for SecondOfMinute \(valid values 0 - 59\): 100\.\s+SQLSTATE: 22023 +SELECT make_time(0, 0, 100.5); + +statement error Invalid value for SecondOfMinute \(valid values 0 - 59\): 100\.\s+SQLSTATE: 22023 +SELECT make_time(0, 0, 100.5::DECIMAL(4, 1)); + +# Hour and minute are checked before seconds in 60..=i32::MAX, like LocalTime.of +statement error Invalid value for HourOfDay \(valid values 0 - 23\): 24\. +SELECT make_time(24, 0, 60); + +statement error Invalid value for MinuteOfHour \(valid values 0 - 59\): 60\. +SELECT make_time(1, 60, 60); + +statement error Invalid value for HourOfDay \(valid values 0 - 23\): -1\. +SELECT make_time(-1, 0, 60); + +statement error Invalid value for SecondOfMinute \(valid values 0 - 59\): 2147483647\. +SELECT make_time(0, 0, 2147483647); + +# Seconds that are negative or above i32::MAX are checked first +statement error Invalid value for SecondOfMinute \(valid values 0 - 59\): -1\. +SELECT make_time(24, 0, -1); + +statement error Invalid value for SecondOfMinute \(valid values 0 - 59\): -1\. +SELECT make_time(0, 0, -1); + +statement error Invalid value for SecondOfMinute \(valid values 0 - 59\): 2147483648\. +SELECT make_time(0, 0, 2147483648); + +statement error Invalid value for SecondOfMinute \(valid values 0 - 59\): 4294967297\. +SELECT make_time(24, 0, 4294967297.5); + +# Negative fractional seconds are floored for the check but truncated in the message +statement error Invalid value for SecondOfMinute \(valid values 0 - 59\): 0\. +SELECT make_time(1, 1, -0.5); + +statement error Invalid value for SecondOfMinute \(valid values 0 - 59\): 0\. +SELECT make_time(1, 1, -0.5::DECIMAL(2, 1)); + +# 59.9999995 rounds half up to 60.000000 in DECIMAL(16, 6) +statement error Invalid value for SecondOfMinute \(valid values 0 - 59\): 60\. +SELECT make_time(1, 1, 59.9999995::DECIMAL(10, 7)); + +statement error Invalid value for SecondOfMinute \(valid values 0 - 59\): 9999999999\. +SELECT make_time(1, 18, 9999999999.999999); + +statement error Invalid value for SecondOfMinute \(valid values 0 - 59\): 9999999999\. +SELECT make_time(1, 18, 9999999999.999999::DECIMAL(16, 6)); + +statement error Invalid value for SecondOfMinute \(valid values 0 - 59\): -999999999\. +SELECT make_time(1, 18, -999999999.999999); + +statement error Invalid value for SecondOfMinute \(valid values 0 - 59\): -999999999\. +SELECT make_time(1, 18, -999999999.999999::DECIMAL(15, 6)); + +statement error Invalid value for SecondOfMinute \(valid values 0 - 59\): 4294967297\. +SELECT make_time(1, 18, 4294967297.999999); + +statement error Invalid value for SecondOfMinute \(valid values 0 - 59\): 4294967297\. +SELECT make_time(1, 18, 4294967297.999999::DECIMAL(16, 6)); + +# Column inputs Review Comment: Thanks, added in #341: table rows pairing a NULL with invalid fields, including the three you suggested, each returning NULL. Checked against PySpark 4.2.0. ########## crates/spark/src/function/datetime/make_time.rs: ########## @@ -0,0 +1,284 @@ +// SPDX-License-Identifier: Apache-2.0 +// SPDX-FileCopyrightText: See the NOTICE file distributed with this work for additional information regarding copyright ownership +// SPDX-FileContributor: Licensed to the Apache Software Foundation (ASF) under one or more contributor license agreements + +use std::sync::Arc; + +use arrow::array::{Array, ArrayRef, AsArray, PrimitiveArray, Time64NanosecondArray}; +use arrow::datatypes::ArrowPrimitiveType; +use arrow::datatypes::{DataType, Decimal128Type, Int32Type, TimeUnit}; +use datafusion_common::utils::take_function_args; +use datafusion_common::{ + DataFusionError, Result, exec_datafusion_err, exec_err, internal_err, +}; +use datafusion_expr::{ + ColumnarValue, Documentation, ScalarFunctionArgs, ScalarUDFImpl, Signature, + Volatility, +}; +use datafusion_macros::user_doc; + +const MICROS_PER_SECOND: i128 = 1_000_000; +const NANOS_PER_MICRO: i64 = 1_000; + +/// Spark's seconds argument type, `DecimalType(16, 6)`. +const SECONDS_TYPE: DataType = DataType::Decimal128(16, 6); + +/// Spark-compatible `make_time` expression (Spark 4.1+). +/// +/// <https://spark.apache.org/docs/latest/api/sql/index.html#make_time> +#[user_doc( + doc_section(label = "Time and Date Functions"), + description = "Creates a time of day from hour, minute and second fields. The result is a \ + `Time64(Nanosecond)` with microsecond precision, like Spark's `TIME(6)`. \ + Returns NULL if any argument is NULL. \ + Out-of-range fields raise a `DATETIME_FIELD_OUT_OF_BOUNDS` error whether or not ANSI mode is enabled. \ + Seconds that are negative or above 2147483647 are checked first, then hour, minute and second, \ + so the error names the same field as Spark's. \ + Arguments are cast to INT, INT and DECIMAL(16, 6) with DataFusion's cast rather than Spark's, \ + so a value the cast cannot represent (an integer outside the INT range, a malformed string, \ + seconds that do not fit DECIMAL(16, 6), NaN or Infinity) raises a cast error in both ANSI modes. \ + With ANSI mode off Spark wraps the integer or returns NULL instead, and it returns NULL for NaN \ + and Infinity in both modes (https://github.com/apache/datafusion-spark/issues/88).", + syntax_example = "make_time(hour, minute, second)", + sql_example = r#"```sql +> SELECT make_time(6, 30, 45.887); +06:30:45.887 +> SELECT make_time(23, 59, 59.999999); +23:59:59.999999 +> SELECT make_time(1, 18, NULL); +NULL +> SELECT make_time(24, 18, 19.87); +-- error: Invalid value for HourOfDay (valid values 0 - 23): 24 +```"#, + argument( + name = "hour", + description = "Hour of the day, 0 to 23. Numeric and string values are cast to INT, \ + and a fractional number is truncated." + ), + argument( + name = "minute", + description = "Minute of the hour, 0 to 59. Numeric and string values are cast to INT, \ + and a fractional number is truncated." + ), + argument( + name = "second", + description = "Second of the minute with an optional fraction, 0 to 59.999999. \ + Numeric and string values are cast to DECIMAL(16, 6), so extra fractional digits are rounded half up \ + (59.9999995 becomes 60 and is out of range)." + ) +)] +#[derive(Debug, PartialEq, Eq, Hash)] +pub struct SparkMakeTime { + signature: Signature, +} + +impl Default for SparkMakeTime { + fn default() -> Self { + Self::new() + } +} + +impl SparkMakeTime { + pub fn new() -> Self { + Self { + signature: Signature::user_defined(Volatility::Immutable), + } + } +} + +fn out_of_bounds( + field: &str, + range: &str, + value: impl std::fmt::Display, +) -> DataFusionError { + exec_datafusion_err!( + "[DATETIME_FIELD_OUT_OF_BOUNDS.WITHOUT_SUGGESTION] Invalid value for {field} (valid values {range}): {value}. SQLSTATE: 22023" + ) +} + +/// Downcasts an argument that `coerce_types` should already have cast. Callers that +/// skip DataFusion's coercion, such as Comet, get an error rather than a panic. +fn primitive_arg<'a, T: ArrowPrimitiveType>( + array: &'a ArrayRef, + name: &str, +) -> Result<&'a PrimitiveArray<T>> { + match array.as_primitive_opt::<T>() { + Some(array) => Ok(array), + None => internal_err!( + "make_time expects {name} as {}, got {}", + T::DATA_TYPE, + array.data_type() + ), + } +} + +/// `secs_micros` is the unscaled value of the `DECIMAL(16, 6)` seconds argument. +fn make_time(hours: i32, minutes: i32, secs_micros: i128) -> Result<i64> { + let full_secs = secs_micros.div_euclid(MICROS_PER_SECOND); + let frac_micros = secs_micros.rem_euclid(MICROS_PER_SECOND) as i64; + + // Like Spark's `DateTimeUtils.makeTime`, seconds outside `0..=i32::MAX` + // are rejected first, so the message reports the whole seconds + // (truncated toward zero) rather than a wrapped value. Otherwise + // `LocalTime.of` checks hour, then minute, then second. + if full_secs < 0 || full_secs > i128::from(i32::MAX) { + return Err(out_of_bounds( + "SecondOfMinute", + "0 - 59", + secs_micros / MICROS_PER_SECOND, + )); + } + if !(0..=23).contains(&hours) { + return Err(out_of_bounds("HourOfDay", "0 - 23", hours)); + } + if !(0..=59).contains(&minutes) { + return Err(out_of_bounds("MinuteOfHour", "0 - 59", minutes)); + } + if full_secs > 59 { + return Err(out_of_bounds("SecondOfMinute", "0 - 59", full_secs)); + } + + // Whole seconds of the day plus the fraction, in micros, then to nanos. + let day_secs = i64::from(hours) * 3_600 + i64::from(minutes) * 60 + full_secs as i64; + Ok((day_secs * 1_000_000 + frac_micros) * NANOS_PER_MICRO) +} + +impl ScalarUDFImpl for SparkMakeTime { + fn name(&self) -> &str { + "make_time" + } + + fn signature(&self) -> &Signature { + &self.signature + } + + fn return_type(&self, _: &[DataType]) -> Result<DataType> { + Ok(DataType::Time64(TimeUnit::Nanosecond)) + } + + fn documentation(&self) -> Option<&Documentation> { + self.doc() + } + + fn coerce_types(&self, arg_types: &[DataType]) -> Result<Vec<DataType>> { + let [hours, minutes, secs] = take_function_args(self.name(), arg_types)?; + // Spark's ImplicitCastInputTypes also casts strings to INT / DECIMAL. + let castable = |t: &DataType| { + t.is_numeric() + || *t == DataType::Null + || matches!(t, DataType::Utf8 | DataType::LargeUtf8 | DataType::Utf8View) + }; + for (arg, t) in [("hour", hours), ("minute", minutes), ("second", secs)] { + if !castable(t) { + return exec_err!("make_time expects a numeric {arg}, got {t}"); + } + } + Ok(vec![DataType::Int32, DataType::Int32, SECONDS_TYPE]) + } + + fn invoke_with_args(&self, args: ScalarFunctionArgs) -> Result<ColumnarValue> { + let arrays: Vec<ArrayRef> = ColumnarValue::values_to_arrays(&args.args)?; + let [hours, minutes, secs] = take_function_args(self.name(), arrays)?; + let hours = primitive_arg::<Int32Type>(&hours, "hour")?; + let minutes = primitive_arg::<Int32Type>(&minutes, "minute")?; + // The kernel reads the unscaled value as microseconds, so only the scale matters. + if !matches!(secs.data_type(), DataType::Decimal128(_, 6)) { + return internal_err!( + "make_time expects second as Decimal128(_, 6), got {}", + secs.data_type() + ); + } + let secs = primitive_arg::<Decimal128Type>(&secs, "second")?; + + let times: Time64NanosecondArray = (0..hours.len()) + .map(|i| { + if hours.is_null(i) || minutes.is_null(i) || secs.is_null(i) { + Ok(None) + } else { + make_time(hours.value(i), minutes.value(i), secs.value(i)).map(Some) + } + }) + .collect::<Result<_>>()?; + Ok(ColumnarValue::Array(Arc::new(times))) + } +} + +#[cfg(test)] +mod tests { + use super::*; + use arrow::array::{Decimal128Array, Int32Array, Int64Array}; + use arrow::datatypes::{Field, Time64NanosecondType}; + use datafusion_common::config::ConfigOptions; + + /// Invokes the kernel directly, skipping `coerce_types`, as Comet does. + fn invoke(args: Vec<ArrayRef>) -> Result<ColumnarValue> { + let arg_fields = args + .iter() + .map(|a| Arc::new(Field::new("a", a.data_type().clone(), true))) + .collect(); + SparkMakeTime::new().invoke_with_args(ScalarFunctionArgs { + number_rows: args[0].len(), + args: args.into_iter().map(ColumnarValue::Array).collect(), + arg_fields, + return_field: Arc::new(Field::new( + "r", + DataType::Time64(TimeUnit::Nanosecond), + true, + )), + config_options: Arc::new(ConfigOptions::default()), + }) + } + + fn int32(v: i32) -> ArrayRef { + Arc::new(Int32Array::from(vec![v])) + } + + fn secs(unscaled: i128, precision: u8, scale: i8) -> ArrayRef { + Arc::new( + Decimal128Array::from(vec![unscaled]) + .with_precision_and_scale(precision, scale) + .unwrap(), + ) + } + + fn assert_err(args: Vec<ArrayRef>, expected: &str) { + let err = invoke(args).unwrap_err().to_string(); + assert!(err.contains(expected), "{err}"); + } + + #[test] + fn test_uncoerced_arguments_error() { + assert_err( + vec![ + Arc::new(Int64Array::from(vec![1])), + int32(2), + secs(3, 16, 6), + ], + "make_time expects hour as Int32, got Int64", + ); + assert_err( + vec![int32(1), int32(2), int32(3)], + "make_time expects second as Decimal128(_, 6), got Int32", + ); + assert_err( + vec![int32(1), int32(2), secs(3, 10, 2)], + "make_time expects second as Decimal128(_, 6), got Decimal128(10, 2)", + ); + } + + #[test] + fn test_seconds_with_other_precision() -> Result<()> { Review Comment: Thanks, added in #341: `test_sliced_arrays` invokes the kernel directly with arrays sliced at different offsets, including sliced null bitmaps, with invalid values outside the visible slices, and checks the results, NULL positions and output length. `test_empty_arrays` covers empty arrays and empty slices. -- 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]
