an out-of-range date to UNIX_TIMESTAMP() will lead selete got wrong result
Open
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
- Read the whole issue, then the project's contributing guide.
- Comment on the issue to say you are picking it up — it saves two people doing the same work.
- Fork the repository and make your change on a branch.
- 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