aminghadersohi opened a new pull request, #44700:
URL: https://github.com/apache/superset/pull/44700
### SUMMARY
`DataFusionEngineSpec` renders temporal SQL that Apache DataFusion rejects:
- **`convert_dttm`** emits `TO_TIMESTAMP('2024-03-31 23:59:59.999',
'YYYY-MM-DD HH24:MI:SS.FFF')` and `TO_DATE('2024-03-31', 'YYYY-MM-DD')`.
DataFusion's `to_timestamp`/`to_date` take [chrono format
strings](https://datafusion.apache.org/user-guide/sql/scalar_functions.html#to-timestamp),
so both fail with `Error parsing timestamp`. Every chart time-range filter on
a DataFusion dataset therefore errors.
- **Sub-hour grains** (`PT5S`, `PT30S`, `PT5M`, `PT10M`, `PT15M`, `PT30M`)
compute `DATE_TRUNC(...) + INTERVAL 'n unit' * FLOOR(...)`. DataFusion's
planner rejects multiplying an interval by a `Float64`: `Invalid interval
arithmetic operation: Interval(MonthDayNano) * Interval(MonthDayNano)`.
This PR renders ANSI typed literals (`TIMESTAMP '...'`, `DATE '...'`) in
`convert_dttm`, and uses DataFusion's native
[`DATE_BIN`](https://datafusion.apache.org/user-guide/sql/scalar_functions.html#date-bin)
for the six sub-hour grains, with the Unix epoch as origin. The other grains
are unchanged.
### TESTING INSTRUCTIONS
Unit tests updated: `pytest
tests/unit_tests/db_engine_specs/test_datafusion.py` (20 passed).
Also executed live: every grain expression and each `convert_dttm` output
ran against Apache DataFusion 55.1.0 served over Arrow Flight SQL
(`datafusion-flight-sql-server` 0.4.19), using the `datafusion://` dialect from
`flightsql-dbapi`. Before this change, the six sub-hour grains and all
`convert_dttm` outputs fail. After it, each returns the expected bucket, e.g.
`2024-03-31 23:59:59` → `PT15M` `2024-03-31 23:45:00`, `PT5S` `2024-03-31
23:59:55`, and `TIMESTAMP '2024-03-31 23:59:59.999000'` → `2024-03-31
23:59:59.999000`. `DATE_BIN` also works on timezone-aware timestamp columns and
on `DATE` columns.
### ADDITIONAL INFORMATION
- [ ] Has associated issue:
- [ ] Required feature flags:
- [ ] Changes UI
- [ ] Includes DB Migration (follow approval process in
[SIP-59](https://github.com/apache/superset/issues/13351))
- [ ] Introduces new feature or API
- [ ] Removes existing feature or API
--
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]
