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]

Reply via email to