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]

Reply via email to