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]

Reply via email to