[
https://issues.apache.org/jira/browse/CALCITE-2158?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16479476#comment-16479476
]
Volodymyr Vysotskyi commented on CALCITE-2158:
----------------------------------------------
Realized that this aggregate call may be used to check that aggregate call
returns the single value, so it is not needed to remove it from the aggregate.
But the real problem appears in the case when the query has several correlated
subqueries. This test:
{code:java}
@Test public void testRelDecorrelatorWithComplexFilters() {
final HepProgram program = HepProgram.builder()
.build();
final String sql = "SELECT cs1.sal\n"
+ "FROM emp cs1,\n"
+ " emp cs3\n"
+ "WHERE cs1.ename = cs3.ename\n"
+ "AND EXISTS\n"
+ "(SELECT *\n"
+ "FROM emp cs2\n"
+ "WHERE cs1.sal = cs2.sal\n"
+ "AND cs1.deptno <> cs2.deptno)"
+ "AND EXISTS"
+ "(SELECT *\n"
+ "FROM emp cr1\n"
+ "WHERE cs1.sal = cr1.sal)";
sql(sql)
.withDecorrelation(true)
.expand(true)
.with(program)
.checkUnchanged();
}
{code}
Returns plan with join with true in condition
{noformat}
LogicalProject(SAL=[$5])
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], $f0=[$18], $f019=[$20])
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], $f0=[$18], SAL9=[CAST($19):INTEGER],
$f1=[CAST($20):BOOLEAN])
LogicalJoin(condition=[=($5, $19)], joinType=[inner])
LogicalFilter(condition=[AND(=($1, $10), IS NOT NULL($18))])
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], $f0=[$20])
LogicalJoin(condition=[AND(=($5, $18), =($7, $19))],
joinType=[left])
LogicalJoin(condition=[true], joinType=[inner])
LogicalTableScan(table=[[CATALOG, SALES, EMP]])
LogicalTableScan(table=[[CATALOG, SALES, EMP]])
LogicalAggregate(group=[{0, 1}], agg#0=[MIN($2)])
LogicalProject(SAL0=[$1], DEPTNO0=[$2], $f0=[$0])
LogicalProject($f0=[true], SAL0=[$9], DEPTNO0=[$10])
LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3],
HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8], SAL0=[$9],
DEPTNO0=[$10])
LogicalJoin(condition=[AND(=($9, $5), <>($10, $7))],
joinType=[inner])
LogicalTableScan(table=[[CATALOG, SALES, EMP]])
LogicalAggregate(group=[{0, 1}])
LogicalProject(SAL=[$5], DEPTNO=[$7])
LogicalJoin(condition=[true], joinType=[inner])
LogicalTableScan(table=[[CATALOG, SALES, EMP]])
LogicalTableScan(table=[[CATALOG, SALES, EMP]])
LogicalAggregate(group=[{0}], agg#0=[MIN($1)])
LogicalProject(SAL9=[$1], $f0=[$0])
LogicalProject($f0=[true], SAL9=[$9])
LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3],
HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8], SAL9=[$5])
LogicalTableScan(table=[[CATALOG, SALES, EMP]])
{noformat}
It happens because {{HepPlanner}} in {{RelDecorrelator}} partially optimizes
input plan:
{noformat}
LogicalProject(SAL=[$5]): rowcount = 1.0, cumulative cost = 4049.750000000001,
id = 67
LogicalFilter(condition=[AND(=($1, $10), IS NOT NULL($18), IS NOT
NULL($19))]): rowcount = 1.0, cumulative cost = 4048.750000000001, id = 65
LogicalCorrelate(correlation=[$cor2], joinType=[left],
requiredColumns=[{5}]): rowcount = 1.0, cumulative cost = 4047.750000000001, id
= 63
LogicalCorrelate(correlation=[$cor0], joinType=[left],
requiredColumns=[{5, 7}]): rowcount = 1.0, cumulative cost =
4002.9000000000005, id = 52
LogicalJoin(condition=[true], joinType=[inner]): rowcount = 196.0,
cumulative cost = 224.0, id = 41
LogicalTableScan(table=[[CATALOG, SALES, EMP]]): rowcount = 14.0,
cumulative cost = 14.0, id = 22
LogicalTableScan(table=[[CATALOG, SALES, EMP]]): rowcount = 14.0,
cumulative cost = 14.0, id = 23
LogicalAggregate(group=[{}], agg#0=[MIN($0)]): rowcount = 1.0,
cumulative cost = 18.275000000000002, id = 50
LogicalProject($f0=[true]): rowcount = 1.05, cumulative cost =
17.150000000000002, id = 48
LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3],
HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8]): rowcount =
1.05, cumulative cost = 16.1, id = 46
LogicalFilter(condition=[AND(=($cor0.SAL, $5), <>($cor0.DEPTNO,
$7))]): rowcount = 1.05, cumulative cost = 15.05, id = 44
LogicalTableScan(table=[[CATALOG, SALES, EMP]]): rowcount =
14.0, cumulative cost = 14.0, id = 25
LogicalAggregate(group=[{}], agg#0=[MIN($0)]): rowcount = 1.0, cumulative
cost = 21.425000000000004, id = 61
LogicalProject($f0=[true]): rowcount = 2.1, cumulative cost =
20.300000000000004, id = 59
LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3],
HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8]): rowcount = 2.1,
cumulative cost = 18.200000000000003, id = 57
LogicalFilter(condition=[=($cor2.SAL, $5)]): rowcount = 2.1,
cumulative cost = 16.1, id = 55
LogicalTableScan(table=[[CATALOG, SALES, EMP]]): rowcount = 14.0,
cumulative cost = 14.0, id = 31
{noformat}
Plan after applying {{HepPlanner}} (stage before performing decorrelation
itself):
{noformat}
LogicalProject(SAL=[$5]): rowcount = 1.0, cumulative cost = 4051.8850000000007,
id = 97
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], $f0=[$18], $f019=[$19]): rowcount = 1.0,
cumulative cost = 4050.8850000000007, id = 124
LogicalFilter(condition=[IS NOT NULL($19)]): rowcount = 1.0, cumulative
cost = 4049.8850000000007, id = 132
LogicalCorrelate(correlation=[$cor2], joinType=[left],
requiredColumns=[{5}]): rowcount = 1.0, cumulative cost = 4048.8850000000007,
id = 130
LogicalFilter(condition=[AND(=($1, $10), IS NOT NULL($18))]): rowcount
= 1.0, cumulative cost = 4004.9000000000005, id = 126
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], $f0=[$18]): rowcount = 1.0, cumulative cost =
4003.9000000000005, id = 116
LogicalCorrelate(correlation=[$cor0], joinType=[left],
requiredColumns=[{5, 7}]): rowcount = 1.0, cumulative cost =
4002.9000000000005, id = 114
LogicalJoin(condition=[true], joinType=[inner]): rowcount =
196.0, cumulative cost = 224.0, id = 71
LogicalTableScan(table=[[CATALOG, SALES, EMP]]): rowcount =
14.0, cumulative cost = 14.0, id = 22
LogicalTableScan(table=[[CATALOG, SALES, EMP]]): rowcount =
14.0, cumulative cost = 14.0, id = 23
LogicalAggregate(group=[{}], agg#0=[MIN($0)]): rowcount = 1.0,
cumulative cost = 18.275000000000002, id = 80
LogicalProject($f0=[true]): rowcount = 1.05, cumulative cost =
17.150000000000002, id = 78
LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3],
HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8]): rowcount =
1.05, cumulative cost = 16.1, id = 76
LogicalFilter(condition=[AND(=($cor0.SAL, $5),
<>($cor0.DEPTNO, $7))]): rowcount = 1.05, cumulative cost = 15.05, id = 74
LogicalTableScan(table=[[CATALOG, SALES, EMP]]): rowcount
= 14.0, cumulative cost = 14.0, id = 25
LogicalAggregate(group=[{}], agg#0=[MIN($0)]): rowcount = 1.0,
cumulative cost = 21.425000000000004, id = 91
LogicalProject($f0=[true]): rowcount = 2.1, cumulative cost =
20.300000000000004, id = 89
LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3],
HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8]): rowcount = 2.1,
cumulative cost = 18.200000000000003, id = 87
LogicalFilter(condition=[=($cor2.SAL, $5)]): rowcount = 2.1,
cumulative cost = 16.1, id = 85
LogicalTableScan(table=[[CATALOG, SALES, EMP]]): rowcount =
14.0, cumulative cost = 14.0, id = 31
{noformat}
But some rules may be applied more times to receive more optimal plans because
for this case some joins and filters are split by correlates and after
decorrelating it is impossible to push those filters past aggregates.
> SubQuery with EXISTS clause creates redundant aggregate call
> ------------------------------------------------------------
>
> Key: CALCITE-2158
> URL: https://issues.apache.org/jira/browse/CALCITE-2158
> Project: Calcite
> Issue Type: Bug
> Reporter: Volodymyr Vysotskyi
> Assignee: Julian Hyde
> Priority: Major
>
> When {{SqlToRelConverter.Config.isExpand()}} returns true, subqueries are
> expanded in {{SqlToRelConverter}}.
> Then for the queries, like this:
> {code:sql}
> SELECT cs1.sal
> FROM emp cs1
> WHERE EXISTS
> (SELECT *
> FROM emp cs2
> WHERE cs1.sal = cs2.sal
> AND cs1.deptno <> cs2.deptno)
> {code}
> Calcite returns logical plan with excessive aggregate calls:
> {noformat}
> LogicalProject(SAL=[$5])
> LogicalFilter(condition=[IS NOT NULL($9)])
> LogicalCorrelate(correlation=[$cor0], joinType=[left],
> requiredColumns=[{5, 7}])
> LogicalTableScan(table=[[CATALOG, SALES, EMP]])
> LogicalAggregate(group=[{}], agg#0=[MIN($0)])
> LogicalProject($f0=[true])
> LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3],
> HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8])
> LogicalFilter(condition=[AND(=($cor0.SAL, $5), <>($cor0.DEPTNO,
> $7))])
> LogicalTableScan(table=[[CATALOG, SALES, EMP]])
> {noformat}
> But when {{SqlToRelConverter.Config.isExpand()}} returns false and
> SubQueryRemoveRule rules are applied to the logical plan with RexSubQuery,
> the resulting logical plan is correct and does not contain excessive
> aggregate calls:
> {noformat}
> LogicalProject(SAL=[$5])
> LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4],
> SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8])
> LogicalCorrelate(correlation=[$cor0], joinType=[inner],
> requiredColumns=[{5, 7}])
> LogicalTableScan(table=[[CATALOG, SALES, EMP]])
> LogicalAggregate(group=[{0}])
> LogicalProject(i=[true])
> LogicalFilter(condition=[AND(=($cor0.SAL, $5), <>($cor0.DEPTNO,
> $7))])
> LogicalTableScan(table=[[CATALOG, SALES, EMP]])
> {noformat}
> These cases may be observed using this unit test:
> {code:java}
> @Test public void testExistsExpand() {
> final HepProgram preProgram = HepProgram.builder()
> .addRuleInstance(SubQueryRemoveRule.FILTER)
> .addRuleInstance(SubQueryRemoveRule.PROJECT)
> .addRuleInstance(SubQueryRemoveRule.JOIN)
> .build();
> final HepProgram program = HepProgram.builder()
> .build();
> final String sql = "SELECT cs1.sal\n"
> + "FROM emp cs1 \n"
> + "WHERE EXISTS\n"
> + "(SELECT *\n"
> + "FROM emp cs2\n"
> + "WHERE cs1.sal = cs2.sal\n"
> + "AND cs1.deptno <> cs2.deptno)";
> sql(sql)
> .withDecorrelation(false)
> .withTrim(false)
> .expand(true) // change to false
> .withPre(preProgram)
> .with(program)
> .checkUnchanged();
> }
> {code}
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
