postgresql 聚合的暗坑

--对于聚合操作，pg约束是不严格的，比如如下sql中，group by 缺少 name，但也能执行
postgres=# select id,name ,count(*) from t group by id;
 id | name | count 
----+------+-------
  1 | bcd  |     1
  2 | abc  |     1
  
  
--现模拟如下  
create table t(id int,name varchar(20));
insert into t values(1,'abc'),(2,'bcd');   

--再次执行，不行了，说语法不对
postgres=# select id,name ,count(*) from t group by id;
ERROR:  column "t.name" must appear in the GROUP BY clause or be used in an aggregate function
LINE 1: select id,name ,count(*) from t group by id;


--添加主键约束，则能直行成功，说明pg进行了智能判断,在有唯一约束的前提下，当select的非聚合字段比如name是伴随id成对出现的时候，则pg允许
--如下：因为id是唯一的，id与name也是唯一的（两个字段必须是在同一个表中），故pg允许
postgres=# alter table t add primary key(id);      
ALTER TABLE
postgres=# select id,name ,count(*) from t group by id;
 id | name | count 
----+------+-------
  1 | bcd  |     1
  2 | abc  |     1
  
  
--创建t1表  
create table t1(id int,name varchar(20));
insert into t1 values(1,'abc'),(2,'bcd');   
alter table t1 add primary key(id);  
--因为t.id是唯一的，但t.id与t1.name并不是唯一的（两个字段不在同一个表中），所以会把语法错误
postgres=# select t.id,t1.name from t1,t where t1.id=t.id group by t.id;     
ERROR:  column "t1.name" must appear in the GROUP BY clause or be used in an aggregate function
LINE 1: select t.id,t1.name from t1,t where t1.id=t.id group by t.id...



--而对于mysql，当sql_mode不设置ONLY_FULL_GROUP_BY是，它并不限制group by字段的完整性
mysql> select id,name ,count(*) from t group by id;
+------+------+----------+
| id   | name | count(*) |
+------+------+----------+
|    1 | abc  |        1 |
|    2 | bcd  |        1 |
+------+------+----------+
2 rows in set (0.02 sec)
--设置ONLY_FULL_GROUP_BY
mysql> set sql_mode='ONLY_FULL_GROUP_BY';
Query OK, 0 rows affected (0.11 sec)
--group by 语法不全规范，报错
mysql> select id,name ,count(*) from t group by id;
ERROR 1055 (42000): Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'test.t.name' 
which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by