[
https://issues.apache.org/jira/browse/CALCITE-6966?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17945736#comment-17945736
]
Mihai Budiu commented on CALCITE-6966:
--------------------------------------
I think your description is still too low level.
The rule JoinConditionOrExpansionRule expands a Join with an OR predicate into
multiple joins whose results are UNIONed.
This does not happen for any OR predicate, only if there is some benefit to be
gained from this expansion, generally converting a CROSS JOIN into a sequence
of equi JOINs.
This issue is about accepting more predicates that will allow the expansion to
be performed.
In particular, when components of the OR are predicates that contain equality,
that are single-side (refer to only on of the collections joined), or which are
constant, they will all trigger the expansion.
> 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)
