LightDB24.1新特性oracle模式 支持聚合函数表达式+order by

Oracle中支持聚合后接order by语句，虽然count(*) + xx 运算order by 这样order by本来没有意义，反而增加性能开销，做count聚合本身加和不加order by 结果集是一样的，但是仍需支持该写法。

下面是PostgreSQL模式行为

lightdb@test=# select count(*) from emp order by empno;
ERROR:  column "emp.empno" must appear in the GROUP BY clause or be used in an aggregate function
LINE 1: select count(*) from emp order by empno;
                                          ^

lightdb@test=# select count(*) from emp group by empno order by ename;
ERROR:  column "emp.ename" must appear in the GROUP BY clause or be used in an aggregate function
LINE 1: select count(*) from emp group by empno order by ename;

复制

LightDB24.1版本Oracle模式开始支持改用法

lightdb@test=# select count(*) from emp order by empno;
 count 
-------
    14
(1 row)


lightdb@test=# select count(*) from emp group by empno order by ename;
 count 
-------
     1
     1
     1
     1
     1
     1
     1
     1
     1
     1
     1
     1
     1
     1
(14 rows)

lightdb@test=# select count(*) + 1 from emp group by empno order by empno;
 ?column? 
----------
        2
        2
        2
        2
        2
        2
        2
        2
        2
        2
        2
        2
        2
        2
(14 rows)



复制