[CALCITE-4886] When converting SQL to RelNode, SqlOrderBy is missing in sub-query that contains SqlSetOperator

• Type: Bug
• Status: Closed
• Priority: Major
• Resolution: Not A Problem
• Affects Version/s: 1.28.0
• Fix Version/s: 1.32.0
• Component/s: core
• Assignee: Unassigned
• Reporter: ZheHu
• Created: 14/Nov/21 06:45
• Updated: 10/Sep/22 08:42
• Resolved: 16/Nov/21 14:29

Description

When I run the following test in SqlToRelConverterTest.java:

@Test void testIntersectWithNulls() {
final String sql = ""
+ "select deptno from\n"
+ "(select deptno from dept\n"
+ "union all\n"
+ "select deptno from emp order by deptno) as alia";
final RelNode rel = tester.convertSqlToRel(sql).rel;
System.out.println(RelOptUtil.toString(rel));
}

The RelNode is:

LogicalProject(DEPTNO=[$0])
  LogicalUnion(all=[true])
    LogicalProject(DEPTNO=[$0])
      LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
    LogicalProject(DEPTNO=[$7])
      LogicalTableScan(table=[[CATALOG, SALES, EMP]])

I also tried INTERSECT and EXCEPT, which they all lost ORDER BY operator.

Issue Links

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