iwanttobepowerful commented on code in PR #4619:
URL: https://github.com/apache/calcite/pull/4619#discussion_r2550510779
##########
core/src/test/java/org/apache/calcite/test/RelOptRulesTest.java:
##########
@@ -11413,4 +11413,208 @@ private void
checkLoptOptimizeJoinRule(LoptOptimizeJoinRule rule) {
})
.check();
}
+
+ @Test void testTopDownGeneralDecorrelateForFilterExists() {
+ final String sql = "select empno from emp where "
+ + "exists(select * from dept where dept.deptno = emp.deptno)";
+
+ sql(sql)
+ .withRule(
+ CoreRules.FILTER_SUBQUERY_REMOVE_ENABLE_MARK_JOIN,
+ CoreRules.PROJECT_MERGE,
+ CoreRules.PROJECT_REMOVE)
+ .withLateDecorrelate(true)
+ .withTopDownGeneralDecorrelate(true)
+ .check();
+ }
+
+ @Test void testTopDownGeneralDecorrelateForFilterSome() {
+ final String sql = "select empno from emp where "
+ + "empno > SOME(select empno from emp_b where emp.ename =
emp_b.ename)";
+
+ sql(sql)
+ .withRule(
+ CoreRules.FILTER_SUBQUERY_REMOVE_ENABLE_MARK_JOIN,
+ CoreRules.PROJECT_MERGE,
+ CoreRules.PROJECT_REMOVE)
+ .withLateDecorrelate(true)
+ .withTopDownGeneralDecorrelate(true)
+ .check();
+ }
+
+ @Test void testTopDownGeneralDecorrelateForFilterNotIn() {
+ final String sql = "select empno from emp where "
+ + "empno not in (select empno from emp_b where emp.ename =
emp_b.ename)";
+
+ sql(sql)
+ .withRule(
+ CoreRules.FILTER_SUBQUERY_REMOVE_ENABLE_MARK_JOIN,
+ CoreRules.PROJECT_MERGE,
+ CoreRules.PROJECT_REMOVE)
+ .withLateDecorrelate(true)
+ .withTopDownGeneralDecorrelate(true)
+ .check();
+ }
+
+ @Test void testTopDownGeneralDecorrelateForFilterNotExists() {
+ final String sql = "select empno from emp where "
+ + "not exists(select * from emp_b where emp.ename = emp_b.ename)";
+
+ sql(sql)
+ .withRule(
+ CoreRules.FILTER_SUBQUERY_REMOVE_ENABLE_MARK_JOIN,
+ CoreRules.PROJECT_MERGE,
+ CoreRules.PROJECT_REMOVE)
+ .withLateDecorrelate(true)
+ .withTopDownGeneralDecorrelate(true)
+ .check();
+ }
+
+ @Test void testTopDownGeneralDecorrelateForFilterScalar() {
+ final String sql = "select empno from emp where "
+ + "sal > (select avg(sal) from emp_b where emp.ename = emp_b.ename)";
+
+ sql(sql)
+ .withRule(
+ CoreRules.FILTER_SUBQUERY_REMOVE_ENABLE_MARK_JOIN,
+ CoreRules.PROJECT_MERGE,
+ CoreRules.PROJECT_REMOVE)
+ .withLateDecorrelate(true)
+ .withTopDownGeneralDecorrelate(true)
+ .check();
+ }
+
+ @Test void testTopDownGeneralDecorrelateForSubqueryWithSetOp() {
+ final String sql = "select empno, (select sum(deptno) from ("
+ + "select deptno from emp_b where emp.empno = emp_b.empno "
+ + "union all select deptno from empnullables where emp.empno =
empnullables.empno))"
+ + " from emp";
+
+ sql(sql)
+ .withRule(
+ CoreRules.PROJECT_SUBQUERY_REMOVE_ENABLE_MARK_JOIN,
Review Comment:
Could we add more test cases for PROJECT_SUBQUERY_REMOVE_ENABLE_MARK_JOIN?
For example:
```sql
SELECT dept.deptno, EXISTS ( SELECT 1 FROM emp e WHERE e.deptno =
dept.deptno ) AS has_employees FROM dept
```
or
```sql
SELECT emp.deptno, emp.deptno IN (SELECT deptno FROM dept where name
IN('Sales', 'Engineering')) FROM emp
```
or
```sql
SELECT emp.deptno, emp.deptno IN (SELECT dept.deptno FROM dept where
dept.deptno < emp.empno ) FROM emp
```
--
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.
To unsubscribe, e-mail: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
