Function DAY/DAYOFMONTH should do type conversation

[SunRunAway]

Description

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. What did you do?
   If possible, provide a recipe for reproducing the error.

2. What did you expect to see?

mysql> SELECT DAY(120.000), DAYOFMONTH(0.000), DAYOFMONTH(0);
+--------------+-------------------+---------------+
| DAY(120.000) | DAYOFMONTH(0.000) | DAYOFMONTH(0) |
+--------------+-------------------+---------------+
|           20 |                 0 |             0 |
+--------------+-------------------+---------------+
1 row in set (0.00 sec)

3. What did you see instead?

mysql> SELECT DAY(120.000), DAYOFMONTH(0.000), DAYOFMONTH(0);
+--------------+-------------------+---------------+
| DAY(120.000) | DAYOFMONTH(0.000) | DAYOFMONTH(0) |
+--------------+-------------------+---------------+
|         NULL |              NULL |          NULL |
+--------------+-------------------+---------------+
1 row in set, 3 warnings (0.00 sec)

mysql> show warnings;
+---------+------+--------------------------------------------------------+
| Level   | Code | Message                                                |
+---------+------+--------------------------------------------------------+
| Warning | 1292 | invalid time format: '120.000'                         |
| Warning | 1292 | invalid time format: '0.000'                           |
| Warning | 1292 | Incorrect datetime value: '0000-00-00 00:00:00.000000' |
+---------+------+--------------------------------------------------------+
3 rows in set (0.00 sec)

4. What version of TiDB are you using (tidb-server -V or run select tidb_version(); on TiDB)?
   fdbc149

SIG slack channel

#sig-exec

Score

• 300

Mentor

• @qw4990

[SunRunAway]

Perhaps it's a common issue for all date and time functions.

In MySQL,

mysql>  SELECT  TO_SECONDS('0.112') ; show warnings;
+---------------------+
| TO_SECONDS('0.112') |
+---------------------+
|         63140342400 |
+---------------------+
1 row in set (0.00 sec)

Empty set (0.00 sec)

In TiDB,

mysql>  SELECT  TO_SECONDS('0.112') ; show warnings;
+---------------------+
| TO_SECONDS('0.112') |
+---------------------+
|                NULL |
+---------------------+
1 row in set, 1 warning (0.00 sec)

+---------+------+------------------------------+
| Level   | Code | Message                      |
+---------+------+------------------------------+
| Warning | 1292 | invalid time format: '0.112' |
+---------+------+------------------------------+
1 row in set (0.00 sec)

[ghost]

Confirming I can still reproduce this against master:

SELECT DAY(120.000), DAYOFMONTH(0.000), DAYOFMONTH(0);
SHOW WARNINGS;
SELECT TO_SECONDS('0.112') ;
SHOW WARNINGS;

..

mysql> SELECT DAY(120.000), DAYOFMONTH(0.000), DAYOFMONTH(0);
+--------------+-------------------+---------------+
| DAY(120.000) | DAYOFMONTH(0.000) | DAYOFMONTH(0) |
+--------------+-------------------+---------------+
|         NULL |              NULL |          NULL |
+--------------+-------------------+---------------+
1 row in set, 3 warnings (0.00 sec)

mysql> SHOW WARNINGS;
+---------+------+-------------------------------------------------+
| Level   | Code | Message                                         |
+---------+------+-------------------------------------------------+
| Warning | 1292 | Incorrect time value: '120.000'                 |
| Warning | 1292 | Incorrect datetime value: '0000-00-00 00:00:00' |
| Warning | 1292 | Incorrect datetime value: '0000-00-00 00:00:00' |
+---------+------+-------------------------------------------------+
3 rows in set (0.00 sec)

mysql> SELECT TO_SECONDS('0.112') ;
+---------------------+
| TO_SECONDS('0.112') |
+---------------------+
|                NULL |
+---------------------+
1 row in set, 1 warning (0.00 sec)

mysql> SHOW WARNINGS;
+---------+------+-------------------------------+
| Level   | Code | Message                       |
+---------+------+-------------------------------+
| Warning | 1292 | Incorrect time value: '0.112' |
+---------+------+-------------------------------+
1 row in set (0.00 sec)

mysql> SELECT tidb_version()\G
*************************** 1. row ***************************
tidb_version(): Release Version: v4.0.0-beta.2-750-g8a661044c
Edition: Community
Git Commit Hash: 8a661044cedf8daad1de4fbf79a390962b6f6c3b
Git Branch: master
UTC Build Time: 2020-07-10 10:52:37
GoVersion: go1.13
Race Enabled: false
TiKV Min Version: v3.0.0-60965b006877ca7234adaced7890d7b029ed1306
Check Table Before Drop: false
1 row in set (0.00 sec)

[youwithouto]

Hi @SunRunAway , can I work on this issue as the HP TiDB challenge?

[XiaoGe2030]

/pick-up

[ti-challenge-bot]

It is not a pickable issue!

More

Tip : If you want this issue to be picked, you need to add a challenge-program label to it.

Warning: None

[Patrick0308]

/pick-up

[ti-challenge-bot]

Pick up success.

[ti-challenge-bot]

@Patrick0308 You did not submit PR within 7 days, so give up automatically.

[Patrick0308]

/pick-up

[ti-challenge-bot]

Pick up success.

[ti-challenge-bot]

@Patrick0308 You did not submit PR within 7 days, so give up automatically.

[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