This is an automated email from the ASF dual-hosted git repository. github-merge-queue[bot] pushed a commit to branch gh-readonly-queue/main/pr-25140-14b81008a28d6e40f59cbd6f61886f423f03e5ac in repository https://gitbox.apache.org/repos/asf/datafusion.git
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]
