Feedback on SQL Incompatibility When Upgrading from TiDB 5.0.4 to TiDB 7.1.0: Lessons Learned the Hard Way [Contributions Welcome]

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

Original topic: tidb-5.0.4升级到tidb7.1.0 sql 不兼容反馈,踩坑血泪史【欢迎大家补充】

| username: TiDBer_yyy

【TiDB Usage Environment】Production Environment
【TiDB Version】7.1.0

1. The results of JSON_OBJECT + if() in tidb 7.1.0 are inconsistent with tidb-5. tidb-5 returns true/false, while tidb-7 returns 0/1.

CREATE TABLE `tt2` (
  `I_ID` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Primary Key ID',
  `I_HAS_RECEIPT` tinyint(4) NOT NULL DEFAULT '0' COMMENT '0: No, 1: Yes',
  PRIMARY KEY (`I_ID`) /*T![clustered_index] CLUSTERED */
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin AUTO_INCREMENT=1 COMMENT='xx';

insert into tt2 values(1, 0),(2, 1);

select JSON_OBJECT('has_receipt',if(I_HAS_RECEIPT = 0, false, true))from tt2 limit 10;

2. group_concat(order by) needs to add order by at the temporary table or memory table level.

3. Upgrading the TIDB version requires DM to be upgraded simultaneously, otherwise it may cause JSON data incompatibility.

4. The integer comparison with date_sub in tidb-5 returns inconsistent results; tidb-5 returns true, tidb-7 returns false.

select  date_sub(date_sub("2023-09-22 00:00:00", interval 2 month),INTERVAL WEEKDAY(date_sub("2023-09-22 00:00:00", interval 2 month)) DAY)
 

5. During the upgrade process of ticdc in tidb-5, message pushing/synchronization will be blocked, resulting in no request data downstream.

| username: Fly-bird | Original post link

We didn’t encounter any issues when upgrading TiCDC. Is there a specific case for this?

| username: TiDBer_yyy | Original post link

Known bug: https://asktug.com/t/topic/693249

| username: ajin0514 | Original post link

Is it OMM?