pingcap / pingcap/tiflash

an out-of-range date to UNIX_TIMESTAMP() will lead selete got wrong result

Open
#3,171 0 comments 0 reactions 0 assignees View on GitHub

Nobody has claimed this yet.

affects-5.0 affects-5.1 affects-5.2 affects-5.3 affects-5.4 affects-6.0 affects-6.1 component/compute severity/moderate type/bug
Dominant language
C++
Stars
1k
Forks
423
Avg merge
1d 15h
Merged PRs (30d)
24

Description

MySQL root@172.16.6.31:test> create table t02(a date);
Query OK, 0 rows affected
Time: 0.112s
MySQL root@172.16.6.31:test> insert into t02 values ('2059-09-02'),('2022-02-01'),('2012-02-01');
Query OK, 3 row affected
Time: 0.053s

MySQL root@172.16.6.31:test> alter table t02 set tiflash replica 1;
Query OK, 0 rows affected
Time: 0.114s
MySQL root@172.16.6.31:test> select unix_timestamp(a) > 1615852800 from t02;
+--------------------------------+
| unix_timestamp(a) > 1615852800 |
+--------------------------------+
| 0                              |
| 1                              |
| 0                              |
+--------------------------------+
3 rows in set
Time: 0.065s
-- should be 2022-02-01
-- or should be consistent with above select 
MySQL root@172.16.6.31:test> select * from t02 where unix_timestamp(a) > 1615852800;  
+------------+
| a          |
+------------+
| 2059-09-29 |
| 2022-02-01 |
+------------+

MySQL root@172.16.6.31:test> select from_unixtime(1615852800);
+---------------------------+
| from_unixtime(1615852800) |
+---------------------------+
| 2021-03-16 00:00:00       |
+---------------------------+
1 row in set
Time: 0.051s

MySQL root@172.16.6.31:test> select * from information_schema.cluster_info;
+---------+-------------------+-------------------+---------+------------------------------------------+---------------------------+---------------------+-----------+
| TYPE    | INSTANCE          | STATUS_ADDRESS    | VERSION | GIT_HASH                                 | START_TIME                | UPTIME              | SERVER_ID |
+---------+-------------------+-------------------+---------+------------------------------------------+---------------------------+---------------------+-----------+
| tidb    | 172.16.6.31:4010  | 172.16.6.31:11080 | 5.2.1   | cd8fb24c5f7ebd9d479ed228bb41848bd5e97445 | 2021-09-27T15:39:55+08:00 | 20h11m21.173743567s | 0         |
| pd      | 172.16.6.31:2379  | 172.16.6.31:2379  | 5.2.1   | 8afd38d919ec727712a9518cc3ebdcab47b2fbcb | 2021-09-27T15:28:03+08:00 | 20h23m13.173748153s | 0         |
| tikv    | 172.16.6.31:20163 | 172.16.6.31:20183 | 5.2.1   | 2c99f317d4ba125b772a8b94a6c3c0eb9d07ac59 | 2021-09-27T15:31:19+08:00 | 20h19m57.173749749s | 0         |
| tikv    | 172.16.6.31:20162 | 172.16.6.31:20182 | 5.2.1   | 2c99f317d4ba125b772a8b94a6c3c0eb9d07ac59 | 2021-09-27T15:39:38+08:00 | 20h11m38.173751195s | 0         |
| tikv    | 172.16.6.31:20161 | 172.16.6.31:20181 | 5.2.1   | 2c99f317d4ba125b772a8b94a6c3c0eb9d07ac59 | 2021-09-27T15:35:20+08:00 | 20h15m56.173755385s | 0         |
| tiflash | 172.16.6.31:3931  | 172.16.6.31:20293 | 5.2.1   | 576ddf874d84568b9ac94e803973861b00bd2b2b | 2021-09-27T15:28:03+08:00 | 20h23m13.173757001s | 0         |
| tiflash | 172.16.6.31:3930  | 172.16.6.31:20292 | 5.2.1   | 576ddf874d84568b9ac94e803973861b00bd2b2b | 2021-09-27T15:27:31+08:00 | 20h23m45.173758412s | 0         |
+---------+-------------------+-------------------+---------+------------------------------------------+---------------------------+---------------------+-----------+
7 rows in set
Time: 0.050s

Contributor guide

No contributing guide indexed for this repository

First steps

  1. Read the whole issue, then the project's contributing guide.
  2. Comment on the issue to say you are picking it up — it saves two people doing the same work.
  3. Fork the repository and make your change on a branch.
  4. Open a pull request that references the issue number.

Research direction

Start by reproducing the SQL sequence in the issue, including the TiFlash replica, and compare the two UNIX_TIMESTAMP queries. Trace the date conversion and predicate evaluation involved; done means the scalar comparison and filtered query return consistent results for the supplied dates.

Written by the indexing model from the issue text.

Assessment

Tech stack
cpp, sql
Domain
database
Issue type
Bug
Difficulty
4/5
Estimated time
3-5 days
Activity status
Stale
Clarity
Mostly clear
Newbie friendliness
35/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.