andygrove opened a new issue, #263: URL: https://github.com/apache/datafusion-spark/issues/263
Part of #174 and apache/datafusion-comet#7300. ### What the function does `try_add(expr1, expr2)` returns `expr1 + expr2`, but NULL instead of an error when the addition fails. It accepts the same operand types as `+`: numeric, DATE with an integer day count, DATE / TIMESTAMP / TIMESTAMP_NTZ with an interval, and interval with interval. Available since 3.2.0. It is `RuntimeReplaceable`. For two numeric operands it becomes `Add(l, r, EvalMode.TRY)`: normal type coercion (wider integral type, decimal precision rules, string to numeric), and an integer or decimal overflow returns NULL. For any other operand types it becomes `TryEval(Add(l, r, EvalMode.ANSI))`, which the analyzer resolves to the datetime expression for that combination (`DateAdd`, `DateAddYMInterval`, `TimestampAddYMInterval`, `TimestampAddInterval`, `DateAddInterval`, or interval `Add`), and `TryEval` turns any exception into NULL. The result type is the type of the resolved `+`. ### Examples ```sql SELECT try_add(1, 2); -- 3 SELECT try_add(2147483647, 1); -- NULL SELECT try_add(2147483647, decimal(1)); -- 2147483648 decimal(11,0) SELECT try_add(-9223372036854775808L, -1); -- NULL SELECT try_add(date'2021-01-01', 1); -- 2021-01-02 SELECT try_add(date'2021-01-01', interval 1 year); -- 2022-01-01 SELECT try_add(date'2021-01-01', interval 2 second); -- 2021-01-01 00:00:02 timestamp SELECT try_add(timestamp_ntz'2021-01-01 00:00:00', interval 2 second); -- 2021-01-01 00:00:02 timestamp_ntz SELECT try_add(interval 1 year, interval 2 year); -- 3-0 SELECT try_add(interval 2 second, interval 2 second); -- 0 00:00:04.000000000 SELECT try_add(interval 2147483647 month, interval 2 month); -- NULL SELECT try_add(interval 106751991 day, interval 3 day); -- NULL SELECT try_add(interval 2 year, interval 2 second); -- error DATATYPE_MISMATCH.UNEXPECTED_INPUT_TYPE ``` ### Edge cases to test - NULL in either argument, and an untyped NULL with an interval or datetime operand (the analyzer casts it to the other side's interval type; in 4.1+ a NULL next to a datetime is cast to `INTERVAL DAY TO SECOND`). - Numeric: TINYINT/SMALLINT/INT/BIGINT overflow in both directions returns NULL; float/double overflow gives +/-Infinity (no NULL); NaN propagates; DECIMAL result precision/scale follows Spark's `Add` rules and overflow beyond precision 38 returns NULL; string operands are coerced (`try_add(2147483647, "1")` is BIGINT `2147483648` in 4.x, DOUBLE `2.147483648E9` in 3.5). - Errors in the operands are not suppressed on the numeric path: `try_add(1, (2147483647 + 1))` fails with `ARITHMETIC_OVERFLOW` and `try_add(1, 1.0 / 0.0)` with `DIVIDE_BY_ZERO` under ANSI. On the datetime path `TryEval` wraps the whole `Add`, including its operands, so an error inside either operand becomes NULL (derived from `TryEval.eval`). A UDF receives already-evaluated arguments and cannot reproduce this; document it. - DATE + integer: only INT, SMALLINT, TINYINT day counts are accepted (`DateAdd` uses `ExpectsInputTypes`); the addition is plain `int` addition, so it wraps rather than returning NULL (existing `date_add` in this crate does the same). - DATE + `INTERVAL DAY` returns DATE; DATE + day-time interval with a time part returns TIMESTAMP (the date is cast to TIMESTAMP in the session time zone first). - DATE / TIMESTAMP + year-month interval uses `plusMonths` with end-of-month clamping (`2021-01-31` + 1 month is `2021-02-28`). - TIMESTAMP + day-time interval adds days in `spark.sql.session.timeZone` local time and then microseconds, so `INTERVAL 1 DAY` across a DST transition adds 23 or 25 hours; TIMESTAMP_NTZ uses UTC. Test with `America/Los_Angeles` around 2021-03-14 and 2021-11-07. - Interval + interval: same interval type only; overflow returns NULL (examples above). Year-month and day-time intervals cannot be mixed. - A result outside the TIMESTAMP range overflows in `instantToMicros` (`Math.multiplyExact` / `addExact`) and becomes NULL. - Legacy `CalendarIntervalType` operands (e.g. from `make_interval`): `DateAddInterval` is created with `ansiEnabled = true`, so DATE + an interval with a non-zero microseconds part throws and becomes NULL. - TIME + day-time interval (4.1+, `TimeAddInterval`). - Operand order: interval or integer on the left gives the same result (`try_add(1, date'2021-01-01')` is `2021-01-02`). - `spark.sql.ansi.enabled` does not change `try_add` itself, but changes coercion of strings and errors raised by its operands. ### Differences across Spark versions - 4.0: ANSI on by default, so operand errors such as `2147483647 + 1` inside `try_add` now fail (they returned `-2147483647` in 3.x), and string operands coerce to BIGINT instead of DOUBLE. - 4.1: `TimeAdd` was renamed `TimestampAddInterval` (no semantic change found); TIME type support and NULL-with-datetime casting added in `BinaryArithmeticWithDatetimeResolver`. - `TryAdd` itself is unchanged from 3.4 to 4.2. ### Implementation notes - Spark: `sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/expressions/TryEval.scala` (`TryAdd`, `TryEval`), `arithmetic.scala` (`Add`, `BinaryArithmetic.eval` for `EvalMode.TRY`), `analysis/BinaryArithmeticWithDatetimeResolver.scala` (4.0+; inside `Analyzer.scala` in 3.x), `datetimeExpressions.scala` (`TimestampAddInterval`, `DateAddInterval`, `DateAddYMInterval`, `TimestampAddYMInterval`), `util/DateTimeUtils.scala`. - DataFusion core has no `try_add`. Its `+` operator wraps on integer overflow, or errors when `fail_on_overflow` is set; there is no NULL-on-overflow mode. - `datafusion-spark` has `try_sum` (aggregate), `date_add`, `add_months`, `make_interval` / `make_dt_interval`; reuse their date and interval arithmetic. Spark day-time intervals map to `Duration(Microsecond)` here (see `make_dt_interval.rs`), year-month intervals to `Interval(YearMonth)`. - Belongs in `crates/spark/src/function/math/`. The placeholder `crates/sqllogictest/test_files/math/try_add.slt` already has commented-out queries; enable and extend it. Document with `#[user_doc]`. --- _This issue was generated by an LLM (Claude) from the Apache Spark source code and tests. Examples come from Spark's `ExpressionDescription` examples, test suites and `.sql.out` golden files, but please verify them against Spark before relying on them._ -- 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]
