Norio Akagi created SPARK-61109:
-----------------------------------
Summary: Merged LIMIT and OFFSET values can overflow Int in the
optimizer, so valid queries fail or return wrong rows
Key: SPARK-61109
URL: https://issues.apache.org/jira/browse/SPARK-61109
Project: Spark
Issue Type: Bug
Components: SQL
Affects Versions: 4.3.0, 4.2.0, 4.0.0, 4.1.0, 3.5.0, 3.4.0
Reporter: Norio Akagi
The optimizer merges limit and offset values in two rules. \{{LimitPushDown}}
rewrites \{{LocalLimit(le, Offset(oe, child))}} to \{{Offset(oe, LocalLimit(le
+ oe, child))}}, and \{{EliminateOffsets}} rewrites \{{Offset(oe1, Offset(oe2,
child))}} to \{{Offset(oe1 + oe2, child)}}. Neither checks that the sum fits in
an Int.
{\{CheckAnalysis}} rejects an overflowing sum with
\{{SUM_OF_LIMIT_AND_OFFSET_EXCEEDS_MAX_INT}}, but only for a \{{LocalLimit}}
directly over an \{{Offset}} in the analyzed plan, and never for two
\{{Offset}} operators. The optimizer builds other such pairs later, e.g. when
it pushes a limit into a \{{Union}} branch or removes a \{{Project}} between a
limit and an offset. The user writes no addition, but the query fails or
returns wrong rows.
With a table \{{t}} of 3 rows and no known row count (e.g.
\{{spark.range(3).write.saveAsTable("t")}}), on master:
||Query||Expected||ANSI on||ANSI off||
|\{{(SELECT * FROM t OFFSET 1) UNION ALL (SELECT * FROM t) LIMIT 2147483647}}|5
rows|ARITHMETIC_OVERFLOW|3 rows|
|\{{SELECT * FROM (SELECT * FROM t OFFSET 10) LIMIT 2147483647}}|0
rows|ARITHMETIC_OVERFLOW|INTERNAL_ERROR|
|\{{SELECT * FROM (SELECT * FROM t OFFSET 2147483647) OFFSET 10}}|0
rows|ARITHMETIC_OVERFLOW|INTERNAL_ERROR|
With ANSI on, the sum fails when a later rule evaluates it. With ANSI off, it
wraps to a negative value. A \{{LocalLimit}} with a negative limit returns no
rows, so the \{{UNION ALL}} query loses the rows of the first branch. The other
two queries fail during physical planning.
Proposed fix: when both values are integer literals and their sum does not fit
in an Int, \{{LimitPushDown}} keeps the \{{LocalLimit}} above the \{{Offset}},
and \{{EliminateOffsets}} keeps both \{{Offset}} operators. Pushing a limit
that large down would not reduce the rows read in practice.
This was raised in the review of SPARK-57125
(https://github.com/apache/spark/pull/59194), which folds the merged values
when they fit in an Int. The fix builds on that change.
--
This message was sent by Atlassian Jira
(v10.3.26#10030026)
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
