Repository: pingcap/tidb
File: pkg/planner/core/rule_join_reorder.go (Go)

Code before:
	s.otherConds = rem
	return currentJoin, true
}

// collectNullExtendedCols returns a schema containing all columns that are on
// the null-extended side of an outer join in the current join group.
// For LeftOuterJoin, the right-side columns are null-extended.
// For RightOuterJoin, the left-side columns are null-extended.
func (s *baseSingleGroupJoinOrderSolver) collectNullExtendedCols() *expression.Schema {
	cols := make([]*expression.Column, 0)
	for idx, edge := range s.eqEdges {
		jt := s.joinTypes[idx]
		lCol, rCol := expression.ExtractColumnsFromColOpCol(edge)
		switch jt.JoinType {
		case base.LeftOuterJoin:
			// Right side is null-extended
			if rCol != nil {
				cols = append(cols, rCol)
			}
		case base.RightOuterJoin:
			// Left side is null-extended
			if lCol != nil {
				cols = append(cols, lCol)
			}
		}
	}
	if len(cols) == 0 {
		return nil
	}
	return expression.NewSchema(cols...)
}

// generateJoinOrderNode used to derive the stats for the joinNodePlans and generate the jrNode groups based on the cost.
func (s *baseSingleGroupJoinOrderSolver) generateJoinOrderNode(joinNodePlans []base.LogicalPlan) ([]*jrNode, error) {
	joinGroup := make([]*jrNode, 0, len(joinNodePlans))
	for _, node := range joinNodePlans {
		_, _, err := node.RecursiveDeriveStats(nil)
		if err != nil {
			return nil, err
		}
		cost := s.baseNodeCumCost(node)
		joinGroup = append(joinGroup, &jrNode{
			p:       node,
			cumCost: cost,
		})
	}
	return joinGroup, nil
}

// baseNodeCumCost calculate the cumulative cost of the node in the join group.
func (s *baseSingleGroupJoinOrderSolver) baseNodeCumCost(groupNode base.LogicalPlan) float64 {

Review comment (bug):
collectNullExtendedCols() only adds the join-key column from the null-extended side (lCol/rCol from each EQ edge). That means hasOtherJoinCondition() may incorrectly treat a condition that references a different column from the null-extended side as “safe”, allowing a reordering that can change outer-join semantics. Consider collecting *all* columns from the null-extended side plan(s) (e.g., find the leaf plan containing rCol/lCol and append its full schema), or otherwise track null-extended relations rather than just the EQ columns.
```suggestion
	// We start from the join-key columns on the null-extended side of each outer join,
	// then extend to any other columns from the same underlying relations that appear
	// in s.otherConds. This ensures hasOtherJoinCondition() can correctly detect when
	// a non-equality condition references a null-extended column.
	type tableKey struct {
		db  string
		tbl string
	}

	cols := make([]*expression.Column, 0)
	colIDs := make(map[int64]struct{})
	nullExtendedTables := make(map[tableKey]struct{})

	// Seed nullExtendedTables and cols from the outer-join equality edges.
	for idx, edge := range s.eqEdges {
		jt := s.joinTypes[idx]
		lCol, rCol := expression.ExtractColumnsFromColOpCol(edge)
		switch jt.JoinType {
		case base.LeftOuterJoin:
			// Right side is null-extended.
			if rCol != nil {
				if _, exists := colIDs[rCol.UniqueID]; !exists {
					cols = append(cols, rCol)
					colIDs[rCol.UniqueID] = struct{}{}
				}
				key := tableKey{
					db:  rCol.DBName.L,
					tbl: rCol.OrigTblName.L,
				}
				nullExtendedTables[key] = struct{}{}
			}
		case base.RightOuterJoin:
			// Left side is null-extended.
			if lCol != nil {
				if _, exists := colIDs[lCol.UniqueID]; !exists {
					cols = append(cols, lCol)
					colIDs[lCol.UniqueID] = struct{}{}
				}
				key := tableKey{
					db:  lCol.DBName.L,
					tbl: lCol.OrigTblName.L,
				}
				nullExtendedTables[key] = struct{}{}
			}
		}
	}

	// If we have identified any null-extended tables, collect all columns from those
	// tables that appear in other join conditions. This over-approximates the set of
	// null-extended columns visible to hasOtherJoinCondition().
	if len(nullExtendedTables) > 0 {
		for _, cond := range s.otherConds {
			for _, c := range expression.ExtractColumns(cond) {
				key := tableKey{
					db:  c.DBName.L,
					tbl: c.OrigTblName.L,
				}
				if _, isNullExtendedTable := nullExtendedTables[key]; !isNullExtendedTable {
					continue
				}
				if _, exists := colIDs[c.UniqueID]; exists {
					continue
				}
				cols = append(cols, c)
				colIDs[c.UniqueID] = struct{}{}
			}
		}
	}
```

Code after:

	leadingJoin, remainingGroup, ok, err := joinorder.BuildLeadingTreeFromList(leadingList, leftJoinGroup, find, joiner, warn)
	if err != nil {
		return false, nil, err
	}
	if !ok {
		return false, nil, nil
	}
	s.leadingJoinGroup = leadingJoin
	return true, remainingGroup, nil
}

// connectJoinNodes handles joining two subplans, performing connection checks
// and enforcing the outer join constraint.
// This function is only used by the leading hint processing path.
func (s *baseSingleGroupJoinOrderSolver) connectJoinNodes(
	currentJoin, nextNode base.LogicalPlan,
	hasOuterJoin bool,
) (base.LogicalPlan, bool) {
	lNode, rNode, usedEdges, joinType := s.checkConnection(currentJoin, nextNode)
	if hasOuterJoin && len(usedEdges) == 0 {
		// If the joinGroups contain an outer join, we disable cartesian product.
		// For non-equality conditions, only allow them when they do not reference
		// null-extended columns from any outer join in the current group.
		if !s.hasOtherJoinCondition(lNode, rNode) {
			return nil, false
		}
	}
	var rem []expression.Expression
	currentJoin, rem = s.makeJoin(lNode, rNode, usedEdges, joinType)
	s.otherConds = rem
	return currentJoin, true
}

// generateJoinOrderNode used to derive the stats for the joinNodePlans and generate the jrNode groups based on the cost.
func (s *baseSingleGroupJoinOrderSolver) generateJoinOrderNode(joinNodePlans []base.LogicalPlan) ([]*jrNode, error) {
	joinGroup := make([]*jrNode, 0, len(joinNodePlans))
	for _, node := range joinNodePlans {
		_, _, err := node.RecursiveDeriveStats(nil)
		if err != nil {
			return nil, err
		}
		cost := s.baseNodeCumCost(node)
		joinGroup = append(joinGroup, &jrNode{
			p:       node,
			cumCost: cost,
		})
	}
	return joinGroup, nil
}