You are viewing a plain text version of this content. The canonical link for it is here.
Posted to github@arrow.apache.org by "alamb (via GitHub)" <gi...@apache.org> on 2023/12/08 14:12:26 UTC
[I] Discussion: handling comparison of intervals [arrow-datafusion]
alamb opened a new issue, #8468:
URL: https://github.com/apache/arrow-datafusion/issues/8468
### Is your feature request related to a problem or challenge?
Data of type [DateTime::Interval](https://docs.rs/arrow/latest/arrow/datatypes/enum.DataType.html#variant.Interval) (spans of time measured in calendar units like days and months) are tricky business because the absolute size (in number of seconds) is not a fixed quantity. For example the `1 month` is 28 days for February but `1 month` is 31 days in December.
This makes the seemingly simple operation of comparing two intervals quite complicated in practice. For example is `1 month` more or less than `30 days`? The answer depends on what month you are talking about.
Arrow also includes a type [DateTime::Duration ](https://docs.rs/arrow/latest/arrow/datatypes/enum.DataType.html#variant.Duration)that is a fixed width of time and suffers from many fewer challenges, but may not be as intuitive for humans to use;
Currently DataFusion \ errors with "not supported" when trying to compare an interval. This is
* good as it doesn't get inconsistent answers
* bad as it might imply it is a feature gap to be filled rather than an intentional lack (which seems to have happened in https://github.com/apache/arrow-rs/pull/5180)
For example you can't compare an interval of `1 month` and `100 days`:
```
❯ select interval '1 month' = interval '100 days';
This feature is not implemented: Unsupported interval operator: Eq
❯ select interval '1 month' < interval '100 days';;
This feature is not implemented: Unsupported interval operator: Lt
```
Nor can you compute things like min/max on an interval
```
❯ create table foo(i interval) as values (interval '1 month'), (interval '100 days');
0 rows in set. Query took 0.008 seconds.
❯ select max(i) from foo;
Internal error: Min/Max accumulator not implemented for type Interval(MonthDayNano).
This was likely caused by a bug in DataFusion's code and we would welcome that you file an bug report in our issue tracker
❯ select min(i) from foo;
Internal error: Min/Max accumulator not implemented for type Interval(MonthDayNano).
```
(those should probably be unsupported errors rather than internal errors 🤔 )
```sql
select interval '100 days';
+---------------------------------------------------------+
| IntervalMonthDayNano("1844674407370955161600") |
+---------------------------------------------------------+
| 0 years 0 mons 100 days 0 hours 0 mins 0.000000000 secs |
+---------------------------------------------------------+
1 row in set. Query took 0.000 seconds.
❯ select interval '1 month';
+-------------------------------------------------------+
| IntervalMonthDayNano("79228162514264337593543950336") |
+-------------------------------------------------------+
| 0 years 1 mons 0 days 0 hours 0 mins 0.000000000 secs |
+-------------------------------------------------------+
1 row in set. Query took 0.001 seconds.
```
### Describe the solution you'd like
It would be nice to discuss and determine what behavior we would like. Saying "datafusion will not support this kind of comparison" would be fine as an outcome, for the reasons explained above
### Describe alternatives you've considered
We could also adopt what postgres does which is to simply convert months into 30 days and treat them as a fixed quantity
```sql
postgres=# select interval '1 month' < interval '100 days';
?column?
----------
t
(1 row)
postgres=# select interval '1 month' < interval '31 days';
?column?
----------
t
(1 row)
postgres=# select interval '1 month' < interval '30 days';
?column?
----------
f
(1 row)
```
### 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@arrow.apache.org.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [I] Discussion: handling comparison of intervals [arrow-datafusion]
Posted by "tustvold (via GitHub)" <gi...@apache.org>.
tustvold commented on issue #8468:
URL: https://github.com/apache/arrow-datafusion/issues/8468#issuecomment-1847282239
> We could also adopt what postgres does which is to simply convert months into 30 days and treat them as a fixed quantity
Coercing to a duration is an interesting option, although this would inherently be lossy and so could also be confusing...
Perhaps we could have a middle-ground where we add syntactic sugar to make coercing to a duration easy, and then direct users to this? e.g. something like `'1 month'::duration_nanos` or something... :thinking:
--
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@arrow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [I] Discussion: handling comparison of intervals [arrow-datafusion]
Posted by "ozankabak (via GitHub)" <gi...@apache.org>.
ozankabak commented on issue #8468:
URL: https://github.com/apache/arrow-datafusion/issues/8468#issuecomment-1847448868
We fixed this in our fork with the semantics discussed in the linked arrow-rs issue. Happy to contribute it upstream if there is interest in it, just let us know.
--
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@arrow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org