comphead opened a new issue, #26336: URL: https://github.com/apache/datafusion/issues/26336
### Describe the bug `SparkDatePart` declares `Int32` as its return type ([date_part.rs#L87](https://github.com/apache/datafusion/blob/9dec77584ea39d93aa01bba7f880b318b37d2c24/datafusion/spark/src/function/datetime/date_part.rs#L87)) and rewrites the call to the core `date_part`, which returns the integer second field. In Spark, `date_part('SECOND', ts)` and `extract(SECOND FROM ts)` return `DECIMAL(8, 6)` with the microsecond fraction ([datetimeExpressions.scala#L455-L460](https://github.com/apache/spark/blob/v4.0.0/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/expressions/datetimeExpressions.scala#L455-L460), mapped from `SECOND`, `S`, `SEC`, `SECONDS` and `SECS` at [#L3051](https://github.com/apache/spark/blob/v4.0.0/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/expressions/datetimeExpressions.scala#L3051)). The separate `second(ts)` function returns an `INT`, and `SparkSecond` is fine. So the fractional seconds are dropped. ### To Reproduce Spark 4.0.0: ```sql SELECT date_part('SECOND', TIMESTAMP '2000-01-01 12:30:45.123456') -- 45.123456, DECIMAL(8,6) SELECT extract(SECOND FROM TIMESTAMP '2000-01-01 12:30:45.123456') -- 45.123456, DECIMAL(8,6) SELECT date_part('SECOND', TIMESTAMP '2000-01-01 12:30:45') -- 45.000000, DECIMAL(8,6) SELECT second(TIMESTAMP '2000-01-01 12:30:45.123456') -- 45, INT ``` DataFusion: [date_part.slt#L206](https://github.com/apache/datafusion/blob/9dec77584ea39d93aa01bba7f880b318b37d2c24/datafusion/sqllogictest/test_files/spark/datetime/date_part.slt#L206) expects an integer for a whole second timestamp, and the aliases right after it do the same: ``` query I SELECT date_part('SECOND'::string, '2000-01-01 12:30:45'::timestamp) ---- 45 ``` The tests only use whole seconds, so they do not show the lost fraction. For `12:30:45.123456` DataFusion returns `45` by reading the code (not run). ### Expected behavior For the `SECOND` family return `Decimal128(8, 6)` with the fractional seconds, and update the expectations in `date_part.slt`. A test with a fractional second would show the difference. ### 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]
