vbarua opened a new issue, #11055:
URL: https://github.com/apache/datafusion/issues/11055
### 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_
--
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]
