GBase 8a数据库集群，在处理group的select 列时，要求其必须是group的列或者聚合运算的列，否则会报isn't in GROUP BY的错误。
目录导航
原因：
select的列，不是group的列，也不是聚合列。在新版的85和所有86版本里，不再允许非group和聚合列出现在select里面。
gbase> desc t2;
+-------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| a | int(11) | YES | | NULL | |
| b | int(11) | YES | | NULL | |
| c | int(11) | YES | | NULL | |
+-------+---------+------+-----+---------+-------+
3 rows in set (Elapsed: 00:00:00.00)
gbase> select a,sum(b),c from t2 group by a;
ERROR 1055 (42000): 'testdb.t2.c' isn't in GROUP BY
解决：
a)去掉不符合要求的列。强烈建议如此操作。
b)恢复到老版本的兼容模式，但从语义上无法保证结果的一致性。
将集群参数SQL_MODE中的ONLY_FULL_GROUP_BY去掉。默认值如下：
PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ONLY_FULL_GROUP_BY,NO_AUTO_VALUE_ON_ZERO,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION,PAD_CHAR_TO_FULL_LENGTH
修改SQL_MODE的例子
数据
gbase> select * from a1;
+------+------+------------+--------+
| id | name | birthday | salary |
+------+------+------------+--------+
| 1 | 1111 | 2011-11-11 | 10000 |
| 2 | 2222 | 2011-12-12 | 20000 |
| 3 | 3333 | 2011-12-13 | 10000 |
+------+------+------------+--------+
3 rows in set (Elapsed: 00:00:00.01)查询效果
包含不符合条件的列时，直接报错 ERROR 1140 (42000): Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause
gbase> select max(salary) from a1;
+-------------+
| max(salary) |
+-------------+
| 20000 |
+-------------+
1 row in set (Elapsed: 00:00:00.02)
gbase> select id,name,birthday,max(salary) from a1;
ERROR 1140 (42000): Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause修改参数
gbase> show variables like '%sql_mode%';
+---------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Variable_name | Value |
+---------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| sql_mode | PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ONLY_FULL_GROUP_BY,NO_AUTO_VALUE_ON_ZERO,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION,PAD_CHAR_TO_FULL_LENGTH |
+---------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (Elapsed: 00:00:00.00)
gbase> set sql_mode='PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,NO_AUTO_VALUE_ON_ZERO,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION,PAD_CHAR_TO_FULL_LENGTH';
Query OK, 0 rows affected (Elapsed: 00:00:00.00)
gbase> show variables like '%sql_mode%';
+---------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Variable_name | Value |
+---------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| sql_mode | PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,NO_AUTO_VALUE_ON_ZERO,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION,PAD_CHAR_TO_FULL_LENGTH |
+---------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (Elapsed: 00:00:00.01)再次查询
不符合条件的列，可以查询出来了，但其只是从符合where和group分组的数据中，任意选一行返回，不确保顺序。
gbase> select id,name, max(birthday),salary from a1;
+------+------+---------------+--------+
| id | name | max(birthday) | salary |
+------+------+---------------+--------+
| 1 | 1111 | 2011-12-13 | 10000 |
+------+------+---------------+--------+
1 row in set (Elapsed: 00:00:00.03)
gbase> select id,name, max(birthday),salary from a1 group by salary;
+------+------+---------------+--------+
| id | name | max(birthday) | salary |
+------+------+---------------+--------+
| 1 | 1111 | 2011-12-13 | 10000 |
| 2 | 2222 | 2011-12-12 | 20000 |
+------+------+---------------+--------+
2 rows in set (Elapsed: 00:00:00.06)