[CALCITE-4702] Error when executing query with GROUP BY constant via JDBC adapter

• Type: Bug
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Affects Version/s: 1.27.0
• Fix Version/s: 1.30.0
• Component/s: core, jdbc-adapter
• Labels: pull-request-available
• Assignee: Soumyakanti Das
• Reporter: Stamatis Zampetakis
• Created: 26/Jul/21 17:17
• Updated: 19/Mar/22 09:20
• Resolved: 03/Feb/22 11:56

Description

The following functionally equivalent SQL queries are accepted by Calcite and produce a valid plan

select avg(salary) from employee group by true
select avg(salary) from employee group by 'a'

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:

com.amazon.redshift.util.RedshiftException: ERROR: non-integer constant in GROUP BY

The GROUP BY 'a' query works in MySQL but fails in Postgres with the following exception:

ERROR:  non-integer constant in GROUP BY

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 listing some limitations of various DBMS when it comes to GROUP BY ().

Issue Links

• is related to: CALCITE-4723 Check whether JDBC adapter generates "GROUP BY ()" against Oracle, DB2, MSSQL
• is related to: CALCITE-1038 Obsolete AggregateConstantKeyRule
• links to: GitHub Pull Request #2482