You are viewing a plain text version of this content. The canonical link for it is here.
Posted to github@datafusion.apache.org by "vbarua (via GitHub)" <gi...@apache.org> on 2024/06/23 06:30:17 UTC
[I] Order of Interval Addition Should Affect Final Output [datafusion]
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: github-unsubscribe@datafusion.apache.org.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: [I] Order of Interval Addition Should Affect Final Output [datafusion]
Posted by "alamb (via GitHub)" <gi...@apache.org>.
alamb commented on issue #11055:
URL: https://github.com/apache/datafusion/issues/11055#issuecomment-2185275095
> Thanks so much. Should I fix it in sqlparser?
I think that is probably the best way -- though it is likely pretty tricky (likely related to operator precidence). I think the correct first step would be to do some research and see how such arithmetic is parsed by other databases
--
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: [I] Order of Interval Addition Should Affect Final Output [datafusion]
Posted by "alamb (via GitHub)" <gi...@apache.org>.
alamb commented on issue #11055:
URL: https://github.com/apache/datafusion/issues/11055#issuecomment-2184954245
I agree with @Lordworms the root cause is related to the precidence rules in sqlparser which control if expressions like
```sl
DATE '2019-02-28' + INTERVAL '1 YEAR' + INTERVAL '1 DAY' AS FEB,
```
are parsed like this
```sql
((DATE '2019-02-28' + INTERVAL '1 YEAR') + INTERVAL '1 DAY' AS FEB),
```
Or like
```sql
(DATE '2019-02-28' + (INTERVAL '1 YEAR' + INTERVAL '1 DAY' AS FEB)),
```
Users who want to control the order of expression evaluation can use `(` and `)` to explicitly control the evaluation order.
--
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