You are viewing a plain text version of this content. The canonical link for it is here.
Posted to github@datafusion.apache.org by "comphead (via GitHub)" <gi...@apache.org> on 2026/10/14 23:25:56 UTC
[I] [Bug] date_part('SECOND', ts) drops the fractional seconds, Spark returns decimal(8,6) [datafusion]
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: github-unsubscribe@datafusion.apache.org.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org
Re: [I] [Bug] date_part('SECOND', ts) drops the fractional seconds, Spark returns decimal(8,6) [datafusion]
Posted by "founderinaction (via GitHub) via github" <gi...@datafusion.apache.org>.
founderinaction commented on issue #26336:
URL: https://github.com/apache/datafusion/issues/26336#issuecomment-6710168193
take
--
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: github-unsubscribe@datafusion.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org
Re: [I] [Bug] date_part('SECOND', ts) drops the fractional seconds, Spark returns decimal(8,6) [datafusion]
Posted by "founderinaction (via GitHub) via github" <gi...@datafusion.apache.org>.
founderinaction commented on issue #26336:
URL: https://github.com/apache/datafusion/issues/26336#issuecomment-6710668167
Draft comment for apache/datafusion#26336. DataFusion asks for replies in your own words, so reword it before posting.
---
I have the three fixes working locally against the Spark 4.2 source. Field names are now a closed list matching `DatePart.parseExtractField`, so `QUARTERS`, `DAYOFYEAR` and other non-Spark spellings get a plan error. A NULL field returns NULL (Spark replaces it with a `DOUBLE` null). SECOND returns `Decimal(8, 6)`.
One design question before I open the PR. The return type now depends on the field literal, but since #23232 an explicit cast isn't passed to `return_field_from_args` as a scalar argument. So for `date_part('SECOND'::string, ts)`, the style `date_part.slt` uses, planning declares `Int32`. After the cast is folded, the type becomes `Decimal(8, 6)` and the optimizer rejects the schema change. A plain literal, `date_part('SECOND', ts)`, works. Integer fields work either way, because their type doesn't change.
The options I can see:
1. Require a plain literal for SECOND and a NULL field (the cast form errors), and switch those slt cases to plain literals. This matches how core `date_part` already requires a literal field.
2. Keep `Int32` for SECOND, which leaves the fractional-seconds part of this issue unfixed.
3. Expose string-to-string literal casts as scalar arguments, which would revisit the explicit-cast rule from #23232.
I'd go with 1, unless you'd prefer a different approach. @andygrove does that work for you?
--
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: github-unsubscribe@datafusion.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org
Re: [I] [Bug] date_part('SECOND', ts) drops the fractional seconds, Spark returns decimal(8,6) [datafusion]
Posted by "andygrove (via GitHub) via github" <gi...@datafusion.apache.org>.
andygrove commented on issue #26336:
URL: https://github.com/apache/datafusion/issues/26336#issuecomment-6698730959
Running `date_part.slt` on Spark 4.2.0 with the runner in #27165 found two more differences:
- `QUARTERS` and `DAYOFYEAR` are accepted, but Spark raises `INVALID_EXTRACT_FIELD` for both. Spark accepts `QUARTER`, `QTR` and `DOY` ([datetimeExpressions.scala#L3361-L3373](https://github.com/apache/spark/blob/v4.2.0/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/expressions/datetimeExpressions.scala#L3361-L3373)). The list of field names is the same in every Spark version from 3.4 to 4.2, so this does not depend on the version.
- `date_part(NULL::string, '2000-01-01'::date)` is an error, while Spark 4.2.0 returns NULL.
--
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: github-unsubscribe@datafusion.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org