After upgrading TiDB from 4.0.9 to 6.5.0, business encounters SQL compatibility errors

Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.

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

| username: shipo

[TiDB Usage Environment] Production Environment / Testing / Poc
[TiDB Version]
Upgraded from 4.0.9 to 6.5.0
[Reproduction Path] Operations performed that led to the issue
select adddate(‘1970-01-05’, interval (floor(datediff(current_date(), ‘1970-01-05’) / 7) * 7) + 7 day);
[Encountered Issue: Problem Phenomenon and Impact]
SQL execution error
[Resource Configuration]
[Attachments: Screenshots / Logs / Monitoring]


| username: dba-kit | Original post link

It looks like the alias adddate has been deprecated, and using date_add works successfully. (PS: Your original SQL used Chinese quotation marks…)

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)]>
| username: xingzhenxiang | Original post link

It should be a bug. My v6.5.1 is the same, but I can execute it step by step.

| username: dba-kit | Original post link

Indeed, directly writing the numbers can be executed.

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)
| username: shipo | Original post link

Thank you very much. The issue with the Chinese quotation marks might be due to copy-pasting. Additionally, I tried removing the outer parentheses of the floor function, and it executed normally.

> 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)
| username: dba-kit | Original post link

The simplest reproduction is that the value between the interval day in the adddate function cannot be enclosed in parentheses, while date_add can.

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)]>
| username: dba-kit | Original post link

It’s surprising that TiDB doesn’t just use aliases to implement these two functions.

| username: shipo | Original post link

MySQL is indeed a synonym.

| username: xingzhenxiang | Original post link

Make a suggestion for TiDB to use synonyms as well.