[
https://issues.apache.org/jira/browse/CALCITE-1016?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15051666#comment-15051666
]
Julian Hyde edited comment on CALCITE-1016 at 12/10/15 9:20 PM:
----------------------------------------------------------------
[~vlsi], I'd correct that to "as soon as you have a *non-empty*
group_by_clause, empty input results in empty output".
So, Oracle does not comply with the standard for the query {{select count( * )
from emp where 1 = 0 group by ()}}. Per the standard, it should return 1 row.
was (Author: julianhyde):
[~vlsi], I'd correct that to "as soon as you have a *non-empty*
group_by_clause, empty input results in empty output".
So, Oracle does not comply with the standard for the query {{select count(*)
from emp where 1 = 0 group by ()}}. Per the standard, it should return 1 row.
> What should "GROUP BY 1" return on an empty table?
> --------------------------------------------------
>
> Key: CALCITE-1016
> URL: https://issues.apache.org/jira/browse/CALCITE-1016
> Project: Calcite
> Issue Type: Bug
> Reporter: Julian Hyde
> Assignee: Julian Hyde
>
> What should "GROUP BY 1" return on an empty table? Calcite currently returns
> 0 rows. I am hearing claims that according to standard SQL it should return 1
> row.
> Here is what Oracle 11.2.0.2.0 does.
> {noformat}
> SQL> select count(*) from emp where 1 = 0;
> COUNT(*)
> ----------
> 0
> SQL> select count(*) from emp where 1 = 0 group by ();
> no rows selected
> SQL> select count(*) from emp where 1 = 0 group by 1;
> no rows selected
> SQL> select count(*) from emp;
> COUNT(*)
> ----------
> 14
> SQL> select count(*) from emp group by ();
> COUNT(*)
> ----------
> 14
> SQL> select count(*) from emp group by 1;
> COUNT(*)
> ----------
> 14
> {noformat}
> I had expected {code}select count(*) from emp where 1 = 0 group by (){code}
> would return 1 row, but it returns 0, like {code}group by 1{code}.
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
