neilconway opened a new issue, #25947:
URL: https://github.com/apache/datafusion/issues/25947
### Describe the bug
`nvl` and `ifnull` change the type of their arguments:
- Decimals become `Float64`, which loses precision for large values.
- Timestamps and dates become strings (`Utf8View`), so date/time operations
on the result fail.
`coalesce` with the same arguments keeps the original types.
### To Reproduce
```sql
CREATE TABLE t AS SELECT
CAST('12345678901234567890123456789012345678' AS DECIMAL(38,0)) AS big,
CAST(1.23 AS DECIMAL(5,2)) AS d,
TIMESTAMP '2024-01-01 10:00:00' AS ts,
DATE '2024-01-01' AS dt;
SELECT nvl(big, 0) AS nvl_big, coalesce(big, 0) AS coalesce_big FROM t;
-- 1.2345678901234568e37 | 12345678901234567890123456789012345678
SELECT arrow_typeof(nvl(d, d)) AS nvl_d, arrow_typeof(ifnull(d, d)) AS
ifnull_d, arrow_typeof(coalesce(d, d)) AS coalesce_d FROM t;
-- Float64 | Float64 | Decimal128(5, 2)
SELECT arrow_typeof(nvl(ts, ts)) AS nvl_ts, arrow_typeof(ifnull(ts, ts)) AS
ifnull_ts, arrow_typeof(coalesce(ts, ts)) AS coalesce_ts FROM t;
-- Utf8View | Utf8View | Timestamp(ns)
SELECT arrow_typeof(nvl(dt, dt)) AS nvl_dt, arrow_typeof(coalesce(dt, dt))
AS coalesce_dt FROM t;
-- Utf8View | Date32
SELECT ifnull(ts, ts) + INTERVAL '1 hour' AS ifnull_plus FROM t;
-- Error during planning: Cannot coerce arithmetic expression Utf8View +
Interval(MonthDayNano) to valid types
SELECT coalesce(ts, ts) + INTERVAL '1 hour' AS coalesce_plus FROM t;
-- 2024-01-01T11:00:00
```
### Expected behavior
`nvl` and `ifnull` return the same types and values as `coalesce` for these
arguments:
- `nvl(big, 0)` returns `12345678901234567890123456789012345678`.
- The other results keep the `Decimal128(5, 2)`, `Timestamp(ns)` and
`Date32` types.
- `ifnull(ts, ts) + INTERVAL '1 hour'` returns `2024-01-01T11:00:00`.
### Additional context
Reproduced on `main` at commit 2b1fcae86, in both release and debug builds.
*This bug report was generated by Claude.*
--
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]
