问题描述
TiDB 版本从 5.1 升级到 6.5 后，相同 date_add 函数写法，升级前后执行结果不一致。
一开始的时候看到这个描述，第一个印象是个 bug。然后就拿着 SQL 进行复现分析。
问题分析
复现案例：
mysql 结果：
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 43
Server version: 8.0.35 MySQL Community Server - GPLCopyright (c) 2000, 2024, Oracle and/or its affiliates.Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.(root@10.110.66.172) [(none)]>select date_add('20240525',interval -1 month)-1;
+------------------------------------------+
| date_add('20240525',interval -1 month)-1 |
+------------------------------------------+
| 20240424 |
+------------------------------------------+
1 row in set (0.02 sec)(root@10.110.66.172) [(none)]>show warnings;
Empty set (0.00 sec)
tidb 结果：
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 611
Server version: 5.7.25-TiDB-v7.1.5 TiDB Server (Apache License 2.0) Community Edition, MySQL 5.7 compatibleCopyright (c) 2000, 2024, Oracle and/or its affiliates.Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.(root@127.0.0.1) [test]>select date_add('20240525',interval -1 month)-1;
+------------------------------------------+
| date_add('20240525',interval -1 month)-1 |
+------------------------------------------+
| 2023 |
+------------------------------------------+
1 row in set, 1 warning (0.00 sec)
(root@127.0.0.1) [test]>show warnings;
+---------+------+------------------------------------------------+
| Level | Code | Message |
+---------+------+------------------------------------------------+
| Warning | 1292 | Truncated incorrect DOUBLE value: '2024-04-25' |
+---------+------+------------------------------------------------+
1 row in set (0.00 sec)
这个表现的原因是：
TiDB 新版本有相关行为变化，属于 MySQL 兼容性问题。
具体 PR 可以见：https://github.com/pingcap/tidb/pull/28133
也就是将 date_add 返回值改成了 string 类型，导致当前现象。
绕过方法
(root@127.0.0.1) [test]>SELECT DATE_ADD(DATE('2024-05-25'), INTERVAL -1 MONTH) - INTERVAL 1 DAY;
+------------------------------------------------------------------+
| DATE_ADD(DATE('2024-05-25'), INTERVAL -1 MONTH) - INTERVAL 1 DAY |
+------------------------------------------------------------------+
| 2024-04-24 |
+------------------------------------------------------------------+
1 row in set (0.00 sec)
额外分析
实际上之前写法即使在 MySQL 中，也是不推荐写法，会导致结果不正确
(root@10.110.66.172) [(none)]>select date_add('20240101',interval -1 month)-1;
+------------------------------------------+
| date_add('20240101',interval -1 month)-1 |
+------------------------------------------+
| 20231200 |
+------------------------------------------+
1 row in set (0.00 sec)
(root@10.110.66.172) [(none)]>SELECT DATE_ADD(DATE('2024-01-01'), INTERVAL -1 MONTH) - INTERVAL 1 DAY;
+------------------------------------------------------------------+
| DATE_ADD(DATE('2024-01-01'), INTERVAL -1 MONTH) - INTERVAL 1 DAY |
+------------------------------------------------------------------+
| 2023-11-30 |
+------------------------------------------------------------------+
1 row in set (0.00 sec)
这里可以看到结果是不预期的。
原始 SQL 的主要问题是它使用了字符串 '20240101' 来表示日期，而不是使用日期类型。这使得代码难以理解，并且容易出错。
最终结论
- 相关表现预期，是 TiDB 新版本行为有变化：https://github.com/pingcap/tidb/pull/28133 (也就是将 date_add 返回值改成了 string 类型，导致当前现象)
- 原始 SQL 用法不推荐，可能在一些时间下结果不正确，推荐修改写法。