[jira] [Created] (CALCITE-2967) New rule to remove SemiJoin

["Ruben Quesada Lopez (JIRA)" <ji...@apache.org>]

Ruben Quesada Lopez created CALCITE-2967:
--------------------------------------------

             Summary: New rule to remove SemiJoin
                 Key: CALCITE-2967
                 URL: https://issues.apache.org/jira/browse/CALCITE-2967
             Project: Calcite
          Issue Type: New Feature
            Reporter: Ruben Quesada Lopez


(As far as I know, there is no rule to achieve what I am about to describe, if there exists already a way to do it, please let me know).

In some specific situations, a SemiJoin can be completely removed and replaced by its left child with an appropriate transformed filter.
Let us say we want to retrieve all employees whose department satisfy a certain condition, i.e.:
{code}
SELECT * from Employee e
WHERE e.deptno IN
  (SELECT d.deptno FROM Department d
    WHERE <condition>)
{code}

Which would translate to something like:
{code}
SemiJoin (e.deptno=d.deptno)
    Scan (table=Employee as e)
    Filter(<condition>)
        Scan (table=Department as d)
{code}

In a "normal" scenario, e.g. "all employees from Sales department", the plan could no be simplified:
{code}
SemiJoin (e.deptno=d.deptno)
    Scan (table=Employee as e)
    Filter(d.name="Sales")
        Scan (table=Department as d)
{code}

But with a specific condition, based on deptno, e.g. "all employees whose deptno is greater than 10":
{code}
SemiJoin (e.deptno=d.deptno)
    Scan (table=Employee as e)
    Filter(d.deptno>10)
        Scan (table=Department as d)
{code}
The plan could be simplified: the SemiJoin is not actually needed, we can perform the query with a single scan and a converted filter:
{code}
Filter(e.deptno>10)
    Scan (table=Employee as e)
{code}

The goal would be to provide a new rule to achieve that (since there is already a SemiJoinRemoveRule, we could name this new rule as e.g. SemiJoinSimplifyRule?).
I know that, ideally, this rule should not be needed because the plan could be directly written without the SemiJoin, but let's say that we are in a situation were the plan is systematically generated with the same pattern, and there is no way to know in advanced the filter condition that will be used within.




--
This message was sent by Atlassian JIRA
(v7.6.3#76005)