Affects Version/s: None
Fix Version/s: None
Seems there is a bug in RemoveSortRule. For this query,
"select * from \"emps\" "
+ "order by \"emps\".\"deptno\""
If I add RemoveSortRule.INSTANCE to the planner's ruleset, then the SortRel will be removed, which means the result will not be ordered by "deptno".
Here is what I got before the fix for the query
"EnumerableProjectRel(empid=[$0], deptno=[$1], name=[$2], salary=[$3], commission=[$4])\n EnumerableTableAccessRel(table=[[hr, emps]])\n”
To re-create this problem, you can use the original version of RemoveSortRule, and run the PlannerTest.testSortPlan(). You will get the above RelNodes for the query.
I modify RemoveSortRule, and also add two test cases to test the query with duplicate sorts:
"select \"empid\" from ( "
+ "select * "
+ "from \"emps\" "
+ "order by \"emps\".\"deptno\") "
+ "order by \"deptno\"")
1. testDuplicateSortPlan() will verify if the new version of RemoveSortRule works.
2. testDuplicateSortPlanWORemoveSortRule() will verify the result without using REmoveSortRule.
Please take a look and see if there is any issue in the fix.