get wrong result when querying slow logs after time zone changed

[lcwangchao]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

tested in my local MacBook, my local timezone is Asia/Shanghai

1. clear slow log files
2. generate a slow log by running select sleep(1)
3. run select * from information_schema.slow_query where time > now() - interval 10 minute;

> select * from information_schema.slow_query where time > now() - interval 20 minute and time < now();
+----------------------------+--------------+------+-----------+---------+---------------+------------------+-----------------+-------------+-------------+--------------+--------------+--------------------+-------------------------+---------------+---------+---------------+---------------------------+-------------+--------------------+---------------------+---------------+-------------------+-----------------------+------------+------------+-----------------+-----------+----------+--------------+-----------+--------------+---------------+---------------+------------+--------------+------------------------------+---------------------------+-------------------------------+--------------------------+-------------------------+------+-------------+-------------+------------------------------------------------------------------+-------+--------------+--------------+--------------+---------------+--------------+--------------+--------------+---------------+---------+----------+----------+-----------+---------------+--------------------------+-------------+----------+----------------+----------+------+---------------+-------------------+-----------------+-------------------+------------------+----------------+-------------------+--------------------+-------------------+---------------+---------------+------+------------------------------------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------+------------------+
| Time                       | Txn_start_ts | User | Host      | Conn_ID | Session_alias | Exec_retry_count | Exec_retry_time | Query_time  | Parse_time  | Compile_time | Rewrite_time | Preproc_subqueries | Preproc_subqueries_time | Optimize_time | Wait_TS | Prewrite_time | Wait_prewrite_binlog_time | Commit_time | Get_commit_ts_time | Commit_backoff_time | Backoff_types | Resolve_lock_time | Local_latch_wait_time | Write_keys | Write_size | Prewrite_region | Txn_retry | Cop_time | Process_time | Wait_time | Backoff_time | LockKeys_time | Request_count | Total_keys | Process_keys | Rocksdb_delete_skipped_count | Rocksdb_key_skipped_count | Rocksdb_block_cache_hit_count | Rocksdb_block_read_count | Rocksdb_block_read_byte | DB   | Index_names | Is_internal | Digest                                                           | Stats | Cop_proc_avg | Cop_proc_p90 | Cop_proc_max | Cop_proc_addr | Cop_wait_avg | Cop_wait_p90 | Cop_wait_max | Cop_wait_addr | Mem_max | Disk_max | KV_total | PD_total  | Backoff_total | Write_sql_response_total | Result_rows | Warnings | Backoff_Detail | Prepared | Succ | IsExplicitTxn | IsWriteCacheTable | Plan_from_cache | Plan_from_binding | Has_more_results | Resource_group | Request_unit_read | Request_unit_write | Time_queued_by_rc | Tidb_cpu_time | Tikv_cpu_time | Plan | Plan_digest                                                      | Binary_plan                                                                                                                                                                                                                                                                                          | Prev_stmt | Query            |
+----------------------------+--------------+------+-----------+---------+---------------+------------------+-----------------+-------------+-------------+--------------+--------------+--------------------+-------------------------+---------------+---------+---------------+---------------------------+-------------+--------------------+---------------------+---------------+-------------------+-----------------------+------------+------------+-----------------+-----------+----------+--------------+-----------+--------------+---------------+---------------+------------+--------------+------------------------------+---------------------------+-------------------------------+--------------------------+-------------------------+------+-------------+-------------+------------------------------------------------------------------+-------+--------------+--------------+--------------+---------------+--------------+--------------+--------------+---------------+---------+----------+----------+-----------+---------------+--------------------------+-------------+----------+----------------+----------+------+---------------+-------------------+-----------------+-------------------+------------------+----------------+-------------------+--------------------+-------------------+---------------+---------------+------+------------------------------------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------+------------------+
| 2024-12-23 10:28:59.919753 | 0            | root | 127.0.0.1 | 2097154 |               | 0                | 0.0             | 1.004329625 | 0.000863208 | 0.000881958  | 0.000285084  | 0                  | 0.0                     | 0.000208292   | 0.0     | 0.0           | 0.0                       | 0.0         | 0.0                | 0.0                 |               | 0.0               | 0.0                   | 0          | 0          | 0               | 0         | 0.0      | 0.0          | 0.0       | 0.0          | 0.0           | 0             | 0          | 0            | 0                            | 0                         | 0                             | 0                        | 0                       | test |             | 0           | a0adeeb79b71315ac13a77f3f11162106b5ec7b48212cf17c20c754263ab9228 |       | 0.0          | 0.0          | 0.0          |               | 0.0          | 0.0          | 0.0          |               | 0       | 0        | 0.0      | 3.333e-06 | 0.0           | 0.000164665              | 1           |          |                | 0        | 1    | 0             | 0                 | 0               | 0                 | 0                | default        | 0.0               | 0.0                | 0.0               | 0.0           | 0.0           | 	id           	task	estRows	operator info     	actRows	execution info                                                	memory 	disk      | a55eaee1d30304ad05500afbbfff1409ce22376e0f062c0d06ff34b8ed9c2b47 | 9gGICvEBCgxQcm9qZWN0aW9uXzMSagoLVGFibGVEdWFsXzQpAAABAvBD8D8wATgBQAFSBnJvd3M6MVoudGltZTo5LjEzwrVzLCBvcGVuOjc5Mm5zLCBjbG9zZTo1ODNucywgbG9vcHM6MnD///8JAwQBeAkIMP///wEh9ihcj8L1I0A+ZgBQEnNsZWVwKDEpLT5Db2x1bW4jMVotBXIAMRFtDDI1LjUFeyRjbG9zZToxMS43BQ8EbG8FcXRiD0NvbmN1cnJlbmN5Ok9GRnj///////////8BGAE= |           | select sleep(1); |
|                            |              |      |           |         |               |                  |                 |             |             |              |              |                    |                         |               |         |               |                           |             |                    |                     |               |                   |                       |            |            |                 |           |          |              |           |              |               |               |            |              |                              |                           |                               |                          |                         |      |             |             |                                                                  |       |              |              |              |               |              |              |              |               |         |          |          |           |               |                          |             |          |                |          |      |               |                   |                 |                   |                  |                |                   |                    |                   |               |               | 	Projection_3 	root	1      	sleep(1)->Column#1	1      	time:1s, open:25.5µs, close:11.7µs, loops:2, Concurrency:OFF	0 Bytes	N/A      |                                                                  |                                                                                                                                                                                                                                                                                                      |           |                  |
|                            |              |      |           |         |               |                  |                 |             |             |              |              |                    |                         |               |         |               |                           |             |                    |                     |               |                   |                       |            |            |                 |           |          |              |           |              |               |               |            |              |                              |                           |                               |                          |                         |      |             |             |                                                                  |       |              |              |              |               |              |              |              |               |         |          |          |           |               |                          |             |          |                |          |      |               |                   |                 |                   |                  |                |                   |                    |                   |               |               | 	└─TableDual_4	root	1      	rows:1            	1      	time:9.13µs, open:792ns, close:583ns, loops:2                	N/A    	N/A      |                                                                  |                                                                                                                                                                                                                                                                                                      |           |                  |
+----------------------------+--------------+------+-----------+---------+---------------+------------------+-----------------+-------------+-------------+--------------+--------------+--------------------+-------------------------+---------------+---------+---------------+---------------------------+-------------+--------------------+---------------------+---------------+-------------------+-----------------------+------------+------------+-----------------+-----------+----------+--------------+-----------+--------------+---------------+---------------+------------+--------------+------------------------------+---------------------------+-------------------------------+--------------------------+-------------------------+------+-------------+-------------+------------------------------------------------------------------+-------+--------------+--------------+--------------+---------------+--------------+--------------+--------------+---------------+---------+----------+----------+-----------+---------------+--------------------------+-------------+----------+----------------+----------+------+---------------+-------------------+-----------------+-------------------+------------------+----------------+-------------------+--------------------+-------------------+---------------+---------------+------+------------------------------------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------+------------------+
1 row in set
Time: 0.009s

4. change timezone to UTC and query again.

> set @@time_zone='UTC'; --- change time zone and select again
> select * from information_schema.slow_query where time > now() - interval 20 minute and time < now();

2. What did you expect to see? (Required)

The second select should return the same row even if time zone changes.

3. What did you see instead (Required)

Emty row returns.

> select * from information_schema.slow_query where time > now() - interval 20 minute and time < now();
+------+--------------+------+------+---------+---------------+------------------+-----------------+------------+------------+--------------+--------------+--------------------+-------------------------+---------------+---------+---------------+---------------------------+-------------+--------------------+---------------------+---------------+-------------------+-----------------------+------------+------------+-----------------+-----------+----------+--------------+-----------+--------------+---------------+---------------+------------+--------------+------------------------------+---------------------------+-------------------------------+--------------------------+-------------------------+----+-------------+-------------+--------+-------+--------------+--------------+--------------+---------------+--------------+--------------+--------------+---------------+---------+----------+----------+----------+---------------+--------------------------+-------------+----------+----------------+----------+------+---------------+-------------------+-----------------+-------------------+------------------+----------------+-------------------+--------------------+-------------------+---------------+---------------+------+-------------+-------------+-----------+-------+
| Time | Txn_start_ts | User | Host | Conn_ID | Session_alias | Exec_retry_count | Exec_retry_time | Query_time | Parse_time | Compile_time | Rewrite_time | Preproc_subqueries | Preproc_subqueries_time | Optimize_time | Wait_TS | Prewrite_time | Wait_prewrite_binlog_time | Commit_time | Get_commit_ts_time | Commit_backoff_time | Backoff_types | Resolve_lock_time | Local_latch_wait_time | Write_keys | Write_size | Prewrite_region | Txn_retry | Cop_time | Process_time | Wait_time | Backoff_time | LockKeys_time | Request_count | Total_keys | Process_keys | Rocksdb_delete_skipped_count | Rocksdb_key_skipped_count | Rocksdb_block_cache_hit_count | Rocksdb_block_read_count | Rocksdb_block_read_byte | DB | Index_names | Is_internal | Digest | Stats | Cop_proc_avg | Cop_proc_p90 | Cop_proc_max | Cop_proc_addr | Cop_wait_avg | Cop_wait_p90 | Cop_wait_max | Cop_wait_addr | Mem_max | Disk_max | KV_total | PD_total | Backoff_total | Write_sql_response_total | Result_rows | Warnings | Backoff_Detail | Prepared | Succ | IsExplicitTxn | IsWriteCacheTable | Plan_from_cache | Plan_from_binding | Has_more_results | Resource_group | Request_unit_read | Request_unit_write | Time_queued_by_rc | Tidb_cpu_time | Tikv_cpu_time | Plan | Plan_digest | Binary_plan | Prev_stmt | Query |
+------+--------------+------+------+---------+---------------+------------------+-----------------+------------+------------+--------------+--------------+--------------------+-------------------------+---------------+---------+---------------+---------------------------+-------------+--------------------+---------------------+---------------+-------------------+-----------------------+------------+------------+-----------------+-----------+----------+--------------+-----------+--------------+---------------+---------------+------------+--------------+------------------------------+---------------------------+-------------------------------+--------------------------+-------------------------+----+-------------+-------------+--------+-------+--------------+--------------+--------------+---------------+--------------+--------------+--------------+---------------+---------+----------+----------+----------+---------------+--------------------------+-------------+----------+----------------+----------+------+---------------+-------------------+-----------------+-------------------+------------------+----------------+-------------------+--------------------+-------------------+---------------+---------------+------+-------------+-------------+-----------+-------+
+------+--------------+------+------+---------+---------------+------------------+-----------------+------------+------------+--------------+--------------+--------------------+-------------------------+---------------+---------+---------------+---------------------------+-------------+--------------------+---------------------+---------------+-------------------+-----------------------+------------+------------+-----------------+-----------+----------+--------------+-----------+--------------+---------------+---------------+------------+--------------+------------------------------+---------------------------+-------------------------------+--------------------------+-------------------------+----+-------------+-------------+--------+-------+--------------+--------------+--------------+---------------+--------------+--------------+--------------+---------------+---------+----------+----------+----------+---------------+--------------------------+-------------+----------+----------------+----------+------+---------------+-------------------+-----------------+-------------------+------------------+----------------+-------------------+--------------------+-------------------+---------------+---------------+------+-------------+-------------+-----------+-------+
0 rows in set
Time: 0.007s

4. What is your TiDB version? (Required)