> 1055 - Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'xxxx.create_date' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

TiDb版本:4.04

问题描述:

1055 - Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column ‘tb1.create_date’ which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

语句如下:
select DATE_ADD(DATE_FORMAT(create_date,’%Y-%m-%d’),INTERVAL 2 Day) ,count(1) from tb1 where create_date>‘2020-09-01’ GROUP BY DATE_FORMAT(create_date,’%Y-%m-%d’)

以上语句会报 group by错误

但是 select DATE_ADD(create_date,INTERVAL 2 Day) ,count(1) from tb1 where create_date>‘2020-09-01’ GROUP BY create_date
又是可正常得到结果

select DATE_FORMAT(create_date,’%Y-%m-%d’) ,count(1) from tb1 where create_date>‘2020-09-01’ GROUP BY DATE_FORMAT(create_date,’%Y-%m-%d’)

也可得到正确结果

请问是有什么限制么?

  1. 麻烦上传下表结构,我们测试一下,多谢。
  2. 反馈下 sql_mode 当前配置

SELECT @@sql_mode;

ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

表结构:
CREATE TABLE tb1 (
id int(11) NOT NULL AUTO_INCREMENT,
create_date datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
userid int(11) NOT NULL DEFAULT 0,
amount int(11) DEFAULT 0,
PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin AUTO_INCREMENT=30001;

  1. 测试了下,mysql 会报同样的错

  2. 可以修改为: select DATE_ADD(DATE_FORMAT(create_date,’%Y-%m-%d’),INTERVAL 2 Day) ,count(1) from tb1 where create_date>‘2020-09-01’ GROUP BY DATE_ADD(DATE_FORMAT(create_date,’%Y-%m-%d’),INTERVAL 2 Day); 试试

OK 我没测试在mysql上,抱歉,你这个我晓得,再加一个子查询也是可以的

:+1:

请大神帮忙看下报错原因