Luka Zdravic created SPARK-62092:
------------------------------------
Summary: LimitPushDown drops the top-N guarantee of ORDER BY ...
LIMIT when it pushes a smaller limit into it
Key: SPARK-62092
URL: https://issues.apache.org/jira/browse/SPARK-62092
Project: Spark
Issue Type: Bug
Components: SQL
Affects Versions: 4.4.0
Reporter: Luka Zdravic
`LimitPushDown` pushes a smaller `LocalLimit` into UNION ALL, outer joins and
Python UDF inputs.
If the child is a `GlobalLimit`, the rule removes it and keeps only the
`LocalLimit`. When that
`GlobalLimit` sits over a global `Sort` (`ORDER BY a LIMIT 5`), the query loses
its meaning. A `LocalLimit` keeps the first rows of each partition, not the top
rows. So the outer limit can
return rows that are not in the top 5.
Repro (apache/master, `spark.sql.autoBroadcastJoinThreshold=-1`,
`spark.sql.adaptive.coalescePartitions.enabled=false`,
`spark.sql.shuffle.partitions=4`):
CREATE TABLE sorted_t USING parquet AS SELECT id AS a FROM range(999, -1, -1,
4);
CREATE TABLE join_r USING parquet AS SELECT id AS b FROM range(0, 1000, 1, 4);
SELECT l.a
FROM (SELECT a FROM sorted_t ORDER BY a LIMIT 5) l
LEFT JOIN join_r r ON l.a = r.b
LIMIT 3;
Wrong result: `248, 997, 998`.
Right result: 3 values from `
{0, 1, 2, 3, 4}
The optimized plan has `LocalLimit 3` directly over the `sorted_t` scan. The
`GlobalLimit 5` and the `Sort` are gone. The same happens with a `RIGHT JOIN`
and with `UNION ALL` (plan level).
Fix: keep the `GlobalLimit` when its child is a global `Sort` (seen through
order-keeping nodes such as `Project` and `Filter`), and put the new
`LocalLimit` on top. The right side of a LEFT SEMI/ANTI join without a
condition still drops it, since only whether it is empty matters.
Related, different causes: SPARK-58385 (non-literal limit, PR #58065),
SPARK-61573.
--
This message was sent by Atlassian Jira
(v10.3.26#10030026)
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]