This is an automated email from the ASF dual-hosted git repository.

LuciferYang pushed a commit to branch branch-4.x
in repository https://gitbox.apache.org/repos/asf/spark.git


The following commit(s) were added to refs/heads/branch-4.x by this push:
     new fb29ec89a6e4 [SPARK-58449][SQL] Only push a Join through a Union when 
the right side is the broadcast side
fb29ec89a6e4 is described below

commit fb29ec89a6e46474faf8a4769fb0700113285f6f
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 8a8611ba9684..403de67e56e7 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
@@ -975,9 +975,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]

Reply via email to