sync-diff-inspector : When the range condition is of timestamp type, the time zone cannot be converted correctly

Bug Report

Please answer these questions before submitting your issue. Thanks!

What did you do? 上游：

mysql> show variables like '%zone%';
+------------------+---------------+
| Variable_name    | Value         |
+------------------+---------------+
| system_time_zone | Asia/Shanghai |
| time_zone        | SYSTEM        |
+------------------+---------------+
2 rows in set (0.00 sec)
mysql> show create table test1;
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------+
| test1 | CREATE TABLE `test1` (
`id` int(11) DEFAULT NULL,
`create_time` timestamp NULL DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)
mysql> select * from test1;
+------+---------------------+
| id   | create_time         |
+------+---------------------+
|    1 | 2023-03-29 00:00:00 |
|    2 | 2023-03-20 00:00:00 |
|    3 | 2023-03-20 20:30:00 |
|    4 | 2023-03-20 22:30:00 |
+------+---------------------+
4 rows in set (0.00 sec)
mysql> select * from test1 where create_time<='2023-03-20 20:30:00';
+------+---------------------+
| id   | create_time         |
+------+---------------------+
|    2 | 2023-03-20 00:00:00 |
|    3 | 2023-03-20 20:30:00 |
+------+---------------------+
2 rows in set (0.00 sec)

下游

mysql> show variables like '%zone%';
+------------------+---------------+
| Variable_name    | Value         |
+------------------+---------------+
| system_time_zone | Asia/Shanghai |
| time_zone        | SYSTEM        |
+------------------+---------------+
2 rows in set (0.00 sec)
mysql> show create table test1_d;
+---------+---------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table   | Create Table                                                                                                                                                  |
+---------+---------------------------------------------------------------------------------------------------------------------------------------------------------------+
| test1_d | CREATE TABLE `test1_d` (
  `id` int(11) DEFAULT NULL,
  `create_time` timestamp NULL DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin |
+---------+---------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> select * from test1_d;
+------+---------------------+
| id   | create_time         |
+------+---------------------+
|    1 | 2023-03-29 00:00:00 |
|    2 | 2023-03-20 00:00:00 |
|    3 | 2023-03-20 20:30:00 |
|    4 | 2023-03-22 20:30:00 |
+------+---------------------+
4 rows in set (0.00 sec)
mysql> select * from test1_d where create_time<='2023-03-20 20:30:00';
+------+---------------------+
| id   | create_time         |
+------+---------------------+
|    2 | 2023-03-20 00:00:00 |
|    3 | 2023-03-20 20:30:00 |
+------+---------------------+
2 rows in set (0.00 sec)

config 文件

[tidb@172-16-120-238 hwtest]$ more syncdiff_config_test.toml
# Diff Configuration.

######################### Global config #########################

# 检查数据的线程数量，上下游数据库的连接数会略大于该值
check-thread-count = 4

# 如果开启，若表存在不一致，则输出用于修复的 SQL 语句。
export-fix-sql = true

# 只对比表结构而不对比数据
check-struct-only = false

######################### Datasource config #########################
[data-sources]
[data-sources.hwtest] # mysql1 是该数据库实例唯一标识的自定义 id，用于下面 task.source-instances/t
ask.target-instance 中
    host = "10.2.8.3"
    port = 24000
    user = "root"
    password = "" # 设置连接上游数据库的密码，可为明文或 Base64 编码。

    #（可选）使用映射规则来匹配上游多个分表，其中 rule1 和 rule2 在下面 Routes 配置栏中定义
    route-rules = ["rule1"]

[data-sources.hwtest_d]
    host = "10.2.8.3"
    port = 24000
    user = "root"
    password = "" # 设置连接下游数据库的密码，可为明文或 Base64 编码。

########################### Routes ###########################
# 如果需要对比大量的不同库名或者表名的表的数据，或者用于校验上游多个分表与下游总表的数据，可以通过
 table-rule 来设置映射关系
# 可以只配置 schema 或者 table 的映射关系，也可以都配置
[routes]
[routes.rule1] # rule1 是该配置的唯一标识的自定义 id，用于上面 data-sources.route-rules 中
schema-pattern = "test"      # 匹配数据源的库名，支持通配符 "*" 和 "?"
table-pattern = "test1"          # 匹配数据源的表名，支持通配符 "*" 和 "?"
target-schema = "test"         # 目标库名
target-table = "test1_d" # 目标表名

######################### Task config #########################
# 配置需要对比的*目标数据库*中的表
[task]
    # output-dir 会保存如下信息
    # 1 sql: 检查出错误后生成的修复 SQL 文件，并且一个 chunk 对应一个文件
    # 2 log: sync-diff.log 保存日志信息
    # 3 summary: summary.txt 保存总结
    # 4 checkpoint: a dir 保存断点续传信息
    output-dir = "./output"

    # 上游数据库，内容是 data-sources 声明的唯一标识 id
    source-instances = ["hwtest"]

    # 下游数据库，内容是 data-sources 声明的唯一标识 id
    target-instance = "hwtest_d"

    # 需要比对的下游数据库的表，每个表需要包含数据库名和表名，两者由 `.` 隔开
    # 使用 ? 来匹配任意一个字符；使用 * 来匹配任意；详细匹配规则参考 golang regexp pkg: https://gi
thub.com/google/re2/wiki/Syntax
    target-check-tables = ["test.test1_d"]

    #（可选）对部分表的额外配置，其中 config1 在下面 Table config 配置栏中定义
    target-configs = ["config1"]

######################### Table config #########################
[table-configs.config1]
target-tables = ["test.test1_d"]
#（可选）指定检查的数据的范围，需要符合 sql 中 where 条件的语法
range = "create_time<='2023-03-20 20:30:00'"

What did you expect to see? 对比范围内数据一致，检查通过，不生成修复 SQL 文件

What did you see instead? 生成有不在对比范围内的修复 SQL

[tidb@172-16-120-238 hwtest]$ more output/fix-on-hwtest_d/test\:test1_d\:0\:0-0\:0.sql
-- table: test.test1_d
-- range in sequence: Full
set @@session.time_zone = "+0:00";
REPLACE INTO `test`.`test1_d`(`id`,`create_time`) VALUES (4,'2023-03-20 14:30:00');

What version of TiDB are you using (tidb-server -V or run select tidb_version(); on TiDB)? v6.5.1
which tool are you using? sync-diff-inspector
what versionof tool are you using (pump -V or tidb-lightning -V or syncer -V)? v6.5.1

pingcap / tidb-tools

sync-diff-inspector : When the range condition is of timestamp type, the time zone cannot be converted correctly #722

Bug Report