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
d.dname,
d.deptno,
EXISTS (
SELECT 1
FROM emp e
WHERE e.deptno = d.deptno
) AS has_employees
FROM dept d;
```
or
```sql
SELECT
e.ename,
e.deptno,
e.deptno IN (
SELECT d.deptno
FROM dept d
WHERE d.loc IN ('NEW YORK', 'CHICAGO')
) AS is_in_major_city
FROM emp e;
```
--
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]
