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: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
