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

peter-toth pushed a commit to branch branch-4.1
in repository https://gitbox.apache.org/repos/asf/spark.git


The following commit(s) were added to refs/heads/branch-4.1 by this push:
     new 336826d0245f [SPARK-60232][SPARK-60552][4.1][SQL] Fix nested SPJ 
overwriting inner join pushdown
336826d0245f is described below

commit 336826d0245f1fb039548b1ae1bcf8f26c6eefa1
Author: Ammar Chalifah <[email protected]>
AuthorDate: Tue Nov 3 14:06:01 2026 +0100

    [SPARK-60232][SPARK-60552][4.1][SQL] Fix nested SPJ overwriting inner join 
pushdown
    
    ### What changes were proposed in this pull request?
    
    When storage-partitioned joins (SPJ) are nested, `EnsureRequirements` 
processes the inner join first and pushes SPJ parameters (join key positions, 
common partition values, reducers) into its `BatchScanExec`s. When it later 
processes the outer join, it pushed new parameters into the same scans and 
_replaced_ the inner ones. The inner join's key positions were lost, so the 
scan reported a different partitioning than the one the inner join was planned 
against.
    
    This PR makes the outer push compose with what an inner join already 
pushed, instead of overwriting it. All changes are in `EnsureRequirements`; 
`KeyGroupedPartitionedScan` is unchanged from `branch-4.1`.
    
    * `populateJoinKeyPositions` and `populateCommonPartitionInfo` are replaced 
by `populateSpjParams`. It is used for the one-side-shuffle path, for pushing 
merged values to both sides of a join, and for `ensureOrdering`.
    * `composeJoinKeyPositions`: outer positions index the layout the outer 
join sees, which is the inner layout, so they are composed 
(`newPositions.map(existing)`) rather than replaced.
    * `finalizeCommonPartitionValues` / 
`projectAndDedupeCommonPartitionValues`: values pushed by the current join win, 
because they are the merge with the other side and already in the target 
layout. Otherwise, values an inner join pushed are projected onto the new 
positions and deduplicated by `KeyGroupedPartitioning.apply` itself, so keys 
keep the order in which they first appear, which is the order a one-side 
shuffle routes the other child into. Values that collapse to the same key be 
[...]
    * `composeReducers`: reducers an inner join pushed are projected onto the 
new positions. If both joins push a reducer for the same position, they are 
chained by a small private `ChainedReducer` (inner first, then outer), because 
the outer reducer applies to values the inner one already produced.
    * The push stops at a `BroadcastExchangeLike` and at a 
`ShuffleExchangeLike`. Neither a broadcast build side nor the input of a 
shuffle feeds the join's partitioning.
    * `ensureOrdering` now pushes the sorted values with `populateSpjParams`, 
so it keeps the key positions and reducers a join below already pushed, instead 
of resetting them. If a one-side shuffle sits below and sorting changes the 
order of the values, it falls back to a range shuffle, because that shuffle 
already routed its child into the unsorted order. The search for that shuffle 
stops where the push stops, at a broadcast and at any shuffle, so a one-side 
shuffle inside a broadcast b [...]
    * Reduced scans. Once an inner join has reduced a scan at some key 
position, the scan reports its own transform there but produces values of the 
reduced transform. Planning a later join on that position would read this stale 
layout. This is the problem 
[SPARK-59121](https://issues.apache.org/jira/browse/SPARK-59121) fixes in 
general on 4.2.1 and later. When a spec could be built on such a position, this 
PR falls back to a shuffle instead:
      * `isBuiltFromReducedScan` collects the positions of the scans that 
decide the layout above a child, with whether an inner join reduced each one. 
The walk stops at exchanges, since a shuffle re-partitions its input and a scan 
below it no longer decides the layout above. It maps the spec's columns back 
through aliases, and through a broadcast hash join's build keys to its streamed 
keys. It then trusts a spec expression only when it maps exactly to a position 
no inner join reduced. An [...]
      * `hasReducedScanOutsideJoin` finds a scan that carries reducers with no 
join above it. Only an earlier run of this rule leaves such a scan, e.g. when 
AQE's forced skew-join optimization re-applies `EnsureRequirements` to an 
already planned nested SPJ.
      * `checkKeyGroupCompatible` first checks for a side with a reduced scan 
and no join above it. The earlier run already pushed this join's pairing into 
both sides, so it keeps that pairing rather than derive a reducer again and 
chain it after the one it pushed the first time. On a first run no such scan 
exists. Otherwise it returns `None` when a side's spec is built from a reduced 
scan. `candidateSpecs` rejects such a spec, including members of a 
`ShuffleSpecCollection` reported by a  [...]
    
    Targets `branch-4.1` only. `master` and `branch-4.2+` use 
`GroupPartitionsExec` (SPARK-55535) and are unaffected. Related: SPARK-60203 / 
#59410.
    
    ### Why are the changes needed?
    
    This PR fixes two tickets on `branch-4.1`.
    
    **[SPARK-60232](https://issues.apache.org/jira/browse/SPARK-60232)** 
reports three ways nested SPJ goes wrong on 4.0 and 4.1:
    
    1. **Join-key positions overwritten.** The outer join replaces the inner 
`joinKeyPositions`, e.g. `[1]` becomes `[0]`, so the scan is grouped on the 
wrong key. Depending on the key types, this returns wrong rows, no rows, or 
fails with `ClassCastException`.
    2. **Reducers dropped.** The outer push clears the inner `reducers`, 
breaking co-partitioning between compatible transforms such as `bucket(4)` and 
`bucket(12)`.
    3. **Common values misaligned.** When the outer join uses a subset of the 
partition keys, the nested sides end up with different partition values, and 
planning fails with `ArrayIndexOutOfBoundsException`.
    
    **[SPARK-60552](https://issues.apache.org/jira/browse/SPARK-60552)**: an 
SPJ with a one-side shuffle pushes common partition values into a partitioned 
scan below the shuffle, which then drops every partition whose key is not among 
them. Under `ORDER BY` with `bucketing.sorting.enabled`, a LEFT JOIN returns 
null for every column of the shuffled side. Stopping the push at the shuffle 
fixes it, and the same stop fixes the nested shape where an outer join's push 
reaches a scan below an in [...]
    
    While fixing these, related problems surfaced that also exist on 
`branch-4.1`:
    
    * With compatible transforms, a later join over an already reduced scan can 
return wrong rows. This includes reaching the scan through an alias or a 
broadcast join's build key (including a build key equated to two streamed keys, 
a cast build key, and one a nested broadcast reuses), and a nested join that 
only looks compatible on the transform the reduced scan still reports.
    * When AQE's forced skew-join optimization re-applies `EnsureRequirements` 
to a planned nested SPJ, the re-run applies a reducer twice. With bucket 
transforms `x % n % n == x % n` hides this, but with transforms whose reducer 
is not idempotent, such as hours to days, the nested SPJ returns no rows.
    * When the outer join pushes only positions, values an inner join already 
narrowed are not narrowed further, and planning fails with 
`PartitioningCollection requires all of its partitionings have the same 
numPartitions`.
    * A LEFT OUTER SPJ on a subset of the partition keys under `ORDER BY`, with 
`bucketing.sorting.enabled`, returns no rows or fails with 
`ArrayIndexOutOfBoundsException`, because `ensureOrdering` reset the join's key 
positions. With a one-side shuffle below, it loses every match from the 
shuffled side.
    * The push reaches the scan of a broadcast build side, which then drops its 
partitions, and a nested SPJ over a broadcast join returns no rows.
    
    Related tickets this PR does not resolve:
    
    * [SPARK-59121](https://issues.apache.org/jira/browse/SPARK-59121): the 
general stale-transform problem, fixed on 4.2.1 and later. This PR only falls 
back to a shuffle for the nested shapes above.
    * [SPARK-60460](https://issues.apache.org/jira/browse/SPARK-60460): a 
nested join that shuffles one side keeps the partition count it was planned 
with. Stopping the push at the shuffle fixes the dropped rows in one of its 
shapes, but not the partition count.
    * [SPARK-60579](https://issues.apache.org/jira/browse/SPARK-60579) and 
[SPARK-58996](https://issues.apache.org/jira/browse/SPARK-58996) are other bugs 
on the ordering and skew-join paths.
    
    ### Does this PR introduce _any_ user-facing change?
    
    Yes, for SPJ on `branch-4.1` (4.1.x):
    
    * The query shapes above, which returned wrong rows or failed during 
planning or at runtime, now return correct results.
    * Some plans now shuffle where `branch-4.1` used SPJ:
      * With compatible transforms, when the outer join's key is on a scan an 
inner join already reduced, e.g. `b12 JOIN b4 JOIN b2` on `b12.id`. Both sides 
of the outer join are shuffled. `branch-4.1` returns the right rows for that 
bucket shape only because bucket reducers compose (`x % 12 % 2 == x % 2`), but 
the layout it plans on is not the one the scan produces.
      * When a later join reaches a reduced scan through a key the check cannot 
trace back to an unreduced position, e.g. a cast broadcast build key. Failing 
closed keeps such plans correct, at the cost of a shuffle where an exact trace 
would have kept SPJ.
      * With `spark.sql.adaptive.forceOptimizeSkewedJoin`, when AQE re-applies 
`EnsureRequirements` to a planned nested SPJ next to a skewed join and the 
outer join's third side carries no reducer, e.g. `b4 JOIN b12 JOIN b6 ON b12.id 
= b6.id`, or `hours`, `days` and `years`. The outer join still refuses the 
reduced spec and shuffles both of its sides, so the query plans 4 shuffles 
where `branch-4.1` plans 3 but returns wrong rows. A single SPJ, and a nested 
SPJ whose outer join pushed red [...]
      * Under `ORDER BY` with `bucketing.sorting.enabled`, when a one-side 
shuffle sits below and sorting reorders the partition values, a range shuffle 
is added. `branch-4.1` returned wrong rows or failed for that shape.
    
    No change on `master` or `branch-4.2+`.
    
    ### How was this patch tested?
    
    Added 30 tests to `KeyGroupedPartitioningSuite`, 29 named after SPARK-60232 
and one after SPARK-60552. Each runs with AQE on and off, except the three 
skew-join ones, which need AQE. None are ignored, and the fallback and skew 
tests also assert the shuffle count. 20 of them fail on `branch-4.1`:
    
    * key positions, mixed key types, reducers, over common partition values, 
multiple names per id
    * compatible transforms across three bucket counts
    * narrowing values already projected by an inner join
    * first-appearance order under a one-side shuffle
    * not shuffling the other side onto a reduced scan's layout, including with 
an outer join below
    * not pushing into a broadcast build side
    * keeping the join's key positions under `ORDER BY`
    * seeing a reduced scan through an alias
    * seeing a reduced scan through a broadcast join's build key
    * falling back when a broadcast build key cannot be traced: one equated to 
two streamed keys (including over a reduced and an unreduced position, in both 
orders of the equalities), a cast one, and one a nested broadcast reuses
    * shuffling a compatible nested join built on a reduced scan, including an 
outer join below
    * not reshuffling a nested join above a shuffle
    * not applying a reducer twice when `EnsureRequirements` re-runs for a 
skewed join, with reducible `hours`, `days` and `years` test functions whose 
reducers are not idempotent
    * falling back to a range shuffle under `ORDER BY` with a one-side shuffle 
below
    * ignoring a one-side shuffle inside a broadcast build side under `ORDER BY 
... DESC`, which needs no range shuffle
    * not pushing below a shuffle (SPARK-60552)
    
    The other ten cover shapes that already work and must keep working. 
Examples are pushing outer merged values into a nested join, a four-way join on 
a key dropped by an earlier join, keeping SPJ on a key the inner join did not 
reduce or on an unreduced column with the same transform, and a nested bucketed 
SPJ that `EnsureRequirements` re-runs over for a skewed join, which asserts the 
3 shuffles `branch-4.1` plans and fails with 5 without keeping the earlier 
pairing first. One of them c [...]
    
    `KeyGroupedPartitioningSuite` and `EnsureRequirementsSuite` pass.
    
    ### Was this patch authored or co-authored using generative AI tooling?
    
    Yes: Grok 4.7
    
    Closes #59599 from ammarchalifah/SPARK-60232-nested-spj.
    
    Authored-by: Ammar Chalifah <[email protected]>
    Signed-off-by: Peter Toth <[email protected]>
---
 .../execution/exchange/EnsureRequirements.scala    |  291 ++++-
 .../connector/KeyGroupedPartitioningSuite.scala    | 1174 ++++++++++++++++++++
 .../catalog/functions/transformFunctions.scala     |   50 +
 3 files changed, 1477 insertions(+), 38 deletions(-)

diff --git 
a/sql/core/src/main/scala/org/apache/spark/sql/execution/exchange/EnsureRequirements.scala
 
b/sql/core/src/main/scala/org/apache/spark/sql/execution/exchange/EnsureRequirements.scala
index 368a94785c0e..b73c8824a9ae 100644
--- 
a/sql/core/src/main/scala/org/apache/spark/sql/execution/exchange/EnsureRequirements.scala
+++ 
b/sql/core/src/main/scala/org/apache/spark/sql/execution/exchange/EnsureRequirements.scala
@@ -23,14 +23,17 @@ import scala.collection.mutable.ArrayBuffer
 import org.apache.spark.internal.{LogKeys}
 import org.apache.spark.sql.catalyst.InternalRow
 import org.apache.spark.sql.catalyst.expressions._
+import org.apache.spark.sql.catalyst.optimizer.{BuildLeft, BuildRight}
 import org.apache.spark.sql.catalyst.plans._
 import org.apache.spark.sql.catalyst.plans.physical._
 import org.apache.spark.sql.catalyst.rules.Rule
 import org.apache.spark.sql.catalyst.util.InternalRowComparableWrapper
 import org.apache.spark.sql.connector.catalog.functions.Reducer
 import org.apache.spark.sql.execution._
+import org.apache.spark.sql.execution.adaptive.ShuffleQueryStageExec
 import org.apache.spark.sql.execution.datasources.v2.BatchScanExec
-import org.apache.spark.sql.execution.joins.{ShuffledHashJoinExec, 
SortMergeJoinExec}
+import org.apache.spark.sql.execution.joins.{
+  BroadcastHashJoinExec, ShuffledHashJoinExec, SortMergeJoinExec, 
StoragePartitionJoinParams}
 import org.apache.spark.sql.internal.SQLConf
 
 /**
@@ -144,7 +147,14 @@ case class EnsureRequirements(
             // To choose a KeyGroupedShuffleSpec, we must be able to push down 
SPJ parameters into
             // the scan (for join key positions). If these parameters can't be 
pushed down, this
             // spec can't be used to shuffle other children.
-            case (idx, _: KeyGroupedShuffleSpec) => 
canPushDownSPJParamsToScan(children(idx))
+            case (idx, s: KeyGroupedShuffleSpec) =>
+              canPushDownSPJParamsToScan(children(idx)) &&
+                !isBuiltFromReducedScan(children(idx), s) &&
+                !hasReducedScanOutsideJoin(children(idx))
+            // A nested join reports a collection, and any of its members can 
end up as the best
+            // member the other side is shuffled onto.
+            case (idx, s: ShuffleSpecCollection) =>
+              !hasMemberBuiltFromReducedScan(children(idx), s)
             case _ => true
           }
           .filter(p => !shouldConsiderMinParallelism ||
@@ -199,8 +209,11 @@ case class EnsureRequirements(
       // from one member, otherwise the sides end up grouped on different 
keys, or on a key set the
       // child does not even have. Pick that member once, preferring the 
finest when several
       // qualify. Only the branch that shuffles a child reads these, hence 
`lazy`.
+      // A child with a member built from a reduced scan only looks compatible 
on the transform
+      // the scan still reports, so it is shuffled onto the best spec instead.
       lazy val matchedIndexes = bestSpecOpt.toSeq.flatMap { best =>
-        childrenIndexes.filter(i => best.isCompatibleWith(specs(i)))
+        childrenIndexes.filter(i => best.isCompatibleWith(specs(i)) &&
+          !hasMemberBuiltFromReducedScan(children(i), specs(i)))
       }
       lazy val bestMemberOpt = bestSpecOpt.flatMap { best =>
         val matchedMembers = matchedIndexes.map(i => flattenSpec(specs(i)))
@@ -229,7 +242,8 @@ case class EnsureRequirements(
               // partitioned side's BatchScanExec is grouped by join keys to 
match,
               // and we do that by pushing down the join keys
               case Some(KeyGroupedShuffleSpec(_, _, Some(joinKeyPositions))) =>
-                populateJoinKeyPositions(child, Some(joinKeyPositions))
+                populateSpjParams(
+                  child, commonPartitionValues = None, joinKeyPositions = 
Some(joinKeyPositions))
               case _ => child
             }
           } else {
@@ -345,10 +359,27 @@ case class EnsureRequirements(
           },
           Nil)
         // Sort 'commonPartitionValues' and use this mechanism to ensure 
BatchScan's
-        // output partitions are ordered
+        // output partitions are ordered. A join below may already have pushed 
key positions and
+        // reducers that these values are expressed in, so keep them rather 
than reset them.
         val sorted = partitionValues.sorted(partitionOrdering)
-        populateCommonPartitionInfo(plan, sorted.map((_, 1)),
-          None, None, applyPartialClustering = false, replicatePartitions = 
false)
+        // A one-side shuffle below routed its other child into the current 
order of the values.
+        // Sorting only the scans would leave that child behind, so fall back 
to a range shuffle.
+        // The walk stops where the push stops: at a broadcast, and at any 
shuffle.
+        def hasKeyGroupedShuffle(p: SparkPlan): Boolean = p match {
+          case s @ (_: ShuffleExchangeLike | _: ShuffleQueryStageExec) =>
+            s.outputPartitioning.isInstanceOf[KeyGroupedPartitioning]
+          case _: BroadcastExchangeLike => false
+          case other => other.children.exists(hasKeyGroupedShuffle)
+        }
+        val reorders = partitionValues.zip(sorted).exists { case (a, b) =>
+          partitionOrdering.compare(a, b) != 0
+        }
+        if (reorders && hasKeyGroupedShuffle(plan)) {
+          ShuffleExchangeExec(d.createPartitioning(conf.numShufflePartitions), 
plan)
+        } else {
+          populateSpjParams(plan, commonPartitionValues = Some(sorted.map((_, 
1))),
+            joinKeyPositions = None)
+        }
       case _ => plan
       }
   }
@@ -502,6 +533,15 @@ case class EnsureRequirements(
       if (specOpt.isEmpty) return None
       specOpt.get
     }
+    // A scan that carries reducers with no join above it in a child was 
planned by an earlier
+    // run of this rule, which already pushed this join's pairing into both 
sides. Keep that
+    // pairing rather than derive the reducers again.
+    if (Seq(left, right).exists(hasReducedScanOutsideJoin)) {
+      return Some(Seq(left, right))
+    }
+    if (Seq(left, right).zip(specs).exists { case (p, s) => 
isBuiltFromReducedScan(p, s) }) {
+      return None
+    }
 
     val leftSpec = specs.head
     val rightSpec = specs(1)
@@ -659,11 +699,15 @@ case class EnsureRequirements(
           }
         }
 
-        // Now we need to push-down the common partition information to the 
scan in each child
-        newLeft = populateCommonPartitionInfo(left, mergedPartValues, 
leftSpec.joinKeyPositions,
-          leftReducers, applyPartialClustering, replicateLeftSide)
-        newRight = populateCommonPartitionInfo(right, mergedPartValues, 
rightSpec.joinKeyPositions,
-          rightReducers, applyPartialClustering, replicateRightSide)
+        // Now we need to push-down the common partition information to the 
scan in each child.
+        newLeft = populateSpjParams(
+          left, commonPartitionValues = Some(mergedPartValues),
+          joinKeyPositions = leftSpec.joinKeyPositions, reducers = 
leftReducers,
+          applyPartialClustering = applyPartialClustering, replicatePartitions 
= replicateLeftSide)
+        newRight = populateSpjParams(
+          right, commonPartitionValues = Some(mergedPartValues),
+          joinKeyPositions = rightSpec.joinKeyPositions, reducers = 
rightReducers,
+          applyPartialClustering = applyPartialClustering, replicatePartitions 
= replicateRightSide)
       }
     }
 
@@ -698,42 +742,206 @@ case class EnsureRequirements(
         joinType == LeftAnti || joinType == LeftOuter
   }
 
-  // Populate the common partition information down to the scan nodes
-  private def populateCommonPartitionInfo(
-      plan: SparkPlan,
+  /**
+   * Compose join-key positions from an outer SPJ with positions already 
pushed by an inner SPJ.
+   * Outer positions index the layout the outer join sees; inner positions 
index the scan's raw
+   * partition expressions, so they are composed rather than replaced.
+   */
+  private def composeJoinKeyPositions(
+      existing: Option[Seq[Int]],
+      newPositions: Option[Seq[Int]]): Option[Seq[Int]] = {
+    (existing, newPositions) match {
+      case (Some(old), Some(newPos)) => Some(newPos.map(old))
+      case (_, None) => existing
+      case (None, Some(newPos)) => Some(newPos)
+    }
+  }
+
+  /**
+   * Project common partition values onto `projectionPositions` of the layout 
`expressions`
+   * describes and deduplicate. Values that collapse to the same key are read 
as one group, so
+   * they become one partition.
+   *
+   * The keys keep the order in which they first appear, as 
`KeyGroupedPartitioning.apply` does.
+   * A one-side shuffle routes the other child into that order, so partition i 
of both sides must
+   * hold the same key.
+   */
+  private def projectAndDedupeCommonPartitionValues(
       values: Seq[(InternalRow, Int)],
-      joinKeyPositions: Option[Seq[Int]],
-      reducers: Option[Seq[Option[Reducer[_, _]]]],
-      applyPartialClustering: Boolean,
-      replicatePartitions: Boolean): SparkPlan = plan match {
-    case scan: BatchScanExec =>
-      scan.copy(
-        spjParams = scan.spjParams.copy(
-          commonPartitionValues = Some(values),
-          joinKeyPositions = joinKeyPositions,
-          reducers = reducers,
-          applyPartialClustering = applyPartialClustering,
-          replicatePartitions = replicatePartitions
-        )
-      )
-    case node =>
-      node.mapChildren(child => populateCommonPartitionInfo(
-        child, values, joinKeyPositions, reducers, applyPartialClustering, 
replicatePartitions))
+      expressions: Seq[Expression],
+      projectionPositions: Seq[Int]): Seq[(InternalRow, Int)] = {
+    KeyGroupedPartitioning(expressions, projectionPositions, values.map(_._1), 
Nil,
+      isPartiallyClustered = false, mayContainUnknownPartitionKeys = false)
+      .partitionValues.map((_, 1))
+  }
+
+  /**
+   * The partition expressions a scan currently reports, i.e. its own 
partition expressions
+   * projected onto the join-key positions pushed so far.
+   */
+  private def reportedLayout(params: StoragePartitionJoinParams): 
Option[Seq[Expression]] =
+    params.keyGroupedPartitioning.map { expressions =>
+      params.joinKeyPositions.map(_.map(expressions)).getOrElse(expressions)
+    }
+
+  /**
+   * Common partition values for a scan after a push. Values pushed by this 
join are the merge with
+   * the other side and already sit in the layout `newPositions` select, so 
they win. Otherwise the
+   * values an inner join pushed sit in the scan's reported layout, and are 
projected onto
+   * `newPositions`, which index that layout.
+   */
+  private def finalizeCommonPartitionValues(
+      existing: StoragePartitionJoinParams,
+      pushedValues: Option[Seq[(InternalRow, Int)]],
+      newPositions: Option[Seq[Int]]): Option[Seq[(InternalRow, Int)]] = {
+    (pushedValues, existing.commonPartitionValues, newPositions, 
reportedLayout(existing)) match {
+      case (Some(values), _, _, _) => Some(values)
+      case (None, Some(values), Some(positions), Some(layout)) if positions != 
layout.indices =>
+        assert(values.forall(_._1.numFields == layout.length),
+          "common partition values must sit in the scan's reported layout")
+        Some(projectAndDedupeCommonPartitionValues(values, layout, positions))
+      case (None, values, _, _) => values
+    }
   }
 
+  /**
+   * Reducers for a scan after a push. The reducers an inner join pushed index 
the scan's reported
+   * layout, so they are first projected onto `newPositions`. A reducer this 
join pushes for a
+   * position applies to values the inner reducer already produced, so the two 
are chained.
+   */
+  private def composeReducers(
+      existing: Option[Seq[Option[Reducer[_, _]]]],
+      newPositions: Option[Seq[Int]],
+      pushed: Option[Seq[Option[Reducer[_, _]]]]): 
Option[Seq[Option[Reducer[_, _]]]] = {
+    val projectedExisting = (existing, newPositions) match {
+      case (Some(rs), Some(positions)) => Some(positions.map(rs))
+      case _ => existing
+    }
+    (projectedExisting, pushed) match {
+      case (Some(inner), Some(outer)) =>
+        assert(inner.length == outer.length,
+          "pushed reducers must index the same layout as the composed join-key 
positions")
+        Some(inner.zip(outer).map {
+          case (Some(i), Some(o)) => Some(ChainedReducer(i, o))
+          case (i, o) => o.orElse(i)
+        })
+      case (inner, outer) => outer.orElse(inner)
+    }
+  }
 
-  private def populateJoinKeyPositions(
+  /**
+   * Whether `spec` may be built on a partition expression of a scan below 
`plan` whose splits an
+   * inner join already reduced at that position. Such a scan reports its own 
partition transform
+   * there but values of the reduced transform, so a spec built on that 
position describes a layout
+   * the scan does not have. Positions without a reducer still hold the scan's 
own values and are
+   * sound. A spec expression that cannot be traced to one of those is treated 
as unsound.
+   */
+  private def isBuiltFromReducedScan(plan: SparkPlan, spec: 
KeyGroupedShuffleSpec): Boolean = {
+    // Each position of the scans that decide the layout above `plan`, with 
whether an inner join
+    // reduced it. An exchange re-partitions its input, so a scan below it no 
longer decides that
+    // layout. Only a nested join can hand a reduced scan's layout to a later 
join, which also keeps
+    // a re-run of this rule from matching the scans directly below an already 
planned join.
+    // `hasReducedScanOutsideJoin` covers those.
+    def positions(p: SparkPlan, belowJoin: Boolean): Seq[(Expression, 
Boolean)] = p match {
+      case _: ShuffleExchangeLike | _: BroadcastExchangeLike => Nil
+      case scan: BatchScanExec =>
+        reportedLayout(scan.spjParams).toSeq.flatMap { layout =>
+          val reducers = 
scan.spjParams.reducers.getOrElse(Seq.fill(layout.length)(None))
+          layout.zip(reducers).map { case (e, r) => (e, belowJoin && 
r.isDefined) }
+        }
+      case join @ (_: SortMergeJoinExec | _: ShuffledHashJoinExec) =>
+        join.children.flatMap(positions(_, belowJoin = true))
+      case other => other.children.flatMap(positions(_, belowJoin))
+    }
+    val scanPositions = positions(plan, belowJoin = false)
+    if (!scanPositions.exists(_._2)) return false
+
+    // A `Project` between the joins can rename the column under the 
transform, so map the spec's
+    // columns back through the aliases below `plan` before comparing them 
with the scan's. A
+    // broadcast hash join also reports the streamed side's partitioning on 
its build keys, so map
+    // those back to the streamed keys too.
+    val broadcastKeys = plan.flatMap {
+      case j: BroadcastHashJoinExec =>
+        val (buildKeys, streamedKeys) = j.buildSide match {
+          case BuildLeft => (j.leftKeys, j.rightKeys)
+          case BuildRight => (j.rightKeys, j.leftKeys)
+        }
+        buildKeys.zip(streamedKeys).collect { case (b: Attribute, s: 
Attribute) => b -> s }
+      case _ => Nil
+    }.groupBy(_._1.exprId).values.collect {
+      // A build key equated to several streamed keys has no single position 
to trace back to.
+      case pairs if pairs.map(_._2.exprId).distinct.size == 1 => pairs.head
+    }.toSeq
+    val aliases = AttributeMap(plan.flatMap(_.expressions).flatMap(_.collect {
+      case a @ Alias(child: Attribute, _) => a.toAttribute -> child
+    }) ++ broadcastKeys)
+    def unalias(e: Expression): Expression = e.transformUp {
+      case a: Attribute if aliases.contains(a) => unalias(aliases(a))
+    }
+    // Trust a spec expression only when it maps to a position no inner join 
reduced. One the
+    // mapping above cannot follow, e.g. a cast build key, falls back to a 
shuffle.
+    spec.partitioning.expressions.map(unalias).exists { e =>
+      !scanPositions.exists { case (p, reduced) => !reduced && 
p.semanticEquals(e) }
+    }
+  }
+
+  // Whether any key-grouped member of `spec`, e.g. of a nested join's 
collection, is built from a
+  // reduced scan below `plan`.
+  private def hasMemberBuiltFromReducedScan(plan: SparkPlan, spec: 
ShuffleSpec): Boolean =
+    flattenSpec(spec).exists {
+      case k: KeyGroupedShuffleSpec => isBuiltFromReducedScan(plan, k)
+      case _ => false
+    }
+
+  // A scan that carries reducers without a join above it in `plan` got them 
from an earlier run
+  // of this rule, so it reports its own transform over reduced values.
+  private def hasReducedScanOutsideJoin(plan: SparkPlan): Boolean = plan match 
{
+    case _: SortMergeJoinExec | _: ShuffledHashJoinExec => false
+    case _: ShuffleExchangeLike | _: BroadcastExchangeLike => false
+    case scan: BatchScanExec => 
scan.spjParams.reducers.exists(_.exists(_.isDefined))
+    case other => other.children.exists(hasReducedScanOutsideJoin)
+  }
+
+  /**
+   * Push SPJ parameters down to every scan below `plan`, composing join-key 
positions with any
+   * already pushed by an inner join instead of replacing them.
+   */
+  private def populateSpjParams(
       plan: SparkPlan,
-      joinKeyPositions: Option[Seq[Int]]): SparkPlan = plan match {
+      commonPartitionValues: Option[Seq[(InternalRow, Int)]],
+      joinKeyPositions: Option[Seq[Int]],
+      reducers: Option[Seq[Option[Reducer[_, _]]]] = None,
+      applyPartialClustering: Boolean = false,
+      replicatePartitions: Boolean = false): SparkPlan = plan match {
     case scan: BatchScanExec =>
+      val existing = scan.spjParams
+      val composedPositions = composeJoinKeyPositions(
+        existing.joinKeyPositions, joinKeyPositions)
       scan.copy(
-        spjParams = scan.spjParams.copy(
-          joinKeyPositions = joinKeyPositions
+        spjParams = existing.copy(
+          commonPartitionValues = finalizeCommonPartitionValues(
+            existing, commonPartitionValues, joinKeyPositions),
+          joinKeyPositions = composedPositions,
+          reducers = composeReducers(existing.reducers, joinKeyPositions, 
reducers),
+          applyPartialClustering = commonPartitionValues match {
+            case Some(_) => applyPartialClustering
+            case _ => existing.applyPartialClustering
+          },
+          replicatePartitions = commonPartitionValues match {
+            case Some(_) => replicatePartitions
+            case _ => existing.replicatePartitions
+          }
         )
       )
+    // A broadcast build side does not feed this join's partitioning.
+    case b: BroadcastExchangeLike => b
+    // Nor does the input of a shuffle.
+    case s: ShuffleExchangeLike => s
     case node =>
-      node.mapChildren(child => populateJoinKeyPositions(
-        child, joinKeyPositions))
+      // Recurse through every child down to the scans.
+      node.mapChildren(child => populateSpjParams(
+        child, commonPartitionValues, joinKeyPositions, reducers,
+        applyPartialClustering, replicatePartitions))
   }
 
   private def reducePartValues(
@@ -879,3 +1087,10 @@ case class EnsureRequirements(
     }
   }
 }
+
+/** Applies `first`, then `second`, to reduce a partition value across two 
nested joins. */
+private case class ChainedReducer(first: Reducer[_, _], second: Reducer[_, _])
+  extends Reducer[Any, Any] {
+  override def reduce(arg: Any): Any = second.asInstanceOf[Reducer[Any, 
Any]].reduce(
+    first.asInstanceOf[Reducer[Any, Any]].reduce(arg))
+}
diff --git 
a/sql/core/src/test/scala/org/apache/spark/sql/connector/KeyGroupedPartitioningSuite.scala
 
b/sql/core/src/test/scala/org/apache/spark/sql/connector/KeyGroupedPartitioningSuite.scala
index 8a9cc671e149..4e112e84215b 100644
--- 
a/sql/core/src/test/scala/org/apache/spark/sql/connector/KeyGroupedPartitioningSuite.scala
+++ 
b/sql/core/src/test/scala/org/apache/spark/sql/connector/KeyGroupedPartitioningSuite.scala
@@ -2980,6 +2980,1180 @@ class KeyGroupedPartitioningSuite extends 
DistributionAndOrderingSuiteBase {
     }
   }
 
+  test("SPARK-60232: nested SPJ preserves inner join key positions") {
+    val cols = Array(
+      Column.create("store_id", IntegerType),
+      Column.create("dept_id", IntegerType),
+      Column.create("data", StringType))
+    // t4 is partitioned by (store_id, dept_id) and joins on dept_id only. The 
inner join
+    // pushes joinKeyPositions [1] into the t4 scan. The outer join must not 
overwrite that
+    // with [0] against the already projected dept_id, or t4 is grouped by 
store_id and
+    // the three-way join returns no rows.
+    createTable("t4", cols, Array(identity("store_id"), identity("dept_id")))
+    sql("INSERT INTO testcat.ns.t4 VALUES (1, 2, 'a'), (2, 3, 'b'), (3, 1, 
'c')")
+
+    createTable("t5", cols, Array(identity("dept_id")))
+    sql("INSERT INTO testcat.ns.t5 VALUES (0, 1, 'x1'), (0, 2, 'x2'), (0, 3, 
'x3')")
+
+    createTable("t6", cols, Array(identity("dept_id")))
+    sql("INSERT INTO testcat.ns.t6 VALUES (0, 1, 'x1'), (0, 2, 'x2'), (0, 3, 
'x3')")
+
+    Seq(true, false).foreach { aqe =>
+      withSQLConf(
+        SQLConf.AUTO_BROADCASTJOIN_THRESHOLD.key -> "-1",
+        SQLConf.ADAPTIVE_EXECUTION_ENABLED.key -> aqe.toString,
+        SQLConf.V2_BUCKETING_ALLOW_JOIN_KEYS_SUBSET_OF_PARTITION_KEYS.key -> 
"true",
+        SQLConf.REQUIRE_ALL_CLUSTER_KEYS_FOR_CO_PARTITION.key -> "false") {
+        val df = sql(
+          """
+            |SELECT /*+ MERGE(t4, t5, t6) */
+            |  t4.store_id, t4.dept_id, t4.data, t5.data, t6.data
+            |FROM testcat.ns.t4
+            |JOIN testcat.ns.t5 ON t4.dept_id = t5.dept_id
+            |JOIN testcat.ns.t6 ON t4.dept_id = t6.dept_id
+            |ORDER BY t4.store_id, t4.dept_id
+            |""".stripMargin)
+
+        val plan = df.queryExecution.executedPlan
+        assert(collectShuffles(plan).isEmpty, "SPJ should be triggered for 
both joins")
+        val t4Scan = collectScans(plan).find(_.table.name().endsWith("t4")).get
+        assert(t4Scan.spjParams.joinKeyPositions.contains(Seq(1)),
+          "t4 scan should keep the inner join's key position [1], not [0]")
+        checkAnswer(df, Seq(
+          Row(1, 2, "a", "x2", "x2"),
+          Row(2, 3, "b", "x3", "x3"),
+          Row(3, 1, "c", "x1", "x1")))
+      }
+    }
+  }
+
+  test("SPARK-60232: nested SPJ with mixed partition key types preserves key 
positions") {
+    val cols = Array(
+      Column.create("id", StringType),
+      Column.create("date", StringType),
+      Column.create("data", StringType))
+    // Same nested join-key overwrite as above, but the leading partition key 
has a different
+    // type than the bucket join key. Without the fix this shape throws 
ClassCastException in
+    // connectors such as Iceberg; here the in-memory source only misaligns 
values.
+    createTable("t1", cols,
+      Array(identity("date"), bucket(32, "id"), bucket(32, "data")))
+    sql("INSERT INTO testcat.ns.t1 VALUES " +
+        "('a', '2026-05-01', 'aa'), " +
+        "('a', '2026-05-02', 'cc'), " +
+        "('b', '2026-05-01', 'bb')")
+
+    createTable("t2", cols, Array(bucket(32, "id")))
+    sql("INSERT INTO testcat.ns.t2 VALUES " +
+        "('a', '2026-05-01', 'xx'), " +
+        "('b', '2026-05-01', 'yy')")
+
+    createTable("t3", cols, Array(bucket(32, "id")))
+    sql("INSERT INTO testcat.ns.t3 VALUES " +
+        "('a', '2026-05-01', 'xx'), " +
+        "('b', '2026-05-01', 'yy')")
+
+    Seq(true, false).foreach { aqe =>
+      withSQLConf(
+        SQLConf.AUTO_BROADCASTJOIN_THRESHOLD.key -> "-1",
+        SQLConf.ADAPTIVE_EXECUTION_ENABLED.key -> aqe.toString,
+        SQLConf.V2_BUCKETING_ALLOW_JOIN_KEYS_SUBSET_OF_PARTITION_KEYS.key -> 
"true",
+        SQLConf.REQUIRE_ALL_CLUSTER_KEYS_FOR_CO_PARTITION.key -> "false") {
+        val df = sql(
+          """
+            |SELECT /*+ MERGE(t1, t2, t3) */
+            |  t1.id, t1.date, t1.data, t2.data, t3.data
+            |FROM testcat.ns.t1
+            |JOIN testcat.ns.t2 ON t1.id = t2.id
+            |JOIN testcat.ns.t3 ON t1.id = t3.id
+            |ORDER BY t1.id, t1.date, t1.data
+            |""".stripMargin)
+
+        val plan = df.queryExecution.executedPlan
+        assert(collectShuffles(plan).isEmpty, "SPJ should be triggered for 
both joins")
+        val t1Scan = collectScans(plan).find(_.table.name().endsWith("t1")).get
+        assert(t1Scan.spjParams.joinKeyPositions.contains(Seq(1)),
+          "t1 scan should keep the inner join's key position [1], not [0]")
+        checkAnswer(df, Seq(
+          Row("a", "2026-05-01", "aa", "xx", "xx"),
+          Row("a", "2026-05-02", "cc", "xx", "xx"),
+          Row("b", "2026-05-01", "bb", "yy", "yy")))
+      }
+    }
+  }
+
+  test("SPARK-60232: nested SPJ preserves inner join reducers") {
+    val idCols = Array(Column.create("id", IntegerType))
+    createTable("b4", idCols, Array(bucket(4, "id")))
+    sql("INSERT INTO testcat.ns.b4 VALUES (4)")
+
+    createTable("b12", idCols, Array(bucket(12, "id")))
+    sql("INSERT INTO testcat.ns.b12 VALUES (4)")
+
+    createTable("b8", idCols, Array(bucket(8, "id")))
+    sql("INSERT INTO testcat.ns.b8 VALUES (1), (4)")
+
+    Seq(true, false).foreach { aqe =>
+      withSQLConf(
+        SQLConf.AUTO_BROADCASTJOIN_THRESHOLD.key -> "-1",
+        SQLConf.ADAPTIVE_EXECUTION_ENABLED.key -> aqe.toString,
+        SQLConf.V2_BUCKETING_ALLOW_COMPATIBLE_TRANSFORMS.key -> "true") {
+        val df = sql(
+          """
+            |SELECT /*+ MERGE(b4, b12, b8) */ b4.id, b12.id, b8.id
+            |FROM testcat.ns.b4
+            |JOIN testcat.ns.b12 ON b4.id = b12.id
+            |JOIN testcat.ns.b8 ON b4.id = b8.id
+            |""".stripMargin)
+
+        val plan = df.queryExecution.executedPlan
+        assert(collectShuffles(plan).isEmpty, "SPJ should be triggered for 
both joins")
+        val scans = collectScans(plan)
+        def reducersOf(table: String): Option[Seq[Option[Reducer[_, _]]]] =
+          
scans.find(_.table.name().endsWith(s".$table")).get.spjParams.reducers
+        assert(reducersOf("b4").isEmpty, "b4 is the coarsest side and needs no 
reducer")
+        assert(reducersOf("b12").exists(_.length == 1) && 
reducersOf("b12").get.head.isDefined,
+          "b12 scan should keep the inner join's reducer onto bucket(4, id)")
+        assert(reducersOf("b8").exists(_.length == 1) && 
reducersOf("b8").get.head.isDefined,
+          "b8 scan should get the outer join's reducer onto bucket(4, id)")
+        checkAnswer(df, Seq(Row(4, 4, 4)))
+      }
+    }
+  }
+
+  test("SPARK-60232: nested SPJ preserves join key positions over common 
partition values") {
+    val items2 = "items2"
+    val itemsPartitions = Array(identity("id"), identity("name"))
+
+    val inserts = Seq(
+      s"INSERT INTO testcat.ns.$items VALUES " +
+        "(1, 'aa', 10.0, cast('2020-01-01' as timestamp)), " +
+        "(2, 'bb', 20.0, cast('2020-01-01' as timestamp))",
+      s"INSERT INTO testcat.ns.$items2 VALUES " +
+        "(1, 'aa', 10.0, cast('2020-01-01' as timestamp)), " +
+        "(2, 'bb', 20.0, cast('2020-01-01' as timestamp))",
+      "INSERT INTO testcat.ns.purchases VALUES " +
+        "(1, 100.0, cast('2020-01-01' as timestamp)), " +
+        "(2, 200.0, cast('2020-01-01' as timestamp))")
+    val expected = Seq(Row(1, "aa", 10.0, 100.0), Row(2, "bb", 20.0, 200.0))
+    Seq(true, false).foreach { aqe =>
+      withSQLConf(
+        SQLConf.AUTO_BROADCASTJOIN_THRESHOLD.key -> "-1",
+        SQLConf.ADAPTIVE_EXECUTION_ENABLED.key -> aqe.toString,
+        SQLConf.V2_BUCKETING_SHUFFLE_ENABLED.key -> "true",
+        SQLConf.V2_BUCKETING_PUSH_PART_VALUES_ENABLED.key -> "true",
+        SQLConf.V2_BUCKETING_ALLOW_JOIN_KEYS_SUBSET_OF_PARTITION_KEYS.key -> 
"true",
+        SQLConf.REQUIRE_ALL_CLUSTER_KEYS_FOR_CO_PARTITION.key -> "false") {
+        catalog.clearTables()
+        createTable(items, itemsColumns, itemsPartitions)
+        createTable(items2, itemsColumns, itemsPartitions)
+        createTable(purchases, purchasesColumns, Array.empty)
+        inserts.foreach(sql)
+
+        val df = sql(
+          s"""
+             |SELECT /*+ MERGE(i, j, p) */ i.id, i.name, i.price, p.price
+             |FROM testcat.ns.$items i JOIN testcat.ns.$items2 j
+             |ON i.id = j.id AND i.name = j.name
+             |JOIN testcat.ns.$purchases p ON i.id = p.item_id
+             |ORDER BY i.id, i.name
+             |""".stripMargin)
+
+        val plan = df.queryExecution.executedPlan
+        assert(collectShuffles(plan).size == 1,
+          "only the unpartitioned purchases side should shuffle")
+        val itemsScan = 
collectScans(plan).find(_.table.name().endsWith(items)).get
+        assert(itemsScan.spjParams.commonPartitionValues.nonEmpty,
+          "inner join should push common partition values into the items scan")
+        assert(itemsScan.spjParams.joinKeyPositions.contains(Seq(0)),
+          "outer join should project common partition values down to the id 
join key")
+        checkAnswer(df, expected)
+      }
+    }
+  }
+
+  test("SPARK-60232: nested SPJ with multiple names per id over common 
partition values") {
+    val items2 = "items2"
+    val itemsPartitions = Array(identity("id"), identity("name"))
+    val inserts = Seq(
+      s"INSERT INTO testcat.ns.$items VALUES " +
+        "(1, 'aa', 10.0, cast('2020-01-01' as timestamp)), " +
+        "(1, 'bb', 20.0, cast('2020-01-02' as timestamp))",
+      s"INSERT INTO testcat.ns.$items2 VALUES " +
+        "(1, 'aa', 10.0, cast('2020-01-01' as timestamp)), " +
+        "(1, 'bb', 20.0, cast('2020-01-02' as timestamp))",
+      "INSERT INTO testcat.ns.purchases VALUES " +
+        "(1, 100.0, cast('2020-01-01' as timestamp))")
+    val expected = Seq(Row(1, "aa", 10.0, 100.0), Row(1, "bb", 20.0, 100.0))
+    Seq(true, false).foreach { aqe =>
+        catalog.clearTables()
+        createTable(items, itemsColumns, itemsPartitions)
+        createTable(items2, itemsColumns, itemsPartitions)
+        createTable(purchases, purchasesColumns, Array.empty)
+        inserts.foreach(sql)
+
+        withSQLConf(
+          SQLConf.AUTO_BROADCASTJOIN_THRESHOLD.key -> "-1",
+          SQLConf.ADAPTIVE_EXECUTION_ENABLED.key -> aqe.toString,
+          SQLConf.V2_BUCKETING_SHUFFLE_ENABLED.key -> "true",
+          SQLConf.V2_BUCKETING_PUSH_PART_VALUES_ENABLED.key -> "true",
+          SQLConf.V2_BUCKETING_ALLOW_JOIN_KEYS_SUBSET_OF_PARTITION_KEYS.key -> 
"true",
+          SQLConf.REQUIRE_ALL_CLUSTER_KEYS_FOR_CO_PARTITION.key -> "false") {
+          val df = sql(
+            s"""
+               |SELECT /*+ MERGE(i, j, p) */ i.id, i.name, i.price, p.price
+               |FROM testcat.ns.$items i JOIN testcat.ns.$items2 j
+               |ON i.id = j.id AND i.name = j.name
+               |JOIN testcat.ns.$purchases p ON i.id = p.item_id
+               |ORDER BY i.id, i.name
+               |""".stripMargin)
+
+          val plan = df.queryExecution.executedPlan
+          assert(collectShuffles(plan).size == 1,
+            "only the unpartitioned purchases side should shuffle")
+          checkAnswer(df, expected)
+        }
+    }
+  }
+
+  private def createNestedSpjIdTables(): Unit = {
+    val cols = Array(
+      Column.create("store", IntegerType),
+      Column.create("id", IntegerType),
+      Column.create("data", StringType))
+    Seq("e1", "e2", "e3", "e5").foreach(t => createTable(t, cols, 
Array(identity("id"))))
+    sql("INSERT INTO testcat.ns.e1 VALUES (0, 1, 'a'), (0, 2, 'b')")
+    sql("INSERT INTO testcat.ns.e2 VALUES (0, 1, 'a'), (0, 2, 'b')")
+    sql("INSERT INTO testcat.ns.e3 VALUES (0, 1, 'a'), (0, 2, 'b'), (0, 3, 
'c')")
+    sql("INSERT INTO testcat.ns.e5 VALUES (0, 2, 'b'), (0, 3, 'c')")
+    createTable("e4", cols, Array(identity("store"), identity("id")))
+    sql("INSERT INTO testcat.ns.e4 VALUES (10, 1, 'a'), (20, 2, 'b'), (30, 3, 
'c')")
+  }
+
+  test("SPARK-60232: nested SPJ pushes outer merged values into a nested 
join") {
+    createNestedSpjIdTables()
+    Seq(true, false).foreach { aqe =>
+      withSQLConf(
+        SQLConf.AUTO_BROADCASTJOIN_THRESHOLD.key -> "-1",
+        SQLConf.ADAPTIVE_EXECUTION_ENABLED.key -> aqe.toString,
+        SQLConf.V2_BUCKETING_ALLOW_JOIN_KEYS_SUBSET_OF_PARTITION_KEYS.key -> 
"true",
+        SQLConf.V2_BUCKETING_PARTITION_FILTER_ENABLED.key -> "true") {
+        val df = sql(
+          """
+            |SELECT /*+ MERGE(e1, e2, e5) */ e1.id, e2.id, e5.id
+            |FROM testcat.ns.e1 JOIN testcat.ns.e2 ON e1.id = e2.id
+            |RIGHT JOIN testcat.ns.e5 ON e1.id = e5.id
+            |""".stripMargin)
+        checkAnswer(df, Seq(Row(2, 2, 2), Row(null, null, 3)))
+      }
+
+      withSQLConf(
+        SQLConf.AUTO_BROADCASTJOIN_THRESHOLD.key -> "-1",
+        SQLConf.ADAPTIVE_EXECUTION_ENABLED.key -> aqe.toString,
+        SQLConf.V2_BUCKETING_ALLOW_JOIN_KEYS_SUBSET_OF_PARTITION_KEYS.key -> 
"true") {
+        val df = sql(
+          """
+            |SELECT /*+ MERGE(e1, e2, e3) */ e1.id, e2.id, e3.id
+            |FROM testcat.ns.e1 JOIN testcat.ns.e2 ON e1.id = e2.id
+            |JOIN testcat.ns.e3 ON e1.id = e3.id
+            |""".stripMargin)
+        assert(collectShuffles(df.queryExecution.executedPlan).isEmpty,
+          "SPJ should be triggered for both joins")
+        checkAnswer(df, Seq(Row(1, 1, 1), Row(2, 2, 2)))
+      }
+    }
+  }
+
+  test("SPARK-60232: nested SPJ with subset join keys on a two-key partitioned 
side") {
+    createNestedSpjIdTables()
+    Seq(true, false).foreach { aqe =>
+      withSQLConf(
+        SQLConf.AUTO_BROADCASTJOIN_THRESHOLD.key -> "-1",
+        SQLConf.ADAPTIVE_EXECUTION_ENABLED.key -> aqe.toString,
+        SQLConf.V2_BUCKETING_SHUFFLE_ENABLED.key -> "true",
+        SQLConf.V2_BUCKETING_ALLOW_JOIN_KEYS_SUBSET_OF_PARTITION_KEYS.key -> 
"true",
+        SQLConf.REQUIRE_ALL_CLUSTER_KEYS_FOR_CO_PARTITION.key -> "false") {
+        val df = sql(
+          """
+            |SELECT /*+ MERGE(e1, e2, e4) */ e1.id, e2.id, e4.id, e4.store
+            |FROM testcat.ns.e1 JOIN testcat.ns.e2 ON e1.id = e2.id
+            |JOIN testcat.ns.e4 ON e1.id = e4.id
+            |""".stripMargin)
+        checkAnswer(df, Seq(Row(1, 1, 1, 10), Row(2, 2, 2, 20)))
+      }
+    }
+  }
+
+  test("SPARK-60232: nested SPJ on all partition keys, then on a subset") {
+    val cols = Array(
+      Column.create("store_id", IntegerType),
+      Column.create("dept_id", IntegerType),
+      Column.create("data", StringType))
+    createTable("a", cols, Array(identity("store_id"), identity("dept_id")))
+    createTable("b", cols, Array(identity("store_id"), identity("dept_id")))
+    createTable("c", cols, Array(identity("dept_id")))
+    sql("INSERT INTO testcat.ns.a VALUES (1, 2, 'a1'), (2, 2, 'a2'), (3, 1, 
'a3')")
+    sql("INSERT INTO testcat.ns.b VALUES (1, 2, 'b1'), (2, 2, 'b2'), (3, 1, 
'b3')")
+    sql("INSERT INTO testcat.ns.c VALUES (0, 1, 'c1'), (0, 2, 'c2'), (0, 3, 
'c3')")
+
+    Seq(true, false).foreach { aqe =>
+      withSQLConf(
+        SQLConf.AUTO_BROADCASTJOIN_THRESHOLD.key -> "-1",
+        SQLConf.ADAPTIVE_EXECUTION_ENABLED.key -> aqe.toString,
+        SQLConf.V2_BUCKETING_ALLOW_JOIN_KEYS_SUBSET_OF_PARTITION_KEYS.key -> 
"true",
+        SQLConf.REQUIRE_ALL_CLUSTER_KEYS_FOR_CO_PARTITION.key -> "false") {
+        val expected = Seq(
+          Row(1, 2, "a1", 1, 2, "b1", 0, 2, "c2"),
+          Row(2, 2, "a2", 2, 2, "b2", 0, 2, "c2"),
+          Row(3, 1, "a3", 3, 1, "b3", 0, 1, "c1"))
+        Seq("a.dept_id", "b.dept_id").foreach { outerKey =>
+          val df = sql(
+            s"""
+               |SELECT /*+ MERGE(a, b, c) */ *
+               |FROM testcat.ns.a JOIN testcat.ns.b
+               |ON a.store_id = b.store_id AND a.dept_id = b.dept_id
+               |JOIN testcat.ns.c ON $outerKey = c.dept_id
+               |""".stripMargin)
+          checkAnswer(df, expected)
+        }
+      }
+    }
+  }
+
+  test("SPARK-60232: nested SPJ dedupes common values that project to the same 
join key") {
+    val cols = Array(
+      Column.create("store_id", IntegerType),
+      Column.create("dept_id", IntegerType),
+      Column.create("data", StringType))
+    createTable("a", cols, Array(identity("store_id"), identity("dept_id")))
+    createTable("b", cols, Array(identity("store_id"), identity("dept_id")))
+    createTable("c", cols, Array(identity("dept_id")))
+    sql("INSERT INTO testcat.ns.a VALUES (1, 2, 'a1'), (2, 2, 'a2')")
+    sql("INSERT INTO testcat.ns.b VALUES (1, 2, 'b1'), (2, 2, 'b2')")
+    sql("INSERT INTO testcat.ns.c VALUES (0, 1, 'c1'), (0, 2, 'c2'), (0, 3, 
'c3')")
+
+    Seq(true, false).foreach { aqe =>
+      withSQLConf(
+        SQLConf.AUTO_BROADCASTJOIN_THRESHOLD.key -> "-1",
+        SQLConf.ADAPTIVE_EXECUTION_ENABLED.key -> aqe.toString,
+        SQLConf.V2_BUCKETING_ALLOW_JOIN_KEYS_SUBSET_OF_PARTITION_KEYS.key -> 
"true",
+        SQLConf.REQUIRE_ALL_CLUSTER_KEYS_FOR_CO_PARTITION.key -> "false") {
+        val df = sql(
+          """
+            |SELECT /*+ MERGE(a, b, c) */ a.data, b.data, c.data
+            |FROM testcat.ns.a JOIN testcat.ns.b
+            |ON a.store_id = b.store_id AND a.dept_id = b.dept_id
+            |JOIN testcat.ns.c ON a.dept_id = c.dept_id
+            |""".stripMargin)
+        checkAnswer(df, Seq(Row("a1", "b1", "c2"), Row("a2", "b2", "c2")))
+      }
+    }
+  }
+
+  test("SPARK-60232: nested SPJ with several names per id and a partitioned 
third side") {
+    val cols = Array(
+      Column.create("id", IntegerType),
+      Column.create("name", StringType),
+      Column.create("price", DoubleType))
+    createTable("i", cols, Array(identity("id"), identity("name")))
+    createTable("j", cols, Array(identity("id"), identity("name")))
+    createTable("k", Array(Column.create("id", IntegerType), 
Column.create("v", DoubleType)),
+      Array(identity("id")))
+    sql("INSERT INTO testcat.ns.i VALUES (1, 'aa', 1.0), (1, 'bb', 2.0)")
+    sql("INSERT INTO testcat.ns.j VALUES (1, 'aa', 1.0), (1, 'bb', 2.0)")
+    sql("INSERT INTO testcat.ns.k VALUES (1, 10.0), (2, 20.0)")
+
+    Seq(true, false).foreach { aqe =>
+      withSQLConf(
+        SQLConf.AUTO_BROADCASTJOIN_THRESHOLD.key -> "-1",
+        SQLConf.ADAPTIVE_EXECUTION_ENABLED.key -> aqe.toString,
+        SQLConf.V2_BUCKETING_ALLOW_JOIN_KEYS_SUBSET_OF_PARTITION_KEYS.key -> 
"true",
+        SQLConf.REQUIRE_ALL_CLUSTER_KEYS_FOR_CO_PARTITION.key -> "false") {
+        checkAnswer(
+          sql(
+            """
+              |SELECT /*+ MERGE(i, j, k) */ i.id, i.name, j.name, k.v
+              |FROM testcat.ns.i JOIN testcat.ns.j ON i.id = j.id AND i.name = 
j.name
+              |JOIN testcat.ns.k ON i.id = k.id
+              |""".stripMargin),
+          Seq(Row(1, "aa", "aa", 10.0), Row(1, "bb", "bb", 10.0)))
+        checkAnswer(
+          sql(
+            """
+              |SELECT /*+ MERGE(i, j, k) */ i.id, i.name, k.v
+              |FROM testcat.ns.i LEFT ANTI JOIN testcat.ns.j
+              |ON i.id = j.id AND i.name = j.name
+              |JOIN testcat.ns.k ON i.id = k.id
+              |""".stripMargin),
+          Seq.empty)
+      }
+    }
+  }
+
+  test("SPARK-60232: four-way nested SPJ joining on a key dropped by an 
earlier join") {
+    val cols = Array(
+      Column.create("id", IntegerType),
+      Column.create("name", StringType),
+      Column.create("price", DoubleType))
+    createTable("i", cols, Array(identity("id"), identity("name")))
+    createTable("j", cols, Array(identity("id"), identity("name")))
+    createTable("k", Array(Column.create("id", IntegerType), 
Column.create("v", DoubleType)),
+      Array(identity("id")))
+    createTable("m", Array(Column.create("name", StringType), 
Column.create("w", DoubleType)),
+      Array(identity("name")))
+    sql("INSERT INTO testcat.ns.i VALUES (1, 'aa', 1.0), (2, 'bb', 2.0)")
+    sql("INSERT INTO testcat.ns.j VALUES (1, 'aa', 1.0), (2, 'bb', 2.0)")
+    sql("INSERT INTO testcat.ns.k VALUES (1, 10.0), (2, 20.0)")
+    sql("INSERT INTO testcat.ns.m VALUES ('aa', 100.0), ('bb', 200.0)")
+
+    Seq(true, false).foreach { aqe =>
+      withSQLConf(
+        SQLConf.AUTO_BROADCASTJOIN_THRESHOLD.key -> "-1",
+        SQLConf.ADAPTIVE_EXECUTION_ENABLED.key -> aqe.toString,
+        SQLConf.V2_BUCKETING_SHUFFLE_ENABLED.key -> "true",
+        SQLConf.V2_BUCKETING_ALLOW_JOIN_KEYS_SUBSET_OF_PARTITION_KEYS.key -> 
"true",
+        SQLConf.REQUIRE_ALL_CLUSTER_KEYS_FOR_CO_PARTITION.key -> "false") {
+        val df = sql(
+          """
+            |SELECT /*+ MERGE(i, j, k, m) */ i.id, i.name, k.v, m.w
+            |FROM testcat.ns.i JOIN testcat.ns.j ON i.id = j.id AND i.name = 
j.name
+            |JOIN testcat.ns.k ON i.id = k.id
+            |JOIN testcat.ns.m ON i.name = m.name
+            |""".stripMargin)
+        checkAnswer(df, Seq(Row(1, "aa", 10.0, 100.0), Row(2, "bb", 20.0, 
200.0)))
+      }
+    }
+  }
+
+  test("SPARK-60232: nested SPJ with compatible transforms across three bucket 
counts") {
+    val idCols = Array(Column.create("id", IntegerType))
+    Seq("b2" -> 2, "b4" -> 4, "b8" -> 8, "b12" -> 12, "c12" -> 12).foreach { 
case (t, n) =>
+      createTable(t, idCols, Array(bucket(n, "id")))
+      sql(s"INSERT INTO testcat.ns.$t SELECT id FROM range(24)")
+    }
+    val expected = (0 until 24).map(i => Row(i, i, i))
+
+    Seq(true, false).foreach { aqe =>
+      withSQLConf(
+        SQLConf.AUTO_BROADCASTJOIN_THRESHOLD.key -> "-1",
+        SQLConf.ADAPTIVE_EXECUTION_ENABLED.key -> aqe.toString,
+        SQLConf.V2_BUCKETING_ALLOW_COMPATIBLE_TRANSFORMS.key -> "true") {
+        // When the outer key is on the reduced side, the outer join falls 
back to a shuffle.
+        Seq(
+          ("b12", "b4", "c12", 2),
+          ("b4", "b12", "c12", 0),
+          ("b8", "b4", "b2", 2),
+          ("b4", "b8", "b2", 0)).foreach { case (t1, t2, t3, numShuffles) =>
+          val df = sql(
+            s"""
+               |SELECT /*+ MERGE($t1, $t2, $t3) */ $t1.id, $t2.id, $t3.id
+               |FROM testcat.ns.$t1 JOIN testcat.ns.$t2 ON $t1.id = $t2.id
+               |JOIN testcat.ns.$t3 ON $t1.id = $t3.id
+               |""".stripMargin)
+          assert(collectShuffles(df.queryExecution.executedPlan).size == 
numShuffles,
+            s"$t1 JOIN $t2 JOIN $t3 should plan $numShuffles shuffles")
+          checkAnswer(df, expected)
+        }
+      }
+    }
+  }
+
+  test("SPARK-60232: nested SPJ narrows common values already projected by an 
inner join") {
+    val cols = Array(
+      Column.create("a", IntegerType),
+      Column.create("b", IntegerType),
+      Column.create("c", IntegerType))
+    createTable("ta", cols, Array(identity("a"), identity("b"), identity("c")))
+    createTable("tb", cols, Array(identity("b"), identity("c")))
+    createTable("tc", cols, Array.empty)
+    sql("INSERT INTO testcat.ns.ta VALUES (1, 10, 100), (2, 20, 100), (3, 30, 
300)")
+    sql("INSERT INTO testcat.ns.tb VALUES (0, 10, 100), (0, 20, 100), (0, 30, 
300)")
+    sql("INSERT INTO testcat.ns.tc VALUES (0, 0, 100), (0, 0, 300)")
+
+    Seq(true, false).foreach { aqe =>
+      withSQLConf(
+        SQLConf.AUTO_BROADCASTJOIN_THRESHOLD.key -> "-1",
+        SQLConf.ADAPTIVE_EXECUTION_ENABLED.key -> aqe.toString,
+        SQLConf.V2_BUCKETING_SHUFFLE_ENABLED.key -> "true",
+        SQLConf.V2_BUCKETING_ALLOW_JOIN_KEYS_SUBSET_OF_PARTITION_KEYS.key -> 
"true",
+        SQLConf.REQUIRE_ALL_CLUSTER_KEYS_FOR_CO_PARTITION.key -> "false") {
+        val df = sql(
+          """
+            |SELECT /*+ MERGE(ta, tb, tc) */ ta.a, ta.b, ta.c, tc.c
+            |FROM testcat.ns.ta JOIN testcat.ns.tb ON ta.b = tb.b AND ta.c = 
tb.c
+            |JOIN testcat.ns.tc ON ta.c = tc.c
+            |""".stripMargin)
+        checkAnswer(df, Seq(Row(1, 10, 100, 100), Row(2, 20, 100, 100), Row(3, 
30, 300, 300)))
+      }
+    }
+  }
+
+  test("SPARK-60232: nested SPJ keeps projected values in the order a one-side 
shuffle uses") {
+    val cols = Array(
+      Column.create("store", IntegerType),
+      Column.create("dept", IntegerType),
+      Column.create("data", StringType))
+    createTable("oa", cols, Array(identity("store"), identity("dept")))
+    createTable("ob", cols, Array(identity("store"), identity("dept")))
+    createTable("oc", cols, Array.empty)
+    // The projected dept keys come out as 20, 10, which is not their sorted 
order.
+    sql("INSERT INTO testcat.ns.oa VALUES (1, 20, 'a1'), (2, 10, 'a2')")
+    sql("INSERT INTO testcat.ns.ob VALUES (1, 20, 'b1'), (2, 10, 'b2')")
+    sql("INSERT INTO testcat.ns.oc VALUES (0, 10, 'c10'), (0, 20, 'c20')")
+
+    Seq(true, false).foreach { aqe =>
+      withSQLConf(
+        SQLConf.AUTO_BROADCASTJOIN_THRESHOLD.key -> "-1",
+        SQLConf.ADAPTIVE_EXECUTION_ENABLED.key -> aqe.toString,
+        SQLConf.V2_BUCKETING_SHUFFLE_ENABLED.key -> "true",
+        SQLConf.V2_BUCKETING_ALLOW_JOIN_KEYS_SUBSET_OF_PARTITION_KEYS.key -> 
"true",
+        SQLConf.REQUIRE_ALL_CLUSTER_KEYS_FOR_CO_PARTITION.key -> "false") {
+        val df = sql(
+          """
+            |SELECT /*+ MERGE(oa, ob, oc) */ oa.store, oa.data, ob.data, 
oc.data
+            |FROM testcat.ns.oa JOIN testcat.ns.ob ON oa.store = ob.store AND 
oa.dept = ob.dept
+            |JOIN testcat.ns.oc ON oa.dept = oc.dept
+            |""".stripMargin)
+        assert(collectShuffles(df.queryExecution.executedPlan).size == 1,
+          "only the unpartitioned oc side should shuffle")
+        checkAnswer(df, Seq(Row(1, "a1", "b1", "c20"), Row(2, "a2", "b2", 
"c10")))
+      }
+    }
+  }
+
+  test("SPARK-60232: nested SPJ does not shuffle the other side onto a reduced 
scan's layout") {
+    val idCols = Array(Column.create("id", LongType))
+    Seq("l12" -> 12, "l4" -> 4, "l2" -> 2).foreach { case (t, n) =>
+      createTable(t, idCols, Array(bucket(n, "id")))
+      sql(s"INSERT INTO testcat.ns.$t SELECT id FROM range(24)")
+    }
+    createTable("ld", idCols, Array.empty)
+    sql("INSERT INTO testcat.ns.ld SELECT id FROM range(24)")
+    val expected = (0L until 24L).map(i => Row(i, i, i))
+
+    Seq(true, false).foreach { aqe =>
+      withSQLConf(
+        SQLConf.AUTO_BROADCASTJOIN_THRESHOLD.key -> "-1",
+        SQLConf.ADAPTIVE_EXECUTION_ENABLED.key -> aqe.toString,
+        SQLConf.V2_BUCKETING_SHUFFLE_ENABLED.key -> "true",
+        SQLConf.V2_BUCKETING_ALLOW_COMPATIBLE_TRANSFORMS.key -> "true") {
+        Seq("l2" -> 1, "ld" -> 2).foreach { case (third, numShuffles) =>
+          val df = sql(
+            s"""
+               |SELECT /*+ MERGE(l12, l4, $third) */ l12.id, l4.id, $third.id
+               |FROM testcat.ns.l12 JOIN testcat.ns.l4 ON l12.id = l4.id
+               |JOIN testcat.ns.$third ON l12.id = $third.id
+               |""".stripMargin)
+          assert(collectShuffles(df.queryExecution.executedPlan).size == 
numShuffles,
+            s"the join with $third should plan $numShuffles shuffles")
+          checkAnswer(df, expected)
+        }
+
+        // An outer join reports a single partitioning rather than a 
collection.
+        val outerDf = sql(
+          """
+            |SELECT /*+ MERGE(l12, l4, ld) */ l12.id, l4.id, ld.id
+            |FROM testcat.ns.l12 LEFT JOIN testcat.ns.l4 ON l12.id = l4.id
+            |JOIN testcat.ns.ld ON l12.id = ld.id
+            |""".stripMargin)
+        checkAnswer(outerDf, expected)
+      }
+    }
+  }
+
+  test("SPARK-60232: nested SPJ keeps SPJ on a key the inner join did not 
reduce") {
+    val cols = Array(Column.create("store", IntegerType), Column.create("id", 
IntegerType))
+    createTable("ra", cols, Array(identity("store"), bucket(12, "id")))
+    createTable("rb", cols, Array(identity("store"), bucket(4, "id")))
+    createTable("rc", Array(Column.create("store", IntegerType)), 
Array(identity("store")))
+    sql("INSERT INTO testcat.ns.ra SELECT CAST(id % 3 AS INT), CAST(id AS INT) 
FROM range(12)")
+    sql("INSERT INTO testcat.ns.rb SELECT CAST(id % 3 AS INT), CAST(id AS INT) 
FROM range(12)")
+    sql("INSERT INTO testcat.ns.rc VALUES (0), (1), (2)")
+
+    Seq(true, false).foreach { aqe =>
+      withSQLConf(
+        SQLConf.AUTO_BROADCASTJOIN_THRESHOLD.key -> "-1",
+        SQLConf.ADAPTIVE_EXECUTION_ENABLED.key -> aqe.toString,
+        SQLConf.V2_BUCKETING_ALLOW_COMPATIBLE_TRANSFORMS.key -> "true",
+        SQLConf.V2_BUCKETING_ALLOW_JOIN_KEYS_SUBSET_OF_PARTITION_KEYS.key -> 
"true",
+        SQLConf.REQUIRE_ALL_CLUSTER_KEYS_FOR_CO_PARTITION.key -> "false") {
+        val df = sql(
+          """
+            |SELECT /*+ MERGE(ra, rb, rc) */ ra.id, rb.id, rc.store
+            |FROM testcat.ns.ra JOIN testcat.ns.rb ON ra.store = rb.store AND 
ra.id = rb.id
+            |JOIN testcat.ns.rc ON ra.store = rc.store
+            |""".stripMargin)
+        assert(collectShuffles(df.queryExecution.executedPlan).isEmpty,
+          "SPJ should be triggered for both joins")
+        checkAnswer(df, (0 until 12).map(i => Row(i, i, i % 3)))
+      }
+    }
+  }
+
+  test("SPARK-60232: nested SPJ does not push into a broadcast build side") {
+    createTable("qa", Array(Column.create("id", IntegerType), 
Column.create("x", StringType)),
+      Array(identity("id")))
+    createTable("qd", Array(Column.create("x", StringType), 
Column.create("region", IntegerType)),
+      Array(identity("region")))
+    createTable("qc", Array(Column.create("id", IntegerType)), 
Array(identity("id")))
+    sql("INSERT INTO testcat.ns.qa VALUES (1, 'a'), (2, 'b'), (3, 'c')")
+    sql("INSERT INTO testcat.ns.qd VALUES ('a', 10), ('b', 20), ('c', 30)")
+    sql("INSERT INTO testcat.ns.qc VALUES (1), (2), (3)")
+
+    Seq(true, false).foreach { aqe =>
+      withSQLConf(
+        SQLConf.AUTO_BROADCASTJOIN_THRESHOLD.key -> "-1",
+        SQLConf.ADAPTIVE_EXECUTION_ENABLED.key -> aqe.toString) {
+        val df = sql(
+          """
+            |SELECT /*+ BROADCAST(qd) */ qa.id, qd.region, qc.id
+            |FROM testcat.ns.qa JOIN testcat.ns.qd ON qa.x = qd.x
+            |JOIN testcat.ns.qc ON qa.id = qc.id
+            |""".stripMargin)
+        checkAnswer(df, Seq(Row(1, 10, 1), Row(2, 20, 2), Row(3, 30, 3)))
+      }
+    }
+  }
+
+  test("SPARK-60232: ordering a subset-key SPJ keeps the join's key 
positions") {
+    val cols = Array(Column.create("a", IntegerType), Column.create("b", 
IntegerType))
+    createTable("oa3", cols, Array(identity("a"), identity("b")))
+    sql("INSERT INTO testcat.ns.oa3 VALUES (1, 10), (2, 20), (3, 10), (4, 30)")
+
+    createTable("ob3", cols, Array(identity("b")))
+    sql("INSERT INTO testcat.ns.ob3 VALUES (0, 10), (0, 20)")
+
+    Seq(true, false).foreach { aqe =>
+      withSQLConf(
+        SQLConf.AUTO_BROADCASTJOIN_THRESHOLD.key -> "-1",
+        SQLConf.ADAPTIVE_EXECUTION_ENABLED.key -> aqe.toString,
+        SQLConf.V2_BUCKETING_SORTING_ENABLED.key -> "true",
+        SQLConf.V2_BUCKETING_ALLOW_JOIN_KEYS_SUBSET_OF_PARTITION_KEYS.key -> 
"true",
+        SQLConf.REQUIRE_ALL_CLUSTER_KEYS_FOR_CO_PARTITION.key -> "false") {
+        val df = sql(
+          """
+            |SELECT /*+ MERGE(oa3, ob3) */ oa3.b, oa3.a, ob3.a
+            |FROM testcat.ns.oa3 LEFT JOIN testcat.ns.ob3 ON oa3.b = ob3.b
+            |ORDER BY oa3.b
+            |""".stripMargin)
+
+        // Rows with the same b may come in either order, so check the order 
on b alone.
+        val rows = df.collect().toSeq
+        assert(rows.map(_.getInt(0)) == Seq(10, 10, 20, 30), "rows should be 
ordered by b")
+        assert(rows.toSet ==
+          Set(Row(10, 1, 0), Row(10, 3, 0), Row(20, 2, 0), Row(30, 4, null)))
+      }
+    }
+  }
+
+  test("SPARK-60232: nested SPJ sees a reduced scan through an alias") {
+    val idCols = Array(Column.create("id", LongType))
+    Seq("l12" -> 12, "l4" -> 4, "l3" -> 3).foreach { case (t, n) =>
+      createTable(t, idCols, Array(bucket(n, "id")))
+      sql(s"INSERT INTO testcat.ns.$t SELECT id FROM range(24)")
+    }
+
+    Seq(true, false).foreach { aqe =>
+      withSQLConf(
+        SQLConf.AUTO_BROADCASTJOIN_THRESHOLD.key -> "-1",
+        SQLConf.ADAPTIVE_EXECUTION_ENABLED.key -> aqe.toString,
+        SQLConf.V2_BUCKETING_ALLOW_COMPATIBLE_TRANSFORMS.key -> "true") {
+        // The nested join may also output the original column next to its 
alias.
+        Seq("", "l12.id, ").foreach { extraColumn =>
+          val df = sql(
+            s"""
+               |SELECT /*+ MERGE(x, l3) */ x.id2, x.id4, l3.id
+               |FROM (
+               |  SELECT /*+ MERGE(l12, l4) */ ${extraColumn}l12.id AS id2, 
l4.id AS id4
+               |  FROM testcat.ns.l12 JOIN testcat.ns.l4 ON l12.id = l4.id) x
+               |JOIN testcat.ns.l3 ON x.id2 = l3.id
+               |""".stripMargin)
+          assert(collectShuffles(df.queryExecution.executedPlan).size == 2,
+            "the outer join should fall back to shuffling both sides")
+          checkAnswer(df, (0L until 24L).map(i => Row(i, i, i)))
+        }
+      }
+    }
+  }
+
+  test("SPARK-60232: nested SPJ shuffles a compatible nested join built on a 
reduced scan") {
+    val idCols = Array(Column.create("id", LongType))
+    Seq("nb4" -> 4, "nb12" -> 12).foreach { case (t, n) =>
+      createTable(t, idCols, Array(bucket(n, "id")))
+      sql(s"INSERT INTO testcat.ns.$t SELECT id FROM range(24)")
+    }
+    createTable("nc6", idCols, Array(bucket(6, "id")))
+    sql("INSERT INTO testcat.ns.nc6 VALUES (0), (1), (2), (3), (6)")
+
+    Seq(true, false).foreach { aqe =>
+      withSQLConf(
+        SQLConf.AUTO_BROADCASTJOIN_THRESHOLD.key -> "-1",
+        SQLConf.ADAPTIVE_EXECUTION_ENABLED.key -> aqe.toString,
+        SQLConf.V2_BUCKETING_SHUFFLE_ENABLED.key -> "true",
+        SQLConf.V2_BUCKETING_ALLOW_COMPATIBLE_TRANSFORMS.key -> "true") {
+        val df = sql(
+          """
+            |SELECT /*+ MERGE(nb4, nb12, nc6) */ nb4.id, nb12.id, nc6.id
+            |FROM testcat.ns.nb4 JOIN testcat.ns.nb12 ON nb4.id = nb12.id
+            |JOIN testcat.ns.nc6 ON nb12.id = nc6.id
+            |""".stripMargin)
+        assert(collectShuffles(df.queryExecution.executedPlan).size == 1,
+          "the nested join should be shuffled onto nc6's layout")
+        checkAnswer(df, Seq(0L, 1L, 2L, 3L, 6L).map(i => Row(i, i, i)))
+
+        // An outer join reports a single partitioning rather than a 
collection.
+        val outerDf = sql(
+          """
+            |SELECT /*+ MERGE(nb4, nb12, nc6) */ nb12.id, nb4.id, nc6.id
+            |FROM testcat.ns.nb12 LEFT JOIN testcat.ns.nb4 ON nb12.id = nb4.id
+            |JOIN testcat.ns.nc6 ON nb12.id = nc6.id
+            |""".stripMargin)
+        assert(collectShuffles(outerDf.queryExecution.executedPlan).size == 1,
+          "the outer join below should be shuffled onto nc6's layout")
+        checkAnswer(outerDf, Seq(0L, 1L, 2L, 3L, 6L).map(i => Row(i, i, i)))
+      }
+    }
+  }
+
+  test("SPARK-60232: nested SPJ does not reshuffle a nested join above a 
shuffle") {
+    val idCols = Array(Column.create("id", LongType))
+    Seq("l12" -> 12, "l4" -> 4, "c12" -> 12, "d12" -> 12).foreach { case (t, 
n) =>
+      createTable(t, idCols, Array(bucket(n, "id")))
+      sql(s"INSERT INTO testcat.ns.$t SELECT id FROM range(24)")
+    }
+
+    Seq(true, false).foreach { aqe =>
+      withSQLConf(
+        SQLConf.AUTO_BROADCASTJOIN_THRESHOLD.key -> "-1",
+        SQLConf.ADAPTIVE_EXECUTION_ENABLED.key -> aqe.toString,
+        SQLConf.V2_BUCKETING_SHUFFLE_ENABLED.key -> "true",
+        SQLConf.V2_BUCKETING_ALLOW_COMPATIBLE_TRANSFORMS.key -> "true") {
+        // The second join shuffles the nested one onto c12's layout. The 
reduced l12 below that
+        // shuffle no longer decides the layout the third join sees.
+        val df = sql(
+          """
+            |SELECT /*+ MERGE(l12, l4, c12, d12) */ l12.id, l4.id, c12.id, 
d12.id
+            |FROM testcat.ns.l12 JOIN testcat.ns.l4 ON l12.id = l4.id
+            |JOIN testcat.ns.c12 ON l12.id = c12.id
+            |JOIN testcat.ns.d12 ON l12.id = d12.id
+            |""".stripMargin)
+        // collectShuffles would count that shuffle once for each join above 
it.
+        assert(collectAllShuffles(df.queryExecution.executedPlan).size == 1,
+          "only the nested join should be shuffled, once")
+        checkAnswer(df, (0L until 24L).map(i => Row(i, i, i, i)))
+      }
+    }
+  }
+
+  test("SPARK-60232: nested SPJ sees a reduced scan through a broadcast join's 
build key") {
+    val idCols = Array(Column.create("id", LongType))
+    Seq("l12" -> 12, "l4" -> 4, "l3" -> 3).foreach { case (t, n) =>
+      createTable(t, idCols, Array(bucket(n, "id")))
+      sql(s"INSERT INTO testcat.ns.$t SELECT id FROM range(24)")
+    }
+    createTable("lx", idCols, Array.empty)
+    sql("INSERT INTO testcat.ns.lx SELECT id FROM range(24)")
+
+    Seq(true, false).foreach { aqe =>
+      withSQLConf(
+        SQLConf.AUTO_BROADCASTJOIN_THRESHOLD.key -> "-1",
+        SQLConf.ADAPTIVE_EXECUTION_ENABLED.key -> aqe.toString,
+        SQLConf.V2_BUCKETING_ALLOW_COMPATIBLE_TRANSFORMS.key -> "true") {
+        // The broadcast join also reports the reduced l12's partitioning on 
lx.id.
+        val df = sql(
+          """
+            |SELECT /*+ MERGE(l12, l4), BROADCAST(lx), MERGE(l3) */ l12.id, 
lx.id, l3.id
+            |FROM testcat.ns.l12 JOIN testcat.ns.l4 ON l12.id = l4.id
+            |JOIN testcat.ns.lx ON l12.id = lx.id
+            |JOIN testcat.ns.l3 ON lx.id = l3.id
+            |""".stripMargin)
+        checkAnswer(df, (0L until 24L).map(i => Row(i, i, i)))
+      }
+    }
+  }
+
+  test("SPARK-60232: nested SPJ survives EnsureRequirements re-running for a 
skewed join") {
+    val idCols = Array(Column.create("id", LongType))
+    Seq("b4" -> 4, "b12" -> 12, "b6" -> 6).foreach { case (t, n) =>
+      createTable(t, idCols, Array(bucket(n, "id")))
+      sql(s"INSERT INTO testcat.ns.$t SELECT id FROM range(24)")
+    }
+
+    withTempView("skewData1", "skewData2") {
+      spark.range(0, 1000, 1, 10)
+        .selectExpr("id % 3 as key1", "id as value1")
+        .createOrReplaceTempView("skewData1")
+      spark.range(0, 1000, 1, 10)
+        .selectExpr("id % 1 as key2", "id as value2")
+        .createOrReplaceTempView("skewData2")
+      val skewQuery = "SELECT key1 AS k, count(*) AS c " +
+        "FROM skewData1 JOIN skewData2 ON key1 = key2 GROUP BY key1"
+      val expected = sql(skewQuery).collect().toSeq ++ (0L until 24L).map(i => 
Row(i, 1L))
+
+      // With forced skew join optimization, OptimizeSkewedJoin re-applies 
EnsureRequirements to
+      // the whole plan, including the nested SPJ in the other branch that is 
already planned.
+      withSQLConf(
+        SQLConf.ADAPTIVE_EXECUTION_ENABLED.key -> "true",
+        SQLConf.ADAPTIVE_OPTIMIZE_SKEWS_IN_REBALANCE_PARTITIONS_ENABLED.key -> 
"false",
+        SQLConf.AUTO_BROADCASTJOIN_THRESHOLD.key -> "-1",
+        SQLConf.SKEW_JOIN_SKEWED_PARTITION_THRESHOLD.key -> "100",
+        SQLConf.ADVISORY_PARTITION_SIZE_IN_BYTES.key -> "100",
+        SQLConf.COALESCE_PARTITIONS_MIN_PARTITION_NUM.key -> "1",
+        SQLConf.SHUFFLE_PARTITIONS.key -> "10",
+        SQLConf.ADAPTIVE_FORCE_OPTIMIZE_SKEWED_JOIN.key -> "true",
+        SQLConf.V2_BUCKETING_SHUFFLE_ENABLED.key -> "true",
+        SQLConf.V2_BUCKETING_ALLOW_COMPATIBLE_TRANSFORMS.key -> "true") {
+        val df = sql(
+          s"""
+             |$skewQuery
+             |UNION ALL
+             |SELECT /*+ MERGE(b4, b12, b6) */ b4.id AS k, 1L AS c
+             |FROM testcat.ns.b4 JOIN testcat.ns.b12 ON b4.id = b12.id
+             |JOIN testcat.ns.b6 ON b4.id = b6.id
+             |""".stripMargin)
+        checkAnswer(df, expected)
+        // Only the skewed branch shuffles: both sides of its join, and its 
aggregate.
+        assert(collectAllShuffles(df.queryExecution.executedPlan).size == 3,
+          "the re-run should keep the nested SPJ's pairing")
+      }
+    }
+  }
+
+  test("SPARK-60232: re-running EnsureRequirements does not apply a reducer 
twice") {
+    Seq(ReducibleHoursFunction, ReducibleDaysFunction, 
ReducibleYearsFunction).foreach { f =>
+      catalog.createFunction(Identifier.of(Array.empty, f.name()), new 
UnboundReducibleFunction(f))
+    }
+    val cols = Array(Column.create("id", LongType), Column.create("ts", 
TimestampType))
+    Seq("qh" -> hours("ts"), "qd" -> days("ts"), "qy" -> years("ts")).foreach 
{ case (t, p) =>
+      createTable(t, cols, Array(p))
+      sql(s"INSERT INTO testcat.ns.$t " +
+        "SELECT id, TIMESTAMP '2026-01-01 00:00:00' + id * INTERVAL '1' HOUR 
FROM range(72)")
+    }
+
+    withTempView("skewData1", "skewData2") {
+      spark.range(0, 1000, 1, 10)
+        .selectExpr("id % 3 as key1", "id as value1")
+        .createOrReplaceTempView("skewData1")
+      spark.range(0, 1000, 1, 10)
+        .selectExpr("id % 1 as key2", "id as value2")
+        .createOrReplaceTempView("skewData2")
+      val skewQuery = "SELECT key1 AS k, count(*) AS c " +
+        "FROM skewData1 JOIN skewData2 ON key1 = key2 GROUP BY key1"
+      val expected = sql(skewQuery).collect().toSeq ++ (0L until 72L).map(i => 
Row(i, 1L))
+
+      // The re-run's inner join must not derive the hours-to-days reducer 
again and chain it after
+      // the one it pushed the first time, since h / 24 applied twice is wrong.
+      withSQLConf(
+        SQLConf.ADAPTIVE_EXECUTION_ENABLED.key -> "true",
+        SQLConf.ADAPTIVE_OPTIMIZE_SKEWS_IN_REBALANCE_PARTITIONS_ENABLED.key -> 
"false",
+        SQLConf.AUTO_BROADCASTJOIN_THRESHOLD.key -> "-1",
+        SQLConf.SKEW_JOIN_SKEWED_PARTITION_THRESHOLD.key -> "100",
+        SQLConf.ADVISORY_PARTITION_SIZE_IN_BYTES.key -> "100",
+        SQLConf.COALESCE_PARTITIONS_MIN_PARTITION_NUM.key -> "1",
+        SQLConf.SHUFFLE_PARTITIONS.key -> "10",
+        SQLConf.ADAPTIVE_FORCE_OPTIMIZE_SKEWED_JOIN.key -> "true",
+        SQLConf.V2_BUCKETING_SHUFFLE_ENABLED.key -> "true",
+        SQLConf.V2_BUCKETING_ALLOW_COMPATIBLE_TRANSFORMS.key -> "true") {
+        val df = sql(
+          s"""
+             |$skewQuery
+             |UNION ALL
+             |SELECT /*+ MERGE(qh, qd, qy) */ qh.id AS k, 1L AS c
+             |FROM testcat.ns.qh JOIN testcat.ns.qd ON qh.ts = qd.ts
+             |JOIN testcat.ns.qy ON qd.ts = qy.ts
+             |""".stripMargin)
+        checkAnswer(df, expected)
+      }
+    }
+  }
+
+  test("SPARK-60232: re-running EnsureRequirements keeps a single SPJ's 
pairing") {
+    Seq(ReducibleHoursFunction, ReducibleDaysFunction, 
ReducibleYearsFunction).foreach { f =>
+      catalog.createFunction(Identifier.of(Array.empty, f.name()), new 
UnboundReducibleFunction(f))
+    }
+    val idCols = Array(Column.create("id", LongType))
+    Seq("b4" -> 4, "b12" -> 12).foreach { case (t, n) =>
+      createTable(t, idCols, Array(bucket(n, "id")))
+      sql(s"INSERT INTO testcat.ns.$t SELECT id FROM range(24)")
+    }
+    val tsCols = Array(Column.create("id", LongType), Column.create("ts", 
TimestampType))
+    Seq("qh" -> hours("ts"), "qd" -> days("ts")).foreach { case (t, p) =>
+      createTable(t, tsCols, Array(p))
+      sql(s"INSERT INTO testcat.ns.$t " +
+        "SELECT id, TIMESTAMP '2026-01-01 00:00:00' + id * INTERVAL '1' HOUR 
FROM range(72)")
+    }
+
+    withTempView("skewData1", "skewData2") {
+      spark.range(0, 1000, 1, 10)
+        .selectExpr("id % 3 as key1", "id as value1")
+        .createOrReplaceTempView("skewData1")
+      spark.range(0, 1000, 1, 10)
+        .selectExpr("id % 1 as key2", "id as value2")
+        .createOrReplaceTempView("skewData2")
+      val skewQuery = "SELECT key1 AS k, count(*) AS c " +
+        "FROM skewData1 JOIN skewData2 ON key1 = key2 GROUP BY key1"
+      val skewRows = sql(skewQuery).collect().toSeq
+
+      // The re-run finds this join's own scans directly below it, still 
carrying the reducer the
+      // first run pushed. With v2 bucketing shuffle off, as by default, 
refusing that pairing
+      // would leave no spec to shuffle onto, so both sides would get a hash 
shuffle.
+      withSQLConf(
+        SQLConf.ADAPTIVE_EXECUTION_ENABLED.key -> "true",
+        SQLConf.ADAPTIVE_OPTIMIZE_SKEWS_IN_REBALANCE_PARTITIONS_ENABLED.key -> 
"false",
+        SQLConf.AUTO_BROADCASTJOIN_THRESHOLD.key -> "-1",
+        SQLConf.SKEW_JOIN_SKEWED_PARTITION_THRESHOLD.key -> "100",
+        SQLConf.ADVISORY_PARTITION_SIZE_IN_BYTES.key -> "100",
+        SQLConf.COALESCE_PARTITIONS_MIN_PARTITION_NUM.key -> "1",
+        SQLConf.SHUFFLE_PARTITIONS.key -> "10",
+        SQLConf.ADAPTIVE_FORCE_OPTIMIZE_SKEWED_JOIN.key -> "true",
+        SQLConf.V2_BUCKETING_SHUFFLE_ENABLED.key -> "false",
+        SQLConf.V2_BUCKETING_ALLOW_COMPATIBLE_TRANSFORMS.key -> "true") {
+        Seq(
+          ("b4", "b12", "id", 24),
+          ("qh", "qd", "ts", 72)).foreach { case (t1, t2, key, numRows) =>
+          val df = sql(
+            s"""
+               |$skewQuery
+               |UNION ALL
+               |SELECT /*+ MERGE($t1, $t2) */ $t1.id AS k, 1L AS c
+               |FROM testcat.ns.$t1 JOIN testcat.ns.$t2 ON $t1.$key = $t2.$key
+               |""".stripMargin)
+          checkAnswer(df, skewRows ++ (0L until numRows.toLong).map(i => 
Row(i, 1L)))
+          // Only the skewed branch shuffles: both sides of its join, and its 
aggregate.
+          assert(collectAllShuffles(df.queryExecution.executedPlan).size == 3,
+            s"$t1 JOIN $t2 should keep SPJ when EnsureRequirements re-runs")
+        }
+      }
+    }
+  }
+
+  test("SPARK-60232: nested SPJ falls back when a broadcast build key cannot 
be traced") {
+    val longCols = Array(Column.create("id", LongType))
+    val intCols = Array(Column.create("id", IntegerType))
+    Seq("l12" -> 12, "l4" -> 4, "l3" -> 3).foreach { case (t, n) =>
+      createTable(t, longCols, Array(bucket(n, "id")))
+    }
+    Seq("lx", "ly").foreach(t => createTable(t, longCols, Array.empty))
+    createTable("lxi", intCols, Array.empty)
+    createTable("l3i", intCols, Array(bucket(3, "id")))
+    Seq("l12", "l4", "l3", "lx", "ly", "lxi", "l3i").foreach { t =>
+      sql(s"INSERT INTO testcat.ns.$t SELECT id FROM range(24)")
+    }
+
+    // Each query reaches the reduced l12 through a broadcast build key that 
maps back to no
+    // single unreduced position, so the outer join has to shuffle.
+    val longRows = (0L until 24L).map(i => Row(i, i, i))
+    val queries = Seq(
+      // A build key equated to two streamed keys.
+      """
+        |SELECT /*+ MERGE(l12, l4), BROADCAST(lx), MERGE(l3) */ l12.id, lx.id, 
l3.id
+        |FROM testcat.ns.l12 JOIN testcat.ns.l4 ON l12.id = l4.id
+        |JOIN testcat.ns.lx ON lx.id = l12.id AND lx.id = l4.id
+        |JOIN testcat.ns.l3 ON lx.id = l3.id
+        |""".stripMargin -> longRows,
+      // A cast build key.
+      """
+        |SELECT /*+ MERGE(l12, l4), BROADCAST(lxi), MERGE(l3i) */ l12.id, 
lxi.id, l3i.id
+        |FROM testcat.ns.l12 JOIN testcat.ns.l4 ON l12.id = l4.id
+        |JOIN testcat.ns.lxi ON l12.id = lxi.id
+        |JOIN testcat.ns.l3i ON lxi.id = l3i.id
+        |""".stripMargin -> (0 until 24).map(i => Row(i.toLong, i, i)),
+      // A build key that a broadcast inside the build side also uses.
+      """
+        |SELECT /*+ MERGE(l12, l4), BROADCAST(b), MERGE(l3) */ l12.id, b.id, 
l3.id
+        |FROM testcat.ns.l12 JOIN testcat.ns.l4 ON l12.id = l4.id
+        |JOIN (
+        |  SELECT /*+ BROADCAST(ly) */ lx.id AS xid, ly.id
+        |  FROM testcat.ns.lx JOIN testcat.ns.ly ON lx.id = ly.id) b ON l12.id 
= b.id
+        |JOIN testcat.ns.l3 ON b.id = l3.id
+        |""".stripMargin -> longRows)
+
+    Seq(true, false).foreach { aqe =>
+      withSQLConf(
+        SQLConf.AUTO_BROADCASTJOIN_THRESHOLD.key -> "-1",
+        SQLConf.ADAPTIVE_EXECUTION_ENABLED.key -> aqe.toString,
+        SQLConf.V2_BUCKETING_ALLOW_COMPATIBLE_TRANSFORMS.key -> "true") {
+        queries.foreach { case (query, expected) =>
+          val df = sql(query)
+          assert(collectAllShuffles(df.queryExecution.executedPlan).size == 2,
+            "the outer join should fall back to shuffling both sides")
+          checkAnswer(df, expected)
+        }
+      }
+    }
+
+    // pa is reduced at b only. lx.id is equated to both pa.a and pa.b, so 
whichever equality
+    // comes last, the build key has no single position to trace back to.
+    val abCols = Array(Column.create("a", LongType), Column.create("b", 
LongType))
+    createTable("pa", abCols, Array(bucket(12, "a"), bucket(12, "b")))
+    createTable("pb", abCols, Array(bucket(12, "a"), bucket(4, "b")))
+    createTable("q12", longCols, Array(bucket(12, "id")))
+    Seq("pa", "pb").foreach(t => sql(s"INSERT INTO testcat.ns.$t SELECT id, id 
FROM range(24)"))
+    sql("INSERT INTO testcat.ns.q12 SELECT id FROM range(24)")
+    Seq("lx.id = pa.b AND lx.id = pa.a", "lx.id = pa.a AND lx.id = 
pa.b").foreach { on =>
+      Seq(true, false).foreach { aqe =>
+        withSQLConf(
+          SQLConf.AUTO_BROADCASTJOIN_THRESHOLD.key -> "-1",
+          SQLConf.ADAPTIVE_EXECUTION_ENABLED.key -> aqe.toString,
+          SQLConf.V2_BUCKETING_ALLOW_COMPATIBLE_TRANSFORMS.key -> "true",
+          SQLConf.V2_BUCKETING_ALLOW_JOIN_KEYS_SUBSET_OF_PARTITION_KEYS.key -> 
"true",
+          SQLConf.REQUIRE_ALL_CLUSTER_KEYS_FOR_CO_PARTITION.key -> "false") {
+          val df = sql(
+            s"""
+               |SELECT /*+ MERGE(pa, pb), BROADCAST(lx), MERGE(q12) */ pa.a, 
lx.id, q12.id
+               |FROM testcat.ns.pa JOIN testcat.ns.pb ON pa.a = pb.a AND pa.b 
= pb.b
+               |JOIN testcat.ns.lx ON $on
+               |JOIN testcat.ns.q12 ON lx.id = q12.id
+               |""".stripMargin)
+          assert(collectAllShuffles(df.queryExecution.executedPlan).size == 2,
+            s"the outer join should fall back to shuffling both sides with ON 
$on")
+          checkAnswer(df, longRows)
+        }
+      }
+    }
+  }
+
+  test("SPARK-60232: nested SPJ keeps SPJ on an unreduced column with the same 
transform") {
+    val cols = Array(Column.create("a", LongType), Column.create("b", 
LongType))
+    createTable("pa", cols, Array(bucket(12, "a"), bucket(12, "b")))
+    createTable("pb", cols, Array(bucket(4, "a"), bucket(12, "b")))
+    createTable("pc", cols, Array(bucket(12, "b")))
+    Seq("pa", "pb", "pc").foreach { t =>
+      sql(s"INSERT INTO testcat.ns.$t SELECT id, id FROM range(24)")
+    }
+
+    Seq(true, false).foreach { aqe =>
+      withSQLConf(
+        SQLConf.AUTO_BROADCASTJOIN_THRESHOLD.key -> "-1",
+        SQLConf.ADAPTIVE_EXECUTION_ENABLED.key -> aqe.toString,
+        SQLConf.V2_BUCKETING_ALLOW_COMPATIBLE_TRANSFORMS.key -> "true",
+        SQLConf.V2_BUCKETING_ALLOW_JOIN_KEYS_SUBSET_OF_PARTITION_KEYS.key -> 
"true",
+        SQLConf.REQUIRE_ALL_CLUSTER_KEYS_FOR_CO_PARTITION.key -> "false") {
+        // The inner join reduces pa at a only, so bucket(12, b) still holds 
pa's own values.
+        val df = sql(
+          """
+            |SELECT /*+ MERGE(pa, pb, pc) */ pa.a, pc.b
+            |FROM testcat.ns.pa JOIN testcat.ns.pb ON pa.a = pb.a AND pa.b = 
pb.b
+            |JOIN testcat.ns.pc ON pa.b = pc.b
+            |""".stripMargin)
+        assert(collectShuffles(df.queryExecution.executedPlan).isEmpty,
+          "SPJ should be triggered for both joins")
+        checkAnswer(df, (0L until 24L).map(i => Row(i, i)))
+      }
+    }
+  }
+
+  test("SPARK-60232: ordering a subset-key SPJ range-shuffles when a one-side 
shuffle is below") {
+    val cols = Array(
+      Column.create("store", IntegerType),
+      Column.create("dept", IntegerType),
+      Column.create("data", StringType))
+    createTable("sa", cols, Array(identity("store"), identity("dept")))
+    // The dept keys come out as 20, 10, so ordering by dept reorders them.
+    sql("INSERT INTO testcat.ns.sa VALUES (1, 20, 'a1'), (2, 10, 'a2')")
+
+    createTable("sb", cols, Array.empty)
+    sql("INSERT INTO testcat.ns.sb VALUES (0, 10, 'b10'), (0, 20, 'b20')")
+
+    Seq(true, false).foreach { aqe =>
+      withSQLConf(
+        SQLConf.AUTO_BROADCASTJOIN_THRESHOLD.key -> "-1",
+        SQLConf.ADAPTIVE_EXECUTION_ENABLED.key -> aqe.toString,
+        SQLConf.V2_BUCKETING_SHUFFLE_ENABLED.key -> "true",
+        SQLConf.V2_BUCKETING_SORTING_ENABLED.key -> "true",
+        SQLConf.V2_BUCKETING_ALLOW_JOIN_KEYS_SUBSET_OF_PARTITION_KEYS.key -> 
"true",
+        SQLConf.REQUIRE_ALL_CLUSTER_KEYS_FOR_CO_PARTITION.key -> "false") {
+        Seq("LEFT JOIN", "JOIN").foreach { joinType =>
+          val df = sql(
+            s"""
+               |SELECT /*+ MERGE(sa, sb) */ sa.dept, sa.data, sb.data, sa.store
+               |FROM testcat.ns.sa $joinType testcat.ns.sb ON sa.dept = sb.dept
+               |ORDER BY sa.dept
+               |""".stripMargin)
+          checkAnswer(df, Seq(Row(10, "a2", "b10", 2), Row(20, "a1", "b20", 
1)))
+        }
+      }
+    }
+  }
+
+  test("SPARK-60232: ordering ignores a one-side shuffle inside a broadcast 
build side") {
+    val deptCol = Array(Column.create("dept", IntegerType))
+    Seq("sa", "ba").foreach { t =>
+      createTable(t, deptCol, Array(identity("dept")))
+      sql(s"INSERT INTO testcat.ns.$t VALUES (1), (2), (3)")
+    }
+    createTable("bb", Array(Column.create("dept", IntegerType), 
Column.create("v", StringType)),
+      Array.empty)
+    sql("INSERT INTO testcat.ns.bb VALUES (1, 'v1'), (2, 'v2'), (3, 'v3')")
+
+    Seq(true, false).foreach { aqe =>
+      withSQLConf(
+        SQLConf.AUTO_BROADCASTJOIN_THRESHOLD.key -> "-1",
+        SQLConf.ADAPTIVE_EXECUTION_ENABLED.key -> aqe.toString,
+        SQLConf.V2_BUCKETING_SHUFFLE_ENABLED.key -> "true",
+        SQLConf.V2_BUCKETING_SORTING_ENABLED.key -> "true") {
+        // The sorted push stops at the broadcast, so bb's shuffle onto ba's 
layout is not left
+        // behind, and the DESC reorder needs no range shuffle.
+        val df = sql(
+          """
+            |SELECT /*+ BROADCAST(b) */ sa.dept, b.v
+            |FROM testcat.ns.sa LEFT JOIN (
+            |  SELECT /*+ MERGE(ba, bb) */ ba.dept, bb.v
+            |  FROM testcat.ns.ba JOIN testcat.ns.bb ON ba.dept = bb.dept) b
+            |ON sa.dept = b.dept
+            |ORDER BY sa.dept DESC
+            |""".stripMargin)
+        checkAnswer(df, Seq(Row(3, "v3"), Row(2, "v2"), Row(1, "v1")))
+        assert(collectAllShuffles(df.queryExecution.executedPlan).size == 1,
+          "only bb should be shuffled")
+      }
+    }
+  }
+
+  test("SPARK-60552: SPJ does not push common values below a shuffle") {
+    createTable("sa7", Array(Column.create("dept", IntegerType)), 
Array(identity("dept")))
+    sql("INSERT INTO testcat.ns.sa7 VALUES (1), (2), (3)")
+
+    createTable("sb7", Array(Column.create("dept", IntegerType),
+      Column.create("region", IntegerType)), Array(identity("region")))
+    sql("INSERT INTO testcat.ns.sb7 VALUES (1, 10), (2, 20), (3, 30)")
+
+    createTable("pa", Array(Column.create("id", IntegerType)), 
Array(identity("id")))
+    sql("INSERT INTO testcat.ns.pa VALUES (1), (2), (3)")
+
+    createTable("pb", Array(Column.create("id", IntegerType),
+      Column.create("region", IntegerType)), Array(identity("region")))
+    sql("INSERT INTO testcat.ns.pb VALUES (1, 10), (2, 20), (3, 30)")
+
+    createTable("pc", Array(Column.create("id", IntegerType)), 
Array(identity("id")))
+    sql("INSERT INTO testcat.ns.pc VALUES (1), (2), (3)")
+
+    Seq(true, false).foreach { aqe =>
+      withSQLConf(
+        SQLConf.AUTO_BROADCASTJOIN_THRESHOLD.key -> "-1",
+        SQLConf.ADAPTIVE_EXECUTION_ENABLED.key -> aqe.toString,
+        SQLConf.V2_BUCKETING_SHUFFLE_ENABLED.key -> "true",
+        SQLConf.V2_BUCKETING_SORTING_ENABLED.key -> "true") {
+        // sb7 is shuffled onto sa7's layout, and ordering then pushes sa7's 
values.
+        val orderedDf = sql(
+          """
+            |SELECT /*+ MERGE(sa7, sb7) */ sa7.dept, sb7.region
+            |FROM testcat.ns.sa7 LEFT JOIN testcat.ns.sb7 ON sa7.dept = 
sb7.dept
+            |ORDER BY sa7.dept
+            |""".stripMargin)
+        checkAnswer(orderedDf, Seq(Row(1, 10), Row(2, 20), Row(3, 30)))
+
+        // pb is shuffled for the inner join, and the outer join then pushes 
its values.
+        val nestedDf = sql(
+          """
+            |SELECT /*+ MERGE(pa, pb, pc) */ pa.id, pb.region, pc.id
+            |FROM testcat.ns.pa JOIN testcat.ns.pb ON pa.id = pb.id
+            |JOIN testcat.ns.pc ON pa.id = pc.id
+            |""".stripMargin)
+        checkAnswer(nestedDf, Seq(Row(1, 10, 1), Row(2, 20, 2), Row(3, 30, 3)))
+      }
+    }
+  }
+
   test("SPARK-48012: one-side shuffle with partition transforms") {
     val items_partitions = Array(bucket(2, "id"), identity("arrive_time"))
     val items_partitions2 = Array(identity("arrive_time"), bucket(2, "id"))
diff --git 
a/sql/core/src/test/scala/org/apache/spark/sql/connector/catalog/functions/transformFunctions.scala
 
b/sql/core/src/test/scala/org/apache/spark/sql/connector/catalog/functions/transformFunctions.scala
index 1aab39ab378f..351c95ca6bc8 100644
--- 
a/sql/core/src/test/scala/org/apache/spark/sql/connector/catalog/functions/transformFunctions.scala
+++ 
b/sql/core/src/test/scala/org/apache/spark/sql/connector/catalog/functions/transformFunctions.scala
@@ -177,3 +177,53 @@ object TruncateFunction extends ScalarFunction[UTF8String] 
{
     str.substring(0, length)
   }
 }
+
+// Reducible `hours`, `days` and `years`, matching the in-memory table's 
transforms of those
+// names. Hours reduce onto days with h / 24, and days onto years. Unlike 
bucket's x % n,
+// neither reducer is idempotent, so applying one twice gives a wrong value.
+object ReducibleHoursFunction extends ScalarFunction[Long] with 
ReducibleFunction[Long, Long] {
+  override def inputTypes(): Array[DataType] = Array(TimestampType)
+  override def resultType(): DataType = LongType
+  override def name(): String = "hours"
+  override def canonicalName(): String = name()
+  override def produceResult(input: InternalRow): Long =
+    ChronoUnit.HOURS.between(Instant.EPOCH, 
DateTimeUtils.microsToInstant(input.getLong(0)))
+  override def reducer(other: ReducibleFunction[_, _]): Reducer[Long, Long] =
+    if (other eq ReducibleDaysFunction) HoursToDaysReducer else null
+}
+
+object ReducibleDaysFunction extends ScalarFunction[Long] with 
ReducibleFunction[Long, Long] {
+  override def inputTypes(): Array[DataType] = Array(TimestampType)
+  override def resultType(): DataType = LongType
+  override def name(): String = "days"
+  override def canonicalName(): String = name()
+  override def produceResult(input: InternalRow): Long =
+    ChronoUnit.DAYS.between(Instant.EPOCH, 
DateTimeUtils.microsToInstant(input.getLong(0)))
+  override def reducer(other: ReducibleFunction[_, _]): Reducer[Long, Long] =
+    if (other eq ReducibleYearsFunction) DaysToYearsReducer else null
+}
+
+object ReducibleYearsFunction extends ScalarFunction[Long] with 
ReducibleFunction[Long, Long] {
+  override def inputTypes(): Array[DataType] = Array(TimestampType)
+  override def resultType(): DataType = LongType
+  override def name(): String = "years"
+  override def canonicalName(): String = name()
+  override def produceResult(input: InternalRow): Long =
+    YearsFunction.invoke(input.getLong(0))
+  override def reducer(other: ReducibleFunction[_, _]): Reducer[Long, Long] = 
null
+}
+
+case object HoursToDaysReducer extends Reducer[Long, Long] {
+  override def reduce(hours: Long): Long = hours / 24
+}
+
+case object DaysToYearsReducer extends Reducer[Long, Long] {
+  override def reduce(days: Long): Long =
+    ChronoUnit.YEARS.between(YearsFunction.EPOCH_LOCAL_DATE, 
LocalDate.ofEpochDay(days))
+}
+
+class UnboundReducibleFunction(fn: BoundFunction) extends UnboundFunction {
+  override def bind(inputType: StructType): BoundFunction = fn
+  override def description(): String = name()
+  override def name(): String = fn.name()
+}


---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]

Reply via email to