[CALCITE-3664] Sort in subquery is lost when converting SqlNode to Relnode

• Type: Bug
• Status: Closed
• Priority: Major
• Resolution: Not A Problem
• Assignee: Wang Yanlin
• Reporter: Wang Yanlin
• Created: 02/Jan/20 09:46
• Updated: 10/Aug/20 03:11
• Resolved: 03/Jan/20 08:38

Description

Lost sort in subquery when converting a SqlNode to Relnode, making its semantics changed.

 The test case to reproduce

// SqlToRelConverterTest
@Test public void testSortInSubquery() {
    final String sql = "select ename from (select ename from emp order by ename) a";
    sql(sql).ok();
  }

This test case will success with this plan.

<TestCase name="testSortInSubquery">
        <Resource name="sql">
            <![CDATA[select ename from (select ename from emp order by ename) a]]>
        </Resource>
        <Resource name="plan">
            <![CDATA[
LogicalProject(ENAME=[$1])
  LogicalTableScan(table=[[CATALOG, SALES, EMP]])
]]>
        </Resource>
    </TestCase>

Issue Links

• duplicates: CALCITE-4160 Add configuration to retain ORDER BY in sub-query
• is related to: CALCITE-2798 Optimizer should remove ORDER BY in sub-query, provided it has no LIMIT or OFFSET