Reporting a tablefullscan When I read the data between invalid data due to DST

[kissmydb]

Bug Report

Please answer these questions before submitting your issue. Thanks!

If there is DST happen, the time '2025-03-30 02:00:00'~'2025-03-30 02:59:00' is not exist. When I read the data between invalid data , Resulting almost read all the data which is not expacted. And if I use a valid data , the explain is normal.

1. Minimal reproduce step (Required)

mysql> SET GLOBAL time_zone = 'Europe/Stockholm';
Query OK, 0 rows affected (0.01 sec)

Create Table: CREATE TABLE `orders` (
  `id` int DEFAULT NULL,
  `created` timestamp NULL DEFAULT NULL,
  KEY `idx_create` (`created`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin
1 row in set (0.00 sec)

mysql> explain select *  from orders  where created between '2025-03-30 02:01:59'   and '2025-03-30 03:00:01' order by 2;
+--------------------------------+--------------+-----------+------------------------------------------------+-------------------------------------------------------------------------+
| id                             | estRows      | task      | access object                                  | operator info                                                           |
+--------------------------------+--------------+-----------+------------------------------------------------+-------------------------------------------------------------------------+
| IndexLookUp_17                 | 103218635.00 | root      |                                                |                                                                         |
| ├─IndexRangeScan_15(Build)     | 103218635.00 | cop[tikv] | table:orders, index:order_created_idx(created) | range:[2025-03-30 02:01:59.000000,2025-03-30 03:00:01], keep order:true |
| └─TableRowIDScan_16(Probe)     | 103218635.00 | cop[tikv] | table:orders                                   | keep order:false                                                        |
+--------------------------------+--------------+-----------+------------------------------------------------+-------------------------------------------------------------------------+
3 rows in set, 26 warnings (0,024 sec)

mysql> explain select * from orders  where created between '2025-03-30 01:01:59'   and '2025-03-30 03:00:01' order by 2;
+--------------------------------+---------+-----------+------------------------------------------------+------------------------------------------------------------------+
| id                             | estRows | task      | access object                                  | operator info                                                    |
+--------------------------------+---------+-----------+------------------------------------------------+------------------------------------------------------------------+
| IndexLookUp_14                 | 2110.60 | root      |                                                |                                                                  |
| ├─IndexRangeScan_12(Build)     | 2110.60 | cop[tikv] | table:orders, index:order_created_idx(created) | range:[2025-03-30 01:01:59,2025-03-30 03:00:01], keep order:true |
| └─TableRowIDScan_13(Probe)     | 2110.60 | cop[tikv] | table:orders                                   | keep order:false                                                 |
+--------------------------------+---------+-----------+------------------------------------------------+------------------------------------------------------------------+
3 rows in set (0,011 sec)

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

Table

3. What did you see instead (Required)

4. What is your TiDB version? (Required)

v8.5.1

[AilinKid]

think about we do the create table first

Create Table: CREATE TABLE `orders` (
  `id` int DEFAULT NULL,
  `created` timestamp NULL DEFAULT NULL,
  KEY `idx_create` (`created`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin
1 row in set (0.00 sec)

and inserted some "invalid rows" '2025-03-30 02:01:59', since the time zone is not in the DST, the data can be passed and stored into the tikv

• and then change to DST

• and we send a query like this to the tidb, the planner can not see, oh i see the the left side of time range here is not invalid, we can just convert it into the 2025-03-30 03:00:01 directly. since the data stored in a DST-insensitive, with this range scan, you can filter to nothing.

• tidb always store the plain UTC data, and change it to the corresponding showing based on what timezone in your session var or the global one on which tidb node that you send your query.

[kissmydb]

Is #61337 it related ?

[mjonss]

Simpler test case:

DROP TABLE IF EXISTS t;
CREATE TABLE t (id int auto_increment primary key, ts timestamp, key (ts));
insert into t values (1, '1970-02-01 00:00:01');
SET time_zone = 'Europe/Amsterdam';
select *, unix_timestamp(ts) from t where ts between '2025-03-30 02:30:00' and '2025-03-30 03:00:00' order by ts limit 1;
show warnings;

Which gives this result:

tidb> CREATE TABLE t (id int auto_increment primary key, ts timestamp, key (ts));
Query OK, 0 rows affected (0.01 sec)

tidb> insert into t values (1, '1970-02-01 00:00:01');
Query OK, 1 row affected (0.00 sec)

tidb> SET time_zone = 'Europe/Amsterdam';
Query OK, 0 rows affected (0.00 sec)

tidb> select *, unix_timestamp(ts) from t where ts between '2025-03-30 02:30:00' and '2025-03-30 03:00:00' order by ts limit 1;
+----+---------------------+--------------------+
| id | ts                  | unix_timestamp(ts) |
+----+---------------------+--------------------+
|  1 | 1970-02-01 00:00:01 |            2674801 |
+----+---------------------+--------------------+
1 row in set, 4 warnings (0.00 sec)

tidb> show warnings;
+---------+------+----------------------------------------------+
| Level   | Code | Message                                      |
+---------+------+----------------------------------------------+
| Warning | 1292 | Incorrect time value: '{2025 3 30 2 30 0 0}' |
| Warning | 1292 | Incorrect time value: '{2025 3 30 2 30 0 0}' |
| Warning | 1292 | Incorrect time value: '{2025 3 30 2 30 0 0}' |
| Warning | 1292 | Incorrect time value: '{2025 3 30 2 30 0 0}' |
+---------+------+----------------------------------------------+
4 rows in set (0.00 sec)

So it looks like the Timestamp'2025-03-30 02:30:00:00' in Europe/Amsterdam time zone is not adjusted to '03:00:00+02:00' but '0000-00-00 00:00:00'/Zero date instead. Similar to what #61337 fixed for INSERT.

[qw4990]

I've investigated this issue a little locally, the reason why #61337 doesn't fix this issue is that insert and select parses datetime string into different internal types, for insert it's TypeTimestamp, but for select it's TypeDatetime.
And the process of checking invalid TypeDatetime is affected by FlagIgnoreInvalidDateErr, which is true for select statements, so this "invalid" datetime string will not raise error in select statements, and we'll skip adjusting it.

MySQL will convert the invalid datetime into the next valid datetime value automatically, you can see the 2 pictures below. MySQL automatically convert "02:01:59" into "03:00:00" when constructing the scan range:

For TiDB, I prefer to keep the behavior unchanged and label this issue as won't fix. Since the current behavior just scans 1 hour data more than MySQL, which seems acceptable. And since there is no such invalid data in this hour 02:00:00 ~ 03:00:00, the actual performance will not be affected by the range with 1 hour more.
And instead changing the predicate value automatically might be dangerous for select statements, it's easy to introduce new bugs in this process. For example, if the predicate is add_date(col, 1 day) = "2025-03-30 02:01:59'" and we change it to add_date(col, 1 day) = "2025-03-30 03:00:00'", it might cause some wrong result problems.
What do you think of this? @mjonss @kissmydb @AilinKid

[mjonss]

@qw4990 I created a draft PR for this, similar to #61337.

I think it is during the range optimizer the conversion goes wrong, and will use 0000-00-00 instead of an adjusted Timestamp values somewhere here:

The draft PR would adjust the date if possible and then convert the error to a warning instead.

[qw4990]

After #61337