date_part('isodow') / EXTRACT(isodow) returns 0..=6 instead of PostgreSQL's 1..=7

[sdf-jkl]

Describe the bug

It seems like a little bit of confusion was introduced in #17112

date_part('isodow', ...) and EXTRACT(isodow FROM ...) return values in 0..=6 (Mon=0, …, Sun=6) instead of PostgreSQL's documented 1..=7 (Mon=1, …, Sun=7). https://www.postgresql.org/docs/current/functions-datetime.html

Root cause: datafusion/functions/src/datetime/date_part.rs:242 maps "isodow" to DatePart::DayOfWeekMonday0, which arrow implements as chrono::Datelike::num_days_from_monday() — defined as 0..=6 with Mon=0. Every value is off by -1 and the range is wrong (df can return 0, never 7; PG is the inverse).

To Reproduce

-- 2000-01-01 was a Saturday; PG isodow should be 6
SELECT date_part('isodow', CAST('2000-01-01' AS DATE));
-- df returns: 5

-- 2020-09-08 was a Tuesday; PG isodow should be 2
SELECT EXTRACT(isodow FROM to_timestamp('2020-09-08T12:00:00+00:00'));
-- df returns: 1

Both wrong values exactly match DayOfWeekMonday0 semantics. The bug is also baked into the test corpus at datafusion/sqllogictest/test_files/datetime/date_part.slt:1243 and :1248,1253,1258, so the test suite passes against the buggy output.

Expected behavior

PostgreSQL-compatible isodow per ISO 8601:

Day	PG isodow	df returns
Mon	1	0
Tue	2	1
Wed	3	2
Thu	4	3
Fri	5	4
Sat	6	5
Sun	7	6

Additional context

Caller-visible breakage

• WHERE date_part('isodow', d) = 7 returns zero rows in df (PG: all Sundays).
• WHERE date_part('isodow', d) = 0 returns Mondays in df (PG: zero rows / domain error).
• Cross-engine SQL silently diverges; users porting from PG/DuckDB/Snowflake get value-shifted results.

Coordination with the Spark wrapper (two bugs that cancel today)

datafusion/spark/src/function/datetime/date_part.rs:127-133 post-evaluates a + 1 for both dow and isodow:

match part {
    \"dow\" | \"isodow\" => date_part_expr + 1,
    _ => date_part_expr,
}

Per the Spark docs:

• `DAYOFWEEK` (alias `DOW`) — Sun(1) → Sat(7)
• `DAYOFWEEK_ISO` (alias `DOW_ISO`) — Mon(1) → Sun(7), ISO 8601

For a known Friday (2011-05-06, Spark's own extract.sql fixture), real Spark returns:

• extract(dayofweek) = 6
• extract(dayofweek_iso) = 5

The df-spark wrapper currently produces those same values, but the two +1s do semantically different jobs:

• dow's +1 is a real convention shift: df's dow is PG's 0..=6 Sun=0; Spark's dayofweek is 1..=7 Sun=1. Same anchor, different numbering — +1 bridges them. Right answer for the right reason.
• isodow's +1 is bug compensation: df's isodow and Spark's dayofweek_iso should have the same convention (Mon=1, ISO 8601). No shift should be needed. The +1 works only because df's isodow is wrong by -1. Right answer by accident — BUG_in_df + extra_+1_in_wrapper = correct_observable_output.

Fixing df's isodow standalone silently breaks Spark's dayofweek_iso (becomes 2..=8). The Spark wrapper's isodow branch must drop the + 1 in the same PR.

Proposed fix chain

1. arrow-rs: add DatePart::DayOfWeekMonday1 variant returning num_days_from_monday() + 1 (1..=7, Mon=1). Non-breaking thanks to #[non_exhaustive]. Symmetric with the existing Week/WeekISO and Year/YearISO pairs.
2. datafusion date_part.rs:242: \"isodow\" => DatePart::DayOfWeekMonday1.
3. datafusion Spark date_part.rs:127-133: drop \"isodow\" from the + 1 match (keep \"dow\" — the genuine convention shift).
4. datafusion date_part.slt: update lines 1243 (5→6) and 1248/1253/1258 (1→2) to PG-correct expectations.

Steps 2–4 ship as one datafusion PR once step 1 lands in arrow. An interim datafusion PR can use DayOfWeekMonday0 + 1 array arithmetic in step 2 to ship the fix today, and swap to the cleaner variant when it ships in arrow.

[sdf-jkl]

take