[
https://issues.apache.org/jira/browse/CALCITE-7239?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
weihua zhang updated CALCITE-7239:
----------------------------------
Description:
{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 calcite 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
[prepareSql()|https://github.com/apache/calcite/blob/b6b3a068f5a524a18db7523a9a6db0a325fb390d/core/src/main/java/org/apache/calcite/prepare/Prepare.java#L229]
as following, we can recurrence the bug as in impala:
{code:java}
public PreparedResult prepareSql(
SqlNode sqlQuery,
SqlNode sqlNodeOriginal,
Class<?> runtimeContextClass,
SqlValidator validator,
boolean needsValidation) {
init(runtimeContextClass);
final SqlToRelConverter.Config config =
SqlToRelConverter.config()
.withTrimUnusedFields(false)
.withExpand(false);
final Holder<SqlToRelConverter.Config> configHolder = Holder.of(config);
Hook.SQL2REL_CONVERTER_CONFIG_BUILDER.run(configHolder);
final SqlToRelConverter sqlToRelConverter =
getSqlToRelConverter(validator, catalogReader, configHolder.get());
// Convert the valid AST into a logical plan
RelRoot root = sqlToRelConverter.convertQuery(sqlQuery, needsValidation,
true);
RelNode relNode = root.project();
RelNode subQueryRemovedPlan =
runProgram(
ImmutableList.of(
CoreRules.JOIN_SUB_QUERY_TO_CORRELATE,
CoreRules.PROJECT_SUB_QUERY_TO_CORRELATE,
CoreRules.FILTER_SUB_QUERY_TO_CORRELATE
),
relNode);
RelBuilder relBuilder =
RelFactories.LOGICAL_BUILDER.create(root.rel.getCluster(), catalogReader);
RelNode decorrelatedPlan =
RelDecorrelator.decorrelateQuery(subQueryRemovedPlan, relBuilder);
root = root.withRel(decorrelatedPlan);
return implement(root);
}
private RelNode runProgram(List<RelOptRule> rules, RelNode currentNode) {
HepProgramBuilder builder = new HepProgramBuilder();
builder.addRuleCollection(rules);
builder.addMatchOrder(HepMatchOrder.BOTTOM_UP);
HepPlanner planner = new HepPlanner(builder.build(),
currentNode.getCluster().getPlanner().getContext(),
false, null, RelOptCostImpl.FACTORY);
planner.setRoot(currentNode);
return planner.findBestExp();
}
{code}
decorrelatedPlan is following:
{code:java}
LogicalSort(sort0=[$0], dir0=[ASC])
LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4],
SAL=[$5], COMM=[$6], DEPTNO=[$7], DEPTNO0=[$8])
LogicalJoin(condition=[=($7, $9)], joinType=[left]) ///
<------------here------------
LogicalTableScan(table=[[scott, EMP]])
LogicalFilter(condition=[=($0, $3)])
LogicalProject(DEPTNO=[$0], DNAME=[$1], LOC=[$2], DEPTNO0=[$4])
LogicalJoin(condition=[=($3, $5)], joinType=[inner])
LogicalProject(DEPTNO=[$0], DNAME=[$1], LOC=[$2],
DEPTNO0=[CAST($0):DECIMAL(7, 2)])
LogicalTableScan(table=[[scott, DEPT]])
LogicalAggregate(group=[{0, 1}])
LogicalProject(DEPTNO=[$7], DEPTNO0=[$8])
LogicalJoin(condition=[>($8, $6)], joinType=[inner])
LogicalTableScan(table=[[scott, EMP]])
LogicalAggregate(group=[{0}])
LogicalProject(DEPTNO0=[CAST($0):DECIMAL(7, 2)])
LogicalTableScan(table=[[scott, DEPT]])
{code}
was:
{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
[prepareSql()|https://github.com/apache/calcite/blob/b6b3a068f5a524a18db7523a9a6db0a325fb390d/core/src/main/java/org/apache/calcite/prepare/Prepare.java#L229]
as following, we can recurrence the bug as in impala:
{code:java}
public PreparedResult prepareSql(
SqlNode sqlQuery,
SqlNode sqlNodeOriginal,
Class<?> runtimeContextClass,
SqlValidator validator,
boolean needsValidation) {
init(runtimeContextClass);
final SqlToRelConverter.Config config =
SqlToRelConverter.config()
.withTrimUnusedFields(false)
.withExpand(false);
final Holder<SqlToRelConverter.Config> configHolder = Holder.of(config);
Hook.SQL2REL_CONVERTER_CONFIG_BUILDER.run(configHolder);
final SqlToRelConverter sqlToRelConverter =
getSqlToRelConverter(validator, catalogReader, configHolder.get());
// Convert the valid AST into a logical plan
RelRoot root = sqlToRelConverter.convertQuery(sqlQuery, needsValidation,
true);
RelNode relNode = root.project();
RelNode subQueryRemovedPlan =
runProgram(
ImmutableList.of(
CoreRules.JOIN_SUB_QUERY_TO_CORRELATE,
CoreRules.PROJECT_SUB_QUERY_TO_CORRELATE,
CoreRules.FILTER_SUB_QUERY_TO_CORRELATE
),
relNode);
RelBuilder relBuilder =
RelFactories.LOGICAL_BUILDER.create(root.rel.getCluster(), catalogReader);
RelNode decorrelatedPlan =
RelDecorrelator.decorrelateQuery(subQueryRemovedPlan, relBuilder);
root = root.withRel(decorrelatedPlan);
return implement(root);
}
private RelNode runProgram(List<RelOptRule> rules, RelNode currentNode) {
HepProgramBuilder builder = new HepProgramBuilder();
builder.addRuleCollection(rules);
builder.addMatchOrder(HepMatchOrder.BOTTOM_UP);
HepPlanner planner = new HepPlanner(builder.build(),
currentNode.getCluster().getPlanner().getContext(),
false, null, RelOptCostImpl.FACTORY);
planner.setRoot(currentNode);
return planner.findBestExp();
}
{code}
decorrelatedPlan is following:
{code:java}
LogicalSort(sort0=[$0], dir0=[ASC])
LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4],
SAL=[$5], COMM=[$6], DEPTNO=[$7], DEPTNO0=[$8])
LogicalJoin(condition=[=($7, $9)], joinType=[left]) ///
<------------here------------
LogicalTableScan(table=[[scott, EMP]])
LogicalFilter(condition=[=($0, $3)])
LogicalProject(DEPTNO=[$0], DNAME=[$1], LOC=[$2], DEPTNO0=[$4])
LogicalJoin(condition=[=($3, $5)], joinType=[inner])
LogicalProject(DEPTNO=[$0], DNAME=[$1], LOC=[$2],
DEPTNO0=[CAST($0):DECIMAL(7, 2)])
LogicalTableScan(table=[[scott, DEPT]])
LogicalAggregate(group=[{0, 1}])
LogicalProject(DEPTNO=[$7], DEPTNO0=[$8])
LogicalJoin(condition=[>($8, $6)], joinType=[inner])
LogicalTableScan(table=[[scott, EMP]])
LogicalAggregate(group=[{0}])
LogicalProject(DEPTNO0=[CAST($0):DECIMAL(7, 2)])
LogicalTableScan(table=[[scott, DEPT]])
{code}
> 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
> Priority: Major
>
> {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 calcite 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
> [prepareSql()|https://github.com/apache/calcite/blob/b6b3a068f5a524a18db7523a9a6db0a325fb390d/core/src/main/java/org/apache/calcite/prepare/Prepare.java#L229]
> as following, we can recurrence the bug as in impala:
> {code:java}
> public PreparedResult prepareSql(
> SqlNode sqlQuery,
> SqlNode sqlNodeOriginal,
> Class<?> runtimeContextClass,
> SqlValidator validator,
> boolean needsValidation) {
> init(runtimeContextClass);
> final SqlToRelConverter.Config config =
> SqlToRelConverter.config()
> .withTrimUnusedFields(false)
> .withExpand(false);
> final Holder<SqlToRelConverter.Config> configHolder = Holder.of(config);
> Hook.SQL2REL_CONVERTER_CONFIG_BUILDER.run(configHolder);
> final SqlToRelConverter sqlToRelConverter =
> getSqlToRelConverter(validator, catalogReader, configHolder.get());
> // Convert the valid AST into a logical plan
> RelRoot root = sqlToRelConverter.convertQuery(sqlQuery, needsValidation,
> true);
> RelNode relNode = root.project();
> RelNode subQueryRemovedPlan =
> runProgram(
> ImmutableList.of(
> CoreRules.JOIN_SUB_QUERY_TO_CORRELATE,
> CoreRules.PROJECT_SUB_QUERY_TO_CORRELATE,
> CoreRules.FILTER_SUB_QUERY_TO_CORRELATE
> ),
> relNode);
> RelBuilder relBuilder =
> RelFactories.LOGICAL_BUILDER.create(root.rel.getCluster(), catalogReader);
> RelNode decorrelatedPlan =
> RelDecorrelator.decorrelateQuery(subQueryRemovedPlan, relBuilder);
> root = root.withRel(decorrelatedPlan);
> return implement(root);
> }
> private RelNode runProgram(List<RelOptRule> rules, RelNode currentNode) {
> HepProgramBuilder builder = new HepProgramBuilder();
> builder.addRuleCollection(rules);
> builder.addMatchOrder(HepMatchOrder.BOTTOM_UP);
> HepPlanner planner = new HepPlanner(builder.build(),
> currentNode.getCluster().getPlanner().getContext(),
> false, null, RelOptCostImpl.FACTORY);
> planner.setRoot(currentNode);
> return planner.findBestExp();
> }
> {code}
> decorrelatedPlan is following:
> {code:java}
> LogicalSort(sort0=[$0], dir0=[ASC])
> LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4],
> SAL=[$5], COMM=[$6], DEPTNO=[$7], DEPTNO0=[$8])
> LogicalJoin(condition=[=($7, $9)], joinType=[left]) ///
> <------------here------------
> LogicalTableScan(table=[[scott, EMP]])
> LogicalFilter(condition=[=($0, $3)])
> LogicalProject(DEPTNO=[$0], DNAME=[$1], LOC=[$2], DEPTNO0=[$4])
> LogicalJoin(condition=[=($3, $5)], joinType=[inner])
> LogicalProject(DEPTNO=[$0], DNAME=[$1], LOC=[$2],
> DEPTNO0=[CAST($0):DECIMAL(7, 2)])
> LogicalTableScan(table=[[scott, DEPT]])
> LogicalAggregate(group=[{0, 1}])
> LogicalProject(DEPTNO=[$7], DEPTNO0=[$8])
> LogicalJoin(condition=[>($8, $6)], joinType=[inner])
> LogicalTableScan(table=[[scott, EMP]])
> LogicalAggregate(group=[{0}])
> LogicalProject(DEPTNO0=[CAST($0):DECIMAL(7, 2)])
> LogicalTableScan(table=[[scott, DEPT]])
> {code}
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
