unlock unlimited code runs for $79, limited time →
NOSSLETTER.techLIVE
back to OSSpedia
pingcap/tidb
senior8/10
query-optimizationdistributed-systemsconsistency-models

planner: support TiFlash MPP full outer join

Project: pingcap/tidb | Language: Go | Concepts: query-optimization, distributed-systems, consistency-models | Difficulty: senior
Link: https://github.com/pingcap/tidb/pull/70562

The problem

TiDB had initial support for FULL OUTER JOIN in its root (single-node) hash join executor, but the distributed (MPP, or Massively Parallel Processing) path using TiFlash did not support full outer joins. This meant distributed queries using FULL OUTER JOIN could not be planned or executed efficiently on TiFlash, limiting performance and scalability for such queries.

Why it's non-trivial

A naive fix would just allow the planner to emit MPP plans for full outer joins, but this would miss several subtleties:

  • MPP supports multiple join algorithms (shuffle join, broadcast join), but not all are compatible with full outer join semantics.
  • Partitioning and property propagation are different for full outer joins, since both sides are preserved (unlike left/right joins).
  • The planner must serialize the join type correctly for the TiFlash executor.
  • Side conditions (filters on left/right tables) require careful validation.
  • Planner warnings and test coverage must be updated to reflect the new support.

The approach

1. Update planner warnings and validation for join side conditions and join types

-          "MPP mode may be blocked because there is a join that is not `left join` but has left conditions, which is not supported by mpp now, see github.com/pingcap/tidb/issues/26090 for more information.",
+          "MPP mode may be blocked because left conditions are only supported for `left outer join` and `full outer join` now.",

This changes the warning message to reflect that full outer joins (not just left outer joins) now support left side conditions in MPP.

2. Prevent MPP broadcast join for full outer join

+	if p.JoinType == base.FullOuterJoin && useBCJ {
+		p.SCtx().GetSessionVars().RaiseWarningWhenMPPEnforced("MPP mode may be blocked because `full outer join` is only supported by shuffle join now.")
+		return nil
+	}

This ensures that only shuffle joins (not broadcast joins) can be used for full outer joins in MPP, as broadcast semantics do not preserve both sides.

3. Allow left/right conditions for full outer join in MPP

-	if len(p.LeftConditions) != 0 && p.JoinType != base.LeftOuterJoin {
-		p.SCtx().GetSessionVars().RaiseWarningWhenMPPEnforced("MPP mode may be blocked because there is a join that is not `left join` but has left conditions, which is not supported by mpp now, see github.com/pingcap/tidb/issues/26090 for more information.")
+	if len(p.LeftConditions) != 0 && p.JoinType != base.LeftOuterJoin && p.JoinType != base.FullOuterJoin {
+		p.SCtx().GetSessionVars().RaiseWarningWhenMPPEnforced("MPP mode may be blocked because left conditions are only supported for `left outer join` and `full outer join` now.")
 		return nil
 	}
-	if len(p.RightConditions) != 0 && p.JoinType != base.RightOuterJoin {
-		p.SCtx().GetSessionVars().RaiseWarningWhenMPPEnforced("MPP mode may be blocked because there is a join that is not `right join` but has right conditions, which is not supported by mpp now.")
+	if len(p.RightConditions) != 0 && p.JoinType != base.RightOuterJoin && p.JoinType != base.FullOuterJoin {
+		p.SCtx().GetSessionVars().RaiseWarningWhenMPPEnforced("MPP mode may be blocked because right conditions are only supported for `right outer join` and `full outer join` now.")
 		return nil
 	}

This allows both left and right conditions for full outer joins, matching the semantics.

4. MPP task property propagation for full outer join

-	// for outer join, it should always be the outer side of the join
+	// for left/right outer join, it should always be the outer side of the join
 	// for semi join, it should be the left side(the same as left out join)
-	outerTaskIndex := 1 - p.InnerChildIdx
-	if p.JoinType != base.InnerJoin {
-		if p.JoinType == base.RightOuterJoin {
-			outerTaskIndex = 1
-		} else {
-			outerTaskIndex = 0
-		}
-	}
-	// can not use the task from tasks because it maybe updated.
-	outerTask := lTask
-	if outerTaskIndex == 1 {
-		outerTask = rTask
-	}
-	task := physicalop.NewMppTask(p,
-		outerTask.GetPartitionType(),
-		outerTask.GetHashCols(),
-		nil, rTask.GetWarnings(), lTask.GetWarnings())
+	// for full outer join, both sides are preserved, so neither side's partition property
+	// can be passed to the join result. Use AnyType instead.
+	partTp := property.AnyType
+	var hashCols []*property.MPPPartitionColumn
+	if p.JoinType != base.FullOuterJoin {
+		outerTaskIndex := 1 - p.InnerChildIdx
+		if p.JoinType != base.InnerJoin {
+			if p.JoinType == base.RightOuterJoin {
+				outerTaskIndex = 1
+			} else {
+				outerTaskIndex = 0
+			}
+		}
+		// can not use the task from tasks because it maybe updated.
+		outerTask := lTask
+		if outerTaskIndex == 1 {
+			outerTask = rTask
+		}
+		partTp = outerTask.GetPartitionType()
+		hashCols = outerTask.GetHashCols()
+	}
+	task := physicalop.NewMppTask(p,
+		partTp,
+		hashCols,
+		nil, rTask.GetWarnings(), lTask.GetWarnings())

For full outer joins, the resulting partitioning is unconstrained (AnyType), because both sides must be preserved.

5. Serialize full outer join type for TiFlash

+	case base.FullOuterJoin:
+		pbJoinType = tipb.JoinType_TypeFullOuterJoin

When building the protobuf for TiFlash, serialize the join type as TypeFullOuterJoin so the executor knows to preserve both sides.

6. Update planner enumeration and test coverage

  • The planner now generates MPP full outer join plans where appropriate, and test cases are added to verify correct planning, warning messages, and serialization.

From the review thread

No notable review discussion on this PR.

Takeaways

  • Distributed query planners must validate algorithm compatibility with SQL semantics. Not all distributed join algorithms (e.g., broadcast join) are compatible with all join types (e.g., full outer join).
  • Partitioning and property propagation must be carefully handled for outer joins. For full outer joins, neither input's partitioning can be assumed for the output, so the planner must not propagate partition properties.
  • Planner warnings and user feedback should be kept accurate as features evolve. When support is expanded (e.g., for full outer join), update warnings and test coverage to match new capabilities.