HyukjinKwon commented on code in PR #59408:
URL: https://github.com/apache/spark/pull/59408#discussion_r4282345827
##########
docs/sql-migration-guide.md:
##########
@@ -38,6 +38,8 @@ license: |
- Since Spark 4.4, the optimizer rule `SimplifyCaseConversionExpressions` no
longer simplifies mixed case conversions `LOWER(UPPER(str))` to `LOWER(str)` or
`UPPER(LOWER(str))` to `UPPER(str)`. This preserves standard Unicode case
mapping semantics for non-ASCII characters such as Latin small letter dotless i
(`ı` U+0131) and micro sign (`µ` U+00B5).
- Since Spark 4.4, the Oracle JDBC datasource maps Oracle DATE and TIMESTAMP
to TimestampNTZType unconditionally, ignoring the JDBC read option
`preferTimestampNTZ` (DATE only when the driver default
`oracle.jdbc.mapDateToTimestamp=true` surfaces it as TIMESTAMP; otherwise DATE
stays DateType). In 4.3 and earlier these were read as TimestampType, or as
TimestampNTZType only when `preferTimestampNTZ=true`. This faithfully
represents these zoneless Oracle types. Unaffected: Oracle DATE read with
`oracle.jdbc.mapDateToTimestamp=false` (mapped to DateType, as before), and
TIMESTAMP WITH TIME ZONE / TIMESTAMP WITH LOCAL TIME ZONE. Symmetrically, a
TimestampNTZType column written to Oracle is now bound zoneless via `setObject`
rather than a JVM-default-zone `java.sql.Timestamp`, which can change the
stored value for a wall-clock that falls in the JVM default zone's DST gap. To
restore the previous read and write behavior, set
`spark.sql.legacy.oracle.timestampNTZMapping.enabled` to `true`
.
- Since Spark 4.4, a nondeterministic common expression that a conditional
branch reads more than once is evaluated once, and every read sees that one
value. `BETWEEN` and `NULLIF` read their input twice, so an expression such as
`CASE WHEN c THEN monotonically_increasing_id() BETWEEN 3 AND 5 END` previously
gave each read its own value and could return a different result. Queries whose
results depended on the earlier behaviour change, and an optimized or physical
plan may now show a `with` node for such a branch. A definition that is cheap
to evaluate and reports itself deterministic is still substituted at every
read, as before.
+- Since Spark 4.4, two-argument `floor` and `ceil` no longer cast Float/Double
inputs to a fixed-scale decimal before directional rounding. For example,
`floor(CAST('0.9999999999999999' AS DOUBLE), 0)` returns `0` instead of `1`. As
with `round` and `bround`, Float inputs are widened to Double before conversion
to its canonical decimal representation: `ceil(CAST('0.1' AS FLOAT), 1)`
returns `0.2` instead of `0.1`, and `floor(CAST('0.9' AS FLOAT), 1)` returns
`0.8` instead of `0.9`. Decimal result types and the maximum output scales of 7
for Float and 15 for Double are unchanged. Applications that require decimal
normalization can cast the input to an explicit `DECIMAL(precision, scale)`
before rounding. Persisted buckets or other derived values affected by these
changes may need to be recomputed.
+- Since Spark 4.4, result overflow in two-argument `floor` and `ceil` raises
an error when `spark.sql.ansi.enabled` is `true` and returns `NULL` when it is
`false`, for Float, Double and Decimal inputs. This also changes existing
Decimal overflow behavior: `ceil(CAST('1e-20' AS DECIMAL(38,20)), -38)`
previously raised an error in both modes. For overflowing Decimal inputs at
negative scales, ANSI-mode errors now use
`NUMERIC_VALUE_OUT_OF_RANGE.WITH_SUGGESTION` instead of
`NUMERIC_VALUE_OUT_OF_RANGE.WITHOUT_SUGGESTION`. Preserving small
floating-point inputs can expose overflow that the earlier fixed-scale cast
hid; for example, `ceil(CAST('1e-20' AS DOUBLE), -38)` previously returned zero
and now follows the ANSI setting because the rounded result cannot fit
`DECIMAL(38,0)`.
Review Comment:
**Nit (P3):** This entry presents the old both-modes error and the
`WITHOUT_SUGGESTION` -> `WITH_SUGGESTION` change as Decimal-specific, but
before this PR every input type took the same path. Integral inputs were
coerced to `DecimalType.forType` (e.g. `DECIMAL(10,0)`), and Float/Double
inputs were cast to `DECIMAL(14,7)`/`DECIMAL(30,15)`. All of them then went
through `Decimal(bd.setScale(_scale, mode), p, s)`, which throws
`NUMERIC_VALUE_OUT_OF_RANGE.WITHOUT_SUGGESTION` whatever the ANSI setting. So
`ceil(1, -38)` and `ceil(CAST(1 AS DOUBLE), -38)` (both `DECIMAL(38,0)`,
rounding to `1E+38`) used to fail in both modes. They now return `NULL` with
ANSI off and raise `WITH_SUGGESTION` with ANSI on.
As written, users with INT/BIGINT or ordinary FLOAT/DOUBLE columns won't
recognize their case. The "overflow that the earlier fixed-scale cast hid"
sentence also suggests Float/Double overflow was previously hidden rather than
raised. A rewording that keeps the `1e-20` Double example as the case where the
old cast returned zero:
```suggestion
- Since Spark 4.4, result overflow in two-argument `floor` and `ceil` raises
an error when `spark.sql.ansi.enabled` is `true` and returns `NULL` when it is
`false`, for integral, Float, Double and Decimal inputs. Previously, such
overflow raised `NUMERIC_VALUE_OUT_OF_RANGE.WITHOUT_SUGGESTION` in both modes,
for example `ceil(1, -38)` or `ceil(CAST('1e-20' AS DECIMAL(38,20)), -38)`;
with ANSI enabled, the error now uses
`NUMERIC_VALUE_OUT_OF_RANGE.WITH_SUGGESTION`. Preserving small floating-point
inputs can also expose overflow that the earlier fixed-scale cast hid; for
example, `ceil(CAST('1e-20' AS DOUBLE), -38)` previously returned zero and now
follows the ANSI setting because the rounded result cannot fit `DECIMAL(38,0)`.
```
**Verification:**
- **Inspection:** The reworded prior and new overflow behavior matches the
merge-target RoundBase negative-scale path and RoundToDecimal.roundDecimal for
an integral, a Float/Double and a Decimal example.
--
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]
