[CALCITE-4349] Support GROUP_CONCAT aggregate function for MySQL

• Type: Bug
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Fix Version/s: 1.27.0
• Labels: pull-request-available
• Assignee: Zhen Wang
• Reporter: Julian Hyde
• Created: 21/Oct/20 23:28
• Updated: 20/May/22 08:58
• Resolved: 16/Jan/21 21:39

Description

Support the GROUP_CONCAT aggregate function for MySQL. Here is the syntax:

GROUP_CONCAT([DISTINCT] expr [,expr ...]
             [ORDER BY {unsigned_integer | col_name | expr}
                 [ASC | DESC] [,col_name ...]]
             [SEPARATOR str_val])

 

GROUP_CONCAT is analogous to LISTAGG (see CALCITE-2754) (and also to BigQuery and PostgreSQL's STRING_AGG, see CALCITE-4335). For example, the query

SELECT deptno, GROUP_CONCAT(ename ORDER BY empno SEPARATOR ';')
FROM Emp
GROUP BY deptno

is equivalent to (and in Calcite's algebra would be desugared to)

SELECT deptno, LISTAGG(ename, ';') WITHIN GROUP (ORDER BY empno)
FROM Emp
GROUP BY deptno

Issue Links

• Blocked: CALCITE-5163 MysqlSqlDialect unparse LISTAGG aggregate function error
• relates to: CALCITE-4335 Aggregate functions for BigQuery
• relates to: CALCITE-2754 LISTAGG support
• links to: GitHub Pull Request #2311