[
https://issues.apache.org/jira/browse/CALCITE-4702?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17412882#comment-17412882
]
Julian Hyde commented on CALCITE-4702:
--------------------------------------
I think there's a simpler solution.
First, all constants in the GROUP BY list (with the exception of positive
integer literals which are, depending on the dialect, interpreted as column
references) are equivalent. We can convert "GROUP BY x, true, 'foo', DATE
'1970-01-01'" to "GROUP BY x, true, true, true".
Second, we know that we can remove constants from the GROUP BY list as long as
it doesn't leave us with an empty GROUP BY. So, let's do that. "GROUP BY x,
true, 'a'" becomes "GROUP BY x".
The only remaining case is "GROUP BY true". This must return 0 rows if the
table is empty, 1 row otherwise. What SQL to generate to achieve this effect on
each dialect? I suggest:
* generate "GROUP BY true" on most dialects;
* generate "GROUP BY (SELECT TRUE)" on dialects in the PostgreSQL family;
* on MSSQL, "GROUP BY ()" has the right effect (relying on the fact that MSSQL
has non-standard behavior for "GROUP BY ()" on an empty table).
> Error when executing query with GROUP BY constant via JDBC adapter
> ------------------------------------------------------------------
>
> Key: CALCITE-4702
> URL: https://issues.apache.org/jira/browse/CALCITE-4702
> Project: Calcite
> Issue Type: Bug
> Components: core, jdbc-adapter
> Affects Versions: 1.27.0
> Reporter: Stamatis Zampetakis
> Assignee: Soumyakanti Das
> Priority: Major
> Labels: pull-request-available
> Time Spent: 10m
> Remaining Estimate: 0h
>
> The following functionally equivalent SQL queries are accepted by Calcite and
> produce a valid plan
> {noformat}
> select avg(salary) from employee group by true
> select avg(salary) from employee group by 'a'
> {noformat}
> but they may fail if they are executed via the JDBC adapter since not all
> DBMS allow grouping by constants expressions. Moreover, what works for one
> may not work for the other.
> +Examples+
> The {{GROUP BY TRUE}} query works in Postgres, and MySQL but fails in
> Redshift with the following exception:
> {noformat}
> com.amazon.redshift.util.RedshiftException: ERROR: non-integer constant in
> GROUP BY
> {noformat}
> The {{GROUP BY 'a'}} query works in MySQL but fails in Postgres with the
> following exception:
> {noformat}
> ERROR: non-integer constant in GROUP BY
> {noformat}
> +Edit:+
> The {{GROUP BY}} constant is similar to {{GROUP BY ()}} "nothing" but as
> shown in the discussion below they are not equivalent. There is a nice [blog
> post|https://blog.jooq.org/2018/05/25/how-to-group-by-nothing-in-sql/]
> listing some limitations of various DBMS when it comes to {{GROUP BY ()}}.
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
