[jira] [Created] (CALCITE-6652) RelDecorrelator can't decorrelate query with limit 1

[suibianwanwan (Jira)]

suibianwanwan created CALCITE-6652:
--------------------------------------

             Summary: RelDecorrelator can't decorrelate query with limit 1
                 Key: CALCITE-6652
                 URL: https://issues.apache.org/jira/browse/CALCITE-6652
             Project: Calcite
          Issue Type: Bug
            Reporter: suibianwanwan
            Assignee: suibianwanwan


The following query can't decorrelate by RelDecorrelate:
{code:java}
@Test void testDecorrelateFetchOne1() {
  final String query = "SELECT deptno FROM dept WHERE\n" +
      "1000 > (SELECT sal FROM emp WHERE dept.deptno = emp.deptno order by 
emp.sal limit 1)";
  sql(query).withRule(
          CoreRules.FILTER_SUB_QUERY_TO_CORRELATE)
      .withLateDecorrelate(true)
      .check();
} 

@Test void testDecorrelateFetchOne2() {
  final String query = "SELECT deptno, (SELECT sal FROM emp where dept.deptno = 
emp.deptno order by emp.sal limit 1) FROM dept";
  sql(query).withRule(
          CoreRules.PROJECT_SUB_QUERY_TO_CORRELATE)
      .withLateDecorrelate(true)
      .check();
}

@Test void testDecorrelateFetchOne3() {
  final String query = "SELECT deptno, (SELECT sal FROM emp where dept.deptno = 
emp.deptno order by emp.sal desc limit 1) FROM dept";
  sql(query).withRule(
          CoreRules.PROJECT_SUB_QUERY_TO_CORRELATE)
      .withLateDecorrelate(true)
      .check();
}{code}
is equivalent to the following query, and the following query can be 
decorrelated into a join, so we can do some appropriate conversions in 
RelDecorrelate:
{code:java}
@Test void testDecorrelateFetchOne1() {
  final String query = "SELECT deptno FROM dept WHERE\n" +
      "1000 > (SELECT min(sal) FROM emp WHERE dept.deptno = emp.deptno)";
  sql(query).withRule(
          CoreRules.FILTER_SUB_QUERY_TO_CORRELATE)
      .withLateDecorrelate(true)
      .check();
} 

@Test void testDecorrelateFetchOne2() {
  final String query = "SELECT deptno, (SELECT SINGLE_VALUE(sal) FROM emp where 
dept.deptno = emp.deptno) FROM dept";
  sql(query).withRule(
          CoreRules.PROJECT_SUB_QUERY_TO_CORRELATE)
      .withLateDecorrelate(true)
      .check();
}

@Test void testDecorrelateFetchOne3() {
  final String query = "SELECT deptno, (SELECT max(sal) FROM emp where 
dept.deptno = emp.deptno) FROM dept";
  sql(query).withRule(
          CoreRules.PROJECT_SUB_QUERY_TO_CORRELATE)
      .withLateDecorrelate(true)
      .check();
}{code}



--
This message was sent by Atlassian Jira
(v8.20.10#820010)