Rafay A created CALCITE-4636:
--------------------------------
Summary: Wrong RelCollation if Group by field appears in Order by
clause
Key: CALCITE-4636
URL: https://issues.apache.org/jira/browse/CALCITE-4636
Project: Calcite
Issue Type: Bug
Components: core
Reporter: Rafay A
If we run this query:
{code:java}
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
{code}
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:
{code:java}
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 }]])
{code}
If we run the following *good* query, it generates correct plan:
{code:java}
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{code}
Generated plan:
{noformat}
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 }]]){noformat}
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
