[CALCITE-827] Calcite incorrectly permutes columns of OVER query

• Type: Bug
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Fix Version/s: 1.5.0
• Assignee: Julian Hyde
• Reporter: Sean Hsuan-Yi Chu
• Created: 03/Aug/15 00:27
• Updated: 27/Feb/24 22:24
• Resolved: 02/Sep/15 01:12

Description

To reproduce this issue, add ProjectToWindowRule to the Hep Planner.

Then, a query below would fail with an Assertion Error (type mis-match)

select count(*) over(partition by empno order by sal) as count1,
      count(*) over(partition by deptno order by sal) as count2, 
      sum(deptno)  over(partition by empno order by sal) as sum1
from emp

However, if the second and third columns are swapped, then it works:

select count(*) over(partition by empno order by sal) as count1,
      sum(deptno)  over(partition by empno order by sal) as sum1,
      count(*) over(partition by deptno order by sal) as count2
from emp

Essentially, the window functions which "share the same window definition" should be placed consecutively in the select-list; Otherwise, the current linking mechanism seems having some difficulty.