Uploaded image for project: 'Calcite'
  1. Calcite
  2. CALCITE-6652

RelDecorrelator can't decorrelate query with limit 1

    XMLWordPrintableJSON

Details

    • Bug
    • Status: Closed
    • Major
    • Resolution: Fixed
    • None
    • 1.39.0
    • None

    Description

      The following query can't decorrelate by RelDecorrelate:

      @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();
      }

      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:

      @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();
      }

      Attachments

        Issue Links

          Activity

            People

              suibianwanwan33 suibianwanwan
              suibianwanwan33 suibianwanwan
              Votes:
              0 Vote for this issue
              Watchers:
              6 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved: