[
https://issues.apache.org/jira/browse/CALCITE-4886?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17443243#comment-17443243
]
Steven Phillips commented on CALCITE-4886:
------------------------------------------
I believe this is actually correct according to SQL standard, e.g. see
https://mariadb.com/kb/en/why-is-order-by-in-a-from-subquery-ignored/
> When converting SQL to RelNode, SqlOrderBy is missing in sub-query that
> contains SqlSetOperator
> -----------------------------------------------------------------------------------------------
>
> Key: CALCITE-4886
> URL: https://issues.apache.org/jira/browse/CALCITE-4886
> Project: Calcite
> Issue Type: Bug
> Components: core
> Affects Versions: 1.28.0
> Reporter: ZheHu
> Priority: Major
>
> When I run the following test in SqlToRelConverterTest.java:
> {code: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));
> }
> {code}
> The RelNode is:
> {code:java}
> LogicalProject(DEPTNO=[$0])
> LogicalUnion(all=[true])
> LogicalProject(DEPTNO=[$0])
> LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
> LogicalProject(DEPTNO=[$7])
> LogicalTableScan(table=[[CATALOG, SALES, EMP]])
> {code}
> I also tried INTERSECT and EXCEPT, which they all lost ORDER BY operator.
--
This message was sent by Atlassian Jira
(v8.20.1#820001)
