Aggregate Functions Support Scenario Extensions

Availability

This feature is available since MogDB 5.0.0.

Introduction

Support for Aggregate functions that include order by without group by.

Benefits

Enhance MogDB compatibility with Oracle to reduce application migration costs.

Description

For SQL query containing Aggregate function, if order by clause is included, the order by must be required to be sorted in the group by clause, otherwise it will directly report an error and exit. Therefore, if the group by clause is not included and the order by clause is included, the query will exit with a check failure. This feature adapts to support queries in this scenario.

Example

-- Ordinary Aggregate Functions
CREATE TABLE tt01(a INT, b INT);
SELECT SUM(a) FROM tt01 ORDER BY a;  --success
SELECT SUM(a) FROM tt01 ORDER BY b;  --success
SELECT SUM(a) FROM tt01 GROUP BY a ORDER BY a;  --success
SELECT SUM(a) FROM tt01 GROUP BY b ORDER BY a;  --failed
SELECT a, SUM(a) FROM tt01; --failed
SELECT b, SUM(a) FROM tt01; --failed
SELECT SUM(a) FROM tt01;  --success

-- Listagg Functions
CREATE TABLE tt02(deptno INT, name VARCHAR);
SELECT listagg(name, ',') within GROUP(ORDER BY name) FROM tt02 ORDER BY name;  --success
SELECT listagg(name, ',') within GROUP(ORDER BY name) FROM tt02 ORDER BY deptno; --success
SELECT listagg(name, ',') within GROUP(ORDER BY name) FROM tt02 GROUP BY deptno ORDER BY deptno;  --success
SELECT listagg(name, ',') within GROUP(ORDER BY name) FROM tt02 GROUP BY deptno ORDER BY name;    --failed
SELECT name, listagg(name, ',') within GROUP(ORDER BY name) FROM tt02;  --failed
SELECT deptno, listagg(name, ',') within GROUP(ORDER BY name) FROM tt02;  --failed
SELECT listagg(name, ',') within GROUP(ORDER BY name) FROM tt02;   --success