[
https://issues.apache.org/jira/browse/CALCITE-4886?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17443244#comment-17443244
]
ZheHu commented on CALCITE-4886:
--------------------------------
Thanks for your explanation [~sphillips] ，I tried the above SQL in PG, and the
query plan does include SORT node, that's a little confusing.
{code:java}
Sort Key: dept.deptno DESC NULLS LAST
-> Append (cost=0.00..33.80 rows=920 width=4)
-> Seq Scan on dept (cost=0.00..14.10 rows=410 width=4)
-> Seq Scan on emp (cost=0.00..15.10 rows=510 width=4)
{code}
> 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)
