[
https://issues.apache.org/jira/browse/CALCITE-4702?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17387898#comment-17387898
]
Stamatis Zampetakis commented on CALCITE-4702:
----------------------------------------------
The blog post implies that {{GROUP BY ()}} can be emulated by {{GROUP BY
constant}} so I was a bit misled thinking they are equivalent but Julian is
right; they are not.
Anyways {{GROUP BY ()}} "nothing" is not be the focus of this issue (I
shouldn't have mentioned it in the first place). The JIRA is about handling
correctly GROUP BY constant depending on the underlying DBMS. I think we should
detect when constants are present in the GROUP BY and if necessary replace them
with another equivalent expression. Replacement candidates can be:
* GROUP BY 'a'
* GROUP BY 'a' || 'b'
* GROUP BY true
* GROUP BY (SELECT 1)
* GROUP BY (SELECT 'a')
> 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: Stamatis Zampetakis
> Priority: Major
>
> 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}
> The {{GROUP BY}} constant essentially means {{GROUP BY}} nothing and 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.
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
