dhruv-techdev opened a new pull request, #45858: URL: https://github.com/apache/superset/pull/45858
### SUMMARY The `Week` time grain for Oracle and Impala used `TRUNC(..., 'WW')`. In both databases, `'WW'` truncates to the **same weekday as January 1st**, not to a fixed week start. As a result: - The weekday a week starts on changes every year (2025 weeks start on Wednesday, 2026 weeks on Thursday). - Every year ends with a 1-day "week" (2 days in leap years), e.g. `2025-12-31` gets its own bucket. - Weeks never cross a year boundary. This PR changes the unit to one that always truncates to Monday, matching the fixed week start used by other engines: - **Oracle:** `'WW'` → `'IW'` (ISO week). `'IW'` doesn't depend on `NLS_TERRITORY`, unlike `'D'`/`'DY'`. - **Impala:** `'WW'` → `'D'`. Impala's `TRUNC` does not accept `'IW'`; `'D'`/`'DY'`/`'DAY'` truncate to the previous Monday (see `TruncDayOfWeek` / `StrToTruncUnit` in [`be/src/exprs/udf-builtins.cc`](https://github.com/apache/impala/blob/master/be/src/exprs/udf-builtins.cc)). The change is one line per engine spec, plus a regression test for each that pins the generated SQL. ### BEFORE/AFTER SCREENSHOTS OR ANIMATED GIF Oracle Database 23ai Free (`gvenzl/oracle-free:23-slim`). `BEFORE (WW)` is the old `TimeGrain.WEEK` expression and `AFTER (IW)` is the new one: <img width="780" height="383" alt="Screenshot 2026-10-29 at 15 15 23" src="https://github.com/user-attachments/assets/d5dc6df4-fb97-4f02-a9f9-2e5ceb2f91be"; /> ### TESTING INSTRUCTIONS **Unit tests** ```bash pytest tests/unit_tests/db_engine_specs/test_oracle.py tests/unit_tests/db_engine_specs/test_impala.py ``` Both new tests fail on `master` (`'WW'` vs `'IW'` / `'D'`) and pass with this change. **Manual check on Oracle** ```bash docker run -d --name oracle-ww -p 1521:1521 -e ORACLE_PASSWORD=test gvenzl/oracle-free:23-slim until docker logs oracle-ww 2>&1 | grep -q "DATABASE IS READY TO USE"; do sleep 5; done docker exec -i oracle-ww sqlplus -s system/test@//localhost/FREEPDB1 <<'SQL' ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD DY'; SELECT d, TRUNC(CAST(d AS DATE), 'WW') AS old_week, TRUNC(CAST(d AS DATE), 'IW') AS new_week FROM ( SELECT DATE '2025-12-30' d FROM dual UNION ALL SELECT DATE '2025-12-31' FROM dual UNION ALL SELECT DATE '2026-01-05' FROM dual ); SQL docker rm -f oracle-ww ``` `new_week` should always be a Monday. In the UI, a time-series chart on an Oracle dataset with **Time grain = Week** should show Monday-aligned buckets and no stub week at year end. ### ADDITIONAL INFORMATION - [x] Has associated issue: Fixes #45857 - [ ] 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 **Notes for reviewers** - Oracle behaviour was verified on a live Oracle 23ai Free instance (output above). Impala's `'D'` is verified against the Impala source; it has not been run on a live Impala instance. - Existing Oracle/Impala charts using the Week grain will regroup into Monday-start weeks. Happy to add an `UPDATING.md` entry if reviewers consider this a breaking change. -- 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]
