amaliujia commented on a change in pull request #1995:
URL: https://github.com/apache/calcite/pull/1995#discussion_r434983306
##########
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:
Add an aggregation is not helping. The hash join still uses
`left_row_count * right_row_count * selectivity` to estimate cost while the
aggreagtion cannot give a big enough `left_row_count`.
----------------------------------------------------------------
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]
