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]

Reply via email to