[jira] [Commented] (CALCITE-6363) Introduce a rule to derive more filters from inner join condition

["Julian Hyde (Jira)" <ji...@apache.org>]

    [ https://issues.apache.org/jira/browse/CALCITE-6363?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17836987#comment-17836987 ] 

Julian Hyde commented on CALCITE-6363:
--------------------------------------

Don’t we have some existing rules (metadata) for this? Can you link any related issues (open or fixed) that you know about. 

> Introduce a rule to derive more filters from inner join condition
> -----------------------------------------------------------------
>
>                 Key: CALCITE-6363
>                 URL: https://issues.apache.org/jira/browse/CALCITE-6363
>             Project: Calcite
>          Issue Type: New Feature
>          Components: core
>            Reporter: ruanhui
>            Priority: Minor
>              Labels: pull-request-available
>
> Sometimes we can infer more predicates from inner Join , for example, in the query
> SELECT * FROM ta INNER JOIN tb ON ta.x = tb.y WHERE ta.x > 10
> we can infer condition tb.y > 10 and we can push it down to the table tb.
> In this way, it is possible to reduce the amount of data involved in the Join.
> To achieve this, here is my idea.
> The core data strucature is two Multimap:
> predicateMap : a map for inputRef to corresponding predicate such as: $1 -> [$1 > 10, $1 < 20, $1 = $2]
> equivalenceMap : a map for inputRef to corresponding equivalent values or inputRefs such as: $1 -> [$2, 1]
> The filter derivation is divided into 4 steps:
> 1. construct predicate map and equivalence map by traversing all conjunctions in the condition
> 2. search map and rewrite predicates with equivalent inputRefs or literals
> 2.1 find all inputRefs that are equivalent to the current inputRef, and then rewrite all predicates involving equivalent inputRefs using inputRef, for example if we have inputRef $1 = equivInputRef $2, then we can rewrite \{$2 = 10} to \{$1 = 10}.
> 2.2 find all predicates involving current inputRef. If any predicate refers to another inputRef, rewrite the predicate with the literal/constant equivalent to that inputRef, such as: if we have inputRef \{$1 > $2} and \{$2 = 10} then we can infer new condition \{$1 > 10}.
> 2.3 derive new predicates based on equivalence relation in equivalenceMultimap
> 3. compose all original predicates and derived predicates
> 4. simplify expression such as range merging, like \{$1 > 10 AND $1 > 20} => \{$1 > 20}, \{$1 > $2 AND $1 > $2} => \{$1 > $2}
> Anyone interested in this, please feel free to comment on this issue.



--
This message was sent by Atlassian Jira
(v8.20.10#820010)