[
https://issues.apache.org/jira/browse/CALCITE-4723?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17397975#comment-17397975
]
duan xiong edited comment on CALCITE-4723 at 8/12/21, 10:27 AM:
----------------------------------------------------------------
[~julianhyde] What I understand about this issue need to do is by add
omitGrandTotalOnEmptyInput property, we can try to make the differt database
return the same answer(always return one "grand total" row). for example MSSQL:
{code:java}
create table tab(v varchar(20));
select count(v) from tab group by ();
{code}
return 0 record. Then need to Change RelToSqlConvert make sure RelNode change
to Sql became:
{code:java}
select count(v) from tab{code}
return 1 record. If I misunderstand, please let me know.
was (Author: nobigo):
[~julianhyde] What I understand about this issue need to do is by add
omitGrandTotalOnEmptyInput property, we can try to make the differt database
return the same answer(always return one "grand total" row). for example MSSQL:
{code:java}
create table tab(v varchar(20));
select count(v) from tab group by ();
{code}
> 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: duan xiong
> 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.3.4#803005)
