lightdb 聚合函数支持order by

在数据库中，对于聚合函数来说，由于不管表中有多少条数据，select 聚合函数始终仅返回一条数据，故对其进行order by是没有意义的，在原生PG中也对该种行为进行了限制：

postgres=#
postgres=# select count(1) from t1 order by id;
2023-11-15 20:21:33.782 CST [77278] ERROR: column "t1.id" must appear in the GROUP BY clause or be used in an aggregate function at character 34
2023-11-15 20:21:33.782 CST [77278] STATEMENT: select count(1) from t1 order by id;
ERROR: column "t1.id" must appear in the GROUP BY clause or be used in an aggregate function
LINE 1: select count(1) from t1 order by id;
^
postgres=#

而在oracle中，则支持该种sql的执行：

*可以看出后面的order by也无实际意义

在lightdb 23.4版本oracle兼容模式中，兼容了oracle该种语法（即select语句中仅有聚合函数，对此支持order by语法），具体示例如下：

create table test_agg_func_table(id int primary key, co1 varchar(50), co2 numeric(10, 3));
create table test_agg_func_table2(a int primary key, b varchar(50), c numeric(10, 3), d text);
insert into test_agg_func_table values(1, 'hangzhou', 100.1), (5, 'beijing', 60), (3, 'hangzhou', 45.678);
insert into test_agg_func_table2 values(7, 'hello world', 12.3, '您好'), (500, 'I am fine', 23.45, '北京欢迎你aa'), (13, 'thank you, and you?', 100.1, '我是谁, why, 我在那里');

select id, co1 from test_agg_func_table order by co2;
id | co1
----+----------
3 | hangzhou
5 | beijing
1 | hangzhou
(3 rows)

select count(*) from test_agg_func_table order by id;
count
-------
3
(1 row)

select count(*), id from test_agg_func_table order by id;
ERROR: column "test_agg_func_table.id" must appear in the GROUP BY clause or be used in an aggregate function
LINE 1: select count(*), id from test_agg_func_table order by id;
^
select count(*), id from test_agg_func_table order by id, count(*);
ERROR: column "test_agg_func_table.id" must appear in the GROUP BY clause or be used in an aggregate function
LINE 1: select count(*), id from test_agg_func_table order by id, co...
^
select count(*) from test_agg_func_table order by count(*);
count
-------
3
(1 row)

select max(co1) from test_agg_func_table order by co2;
max
----------
hangzhou
(1 row)

select count(*), max(co1) from test_agg_func_table order by id;
count | max
-------+----------
3 | hangzhou
(1 row)

select count(*), max(co1), min(co2) from test_agg_func_table order by id;
count | max | min
-------+----------+--------
3 | hangzhou | 45.678
(1 row)

select count(*), max(co1), min(co2), id from test_agg_func_table order by id;
ERROR: column "test_agg_func_table.id" must appear in the GROUP BY clause or be used in an aggregate function
LINE 1: select count(*), max(co1), min(co2), id from test_agg_func_t...
^
select count(*), max(co1), min(co2) from test_agg_func_table order by id, co2;
count | max | min
-------+----------+--------
3 | hangzhou | 45.678
(1 row)

select count(*), max(co1), min(co2), co2 from test_agg_func_table order by id, co2;
ERROR: column "test_agg_func_table.co2" must appear in the GROUP BY clause or be used in an aggregate function
LINE 1: select count(*), max(co1), min(co2), co2 from test_agg_func_...
^
select count(*), max(co1), min(co2), co2 from test_agg_func_table order by id, co2, count(*);
ERROR: column "test_agg_func_table.co2" must appear in the GROUP BY clause or be used in an aggregate function
LINE 1: select count(*), max(co1), min(co2), co2 from test_agg_func_...
^
select count(*), max(co1), min(co2) from test_agg_func_table order by id, co2, count(*);
count | max | min
-------+----------+--------
3 | hangzhou | 45.678
(1 row)

select count(*), max(co1), min(co2) from test_agg_func_table order by count(*), sum(co3);
count | max | min
-------+----------+--------
3 | hangzhou | 45.678
(1 row)

select max(c),count(*),avg(a),sum(c),min(a),stddev(c),variance(c),max(c),count(*),avg(a),sum(c),min(a),stddev(c),variance(c),max(c),count(*),avg(a),sum(c),min(a),stddev(c),
variance(c),max(c),count(*),avg(a),sum(c),min(a),stddev(c),variance(c),max(c),count(*),avg(a),sum(c),min(a),stddev(c),variance(c),max(c),count(*),avg(a),sum(c),min(a),stddev(c),
variance(c),max(c),count(*),avg(a),sum(c),min(a),stddev(c),variance(c),max(c),count(*),avg(a),sum(c),min(a),stddev(c),variance(c),max(c),count(*),avg(a),sum(c),min(a),stddev(c),
variance(c),max(c),count(*),avg(a),sum(c),min(a),stddev(c),variance(c),max(c),count(*),avg(a),sum(c),min(a),stddev(c),variance(c),max(c),count(*),avg(a),sum(c),min(a),stddev(c),
variance(c),max(c),count(*),avg(a),sum(c),min(a),stddev(c),variance(c),max(c),count(*),avg(a),sum(c),min(a),stddev(c),variance(c) from test_agg_func_table2 order by c;
max | count | avg | sum | min | stddev | variance | max | count | avg | sum | min | stddev | variance | max | count | avg | sum | min | stddev | variance | max | count | avg | sum | min | stddev | variance | max | count | avg | sum | min | stddev | variance | max | count | avg | sum | min | stddev | variance | max | count | avg | sum | min | stddev | variance | max | count | avg | sum | min | stddev | variance | max | count | avg | sum | min | stddev | variance | max | count | avg | sum | min | stddev | variance | max | count | avg | sum | min | stddev | variance | max | count | avg | sum | min | stddev | variance | max | count | avg | sum | min | stddev | variance | max | count | avg | sum | min | stddev | variance
---------+-------+----------------------+---------+-----+---------------------+-----------------------+---------+-------+----------------------+---------+-----+---------------------+-----------------------+---------+-------+----------------------+---------+-----+---------------------+-----------------------+---------+-------+----------------------+---------+-----+---------------------+-----------------------+---------+-------+----------------------+---------+-----+---------------------+-----------------------+---------+-------+----------------------+---------+-----+---------------------+-----------------------+---------+-------+----------------------+---------+-----+---------------------+-----------------------+---------+-------+----------------------+---------+-----+---------------------+-----------------------+---------+-------+----------------------+---------+-----+---------------------+-----------------------+---------+-------+----------------------+---------+-----+---------------------+-----------------------+---------+-------+----------------------+---------+-----+---------------------+-----------------------+---------+-------+----------------------+---------+-----+---------------------+-----------------------+---------+-------+----------------------+---------+-----+---------------------+-----------------------+---------+-------+----------------------+---------+-----+---------------------+-----------------------
100.100 | 3 | 173.3333333333333333 | 135.850 | 7 | 47.7988580756207327 | 2284.7308333333333333 | 100.100 | 3 | 173.3333333333333333 | 135.850 | 7 | 47.7988580756207327 | 2284.7308333333333333 | 100.100 | 3 | 173.3333333333333333 | 135.850 | 7 | 47.7988580756207327 | 2284.7308333333333333 | 100.100 | 3 | 173.3333333333333333 | 135.850 | 7 | 47.7988580756207327 | 2284.7308333333333333 | 100.100 | 3 | 173.3333333333333333 | 135.850 | 7 | 47.7988580756207327 | 2284.7308333333333333 | 100.100 | 3 | 173.3333333333333333 | 135.850 | 7 | 47.7988580756207327 | 2284.7308333333333333 | 100.100 | 3 | 173.3333333333333333 | 135.850 | 7 | 47.7988580756207327 | 2284.7308333333333333 | 100.100 | 3 | 173.3333333333333333 | 135.850 | 7 | 47.7988580756207327 | 2284.7308333333333333 | 100.100 | 3 | 173.3333333333333333 | 135.850 | 7 | 47.7988580756207327 | 2284.7308333333333333 | 100.100 | 3 | 173.3333333333333333 | 135.850 | 7 | 47.7988580756207327 | 2284.7308333333333333 | 100.100 | 3 | 173.3333333333333333 | 135.850 | 7 | 47.7988580756207327 | 2284.7308333333333333 | 100.100 | 3 | 173.3333333333333333 | 135.850 | 7 | 47.7988580756207327 | 2284.7308333333333333 | 100.100 | 3 | 173.3333333333333333 | 135.850 | 7 | 47.7988580756207327 | 2284.7308333333333333 | 100.100 | 3 | 173.3333333333333333 | 135.850 | 7 | 47.7988580756207327 | 2284.7308333333333333
(1 row)

select max(x.m) from (select max(id) m, min(co1) n, count(*) from test_agg_func_table order by co2) x order by x.n;
max
-----
5
(1 row)

select max(x.m) from (select max(y.g) m, min(y.h) n, count(*) from (select sum(id) g, count(1) h, min(co1) f from test_agg_func_table order by co2) y order by y.f) x order by x.n;
max
-----
9
(1 row)

select count(*), max(co1) from test_agg_func_table x left join test_agg_func_table2 y on (x.id = y.a) order by x.co1;
count | max
-------+----------
3 | hangzhou
(1 row)

select count(*), max(x.co1), sum(y.c) from test_agg_func_table x left join( select count(a) a, max(b) b, avg(c) c from test_agg_func_table2 order by a) y on (x.id = y.a) order by x.co1;
count | max | sum
-------+----------+---------------------
3 | hangzhou | 45.2833333333333333
(1 row)