[CALCITE-4636] Wrong RelCollation if a field in GROUP BY appears in ORDER BY of WITHIN GROUP clause

• Type: Bug
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Fix Version/s: 1.28.0
• Component/s: core
• Labels: pull-request-available
• Assignee: Unassigned
• Reporter: Rafay A
• Created: 03/Jun/21 18:39
• Updated: 20/Oct/21 05:25
• Resolved: 13/Jun/21 21:58

Description

If we run this query:

select regionkey, collect(regionkey)
within group (order by regionkey)
from (SELECT * FROM (VALUES('blah','blah','blah',1,1)) as TBL(f1,f2,f3,nationkey,regionkey))
group by regionkey

 

We see that we are ordering by the same field as the group by field (regionkey). Now the query may be non-sensicle as there is no point of collecting the same field with in the same group, but syntactically its a valid SQL and should produce correct plan, but it generates this plan instead:

LogicalAggregate(group=[{0}], EXPR$1=[COLLECT($0) WITHIN GROUP ([1])])
  LogicalProject(REGIONKEY=[$4], F1=[$0])
    LogicalProject(F1=[$0], F2=[$1], F3=[$2], NATIONKEY=[$3], REGIONKEY=[$4])
      LogicalValues(tuples=[[{ 'blah', 'blah', 'blah', 1, 1 }]])

 

If we run the following good query, it generates correct plan:

 

select nationkey, collect(regionkey)
within group (order by regionkey)
from (SELECT * FROM (VALUES('blah','blah','blah',1,1)) as TBL(f1,f2,f3,nationkey,regionkey))
group by regionkey

 

Generated plan:

LogicalAggregate(group=[{0}], EXPR$1=[COLLECT($1) WITHIN GROUP ([1])])
  LogicalProject(NATIONKEY=[$3], REGIONKEY=[$4])
    LogicalProject(F1=[$0], F2=[$1], F3=[$2], NATIONKEY=[$3], REGIONKEY=[$4])
      LogicalValues(tuples=[[{ 'blah', 'blah', 'blah', 1, 1 }]])

 

Issue Links

• links to: GitHub Pull Request #2423