南大通用GBase 8a数据库集群查询报错 ERROR 1055 (42000): 'testdb.t2.c' isn't in GROUP BY

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)