str_to_Date() TiDB formatting issues compared to mysql(8.0 and 5.7)

[ramanich1]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

select 
str_to_date('15 SEPTEMB 2001', '%d %M %Y') as d1,
str_to_date('15th May 2001', '%D %b %Y')as d2,
str_to_date('Sunday 15 MAY 2001', '%W %d %b %Y') as d3,
str_to_date('Sund 15 MAY 2001', '%W %d %b %Y')as d4,
str_to_date('Tuesday 00 2002', '%W %U %Y')as d5,
str_to_date('Thursday 53 1998', '%W %u %Y')as d6,
str_to_date('Sunday 01 2001', '%W %v %x')as d7,
str_to_date('Tuesday 52 2001', '%W %V %X')as d8,
str_to_date('060 2004', '%j %Y')as d9,
str_to_date('4 53 1998', '%w %u %Y')as d10;

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

+------------+------------+------------+------------+------------+------------+------------+------------+------------+------------+
| d1         | d2         | d3         | d4         | d5         | d6         | d7         | d8         | d9         | d10        |
+------------+------------+------------+------------+------------+------------+------------+------------+------------+------------+
| 2001-09-15 | 2001-05-15 | 2001-05-15 | 2001-05-15 | 2002-01-01 | 1998-12-31 | 2001-01-07 | 2002-01-01 | 2004-02-29 | 1998-12-31 |
+------------+------------+------------+------------+------------+------------+------------+------------+------------+------------+
1 row in set (0.00 sec)

3. What did you see instead (Required)

+------+------+------+------+------+------+------+------+------------+------+
| d1   | d2   | d3   | d4   | d5   | d6   | d7   | d8   | d9         | d10  |
+------+------+------+------+------+------+------+------+------------+------+
| NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | 2004-00-00 | NULL |
+------+------+------+------+------+------+------+------+------------+------+
1 row in set, 9 warnings (0.00 sec)

4. What is your TiDB version? (Required)

| Release Version: v5.4.0-alpha-67-g17edc5758
Edition: Community
Git Commit Hash: 17edc5758fbf865cada7e156318c41d3ee8a7501
Git Branch: master
UTC Build Time: 2021-11-16 02:57:45
GoVersion: go1.17.2
Race Enabled: false
TiKV Min Version: v3.0.0-60965b006877ca7234adaced7890d7b029ed1306
Check Table Before Drop: false |

[morgo]

I removed the SET sql_mode = 'NO_ENGINE_SUBSTITUTION'; from the test case. It wasn't required to reproduce.

[zhangyangyu]

This case affects TiDB and metabase compatibility. metabase relies a case same as d8: SELECT str_to_date('201525 Sunday', '%X%V %W').

[zhangyangyu]

Looking at

tidb/types/time.go

Line 2925 in 9c73e5e

// TODO: Add the following...

These specifiers are not implemented yet. But our doc https://docs.pingcap.com/tidb/stable/date-and-time-functions#date-and-time-functions directly links to MySQL page, giving a feeling that all specifiers are supported.