Details
Major Description
When we run these queries against a specified data set we expect 2 times the same result for each value:
select * from test where ts = STR_TO_DATE('2024-02-12 17:19:39.123', '%Y-%m-%d %H:%i:%S.%f'); |
select * from test where ts = '2024-02-12 17:19:39.123'; |
select * from test where ts = STR_TO_DATE('2024-02-12 17:19:39.000', '%Y-%m-%d %H:%i:%S.%f'); |
select * from test where ts = '2024-02-12 17:19:39.000'; |
But we get:
Empty set (0.003 sec)
10483 rows in set (0.031 sec)
10483 rows in set (0.036 sec)
10483 rows in set (0.033 sec)
So case #2 IMHO is wrong. The round is done inappropriately.
It does not matter if timestamp or datetime is used. But if datetime(3) timestamp(3) is used the result becomes correct.
Data set is attached to this issue.
Bonus Info: Query execution plans are also different:
STR_TO_DATE()
+------+-------------+-------+------+---------------+------+---------+-------+-------+-----------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+-------+------+---------------+------+---------+-------+-------+-----------------------+
|
| 1 | SIMPLE | test | ref | ts | ts | 6 | const | 20964 | Using index condition |
|
+------+-------------+-------+------+---------------+------+---------+-------+-------+-----------------------+
|
'timestamp'
+------+-------------+-------+------+---------------+------+---------+-------+-------+-------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+-------+------+---------------+------+---------+-------+-------+-------+
|
| 1 | SIMPLE | test | ref | ts | ts | 6 | const | 20964 | |
|
+------+-------------+-------+------+---------------+------+---------+-------+-------+-------+
|
Which leads theoretically to a better performance in the STR_TO_DATE case but in practice it is about <5 % slower.