[
https://issues.apache.org/jira/browse/SPARK-61109?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Norio Akagi updated SPARK-61109:
--------------------------------
Description:
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.
was:
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.
> 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: 3.4.0, 3.5.0, 4.1.0, 4.0.0, 4.2.0, 4.3.0
> Reporter: Norio Akagi
> Priority: Major
>
> 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]
