[
https://issues.apache.org/jira/browse/SPARK-61009?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
ASF GitHub Bot updated SPARK-61009:
-----------------------------------
Labels: correctness pull-request-available (was: correctness)
> RANGE window frames over integral columns return wrong results when the
> offset or the frame bound does not fit in the column type
> ---------------------------------------------------------------------------------------------------------------------------------
>
> Key: SPARK-61009
> URL: https://issues.apache.org/jira/browse/SPARK-61009
> Project: Spark
> Issue Type: Bug
> Components: SQL
> Affects Versions: 4.0.3, 5.0.0, 4.4.0
> Reporter: sepuri sai krishna
> Priority: Major
> Labels: correctness, pull-request-available
>
> For a {{RANGE}} frame with an offset ordered by a {{TINYINT}}, {{SMALLINT}},
> {{INT}} or {{BIGINT}} column, Spark casts the offset to the type of the
> column and computes the frame bound ({{value + offset}}) in that type. When
> the offset does not fit in the type, or the addition overflows, the value
> wraps around with ANSI mode off, so the frame gets the wrong rows, and the
> query fails with ANSI mode on, although the frame is well defined.
> {code:sql}
> CREATE TABLE t USING parquet AS
> SELECT CAST(id AS INT) AS id, CAST(id * 10 AS TINYINT) AS b FROM range(0,
> 6);
> SELECT id, b, count(*) OVER (ORDER BY b RANGE BETWEEN 200 PRECEDING AND
> CURRENT ROW) AS cnt
> FROM t ORDER BY id;
> {code}
> With {{spark.sql.ansi.enabled=false}}, {{cnt}} is 0 for every row (expected
> 1, 2, ..., 6), on 4.0.3 and master: the offset 200 is cast to {{TINYINT}} and
> wraps to -56. With ANSI mode on, the query fails with {{[CAST_OVERFLOW] The
> value -200 of the type "INT" cannot be cast to "TINYINT"}}. The same happens
> for an {{INT}} column with {{3000000000 PRECEDING}}.
> The bound addition also overflows near the limits of the type, even when the
> offset fits:
> {code:sql}
> CREATE TABLE scores USING parquet AS
> SELECT CAST(id AS INT) AS id, CAST(id * 5 AS TINYINT) AS score FROM
> range(0, 21);
> -- number of scores in [score, score + 30]
> SELECT id, score,
> count(*) OVER (ORDER BY score DESC RANGE BETWEEN 30 PRECEDING AND CURRENT
> ROW) AS cnt
> FROM scores ORDER BY id;
> {code}
> With {{spark.sql.ansi.enabled=false}} the rows with a score of 70 or more are
> wrong (on 4.0.3 and master):
> {noformat}
> score cnt expected
> 70 6 7
> 75 5 6
> 80 4 5
> 85 3 4
> 90 2 3
> 95 1 2
> 100 0 1
> {noformat}
> The last row counts 0 rows, although the frame always contains the current
> row. With {{spark.sql.ansi.enabled=true}} (the default) the query fails:
> {noformat}
> [BINARY_ARITHMETIC_OVERFLOW] 100S + 30S caused overflow. Use `try_add` to
> ignore overflow problem and return NULL.
> {noformat}
> The same happens for the other integral types near their limits, for example
> {{count(*) OVER (ORDER BY i RANGE BETWEEN CURRENT ROW AND 100 FOLLOWING)}}
> returns 0 (and {{sum}} returns NULL) for {{INT}} values near 2147483647, and
> {{n PRECEDING}} in ascending order near the minimum.
> The offset is cast by {{WindowFrameTypeCoercion}} ({{TypeCoercionHelper}}),
> and the bound is built by {{WindowEvaluatorFactoryBase.createBoundOrdering}}
> as {{Add(expr, boundOffset)}} with the eval mode of the session. SPARK-41793
> fixed the same problem for {{DECIMAL}} order columns
> ({{DecimalAddNoOverflowCheck}}). For integral types, the frame bounds could
> be compared exactly, without computing them in the column type.
--
This message was sent by Atlassian Jira
(v10.3.26#10030026)
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
