weihua zhang created CALCITE-7239:
-------------------------------------
Summary: if no trim() after SqlToRelConverter.convertQuery, maybe
throw Exception in some cases
Key: CALCITE-7239
URL: https://issues.apache.org/jira/browse/CALCITE-7239
Project: Calcite
Issue Type: Bug
Reporter: weihua zhang
{code:sql}
SELECT e1.*, d.deptno
FROM emp e1
LEFT JOIN dept d
ON e1.deptno = d.deptno
AND d.deptno IN (
SELECT e3.deptno
FROM emp e3
WHERE d.deptno > e3.comm
)
ORDER BY e1.empno;
{code}
when https://issues.apache.org/jira/browse/CALCITE-7034 fixed, I want to check
this fix in impala (I use 1.41.0-snapshot locally), but throw a
RuntimeException:
{code:java}
Could not find a matching signature for call =($7, $9) {code}
[https://github.com/apache/impala/blob/master/java/calcite-planner/src/main/java/org/apache/impala/calcite/service/CalciteRelNodeConverter.java#L129]
Impala's convert() do not have trimmer.trim()
if I change convert() as following:
{code:java}
public RelNode convert(SqlNode validatedNode) {
SqlToRelConverter relConverter = new SqlToRelConverter(
viewExpander_,
sqlValidator_,
reader_,
cluster_,
ImpalaConvertletTable.INSTANCE,
SqlToRelConverter.config().withCreateValuesRel(false));
// Convert the valid AST into a logical plan
RelRoot root = relConverter.convertQuery(validatedNode, false, true);
RelNode relNode = root.project();
LogUtil.logDebug(relNode, "Plan after conversion from Abstract Syntax
Tree");
RelBuilder relBuilder = RelFactories.LOGICAL_BUILDER.create(cluster_,
reader_);
RelFieldTrimmer trimmer = new RelFieldTrimmer(sqlValidator_, relBuilder);
RelNode trimmedPlan = trimmer.trim(relNode);
RelNode subQueryRemovedPlan =
runProgram(
ImmutableList.of(
CoreRules.JOIN_SUB_QUERY_TO_CORRELATE,
CoreRules.PROJECT_SUB_QUERY_TO_CORRELATE,
CoreRules.FILTER_SUB_QUERY_TO_CORRELATE
),
trimmedPlan);
LogUtil.logDebug(subQueryRemovedPlan, "Plan after subquery removal phase");
RelNode decorrelatedPlan =
RelDecorrelator.decorrelateQuery(subQueryRemovedPlan, relBuilder);
LogUtil.logDebug(decorrelatedPlan, "Plan after subquery decorrelation
phase");
return decorrelatedPlan;
}
{code}
Impala can produce right result too.
but I think it's a bug in RelDecorrelator,
https://github.com/apache/calcite/blob/3543c3b7ad39a2aa6920e8e32a06d178b31971b5/core/src/main/java/org/apache/calcite/sql2rel/RelDecorrelator.java#L1763
following plans is no trim in convert()
{code:java}
[Plan after conversion from Abstract Syntax Tree]
LogicalSort(sort0=[$0], dir0=[ASC]), id = 13
LogicalProject(empno=[$0], ename=[$1], job=[$2], mgr=[$3], hiredate=[$4],
sal=[$5], comm=[$6], deptno=[$7], DEPTNO0=[$8]), id = 12
LogicalJoin(condition=[AND(=($7, $8), IN($8, {
LogicalProject(DEPTNO=[$7])
LogicalFilter(condition=[>(CAST($cor0.deptno0):DOUBLE, $6)])
LogicalTableScan(table=[[testdb, emp]])
}))], joinType=[left]), id = 9
LogicalTableScan(table=[[testdb, emp]]), id = 1
LogicalTableScan(table=[[testdb, dept]]), id = 3
{code}
{code:java}
[Plan after subquery removal phase]
LogicalSort(sort0=[$0], dir0=[ASC]), id = 20
LogicalProject(empno=[$0], ename=[$1], job=[$2], mgr=[$3], hiredate=[$4],
sal=[$5], comm=[$6], deptno=[$7], DEPTNO0=[$8]), id = 18
LogicalProject(empno=[$0], ename=[$1], job=[$2], mgr=[$3], hiredate=[$4],
sal=[$5], comm=[$6], deptno=[$7], deptno0=[$8], dname=[$9], loc=[$10]), id = 41
LogicalJoin(condition=[=($7, $8)], joinType=[left]), id = 39
LogicalTableScan(table=[[testdb, emp]]), id = 1
LogicalFilter(condition=[=($0, $3)]), id = 37
LogicalCorrelate(correlation=[$cor0], joinType=[inner],
requiredColumns=[{0}]), id = 35
LogicalTableScan(table=[[testdb, dept]]), id = 3
LogicalAggregate(group=[{0}]), id = 33
LogicalProject(DEPTNO=[$7]), id = 31
LogicalFilter(condition=[>(CAST($cor0.deptno):DOUBLE, $6)]), id
= 29
LogicalTableScan(table=[[testdb, emp]]), id = 1
{code}
{code:java}
Plan after removing Correlator
LogicalSort(sort0=[$0], dir0=[ASC])
LogicalProject(empno=[$0], ename=[$1], job=[$2], mgr=[$3], hiredate=[$4],
sal=[$5], comm=[$6], deptno=[$7], DEPTNO0=[$8])
LogicalProject(empno=[$0], ename=[$1], job=[$2], mgr=[$3], hiredate=[$4],
sal=[$5], comm=[$6], deptno=[$7], deptno0=[$8], dname=[$9], loc=[$10])
LogicalJoin(condition=[=($7, $8)], joinType=[left])
LogicalTableScan(table=[[testdb, emp]])
LogicalFilter(condition=[=($0, $3)])
LogicalCorrelate(correlation=[$cor0], joinType=[inner],
requiredColumns=[{0}])
LogicalTableScan(table=[[testdb, dept]])
LogicalAggregate(group=[{0}])
LogicalProject(DEPTNO=[$7])
LogicalFilter(condition=[>(CAST($cor0.deptno):DOUBLE, $6)])
LogicalTableScan(table=[[testdb, emp]])
{code}
{code:java}
[Plan after subquery decorrelation phase]
LogicalSort(sort0=[$0], dir0=[ASC]), id = 140
LogicalProject(empno=[$0], ename=[$1], job=[$2], mgr=[$3], hiredate=[$4],
sal=[$5], comm=[$6], deptno=[$7], DEPTNO0=[$8]), id = 138
LogicalJoin(condition=[=($7, $9)], joinType=[left]), id = 136
LogicalTableScan(table=[[testdb, emp]]), id = 1
LogicalFilter(condition=[=($0, $3)]), id = 134
LogicalProject(deptno=[$0], dname=[$1], loc=[$2], DEPTNO0=[$4]), id =
132
LogicalJoin(condition=[=($3, $5)], joinType=[inner]), id = 130
LogicalProject(deptno=[$0], dname=[$1], loc=[$2],
deptno0=[CAST($0):DOUBLE]), id = 114
LogicalTableScan(table=[[testdb, dept]]), id = 3
LogicalAggregate(group=[{0, 1}]), id = 128
LogicalProject(DEPTNO=[$7], deptno0=[$8]), id = 126
LogicalJoin(condition=[>($8, $6)], joinType=[inner]), id = 142
LogicalTableScan(table=[[testdb, emp]]), id = 1
LogicalAggregate(group=[{0}]), id = 120
LogicalProject(deptno0=[CAST($0):DOUBLE]), id = 118
LogicalTableScan(table=[[testdb, dept]]), id = 3
{code}
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
