Order of Interval Addition Should Affect Final Output
- Dominant language
- Rust
- Stars
- 9.3k
- Forks
- 2.4k
- Avg merge
- 3d 7h
- Merged PRs (30d)
- 344
Description
### Describe the bug
In various engines, the order in which intervals are added to dates can affect the final value. This is especially noticeable with leap years.
Datafusion appears to constant fold these intervals, which throws away the operation order.
```sql
> EXPLAIN SELECT
DATE '2019-02-28' + INTERVAL '1 YEAR' + INTERVAL '1 DAY' AS FEB,
DATE '2019-02-28' + INTERVAL '1 DAY' + INTERVAL '1 YEAR' AS MAR;
+---------------+----------------------------------------------------------------------+
| plan_type | plan |
+---------------+----------------------------------------------------------------------+
| logical_plan | Projection: Date32("2020-02-29") AS feb, Date32("2020-02-29") AS mar |
| | EmptyRelation |
| physical_plan | ProjectionExec: expr=[2020-02-29 as feb, 2020-02-29 as mar] |
| | PlaceholderRowExec |
| | |
+---------------+----------------------------------------------------------------------+
```
### To Reproduce
Testing via datafusion-cli
```SQL
> SELECT
DATE '2019-02-28' + INTERVAL '1 YEAR' + INTERVAL '1 DAY' AS FEB,
DATE '2019-02-28' + INTERVAL '1 DAY' + INTERVAL '1 YEAR' AS MAR;
+------------+------------+
| feb | mar |
+------------+------------+
| 2020-02-29 | 2020-02-29 |
+------------+------------+
```
### Expected behavior
Due to leap year shenanigans, adding the year before the day results in a different date than adding the day before the year.
Trino emits
```sql
trino> SELECT
-> DATE '2019-02-28' + INTERVAL '1' YEAR + INTERVAL '1' DAY AS FEB,
-> DATE '2019-02-28' + INTERVAL '1' DAY + INTERVAL '1' YEAR AS MAR;
FEB | MAR
------------+------------
2020-02-29 | 2020-03-01
(1 row)
```
as does [Postgres](https://www.db-fiddle.com/f/tpG3LWnAbzBwkELiU5J2NS/0) and Snowflake (based on their documentation for [interval types](https://docs.snowflake.com/en/sql-reference/data-types-datetime#interval-constants) where this example came from)
### Additional context
_No response_
Contributor guide
Research direction
Start by reproducing the query in datafusion-cli and inspecting its EXPLAIN output, focusing on constant folding of chained date intervals. Done means preserving operation order so the two expressions return 2020-02-29 and 2020-03-01 respectively, with coverage for the leap-year case.
Written by the indexing model from the issue text.
Assessment
- Tech stack
- rust, sql
- Domain
- databases
- Issue type
- Bug
- Difficulty
- 4/5
- Estimated time
- 3-5 days
- Activity status
- Stale
- Clarity
- Mostly clear
- Newbie friendliness
- 45/100