sepuri sai krishna created SPARK-61009:
------------------------------------------
Summary: 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, 4.4.0, 5.0.0
Reporter: sepuri sai krishna
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]
