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

# =============================================================================
# join.opt contains exploration rules for the Join operator.
# =============================================================================

# CommuteJoin creates a Join with the left and right inputs swapped. This is
# useful for other rules that convert joins to other operators (like merge
# join).
# If any join hints are specified, we keep the order in the query.
[CommuteJoin, Explore]
(InnerJoin | FullJoin $left:* $right:* $on:* $private:*)
=>
((OpName) $right $left $on (CommuteJoinFlags $private))

# CommuteLeftJoin creates a Join with the left and right inputs swapped.
# This is symmetric with the CommuteRightJoin normalization rule.
[CommuteLeftJoin, Explore]
(LeftJoin $left:* $right:* $on:* $private:*)
=>
(RightJoin $right $left $on (CommuteJoinFlags $private))

# CommuteSemiJoin generates an InnerJoin that is equivalent to the SemiJoin.
# SemiJoins impose a partial order on the joining tables. We can convert a
# SemiJoin into an InnerJoin by applying a DistinctOn operator on the selected
# rows of the RHS and then relaxing the partial join order restriction.
#
# This allows the join orders (A SemiJoin B) and (Distinct(B*) InnerJoin A) to be
# both considered by the optimizer. This is useful as a different join order may
# allow other rules to trigger. A common case is that, it would allow the inner
# join to use a lookup join in some cases (For example, a different join order
# would allow the use of a lookup join if A has much higher cardinality than B).
#
# We only do this when the On conditions guarantee that for each row in the LHS
# there is at most one unique matching row in the RHS. We need this because a
# SemiJoin emits a maximum of one row for every matching row in the LHS.
# This is an important difference between the behavior of a SemiJoin and an
# InnerJoin. For each row in the LHS, an InnerJoin emits a matching row for every
# row in the RHS where the conditions are met. For example consider the tables:
#
#   lhs       rhs
# +-----+   +------+
#    1         10
#    2         20
#
# If we do an InnerJoin on the table where the On condition is (lhs < rhs),
# you'll notice that each of the lhs rows are matched twice. And so the output
# of the InnerJoin would contain 2 rows for each matching row in the LHS.
# In order to guarantee that there is at most 1 matching row for every row in
# the LHS, we only commute a SemiJoin into an InnerJoin when the On conditions
# are only composed of equalities.
#
# Note: We only consider the columns of the RHS that are used in the On
# conditions (B* in the example above). And so we can be certain that
# ((Distinct(RHS*) InnerJoin LHS) will have at most 1 matching row for each row
# in the LHS if the On conditions are simple equalities.
#
# Citations: [7] (see section 2.1.1)
[CommuteSemiJoin, Explore]
(SemiJoin
    $left:*
    $right:*
    $on:* & (IsSimpleEquality $on)
    $private:* & (NoJoinHints $private)
)
=>
(Project
    (InnerJoin
        $left
        (DistinctOn
            $right
            []
            (MakeGrouping
                (IntersectionCols
                    (OutputCols $right)
                    (FilterOuterCols $on)
                )
                (EmptyOrdering)
            )
        )
        $on
        $private
    )
    []
    (OutputCols $left)
)

# GenerateMergeJoins creates MergeJoin operators for the join, using the
# interesting orderings property.
[GenerateMergeJoins, Explore]
(JoinNonApply $left:* $right:* $on:* $private:*)
=>
(GenerateMergeJoins (OpName) $left $right $on $private)

# GenerateLookupJoins creates LookupJoin operators for all indexes (of the Scan
# table) which allow it (including non-covering indexes). See the
# GenerateLookupJoins custom function for more details.
[GenerateLookupJoins, Explore]
(InnerJoin | LeftJoin | SemiJoin | AntiJoin
    $left:*
    (Scan $scanPrivate:*) & (IsCanonicalScan $scanPrivate)
    $on:*
    $private:*
)
=>
(GenerateLookupJoins (OpName) $left $scanPrivate $on $private)

# GenerateGeoLookupJoins creates GeoLookupJoin operators for all geospatial
# indexes (of the Scan table) which allow it. See the GenerateGeoLookupJoins
# custom function for more details.
# TODO(rytaft): Add support for LeftJoin.
[GenerateGeoLookupJoins, Explore]
(InnerJoin | SemiJoin | AntiJoin
    $left:*
    (Scan $scanPrivate:*) &
        (IsCanonicalScan $scanPrivate) &
        (HasInvertedIndexes $scanPrivate)
    $on:[
        ...
        (FiltersItem
            $fn:(Function) &
                (IsGeoIndexFunction $fn) &
                (HasAllVariableArgs $fn)
        )
        ...
    ]
    $private:*
)
=>
(GenerateGeoLookupJoins
    (OpName)
    $left
    $scanPrivate
    $on
    $private
    $fn
)

# GenerateZigzagJoins creates ZigzagJoin operators for all index pairs (of the
# Scan table) where the prefix column(s) of both indexes is/are fixed to
# constant values in the filters. See comments in GenerateZigzagJoin and
# distsqlrun/zigzagjoiner.go for more details on when a zigzag join can be
# planned.
#
# Zigzag joins are prohibited when the source Scan operator has been configured
# with a row-level locking mode. This is mostly out of convenience so that these
# row-level locking modes don't need to added to the ZigzagJoin operator. There
# doesn't seem to be a strong reason to support this, but if one comes up, it
# should be possible to lift this restriction.
[GenerateZigzagJoins, Explore]
(Select
    (Scan $scan:*) & (IsCanonicalScan $scan) & ^(IsLocking $scan)
    $filters:*
)
=>
(GenerateZigzagJoins $scan $filters)

# GenerateInvertedIndexZigzagJoins creates ZigzagJoin operators for inverted
# indexes that can be constrained with two or more distinct constant values.
# Inverted indexes contain one row for each path-to-leaf in a JSON value, so one
# row in the primary index could generate multiple inverted index keys. This
# property can be exploited by zigzag joining on the same inverted index, fixed
# at any two of the JSON paths we are querying for.
#
# Zigzag joins are prohibited when the source Scan operator has been configured
# with a row-level locking mode. This is mostly out of convenience so that these
# row-level locking modes don't need to added to the ZigzagJoin operator. There
# doesn't seem to be a strong reason to support this, but if one comes up, it
# should be possible to lift this restriction.
[GenerateInvertedIndexZigzagJoins, Explore]
(Select
    (Scan $scan:*) &
        (IsCanonicalScan $scan) &
        ^(IsLocking $scan) &
        (HasInvertedIndexes $scan)
    $filters:*
)
=>
(GenerateInvertedIndexZigzagJoins $scan $filters)

# GenerateLookupJoinWithFilter creates a LookupJoin alternative for a Join which
# has a Select->Scan combination as its right input. The filter can get merged
# with the ON condition (this is correct for both inner and left join).
[GenerateLookupJoinsWithFilter, Explore]
(InnerJoin | LeftJoin
    $left:*
    (Select
        (Scan $scanPrivate:*) & (IsCanonicalScan $scanPrivate)
        $filters:*
    )
    $on:*
    $private:*
)
=>
(GenerateLookupJoins
    (OpName)
    $left
    $scanPrivate
    (ConcatFilters $on $filters)
    $private
)

# AssociateJoin applies the rule of join associativity. It converts an
# expression like:
#   (A JOIN B ON A.y = B.y) JOIN C ON B.x = C.x
# to the logically equivalent expression:
#   A JOIN (B JOIN C ON B.x = C.x) ON A.y = B.y
#
# If any of the joins contains a hint, we do not rearrange the joins.
[AssociateJoin, Explore]
(InnerJoin
    $left:(InnerJoin
        $innerLeft:*
        $innerRight:*
        $innerOn:*
        $innerPrivate:* & (NoJoinHints $innerPrivate)
    )
    $right:* & (ShouldReorderJoins $left $right)
    $on:*
    $private:* & (NoJoinHints $private)
)
=>
(InnerJoin
    $innerLeft
    (InnerJoin
        $innerRight
        $right
        (SortFilters
            (ExtractBoundConditions
                $newOn:(MapJoinOpEqualities
                    (ConcatFilters $on $innerOn)
                    (OutputCols $innerLeft)
                    $cols:(OutputCols2 $innerRight $right)
                )
                $cols
            )
        )
        (EmptyJoinPrivate)
    )
    (SortFilters (ExtractUnboundConditions $newOn $cols))
    (EmptyJoinPrivate)
)