apache / apache/datafusion

Order of Interval Addition Should Affect Final Output

Open
#11,055 6 comments 0 reactions 0 assignees View on GitHub
bug
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

Open the contributing 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

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.