[GitHub] [calcite] HanumathRao commented on a diff in pull request #3193: [CALCITE-5683] Two level nested correlated subquery throws an excepti…

["HanumathRao (via GitHub)" <gi...@apache.org>]

HanumathRao commented on code in PR #3193:
URL: https://github.com/apache/calcite/pull/3193#discussion_r1227350034


##########
core/src/test/java/org/apache/calcite/test/RelOptRulesTest.java:
##########
@@ -6823,6 +6823,50 @@ private void checkSemiJoinRuleOnAntiJoin(RelOptRule rule) {
         .checkUnchanged();
   }
 
+  /** Test case for CALCITE-5683 for two level nested decorrelate with standard program
+   * failing during the decorrelation phase. */
+  @Test void testTwoLevelDecorrelate() {
+    final String sql = "SELECT d1.name, d1.deptno + "
+        + " ( SELECT e1.empno "
+        + " FROM emp e1 "
+        + " WHERE d1.deptno = e1.deptno and "
+        + "       e1.sal = (SELECT max(sal) "
+        + "                 FROM emp e2 "
+        + "                 WHERE   e1.sal = e2.sal and"
+        + "                         e1.deptno = e2.deptno and"
+        + "                         d1.deptno < e2.deptno))"
+        + " FROM dept d1";
+
+    sql(sql)
+        .withExpand(false)
+        .withLateDecorrelate(true)
+        .withSubQueryRules()
+        .withTrim(true)
+        .withRule()
+        .checkUnchanged();
+  }
+
+  /** Test case for CALCITE-5683 for two level nested decorrelate with standard program
+   * failing during the decorrelation phase. */
+  @Test void testTwoLevelDecorrelateSkipInBetween() {

Review Comment:
   By SkipInBetween, I actually mean't that the correlation variable is not used at the first level and directly used at the deeper level (here second level depth). I wanted to make sure that even if there is no correlation used in the intermediate levels, the solution works.



-- 
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: commits-unsubscribe@calcite.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org