INTERVAL ... SQL syntax produces inconsistent types

[alamb]

The same SQL syntax

interval '5 months'
interval '5 days 3 nanoseconds'

Produces two different data types.

❯ select   arrow_typeof(interval '5 months'),
  arrow_typeof(interval '5 days 3 nanoseconds')
;
+-------------------------------------+-----------------------------------------------------------+
| arrowtypeof(IntervalYearMonth("5")) | arrowtypeof(IntervalMonthDayNano("92233720368547758083")) |
+-------------------------------------+-----------------------------------------------------------+
| Interval(YearMonth)                 | Interval(MonthDayNano)                                    |
+-------------------------------------+-----------------------------------------------------------+

I would like it it to produce the same datatype always (in this case IntervalMonthDayNano) so that it has the same type as the SQL INTERVAL typed, added in #5792

this is inconsistent with '5 days 3 nanoseconds'::interval, an explicit cast to interval.

[alamb]

The reason two different data types are produced I think is hisorical -- DataFusion's support for interval predates broader arrow-rs support for interval (e.g. apache/arrow-rs#3958).

[alamb]

I am actively working on this item

[alamb]

#5806