Julian Hyde created CALCITE-4723:
------------------------------------
Summary: Check whether JDBC adapter generates "GROUP BY ()"
against Oracle, DB2, MSSQL
Key: CALCITE-4723
URL: https://issues.apache.org/jira/browse/CALCITE-4723
Project: Calcite
Issue Type: Bug
Reporter: Julian Hyde
Oracle, DB2 and MSSQL have non-standard semantics for "GROUP BY ()". Standard
behavior is to always return one "grand total" row, but [Oracle, DB2 and MSSQL
return no rows if the input is
empty|https://blog.jooq.org/2018/05/25/how-to-group-by-nothing-in-sql/].
Calcite's semantics is that "GROUP BY ()" always returns one row, and the JDBC
adapter currently assumes that all back ends have the same semantics. On back
ends that have different semantics, some queries might be giving incorrect
results.
I suggest the following remedy:
* Add a {{SqlDialect}} method {{boolean omitGrandTotalOnEmptyInput()}}
* Run the test suite, and see whether we ever generate "GROUP BY ()" on one of
the affected dialects. Try to write a test case where we do this.
* Modify the dialects to generate safe SQL in these cases (possibly "GROUP BY
()", or possibly something else). As the above article notes, it is
particularly difficult to find SQL that works for MSSQL, because it bumps into
the no-constants rule (see CALCITE-4702)
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
