You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@calcite.apache.org by "Stamatis Zampetakis (Jira)" <ji...@apache.org> on 2021/07/26 17:18:00 UTC
[jira] [Created] (CALCITE-4702) Error when executing query with
GROUP BY constant via JDBC adapter
Stamatis Zampetakis created CALCITE-4702:
--------------------------------------------
Summary: 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
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)