timestamp - interval results in The type of Timestamp(Nanosecond, Some("+00:00")) Minus Utf8 of binary physical should be same

[alamb]

Describe the bug
I ran a query and got an internal error

To Reproduce
Using datafusion-cli:

❯ create table foo(x timestamp) as select '2023-03-01'::timestamp;
0 rows in set. Query took 0.002 seconds.
❯ select * from foo;
+---------------------+
| x                   |
+---------------------+
| 2023-03-01T00:00:00 |
+---------------------+
1 row in set. Query took 0.001 seconds.
❯ select * from foo where x < now() - '1 day';
The type of Timestamp(Nanosecond, Some("+00:00")) Minus Utf8 of binary physical should be same. 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

Note it is possible to use the interval syntax things work out correctly

❯ select * from foo where x < now() - interval '1 day';
+---------------------+
| x                   |
+---------------------+
| 2023-03-01T00:00:00 |
+---------------------+

Note if you try to use the same predicate outside of the where clause, we get a planning error (correctly):

❯ select now()  - '1 day';
Error during planning: 'Timestamp(Nanosecond, Some("+00:00")) - Utf8' can't be evaluated because there isn't a common type to coerce the types to

Expected behavior

1. (minimum) select * from foo where x < now() - '1 day'; should produce a planning error (rather than an internal error)
2. (ideal) Make the query work

Additional context

[comphead]

@alamb select * from foo where x < now() - '1 day' such kind of queries doesn't work in PG. I would think it is expected behavior as '1 day' is really string.

[alamb]

@alamb select * from foo where x < now() - '1 day' such kind of queries doesn't work in PG. I would think it is expected behavior as '1 day' is really string.

I was imagining that we could coerce Utf8 --> interval automatically -- see #5652

For this ticket, I think we should generate a Plan error rather than an Internal error

[Weijun-H]

@alamb select * from foo where x < now() - '1 day' such kind of queries doesn't work in PG. I would think it is expected behavior as '1 day' is really string.

I was imagining that we could coerce Utf8 --> interval automatically -- see #5652

For this ticket, I think we should generate a Plan error rather than an Internal error

I agree that this is an error in plan. If no one else is working on it, I can take responsibility for resolving it.

[comphead]

@alamb are you also expect the case like that to be valid?

with t as
(
    select '1 day' as intvl
) select now() - t.invl from t

Or it has to be literal to make such kind of cast?

[alamb]

@alamb are you also expect the case like that to be valid?

Eventually I would like that case to work. It will require more work (both in arrow to get the appropriate evaluation kernels and datafusion to do the casting / coercion).

I have it on my list to write up the steps for such support (it is a priority for us on IOx now as people start using intervals more widely). I expect a writeup in a day or two