You are viewing a plain text version of this content. The canonical link for it is here.
Posted to github@datafusion.apache.org by "1fanwang (via GitHub)" <gi...@apache.org> on 2026/09/10 02:21:11 UTC
[PR] fix: EXTRACT comparisons against an out-of-range year return NULL [datafusion]
1fanwang opened a new pull request, #25140:
URL: https://github.com/apache/datafusion/pull/25140
## Which issue does this PR close?
- Closes #25135.
## Rationale for this change
`EXTRACT(YEAR FROM ts) < 9999` matches no rows on a nanosecond timestamp
column, and the comparison itself evaluates to NULL:
```sql
SELECT EXTRACT(YEAR FROM ts), EXTRACT(YEAR FROM ts) < 9999 FROM t;
-- 2020 NULL
-- 1995 NULL
```
The comparison is rewritten into a range over the timestamp column, which needs
midnight on January 1 of the bound year. January 1 of 9999 is outside the
nanosecond range, and that conversion returns nothing. The result was wrapped as
a NULL scalar and used as the bound anyway, so every row compared against NULL.
A wrong non-zero count is reachable too, since `OR` over two such predicates
returns 1 instead of 2.
## What changes are included in this PR?
Building the bound now reports that no bound exists when midnight has no
representation in the target unit, so the original predicate is kept. The same
path also covers a time zone that skips midnight on that date, such as Lima in
1990 and 1994.
## What is the testing strategy for this PR?
`datafusion/sqllogictest/test_files/datetime/date_part.slt` gains cases for the
out-of-range bound in `WHERE`, in a projection and across `OR`, plus a bound
inside the nanosecond range to confirm that rewriting still happens.
<details><summary>Reproducer, before and after</summary>
```sql
CREATE VIEW t AS SELECT * FROM (VALUES
('2020-06-01T12:00:00'::timestamp),
('1995-06-01T12:00:00'::timestamp)) s(ts);
SELECT count(*) FROM t WHERE EXTRACT(YEAR FROM ts) < 9999;
SELECT count(*) FROM t WHERE EXTRACT(YEAR FROM ts) >= 1000 OR EXTRACT(YEAR FROM ts) = 2020;
SELECT EXTRACT(YEAR FROM ts), EXTRACT(YEAR FROM ts) < 9999 FROM t;
SELECT count(*) FROM t WHERE EXTRACT(YEAR FROM ts) <= 2262;
SELECT count(*) FROM t WHERE EXTRACT(YEAR FROM ts) >= 1678;
```
| query | before | after |
|---|---|---|
| `< 9999` | 0 | 2 |
| `>= 1000 OR = 2020` | 1 | 2 |
| projection comparison | NULL, NULL | true, true |
| `<= 2262` | 0 | 2 |
| `>= 1678` | 2 | 2 |
</details>
## Are there any user-facing changes?
Yes. `EXTRACT` comparisons against a year whose January 1 is unrepresentable now
return the correct rows instead of NULL. No API 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: github-unsubscribe@datafusion.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org
Re: [PR] fix: preserve EXTRACT year comparisons with unrepresentable bounds [datafusion]
Posted by "1fanwang (via GitHub)" <gi...@apache.org>.
1fanwang commented on code in PR #25140:
URL: https://github.com/apache/datafusion/pull/25140#discussion_r3994508258
##########
datafusion/sqllogictest/test_files/datetime/date_part.slt:
##########
@@ -1859,3 +1859,42 @@ logical_plan
physical_plan
01)FilterExec: c@0 >= 2024-01-01 AND c@0 < 2025-01-01
02)--DataSourceExec: partitions=1, partition_sizes=[1]
+
Review Comment:
Done in https://github.com/apache/datafusion/commit/2df765ce19ed6655dc0322f3057d89255f1dbf8f.
--
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: github-unsubscribe@datafusion.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org
Re: [PR] fix: EXTRACT comparisons against an out-of-range year return NULL [datafusion]
Posted by "codecov-commenter (via GitHub)" <gi...@apache.org>.
codecov-commenter commented on PR #25140:
URL: https://github.com/apache/datafusion/pull/25140#issuecomment-5612582477
## [Codecov](https://app.codecov.io/gh/apache/datafusion/pull/25140?dropdown=coverage&src=pr&el=h1&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=apache) Report
:x: Patch coverage is `20.00000%` with `8 lines` in your changes missing coverage. Please review.
:white_check_mark: Project coverage is 81.80%. Comparing base ([`4086969`](https://app.codecov.io/gh/apache/datafusion/commit/4086969669f96c0d5de03f442f2dba1869d2ee80?dropdown=coverage&el=desc&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=apache)) to head ([`68f6e2a`](https://app.codecov.io/gh/apache/datafusion/commit/68f6e2a134a3ba9254d2d3abf4faa80cc5e2b961?dropdown=coverage&el=desc&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=apache)).
:warning: Report is 2 commits behind head on main.
| [Files with missing lines](https://app.codecov.io/gh/apache/datafusion/pull/25140?dropdown=coverage&src=pr&el=tree&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=apache) | Patch % | Lines |
|---|---|---|
| [datafusion/functions/src/datetime/date\_part.rs](https://app.codecov.io/gh/apache/datafusion/pull/25140?src=pr&el=tree&filepath=datafusion%2Ffunctions%2Fsrc%2Fdatetime%2Fdate_part.rs&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=apache#diff-ZGF0YWZ1c2lvbi9mdW5jdGlvbnMvc3JjL2RhdGV0aW1lL2RhdGVfcGFydC5ycw==) | 20.00% | [8 Missing :warning: ](https://app.codecov.io/gh/apache/datafusion/pull/25140?src=pr&el=tree&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=apache) |
<details><summary>Additional details and impacted files</summary>
```diff
@@ Coverage Diff @@
## main #25140 +/- ##
==========================================
- Coverage 81.80% 81.80% -0.01%
==========================================
Files 1130 1130
Lines 417842 417837 -5
Branches 417842 417837 -5
==========================================
- Hits 341833 341822 -11
- Misses 55877 55882 +5
- Partials 20132 20133 +1
```
</details>
[:umbrella: View full report in Codecov by Harness](https://app.codecov.io/gh/apache/datafusion/pull/25140?dropdown=coverage&src=pr&el=continue&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=apache).
:loudspeaker: Have feedback on the report? [Share it here](https://about.codecov.io/codecov-pr-comment-feedback/?utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=apache).
<details><summary> :rocket: New features to boost your workflow: </summary>
- :snowflake: [Test Analytics](https://docs.codecov.com/docs/test-analytics): Detect flaky tests, report on failures, and find test suite problems.
- :package: [JS Bundle Analysis](https://docs.codecov.com/docs/javascript-bundle-analysis): Save yourself from yourself by tracking and limiting bundle sizes in JS merges.
</details>
--
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: github-unsubscribe@datafusion.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org
Re: [PR] fix: preserve EXTRACT year comparisons with unrepresentable bounds [datafusion]
Posted by "neilconway (via GitHub)" <gi...@apache.org>.
neilconway merged PR #25140:
URL: https://github.com/apache/datafusion/pull/25140
--
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: github-unsubscribe@datafusion.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org
Re: [PR] fix: EXTRACT comparisons against an out-of-range year return NULL [datafusion]
Posted by "neilconway (via GitHub)" <gi...@apache.org>.
neilconway commented on code in PR #25140:
URL: https://github.com/apache/datafusion/pull/25140#discussion_r3993813350
##########
datafusion/sqllogictest/test_files/datetime/date_part.slt:
##########
@@ -1859,3 +1859,42 @@ logical_plan
physical_plan
01)FilterExec: c@0 >= 2024-01-01 AND c@0 < 2025-01-01
02)--DataSourceExec: partitions=1, partition_sizes=[1]
+
Review Comment:
Might also be useful to have a test case for the timezone variant (e.g., Lima in 1994).
--
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: github-unsubscribe@datafusion.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org
Re: [PR] fix: preserve EXTRACT year comparisons with unrepresentable bounds [datafusion]
Posted by "neilconway (via GitHub)" <gi...@apache.org>.
neilconway commented on PR #25140:
URL: https://github.com/apache/datafusion/pull/25140#issuecomment-5642769649
Thanks @1fanwang , looks good!
--
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: github-unsubscribe@datafusion.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org