[ https://issues.apache.org/jira/browse/CALCITE-4702?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17414615#comment-17414615 ]
Julian Hyde commented on CALCITE-4702: -------------------------------------- [~zabetak], Your queries are all invalid. You need to change {{*}} to {{count(*)}}. > 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)