tidb 2.1.6版本,一张表date字段,插入1986-05-04,再group by这个字段报错。插入1986-05-03,1986-05-05都没有问题。
麻烦发下报错的 TiDB.log 日志情况,另外操作的 SQL 的具体语句,我们复现一下。
未发现异常,请确认 SQL_MODE 和 timezone 是否正确,另外 timezone 建议都是
mysql> select count(*) from t_1 group by start_data order by start_data;
+----------+
| count(*) |
+----------+
| 1 |
| 2 |
| 1 |
+----------+
3 rows in set (0.00 sec)
mysql> show create table t_1G
*************************** 1. row ***************************
Table: t_1
Create Table: CREATE TABLE `t_1` (
`start_data` date DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin
1 row in set (0.00 sec)
mysql> select * from t_1;
+------------+
| start_data |
+------------+
| 1986-05-04 |
| 1986-05-03 |
| 1986-05-04 |
| 1986-05-05 |
+------------+
4 rows in set (0.00 sec)
mysql> select tidb_version()G
*************************** 1. row ***************************
tidb_version(): Release Version: v2.1.6
Git Commit Hash: 66e639e8e127f71662f37287eb984dd84d169cba
Git Branch: HEAD
UTC Build Time: 2019-03-15 09:09:07
GoVersion: go version go1.11.2 linux/amd64
Race Enabled: false
TiKV Min Version: 2.1.0-alpha.1-ff3dd160846b7d1aed9079c389fc188f7f5ea13e
Check Table Before Drop: false
1 row in set (0.00 sec)
mysql> select session.sql_mode;
ERROR 1054 (42S22): Unknown column 'session.sql_mode' in 'field list'
mysql> select @@session.sql_mode;
+-------------------------------------------------------------------------------------------------------------------------------------------+
| @@session.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 |
+-------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> select @@session.time_zone;
+---------------------+
| @@session.time_zone |
+---------------------+
| SYSTEM |
+---------------------+
1 row in set (0.00 sec)
时区 东 8 区设置
[tidb@tidb-232 bin]$ date -R
Thu, 15 Aug 2019 11:47:20 +0800
[tidb@tidb-232 bin]$ date
Thu Aug 15 11:47:42 CST 2019
麻烦发一下 tidb.log 中响应的报错详细信息
初步原因:
老版本对 coprocessor 对时间处理的问题
验证方法:
explain 结果再反馈一下
处理方法:
考虑将 group by c1 改成 group by cast(c1 as date)
此话题已在最后回复的 1 分钟后被自动关闭。不再允许新回复。