[
https://issues.apache.org/jira/browse/SPARK-61505?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Wenchen Fan resolved SPARK-61505.
---------------------------------
Fix Version/s: 4.4.0
Resolution: Fixed
Issue resolved by pull request 60376
[https://github.com/apache/spark/pull/60376]
> 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
> Assignee: Xi Liang
> Priority: Major
> Labels: pull-request-available
> Fix For: 4.4.0
>
>
> 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]