julianhyde/optiq — pull request #230 by jinfengni (2014-04-04 01:28:02+00:00)
Title: Fix bug in RemoveSort rule. Add unit test.

Hi Julian,

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.

Thanks!