comphead opened a new issue, #26339: URL: https://github.com/apache/datafusion/issues/26339
### Describe the bug Another group of functions whose signature is stricter than Spark's `ImplicitCastInputTypes`. This is the same class as #23889 (`next_day`), #23896 (`pmod`) and #24941 (`factorial`), and the open design question there applies: should `datafusion-spark` model Spark's implicit casts in the signatures? | function | DataFusion today | Spark 4.0.0 | |---|---|---| | `last_day` | `Signature::exact` with `Date32` ([last_day.rs#L44](https://github.com/apache/datafusion/blob/9dec77584ea39d93aa01bba7f880b318b37d2c24/datafusion/spark/src/function/datetime/last_day.rs#L44)) rejects STRING and TIMESTAMP, for example `last_day('foo')` fails with "Failed to coerce arguments" ([last_day.slt#L104](https://github.com/apache/datafusion/blob/9dec77584ea39d93aa01bba7f880b318b37d2c24/datafusion/sqllogictest/test_files/spark/datetime/last_day.slt#L104)) | `last_day('2016-02-07')` and `last_day(TIMESTAMP '2016-02-07 01:02:03')` return `2016-02-29`. `last_day('foo')` returns NULL | | `monthname` | accepts DATE and TIMESTAMP, rejects STRING with "Function 'monthname' requires Date, but received String" ([monthname.slt#L167](https://github.com/apache/datafusion/blob/9dec77584ea39d93aa01bba7f880b318b37d2c24/datafusion/sqllogictest/test_files/spark/datetime/monthname.slt#L167)) | `monthname('2016-02-07')` returns `Feb` | | `weekday` | same as `monthname` ([weekday.slt#L123](https://github.com/apache/datafusion/blob/9dec77584ea39d93aa01bba7f880b318b37d2c24/datafusion/sqllogictest/test_files/spark/datetime/weekday.slt#L123)) | `weekday('2016-02-07')` returns `6` | | `round` | `round('hello'::varchar)` fails with "Function 'round' failed to match any signature" ([round.slt#L597](https://github.com/apache/datafusion/blob/9dec77584ea39d93aa01bba7f880b318b37d2c24/datafusion/sqllogictest/test_files/spark/math/round.slt#L597)) | `round('1.5')` returns `2.0` as DOUBLE. `round('hello')` returns NULL | | `unbase64` | `unbase64(12::integer)` fails with "requires Binary, but received Int32" ([base64.slt#L115](https://github.com/apache/datafusion/blob/9dec77584ea39d93aa01bba7f880b318b37d2c24/datafusion/sqllogictest/test_files/spark/string/base64.slt#L115)) | casts to STRING first, so `unbase64(12)` decodes `'12'` | Spark's STRING to DATE or DOUBLE cast depends on the ANSI setting. An invalid string gives NULL when ANSI is off and `CAST_INVALID_INPUT` when it is on. I checked that for `last_day('foo')`, `weekday('foo')` and `round('hello')`. DataFusion errors in both modes today. As noted in #23889, the cast would have to be Spark's cast and not `arrow::compute::cast`. ### To Reproduce Spark 4.0.0: ```sql SELECT last_day('2016-02-07'), monthname('2016-02-07'), weekday('2016-02-07') -- 2016-02-29, Feb, 6 SELECT last_day('foo'), monthname('foo'), weekday('foo') -- NULL, NULL, NULL SELECT round('1.5'), round('hello') -- 2.0, NULL ``` With `spark.sql.ansi.enabled=true`, `last_day('foo')`, `weekday('foo')` and `round('hello')` each fail with `CAST_INVALID_INPUT`. ### Expected behavior Accept the argument types Spark accepts and cast them the way Spark does, as part of the decision on implicit casts in #23889. ### Additional context Found by running the queries in `datafusion/sqllogictest/test_files/spark` on a local Spark and comparing the results with the expected values in the files, which is the kind of check proposed in #17045. Spark results come from Apple's build of Spark 4.0.0 (`4.0.0.65-apple`) with a UTC session and `spark.sql.ansi.enabled=false` unless stated. The Spark source links point at the `v4.0.0` tag. DataFusion results are the sqllogictest expectations at 9dec77584, which CI enforces. Anything marked as not run was derived from reading the code. -- 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]
