TiDB从4.0.9升级至6.5.0后业务出现SQL兼容性报错

🪐 TiDB 技术问题
1

【 TiDB 使用环境】生产环境 /测试/ Poc
【 TiDB 版本】
4.0.9升级至6.5.0
【复现路径】做过哪些操作出现的问题
select adddate(‘1970-01-05’,interval (floor(datediff(current_date(),‘1970-01-05’) / 7)*7)+7 day);
【遇到的问题：问题现象及影响】
SQL执行报错
【资源配置】
【附件：截图/日志/监控】

image
image1240×310 8.22 KB

image
image1696×211 8.2 KB

2

看起来adddate这个别名被取消了，用date_add能执行成功。(PS：你原始SQL是中文引号。。。)

MySQL [(none)]> select adddate(‘1970-01-05’,interval (floor(datediff(current_date(),‘1970-01-05’) / 7)*7)+7 day);
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your TiDB version for the right syntax to use line 1 column 103 near "day)"


MySQL [(none)]> select adddate('1970-01-05',interval (floor(datediff(current_date(),'1970-01-05') / 7)*7)+7 day);
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your TiDB version for the right syntax to use line 1 column 95 near "day)"
MySQL [(none)]> select date_add('1970-01-05',interval (floor(datediff(current_date(),'1970-01-05') / 7)*7)+7 day);
+--------------------------------------------------------------------------------------------+
| date_add('1970-01-05',interval (floor(datediff(current_date(),'1970-01-05') / 7)*7)+7 day) |
+--------------------------------------------------------------------------------------------+
| 2023-04-03                                                                                 |
+--------------------------------------------------------------------------------------------+
1 row in set (0.001 sec)

MySQL [(none)]>
3

应该是bug了，我的v6.5.1 也是这样，但是我分步执行可以

image
image1708×586 66.5 KB

4

确实，直接写数字是可以执行的的

MySQL [(none)]> select adddate('1970-01-05',interval 1 day);
+--------------------------------------+
| adddate('1970-01-05',interval 1 day) |
+--------------------------------------+
| 1970-01-06                           |
+--------------------------------------+
1 row in set (0.000 sec)
5

非常感谢，中文引号可能是复制粘贴的问题。另外，我试了下把floor外层的括号打开是可以正常执行的。

> root@tidb 15:38:06 [(none)]> select adddate('1970-01-05',interval floor(datediff(current_date(),'1970-01-05') / 7)*7+7 day);
> +-----------------------------------------------------------------------------------------+
> | adddate('1970-01-05',interval floor(datediff(current_date(),'1970-01-05') / 7)*7+7 day) |
> +-----------------------------------------------------------------------------------------+
> | 2023-04-03                                                                              |
> +-----------------------------------------------------------------------------------------+
> 1 row in set (0.00 sec)
6

最简复现，就是adddate函数的interval day之间的数值不能加括号，而date_add可以

MySQL [(none)]> select adddate('1970-01-05',interval (7) day);
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your TiDB version for the right syntax to use line 1 column 44 near "day)"
MySQL [(none)]> select date_add('1970-01-05',interval (7) day);
+-----------------------------------------+
| date_add('1970-01-05',interval (7) day) |
+-----------------------------------------+
| 1970-01-12                              |
+-----------------------------------------+
1 row in set (0.000 sec)

MySQL [(none)]> select adddate('1970-01-05',interval (7)+7 day);
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your TiDB version for the right syntax to use line 1 column 46 near "day)"
MySQL [(none)]> select date_add('1970-01-05',interval (7)+7 day);
+-------------------------------------------+
| date_add('1970-01-05',interval (7)+7 day) |
+-------------------------------------------+
| 1970-01-19                                |
+-------------------------------------------+
1 row in set (0.000 sec)

MySQL [(none)]>
2 个赞
7

TiDB实现这两个函数竟然还不是直接搞别名。。

8

mysql确实是同义词。

image
image1060×94 10 KB

9

提建议，让tidb 也使用同义词吧