comphead opened a new issue, #26338: URL: https://github.com/apache/datafusion/issues/26338
### Describe the bug In Spark an unsupported, NULL or column-valued format does not raise an error. `TruncDate` and `TruncTimestamp` return NULL for a format they do not support and evaluate the format per row ([datetimeExpressions.scala#L2347](https://github.com/apache/spark/blob/v4.0.0/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/expressions/datetimeExpressions.scala#L2347), [#L2414](https://github.com/apache/spark/blob/v4.0.0/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/expressions/datetimeExpressions.scala#L2414), format names in [DateTimeUtils.scala#L478-L492](https://github.com/apache/spark/blob/v4.0.0/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/util/DateTimeUtils.scala#L478-L492)). The supported names are `YEAR`, `YYYY`, `YY`, `QUARTER`, `MONTH`, `MM`, `MON` and `WEEK` for `trunc`. `date_trunc` also accepts `DAY`, `DD`, `HOUR`, `MINUTE`, `SECOND`, `MILLISECOND` and `MICROSECOND`. The DataFusion versions rewrite the call in `simplify` and need a literal format ([date_trunc.rs#L92-L116](https://github.com/apache/datafusion/blob/9dec77584ea39d93aa01bba7f880b318b37d2c24/datafusion/spark/src/function/datetime/date_trunc.rs#L92-L116), [trunc.rs#L91-L128](https://github.com/apache/datafusion/blob/9dec77584ea39d93aa01bba7f880b318b37d2c24/datafusion/spark/src/function/datetime/trunc.rs#L91-L128)). That gives three differences: 1. An unsupported name such as `'test'` is an error. Spark returns NULL, also with ANSI mode on. 2. A NULL or a non-literal format is a planning error. Spark returns NULL for NULL and evaluates a column per row. 3. Names that Spark does not support are accepted, for example the plural `'MONTHS'`, which the tests expect to truncate to the month. Spark returns NULL. ### To Reproduce Spark 4.0.0: ```sql SELECT date_trunc('test', TIMESTAMP '2015-03-05 09:32:05') -- NULL SELECT trunc(DATE '2009-02-12', 'test') -- NULL SELECT date_trunc(NULL, TIMESTAMP '2015-03-05 09:32:05') -- NULL SELECT trunc(DATE '2009-02-12', f) FROM VALUES ('MONTH'), ('YEAR'), (NULL), ('bad') AS t(f) -- 2009-02-01, 2009-01-01, NULL, NULL SELECT date_trunc('MONTHS', TIMESTAMP '2015-03-05 09:32:05') -- NULL SELECT trunc(DATE '2009-02-12', 'MONTHS') -- NULL SELECT trunc(DATE '2009-02-12', 'DAY') -- NULL ``` DataFusion, existing tests: - [date_trunc.slt#L138](https://github.com/apache/datafusion/blob/9dec77584ea39d93aa01bba7f880b318b37d2c24/datafusion/sqllogictest/test_files/spark/datetime/date_trunc.slt#L138) expects an error for `date_trunc('test', ...)` and [trunc.slt#L97](https://github.com/apache/datafusion/blob/9dec77584ea39d93aa01bba7f880b318b37d2c24/datafusion/sqllogictest/test_files/spark/datetime/trunc.slt#L97) for `trunc(..., 'test')`. - [date_trunc.slt#L129](https://github.com/apache/datafusion/blob/9dec77584ea39d93aa01bba7f880b318b37d2c24/datafusion/sqllogictest/test_files/spark/datetime/date_trunc.slt#L129) and [trunc.slt#L93](https://github.com/apache/datafusion/blob/9dec77584ea39d93aa01bba7f880b318b37d2c24/datafusion/sqllogictest/test_files/spark/datetime/trunc.slt#L93) expect a planning error for a NULL format. - [date_trunc.slt#L57](https://github.com/apache/datafusion/blob/9dec77584ea39d93aa01bba7f880b318b37d2c24/datafusion/sqllogictest/test_files/spark/datetime/date_trunc.slt#L57) and [trunc.slt#L57](https://github.com/apache/datafusion/blob/9dec77584ea39d93aa01bba7f880b318b37d2c24/datafusion/sqllogictest/test_files/spark/datetime/trunc.slt#L57) expect `'MONTHS'` to truncate to the month. `trunc(DATE '2009-02-12', 'DAY')` is accepted by reading `trunc.rs`, which allows `DatePart::Day`. This was not run on DataFusion. ### Expected behavior Return NULL for names Spark does not support, and restrict the accepted names to the lists above. Supporting a NULL or column-valued format would need a kernel instead of the `simplify` rewrite, so that part can be a follow-up. The expectations listed above need to change. ### 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]
