kingbase兼容mysql group by特色语法

sql标准不允许出现select列不在在group by列里，但是mysql老版本允许。
kingbase mysql模式下的数据库提供了参数可以配置是否兼容该特性

group by sql执行报错现象

test=# select relnamespace::regnamespace,relkind,count(*) from sys_class group by relkind;
ERROR:  column "sys_class.relnamespace" must appear in the GROUP BY clause or be used in an aggregate function
LINE 1: select relnamespace::regnamespace,relkind,count(*) from sys_...

确认数据库模式

test=# show database_mode ;
 database_mode
---------------
 mysql
(1 row)

确认数据库参数

test=# show sql_mode ;
            sql_mode
--------------------------------
 ONLY_FULL_GROUP_BY,ANSI_QUOTES
(1 row)

调整参数解决

1.会话级设置

test=# set sql_mode='ANSI_QUOTES';
SET
test=# select relnamespace::regnamespace,relkind,count(*) from sys_class group by relkind;
 relnamespace | relkind | count
--------------+---------+-------
 pg_catalog   | r       |   118
 pg_catalog   | v       |   443
 pg_toast     | i       |   233
 pg_toast     | t       |    49
 sysmac       | S       |     3

2.全局设置

test=# show sql_mode ;
            sql_mode
--------------------------------
 ONLY_FULL_GROUP_BY,ANSI_QUOTES
(1 row)

test=# alter system set sql_mode='ANSI_QUOTES';
ALTER SYSTEM
test=# select sys_reload_conf();
 sys_reload_conf
-----------------
 t
(1 row)

test=# show sql_mode ;
  sql_mode
-------------
 ANSI_QUOTES
(1 row)

test=# select relnamespace::regnamespace,relkind,count(*) from sys_class group by relkind;
 relnamespace | relkind | count
--------------+---------+-------
 pg_catalog   | r       |   118
 pg_catalog   | v       |   443
 pg_toast     | i       |   233
 pg_toast     | t       |    49
 sysmac       | S       |     3
(5 rows)