MogDB distinct order by报错处理

原作者：张晓娟

• 适用范围
• 问题概述
• 问题原因
• 解决方案

适用范围

MogDB 5.0及以上版本

问题概述

• MySQL兼容性数据库下，执行SELECT语句出现报错
• 1）创建MySQL兼容性数据库

MogDB=# create database mysql DBCOMPATIBILITY 'B';
CREATE DATABASE


• 2）创建表及插入数据

MogDB=# \c mysql
Non-SSL connection (SSL connection is recommended when requiring high-security)
You are now connected to database "mysql" as user "omm".
mysql=# create table t (id int,name varchar(10));
CREATE TABLE
mysql=# insert into t values(1,'a'),(2,'b'),(3,'c');
INSERT 0 3
mysql=# insert into t values(1,'aa'),(2,'bbb'),(3,'cccc');
INSERT 0 3


• 3）执行select查询，出现报错

mysql=# select distinct id from t order by name;
ERROR: for SELECT DISTINCT, ORDER BY expressions must appear in select list
LINE 1: select distinct id from t order by name;


问题原因

• order by后面的出现字段或者表达式必须出现在select distinct中，这是数据库的默认标准行为。

解决方案

• dolphin Extension是Mogdb的MySQL兼容性数据库（dbcompatibility=’B’）扩展
• 修改dolphin.sql_mode参数，去掉sql_mode_full_group可以解决
• 1)查看当前的sql_mode

mysql=# show dolphin.sql_mode;
dolphin.sql_mode
------------------------------------------------------------------------------------------------------
sql_mode_strict,sql_mode_full_group,pipes_as_concat,ansi_quotes,no_zero_date,pad_char_to_full_length
(1 row)


• 2）修改sql_mode

mysql=# alter database mysql set dolphin.sql_mode to sql_mode_strict,pipes_as_concat,ansi_quotes,no_zero_date,pad_char_to_full_length;
ALTER DATABASE
mysql=# show dolphin.sql_mode;
dolphin.sql_mode
------------------------------------------------------------------------------------------------------
sql_mode_strict,sql_mode_full_group,pipes_as_concat,ansi_quotes,no_zero_date,pad_char_to_full_length
(1 row)


• 3）再次执行查询，仍然报错，需要重新连接数据库

mysql=# select distinct id from t order by name;
ERROR: for SELECT DISTINCT, ORDER BY expressions must appear in select list
LINE 1: select distinct id from t order by name;
^
mysql=# \c mysql
Non-SSL connection (SSL connection is recommended when requiring high-security)
You are now connected to database "mysql" as user "omm".
mysql=# select distinct id from t order by name;
id
----
1
2
3
(3 rows)


• 总结：修改dolphin.sql_mode后，只对修改后的新连接生效，当前连接不生效。