This is an automated email from the ASF dual-hosted git repository.
LuciferYang pushed a commit to branch branch-4.2
in repository https://gitbox.apache.org/repos/asf/spark.git
The following commit(s) were added to refs/heads/branch-4.2 by this push:
new f61222510a27 [SPARK-58449][SQL] Only push a Join through a Union when
the right side is the broadcast side
f61222510a27 is described below
commit f61222510a27d9abb474c9df915fe860f671f971
Author: YangJie <[email protected]>
AuthorDate: Tue Aug 4 12:05:48 2026 +0800
[SPARK-58449][SQL] Only push a Join through a Union when the right side is
the broadcast side
### What changes were proposed in this pull request?
`PushDownJoinThroughUnion` rewrites `Join(Union(c1, ..., cN), right)` into
`Union(Join(c1, right), ..., Join(cN, right))`, which duplicates the right side
once per Union branch. The rule gated on `canPlanAsBroadcastHashJoin`, which
holds when *either* side of the join is broadcastable. An inner join can
broadcast its left side, so a small `Union` on the left passed the guard even
when the right side was large, and the planner then built from the left in
every branch, leaving each copy [...]
This PR adds `broadcastsRightForEveryBranch`, which requires every join the
rewrite would produce to broadcast its right side. It follows the planner's
precedence: a hinted broadcast first, then sizes, and it skips the size check
when a `SHUFFLE_MERGE` or `SHUFFLE_REPLICATE_NL` hint means the planner would
not pick a broadcast hash join at all. A shuffle hash hint needs no such check
because it already makes `canPlanAsBroadcastHashJoin` reject the join.
The check is per branch rather than against the whole `Union`: a `Union`'s
estimated size is the sum of its children, so the build side after the rewrite
is decided against a smaller left, and `branch < right < union` is exactly the
case where the two disagree.
Five existing catalyst tests moved from an empty `LocalRelation` union to a
new `largeStatsUnion` helper. This is a test-fixture artifact, not a behavior
change for real plans: an empty `LocalRelation` is estimated at 0 bytes while
every non-leaf right side is estimated at 1 byte at least, so `getSmallerSide`
picked the left one and the rule no longer fired.
Two things worth a reviewer's attention. The guard and
`canPlanAsBroadcastHashJoin` are now two partial models of the same planner
decision; converging them behind a shared `getPlannedBroadcastBuildSide` in
`JoinSelectionHelper` would change an API used by `PushDownLeftSemiAntiJoin`
and two suites, so it is left to a follow-up. And the guard reads estimates
from a `Once` batch, so later rules and AQE can still re-decide the build side;
it predicts rather than guarantees.
### Why are the changes needed?
The rewrite is only a win when the right side is broadcast. When it is
probed instead, the rule turns one scan of the right side into N.
### Does this PR introduce _any_ user-facing change?
No. `spark.sql.optimizer.pushDownJoinThroughUnion.enabled` defaults to
false. When it is enabled, queries whose right side would not be broadcast in
every branch are no longer rewritten, which can mean giving up a pushdown that
previously happened. The config documentation is updated accordingly.
### How was this patch tested?
New tests in `PushDownJoinThroughUnionSuite` (catalyst and core) cover the
build-side direction, the per-branch comparison, the left outer case, and each
hint ordering; the core suite counts `FileSourceScanExec` nodes over the
right-hand table end to end. Every new test was checked against the unfixed
rule: the catalyst cases fail on the plan shape and the core cases report two
scans of the right side.
Existing core tests now assert how many `Join` nodes the optimized plan
holds, so a fixture that stops being large enough to keep the right side on the
build side fails instead of silently covering nothing.
The TPC-DS benefit this rule targets has not been re-measured with the
guard in place.
### Was this patch authored or co-authored using generative AI tooling?
Generated-by: Claude Code
Closes #57721 from LuciferYang/worktree-spark-union-join-buildside.
Authored-by: YangJie <[email protected]>
Signed-off-by: yangjie01 <[email protected]>
(cherry picked from commit 16d4c73da4943b57996db2936a716b80a1eb6dfe)
Signed-off-by: yangjie01 <[email protected]>
---
.../optimizer/PushDownJoinThroughUnion.scala | 53 ++++++-
.../org/apache/spark/sql/internal/SQLConf.scala | 6 +-
.../optimizer/PushDownJoinThroughUnionSuite.scala | 171 ++++++++++++++++++++-
.../spark/sql/PushDownJoinThroughUnionSuite.scala | 157 +++++++++++++++++--
4 files changed, 357 insertions(+), 30 deletions(-)
diff --git
a/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/optimizer/PushDownJoinThroughUnion.scala
b/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/optimizer/PushDownJoinThroughUnion.scala
index 7c3331dbadc9..65c2f63c463e 100644
---
a/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/optimizer/PushDownJoinThroughUnion.scala
+++
b/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/optimizer/PushDownJoinThroughUnion.scala
@@ -27,8 +27,7 @@ import org.apache.spark.sql.catalyst.trees.TreePattern.{JOIN,
UNION}
import org.apache.spark.sql.internal.SQLConf
/**
- * Pushes down `Join` through `Union` when the right side of the join is small
enough
- * to broadcast.
+ * Pushes down `Join` through `Union` when every resulting join would
broadcast its right side.
*
* This rule transforms the pattern:
* {{{
@@ -42,9 +41,10 @@ import org.apache.spark.sql.internal.SQLConf
* where each `condK` has the Union output attributes rewritten to the
corresponding child's
* output attributes.
*
- * This is beneficial when the right side is small enough to broadcast,
because each Union
- * branch can directly perform a broadcast join with the right side, avoiding
the need to
- * materialize the entire (potentially very large) Union result before the
Join.
+ * This is beneficial when each branch broadcasts the right side, because the
branch can then join
+ * it directly instead of materializing the whole Union first. The right side
has to be the
+ * broadcast side: the rewrite duplicates it once per branch, and a copy that
is probed rather than
+ * broadcast is scanned on its own.
*
* Applicable join types: Inner, LeftOuter.
*/
@@ -59,7 +59,11 @@ case class PushDownJoinThroughUnion(override val conf:
SQLConf)
case join @ Join(u: Union, right, joinType, joinCond, hint)
if (joinType == Inner || joinType == LeftOuter) &&
+ // Requires equi-join keys, and rejects the join when a shuffle hash
hint already decided
+ // the strategy. The check below adds the build side direction, which
this one does not
+ // constrain.
canPlanAsBroadcastHashJoin(join, conf) &&
+ broadcastsRightForEveryBranch(u, join) &&
// Exclude right subtrees containing subqueries, as
DeduplicateRelations
// may not correctly handle correlated references when cloning.
!right.exists(_.expressions.exists(SubqueryExpression.hasSubquery)) &&
@@ -86,6 +90,45 @@ case class PushDownJoinThroughUnion(override val conf:
SQLConf)
}
}
+ /**
+ * Whether every join produced by the rewrite is expected to broadcast its
right side.
+ *
+ * `canPlanAsBroadcastHashJoin` is not enough. It holds when either side is
broadcastable, and
+ * for an inner join the planner may build from either side, choosing the
smaller one when both
+ * qualify. The rewrite replaces the `Union` on the left with one of its
children, so the build
+ * side is decided per branch against a smaller left. Any branch that ends
up building from the
+ * left leaves its copy of the right side as a plain probe input, which is
not reused, so the
+ * right side is read once per such branch instead of once in total.
+ *
+ * The check follows the planner's broadcast precedence, hints before sizes.
It predicts rather
+ * than guarantees the final build side, because later rules and AQE
re-estimate the sizes it
+ * reads. It is also all or nothing: one branch small enough to be the build
side blocks the
+ * rewrite for the others, trading a missed optimization for never
duplicating a probe side. Only
+ * an inner join can build from the left, so for a left outer join this
reduces to asking whether
+ * the right side is broadcastable at all.
+ */
+ private def broadcastsRightForEveryBranch(u: Union, join: Join): Boolean = {
+ // The planner tries these hints between a hinted broadcast and a
size-based one, so the sizes
+ // below are usually not what decides the strategy. When the hinted
strategy turns out not to
+ // apply the planner does fall back to a size-based broadcast, so this
errs towards not
+ // rewriting. A shuffle hash hint needs no such check: given hash-joinable
keys it makes
+ // `canPlanAsBroadcastHashJoin` reject the join, and a broadcast hint that
outranks it is
+ // already answered by the hint-only query below.
+ val hintPicksOtherStrategy =
+ hintToSortMergeJoin(join.hint) || hintToShuffleReplicateNL(join.hint)
+ u.children.forall { child =>
+ // Only the join type, the hints and each side's stats are read below,
so the condition is
+ // left as is, still referencing the Union output rather than the branch
output.
+ val branchJoin = join.copy(left = child)
+ getBroadcastBuildSide(branchJoin, hintOnly = true, conf)
+ .orElse {
+ if (hintPicksOtherStrategy) None
+ else getBroadcastBuildSide(branchJoin, hintOnly = false, conf)
+ }
+ .contains(BuildRight)
+ }
+ }
+
/**
* Creates a copy of `plan` with fresh ExprIds on all output attributes,
* using the same "fake self-join + DeduplicateRelations" pattern as
InlineCTE.
diff --git
a/sql/catalyst/src/main/scala/org/apache/spark/sql/internal/SQLConf.scala
b/sql/catalyst/src/main/scala/org/apache/spark/sql/internal/SQLConf.scala
index 688ca93320bd..318e16de4339 100644
--- a/sql/catalyst/src/main/scala/org/apache/spark/sql/internal/SQLConf.scala
+++ b/sql/catalyst/src/main/scala/org/apache/spark/sql/internal/SQLConf.scala
@@ -815,9 +815,9 @@ object SQLConf {
val PUSH_DOWN_JOIN_THROUGH_UNION_ENABLED =
buildConf("spark.sql.optimizer.pushDownJoinThroughUnion.enabled")
- .doc("When true, pushes down Join through Union when the right side is
small enough " +
- "to broadcast. This can improve performance by allowing each Union
branch to " +
- "directly perform a broadcast join, avoiding materializing the entire
Union result.")
+ .doc("When true, pushes down Join through Union when every Union branch
would broadcast " +
+ "the right side of the join. This can improve performance by allowing
each Union branch " +
+ "to directly perform a broadcast join, avoiding materializing the
entire Union result.")
.version("4.2.0")
.withBindingPolicy(ConfigBindingPolicy.SESSION)
.booleanConf
diff --git
a/sql/catalyst/src/test/scala/org/apache/spark/sql/catalyst/optimizer/PushDownJoinThroughUnionSuite.scala
b/sql/catalyst/src/test/scala/org/apache/spark/sql/catalyst/optimizer/PushDownJoinThroughUnionSuite.scala
index 65d4621063f8..37ba77312bbe 100644
---
a/sql/catalyst/src/test/scala/org/apache/spark/sql/catalyst/optimizer/PushDownJoinThroughUnionSuite.scala
+++
b/sql/catalyst/src/test/scala/org/apache/spark/sql/catalyst/optimizer/PushDownJoinThroughUnionSuite.scala
@@ -19,10 +19,12 @@ package org.apache.spark.sql.catalyst.optimizer
import org.apache.spark.sql.catalyst.dsl.expressions._
import org.apache.spark.sql.catalyst.dsl.plans._
-import org.apache.spark.sql.catalyst.expressions.{Explode, Rand}
+import org.apache.spark.sql.catalyst.expressions.{AttributeMap, Explode, Rand}
import org.apache.spark.sql.catalyst.plans._
-import org.apache.spark.sql.catalyst.plans.logical.{LocalRelation,
LogicalPlan, Union}
+import org.apache.spark.sql.catalyst.plans.logical.{BROADCAST, HintInfo, Join,
JoinHint, LocalRelation,
+ LogicalPlan, SHUFFLE_HASH, SHUFFLE_MERGE, SHUFFLE_REPLICATE_NL, Union}
import org.apache.spark.sql.catalyst.rules.RuleExecutor
+import org.apache.spark.sql.catalyst.statsEstimation.StatsTestPlan
import org.apache.spark.sql.internal.SQLConf
import org.apache.spark.sql.types.IntegerType
@@ -42,6 +44,16 @@ class PushDownJoinThroughUnionSuite extends PlanTest {
val testRelation3 = LocalRelation($"e".int, $"f".int)
val testRelation4 = LocalRelation($"g".int, $"h".int)
+ // A Union whose branches are far too large to broadcast. The 0-byte empty
LocalRelations above
+ // cannot express that: any non-leaf right side is estimated at 1 byte at
least, so
+ // `getSmallerSide` picks the 0-byte left one and the rule does not fire. A
`def` so that every
+ // test gets fresh ExprIds.
+ private def largeStatsUnion = Union(
+ StatsTestPlan(Seq($"a".int, $"b".int), 1000000, AttributeMap.empty,
+ Some(100 * 1024 * 1024)),
+ StatsTestPlan(Seq($"c".int, $"d".int), 1000000, AttributeMap.empty,
+ Some(100 * 1024 * 1024)))
+
test("Push down Inner Join through Union when right side is small") {
val union = Union(testRelation1, testRelation2)
val query = union.join(testRelation3, Inner, Some($"a" === $"e"))
@@ -139,7 +151,7 @@ class PushDownJoinThroughUnionSuite extends PlanTest {
val complexRight = testRelation3
.where($"f" > 0)
.select($"e", ($"f" + 1).as("f_plus_1"))
- val union = Union(testRelation1, testRelation2)
+ val union = largeStatsUnion
val query = union.join(complexRight, Inner, Some($"a" === $"e"))
val optimized = Optimize.execute(query.analyze)
@@ -161,7 +173,7 @@ class PushDownJoinThroughUnionSuite extends PlanTest {
val rightWithGenerate = arrayRelation
.generate(Explode($"arr"), outputNames = Seq("exploded_val"))
.select($"k", $"exploded_val")
- val union = Union(testRelation1, testRelation2)
+ val union = largeStatsUnion
val query = union.join(rightWithGenerate, Inner, Some($"a" === $"k"))
val optimized = Optimize.execute(query.analyze)
@@ -179,7 +191,7 @@ class PushDownJoinThroughUnionSuite extends PlanTest {
test("Push down when right side contains SubqueryAlias") {
val rightWithAlias = testRelation3.subquery("dim")
- val union = Union(testRelation1, testRelation2)
+ val union = largeStatsUnion
val query = union.join(rightWithAlias, Inner, Some($"a" === $"e"))
val optimized = Optimize.execute(query.analyze)
@@ -198,7 +210,7 @@ class PushDownJoinThroughUnionSuite extends PlanTest {
test("Push down when right side contains Project with Alias") {
val rightWithAlias = testRelation3
.select($"e", ($"f" + 1).as("f_plus_1"))
- val union = Union(testRelation1, testRelation2)
+ val union = largeStatsUnion
val query = union.join(rightWithAlias, Inner, Some($"a" === $"e"))
val optimized = Optimize.execute(query.analyze)
@@ -215,7 +227,7 @@ class PushDownJoinThroughUnionSuite extends PlanTest {
test("Push down when right side contains Aggregate") {
val rightWithAgg = testRelation3
.groupBy($"e")(count($"f").as("cnt"), $"e")
- val union = Union(testRelation1, testRelation2)
+ val union = largeStatsUnion
val query = union.join(rightWithAgg, Inner, Some($"a" === $"e"))
val optimized = Optimize.execute(query.analyze)
@@ -232,10 +244,153 @@ class PushDownJoinThroughUnionSuite extends PlanTest {
test("Do not push down when right side contains non-deterministic
expressions") {
val rightWithRand = testRelation3
.select($"e", Rand(10).as("rand_val"))
- val union = Union(testRelation1, testRelation2)
+ val union = largeStatsUnion
val query = union.join(rightWithRand, Inner, Some($"a" === $"e"))
val optimized = Optimize.execute(query.analyze)
comparePlans(optimized, query.analyze)
}
+
+ test("SPARK-58449: do not push down Inner Join when only the Union side is
broadcastable") {
+ // For an inner join the planner may broadcast either side, so a
broadcastable Union on the
+ // left is enough to make the join a broadcast hash join. Pushing down
then clones the large
+ // right side once per branch, and nothing reuses those scans, so the
right side is read N
+ // times instead of once.
+ val smallUnion = Union(
+ StatsTestPlan(Seq($"a".int, $"b".int), 10, AttributeMap.empty,
Some(100)),
+ StatsTestPlan(Seq($"c".int, $"d".int), 10, AttributeMap.empty,
Some(100)))
+ val largeRight = StatsTestPlan(Seq($"e".int, $"f".int), 1000000,
AttributeMap.empty,
+ Some(100 * 1024 * 1024))
+
+ val query = smallUnion.join(largeRight, Inner, Some($"a" === $"e"))
+ val optimized = Optimize.execute(query.analyze)
+
+ comparePlans(optimized, query.analyze)
+ }
+
+ test("SPARK-58449: push down Inner Join when the right side is
broadcastable") {
+ // Control for the case above: the same shape with a small right side is
still pushed down.
+ val smallRight = StatsTestPlan(Seq($"e".int, $"f".int), 10,
AttributeMap.empty, Some(100))
+
+ val query = largeStatsUnion.join(smallRight, Inner, Some($"a" === $"e"))
+ val optimized = Optimize.execute(query.analyze)
+
+ val union = optimized.asInstanceOf[Union]
+ assert(union.children.size == 2)
+ assert(union.children.forall(_.isInstanceOf[Join]))
+ }
+
+ test("SPARK-58449: do not push down when the right side is the build side
only for the Union") {
+ // The right side is smaller than the whole Union but larger than either
branch, so the join
+ // broadcasts the right side before the rewrite and would build from the
left after it. A guard
+ // that checks the whole join instead of each branch misses this.
+ val union = Union(
+ StatsTestPlan(Seq($"a".int, $"b".int), 10, AttributeMap.empty,
Some(400)),
+ StatsTestPlan(Seq($"c".int, $"d".int), 10, AttributeMap.empty,
Some(400)))
+ val right = StatsTestPlan(Seq($"e".int, $"f".int), 10, AttributeMap.empty,
Some(500))
+
+ val query = union.join(right, Inner, Some($"a" === $"e"))
+ val optimized = Optimize.execute(query.analyze)
+
+ comparePlans(optimized, query.analyze)
+ }
+
+ test("SPARK-58449: push down Left Outer Join when the right side is the
build side only for " +
+ "the Union") {
+ // Only an inner join can build from the left, so a left outer join
broadcasts the right side
+ // whatever the sizes are. The shape that blocks the inner join above is
still pushed down.
+ val union = Union(
+ StatsTestPlan(Seq($"a".int, $"b".int), 10, AttributeMap.empty,
Some(400)),
+ StatsTestPlan(Seq($"c".int, $"d".int), 10, AttributeMap.empty,
Some(400)))
+ val right = StatsTestPlan(Seq($"e".int, $"f".int), 10, AttributeMap.empty,
Some(500))
+
+ val query = union.join(right, LeftOuter, Some($"a" === $"e"))
+ val optimized = Optimize.execute(query.analyze)
+
+ val optimizedUnion = optimized.asInstanceOf[Union]
+ assert(optimizedUnion.children.size == 2)
+ assert(optimizedUnion.children.forall(_.isInstanceOf[Join]))
+ }
+
+ test("SPARK-58449: push down when a broadcast hint names the right side") {
+ // A hint is honored ahead of the sizes, so the right side is the build
side even though it is
+ // the larger one.
+ val largeRight = StatsTestPlan(Seq($"e".int, $"f".int), 1000000,
AttributeMap.empty,
+ Some(100 * 1024 * 1024))
+ val hint = JoinHint(None, Some(HintInfo(Some(BROADCAST))))
+
+ val query = Join(largeStatsUnion, largeRight, Inner, Some($"a" === $"e"),
hint)
+ val optimized = Optimize.execute(query.analyze)
+
+ val union = optimized.asInstanceOf[Union]
+ assert(union.children.size == 2)
+ assert(union.children.forall(_.isInstanceOf[Join]))
+ }
+
+ test("SPARK-58449: do not push down when a hint picks a non-broadcast
strategy") {
+ // The planner honors the merge hint over a size-based broadcast, so no
branch would broadcast
+ // the right side and the rewrite would only multiply the joins.
+ val smallRight = StatsTestPlan(Seq($"e".int, $"f".int), 10,
AttributeMap.empty, Some(100))
+ val hint = JoinHint(None, Some(HintInfo(Some(SHUFFLE_MERGE))))
+
+ val query = Join(largeStatsUnion, smallRight, Inner, Some($"a" === $"e"),
hint)
+ val optimized = Optimize.execute(query.analyze)
+
+ comparePlans(optimized, query.analyze)
+ }
+
+ test("SPARK-58449: do not push down when a hint asks to replicate the right
side") {
+ // A cartesian product requires no distribution on either side, so nothing
would broadcast and
+ // nothing would put the duplicated right side behind a reusable exchange.
+ val smallRight = StatsTestPlan(Seq($"e".int, $"f".int), 10,
AttributeMap.empty, Some(100))
+ val hint = JoinHint(None, Some(HintInfo(Some(SHUFFLE_REPLICATE_NL))))
+
+ val query = Join(largeStatsUnion, smallRight, Inner, Some($"a" === $"e"),
hint)
+ val optimized = Optimize.execute(query.analyze)
+
+ comparePlans(optimized, query.analyze)
+ }
+
+ test("SPARK-58449: do not push down when a shuffle hash hint decided the
strategy") {
+ // The co-guard rejects the join once a shuffle hash hint applies, so the
rewrite never gets to
+ // ask about the build side. Nothing else covers that delegation.
+ val smallRight = StatsTestPlan(Seq($"e".int, $"f".int), 10,
AttributeMap.empty, Some(100))
+ val hint = JoinHint(None, Some(HintInfo(Some(SHUFFLE_HASH))))
+
+ val query = Join(largeStatsUnion, smallRight, Inner, Some($"a" === $"e"),
hint)
+ val optimized = Optimize.execute(query.analyze)
+
+ comparePlans(optimized, query.analyze)
+ }
+
+ test("SPARK-58449: do not push down when only some branches would broadcast
the right side") {
+ // The first branch is far too large to build from, the second is smaller
than the right side.
+ // The rewrite is all or nothing: the second branch would probe its own
copy of the right side,
+ // so neither branch is pushed down.
+ val union = Union(
+ StatsTestPlan(Seq($"a".int, $"b".int), 1000000, AttributeMap.empty,
+ Some(100 * 1024 * 1024)),
+ StatsTestPlan(Seq($"c".int, $"d".int), 10, AttributeMap.empty,
Some(100)))
+ val right = StatsTestPlan(Seq($"e".int, $"f".int), 10, AttributeMap.empty,
Some(500))
+
+ val query = union.join(right, Inner, Some($"a" === $"e"))
+ val optimized = Optimize.execute(query.analyze)
+
+ comparePlans(optimized, query.analyze)
+ }
+
+ test("SPARK-58449: a broadcast hint outranks a non-broadcast hint on the
other side") {
+ // The planner tries a hinted broadcast before the merge hint, so the
right side is still the
+ // build side and the rewrite applies.
+ val largeRight = StatsTestPlan(Seq($"e".int, $"f".int), 1000000,
AttributeMap.empty,
+ Some(100 * 1024 * 1024))
+ val hint = JoinHint(Some(HintInfo(Some(SHUFFLE_MERGE))),
Some(HintInfo(Some(BROADCAST))))
+
+ val query = Join(largeStatsUnion, largeRight, Inner, Some($"a" === $"e"),
hint)
+ val optimized = Optimize.execute(query.analyze)
+
+ val union = optimized.asInstanceOf[Union]
+ assert(union.children.size == 2)
+ assert(union.children.forall(_.isInstanceOf[Join]))
+ }
}
diff --git
a/sql/core/src/test/scala/org/apache/spark/sql/PushDownJoinThroughUnionSuite.scala
b/sql/core/src/test/scala/org/apache/spark/sql/PushDownJoinThroughUnionSuite.scala
index 3b38e7d11fd5..3cd59f8738ab 100644
---
a/sql/core/src/test/scala/org/apache/spark/sql/PushDownJoinThroughUnionSuite.scala
+++
b/sql/core/src/test/scala/org/apache/spark/sql/PushDownJoinThroughUnionSuite.scala
@@ -18,13 +18,18 @@
package org.apache.spark.sql
import org.apache.spark.SparkConf
+import org.apache.spark.sql.catalyst.optimizer.BuildLeft
+import org.apache.spark.sql.catalyst.plans.logical.Join
+import org.apache.spark.sql.execution.FileSourceScanExec
import org.apache.spark.sql.execution.adaptive.{AdaptiveSparkPlanHelper,
BroadcastQueryStageExec}
import org.apache.spark.sql.execution.exchange.ReusedExchangeExec
+import org.apache.spark.sql.execution.joins.BroadcastHashJoinExec
import org.apache.spark.sql.internal.SQLConf
import org.apache.spark.sql.test.SharedSparkSession
class PushDownJoinThroughUnionSuite
- extends SharedSparkSession
+ extends QueryTest
+ with SharedSparkSession
with AdaptiveSparkPlanHelper {
import testImplicits._
@@ -35,8 +40,10 @@ class PushDownJoinThroughUnionSuite
withTempView("fact1", "fact2", "dim") {
withSQLConf(
SQLConf.AUTO_BROADCASTJOIN_THRESHOLD.key -> "10485760") {
- val fact1 = Seq((1, "a"), (2, "b")).toDF("id", "val1")
- val fact2 = Seq((3, "c"), (4, "d")).toDF("id", "val1")
+ // Every branch must stay larger than the dimension table in bytes,
otherwise the planner
+ // builds from the branch and the rule declines to push the join down.
+ val fact1 = Seq((1, "a"), (2, "b"), (5, "e"), (6, "f")).toDF("id",
"val1")
+ val fact2 = Seq((3, "c"), (4, "d"), (7, "g"), (8, "h")).toDF("id",
"val1")
val dim = Seq((1, "x"), (2, "y"), (3, "z")).toDF("id", "label")
fact1.createOrReplaceTempView("fact1")
@@ -54,6 +61,7 @@ class PushDownJoinThroughUnionSuite
Row(2, "b", "y"),
Row(3, "c", "z")
))
+ assertJoinCount(result, 2)
}
}
}
@@ -62,9 +70,11 @@ class PushDownJoinThroughUnionSuite
withTempView("fact1", "fact2", "fact3", "dim") {
withSQLConf(
SQLConf.AUTO_BROADCASTJOIN_THRESHOLD.key -> "10485760") {
- val fact1 = Seq((1, 10), (2, 20)).toDF("id", "amount")
- val fact2 = Seq((3, 30), (4, 40)).toDF("id", "amount")
- val fact3 = Seq((1, 50), (5, 60)).toDF("id", "amount")
+ // Twelve rows per branch keep every branch strictly larger than the
dimension table in
+ // bytes; the extra ids join with nothing and leave the expected rows
unchanged.
+ val fact1 = ((1, 10) +: (2, 20) +: (21 to 30).map(i => (i, i *
10))).toDF("id", "amount")
+ val fact2 = ((3, 30) +: (4, 40) +: (31 to 40).map(i => (i, i *
10))).toDF("id", "amount")
+ val fact3 = ((1, 50) +: (5, 60) +: (41 to 50).map(i => (i, i *
10))).toDF("id", "amount")
val dim = Seq((1, "web"), (2, "store"), (3, "catalog"), (5, "other"))
.toDF("id", "channel")
@@ -90,6 +100,7 @@ class PushDownJoinThroughUnionSuite
Row(1, 50, "web"),
Row(5, 60, "other")
))
+ assertJoinCount(result, 3)
}
}
}
@@ -118,14 +129,17 @@ class PushDownJoinThroughUnionSuite
Row(3, "c", "z"),
Row(99, "d", null)
))
+ assertJoinCount(result, 2)
}
}
}
test("Optimization disabled produces same results") {
withTempView("fact1", "fact2", "dim") {
- val fact1 = Seq((1, "a"), (2, "b")).toDF("id", "val1")
- val fact2 = Seq((3, "c"), (4, "d")).toDF("id", "val1")
+ // Branches larger than the dimension table in bytes, so the rule fires
in the first run and
+ // the comparison against the excluded-rule run is meaningful.
+ val fact1 = Seq((1, "a"), (2, "b"), (5, "e"), (6, "f")).toDF("id",
"val1")
+ val fact2 = Seq((3, "c"), (4, "d"), (7, "g"), (8, "h")).toDF("id",
"val1")
val dim = Seq((1, "x"), (2, "y"), (3, "z")).toDF("id", "label")
fact1.createOrReplaceTempView("fact1")
@@ -146,14 +160,18 @@ class PushDownJoinThroughUnionSuite
withSQLConf(
SQLConf.AUTO_BROADCASTJOIN_THRESHOLD.key -> "10485760") {
- checkAnswer(sql(query), expected)
+ val enabled = sql(query)
+ checkAnswer(enabled, expected)
+ assertJoinCount(enabled, 2)
}
withSQLConf(
SQLConf.AUTO_BROADCASTJOIN_THRESHOLD.key -> "10485760",
SQLConf.OPTIMIZER_EXCLUDED_RULES.key ->
"org.apache.spark.sql.catalyst.optimizer.PushDownJoinThroughUnion") {
- checkAnswer(sql(query), expected)
+ val excluded = sql(query)
+ checkAnswer(excluded, expected)
+ assertJoinCount(excluded, 1)
}
}
}
@@ -162,8 +180,12 @@ class PushDownJoinThroughUnionSuite
withTempView("fact1", "fact2", "dim") {
withSQLConf(
SQLConf.AUTO_BROADCASTJOIN_THRESHOLD.key -> "10485760") {
- val fact1 = Seq((1, "a", 100), (2, "b", 200)).toDF("id", "val1",
"val2")
- val fact2 = Seq((3, "c", 300), (4, "d", 400)).toDF("id", "val1",
"val2")
+ // Column pruning shrinks both sides before the rule reads their
stats, so the branches need
+ // enough rows to stay strictly larger than the dimension table once
pruned.
+ val fact1 = ((1, "a", 100) +: (2, "b", 200) +: (21 to 30).map(i => (i,
"x", i)))
+ .toDF("id", "val1", "val2")
+ val fact2 = ((3, "c", 300) +: (4, "d", 400) +: (31 to 40).map(i => (i,
"y", i)))
+ .toDF("id", "val1", "val2")
val dim = Seq((1, "x", "extra1"), (2, "y", "extra2"), (3, "z",
"extra3"))
.toDF("id", "label", "info")
@@ -182,6 +204,7 @@ class PushDownJoinThroughUnionSuite
Row(2, "y"),
Row(3, "z")
))
+ assertJoinCount(result, 2)
}
}
}
@@ -225,8 +248,12 @@ class PushDownJoinThroughUnionSuite
withTempView("fact1", "fact2", "dim") {
withSQLConf(
SQLConf.AUTO_BROADCASTJOIN_THRESHOLD.key -> "10485760") {
- val fact1 = Seq((1, "a"), (2, "b")).toDF("id", "val1")
- val fact2 = Seq((3, "c"), (4, "d")).toDF("id", "val1")
+ // Each branch must stay strictly larger than the dimension table in
bytes: at equal sizes
+ // the rule fires only through the `right <= left` tie-break in
`getSmallerSide`.
+ val fact1 = Seq((1, "a"), (2, "b"), (5, "e"), (6, "f"), (9, "i"), (10,
"j"))
+ .toDF("id", "val1")
+ val fact2 = Seq((3, "c"), (4, "d"), (7, "g"), (8, "h"), (11, "k"),
(12, "l"))
+ .toDF("id", "val1")
val dim = Seq((1, "x"), (2, "y"), (3, "z"), (4, "w")).toDF("id",
"label")
fact1.createOrReplaceTempView("fact1")
@@ -244,7 +271,109 @@ class PushDownJoinThroughUnionSuite
Row(1, "a", "x"),
Row(3, "c", "z")
))
+ assertJoinCount(result, 2)
+ }
+ }
+ }
+
+ test("SPARK-58449: right side is scanned once when only the Union side is
broadcastable") {
+ withTable("fact1", "fact2", "dim") {
+ // The threshold sits between the two Union branches and the right side,
so the Union is
+ // broadcastable but the right side is not. An inner join can broadcast
either side, so the
+ // join still plans as a broadcast hash join and the rule used to fire,
cloning the right side
+ // once per branch. Nothing reuses a bare probe-side scan, so the right
side would be read
+ // twice.
+ spark.range(0, 4).selectExpr("id", "id AS
v").write.format("parquet").saveAsTable("fact1")
+ spark.range(4, 8).selectExpr("id", "id AS
v").write.format("parquet").saveAsTable("fact2")
+ spark.range(0, 2000).selectExpr("id AS did", "id AS label").write
+ .format("parquet").saveAsTable("dim")
+
+ val unionSize = Seq("fact1", "fact2").map(t =>
+ spark.table(t).queryExecution.optimizedPlan.stats.sizeInBytes).sum
+ val rightSize =
spark.table("dim").queryExecution.optimizedPlan.stats.sizeInBytes
+ assert(unionSize < rightSize, "test setup: the Union must be smaller
than the right side")
+
+ withSQLConf(SQLConf.AUTO_BROADCASTJOIN_THRESHOLD.key ->
unionSize.toString) {
+ val df = sql(
+ """SELECT f.id, d.label
+ |FROM (SELECT * FROM fact1 UNION ALL SELECT * FROM fact2) f
+ |JOIN dim d ON f.id = d.did
+ """.stripMargin)
+ checkAnswer(df, (0 until 8).map(i => Row(i.toLong, i.toLong)))
+
+ assertBuildsFromTheLeft(df)
+ assertJoinCount(df, 1)
+ assert(rightScansOf(df) == 1,
+ s"the right side must be scanned once, found ${rightScansOf(df)}
scans of it")
}
}
}
+
+ test("SPARK-58449: right side is scanned once when it is larger than each
Union branch") {
+ withTable("fact1", "fact2", "dim") {
+ // Both sides are under the threshold, so an inner join can build from
either one and the
+ // planner picks the smaller side, which is the left one. The rule used
to fire anyway,
+ // because a broadcast hash join was plannable, and every branch then
probed its own copy of
+ // the right side.
+ spark.range(0, 4).selectExpr("id", "id AS
v").write.format("parquet").saveAsTable("fact1")
+ spark.range(4, 8).selectExpr("id", "id AS
v").write.format("parquet").saveAsTable("fact2")
+ spark.range(0, 2000).selectExpr("id AS did", "id AS label").write
+ .format("parquet").saveAsTable("dim")
+
+ val branchSizes = Seq("fact1", "fact2").map(t =>
+ spark.table(t).queryExecution.optimizedPlan.stats.sizeInBytes)
+ val rightSize =
spark.table("dim").queryExecution.optimizedPlan.stats.sizeInBytes
+ assert(branchSizes.forall(_ < rightSize) && branchSizes.sum < rightSize,
+ "test setup: the right side must be larger than the whole Union")
+
+ withSQLConf(SQLConf.AUTO_BROADCASTJOIN_THRESHOLD.key -> (rightSize *
2).toString) {
+ val df = sql(
+ """SELECT f.id, d.label
+ |FROM (SELECT * FROM fact1 UNION ALL SELECT * FROM fact2) f
+ |JOIN dim d ON f.id = d.did
+ """.stripMargin)
+ checkAnswer(df, (0 until 8).map(i => Row(i.toLong, i.toLong)))
+
+ assertBuildsFromTheLeft(df)
+ assertJoinCount(df, 1)
+ assert(rightScansOf(df) == 1,
+ s"the right side must be scanned once, found ${rightScansOf(df)}
scans of it")
+ }
+ }
+ }
+
+ /**
+ * Asserts how many `Join` nodes the optimized plan holds. The rule turns
one join into one per
+ * Union branch, so this pins whether it fired. Without it, a fixture whose
branches stopped being
+ * larger than the dimension table would still produce the right rows and
pass silently.
+ */
+ private def assertJoinCount(df: DataFrame, expected: Int): Unit = {
+ val joins = df.queryExecution.optimizedPlan.collect { case j: Join => j }
+ assert(joins.size == expected,
+ s"expected $expected Join nodes in the optimized plan, found
${joins.size}")
+ }
+
+ /**
+ * Asserts that every broadcast hash join in the plan builds from the left,
the condition under
+ * which the rule used to fire and duplicate the probe side. Without it the
scan count would also
+ * be satisfied by a plan that never became a broadcast hash join, which is
a different reason for
+ * the rule not to fire.
+ *
+ * This is a premise of the two tests above rather than a property of the
rule: the number of
+ * joins is left to their own assertion. Should a planner change stop
picking a build-left
+ * broadcast hash join here, retune the table sizes and the threshold.
+ */
+ private def assertBuildsFromTheLeft(df: DataFrame): Unit = {
+ val joins = collectWithSubqueries(df.queryExecution.executedPlan) {
+ case j: BroadcastHashJoinExec => j
+ }
+ assert(joins.nonEmpty && joins.forall(_.buildSide == BuildLeft),
+ s"expected every broadcast hash join to build from the left, found
${joins.map(_.buildSide)}")
+ }
+
+ private def rightScansOf(df: DataFrame): Int = {
+ collectWithSubqueries(df.queryExecution.executedPlan) {
+ case s: FileSourceScanExec if s.tableIdentifier.exists(_.table == "dim")
=> s
+ }.size
+ }
}
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]