Skip to content

Commit 33574a1

Browse files
adriangbclaudealamb
authored
test: PostgreSQL differential coverage for timestamps with time zone (#25164)
## Which issue does this PR close? This PR closes no issue. It adds tests only. Related to these issues. Each one is a divergence from PostgreSQL, so this PR keeps its queries out of the new file: - #12218 - #12892 - #13212 - #25084 - #25095 - #25166 - #25170 Companion PR: #25175 pins the current DataFusion behaviour, bugs included. ## Rationale for this change Set the session time zone to `America/Denver` in both engines. Then this query gives the same answer in DataFusion and in PostgreSQL: ```sql -- ts is 2024-03-09 12:00 MST, one day before the spring-forward transition SELECT date_part('hour', ts + INTERVAL '1 day')::bigint AS plus_1_day, date_part('hour', ts + INTERVAL '24 hours')::bigint AS plus_24_hours FROM tstz_den WHERE id = 1; ``` | Engine | `plus_1_day` | `plus_24_hours` | | --- | --- | --- | | DataFusion | `12` | `13` | | PostgreSQL 15 | `12` | `13` | `INTERVAL '1 day'` keeps the local wall clock. `INTERVAL '24 hours'` adds 24 hours of elapsed time, so the local hour moves by one. **What this PR guarantees.** CI runs each query of the new file on DataFusion and on PostgreSQL 15. If the answers differ, the build fails. So a change cannot move DataFusion away from PostgreSQL on these queries without a red build. ### Why time zone bugs recur - Time zone rules are hard to check by reading code. Two engines can each look correct and still give different answers. - On `main` before this PR, the sqllogictest corpus has 40 `SET datafusion.execution.time_zone` statements, in 9 files. - The PostgreSQL differential harness (`test_files/pg_compat/`) has no time zone coverage. No file in it refers to a time zone or to `timestamptz`. - So no test compares the time zone behaviour of DataFusion with a reference engine. A change can alter an answer, and no test fails. ## What changes are included in this PR? This PR adds one test file and changes no production code: `datafusion/sqllogictest/test_files/pg_compat/pg_compat_timestamptz.slt` (about 700 lines). ### Sections Each block sets the session time zone in both engines: `SET TimeZone` for PostgreSQL, and `SET datafusion.execution.time_zone` for DataFusion. Each block builds its table in the same zone. | Block | Session time zone | Coverage | | --- | --- | --- | | A | `UTC` (DataFusion `+00:00`) | `timestamptz` literals with and without an offset; `AT TIME ZONE` on naive values; comparison by instant; `DISTINCT`, `count(DISTINCT)`, `min`, `max`, `GROUP BY`, `ORDER BY`; filters with aware literals; a self join; `UNION`, `COALESCE`, `CASE`, `greatest`, `least`; `date_part` and `extract`; `to_timestamp`; `date_bin` with an explicit origin; interval arithmetic | | B | `America/Denver` | local `date_part` readings; `'1 day'` against `'24 hours'` across both DST transitions; the repeated hour at fall back; `date_trunc` at day, month and hour; `date_bin`; week, month and year arithmetic; `min`, `max` and `ORDER BY` by instant | | C | `Asia/Kolkata`, then `America/Phoenix` | a half-hour offset, and a zone with no DST | ### How we made the expected output 1. We ran the sqllogictest `--complete` mode against a real PostgreSQL (`PG_COMPAT=true PG_URI=... --complete`). So the answer of PostgreSQL is the expected output, and DataFusion must match it. 2. We read each result. 3. We removed each query where the two engines disagree. The list is in "Field research" below. ### Harness constraints 1. **No aware value in a result.** The PostgreSQL runner cannot render `timestamptz`: `postgres_engine/mod.rs` calls `unimplemented!` for that type. So each result is a naive `timestamp`, a `bigint`, a `boolean` or `text`. 2. **Session zone against value zone.** PostgreSQL uses the session time zone to read a naive literal, to render a `timestamptz`, to apply a day interval and to run `date_trunc`. DataFusion uses the time zone of the value. The two agree only when both zones are the same. So each block sets them to the same zone. 3. **A naive projection must not hide a divergence.** Block A projects with `::timestamp`. That projection gives the UTC wall clock in both engines only because the session time zone is UTC. Blocks B and C read each value through `date_part`. The self-review found one query that breaks this rule (lines 112-118). A follow-up commit must fix it. ## What is the testing strategy for this PR? This PR adds tests only. Checks at head `2b0a37fb91`: - `PG_COMPAT=true PG_URI=... cargo test --profile ci -p datafusion-sqllogictest --features postgres --test sqllogictests -- pg_compat` against `postgres:15`: 7 of 7 files pass. - With `log_statement = 'all'` on the server, the PostgreSQL log shows the harness execute each statement of this file, with zero errors. So the run really compares the two engines. - The same command without `PG_COMPAT` (DataFusion only): the file passes. - CI on 2026-09-10: all 38 checks pass, the "Run sqllogictest with Postgres runner" job included. - On 2026-09-11, #25182 added `*.slt` to the license header check. The new file now has `#` on header lines 8 and 10, as `main` expects (fixed in `aafa2e7df4`). ### Field research #### PostgreSQL version - CI runs the harness against `postgres:15`: see the `sqllogictest-postgres` job in `.github/workflows/rust.yml`. - The harness compares text. If a value renders differently in another PostgreSQL version, CI fails, even when the instant is correct. So generate and check the expected output against `postgres:15`, not against a newer local server. - An earlier revision of this PR failed in CI on `extract(epoch ...)`. The expected output had `1719792000`, and CI produced `1719792000.000000`. The file now casts each `extract` and `date_part` result to `bigint`. - We measured that value in `psql` on PostgreSQL 15.19 and 17.11. Both give `numeric` `1719792000.000000`. So `psql` shows no version difference for this value, and the cause of the earlier mismatch is not confirmed. #### Divergences that this file leaves out We measured each answer. The DataFusion answers come from the pinned expectations in #25175, or from `datafusion-cli` at the same base. PostgreSQL answers come from `postgres:15`. | # | Query | Session time zone | DataFusion | PostgreSQL 15 | Issue | | --- | --- | --- | --- | --- | --- | | 1 | `arrow_typeof('2024-07-01 12:00:00Z'::timestamptz)` (PostgreSQL: `pg_typeof`) | DataFusion unset, PostgreSQL `UTC` | `Timestamp(ns)` (naive) | `timestamp with time zone` | #25166 | | 2 | `'2024-07-01 12:00:00'::timestamp::timestamptz::timestamp` | `America/Denver` | `2024-07-01T18:00:00` | `2024-07-01 12:00:00` | #12218 | | 3 | `ts AT TIME ZONE 'Europe/Brussels'`, where `ts` is the aware value `2024-07-01 12:00:00Z` | DataFusion `+00:00`, PostgreSQL `UTC` | `Timestamp(ns, "Europe/Brussels")` `2024-07-01T14:00:00+02:00` | `timestamp without time zone` `2024-07-01 14:00:00` | #12218 | | 4 | `TIMESTAMP '2024-07-01 12:00:00' AT TIME ZONE '+05:30'` | DataFusion unset, PostgreSQL `UTC` | `2024-07-01T12:00:00+05:30` (06:30 UTC) | `2024-07-01 17:30:00+00` | #25170 | | 5 | `WHERE ts > '2024-07-01 06:00:00'` on rows at 00:00, 06:00, 12:00 and 18:00 UTC; DataFusion column zone `America/Denver` | DataFusion unset, PostgreSQL `UTC` | 1 row (18:00 UTC) | 2 rows (12:00 and 18:00 UTC) | closest: #13212 | | 6 | `WHERE ts = '2024-07-01T06:00:00Z'::timestamptz` on the same rows | DataFusion unset, PostgreSQL `UTC` | the 12:00 UTC row | the 06:00 UTC row | #25166 | | 7 | naive column `= '2024-07-01T18:00:00Z'::timestamptz`, for the naive row `2024-07-01 12:00:00` | `America/Denver` | 0 rows | 1 row | #25095 | | 8 | `date_trunc('day', ts)` for `2024-07-01T00:00:00Z`; DataFusion column zone `America/Denver` | DataFusion unset, PostgreSQL `UTC` | `2024-06-30T00:00:00-06:00` | `2024-07-01 00:00:00+00` | no issue | | 9 | `(TIMESTAMP '2024-01-15 12:00:00' AT TIME ZONE 'America/Denver') = (TIMESTAMP '2024-07-01 12:00:00' AT TIME ZONE 'America/Denver') - INTERVAL '168 days'` | DataFusion `+00:00`, PostgreSQL `UTC` | `true` | `f` | no issue | | 10 | `'2024-11-03 01:30:00'::timestamptz` (repeated hour) | `America/Denver` | error: `error computing timezone offset` | `2024-11-03 01:30:00-07` | #25084 | | 11 | `'2024-03-10 02:30:00'::timestamptz` (hour that does not exist) | `America/Denver` | error: `error computing timezone offset` | `2024-03-10 03:30:00-06` | #25084 | | 12 | `date_part('timezone_hour', ts)` in July | `America/Denver` | error: `Date part 'timezone_hour' not supported` | `-6` | fix in #25163 | | 13 | `date_bin` with a `1 month` stride | DataFusion unset, PostgreSQL `UTC` | returns a bin | error: `timestamps cannot be binned into intervals containing months or years` | no issue | | 14 | `from_unixtime(1719792000)` (PostgreSQL: `to_timestamp(1719792000)`) | `America/Denver` (PostgreSQL `UTC`) | `Timestamp(s)` `2024-07-01T00:00:00` (naive) | `timestamp with time zone` | #12892 | Rows 5 to 9 have one cause in common: DataFusion uses the time zone of the value, and PostgreSQL uses the session time zone. That is why each block of this file sets the two to the same zone. ## Are there any user-facing changes? No. This PR adds one test file. It changes no production code, no public API and no behaviour. 🤖 Generated with [Claude Code](https://claude.com/claude-code) --------- Co-authored-by: Claude Opus 5 <[email protected]> Co-authored-by: Andrew Lamb <[email protected]>
1 parent 87b1f6a commit 33574a1

1 file changed

Lines changed: 704 additions & 0 deletions

File tree

0 commit comments

Comments
 (0)