andygrove commented on code in PR #329:
URL: https://github.com/apache/datafusion-spark/pull/329#discussion_r4603541028


##########
crates/spark/src/function/datetime/make_date.rs:
##########
@@ -0,0 +1,538 @@
+// 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, Date32Array, Date32Builder, new_empty_array};
+use arrow::datatypes::{DataType, Field, FieldRef};
+use datafusion_common::cast::as_int32_array;
+use datafusion_common::types::{NativeType, logical_int32, logical_string};
+use datafusion_common::utils::take_function_args;
+use datafusion_common::{Result, ScalarValue, exec_err, internal_err};
+use datafusion_expr::{
+    Coercion, ColumnarValue, Documentation, ReturnFieldArgs, 
ScalarFunctionArgs,
+    ScalarUDFImpl, Signature, TypeSignatureClass, Volatility,
+};
+use datafusion_macros::user_doc;
+
+const ARITHMETIC_OVERFLOW_ERROR: &str = r#"[ARITHMETIC_OVERFLOW] overflow. If 
necessary set "spark.sql.ansi.enabled" to "false" to bypass this error. 
SQLSTATE: 22003"#;
+
+/// Spark-compatible `make_date` expression.
+///
+/// <https://spark.apache.org/docs/latest/api/sql/index.html#make_date>
+#[user_doc(
+    doc_section(label = "Time and Date Functions"),
+    description = "Creates a date from `year`, `month` and `day` fields. 
DataFusion has no built-in \
+        equivalent. Years from -999999999 to 999999999 are accepted (year 0 is 
1 BC), as long as \

Review Comment:
   Thanks, I hadn't noticed the built-in. I checked all three by running 
DataFusion's `make_date`, and they hold. 777724a replaces the sentence with 
those differences, and the PR description now says it replaces the built-in and 
lists them.



##########
crates/spark/src/function/datetime/make_date.rs:
##########
@@ -0,0 +1,538 @@
+// 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, Date32Array, Date32Builder, new_empty_array};
+use arrow::datatypes::{DataType, Field, FieldRef};
+use datafusion_common::cast::as_int32_array;
+use datafusion_common::types::{NativeType, logical_int32, logical_string};
+use datafusion_common::utils::take_function_args;
+use datafusion_common::{Result, ScalarValue, exec_err, internal_err};
+use datafusion_expr::{
+    Coercion, ColumnarValue, Documentation, ReturnFieldArgs, 
ScalarFunctionArgs,
+    ScalarUDFImpl, Signature, TypeSignatureClass, Volatility,
+};
+use datafusion_macros::user_doc;
+
+const ARITHMETIC_OVERFLOW_ERROR: &str = r#"[ARITHMETIC_OVERFLOW] overflow. If 
necessary set "spark.sql.ansi.enabled" to "false" to bypass this error. 
SQLSTATE: 22003"#;
+
+/// Spark-compatible `make_date` expression.
+///
+/// <https://spark.apache.org/docs/latest/api/sql/index.html#make_date>
+#[user_doc(
+    doc_section(label = "Time and Date Functions"),
+    description = "Creates a date from `year`, `month` and `day` fields. 
DataFusion has no built-in \
+        equivalent. Years from -999999999 to 999999999 are accepted (year 0 is 
1 BC), as long as \
+        the date fits the DATE range. When the fields do not form a valid date 
(for example \
+        month 13 or February 29 in a non-leap year), the function returns 
NULL, or raises \
+        DATETIME_FIELD_OUT_OF_BOUNDS when 
`datafusion.execution.enable_ansi_mode` is true. A \
+        valid date outside the DATE range (before -5877641-06-23 or after 
+5881580-07-11) raises \
+        ARITHMETIC_OVERFLOW in both modes, as in Spark. Other numeric and 
string inputs are cast \
+        to INT first, which truncates fractions and raises an error if the 
value is out of the \
+        INT range or is not a valid number. These errors match Spark with ANSI 
mode enabled. \
+        With ANSI mode disabled, Spark wraps an out-of-range BIGINT and 
returns NULL for a \
+        malformed string, while this function still errors (see \

Review Comment:
   Added in 777724a as commented-out queries citing #88, and the sentence now 
covers NaN, infinite DOUBLEs and out-of-range DECIMALs. I confirmed on PySpark 
4.2.0 that NaN gives `0000-01-01`.



##########
crates/spark/src/function/datetime/make_date.rs:
##########
@@ -0,0 +1,538 @@
+// 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, Date32Array, Date32Builder, new_empty_array};
+use arrow::datatypes::{DataType, Field, FieldRef};
+use datafusion_common::cast::as_int32_array;
+use datafusion_common::types::{NativeType, logical_int32, logical_string};
+use datafusion_common::utils::take_function_args;
+use datafusion_common::{Result, ScalarValue, exec_err, internal_err};
+use datafusion_expr::{
+    Coercion, ColumnarValue, Documentation, ReturnFieldArgs, 
ScalarFunctionArgs,
+    ScalarUDFImpl, Signature, TypeSignatureClass, Volatility,
+};
+use datafusion_macros::user_doc;
+
+const ARITHMETIC_OVERFLOW_ERROR: &str = r#"[ARITHMETIC_OVERFLOW] overflow. If 
necessary set "spark.sql.ansi.enabled" to "false" to bypass this error. 
SQLSTATE: 22003"#;
+
+/// Spark-compatible `make_date` expression.
+///
+/// <https://spark.apache.org/docs/latest/api/sql/index.html#make_date>
+#[user_doc(
+    doc_section(label = "Time and Date Functions"),
+    description = "Creates a date from `year`, `month` and `day` fields. 
DataFusion has no built-in \
+        equivalent. Years from -999999999 to 999999999 are accepted (year 0 is 
1 BC), as long as \
+        the date fits the DATE range. When the fields do not form a valid date 
(for example \
+        month 13 or February 29 in a non-leap year), the function returns 
NULL, or raises \
+        DATETIME_FIELD_OUT_OF_BOUNDS when 
`datafusion.execution.enable_ansi_mode` is true. A \
+        valid date outside the DATE range (before -5877641-06-23 or after 
+5881580-07-11) raises \
+        ARITHMETIC_OVERFLOW in both modes, as in Spark. Other numeric and 
string inputs are cast \
+        to INT first, which truncates fractions and raises an error if the 
value is out of the \
+        INT range or is not a valid number. These errors match Spark with ANSI 
mode enabled. \
+        With ANSI mode disabled, Spark wraps an out-of-range BIGINT and 
returns NULL for a \
+        malformed string, while this function still errors (see \
+        https://github.com/apache/datafusion-spark/issues/88). Returns NULL if 
any input is NULL.",
+    syntax_example = "make_date(year, month, day)",
+    sql_example = r#"```sql
+> SELECT make_date(2013, 7, 15);
+2013-07-15
+> SELECT make_date(2019, 7, NULL);
+NULL
+> SELECT make_date(2023, 2, 29);
+NULL
+```"#,
+    argument(
+        name = "year",
+        description = "The year, from -999999999 to 999999999, as an INT. 
Other integer, floating-point, decimal and string inputs are cast to INT. NULL 
returns NULL."
+    ),
+    argument(
+        name = "month",
+        description = "The month of the year, from 1 (January) to 12 
(December), as an INT. NULL returns NULL."
+    ),
+    argument(
+        name = "day",
+        description = "The day of the month, from 1 to 31, as an INT. NULL 
returns NULL."
+    )
+)]
+#[derive(Debug, PartialEq, Eq, Hash)]
+pub struct SparkMakeDate {
+    signature: Signature,
+}
+
+impl Default for SparkMakeDate {
+    fn default() -> Self {
+        Self::new()
+    }
+}
+
+impl SparkMakeDate {
+    pub fn new() -> Self {
+        // Spark declares the arguments as INT and implicitly casts other 
numeric
+        // and string types to it. DataFusion types bare integer literals as 
BIGINT.
+        let int32 = || {
+            Coercion::new_implicit(
+                TypeSignatureClass::Native(logical_int32()),
+                vec![
+                    TypeSignatureClass::Integer,
+                    TypeSignatureClass::Float,
+                    TypeSignatureClass::Decimal,
+                    TypeSignatureClass::Native(logical_string()),
+                ],
+                NativeType::Int32,
+            )
+        };

Review Comment:
   Done in 777724a. `int32_coercion()` now sits next to `date_signature()` in 
`date_part_name.rs`, and both `make_date` and `date_from_unix_date` use it.



##########
crates/sqllogictest/test_files/datetime/make_date.slt:
##########
@@ -2,18 +2,196 @@
 # 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
 
-# This file was originally created by a porting script from:
-#   
https://github.com/lakehq/sail/tree/43b6ed8221de5c4c4adbedbb267ae1351158b43c/crates/sail-spark-connect/tests/gold_data/function
-# This file is part of the implementation of the datafusion-spark function 
library.
-# For more information, please see:
-#   https://github.com/apache/datafusion/issues/15914
-
-## Original Query: SELECT make_date(2013, 7, 15);
-## PySpark 3.5.5 Result: {'make_date(2013, 7, 15)': datetime.date(2013, 7, 
15), 'typeof(make_date(2013, 7, 15))': 'date', 'typeof(2013)': 'int', 
'typeof(7)': 'int', 'typeof(15)': 'int'}
-#query
-#SELECT make_date(2013::int, 7::int, 15::int);
-
-## Original Query: SELECT make_date(2019, 7, NULL);
-## PySpark 3.5.5 Result: {'make_date(2019, 7, NULL)': None, 
'typeof(make_date(2019, 7, NULL))': 'date', 'typeof(2019)': 'int', 'typeof(7)': 
'int', 'typeof(NULL)': 'void'}
-#query
-#SELECT make_date(2019::int, 7::int, NULL::void);
+# Tests for Spark-compatible make_date
+# Reference: https://spark.apache.org/docs/latest/api/sql/index.html#make_date
+# Expected values verified with pyspark==4.2.0, with spark.sql.ansi.enabled 
both
+# false and true.
+# Known divergences are captured as commented-out queries below.
+
+query D
+SELECT make_date(2013::int, 7::int, 15::int);
+----
+2013-07-15
+
+query T
+SELECT arrow_typeof(make_date(2013::int, 7::int, 15::int));
+----
+Date32
+
+# NULL in any argument returns NULL
+query DDDD
+SELECT make_date(2019::int, 7::int, NULL), make_date(NULL, 7::int, 1::int),
+  make_date(2019::int, NULL::int, 1::int), make_date(2024::int, 13::int, NULL);
+----
+NULL NULL NULL NULL
+
+# Leap years follow the proleptic Gregorian rules
+query DDD
+SELECT make_date(2024, 2, 29), make_date(2000, 2, 29), make_date(1970, 1, 1);
+----
+2024-02-29 2000-02-29 1970-01-01
+
+# Invalid dates return NULL when ANSI mode is off
+query DDDDDDDDDD
+SELECT make_date(2023, 2, 29), make_date(1900, 2, 29), make_date(2023, 2, 30),
+  make_date(2023, 4, 31), make_date(2023, 13, 1), make_date(2023, 0, 1),
+  make_date(2023, 6, 0), make_date(2023, 6, 32), make_date(1000000000, 1, 1),
+  make_date(-1000000000, 1, 1);
+----
+NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
+
+# Years outside 0001-9999 are accepted; year 0 is 1 BC
+query IIIIII
+SELECT unix_date(make_date(0, 1, 1)), unix_date(make_date(-1, 1, 1)),
+  unix_date(make_date(300000, 6, 15)), unix_date(make_date(300000, 2, 29)),
+  unix_date(make_date(5881580, 7, 11)), unix_date(make_date(-5877641, 6, 23));
+----
+-719528 -719893 108853388 108853281 2147483647 -2147483648
+
+# A valid date outside the DATE range raises ARITHMETIC_OVERFLOW in both modes
+query error \[ARITHMETIC_OVERFLOW\] overflow
+SELECT make_date(5881580, 7, 12);
+
+query error \[ARITHMETIC_OVERFLOW\] overflow
+SELECT make_date(-5877641, 6, 22);
+
+query error \[ARITHMETIC_OVERFLOW\] overflow
+SELECT make_date(999999999, 12, 31);
+
+# Array inputs
+statement ok
+CREATE TABLE ymd AS VALUES
+  (2024, 1, 31),
+  (2023, 2, 29),
+  (NULL, 1, 1),
+  (2020, 2, 29),
+  (2024, NULL, 1),
+  (2024, 13, 1);
+
+query D
+SELECT make_date(column1::int, column2::int, column3::int) FROM ymd;
+----
+2024-01-31
+NULL
+NULL
+2020-02-29
+NULL
+NULL
+
+# Mixed array and scalar inputs
+query D
+SELECT make_date(column1::int, 2, 29) FROM ymd;
+----
+2024-02-29
+NULL
+NULL
+2020-02-29
+2024-02-29
+2024-02-29
+
+# Other integer types are cast to INT
+query D
+SELECT make_date(arrow_cast(24, 'Int8'), arrow_cast(1, 'Int16'), arrow_cast(1, 
'Int64'));
+----
+0024-01-01
+
+# Floating-point and decimal inputs are truncated toward zero
+query DD
+SELECT make_date(2024.7::DOUBLE, 1.9::DOUBLE, 1) AS d1, 
make_date(2024.7::DECIMAL(5, 1), 1.9::DECIMAL(2, 1), 1) AS d2;
+----
+2024-01-01 2024-01-01
+
+# Strings are cast to INT
+query D
+SELECT make_date('2024', '2', '3');
+----
+2024-02-03
+
+# Spark 4.2.0 returns NULL for a malformed string when ANSI mode is off; 
DataFusion errors
+# https://github.com/apache/datafusion-spark/issues/88
+# query D
+# SELECT make_date('abc', '2', '3');
+# ----
+# NULL
+
+# Spark 4.2.0 wraps an out-of-range BIGINT to INT when ANSI mode is off 
(giving an
+# invalid year and so NULL); DataFusion errors
+# https://github.com/apache/datafusion-spark/issues/88
+# query D
+# SELECT make_date(3000000000, 1, 1);
+# ----
+# NULL
+
+# BOOLEAN is not accepted
+query error
+SELECT make_date(true, 1, 1);
+
+# A constant invalid date is not evaluated when there are no rows

Review Comment:
   Dropped it in 777724a, since 
`test_make_date_scalar_empty_batch_does_not_validate` covers that case.



##########
crates/sqllogictest/test_files/datetime/make_date.slt:
##########
@@ -2,18 +2,196 @@
 # 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
 
-# This file was originally created by a porting script from:
-#   
https://github.com/lakehq/sail/tree/43b6ed8221de5c4c4adbedbb267ae1351158b43c/crates/sail-spark-connect/tests/gold_data/function
-# This file is part of the implementation of the datafusion-spark function 
library.
-# For more information, please see:
-#   https://github.com/apache/datafusion/issues/15914
-
-## Original Query: SELECT make_date(2013, 7, 15);
-## PySpark 3.5.5 Result: {'make_date(2013, 7, 15)': datetime.date(2013, 7, 
15), 'typeof(make_date(2013, 7, 15))': 'date', 'typeof(2013)': 'int', 
'typeof(7)': 'int', 'typeof(15)': 'int'}
-#query
-#SELECT make_date(2013::int, 7::int, 15::int);
-
-## Original Query: SELECT make_date(2019, 7, NULL);
-## PySpark 3.5.5 Result: {'make_date(2019, 7, NULL)': None, 
'typeof(make_date(2019, 7, NULL))': 'date', 'typeof(2019)': 'int', 'typeof(7)': 
'int', 'typeof(NULL)': 'void'}
-#query
-#SELECT make_date(2019::int, 7::int, NULL::void);
+# Tests for Spark-compatible make_date
+# Reference: https://spark.apache.org/docs/latest/api/sql/index.html#make_date
+# Expected values verified with pyspark==4.2.0, with spark.sql.ansi.enabled 
both
+# false and true.
+# Known divergences are captured as commented-out queries below.
+
+query D
+SELECT make_date(2013::int, 7::int, 15::int);
+----
+2013-07-15
+
+query T
+SELECT arrow_typeof(make_date(2013::int, 7::int, 15::int));
+----
+Date32
+
+# NULL in any argument returns NULL
+query DDDD
+SELECT make_date(2019::int, 7::int, NULL), make_date(NULL, 7::int, 1::int),
+  make_date(2019::int, NULL::int, 1::int), make_date(2024::int, 13::int, NULL);
+----
+NULL NULL NULL NULL
+
+# Leap years follow the proleptic Gregorian rules
+query DDD
+SELECT make_date(2024, 2, 29), make_date(2000, 2, 29), make_date(1970, 1, 1);
+----
+2024-02-29 2000-02-29 1970-01-01
+
+# Invalid dates return NULL when ANSI mode is off
+query DDDDDDDDDD
+SELECT make_date(2023, 2, 29), make_date(1900, 2, 29), make_date(2023, 2, 30),
+  make_date(2023, 4, 31), make_date(2023, 13, 1), make_date(2023, 0, 1),
+  make_date(2023, 6, 0), make_date(2023, 6, 32), make_date(1000000000, 1, 1),
+  make_date(-1000000000, 1, 1);
+----
+NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
+
+# Years outside 0001-9999 are accepted; year 0 is 1 BC
+query IIIIII
+SELECT unix_date(make_date(0, 1, 1)), unix_date(make_date(-1, 1, 1)),
+  unix_date(make_date(300000, 6, 15)), unix_date(make_date(300000, 2, 29)),
+  unix_date(make_date(5881580, 7, 11)), unix_date(make_date(-5877641, 6, 23));
+----
+-719528 -719893 108853388 108853281 2147483647 -2147483648
+
+# A valid date outside the DATE range raises ARITHMETIC_OVERFLOW in both modes
+query error \[ARITHMETIC_OVERFLOW\] overflow
+SELECT make_date(5881580, 7, 12);
+
+query error \[ARITHMETIC_OVERFLOW\] overflow
+SELECT make_date(-5877641, 6, 22);
+
+query error \[ARITHMETIC_OVERFLOW\] overflow
+SELECT make_date(999999999, 12, 31);
+
+# Array inputs
+statement ok
+CREATE TABLE ymd AS VALUES
+  (2024, 1, 31),
+  (2023, 2, 29),
+  (NULL, 1, 1),
+  (2020, 2, 29),
+  (2024, NULL, 1),
+  (2024, 13, 1);
+
+query D
+SELECT make_date(column1::int, column2::int, column3::int) FROM ymd;
+----
+2024-01-31
+NULL
+NULL
+2020-02-29
+NULL
+NULL
+
+# Mixed array and scalar inputs
+query D
+SELECT make_date(column1::int, 2, 29) FROM ymd;
+----
+2024-02-29
+NULL
+NULL
+2020-02-29
+2024-02-29
+2024-02-29
+
+# Other integer types are cast to INT
+query D
+SELECT make_date(arrow_cast(24, 'Int8'), arrow_cast(1, 'Int16'), arrow_cast(1, 
'Int64'));
+----
+0024-01-01
+
+# Floating-point and decimal inputs are truncated toward zero
+query DD
+SELECT make_date(2024.7::DOUBLE, 1.9::DOUBLE, 1) AS d1, 
make_date(2024.7::DECIMAL(5, 1), 1.9::DECIMAL(2, 1), 1) AS d2;
+----
+2024-01-01 2024-01-01
+
+# Strings are cast to INT
+query D
+SELECT make_date('2024', '2', '3');
+----
+2024-02-03
+
+# Spark 4.2.0 returns NULL for a malformed string when ANSI mode is off; 
DataFusion errors
+# https://github.com/apache/datafusion-spark/issues/88
+# query D
+# SELECT make_date('abc', '2', '3');
+# ----
+# NULL
+
+# Spark 4.2.0 wraps an out-of-range BIGINT to INT when ANSI mode is off 
(giving an
+# invalid year and so NULL); DataFusion errors
+# https://github.com/apache/datafusion-spark/issues/88
+# query D
+# SELECT make_date(3000000000, 1, 1);
+# ----
+# NULL
+
+# BOOLEAN is not accepted
+query error
+SELECT make_date(true, 1, 1);
+
+# A constant invalid date is not evaluated when there are no rows
+statement ok
+set datafusion.execution.enable_ansi_mode = true;
+
+query D
+SELECT make_date(2024, 13, 1) FROM ymd WHERE column1 < 0;
+----
+
+statement ok
+set datafusion.execution.enable_ansi_mode = false;
+
+# Invalid dates raise DATETIME_FIELD_OUT_OF_BOUNDS under ANSI mode, with the
+# message from java.time.LocalDate.of
+statement ok
+set datafusion.execution.enable_ansi_mode = true;
+
+query D
+SELECT make_date(2024, 2, 29);
+----
+2024-02-29
+
+query D
+SELECT make_date(2019, 7, NULL);

Review Comment:
   Added in 777724a, in the ANSI block.



-- 
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