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

["duan xiong (Jira)" <ji...@apache.org>]

    [ https://issues.apache.org/jira/browse/CALCITE-4886?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17443326#comment-17443326 ] 

duan xiong edited comment on CALCITE-4886 at 11/14/21, 12:35 PM:
-----------------------------------------------------------------

According to SQL Std 2006:
{noformat}
Table -> An unordered collection of rows...{noformat}
And I think The user should not expect the sorted results by adding an order list in a subquery. Although MySQL and Oracle all can get the sorted results too.


was (Author: nobigo):
According to Sql Std 2006:
{noformat}
Table -> An unordered collection of rows...{noformat}
And I think The user should not expect the sorted results by adding an order list in a subquery. Although MySQL and Oracle all can get the sorted results.

> 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)