[jira] [Commented] (CALCITE-7828) Aggregate whose grouping sets include () returns no rows on empty input

["Weihua Zhang (Jira)" <ji...@apache.org>]

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

Weihua Zhang commented on CALCITE-7828:
---------------------------------------

oracle:
https://onecompiler.com/oracle/456t3xmtf
sql server:
https://onecompiler.com/sqlserver/456t3shrh


> Aggregate whose grouping sets include () returns no rows on empty input
> -----------------------------------------------------------------------
>
>                 Key: CALCITE-7828
>                 URL: https://issues.apache.org/jira/browse/CALCITE-7828
>             Project: Calcite
>          Issue Type: Bug
>          Components: core
>    Affects Versions: 1.42.0
>            Reporter: Aleksandr Efimov
>            Assignee: Yu Xu
>            Priority: Major
>              Labels: pull-request-available
>
> ROLLUP(a) is GROUPING SETS ((a), ()), and the () set yields one row even when the input is empty, as a query with no GROUP BY does (CALCITE-1059). Calcite returns no rows instead:
> {code:sql}
> SELECT a, COUNT(*) AS c FROM (VALUES (1)) AS t(a) WHERE FALSE GROUP BY ROLLUP(a);
> {code}
> The expected result is one row, (NULL, 0). CUBE(a, b) and GROUPING SETS ((a), ()) lose the row for () in the same way.
> Two places drop the row. {{PruneEmptyRules.AGGREGATE_INSTANCE}} replaces the aggregate with an empty {{Values}}, because its guard, {{Aggregate.isNotGrandTotal}}, only checks that the group count is greater than 0. With that rule removed, the plan keeps an {{EnumerableAggregate}} over the empty {{Values}}, but the query still returns no rows:
> {noformat}
> EnumerableAggregate(group=[{0}], groups=[[{0}, {}]], C=[COUNT()])
>   EnumerableValues(tuples=[[]])
> {noformat}
> Reproduced on 1.42.0 and on main (4e9460600c).



--
This message was sent by Atlassian Jira
(v8.20.10#820010)