[
https://issues.apache.org/jira/browse/CALCITE-2948?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16827891#comment-16827891
]
Danny Chan edited comment on CALCITE-2948 at 4/28/19 10:03 AM:
---------------------------------------------------------------
[~hyuan], [~julianhyde]
Instead of making a rule, i extend the RelDecorrelator to generate simpler plan
at the very first time.
The main idea is to rewrite and get a equivalent expression for correlated
variable when we try to generate correlated value, in order to keep
correctness, i only support operators +(PLUS) and -(MINUS), the main change
list is:
# Add method RelDecorrelator#references2 to recognize pattern:
{code:java}
a = $b + 1 => $b = a - 1
a = 1 + $b => $b = a - 1
a = $b - 1 => $b = a + 1
a = 1 - $b => $b = 1 - a{code}
# Add a tool method RexUtil#simplifyCondition to simplify the useless *always
true* predicate generated during decorrelation, also add a test case for it.
Now it is only used in decorrelation but i think it could be used in any
filter/join condition simplification
# This sql statement now can be simplified and i add it to test case:
{code:java}
select deptno from dept d where deptno in (select deptno from EMP where
empno=d.deptno+1);{code}
I also found that we make join condition 123(smallint) = 123 (int) returns
false but i think it should return true, so i extend it. Correct me if i'm
wrong.
I'm glad that CALCITE-1513 has better plan also.
was (Author: danny0405):
[~hyuan], [~julianhyde]
Instead of making a rule, i extend the RelDecorrelator to generate simpler plan
at the very first time.
The main idea is to rewrite and a equivalent expression when we try to generate
correlated value, in order ro keep correctness, i only support operators
+(PLUS) and -(MINUS), the main change list is:
# add references2 to RelDecorrelator to recognize pattern:
{code:java}
a = $b + 1 => $b = a - 1
a = 1 + $b => $b = a - 1
a = $b - 1 => $b = a + 1
a = 1 - $b => $b = 1 - a{code}
# Add a tool method in RexUtil#simplifyCondition to simplify the useless
always true predicate generated during decorrelation, also add a test case
for it. Now it is only used in decorrelation but i think it may be used in any
filter/join condition simplification
# This sql statement now can be simplified and i add it to test case:
{code:java}
select deptno from dept d where deptno in (select deptno from EMP where
empno=d.deptno+1);
{code}
I also found that we make join condition 123(smallint) = 123 (int) returns
falsem but i think it should returns true, so i extend it. Correct me if i'm
wrong.
I'm glad that CALCITE-1513 has better plan also.
> Complicated logical plan generated for in subquery with non-equi condition
> --------------------------------------------------------------------------
>
> Key: CALCITE-2948
> URL: https://issues.apache.org/jira/browse/CALCITE-2948
> Project: Calcite
> Issue Type: Bug
> Components: core
> Reporter: Haisheng Yuan
> Assignee: Danny Chan
> Priority: Major
> Labels: pull-request-available, sub-query
> Time Spent: 1.5h
> Remaining Estimate: 0h
>
> Repro:
> Add the following test to SqlToRelConverterTest.java.
> {code:java}
> @Test public void testSubQueryIN() {
> final String sql = "select deptno\n"
> + "from EMP e\n"
> + "where deptno in (select deptno\n"
> + "from EMP where empno=e.empno+1)";
> sql(sql).ok();
> }
> {code}
> Plan:
> {code:java}
> LogicalProject(DEPTNO=[$7])
> LogicalJoin(condition=[AND(=($0, $10), =($7, $9))], joinType=[inner])
> LogicalTableScan(table=[[CATALOG, SALES, EMP]])
> LogicalAggregate(group=[{0, 1}])
> LogicalProject(DEPTNO=[$7], EMPNO0=[$9])
> LogicalJoin(condition=[=($0, +($9, 1))], joinType=[inner])
> LogicalTableScan(table=[[CATALOG, SALES, EMP]])
> LogicalProject(EMPNO=[$0])
> LogicalTableScan(table=[[CATALOG, SALES, EMP]])
> {code}
> One join would suffice.
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
