[BUG] Error when adding Date32 and Int64

[comphead]

Describe the bug

DataFusion 41

>  select to_date('1970-01-01', 'yyyy-mm-dd') + 5;
Error during planning: Cannot coerce arithmetic expression Date32 + Int64 to valid types

The same query runs ok in Postgres
Workaround

> select to_date('1970-01-01') + interval '5' day;
+-------------------------------------------------------------------------------------------------------------------+
| to_date(Utf8("1970-01-01")) + IntervalMonthDayNano("IntervalMonthDayNano { months: 0, days: 5, nanoseconds: 0 }") |
+-------------------------------------------------------------------------------------------------------------------+
| 1970-01-06                                                                                                        |
+-------------------------------------------------------------------------------------------------------------------+

To Reproduce

 select to_date('1970-01-01', 'yyyy-mm-dd') + 5;

Expected behavior

Query should work

Additional context

No response

[Omega359]

That is an odd thing for pg to support because the unit for the int is implied as it could just as well be months or years.

[comphead]

That is an odd thing for pg to support because the unit for the int is implied as it could just as well be months or years.

I cannot find the standard right away, but for binary ops with Date and numeric, the numeric is supposed to be number of days

[gnayus]

Using intervals, both of the following work:

select to_date('1970-01-01') + interval '5' day;
select interval '5' day + to_date('1970-01-01');

Do we want to support both cases for Date32 and Int64 as well?

select to_date('1970-01-01', 'yyyy-mm-dd') + 5;
select 5 + to_date('1970-01-01', 'yyyy-mm-dd');

How about minus?

select to_date('1970-01-01', 'yyyy-mm-dd') - 5;

[milevin]

take

[milevin]

I put up a PR, but it's not perfect as I describe in its summary. Would love to get feedback and suggestions from the experts!