jamesstarr commented on a change in pull request #2138:
URL: https://github.com/apache/calcite/pull/2138#discussion_r496347171
##########
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:
If another part of the clause would filter out the particular rows, then
you would end up with extra rows.
For instance give query:
`
SELECT * FROM left_side
LEFT JOIN right_side ON exist(sub_query) AND some_other_clause
`
If the sub query cause multiple rows to produced on the left side, then
some_other_clause cause those rows to not joined, you will end up with repeated
rows. This is why a full outer join can produce erroneous results. The
solution would be to generate a decorrelated query that joins on all columns
columns used in the ON clause regardless if they are used in the subquery.
----------------------------------------------------------------
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]
