[ https://issues.apache.org/jira/browse/CALCITE-1016?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Julian Hyde updated CALCITE-1016: --------------------------------- Description: What should "GROUP BY 1" return on an empty table? Calcite currently returns 0 rows. Does that comply with the SQL standard? 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}. was: 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}. > "GROUP BY constant" on empty relation should return 0 rows > ---------------------------------------------------------- > > 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. Does that comply with the SQL standard? > 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)