This is an automated email from the ASF dual-hosted git repository.
github-merge-queue[bot] pushed a commit to branch main
in repository https://gitbox.apache.org/repos/asf/datafusion.git
The following commit(s) were added to refs/heads/main by this push:
new 6079a1e6ef fix: preserve EXTRACT year comparisons with unrepresentable
bounds (#25140)
6079a1e6ef is described below
commit 6079a1e6ef811958b41593b559b572df5bd55690
Author: Stefan Wang <[email protected]>
AuthorDate: Sat Sep 12 13:28:04 2026 +0000
fix: preserve EXTRACT year comparisons with unrepresentable bounds (#25140)
## Which issue does this PR close?
Closes https://github.com/apache/datafusion/issues/25135.
## Rationale for this change
Year 9999 cannot be represented as a nanosecond timestamp, but comparing
an extracted year with 9999 is valid. For timestamps in 1995 and 2020,
`EXTRACT(YEAR FROM ts) < 9999` should match both rows. DataFusion
instead returned no rows because its timestamp-range rewrite introduced
a NULL bound.
A skipped local midnight also prevents constructing a year boundary, as
in America/Lima on January 1, 1994.
## What changes are included in this PR?
Keep the original comparison when a timestamp bound cannot be
represented. Representable bounds still use the range rewrite.
## What is the testing strategy for this PR?
SQL regressions cover out-of-range years and the skipped Lima midnight,
including NULL input and all four timestamp units.
<details><summary>Raw logs</summary>
Before: [the pre-fix
base](https://github.com/apache/datafusion/commit/4086969669f96c0d5de03f442f2dba1869d2ee80),
with this PR's SQL test file. Output excerpts omit compilation.
```console
$ SDKROOT="$(xcrun --sdk macosx15.4 --show-sdk-path)"
CARGO_TARGET_DIR=../datafusion/target cargo test -p datafusion-sqllogictest
--test sqllogictests -- date_part:1915
[Diff] (-expected|+actual)
NULL NULL NULL NULL NULL
- 1993-12-31T23:59:59-05:00 false false false false
- 1994-01-01T01:00:00-04:00 true true true true
+ 1993-12-31T23:59:59-05:00 NULL NULL NULL NULL
+ 1994-01-01T01:00:00-04:00 NULL NULL NULL NULL
at test_files/datetime/date_part.slt:1915
exit_code=1
```
After: the full SQL file on this PR, with isolated build output.
```console
$ SDKROOT="$(xcrun --sdk macosx15.4 --show-sdk-path)"
CARGO_TARGET_DIR=target CARGO_BUILD_JOBS=2 cargo test -p
datafusion-sqllogictest --test sqllogictests --
test_files/datetime/date_part.slt
Progress: 1/1 files completed (100%)
exit_code=0
```
</details>
## Are there any user-facing changes?
Year comparisons return correct results instead of NULL when a timestamp
bound is unavailable. No API change.
---------
Signed-off-by: 1fanwang <[email protected]>
Co-authored-by: Neil Conway <[email protected]>
---
datafusion/functions/src/datetime/date_part.rs | 21 ++++--
.../sqllogictest/test_files/datetime/date_part.slt | 78 ++++++++++++++++++++++
2 files changed, 92 insertions(+), 7 deletions(-)
diff --git a/datafusion/functions/src/datetime/date_part.rs
b/datafusion/functions/src/datetime/date_part.rs
index 4bfadf7999..c7f7ef88c0 100644
--- a/datafusion/functions/src/datetime/date_part.rs
+++ b/datafusion/functions/src/datetime/date_part.rs
@@ -326,30 +326,37 @@ fn date_to_scalar(date: NaiveDate, target_type:
&DataType) -> Option<ScalarValue
let naive_midnight = date.and_hms_opt(0, 0, 0)?;
let tz: Option<Tz> = tz_opt.clone().and_then(|s| s.parse().ok());
+ // Midnight has no representation when the date falls outside the
+ // unit's range, or when the zone skips it. Reporting no bound
keeps
+ // the caller on the original predicate; a NULL bound here would
make
+ // the rewritten comparison NULL on every row.
match unit {
Second => ScalarValue::TimestampSecond(
- TimestampSecondType::from_naive_datetime(naive_midnight,
tz.as_ref()),
+ Some(TimestampSecondType::from_naive_datetime(
+ naive_midnight,
+ tz.as_ref(),
+ )?),
tz_opt.clone(),
),
Millisecond => ScalarValue::TimestampMillisecond(
- TimestampMillisecondType::from_naive_datetime(
+ Some(TimestampMillisecondType::from_naive_datetime(
naive_midnight,
tz.as_ref(),
- ),
+ )?),
tz_opt.clone(),
),
Microsecond => ScalarValue::TimestampMicrosecond(
- TimestampMicrosecondType::from_naive_datetime(
+ Some(TimestampMicrosecondType::from_naive_datetime(
naive_midnight,
tz.as_ref(),
- ),
+ )?),
tz_opt.clone(),
),
Nanosecond => ScalarValue::TimestampNanosecond(
- TimestampNanosecondType::from_naive_datetime(
+ Some(TimestampNanosecondType::from_naive_datetime(
naive_midnight,
tz.as_ref(),
- ),
+ )?),
tz_opt.clone(),
),
}
diff --git a/datafusion/sqllogictest/test_files/datetime/date_part.slt
b/datafusion/sqllogictest/test_files/datetime/date_part.slt
index 5bbd93f8ad..18a81bd94f 100644
--- a/datafusion/sqllogictest/test_files/datetime/date_part.slt
+++ b/datafusion/sqllogictest/test_files/datetime/date_part.slt
@@ -1859,3 +1859,81 @@ logical_plan
physical_plan
01)FilterExec: c@0 >= 2024-01-01 AND c@0 < 2025-01-01
02)--DataSourceExec: partitions=1, partition_sizes=[1]
+
+# A bound whose January 1 falls outside the timestamp unit's range must leave
the
+# predicate alone rather than rewriting it against a NULL bound.
+
+statement ok
+CREATE VIEW year_bound AS SELECT * FROM (VALUES
+ ('2020-06-01T12:00:00'::timestamp),
+ ('1995-06-01T12:00:00'::timestamp)) s(ts);
+
+query I
+SELECT count(*) FROM year_bound WHERE EXTRACT(YEAR FROM ts) < 9999;
+----
+2
+
+query I
+SELECT count(*) FROM year_bound WHERE EXTRACT(YEAR FROM ts) <= 2262;
+----
+2
+
+query I
+SELECT count(*) FROM year_bound WHERE EXTRACT(YEAR FROM ts) >= 1000 OR
EXTRACT(YEAR FROM ts) = 2020;
+----
+2
+
+query IB rowsort
+SELECT EXTRACT(YEAR FROM ts), EXTRACT(YEAR FROM ts) < 9999 FROM year_bound;
+----
+1995 true
+2020 true
+
+# A bound inside the nanosecond range still rewrites as before.
+
+query I
+SELECT count(*) FROM year_bound WHERE EXTRACT(YEAR FROM ts) >= 1678;
+----
+2
+
+statement ok
+DROP VIEW year_bound;
+
+# Lima skipped midnight on 1994-01-01.
+statement ok
+CREATE TABLE year_bound_lima AS
+SELECT
+ arrow_cast(ts, 'Timestamp(Second, Some("America/Lima"))') AS ts_sec,
+ arrow_cast(ts, 'Timestamp(Millisecond, Some("America/Lima"))') AS ts_milli,
+ arrow_cast(ts, 'Timestamp(Microsecond, Some("America/Lima"))') AS ts_micro,
+ arrow_cast(ts, 'Timestamp(Nanosecond, Some("America/Lima"))') AS ts_nano
+FROM (VALUES
+ (NULL),
+ ('1994-01-01T04:59:59Z'),
+ ('1994-01-01T05:00:00Z')) AS input(ts);
+
+query PBBBB
+SELECT ts_nano,
+ EXTRACT(YEAR FROM ts_sec) = 1994,
+ EXTRACT(YEAR FROM ts_milli) = 1994,
+ EXTRACT(YEAR FROM ts_micro) = 1994,
+ EXTRACT(YEAR FROM ts_nano) = 1994
+FROM year_bound_lima
+ORDER BY ts_nano NULLS FIRST;
+----
+NULL NULL NULL NULL NULL
+1993-12-31T23:59:59-05:00 false false false false
+1994-01-01T01:00:00-04:00 true true true true
+
+query P
+SELECT ts_nano FROM year_bound_lima WHERE EXTRACT(YEAR FROM ts_nano) = 1994;
+----
+1994-01-01T01:00:00-04:00
+
+query I
+SELECT count(*) FROM year_bound_lima WHERE EXTRACT(YEAR FROM ts_nano) = 1993;
+----
+1
+
+statement ok
+DROP TABLE year_bound_lima;
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
