[Bug] str_to_date always return result with microsecond no matter you specfic %f in date format strin

[Yulei-Yang]

Search before asking

• I had searched in the issues and found no similar issues.

Version

master & 2.1

What's Wrong?

In Doris
mysql> select id,str_to_date(dt, '%Y-%m-%d %H:%i:%s.%f') from test1 limit 1;
+------+--------------------------------------+
| id | str_to_date(dt, '%Y-%m-%d %H:%i:%s') |
+------+--------------------------------------+
| 2 | 2024-12-28 10:11:12.000000 |
+------+--------------------------------------+
this is correct. But below is wrong:

mysql> select id,str_to_date(dt, '%Y-%m-%d %H:%i:%s') from test1 limit 1;
+------+--------------------------------------+
| id | str_to_date(dt, '%Y-%m-%d %H:%i:%s') |
+------+--------------------------------------+
| 2 | 2024-12-28 10:11:12.000000 |
+------+--------------------------------------+
it should be 2024-12-28 10:11:12

In mysql str_to_date(dt, '%Y-%m-%d %H:%i:%s') does not return microsecond part
mysql> desc test1;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int | YES | | NULL | |
| name | varchar(32) | YES | | NULL | |
| dt | varchar(32) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> select * from test1;
+------+------+---------------------+
| id | name | dt |
+------+------+---------------------+
| 3 | Carl | 2024-12-29 10:11:12 |
| 2 | Bob | 2024-12-28 10:11:12 |
+------+------+---------------------+
2 rows in set (0.00 sec)

mysql> select id,str_to_date(dt, '%Y-%m-%d %H:%i:%s') from test1;
+------+--------------------------------------+
| id | str_to_date(dt, '%Y-%m-%d %H:%i:%s') |
+------+--------------------------------------+
| 3 | 2024-12-29 10:11:12 |
| 2 | 2024-12-28 10:11:12 |
+------+--------------------------------------+
2 rows in set (0.00 sec)

mysql> select id,str_to_date(dt, '%Y-%m-%d %H:%i:%s.%f') from test1;
+------+-----------------------------------------+
| id | str_to_date(dt, '%Y-%m-%d %H:%i:%s.%f') |
+------+-----------------------------------------+
| 3 | 2024-12-29 10:11:12.000000 |
| 2 | 2024-12-28 10:11:12.000000 |
+------+-----------------------------------------+
2 rows in set (0.00 sec)

What You Expected?

fix it, worked same as mysql

How to Reproduce?

No response

Anything Else?

CREATE TABLE test1 (
id INT,
name varchar(32),
dt varchar(32)
) ENGINE=OLAP
UNIQUE KEY(id)
DISTRIBUTED BY HASH(id) BUCKETS 1
PROPERTIES (
"replication_allocation" = "tag.location.default: 1"
);

insert into test1 values (3, 'Carl','2024-12-29 10:11:12'),(2, 'Bob','2024-12-28 10:11:12');
select id,str_to_date(dt, '%Y-%m-%d %H:%i:%s') from test1;

Are you willing to submit PR?

• Yes I am willing to submit a PR!

Code of Conduct

• I agree to follow this project's Code of Conduct