Xi Liang created SPARK-61505:
--------------------------------
Summary: 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: 5.0.0
Reporter: Xi Liang
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]