[
https://issues.apache.org/jira/browse/CALCITE-4723?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=18003630#comment-18003630
]
Julian Hyde commented on CALCITE-4723:
--------------------------------------
Thanks for checking, [~nobigo]. The best way to solve this is to run the
generated SQL against each DB (the goal of CALCITE-5529) but you've done what
was asked, so I think this case can be closed.
> 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
> Assignee: xiong duan
> Priority: Major
>
> 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.20.10#820010)
