[ 
https://issues.apache.org/jira/browse/SPARK-61505?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
 ]

ASF GitHub Bot updated SPARK-61505:
-----------------------------------
    Labels: pull-request-available  (was: )

> Never build the left side of a non-equi LeftSingle join
> -------------------------------------------------------
>
>                 Key: SPARK-61505
>                 URL: https://issues.apache.org/jira/browse/SPARK-61505
>             Project: Spark
>          Issue Type: Bug
>          Components: SQL
>    Affects Versions: 4.1.0, 4.0.0, 4.2.0, 4.3.0, 5.0.0
>            Reporter: Xi Liang
>            Priority: Major
>              Labels: pull-request-available
>
> With {{spark.sql.optimizer.scalarSubqueryUseSingleJoin}} on (the default 
> since 4.0.0), a correlated scalar subquery that the optimizer can't prove 
> returns at most one row becomes a LeftSingle join. Its left side is the outer 
> query and its right side is the subquery. When that join has no equi-join 
> keys, for example because the correlation is a range predicate such as {{id < 
> l.a}} below, it runs as a {{BroadcastNestedLoopJoinExec}}, the only operator 
> for such a join. {{BroadcastNestedLoopJoinExec}} throws if it is asked to 
> build the left side of a LeftSingle join, yet the planner can choose the left 
> side, and the query then fails at execution. On master, with AQE on and off:
> {code:sql}
> SET spark.sql.autoBroadcastJoinThreshold=1000;
> SET spark.sql.adaptive.autoBroadcastJoinThreshold=1000;
> SELECT a, (SELECT id FROM range(0, 1000) WHERE id < l.a) AS s FROM VALUES 
> (0), (1) AS l(a);
> -- expected: (0, NULL), (1, 0)
> -- actual: java.lang.IllegalArgumentException: BroadcastNestedLoopJoin should 
> not use the left side as build when executing a LeftSingle join
> {code}
> The planner chooses the build side of a join without equi-join keys in three 
> steps: the hint pass, then the size pass, then a last resort when no side 
> qualifies in either pass. (An inner join may get a Cartesian product instead 
> of a build side.) A side qualifies in the hint pass if it has a broadcast 
> hint, and in the size pass if its estimated size is at most the broadcast 
> threshold (for runtime statistics under AQE, the adaptive one) and it is not 
> hinted {{NO_BROADCAST_AND_REPLICATION}}. A pass in which only one side 
> qualifies builds that side. A pass in which both sides qualify builds the 
> smaller side for an inner or full outer join, the left side for a right outer 
> join, and the right side for every other join type, LeftSingle included. The 
> last resort builds a side whatever its size, and for a LeftSingle join it 
> builds the right side.
> So the left side of a LeftSingle join is built exactly when only the left 
> side qualifies in the first pass in which any side qualifies. In the query 
> above, the size pass builds the left side: the outer side is estimated at 24 
> bytes, under the 1000-byte thresholds, and {{range(0, 1000)}} at 8000 bytes, 
> over them. The small thresholds only keep the example's data small. With the 
> default thresholds (10MB) and no hints, the same happens when the outer side 
> is estimated under 10MB and the subquery side over it. The hint pass also 
> builds the left side when only the left side has a broadcast hint. 
> {{BroadcastNestedLoopJoinExec}} runs every other join type with either build 
> side, so only LeftSingle joins fail.
> SPARK-49653 (https://github.com/apache/spark/pull/48145) added the LeftSingle 
> join and the check in {{BroadcastNestedLoopJoinExec}} that throws, and made 
> the last resort build the right side of a LeftSingle join, but did not change 
> the hint and size passes. On master, SPARK-60941 
> (https://github.com/apache/spark/pull/60144) then moved the build-side choice 
> for joins without equi-join keys, unchanged, from {{JoinSelection}} into 
> {{JoinSelectionHelper.getNonEquiJoinBuildSide}}.
> The released 4.0.0 and 4.3.0 fail the same way: the query above, run with 
> pyspark 4.0.0 and 4.3.0 from PyPI with AQE on and off, throws the same 
> {{IllegalArgumentException}}. In those releases the choice is still inline in 
> {{JoinSelection}}. The 4.1.0 and 4.2.0 sources have the same hint and size 
> passes, the same check in {{BroadcastNestedLoopJoinExec}} and the same conf 
> default; the query was not run on them.



--
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