andygrove opened a new issue, #238:
URL: https://github.com/apache/datafusion-spark/issues/238
Part of #175 and apache/datafusion-comet#7300.
### What the function does
`make_ym_interval([years[, months]])` builds a year-month interval
(`INTERVAL YEAR TO MONTH`) from a number
of years and a number of months. Both arguments are optional and default to
`0`, so `make_ym_interval()` and
`make_ym_interval(years)` are valid. The result is `years * 12 + months`
total months, stored as a 32-bit
month count.
`inputTypes` is `(INT, INT)` with `ImplicitCastInputTypes`, so other numeric
and string arguments are cast to
`INT` by the analyzer (with ANSI on, out-of-range or malformed values fail
in the cast with `CAST_OVERFLOW` /
`CAST_INVALID_INPUT`; with ANSI off they wrap or become NULL as legacy casts
do). The expression is
null-intolerant: NULL in either argument gives NULL. Overflow of the total
month count always raises an
error, independent of `spark.sql.ansi.enabled`. Since 3.2.0. No aliases.
### Examples
```sql
SELECT make_ym_interval(1, 2);
-- 1-2
SELECT make_ym_interval(1, 0);
-- 1-0
SELECT make_ym_interval(-1, 1);
-- -0-11
SELECT make_ym_interval(2);
-- 2-0
SELECT make_ym_interval(0, 1);
-- 0-1
-- Boundaries of the 32-bit month count
SELECT make_ym_interval(178956970, 7);
-- 178956970-7 (Int.MaxValue months)
SELECT make_ym_interval(-178956970, -8);
-- -178956970-8 (Int.MinValue months)
SELECT make_ym_interval(178956970, 8);
-- [INTERVAL_ARITHMETIC_OVERFLOW.WITHOUT_SUGGESTION] Integer overflow while
operating with intervals.
-- Try devising appropriate values for the interval parameters. SQLSTATE:
22015
SELECT make_ym_interval(-178956970, -9);
-- [INTERVAL_ARITHMETIC_OVERFLOW.WITHOUT_SUGGESTION] ... SQLSTATE: 22015
```
Month counts from `IntervalExpressionsSuite`: `(0, 10)` -> 10, `(1, 10)` ->
22, `(1, -1)` -> 11,
`(-2, -1)` -> -25.
### Edge cases to test
- Zero, one and two argument forms (`make_ym_interval()` is `0-0` months).
- NULL in either argument returns NULL.
- Mixed signs (`(1, -1)` is 11 months, `(-1, 1)` is -11 months) and `months`
outside `0..11`
(e.g. `(0, 25)` is 25 months; Spark does not normalise or reject it).
- Exact `Int.MaxValue` / `Int.MinValue` boundaries and one past each (see
examples).
- `years * 12` is computed in 32-bit with `Math.multiplyExact` before
`months` is added, so it overflows
even when the final total would fit: `make_ym_interval(178956971, -12)` is
an error (derived from the
code, not from a golden file). Match this rather than computing in 64-bit.
- `BIGINT`, `DECIMAL`, `DOUBLE` and `STRING` arguments go through an
implicit cast to `INT`; with ANSI on,
a `BIGINT` above `Int.MaxValue` fails with `CAST_OVERFLOW` before the
function runs.
- Scalar and array for each argument.
- Result type: Arrow `Interval(YearMonth)` (i32 months) maps exactly onto
Spark's `YearMonthIntervalType`.
DataFusion prints it differently from Spark's `y-m` form, so the `.slt`
should compare values, not Spark's
display string.
### Differences across Spark versions
- 3.4 and 3.5: overflow surfaces as a raw `java.lang.ArithmeticException:
integer overflow` (from
`interval.sql.out` in 3.5) with no error condition. The interpreted path
there uses 64-bit arithmetic while
codegen uses 32-bit, so the intermediate-overflow case above is only
consistent from 4.0.
- 4.0 onwards: `IntervalUtils.makeYearMonthInterval` uses 32-bit
`multiplyExact`/`addExact` and raises
`INTERVAL_ARITHMETIC_OVERFLOW.WITHOUT_SUGGESTION` (SQLSTATE 22015) with a
query context.
- 4.0 makes ANSI the default, which changes how implicit casts of
out-of-range arguments behave.
### Implementation notes
- Spark:
`sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/expressions/intervalExpressions.scala`
(`MakeYMInterval`) and `IntervalUtils.makeYearMonthInterval` in
`sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/util/IntervalUtils.scala`.
- DataFusion core has no `make_ym_interval`.
- datafusion-spark already has `make_dt_interval` and `make_interval` in
`crates/spark/src/function/datetime/`; follow their structure (signature
with optional arguments,
null propagation, `return_field_from_args` for nullability). Return
`DataType::Interval(IntervalUnit::YearMonth)`.
- `crates/sqllogictest/test_files/datetime/make_ym_interval.slt` exists but
contains only commented-out
queries; enable and extend it. Document the function 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]