sdf-jkl opened a new issue, #27359: URL: https://github.com/apache/datafusion/issues/27359
### Describe the bug DataFusion has no type coercion between Arrow time types with different units (`Time32(Second)`, `Time32(Millisecond)`, `Time64(Microsecond)`, `Time64(Nanosecond)`). Comparisons, `IN` lists, join keys, `UNION`, and `coalesce` that mix two of them fail during planning: ``` Cannot infer common argument type for comparison operation Time64(µs) = Time64(ns) ``` A common way to hit this is comparing a Parquet `TIME(MICROS)` column, which is read as `Time64(µs)`, with a SQL `TIME` value, which is `Time64(ns)`. pyarrow writes Python `datetime.time` values as `TIME(MICROS)` by default. @kumarUjjawal found this while reviewing #27316: https://github.com/apache/datafusion/pull/27316#discussion_r4533709191 ### To Reproduce Write a file with pyarrow defaults: ```python import datetime import pyarrow as pa import pyarrow.parquet as pq pq.write_table(pa.table({"t": [datetime.time(12, 0), datetime.time(13, 30)]}), "times.parquet") ``` Then run: ```sql CREATE EXTERNAL TABLE pq STORED AS PARQUET LOCATION 'times.parquet'; SELECT arrow_typeof(t) FROM pq LIMIT 1; -- Time64(µs) SELECT t FROM pq WHERE t = '12:00:00'; -- works, the string is cast to Time64(µs) SELECT t FROM pq WHERE t = TIME '12:00:00'; SELECT t FROM pq WHERE t < TIME '13:00:00'; SELECT t FROM pq WHERE t IN (TIME '12:00:00', TIME '13:30:00'); CREATE TABLE schedule(s TIME) AS VALUES (TIME '12:00:00'); SELECT * FROM pq JOIN schedule ON t = s; SELECT t FROM pq UNION ALL SELECT s FROM schedule; SELECT coalesce(t, TIME '00:00:00') FROM pq; ``` Every query after the string comparison fails: | Query | Error | | --- | --- | | `t = TIME '12:00:00'` | `Cannot infer common argument type for comparison operation Time64(µs) = Time64(ns)` | | `t < TIME '13:00:00'` | `Cannot infer common argument type for comparison operation Time64(µs) < Time64(ns)` | | `t IN (...)` | `Can not find compatible types to compare Time64(µs) with [Time64(ns), Time64(ns)]` | | `JOIN ... ON t = s` | `Cannot infer common argument type for comparison operation Time64(µs) = Time64(ns)` | | `UNION ALL` | `Incompatible inputs for Union: Previous inputs were of type Time64(µs), but got incompatible type Time64(ns) on column 's'` | | `coalesce` | `No function matches the given name and argument types 'coalesce(Time64(µs), Time64(ns))'` | Any two different units fail the same way, for example `arrow_cast('12:00:00', 'Time32(Millisecond)') = arrow_cast('12:00:00', 'Time32(Second)')`. ### Expected behavior Time values with different units coerce to the finer unit, as timestamps already do (`CAST('2024-01-01 12:00:00' AS TIMESTAMP(0)) = CAST('2024-01-01 12:00:00' AS TIMESTAMP)` returns `true`). All the queries above plan and return results. ### Additional context [`temporal_coercion`](https://github.com/apache/datafusion/blob/f94aaa1876581d7010b0d643aded9b691f0c30e7/datafusion/expr-common/src/type_coercion/binary.rs#L2251) pairs `Time32`/`Time64` with `Date32`/`Date64` but has no rule for two time types. Timestamps avoid the problem because [`temporal_coercion_nonstrict_timezone`](https://github.com/apache/datafusion/blob/f94aaa1876581d7010b0d643aded9b691f0c30e7/datafusion/expr-common/src/type_coercion/binary.rs#L2113-L2135) unifies their units with `timeunit_coercion`. Comparison, `IN`, `UNION`, and `coalesce` coercion all reach `temporal_coercion`, so a matching time rule there would fix every query above. `Time - Time` already works across units through `temporal_math_coercion` and would not change. #27316 (support `TIME(p)`) also makes this reachable from SQL type names alone, since `CAST(x AS TIME(6))` or a column declared `TIME(0)` cannot be compared with plain `TIME`. @alamb suggested tracking it separately: https://github.com/apache/datafusion/pull/27316#discussion_r4538803450 Reproduced on `main` at 64c29642d. `branch-56` and 55.2.0 have no time rule in `temporal_coercion` either. #27316 has been requested for 56, so it would help to backport this fix with it. ### Willingness to contribute I plan to contribute a fix for this bug independently -- 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: [email protected] For queries about this service, please contact Infrastructure at: [email protected] --------------------------------------------------------------------- To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected]
