You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@calcite.apache.org by Mihai Budiu <mb...@gmail.com> on 2025/03/09 16:26:41 UTC

predicate pull-up

Given this query:

CREATE TABLE X(x INT);
CREATE TABLE Y(x INT, y INT);
CREATE VIEW V AS SELECT X.x, Y.y FROM X JOIN Y ON X.x = Y.x WHERE Y.y = 23

there is a rewrite rule which pulls up the Y.y = 23 predicate, optimizing this:

    LogicalProject(x=[$0], y=[$2]), id = 65
      LogicalFilter(condition=[=($2, 23)]), id = 63
        LogicalJoin(condition=[=($0, $1)], joinType=[inner]), id = 62
          LogicalTableScan(table=[[schema, x]]), id = 59
          LogicalTableScan(table=[[schema, y]]), id = 61

into this:

    LogicalProject(x=[$0], y=[CAST(23):INTEGER]), id = 136
      LogicalJoin(condition=[=($0, $1)], joinType=[inner]), id = 134
        LogicalTableScan(table=[[schema, x]]), id = 59
        LogicalFilter(condition=[=($1, 23)]), id = 132
          LogicalTableScan(table=[[schema, y]]), id = 61

But is there a rule that would pull up the predicate from the following query?

CREATE VIEW V AS SELECT X.x, Y.y FROM X JOIN Y ON X.x = Y.x AND Y.y = 23

Thank you,
Mihai


Re:predicate pull-up

Posted by suibianwanwan33 <su...@foxmail.com>.
It might be helpful to add a CoreRules.JOIN_CONDITION_PUSH rule.


Best,
Suibianwanwan


------------------&nbsp;Original&nbsp;------------------
From:                                                                                                                        "dev"                                                                                    <mbudiu@gmail.com&gt;;
Date:&nbsp;Mon, Mar 10, 2025 00:26 AM
To:&nbsp;"dev@calcite.apache.org"<dev@calcite.apache.org&gt;;

Subject:&nbsp;predicate pull-up



Given this query:

CREATE TABLE X(x INT);
CREATE TABLE Y(x INT, y INT);
CREATE VIEW V AS SELECT X.x, Y.y FROM X JOIN Y ON X.x = Y.x WHERE Y.y = 23

there is a rewrite rule which pulls up the Y.y = 23 predicate, optimizing this:

&nbsp;&nbsp;&nbsp; LogicalProject(x=[$0], y=[$2]), id = 65
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; LogicalFilter(condition=[=($2, 23)]), id = 63
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; LogicalJoin(condition=[=($0, $1)], joinType=[inner]), id = 62
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; LogicalTableScan(table=[[schema, x]]), id = 59
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; LogicalTableScan(table=[[schema, y]]), id = 61

into this:

&nbsp;&nbsp;&nbsp; LogicalProject(x=[$0], y=[CAST(23):INTEGER]), id = 136
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; LogicalJoin(condition=[=($0, $1)], joinType=[inner]), id = 134
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; LogicalTableScan(table=[[schema, x]]), id = 59
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; LogicalFilter(condition=[=($1, 23)]), id = 132
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; LogicalTableScan(table=[[schema, y]]), id = 61

But is there a rule that would pull up the predicate from the following query?

CREATE VIEW V AS SELECT X.x, Y.y FROM X JOIN Y ON X.x = Y.x AND Y.y = 23

Thank you,
Mihai