github.com/cockroachdb/cockroach@v20.2.0-alpha.1+incompatible/pkg/sql/opt/norm/rules/join.opt

# =============================================================================
# join.opt contains normalization rules for Join operators.
# =============================================================================

# CommuteRightJoin converts a RightJoin to a LeftJoin with the left and right
# inputs swapped. This allows other normalization rules to only worry about the
# LeftJoin case.
[CommuteRightJoin, Normalize, HighPriority]
(RightJoin $left:* $right:* $on:* $private:*)
=>
(LeftJoin $right $left $on (CommuteJoinFlags $private))

# SimplifyJoinFilters works like SimplifySelectFilters, except that it operates
# on Join filters rather than Select filters.
[SimplifyJoinFilters, Normalize, HighPriority]
(Join
    $left:*
    $right:*
    $on:[
            ...
            $item:(FiltersItem
                    (And | True | False | Null | Or)
                ) &
                ^(IsUnsimplifiableOr $item)
            ...
        ] &
        ^(IsFilterFalse $on)
    $private:*
)
=>
((OpName) $left $right (SimplifyFilters $on) $private)

# DetectJoinContradiction replaces a Join condition with False if it detects a
# contradiction in the filter.
[DetectJoinContradiction, Normalize]
(Join
    $left:*
    $right:*
    [ ... $item:(FiltersItem) & (IsContradiction $item) ... ]
    $private:*
)
=>
((OpName) $left $right [ (FiltersItem (False)) ] $private)

# PushFilterIntoJoinLeftAndRight pushes a filter into both the left and right
# sides of an InnerJoin or SemiJoin if it can be mapped to use the columns of
# both sides. For example, consider this query:
#
#   SELECT * FROM a JOIN b ON a.x=b.x AND a.y=b.y AND a.x + b.y < 5
#
# In this case, we can map a.x + b.y < 5 to both sides based on the equality
# filters a.x=b.x AND a.y=b.y. For the left side, we can map it to
# a.x + a.y < 5, and for the right side, we can map it to b.x + b.y < 5.
# Given this mapping, we can safely push the filter down to both sides and
# remove it from the ON filters list.
#
# Note that this rule is only applied when the left and right inputs do not have
# outer columns. If they do, then this rule can cause undetectable cycles with
# TryDecorrelateSelect, since the filter is pushed down to both sides, but then
# only pulled up from the right side by TryDecorrelateSelect. For this reason,
# the rule also does not apply to InnerJoinApply or SemiJoinApply.
#
# NOTE: It is important that this rule is first among the join filter push-down
#       rules.
[PushFilterIntoJoinLeftAndRight, Normalize]
(InnerJoin | SemiJoin
    $left:* & ^(HasOuterCols $left)
    $right:* & ^(HasOuterCols $right)
    $on:[
        ...
        $item:* &
            ^(FiltersItem (Eq (Variable) (Variable))) &
            (CanMapJoinOpFilter
                $item
                $leftCols:(OutputCols $left)
                $equivFD:(GetEquivFD $on $left $right)
            ) &
            (CanMapJoinOpFilter
                $item
                $rightCols:(OutputCols $right)
                $equivFD
            )
        ...
    ]
    $private:*
)
=>
((OpName)
    (Select
        $left
        [
            (FiltersItem
                (MapJoinOpFilter $item $leftCols $equivFD)
            )
        ]
    )
    (Select
        $right
        [
            (FiltersItem
                (MapJoinOpFilter $item $rightCols $equivFD)
            )
        ]
    )
    (RemoveFiltersItem $on $item)
    $private
)

# MapFilterIntoJoinLeft maps a filter that is not bound by the left side of
# the join to use the columns from the left side. This will allow
# the filter to be pushed down by the PushFilterIntoJoinLeft rule.
# For example, consider this query:
#
#   SELECT * FROM a INNER JOIN b ON a.x = b.x AND b.x + a.y < 5
#
# In this case, we can map b.x + a.y < 5 to the left side by replacing b.x
# with the equivalent column a.x.
# NOTE: This rule only applies to cases where it is not possible or not safe
#       to map the filter to both sides. If it can be mapped to both sides, it
#       will be handled by PushFilterIntoJoinLeftAndRight (which must be
#       ordered above this rule). For performance reasons, this rule should
#       be ordered before PushFilterIntoJoinLeft (otherwise,
#       PushFilterIntoJoinLeft might need to be applied multiple times).
[MapFilterIntoJoinLeft, Normalize]
(InnerJoin | InnerJoinApply | SemiJoin | SemiJoinApply
    $left:* & ^(HasOuterCols $left)
    $right:*
    $on:[
        ...
        $item:* &
            ^(FiltersItem (Eq (Variable) (Variable))) &
            ^(IsBoundBy $item $leftCols:(OutputCols $left)) &
            (CanMapJoinOpFilter
                $item
                $leftCols
                $equivFD:(GetEquivFD $on $left $right)
            )
        ...
    ]
    $private:*
)
=>
((OpName)
    $left
    $right
    (ReplaceFiltersItem
        $on
        $item
        (MapJoinOpFilter $item $leftCols $equivFD)
    )
    $private
)

# MapFilterIntoJoinRight is symmetric with MapFilterIntoJoinLeft. It maps
# Join filter conditions to use columns from the right side of the join rather
# than the left side. See that rule's comments for more details.
[MapFilterIntoJoinRight, Normalize]
(InnerJoin | InnerJoinApply | LeftJoin | LeftJoinApply | SemiJoin
        | SemiJoinApply | AntiJoin | AntiJoinApply
    $left:*
    $right:* & ^(HasOuterCols $right)
    $on:[
        ...
        $item:* &
            ^(FiltersItem (Eq (Variable) (Variable))) &
            ^(IsBoundBy $item $rightCols:(OutputCols $right)) &
            (CanMapJoinOpFilter
                $item
                $rightCols
                $equivFD:(GetEquivFD $on $left $right)
            )
        ...
    ]
    $private:*
)
=>
((OpName)
    $left
    $right
    (ReplaceFiltersItem
        $on
        $item
        (MapJoinOpFilter $item $rightCols $equivFD)
    )
    $private
)

# MapEqualityIntoJoinLeftAndRight checks whether it is possible to map
# equality conditions in a join to use different variables so that the
# number of conditions crossing both sides of a join are minimized. If so,
# the MapEqualityConditions function performs this mapping to construct new
# filters.
#
# For example, consider this query:
#
#   SELECT * FROM a, b WHERE a.x = b.x AND b.x = a.y;
#
# As written, both equality conditions contain variables from both sides of
# the join. We can rewrite this query, however, so that only one condition
# spans both sides:
#
#   SELECT * FROM a, b WHERE a.x = a.y AND b.x = a.y;
#
# Now the condition a.x = a.y is fully bound by the left side of the join,
# and is available to be pushed down by PushFilterIntoJoinLeft.
#
# See the MapEqualityConditions function for more details.
[MapEqualityIntoJoinLeftAndRight, Normalize]
(InnerJoin | InnerJoinApply | LeftJoin | LeftJoinApply | SemiJoin
        | SemiJoinApply | AntiJoin | AntiJoinApply
    $left:* & ^(HasOuterCols $left)
    $right:* & ^(HasOuterCols $right)
    $on:* &
        (CanMapJoinOpEqualities
            $on
            $leftCols:(OutputCols $left)
            $rightCols:(OutputCols $right)
        )
    $private:*
)
=>
((OpName)
    $left
    $right
    (MapJoinOpEqualities $on $leftCols $rightCols)
    $private
)

# PushFilterIntoJoinLeft pushes Join filter conditions into the left side of the
# join. This is possible in the case of InnerJoin, as long as the condition has
# no dependencies on the right side of the join. Left and Full joins are not
# eligible, since filtering left rows will change the number of rows in the
# result for those types of joins:
#
#   -- A row with nulls on the right side is returned for a.x=1, a.y=2, b.x=1.
#   SELECT * FROM a LEFT JOIN b ON a.x=b.x AND a.y < 0
#
#   -- But if the filter is incorrectly pushed down, then no row is returned.
#   SELECT * FROM (SELECT * FROM a WHERE a.y < 0) a LEFT JOIN b ON a.x=b.x
#
# In addition, AntiJoin is not eligible for this rule, as illustrated by this
# example:
#
#   -- A row is returned for a.y=2.
#   SELECT * FROM a ANTI JOIN b ON a.y < 0
#
#   -- But if the filter is incorrectly pushed down, then no row is returned.
#   SELECT * FROM (SELECT * FROM a WHERE a.y < 0) a ANTI JOIN b ON True
#
# Citations: [1]
[PushFilterIntoJoinLeft, Normalize]
(InnerJoin | InnerJoinApply | SemiJoin | SemiJoinApply
    $left:* & ^(HasOuterCols $left)
    $right:*
    $on:[
        ...
        $item:* & (IsBoundBy $item $leftCols:(OutputCols $left))
        ...
    ]
    $private:*
)
=>
((OpName)
    (Select $left (ExtractBoundConditions $on $leftCols))
    $right
    (ExtractUnboundConditions $on $leftCols)
    $private
)

# PushFilterIntoJoinRight is symmetric with PushFilterIntoJoinLeft. It pushes
# Join filter conditions into the right side of the join rather than into the
# left side. See that rule's comments for more details.
[PushFilterIntoJoinRight, Normalize]
(InnerJoin | InnerJoinApply | LeftJoin | LeftJoinApply | SemiJoin
        | SemiJoinApply | AntiJoin | AntiJoinApply
    $left:*
    $right:* & ^(HasOuterCols $right)
    $on:[
        ...
        $item:* &
            (IsBoundBy $item $rightCols:(OutputCols $right))
        ...
    ]
    $private:*
)
=>
((OpName)
    $left
    (Select $right (ExtractBoundConditions $on $rightCols))
    (ExtractUnboundConditions $on $rightCols)
    $private
)

# SimplifyLeftJoin reduces a LeftJoin operator to an InnerJoin operator (or a
# FullJoin to a RightJoin) when it's known that every row in the join's left
# input will match at least one row in the right input. Since every row matches,
# NULL-extended rows will never be added by the outer join, and therefore can be
# mapped to an InnerJoin (or RightJoin in case of FullJoin). See
# filtersMatchAllLeftRows comment for conditions in which this rule can match.
#
# Self-join example:
#   SELECT * FROM xy LEFT JOIN xy AS xy2 ON xy.y = xy2.y
#   =>
#   SELECT * FROM xy INNER JOIN xy AS xy2 ON xy.y = xy2.y
#
# Foreign-key example:
#   SELECT * FROM orders o LEFT JOIN customers c ON o.customer_id = c.id
#   =>
#   SELECT * FROM orders o INNER JOIN customers c ON o.customer_id = c.id
[SimplifyLeftJoin, Normalize]
(LeftJoin | LeftJoinApply | FullJoin
    $left:*
    $right:*
    $on:* & (JoinFiltersMatchAllLeftRows $left $right $on)
    $private:*
)
=>
(ConstructNonLeftJoin (OpName) $left $right $on $private)

# SimplifyRightJoin reduces a FullJoin operator to a LeftJoin operator when it's
# known that every row in the join's right input will match at least one row in
# the left input. This rule is symmetric with SimplifyLeftJoin; see that rule
# for more details and examples.
[SimplifyRightJoin, Normalize]
(FullJoin
    $left:*
    $right:*
    $on:* & (JoinFiltersMatchAllLeftRows $right $left $on)
    $private:*
)
=>
(LeftJoin $left $right $on $private)

# EliminateSemiJoin discards a SemiJoin operator when it's known that the right
# input never returns zero rows, and there is no join condition.
[EliminateSemiJoin, Normalize]
(SemiJoin | SemiJoinApply
    $left:*
    $right:* & ^(CanHaveZeroRows $right)
    []
)
=>
$left

# SimplifyZeroCardinalitySemiJoin converts a SemiJoin operator to an empty
# Values when it's known that the right input never returns any rows.
[SimplifyZeroCardinalitySemiJoin, Normalize]
(SemiJoin | SemiJoinApply
    $left:*
    $right:* & (HasZeroRows $right)
)
=>
(ConstructEmptyValues (OutputCols $left))

# EliminateAntiJoin discards an AntiJoin operator when it's known that the right
# input never returns any rows.
[EliminateAntiJoin, Normalize]
(AntiJoin | AntiJoinApply
    $left:*
    $right:* & (HasZeroRows $right)
)
=>
$left

# SimplifyZeroCardinalityAntiJoin converts an AntiJoin operator to an empty
# Values when it's known that the right input never returns zero rows, and
# there is no join condition.
[SimplifyZeroCardinalityAntiJoin, Normalize]
(AntiJoin | AntiJoinApply
    $left:*
    $right:* & ^(CanHaveZeroRows $right)
    []
)
=>
(ConstructEmptyValues (OutputCols $left))

# EliminateJoinNoColsLeft eliminates an InnerJoin with a one row, zero column
# left input set. These can be produced when a Values, scalar GroupBy, or other
# one-row operator's columns are never used.
[EliminateJoinNoColsLeft, Normalize]
(InnerJoin | InnerJoinApply
    $left:* & (HasNoCols $left) & (HasOneRow $left)
    $right:*
    $on:*
)
=>
(Select $right $on)

# EliminateJoinNoColsRight eliminates an InnerJoin with a one row, zero column
# right input set. These can be produced when a Values, scalar GroupBy, or other
# one-row operator's columns are never used.
[EliminateJoinNoColsRight, Normalize]
(InnerJoin | InnerJoinApply
    $left:*
    $right:* & (HasNoCols $right) & (HasOneRow $right)
    $on:*
)
=>
(Select $left $on)

# HoistJoinProjectRight lifts a passthrough Project operator from within a Join
# operator's right input to outside the join. This often allows the Project
# operator to be merged with an outer Project. Since Project operators tend to
# prevent other rules from matching, this and other rules try to either push
# them down (to prune columns), or else to pull them up (to get them out of the
# way of other operators).
#
# TODO(andyk): Add other join types.
[HoistJoinProjectRight, Normalize]
(InnerJoin | InnerJoinApply | LeftJoin | LeftJoinApply
    $left:*
    $right:(Project $input:* $projections:[])
    $on:*
    $private:*
)
=>
(Project
    ((OpName) $left $input $on $private)
    $projections
    (OutputCols2 $left $right)
)

# HoistJoinProjectLeft is the same as HoistJoinProjectRight, but for the left
# input of the join.
[HoistJoinProjectLeft, Normalize]
(InnerJoin | InnerJoinApply | LeftJoin | LeftJoinApply
    $left:(Project $input:* $projections:[])
    $right:*
    $on:*
    $private:*
)
=>
(Project
    ((OpName) $input $right $on $private)
    $projections
    (OutputCols2 $left $right)
)

# SimplifyJoinNotNullEquality simplifies an Is/IsNot equality filter condition
# when it's not possible for it to be null, as in the following case:
#
#   WHERE (a=b) IS NOT False
#
# If a and b are not null, then this can be simplified to:
#
#   WHERE a=b
#
# This pattern can be generated by the NormalizeNotAnyFilter rule, and its
# simplification is necessary for making anti-joins efficient, such as in TPCH
# query 16.
[SimplifyJoinNotNullEquality, Normalize]
(Join
    $left:*
    $right:*
    $on:[
        ...
        $item:(FiltersItem
            $condition:(Is | IsNot
                $eq:(Eq
                    # Check whether variable is a not-null column of left or right input.
                    (Variable
                        $col1:* &
                            (IsColNotNull2 $col1 $left $right)
                    )
                    (Variable
                        $col2:* &
                            (IsColNotNull2 $col2 $left $right)
                    )
                )
                $cnst:(True | False | Null)
            )
        )
        ...
    ]
    $private:*
)
=>
((OpName)
    $left
    $right
    (ReplaceFiltersItem
        $on
        $item
        (SimplifyNotNullEquality
            $eq
            (OpName $condition)
            (OpName $cnst)
        )
    )
    $private
)

# ExtractJoinEqualities finds equality conditions such that one side only
# depends on left columns and the other only on right columns and pushes the
# expressions down into Project operators. The result is a join that has an
# equality constraint, which is much more efficient. For example:
#
#   SELECT * FROM abc JOIN xyz ON a=x+1
#
# This join would be quadratic because we have no equality columns.
# This rule rewrites it as:
#
#   SELECT a,b,c,x,y,z FROM abc JOIN (SELECT *, x+1 AS x1 FROM xyz) ON a=x1
#
# This join can use hash join or lookup on the equality columns.
#
# Depending on the expressions involved, one or both sides require a projection.
[ExtractJoinEqualities, Normalize]
(JoinNonApply
    $left:* & ^(HasOuterCols $left)
    $right:* & ^(HasOuterCols $right)
    $on:[
        ...
        $item:(FiltersItem
            (Eq $a:^(ConstValue) $b:^(ConstValue)) &
                (CanExtractJoinEquality
                    $a
                    $b
                    (OutputCols $left)
                    (OutputCols $right)
                )
        )
        ...
    ]
    $private:*
)
=>
(ExtractJoinEquality (OpName) $left $right $on $item $private)

# SortFiltersInJoin ensures that any filters in an inner join are canonicalized
# by sorting them.
[SortFiltersInJoin, Normalize]
(InnerJoin
    $left:*
    $right:*
    $on:* & ^(AreFiltersSorted $on)
    $private:*
)
=>
(InnerJoin $left $right (SortFilters $on) $private)