Why does Calcite change GROUP_CONCAT to LISTAGG?

Question

I built a RelNode using the following SQL:

 SELECT GROUP_CONCAT(ename ORDER BY ename DESC SEPARATOR 'a') FROM emp

and I used RelToSqlConverter to converter it to SQL. I get this SQL:

SELECT LISTAGG(`ename`, 'a') WITHIN GROUP (ORDER BY `ename` IS NULL DESC, `ename` DESC) FROM `emp`

But I want to get GROUP_CONCAT not LISTAGG.

Answer 1

Check https://issues.apache.org/jira/browse/CALCITE-4349

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