danny0405 commented on a change in pull request #2138:
URL: https://github.com/apache/calcite/pull/2138#discussion_r495677904
##########
File path: core/src/main/java/org/apache/calcite/sql2rel/SqlToRelConverter.java
##########
@@ -2807,34 +2756,109 @@ private boolean isSubQueryNonCorrelated(RelNode subq,
Blackboard bb) {
return Collections.emptyList();
}
- private RexNode convertJoinCondition(Blackboard bb,
+ private void convertJoin(Blackboard bb, SqlJoin join) {
+ final SqlValidatorScope scope = validator.getJoinScope(join);
+ final Blackboard fromBlackboard = createBlackboard(scope, null, false);
+ SqlNode left = join.getLeft();
+ SqlNode right = join.getRight();
+ final SqlValidatorScope leftScope =
+ Util.first(validator.getJoinScope(left),
+ ((DelegatingScope) bb.scope).getParent());
+ final Blackboard leftBlackboard =
+ createBlackboard(leftScope, null, false);
+ final SqlValidatorScope rightScope =
+ Util.first(validator.getJoinScope(right),
+ ((DelegatingScope) bb.scope).getParent());
+ final Blackboard rightBlackboard =
+ createBlackboard(rightScope, null, false);
+ convertFrom(leftBlackboard, left);
+ final RelNode leftRel = leftBlackboard.root;
+ convertFrom(rightBlackboard, right);
+ final RelNode tempRightRel = rightBlackboard.root;
+
+ final JoinConditionType conditionType = join.getConditionType();
+ final RexNode condition;
+ final RelNode rightRel;
+ if (join.isNatural()) {
+ condition = createNaturalJoinCondition(validator.getNamespace(left),
+ validator.getNamespace(right));
+ rightRel = tempRightRel;
+ } else if (conditionType == JoinConditionType.NONE) {
+ condition = rexBuilder.makeLiteral(true);
+ rightRel = tempRightRel;
Review comment:
Replace the if else with `switch case`.
##########
File path: core/src/main/java/org/apache/calcite/sql2rel/SqlToRelConverter.java
##########
@@ -2807,34 +2756,109 @@ private boolean isSubQueryNonCorrelated(RelNode subq,
Blackboard bb) {
return Collections.emptyList();
}
- private RexNode convertJoinCondition(Blackboard bb,
+ private void convertJoin(Blackboard bb, SqlJoin join) {
+ final SqlValidatorScope scope = validator.getJoinScope(join);
+ final Blackboard fromBlackboard = createBlackboard(scope, null, false);
+ SqlNode left = join.getLeft();
+ SqlNode right = join.getRight();
+ final SqlValidatorScope leftScope =
+ Util.first(validator.getJoinScope(left),
+ ((DelegatingScope) bb.scope).getParent());
+ final Blackboard leftBlackboard =
+ createBlackboard(leftScope, null, false);
+ final SqlValidatorScope rightScope =
+ Util.first(validator.getJoinScope(right),
+ ((DelegatingScope) bb.scope).getParent());
+ final Blackboard rightBlackboard =
+ createBlackboard(rightScope, null, false);
+ convertFrom(leftBlackboard, left);
+ final RelNode leftRel = leftBlackboard.root;
+ convertFrom(rightBlackboard, right);
+ final RelNode tempRightRel = rightBlackboard.root;
+
+ final JoinConditionType conditionType = join.getConditionType();
+ final RexNode condition;
+ final RelNode rightRel;
+ if (join.isNatural()) {
+ condition = createNaturalJoinCondition(validator.getNamespace(left),
+ validator.getNamespace(right));
+ rightRel = tempRightRel;
+ } else if (conditionType == JoinConditionType.NONE) {
+ condition = rexBuilder.makeLiteral(true);
+ rightRel = tempRightRel;
+ } else if (conditionType == JoinConditionType.USING) {
+ condition = createJoinUsingCondition(join,
+ validator.getNamespace(left),
+ validator.getNamespace(right));
+ rightRel = tempRightRel;
+ } else if (conditionType == JoinConditionType.ON) {
+ Pair<RexNode, RelNode> conditionAndRightNode =
createJoinOnCondition(fromBlackboard,
+ join,
+ leftRel,
+ tempRightRel);
+ condition = conditionAndRightNode.left;
+ rightRel = conditionAndRightNode.right;
+ } else {
+ throw Util.unexpected(conditionType);
+ }
+ final RelNode joinRel = createJoin(
+ fromBlackboard,
+ leftRel,
+ rightRel,
+ condition,
+ convertJoinType(join.getJoinType()));
+ bb.setRoot(joinRel, false);
+ }
+
+ private RexNode createNaturalJoinCondition(SqlValidatorNamespace
leftNamespace,
+ SqlValidatorNamespace rightNamespace) {
+ final List<String> columnList =
+ SqlValidatorUtil.deriveNaturalJoinColumnList(
+ catalogReader.nameMatcher(),
+ leftNamespace.getRowType(),
+ rightNamespace.getRowType());
+ return convertUsing(leftNamespace, rightNamespace, columnList);
+ }
+
+ private RexNode createJoinUsingCondition(SqlJoin join,
SqlValidatorNamespace leftNamespace,
- SqlValidatorNamespace rightNamespace,
- SqlNode condition,
- JoinConditionType conditionType,
+ SqlValidatorNamespace rightNamespace) {
+ SqlNode condition = join.getCondition();
+
+ final SqlNodeList list = (SqlNodeList) condition;
+ final List<String> nameList = new ArrayList<>();
+ for (SqlNode columnName : list) {
+ final SqlIdentifier id = (SqlIdentifier) columnName;
+ String name = id.getSimple();
+ nameList.add(name);
+ }
+ return convertUsing(leftNamespace, rightNamespace, nameList);
+ }
+
+ private Pair<RexNode, RelNode> createJoinOnCondition(Blackboard bb,
+ SqlJoin join,
RelNode leftRel,
RelNode rightRel) {
- if (condition == null) {
- return rexBuilder.makeLiteral(true);
- }
+ SqlNode condition = join.getCondition();
+
bb.setRoot(ImmutableList.of(leftRel, rightRel));
replaceSubQueries(bb, condition, RelOptUtil.Logic.UNKNOWN_AS_FALSE);
- switch (conditionType) {
- case ON:
- bb.setRoot(ImmutableList.of(leftRel, rightRel));
- return bb.convertExpression(condition);
- case USING:
- final SqlNodeList list = (SqlNodeList) condition;
- final List<String> nameList = new ArrayList<>();
- for (SqlNode columnName : list) {
- final SqlIdentifier id = (SqlIdentifier) columnName;
- String name = id.getSimple();
- nameList.add(name);
- }
- return convertUsing(leftNamespace, rightNamespace, nameList);
- default:
- throw Util.unexpected(conditionType);
- }
+ final RelNode newRightRel;
+ if (bb.root == null) {
+ newRightRel = rightRel;
+ } else {
+ bb.setRoot(rightRel, false);
+ List<BlackboardRegisterArgs> blackboardRegisterArgsList =
Review comment:
Why we set the `rightRel` as root not the `leftRel` ?
##########
File path: core/src/test/java/org/apache/calcite/test/SqlToRelConverterTest.java
##########
@@ -4029,6 +4029,88 @@ private Tester getExtendedTester() {
sql(sql).trim(true).ok();
}
+ @Test void testJoinExpandAndDecorrelation() {
+ String sql = ""
+ + "SELECT emp.deptno, emp.sal\n"
+ + "FROM dept\n"
+ + "JOIN emp ON emp.deptno = dept.deptno AND emp.sal < (\n"
+ + " SELECT AVG(emp.sal)\n"
+ + " FROM emp\n"
+ + " WHERE emp.deptno = dept.deptno\n"
+ + ")";
+ sql(sql)
+ .withConfig(configBuilder -> configBuilder
+ .withExpand(true)
+ .withDecorrelationEnabled(true))
+ .convertsTo("\n"
+ + "LogicalProject(DEPTNO=[$9], SAL=[$7])\n"
+ + " LogicalJoin(condition=[AND(=($9, $0), <($7, $0))],
joinType=[inner])\n"
+ + " LogicalTableScan(table=[[CATALOG, SALES, DEPT]])\n"
+ + " LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3],
HIREDATE=[$4], "
+ + "SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8], EXPR$0=[$10])\n"
+ + " LogicalJoin(condition=[=($0, $9)], joinType=[left])\n"
+ + " LogicalTableScan(table=[[CATALOG, SALES, EMP]])\n"
+ + " LogicalAggregate(group=[{0}], EXPR$0=[AVG($1)])\n"
Review comment:
Can we test the plan in the xml ?
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
[email protected]
