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

# =============================================================================
# select.opt contains normalization rules for the Select operator.
# =============================================================================

# SimplifySelectFilters simplifies the Filters operator in several possible
# ways:
#   - Removes True operands
#   - Replaces the Filters operator with False if any operand is False or Null
#   - Flattens nested And operands by merging their conditions into parent
#   - Simplifies Or operands where one side is a Null to the other side
#
# Note that the Null handling behavior is different than the SimplifyAnd rules,
# because Filters only appears as a Select or Join filter condition, both of
# which treat a Null filter conjunct exactly as if it were False.
[SimplifySelectFilters, Normalize, HighPriority]
(Select
    $input:*
    $filters:[
            ...
            $item:(FiltersItem
                    (And | True | False | Null | Or)
                ) &
                ^(IsUnsimplifiableOr $item)
            ...
        ] &
        ^(IsFilterFalse $filters)
)
=>
(Select $input (SimplifyFilters $filters))

# ConsolidateSelectFilters consolidates filters that constrain a single
# variable. For example, filters x >= 5 and x <= 10 would be combined into a
# single Range operation.
#
# The benefit of consolidating these filters is it allows a single constraint
# to be generated for the variable instead of multiple. In the example above,
# we can generate the single constraint [/5 - /10] instead of the two
# constraints [/5 - ] and [ - /10]. The single constraint allows us to better
# estimate the selectivity of the predicate when calculating statistics for
# the Select expression.
#
# This rule is low priority so other rules in this file such as
# RemoveNotNullCondition can run first.
[ConsolidateSelectFilters, Normalize, LowPriority]
(Select $input:* $filters:* & (CanConsolidateFilters $filters))
=>
(Select $input (ConsolidateFilters $filters))

# DetectSelectContradiction replaces a Select with an empty Values if it detects
# a contradiction in the filter.
[DetectSelectContradiction, Normalize]
(Select
    $input:*
    [ ... $item:(FiltersItem) & (IsContradiction $item) ... ]
)
=>
(ConstructEmptyValues (OutputCols $input))

# EliminateSelect discards an unnecessary Select operator in the case where its
# filter is always true. Keep this near the top of the file so that it tends to
# be checked early.
[EliminateSelect, Normalize]
(Select $input:* [])
=>
$input

# MergeSelects combines two nested Select operators into a single Select that
# ANDs the filter conditions of the two Selects.
[MergeSelects, Normalize]
(Select (Select $input:* $innerFilters:*) $filters:*)
=>
(Select $input (ConcatFilters $innerFilters $filters))

# PushSelectIntoProject pushes the Select operator into its Project input. This
# is typically preferable because it minimizes the number of rows which Project
# needs to process. This is especially important if Project is adding expensive
# computed columns.
[PushSelectIntoProject, Normalize]
(Select
    (Project $input:* $projections:* $passthrough:*)
    $filters:[
        ...
        $item:* &
            (IsBoundBy $item $inputCols:(OutputCols $input))
        ...
    ]
)
=>
(Select
    (Project
        (Select
            $input
            (ExtractBoundConditions $filters $inputCols)
        )
        $projections
        $passthrough
    )
    (ExtractUnboundConditions $filters $inputCols)
)

# MergeSelectInnerJoin merges a Select operator with an InnerJoin input by
# AND'ing the filter conditions of each and creating a new InnerJoin with that
# On condition. This is only safe to do with InnerJoin in the general case
# where the conditions could filter either left or right rows. The special case
# where a condition filters only one or the other is already taken care of by
# the PushSelectIntoJoin rules.
# NOTE: Keep this rule ordered before the PushSelectIntoJoin rules to avoid
#       missing out on the potential for new filter inference based on
#       equivalent columns.
[MergeSelectInnerJoin, Normalize]
(Select
    $input:(InnerJoin | InnerJoinApply
        $left:*
        $right:*
        $on:*
        $private:*
    )
    $filters:*
)
=>
((OpName $input)
    $left
    $right
    (ConcatFilters $on $filters)
    $private
)

# PushSelectCondLeftIntoJoinLeftAndRight applies to the case when a condition
# bound by the left side of a join can be mapped to the right side using
# equality columns from the ON condition of the join. It pushes the original
# filter to the left side, and the mapped filter to the right side.
# For example, consider this query:
#
#   SELECT * FROM l LEFT JOIN r ON l.x = r.x WHERE l.x = 5;
#
# This can safely be converted to:
#
#   SELECT * FROM (SELECT * FROM l WHERE l.x = 5)
#   LEFT JOIN (SELECT * FROM r WHERE r.x = 5) ON l.x = r.x;
#
# It's not normally correct to push filters from the SELECT clause to
# the right side of a LEFT JOIN, since those rows might still show up
# in the output as NULL-extended rows from the left side. In this case,
# however, for any rows removed from the right side, the matching rows are
# also removed from the left side (and thus removed from the output).
# To ensure that this is the case, it's important that the filter only refers
# to columns on the left side that have corresponding equivalent columns on
# the right side.
[PushSelectCondLeftIntoJoinLeftAndRight, Normalize]
(Select
    $input:(LeftJoin | LeftJoinApply | SemiJoin | SemiJoinApply
            | AntiJoin | AntiJoinApply
        $left:*
        $right:*
        $on:*
        $private:*
    )
    $filters:[
        ...
        $item:(FiltersItem $condition:*) &
            (IsBoundBy $item (OutputCols $left)) &
            (CanMapJoinOpFilter
                $item
                $rightCols:(OutputCols $right)
                $equivFD:(GetEquivFD $on $left $right)
            )
        ...
    ]
)
=>
(Select
    ((OpName $input)
        (Select $left [ (FiltersItem $condition) ])
        (Select
            $right
            [
                (FiltersItem
                    (MapJoinOpFilter $item $rightCols $equivFD)
                )
            ]
        )
        $on
        $private
    )
    (RemoveFiltersItem $filters $item)
)

# PushSelectIntoJoinLeft pushes Select filter conditions into the left side of
# an input Join. This is possible in the case of InnerJoin, LeftJoin, SemiJoin,
# and AntiJoin, as long as the condition has no dependencies on the right side
# of the join. Right and Full joins are not eligible, since attempting to filter
# left rows would just result in NULL left rows instead.
#
#   -- No row is returned for a.x=1, a.y=2, b.x=1, since the WHERE excludes it.
#   SELECT * FROM a RIGHT JOIN b ON a.x=b.x WHERE a.y < 0
#
#   -- But if the filter is incorrectly pushed down in RIGHT/FULL JOIN case,
#   -- then a row containing null values on the left side is returned.
#   SELECT * FROM (SELECT * FROM a WHERE a.y < 0) a RIGHT JOIN b ON a.x=b.x
#
# Citations: [1]
[PushSelectIntoJoinLeft, Normalize]
(Select
    $input:(LeftJoin | LeftJoinApply | SemiJoin | SemiJoinApply
            | AntiJoin | AntiJoinApply
        $left:*
        $right:*
        $on:*
        $private:*
    )
    $filters:[
        ...
        $item:* & (IsBoundBy $item $leftCols:(OutputCols $left))
        ...
    ]
)
=>
(Select
    ((OpName $input)
        (Select
            $left
            (ExtractBoundConditions $filters $leftCols)
        )
        $right
        $on
        $private
    )
    (ExtractUnboundConditions $filters $leftCols)
)

# PushSelectIntoGroupBy pushes a Select condition below a GroupBy in the case
# where it only references grouping columns or ConstAgg columns.
#
# This rule doesn't work on ScalarGroupBy which exhibits different behavior if
# the input is empty:
#   SELECT MAX(y) FROM a
#
# If "a" is empty, this returns a single row containing a null value. This is
# different behavior than a GroupBy with grouping columns, which would return
# the empty set for a similar query:
#   SELECT MAX(y) FROM a GROUP BY x
#
# Citations: [2]
#
# Note: Do not add EnsureDistinctOn to the match pattern. Pushing the select
# filters through the EnsureDistinctOn can prevent it from detecting duplicate
# rows and therefore change error behavior.
[PushSelectIntoGroupBy, Normalize]
(Select
    $input:(GroupBy | DistinctOn
        $groupingInput:*
        $aggregations:*
        $groupingPrivate:*
    )
    $filters:[
        ...
        $item:* &
            (IsBoundBy
                $item
                $passthrough:(GroupingAndConstCols
                    $groupingPrivate
                    $aggregations
                )
            )
        ...
    ]
)
=>
(Select
    ((OpName $input)
        (Select
            $groupingInput
            (ExtractBoundConditions $filters $passthrough)
        )
        $aggregations
        $groupingPrivate
    )
    (ExtractUnboundConditions $filters $passthrough)
)

# RemoveNotNullCondition removes a filter with an IS NOT NULL condition
# when the given column has a NOT NULL constraint.
[RemoveNotNullCondition, Normalize]
(Select
    $input:*
    $filters:[
        ...
        $item:(FiltersItem
            (IsNot
                (Variable $col:* & (IsColNotNull $col $input))
                (Null)
            )
        )
        ...
    ]
)
=>
(Select $input (RemoveFiltersItem $filters $item))

# PushSelectIntoProjectSet pushes filters into a ProjectSet. In particular,
# the filters that are bound to the input columns of the ProjectSet are
# pushed down into it, in hopes of being pushed down further into joins
# and scans underneath the ProjectSet.
[PushSelectIntoProjectSet, Normalize]
(Select
    (ProjectSet $input:* $zip:*)
    $filters:[
        ...
        $item:* &
            (IsBoundBy $item $inputCols:(OutputCols $input))
        ...
    ]
)
=>
(Select
    (ProjectSet
        (Select
            $input
            (ExtractBoundConditions $filters $inputCols)
        )
        $zip
    )
    (ExtractUnboundConditions $filters $inputCols)
)

# PushFilterIntoSetOp pushes filters down to both the left and right sides
# of all set operators. For example, consider this query:
#
#   SELECT * FROM (SELECT x FROM a UNION ALL SELECT y FROM b) WHERE x < 5
#
# In this case, we can map x < 5 to both sides based on the knowledge that
# in the union the x out col corresponds to the x column in the (a) table and
# the y column in the (b) table. Therefore our mapping becomes x < 5 in the
# left side, and y <  5 in the right side. Given this mapping, we can now
# safely push the filter down to both sides as follows:
#
# SELECT * FROM (SELECT x FROM a UNION ALL SELECT y FROM b) WHERE x < 5
# =>
# (SELECT x FROM a WHERE x < 5) UNION ALL (SELECT y FROM b WHERE y < 5)
#
# Pushing (all) the filters down for each of the set operators (Union, Union
# All, Except, Except All, Intersect, Intersect All) is logically equivalent
# to filtering after applying the set operator. Here's some justification for
# this claim:
#
# Notice that each of the set operators this rule applies to, only works on
# union compatible relations. The resulting column set after applying the set
# operator, is also necessarily a subset of the column set of the (any)
# relations it was composed of. And so, any filter applied must have a
# corresponding column in each of the base relations.
#
# This works in the case of Union because a row passing the filter is
# independent of the other rows in its relation. It works in the case of
# Intersect because if a row `a` was filtered from the intersection, it will
# also be filtered from the LHS and RHS. It works in the case of Except because
# if a row is filtered from the RHS, preventing its removal from the LHS, it
# will also have been filtered from the LHS.
#
# Visualization of the rule:
# Let A and B be sets. Let the filter be represented by eliminating some set C.
#
# Union (All):        (A Union B) \ C => (A \ C) Union (B \ C)
# Intersection (All): (A Intersect B) \ C => (A \ C) Intersect (B \ C)
# Except (All):       (A Except B) \ C => (A \ C) Except (B \ C)
#
# We don't push a filter down if it references outer columns because doing so
# prevents decorrelation.
[PushFilterIntoSetOp, Normalize]
(Select
    $input:(Set $left:* $right:* $colmap:*)
    $filter:[
        ...
        $item:* &
            (CanMapOnSetOp $item) &
            (IsBoundBy $item $inputCols:(OutputCols $input))
        ...
    ]
)
=>
(Select
    ((OpName $input)
        (Select
            $left
            [ (FiltersItem (MapSetOpFilterLeft $item $colmap)) ]
        )
        (Select
            $right
            [ (FiltersItem (MapSetOpFilterRight $item $colmap)) ]
        )
        $colmap
    )
    (RemoveFiltersItem $filter $item)
)