[jira] [Commented] (CALCITE-4634) Improve AggregateProjectPullUpConstantsRule to remove all constant keys

["Julian Hyde (Jira)" <ji...@apache.org>]

    [ https://issues.apache.org/jira/browse/CALCITE-4634?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17356621#comment-17356621 ] 

Julian Hyde commented on CALCITE-4634:
--------------------------------------

Have you considered empty tables? {{GROUP BY ()}} produces one row, even on an empty table.

> Improve AggregateProjectPullUpConstantsRule to remove all constant keys
> -----------------------------------------------------------------------
>
>                 Key: CALCITE-4634
>                 URL: https://issues.apache.org/jira/browse/CALCITE-4634
>             Project: Calcite
>          Issue Type: Improvement
>          Components: core
>    Affects Versions: 1.16.0
>            Reporter: Yingyu
>            Priority: Major
>
> *AggregateProjectPullUpConstantsRule* removes constant keys from an Aggregate. However, this rules never removes the last column, because Aggregate([]) returns 1 row even if its input is empty. 
> The problem is if the remaining last column is a constant, it will fail on *SQL Server* with this error:
> {noformat}
> Each GROUP BY expression must contain at least one column that is not an outer reference.
> {noformat}
>  To improve on this we can improve the AggregateProjectPullUpConstantsRule to:
>  - Remove all constant keys from an Aggregate
>  - When no GROUP BY column is left, add a filter to the Aggregate([]) so that it returns empty dataset if its input is empty. 
>   
>  E.g. 
> {noformat}
> SELECT 'abc' FROM table_name GROUP BY ('abc')
> {noformat}
> will be translated to:
> {noformat}
> SELECT 'abc' FROM table_name GROUP BY () having count(*)>0
> {noformat}



--
This message was sent by Atlassian Jira
(v8.3.4#803005)