str_to_date function push down (for poc) by JaySon-Huang · Pull Request #1960 · pingcap/tiflash

JaySon-Huang · 2021-05-22T16:37:16Z

A cherry-pick to POC branch for #1961

Signed-off-by: JaySon-Huang jayson.hjs@gmail.com

What problem does this PR solve?

Issue Number: #1942

Problem Summary:

What is changed and how it works?

Proposal: xxx

What's Changed:

Related changes

PR to update pingcap/docs/pingcap/docs-cn:
Need to cherry-pick to the release branch:

Check List

Tests

Unit test

Side effects

Release note

Support str_to_date push down

Signed-off-by: JaySon-Huang <jayson.hjs@gmail.com>

JaySon-Huang

~~Four test cases not passed.~~
All passed now.

dbms/src/Common/tests/gtest_mytime.cpp

Signed-off-by: JaySon-Huang <jayson.hjs@gmail.com>

JaySon-Huang · 2021-05-23T03:12:20Z

I have found some different behavior of str_to_date under some inputs between MariaDB 10.3.14 and TiDB 5.0.0

'%b' should be case insensitive

> -- 10.3.14-MariaDB %b should be case insensitive
> select str_to_date('31/may/2016 12:34:56.1234', "%d/%b/%Y %H:%i:%S.%f")
+------------------------------------------------------------------+
| str_to_date('31/may/2016 12:34:56.1234', "%d/%b/%Y %H:%i:%S.%f") |
+------------------------------------------------------------------+
| 2016-05-31 12:34:56.123400                                       |
+------------------------------------------------------------------+
> -- tidb 5.0.0 %b should be case insensitive, but tidb not support
> select str_to_date('31/may/2016 12:34:56.1234', "%d/%b/%Y %H:%i:%S.%f")
+------------------------------------------------------------------+
| str_to_date('31/may/2016 12:34:56.1234', "%d/%b/%Y %H:%i:%S.%f") |
+------------------------------------------------------------------+
| <null>                                                           |
+------------------------------------------------------------------+

'%M' should be case insensitive

> -- 10.3.14-MariaDB %M should be case insensitive
> select str_to_date("30/april/2016 12:34:56.", "%d/%M/%Y %H:%i:%s.%f")
+----------------------------------------------------------------+
| str_to_date("30/april/2016 12:34:56.", "%d/%M/%Y %H:%i:%s.%f") |
+----------------------------------------------------------------+
| 2016-04-30 12:34:56                                            |
+----------------------------------------------------------------+
> -- tidb 5.0.0 %M should be case insensitive, but tidb not support
> select str_to_date("30/april/2016 12:34:56.", "%d/%M/%Y %H:%i:%s.%f")
+----------------------------------------------------------------+
| str_to_date("30/april/2016 12:34:56.", "%d/%M/%Y %H:%i:%s.%f") |
+----------------------------------------------------------------+
| <null>                                                         |
+----------------------------------------------------------------+

Parsing empty string

> -- 10.3.14-MariaDB empty string, MariaDB return a date
> select str_to_date("   ", " ")
+-------------------------+
| str_to_date("   ", " ") |
+-------------------------+
| 0000-00-00              |
+-------------------------+
> -- tidb 5.0.0 empty string, tidb return a datetime
> select str_to_date("   ", " ")
+-------------------------+
| str_to_date("   ", " ") |
+-------------------------+
| 0000-00-00 00:00:00     |
+-------------------------+

The day that is not exists in real world

> -- 10.3.14-MariaDB A date "Feb 30th"/"April 31th" not exists int real world, can be parsed in Mariadb
> select str_to_date("30/Feb/2016 12:34:56.1234", "%d/%b/%Y %H:%i:%S.%f")
+------------------------------------------------------------------+
| str_to_date("30/Feb/2016 12:34:56.1234", "%d/%b/%Y %H:%i:%S.%f") |
+------------------------------------------------------------------+
| 2016-02-30 12:34:56.123400                                       |
+------------------------------------------------------------------+
> select str_to_date("31/April/2016 12:34:56.", "%d/%M/%Y %H:%i:%s.%f")
+----------------------------------------------------------------+
| str_to_date("31/April/2016 12:34:56.", "%d/%M/%Y %H:%i:%s.%f") |
+----------------------------------------------------------------+
| 2016-04-31 12:34:56.000000                                     |
+----------------------------------------------------------------+
> -- tidb 5.0.0 A date "Feb 30th"/"April 31th" not exists int real world, can not be parsed in tidb
> select str_to_date("30/Feb/2016 12:34:56.1234", "%d/%b/%Y %H:%i:%S.%f")
+------------------------------------------------------------------+
| str_to_date("30/Feb/2016 12:34:56.1234", "%d/%b/%Y %H:%i:%S.%f") |
+------------------------------------------------------------------+
| <null>                                                           |
+------------------------------------------------------------------+
> select str_to_date("31/April/2016 12:34:56.", "%d/%M/%Y %H:%i:%s.%f")
+----------------------------------------------------------------+
| str_to_date("31/April/2016 12:34:56.", "%d/%M/%Y %H:%i:%s.%f") |
+----------------------------------------------------------------+
| <null>                                                         |
+----------------------------------------------------------------+

The "Feb 29th" for leap year

> -- 10.3.14-MariaDB "Feb 29th" for leap year and non leap year can be parsed in Mariadb
> select str_to_date("29/Feb/2020 12:34:56.", "%d/%b/%Y %H:%i:%s.%f")
+--------------------------------------------------------------+
| str_to_date("29/Feb/2020 12:34:56.", "%d/%b/%Y %H:%i:%s.%f") |
+--------------------------------------------------------------+
| 2020-02-29 12:34:56                                          |
+--------------------------------------------------------------+
> select str_to_date("29/Feb/2021 12:34:56.", "%d/%b/%Y %H:%i:%s.%f")
+--------------------------------------------------------------+
| str_to_date("29/Feb/2021 12:34:56.", "%d/%b/%Y %H:%i:%s.%f") |
+--------------------------------------------------------------+
| 2021-02-29 12:34:56.000000                                   |
+--------------------------------------------------------------+
> -- tidb 5.0.0 Only "Feb 29th" of leap year can be parsed, non leap year parse error
> select str_to_date("29/Feb/2020 12:34:56.", "%d/%b/%Y %H:%i:%s.%f")
+--------------------------------------------------------------+
| str_to_date("29/Feb/2020 12:34:56.", "%d/%b/%Y %H:%i:%s.%f") |
+--------------------------------------------------------------+
| 2020-02-29 12:34:56                                          |
+--------------------------------------------------------------+
> select str_to_date("29/Feb/2021 12:34:56.", "%d/%b/%Y %H:%i:%s.%f")
+--------------------------------------------------------------+
| str_to_date("29/Feb/2021 12:34:56.", "%d/%b/%Y %H:%i:%s.%f") |
+--------------------------------------------------------------+
| <null>                                                       |
+--------------------------------------------------------------+

The '%%' pattern (https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html#function_date-format)

> -- %% Both tidb and mariadb can not handle '%%' properly
> -- https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html#function_date-format
> -- 10.3.14-MariaDB
> select str_to_date("01/Feb/2016 % 23:45:54", "%d/%b/%Y %% %H:%i:%S")
+---------------------------------------------------------------+
| str_to_date("01/Feb/2016 % 23:45:54", "%d/%b/%Y %% %H:%i:%S") |
+---------------------------------------------------------------+
| <null>                                                        |
+---------------------------------------------------------------+
> -- tidb 5.0.0
> select str_to_date("01/Feb/2016 % 23:45:54", "%d/%b/%Y %% %H:%i:%S")
+---------------------------------------------------------------+
| str_to_date("01/Feb/2016 % 23:45:54", "%d/%b/%Y %% %H:%i:%S") |
+---------------------------------------------------------------+
| <null>                                                        |
+---------------------------------------------------------------+

TiDB version

> select tidb_version();
+-------------------------------------------------------------------+
| tidb_version()                                                    |
+-------------------------------------------------------------------+
| Release Version: v5.0.0                                           |
| Edition: Community                                                |
| Git Commit Hash: bdac0885cd11bdf571aad9353bfc24e13554b91c         |
| Git Branch: heads/refs/tags/v5.0.0                                |
| UTC Build Time: 2021-04-06 16:36:29                               |
| GoVersion: go1.13                                                 |
| Race Enabled: false                                               |
| TiKV Min Version: v3.0.0-60965b006877ca7234adaced7890d7b029ed1306 |
| Check Table Before Drop: false                                    |
+-------------------------------------------------------------------+

Signed-off-by: JaySon-Huang <jayson.hjs@gmail.com>

This reverts commit c72421d.

dbms/src/Common/MyTime.cpp

Signed-off-by: JaySon-Huang <jayson.hjs@gmail.com>

dbms/src/Common/MyTime.cpp

Signed-off-by: JaySon-Huang <jayson.hjs@gmail.com>

zanmato1984

LGTM

str_to_date

3fefde2

Signed-off-by: JaySon-Huang <jayson.hjs@gmail.com>

JaySon-Huang self-assigned this May 22, 2021

JaySon-Huang commented May 22, 2021

View reviewed changes

dbms/src/Common/tests/gtest_mytime.cpp Outdated Show resolved Hide resolved

dbms/src/Common/tests/gtest_mytime.cpp Outdated Show resolved Hide resolved

JaySon-Huang added 4 commits May 23, 2021 00:52

Remove test from master

03f9ad4

Signed-off-by: JaySon-Huang <jayson.hjs@gmail.com>

Add test cases and fix bugs

1a002f4

Signed-off-by: JaySon-Huang <jayson.hjs@gmail.com>

Refactor code; fix all unittest

d33b2a1

Signed-off-by: JaySon-Huang <jayson.hjs@gmail.com>

Support strToDateDate

9a15954

Signed-off-by: JaySon-Huang <jayson.hjs@gmail.com>

JaySon-Huang changed the title ~~[WIP] str_to_date function push down~~ str_to_date function push down May 23, 2021

remove useless method from master

b679f25

Signed-off-by: JaySon-Huang <jayson.hjs@gmail.com>

JaySon-Huang added the CHERRY-PICK cherry pick label May 23, 2021

JaySon-Huang changed the title ~~str_to_date function push down~~ str_to_date function push down (for poc) May 23, 2021

JaySon-Huang mentioned this pull request May 23, 2021

Support str_to_date function push down #1942

Closed

JaySon-Huang added 5 commits May 23, 2021 16:03

Fix exception

71dfce4

Signed-off-by: JaySon-Huang <jayson.hjs@gmail.com>

Small fix

c4a5596

Signed-off-by: JaySon-Huang <jayson.hjs@gmail.com>

Add fullstack-test

91f150e

Recover the test case deleted by accident

c72421d

Signed-off-by: JaySon-Huang <jayson.hjs@gmail.com>

Revert "Recover the test case deleted by accident"

6552b24

This reverts commit c72421d.

zanmato1984 reviewed May 23, 2021

View reviewed changes

dbms/src/Common/MyTime.cpp Outdated Show resolved Hide resolved

zanmato1984 reviewed May 23, 2021

View reviewed changes

dbms/src/Common/MyTime.cpp Outdated Show resolved Hide resolved

JaySon-Huang added 3 commits May 23, 2021 19:05

Add group by to push down

3a1faaf

Signed-off-by: JaySon-Huang <jayson.hjs@gmail.com>

Remove reduant check

f9e7047

Signed-off-by: JaySon-Huang <jayson.hjs@gmail.com>

Fix off-by-one check

47965f9

Signed-off-by: JaySon-Huang <jayson.hjs@gmail.com>

zanmato1984 reviewed May 23, 2021

View reviewed changes

dbms/src/Common/MyTime.cpp Outdated Show resolved Hide resolved

JaySon-Huang added 4 commits May 23, 2021 20:38

Fix broken unittest case

1e40e36

Signed-off-by: JaySon-Huang <jayson.hjs@gmail.com>

Fix MyDateTimeParser::Context

620dcae

Signed-off-by: JaySon-Huang <jayson.hjs@gmail.com>

Fix off-by-one check

28b683a

Signed-off-by: JaySon-Huang <jayson.hjs@gmail.com>

Confirm test pass

cbc28be

Signed-off-by: JaySon-Huang <jayson.hjs@gmail.com>

zanmato1984 approved these changes May 23, 2021

View reviewed changes

ti-srebot added the status/LGT1 Indicates that a PR has LGTM 1. label May 23, 2021

JaySon-Huang merged commit 6240d74 into pingcap:v5.0.1_may_poc May 23, 2021

JaySon-Huang deleted the str_to_date_pushdown_hotfix branch May 23, 2021 14:00

JaySon-Huang mentioned this pull request May 25, 2021

str_to_date function push down #1961

Merged

ti-srebot mentioned this pull request Jun 3, 2021

str_to_date function push down (#1961) #2063

Merged

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

str_to_date function push down (for poc)#1960

str_to_date function push down (for poc)#1960
JaySon-Huang merged 18 commits intopingcap:v5.0.1_may_pocfrom
JaySon-Huang:str_to_date_pushdown_hotfix

JaySon-Huang commented May 22, 2021 •

edited

Loading

Uh oh!

JaySon-Huang left a comment •

edited

Loading

Uh oh!

Uh oh!

Uh oh!

JaySon-Huang commented May 23, 2021 •

edited

Loading

Uh oh!

Uh oh!

Uh oh!

Uh oh!

zanmato1984 left a comment

Uh oh!

Reviewers

Assignees

Labels

Projects

Milestone

Development

Uh oh!

3 participants

Conversation

JaySon-Huang commented May 22, 2021 • edited Loading Uh oh! There was an error while loading. Please reload this page.

Uh oh!

What problem does this PR solve?

What is changed and how it works?

Related changes

Check List

Release note

Uh oh!

JaySon-Huang left a comment • edited Loading Uh oh! There was an error while loading. Please reload this page.

Uh oh!

Choose a reason for hiding this comment

Uh oh!

Uh oh!

Uh oh!

JaySon-Huang commented May 23, 2021 • edited Loading Uh oh! There was an error while loading. Please reload this page.

Uh oh!

'%b' should be case insensitive

'%M' should be case insensitive

Parsing empty string

The day that is not exists in real world

The "Feb 29th" for leap year

The '%%' pattern (https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html#function_date-format)

TiDB version

Uh oh!

Uh oh!

Uh oh!

Uh oh!

zanmato1984 left a comment

Choose a reason for hiding this comment

Uh oh!

Reviewers

Assignees

Labels

Projects

Milestone

Development

Uh oh!

3 participants

JaySon-Huang commented May 22, 2021 •

edited

Loading

JaySon-Huang left a comment •

edited

Loading

JaySon-Huang commented May 23, 2021 •

edited

Loading