danielalyoshin opened a new pull request, #44805: URL: https://github.com/apache/superset/pull/44805
<!--- Please write the PR title following the conventions at https://www.conventionalcommits.org/en/v1.0.0/ Example: fix(dashboard): load charts correctly --> ### SUMMARY <!--- Describe the change below, including rationale and design decisions --> `SqliteEngineSpec.convert_dttm` only handles `String` and `DateTime` columns and returns `None` for `Date`. Superset then falls back to a literal such as `'2026-09-20 00:00:00.000000'`. SQLite has no date type, so a `DATE` column usually holds text such as `2026-09-20` and is compared as text, and that literal sorts after the day it starts. A time range on a `DATE` column therefore starts and ends one day late: 2026-09-20 to 2026-09-22 returns 09-21 and 09-22 instead of 09-20 and 09-21. This PR writes midnight as a bare date for `DATE` columns, the same fix #44505 made for D1, and close to what #43355 did for Google Sheets. A time other than midnight keeps its time part (`'2026-09-20 12:00:00'`), which sorts between two days, so each day counts as its midnight, as in a comparison of dates. `DATETIME`, `TIMESTAMP` and text columns are unchanged. Specs that inherit `convert_dttm` from `SqliteEngineSpec`: * `ShillelaghEngineSpec` and `SupersetEngineSpec` (the meta database) change too. Shillelagh reads a date filter with `date.fromisoformat`, which rejects a time part, so on the meta database a filter on a `DATE` column was dropped and every row came back. Midnight bounds now parse, so whole-day ranges are applied. A bound with a time part is still dropped there, as before. * `GSheetsEngineSpec` and `CloudflareD1EngineSpec` have their own override and do not change. The D1 override now does the same as the SQLite one; I left it in place. One behavior change to be aware of: for a `DATE` column the literal now comes from the engine spec, so the column's **Datetime format** (`python_date_format`) is no longer used in its time filters. SQLite already works this way for `TEXT` and `DATETIME` columns. A `DATE` column that holds numbers, such as epoch seconds with the format `epoch_s`, got the right rows before and gets none now. Columns declared as `INTEGER` are not affected. Tests: * `test_sqlite.py`: `convert_dttm` for `DATE` at midnight and at other times, and a range test on an in-memory SQLite. * `test_shillelagh.py`: `convert_dttm` for `DATE` on `ShillelaghEngineSpec` and `SupersetEngineSpec`. * `extensions/test_sqlalchemy.py`: a range test on a `DATE` column through the meta database. The two range tests build their bounds with `dttm_sql_literal`, the call the time filter uses, so on master they return the wrong rows: 09-21 and 09-22 on SQLite, all four days on the meta database. The change cherry-picks cleanly onto `7.0`, and the changed test files pass there (checked against `78611f6c0b`). ### BEFORE/AFTER SCREENSHOTS OR ANIMATED GIF <!--- Skip this if not applicable --> Rows returned by the chart query path (`SqlaTable.get_query_result` with a `TEMPORAL_RANGE` filter) for a table holding 2026-09-19 to 2026-09-22: | Column and range | Before | After | |------------------|--------|-------| | `DATE`, 09-20 to 09-22 | 09-21, 09-22 | 09-20, 09-21 | | `DATE`, 09-20 12:00 to 09-22 12:00 | 09-21, 09-22 | 09-21, 09-22 | | `DATETIME`, 09-20 to 09-22 | 09-20, 09-21 | 09-20, 09-21 | | `DATE` on the meta database, 09-20 to 09-22 | all four days | 09-20, 09-21 | | `DATE` holding epoch seconds, format `epoch_s`, 09-20 to 09-22 | 09-20, 09-21 | none | ### TESTING INSTRUCTIONS <!--- Required! What steps can be taken to manually verify the changes? --> Unit tests: ```bash pytest tests/unit_tests/db_engine_specs/test_sqlite.py tests/unit_tests/db_engine_specs/test_shillelagh.py tests/unit_tests/extensions/test_sqlalchemy.py ``` By hand: 1. Make a SQLite file with `CREATE TABLE t (day DATE); INSERT INTO t VALUES ('2026-09-19'), ('2026-09-20'), ('2026-09-21'), ('2026-09-22');` and connect it to Superset (SQLite needs `PREVENT_UNSAFE_DB_CONNECTIONS = False`). 2. Create a dataset from `t` and a table chart on `day` with a custom time range from 2026-09-20 to 2026-09-22. 3. Before this PR the chart shows 09-21 and 09-22. With it, 09-20 and 09-21. ### ADDITIONAL INFORMATION <!--- Check any relevant boxes with "x" --> <!--- HINT: Include "Fixes #nnn" if you are fixing an existing issue --> - [ ] Has associated issue: - [ ] Required feature flags: - [ ] Changes UI - [ ] Includes DB Migration (follow approval process in [SIP-59](https://github.com/apache/superset/issues/13351)) - [ ] Migration is atomic, supports rollback & is backwards-compatible - [ ] Confirm DB migration upgrade and downgrade tested - [ ] Runtime estimates and downtime expectations provided - [ ] 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]
