MogDB3.0兼容select子句非聚合列语法

原作者：杨明翰

在MySQL中如果不指定sql_mode 等于 ANSI或者ONLY_FULL_GROUP_BY时，select、having和order by 子句可以使用非聚合列。这种写法虽然不规范，也不能保证非聚合列返回数据的唯一性，但是在实际使用中还是比较常见的。

# SQL_mode不包含ANSI或者ONLY_FULL_GROUP_BY
mysql> select @@sql_mode;       
+--------------------------------------------+
| @@sql_mode                                 |
+--------------------------------------------+
| STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION |
+--------------------------------------------+
1 row in set (0.00 sec)

mysql> select * from t3;
+------+-------+------+
| id   | name  | age  |
+------+-------+------+
|    1 | andy  |   60 |
|    2 | lee   |   30 |
|    3 | yun   |   50 |
|    4 | tony  |   60 |
|    5 | candy |   30 |
|    6 | luss  |   50 |
+------+-------+------+
6 rows in set (0.00 sec)

mysql> select name,age from t3 group by age;
+------+------+
| name | age  |
+------+------+
| andy |   60 |
| lee  |   30 |
| yun  |   50 |
+------+------+
3 rows in set (0.00 sec)

这种在MySQL中“特殊”的语法，在PG、MogDB等数据库中执行会报错。导致迁移到其他数据库时业务侧需要做针对性的调整，增加了迁移的工作量。

##在MogDB中创建一个MySQL兼容性的数据库，执行相同SQL 报错。
MogDB=# create database db_m dbcompatibility ='B';
CREATE DATABASE
MogDB=# \c db_m
Non-SSL connection (SSL connection is recommended when requiring high-security)
You are now connected to database "db_m" as user "omm".
db_m=#  create table t3(id int ,name varchar(255),age int);
CREATE TABLE
db_m=# insert into t3 values (1,'andy',60),(2,'lee',30),(3,'yun',50), (4,'tony',60),(5,'candy',30),(6,'luss',50); 
INSERT 0 6
db_m=# select * from t3;
 id | name  | age 
----+-------+-----
  1 | andy  |  60
  2 | lee   |  30
  3 | yun   |  50
  4 | tony  |  60
  5 | candy |  30
  6 | luss  |  50
(6 rows)

db_m=# select name,age from t3 group by age;
ERROR:  column "t3.name" must appear in the GROUP BY clause or be used in an aggregate function
LINE 1: select name,age from t3 group by age;

为了解决这个常见需求，MogDB提供了dolphin 插件包，支持通过设置sql_mode兼容 select子句使用非聚合列的语法。下面进行测试

• 安装dolphin插件

su - omm
cd $GPHOME/script/
mkdir -p static
#下载对应版本的插件安装包
cd static/
wget -c https://cdn-mogdb.enmotech.com/mogdb-media/3.0.0/Plugins-3.0.0-CentOS-x86_64.tar.gz
chmod +x Plugins-3.0.0-CentOS-x86_64.tar.gz
[omm@node1 static]$ gs_install_plugin_local --all
SUCCESS: pg_trgm
SUCCESS: dblink
SUCCESS: orafce
SUCCESS: wal2json
SUCCESS: pg_repack
SUCCESS: pg_bulkload
SUCCESS: pg_prewarm
SUCCESS: dolphin
SUCCESS: whale
SUCCESS: postgis

注意：只有以MySQL兼容创建的数据库才能使用dolphin插件 否则会报错

MogDB=# CREATE EXTENSION dolphin;
ERROR:  please create extension "dolphin" with B type DBCOMPATIBILITY

以MySQL兼容模式创建数据库，安装dolphin插件

#安装前需要设置系统参数并重启数据库
MogDB=# alter system set shared_preload_libraries=dolphin;  
NOTICE:  please restart the database for the POSTMASTER level parameter to take effect.
ALTER SYSTEM SET

#以MySQL兼容模式创建数据库，安装dolphin脚本
MogDB=# create database db_mysql dbcompatibility = 'B';
CREATE DATABASE
MogDB=# \c db_mysql
Non-SSL connection (SSL connection is recommended when requiring high-security)
You are now connected to database "db_mysql" as user "omm".
db_mysql=# CREATE EXTENSION dolphin;
CREATE EXTENSION

dolphin插件提供了类似MySQL的sql_mode参数，可以通过调整sql_mode的配置实现更强的MySQL兼容性

## 默认为严格模式，在此模式下 非聚合列的查询会报错
db_mysql=# create table t3(id int ,name varchar(255),age int);
CREATE TABLE
db_mysql=# insert into t3 values (1,'andy',60),(2,'lee',30),(3,'yun',50), (4,'tony',60),(5,'candy',30),(6,'luss',50); 
INSERT 0 6
db_mysql=# select * from t3;
 id | name  | age 
----+-------+-----
  1 | andy  |  60
  2 | lee   |  30
  3 | yun   |  50
  4 | tony  |  60
  5 | candy |  30
  6 | luss  |  50
(6 rows)

db_mysql=# select name,age from t3 group by age;
ERROR:  column "t3.name" must appear in the GROUP BY clause or be used in an aggregate function
LINE 1: select name,age from t3 group by age;

我们将sql_mode置空，再来执行非聚合列的查询就可以正常返回结果。

db_mysql=# set sql_mode = '';
SET
db_mysql=# select name,age from t3 group by age;
 name | age 
------+-----
 andy |  60
 yun  |  50
 lee  |  30
(3 rows)

安装并使用dolphin插件可以显著的提升MySQL兼容性，除了非聚合列的查询外 对 空值的处理、timestamp的默认值设置 都可以做到和MySQL的处理逻辑一致。 有兴趣可以深入研究下。