[
https://issues.apache.org/jira/browse/CALCITE-4702?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Stamatis Zampetakis updated CALCITE-4702:
-----------------------------------------
Description:
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 ()}}.
was:
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.
> 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}
> +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)
