[
https://issues.apache.org/jira/browse/CALCITE-6966?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17945696#comment-17945696
]
Zhen Chen commented on CALCITE-6966:
------------------------------------
[~julianhyde] I have already linked the previous Jira, which describes the
purpose of this rule. If you have time, you can take a look. The current Jira
is to relax the validation criteria for the join condition in this rule.
Splitting the OR condition into multiple RexNodes, where RexNodes may be
complex conditions. Now requires that there must be an equivalent condition in
RexNodes (in order to be converted into a hash join later) and other expr does
not include both columns of the left and right tables, it is now considered to
meet the requirements. Then this RexNode can be used as a separable unit.
> Extend JoinConditionOrExpansionRule to support column and constants as valid
> conditions
> ---------------------------------------------------------------------------------------
>
> Key: CALCITE-6966
> URL: https://issues.apache.org/jira/browse/CALCITE-6966
> Project: Calcite
> Issue Type: Improvement
> Reporter: Zhen Chen
> Assignee: Zhen Chen
> Priority: Major
> Labels: pull-request-available
>
> SQL:
> {code:java}
> select * from emp as t1 inner join emp as t2
> on (t1.empno=t2.empno
> and t1.empno > 100
> and t1.ename in ('a', 'bb', 'cc')
> and t1.sal > 120 and t1.sal < 3000)
> or
> (t1.deptno=t2.deptno
> and t2.empno > 200
> and t2.ename in ('a', 'bb', 'cc')
> and t2.sal > 110 and t2.sal < 3000) {code}
> Init plan:
> {code:java}
> LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4],
> SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8], EMPNO0=[$9], ENAME0=[$10],
> JOB0=[$11], MGR0=[$12], HIREDATE0=[$13], SAL0=[$14], COMM0=[$15],
> DEPTNO0=[$16], SLACKER0=[$17])
> LogicalJoin(condition=[OR(AND(=($0, $9), >($0, 100), SEARCH($1,
> Sarg['a':VARCHAR(20), 'bb':VARCHAR(20), 'cc':VARCHAR(20)]:VARCHAR(20)),
> SEARCH($5, Sarg[(120..3000)])), AND(=($7, $16), >($9, 200), SEARCH($10,
> Sarg['a':VARCHAR(20), 'bb':VARCHAR(20), 'cc':VARCHAR(20)]:VARCHAR(20)),
> SEARCH($14, Sarg[(110..3000)])))], joinType=[inner])
> LogicalTableScan(table=[[CATALOG, SALES, EMP]])
> LogicalTableScan(table=[[CATALOG, SALES, EMP]]) {code}
> can convert to:
> {code:java}
> LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4],
> SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8], EMPNO0=[$9], ENAME0=[$10],
> JOB0=[$11], MGR0=[$12], HIREDATE0=[$13], SAL0=[$14], COMM0=[$15],
> DEPTNO0=[$16], SLACKER0=[$17])
> LogicalUnion(all=[true])
> LogicalJoin(condition=[AND(=($0, $9), >($0, 100), SEARCH($1,
> Sarg['a':VARCHAR(20), 'bb':VARCHAR(20), 'cc':VARCHAR(20)]:VARCHAR(20)),
> SEARCH($5, Sarg[(120..3000)]))], joinType=[inner])
> LogicalTableScan(table=[[CATALOG, SALES, EMP]])
> LogicalTableScan(table=[[CATALOG, SALES, EMP]])
> LogicalJoin(condition=[AND(=($7, $16), >($9, 200), SEARCH($10,
> Sarg['a':VARCHAR(20), 'bb':VARCHAR(20), 'cc':VARCHAR(20)]:VARCHAR(20)),
> SEARCH($14, Sarg[(110..3000)]), OR(<>($0, $9), <=($0, 100), SEARCH($1,
> Sarg[(-∞..'a':VARCHAR(20)), ('a':VARCHAR(20)..'bb':VARCHAR(20)),
> ('bb':VARCHAR(20)..'cc':VARCHAR(20)), ('cc':VARCHAR(20)..+∞)]:VARCHAR(20)),
> SEARCH($5, Sarg[(-∞..120], [3000..+∞)])))], joinType=[inner])
> LogicalTableScan(table=[[CATALOG, SALES, EMP]])
> LogicalTableScan(table=[[CATALOG, SALES, EMP]]) {code}
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
