1fanwang opened a new pull request, #25964: URL: https://github.com/apache/datafusion/pull/25964
## Which issue does this PR close? - Closes https://github.com/apache/datafusion/issues/25947. ## Rationale for this change nvl and ifnull change the type of decimal, date and timestamp arguments, while coalesce with the same arguments keeps them. A DECIMAL(38,0) value comes back from nvl as the Float64 1.2345678901234568e37, losing its digits, and timestamps and dates come back as strings, so ifnull(ts, ts) + INTERVAL '1 hour' fails to plan. The cause is the nvl signature: it lists only booleans, integers, floats and strings, so any other argument type is coerced to the first listed type it can be cast to. ## What changes are included in this PR? nvl now coerces its two arguments with the same type union that coalesce uses. It still requires exactly two arguments, nvl(NULL, NULL) is still Boolean, and evaluation is unchanged, since nvl already runs through coalesce. Arguments of the types the old signature listed resolve to the same types as before. ## What is the testing strategy for this PR? New cases in nvl.slt check the decimal value and the result types for decimals, timestamps and dates through nvl, ifnull and coalesce, plus timestamp arithmetic after ifnull. ### Testing Done The issue's queries in datafusion-cli, built with cargo build --profile ci -p datafusion-cli on main (416002a5b) and on this branch: ```shell target/ci/datafusion-cli -f datafusion-25947.sql ``` On main, nvl returns Float64 for the decimals and Utf8View for the timestamp and date, and the interval addition fails: ```text DataFusion CLI v55.1.0 0 row(s) fetched. Elapsed 0.018 seconds. +-----------------------+----------------------------------------+ | nvl_big | coalesce_big | +-----------------------+----------------------------------------+ | 1.2345678901234568e37 | 12345678901234567890123456789012345678 | +-----------------------+----------------------------------------+ 1 row(s) fetched. Elapsed 0.004 seconds. +---------+----------+------------------+ | nvl_d | ifnull_d | coalesce_d | +---------+----------+------------------+ | Float64 | Float64 | Decimal128(5, 2) | +---------+----------+------------------+ 1 row(s) fetched. Elapsed 0.003 seconds. +----------+-----------+---------------+ | nvl_ts | ifnull_ts | coalesce_ts | +----------+-----------+---------------+ | Utf8View | Utf8View | Timestamp(ns) | +----------+-----------+---------------+ 1 row(s) fetched. Elapsed 0.003 seconds. +----------+-------------+ | nvl_dt | coalesce_dt | +----------+-------------+ | Utf8View | Date32 | +----------+-------------+ 1 row(s) fetched. Elapsed 0.001 seconds. Error during planning: Cannot coerce arithmetic expression Utf8View + Interval(MonthDayNano) to valid types datafusion-cli exit status: 0 ``` On this branch, nvl and ifnull return the same types and values as coalesce, and the interval addition works: ```text DataFusion CLI v55.1.0 0 row(s) fetched. Elapsed 0.018 seconds. +----------------------------------------+----------------------------------------+ | nvl_big | coalesce_big | +----------------------------------------+----------------------------------------+ | 12345678901234567890123456789012345678 | 12345678901234567890123456789012345678 | +----------------------------------------+----------------------------------------+ 1 row(s) fetched. Elapsed 0.003 seconds. +------------------+------------------+------------------+ | nvl_d | ifnull_d | coalesce_d | +------------------+------------------+------------------+ | Decimal128(5, 2) | Decimal128(5, 2) | Decimal128(5, 2) | +------------------+------------------+------------------+ 1 row(s) fetched. Elapsed 0.003 seconds. +---------------+---------------+---------------+ | nvl_ts | ifnull_ts | coalesce_ts | +---------------+---------------+---------------+ | Timestamp(ns) | Timestamp(ns) | Timestamp(ns) | +---------------+---------------+---------------+ 1 row(s) fetched. Elapsed 0.002 seconds. +--------+-------------+ | nvl_dt | coalesce_dt | +--------+-------------+ | Date32 | Date32 | +--------+-------------+ 1 row(s) fetched. Elapsed 0.001 seconds. +---------------------+ | ifnull_plus | +---------------------+ | 2024-01-01T11:00:00 | +---------------------+ 1 row(s) fetched. Elapsed 0.002 seconds. datafusion-cli exit status: 0 ``` For argument types the old signature already listed, the result types are the same on main and on this branch: ```shell target/ci/datafusion-cli -q -f nvl-unchanged-types.sql ``` On main: ```text +----------+---------------+ | nvl_utf8 | coalesce_utf8 | +----------+---------------+ | Utf8 | Utf8 | +----------+---------------+ +---------+--------------+ | nvl_int | coalesce_int | +---------+--------------+ | Int16 | Int16 | +---------+--------------+ +-----------+----------------+ | nvl_float | coalesce_float | +-----------+----------------+ | Float32 | Float32 | +-----------+----------------+ Optimizer rule 'simplify_expressions' failed caused by Failed to cast field 'lit' from Utf8 to Int64 caused by Arrow error: Cast error: Cannot cast string 'x' to value of Int64 type +-----------+----------------+ | nvl_nulls | nvl_nulls_type | +-----------+----------------+ | NULL | Boolean | +-----------+----------------+ ``` On this branch: ```text +----------+---------------+ | nvl_utf8 | coalesce_utf8 | +----------+---------------+ | Utf8 | Utf8 | +----------+---------------+ +---------+--------------+ | nvl_int | coalesce_int | +---------+--------------+ | Int16 | Int16 | +---------+--------------+ +-----------+----------------+ | nvl_float | coalesce_float | +-----------+----------------+ | Float32 | Float32 | +-----------+----------------+ Optimizer rule 'simplify_expressions' failed caused by Failed to cast field 'lit' from Utf8 to Int64 caused by Arrow error: Cast error: Cannot cast string 'x' to value of Int64 type +-----------+----------------+ | nvl_nulls | nvl_nulls_type | +-----------+----------------+ | NULL | Boolean | +-----------+----------------+ ``` <details> <summary>Reproducer source: datafusion-25947.sql</summary> ```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; 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; 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; SELECT arrow_typeof(nvl(dt, dt)) AS nvl_dt, arrow_typeof(coalesce(dt, dt)) AS coalesce_dt FROM t; SELECT ifnull(ts, ts) + INTERVAL '1 hour' AS ifnull_plus FROM t; ``` </details> <details> <summary>Reproducer source: nvl-unchanged-types.sql</summary> ```sql SELECT arrow_typeof(nvl(arrow_cast('a', 'Utf8'), arrow_cast('b', 'Utf8'))) AS nvl_utf8, arrow_typeof(coalesce(arrow_cast('a', 'Utf8'), arrow_cast('b', 'Utf8'))) AS coalesce_utf8; SELECT arrow_typeof(nvl(arrow_cast(1, 'Int8'), arrow_cast(2, 'Int16'))) AS nvl_int, arrow_typeof(coalesce(arrow_cast(1, 'Int8'), arrow_cast(2, 'Int16'))) AS coalesce_int; SELECT arrow_typeof(nvl(arrow_cast(1.5, 'Float32'), 2)) AS nvl_float, arrow_typeof(coalesce(arrow_cast(1.5, 'Float32'), 2)) AS coalesce_float; SELECT arrow_typeof(nvl(1, 'x')) AS nvl_mixed, arrow_typeof(coalesce(1, 'x')) AS coalesce_mixed; SELECT nvl(NULL, NULL) AS nvl_nulls, arrow_typeof(nvl(NULL, NULL)) AS nvl_nulls_type; ``` </details> ## Are there any user-facing changes? Yes. nvl and ifnull now return decimals, dates and timestamps with their own types, as coalesce does, instead of Float64 or strings. No API changes. -- 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]
