extract result incompatible with mysql

[aytrack]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

select extract(hour_second from "-838:59:59.00");

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

mysql> select  extract(hour_second from "-838:59:59.00");
+-------------------------------------------+
| extract(hour_second from "-838:59:59.00") |
+-------------------------------------------+
|                                  -8385959 |
+-------------------------------------------+
1 row in set (0.22 sec)

3. What did you see instead (Required)

MySQL > select  extract(hour_second from "-838:59:59.00");
+-------------------------------------------+
| extract(hour_second from "-838:59:59.00") |
+-------------------------------------------+
| 8385959                                   |
+-------------------------------------------+
1 row in set
Time: 0.051s

4. What is your TiDB version? (Required)

v5.0.3, v4.0.14,
master: da8bb5e
release-5.2.0: 4c68def

[aytrack]

other case:
mysql

mysql> select extract(minute_microsecond from '2018-02-01');
Field   1:  `extract(minute_microsecond from '2018-02-01')`
Catalog:    `def`
Database:   ``
Table:      ``
Org_table:  ``
Type:       LONGLONG
Collation:  binary (63)
Length:     11
Max_length: 10
Decimals:   0
Flags:      BINARY NUM


+-----------------------------------------------+
| extract(minute_microsecond from '2018-02-01') |
+-----------------------------------------------+
|                                    2018000000 |
+-----------------------------------------------+
1 row in set, 1 warning (0.03 sec)

tidb：

mysql> select extract(minute_microsecond from '2018-02-01');
Field   1:  `extract(minute_microsecond from '2018-02-01')`
Catalog:    `def`
Database:   ``
Table:      ``
Org_table:  ``
Type:       LONGLONG
Collation:  binary (63)
Length:     20
Max_length: 0
Decimals:   0
Flags:      NOT_NULL BINARY NUM


+-----------------------------------------------+
| extract(minute_microsecond from '2018-02-01') |
+-----------------------------------------------+
|                                          NULL |
+-----------------------------------------------+
1 row in set, 1 warning (0.04 sec)

[guo-shaoge]

other case:
mysql

mysql> select extract(minute_microsecond from '2018-02-01');
Field   1:  `extract(minute_microsecond from '2018-02-01')`
Catalog:    `def`
Database:   ``
Table:      ``
Org_table:  ``
Type:       LONGLONG
Collation:  binary (63)
Length:     11
Max_length: 10
Decimals:   0
Flags:      BINARY NUM


+-----------------------------------------------+
| extract(minute_microsecond from '2018-02-01') |
+-----------------------------------------------+
|                                    2018000000 |
+-----------------------------------------------+
1 row in set, 1 warning (0.03 sec)

tidb：

mysql> select extract(minute_microsecond from '2018-02-01');
Field   1:  `extract(minute_microsecond from '2018-02-01')`
Catalog:    `def`
Database:   ``
Table:      ``
Org_table:  ``
Type:       LONGLONG
Collation:  binary (63)
Length:     20
Max_length: 0
Decimals:   0
Flags:      NOT_NULL BINARY NUM


+-----------------------------------------------+
| extract(minute_microsecond from '2018-02-01') |
+-----------------------------------------------+
|                                          NULL |
+-----------------------------------------------+
1 row in set, 1 warning (0.04 sec)

Looks like a different problem, and this one is not as serious as above. Maybe we can record this problem in another issue.

[aytrack]

Looks like a different problem, and this one is not as serious as above. Maybe we can record this problem in another issue.

open new issue #27333 to trace this.

[ti-srebot]

Please edit this comment or add a new comment to complete the following information

Not a bug

1. Remove the 'type/bug' label
2. Add notes to indicate why it is not a bug

Duplicate bug

1. Add the 'type/duplicate' label
2. Add the link to the original bug

Bug

Note: Make Sure that 'component', and 'severity' labels are added
Example for how to fill out the template: #20100

1. Root Cause Analysis (RCA) (optional)

2. Symptom (optional)

3. All Trigger Conditions (optional)

4. Workaround (optional)

5. Affected versions

6. Fixed versions