Regression in first_value and last_value coercion #10781
Description
Describe the bug
There is a regression in first_value and last_value coercion after #10648 is merged.
The error message looks like: Error during planning: Coercion from [Timestamp(Nanosecond, Some("+00:00"))] to the signature OneOf([ArraySignature(Array), Uniform(1, [Int8, Int16, Int32, Int64, UInt8, UInt16, UInt32, UInt64, Float32, Float64])]) failed.
To Reproduce
- Pull the latest datafusion and build
datafusion-clilocally - Write data
~/datafusion/datafusion-cli$ ./target/debug/datafusion-cli
DataFusion CLI v38.0.0
> CREATE TABLE table1 (
bar DECIMAL(10,1),
foo VARCHAR(10),
time TIMESTAMP WITH TIME ZONE
);
INSERT INTO table1 (bar, foo, time) VALUES
(200.0, 'me', '1970-01-01T00:00:00.000000010Z'),
(1.0, 'me', '1970-01-01T00:00:00.000000030Z'),
(1.0, 'me', '1970-01-01T00:00:00.000000040Z'),
(2.0, 'you', '1970-01-01T00:00:00.000000020Z');
0 row(s) fetched.
Elapsed 0.053 seconds.- Query using
first_valueandlast_value
> SELECT foo, first_value(time ORDER BY time DESC NULLS LAST) AS time FROM table1 GROUP BY foo ORDER BY foo;
type_coercion
caused by
Error during planning: Coercion from [Timestamp(Nanosecond, Some("+00:00"))] to the signature OneOf([ArraySignature(Array), Uniform(1, [Int8, Int16, Int32, Int64, UInt8, UInt16, UInt32, UInt64, Float32, Float64])]) failed.
> SELECT foo, last_value(time ORDER BY time DESC NULLS LAST) AS time FROM table1 GROUP BY foo ORDER BY foo;
Error during planning: Error during planning: Coercion from [Timestamp(Nanosecond, Some("+00:00"))] to the signature OneOf([ArraySignature(Array), Uniform(1, [Int8, Int16, Int32, Int64, UInt8, UInt16, UInt32, UInt64, Float32, Float64])]) failed. and No function matches the given name and argument types 'last_value(Timestamp(Nanosecond, Some("+00:00")))'. You might need to add explicit type casts.
Candidate functions:
last_value(array)
last_value(Int8/Int16/Int32/Int64/UInt8/UInt16/UInt32/UInt64/Float32/Float64)Expected behavior
> SELECT foo, first_value(time ORDER BY time DESC NULLS LAST) AS time FROM table1 GROUP BY foo ORDER BY foo;
+-----+--------------------------------+
| foo | time |
+-----+--------------------------------+
| me | 1970-01-01T00:00:00.000000040Z |
| you | 1970-01-01T00:00:00.000000020Z |
+-----+--------------------------------+
2 row(s) fetched.
Elapsed 0.031 seconds.
> SELECT foo, last_value(time ORDER BY time DESC NULLS LAST) AS time FROM table1 GROUP BY foo ORDER BY foo;
+-----+--------------------------------+
| foo | time |
+-----+--------------------------------+
| me | 1970-01-01T00:00:00.000000010Z |
| you | 1970-01-01T00:00:00.000000020Z |
+-----+--------------------------------+
2 row(s) fetched.
Elapsed 0.022 seconds.Additional context
No response