[GitHub] [calcite] hsyuan commented on a change in pull request #1995: [CALCITE-4012] Implement trait propagation for EnumerableHashJoin and…

[GitBox <gi...@apache.org>]

hsyuan commented on a change in pull request #1995:
URL: https://github.com/apache/calcite/pull/1995#discussion_r434623429



##########
File path: core/src/main/java/org/apache/calcite/adapter/enumerable/EnumerableHashJoin.java
##########
@@ -100,6 +106,53 @@ public static EnumerableHashJoin create(
         condition, variablesSet, joinType);
   }
 
+  @Override public Pair<RelTraitSet, List<RelTraitSet>> passThroughTraits(
+      final RelTraitSet required) {
+    RelCollation collation = required.getCollation();
+    if (collation == null || collation == RelCollations.EMPTY) {
+      return null;
+    }
+
+    List<Integer> requiredKeys = RelCollations.ordinals(collation);
+    ImmutableBitSet requiredKeySet = ImmutableBitSet.of(requiredKeys);
+
+    ImmutableBitSet leftKeySet = ImmutableBitSet.of(joinInfo.leftKeys);
+    ImmutableBitSet rightKeySet = ImmutableBitSet.of(joinInfo.rightKeys)
+        .shift(left.getRowType().getFieldCount());
+
+    // HashJoin traits passdown shall only consider left/right outer join.
+    // It is because for a hash-based implementation, only non-hashed side can
+    // preserve ordering, thus only for left/right outer join, we are sure which
+    // side is non-hashed side (the outer child).
+    if (joinType == JoinRelType.LEFT) {

Review comment:
       Then for FULL/RIGHT OUTER JOIN, we should prohibit trait propagations. 

##########
File path: core/src/test/java/org/apache/calcite/test/TopDownOptTest.java
##########
@@ -288,6 +290,44 @@
         .removeRule(EnumerableRules.ENUMERABLE_JOIN_RULE)
         .check();
   }
+
+  // Not push down sort for hash join in inner join case.
+  @Test void testHashJoinInnerJoinNotPushDownSort() {
+    final String sql = "select * from\n"
+        + "sales.emp r join sales.bonus s on r.ename=s.ename and r.job=s.job\n"
+        + "order by r.job desc nulls last, r.ename nulls first";
+    Query.create(sql)
+        .removeRule(EnumerableRules.ENUMERABLE_MERGE_JOIN_RULE)
+        .removeRule(EnumerableRules.ENUMERABLE_BATCH_NESTED_LOOP_JOIN_RULE)
+        .check();
+  }
+
+  // Not push down sort for hash join in full outer join case.
+  @Test void testHashJoinFullOuterJoinNotPushDownSort() {
+    final String sql = "select * from\n"
+        + "sales.emp r full outer join sales.bonus s on r.ename=s.ename and r.job=s.job\n"
+        + "order by r.job desc nulls last, r.ename nulls first";
+    Query.create(sql)
+        .removeRule(EnumerableRules.ENUMERABLE_MERGE_JOIN_RULE)
+        .removeRule(EnumerableRules.ENUMERABLE_BATCH_NESTED_LOOP_JOIN_RULE)
+        .check();
+  }
+
+  // Push down sort to left input.
+  @Test void testHashJoinLeftOuterJoinPushDownSort() {
+    final String sql = "select * from\n"
+        + "(select ename from sales.emp UNION ALL select ename from sales.emp UNION ALL select ename from sales.emp UNION ALL select ename from sales.emp UNION ALL select ename from sales.emp UNION ALL select ename from sales.emp UNION ALL select ename from sales.emp UNION ALL select ename from sales.emp UNION ALL select ename from sales.emp) r left outer join\n"
+        + "(select ename from sales.bonus UNION ALL select ename from sales.bonus UNION ALL select ename from sales.bonus UNION ALL select ename from sales.bonus UNION ALL select ename from sales.bonus UNION ALL select ename from sales.bonus UNION ALL select ename from sales.bonus UNION ALL select ename from sales.bonus UNION ALL select ename from sales.bonus UNION ALL select ename from sales.bonus) s\n"
+        + "on r.ename=s.ename\n"
+        + "order by r.ename nulls first";

Review comment:
       How about this:
   ```sql
   select * from
   (select distinct a, b from foo) foo
   join bar on foo.c = bar.c
   order by b desc, a desc; 
   ```




----------------------------------------------------------------
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:
users@infra.apache.org