Incompatible behaviour of least() and greatest() functions with MySQL

[JmPotato]

Description

Bug Report

1. Minimal reproduce step (Required)

drop table t;
create table t (int_type int, str_type char(255), datetime_type datetime);
insert into t values (12345, "invalid_time", "2020-01-01");
select int_type, str_type, datetime_type from t;
select least(int_type, str_type, datetime_type) from t;
select least(int_type, str_type) from t;
select least(int_type, datetime_type) from t;
select least(str_type, datetime_type) from t;
select greatest(int_type, str_type, datetime_type) from t;
select greatest(int_type, str_type) from t;
select greatest(int_type, datetime_type) from t;
select greatest(str_type, datetime_type) from t;

I tried these queries on MySQL and TiDB respectively.

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

mysql Ver 8.0.19 for osx10.15 on x86_64 (Homebrew)

In MySQL:

+----------+--------------+---------------------+
| int_type | str_type     | datetime_type       |
+----------+--------------+---------------------+
|    12345 | invalid_time | 2020-01-01 00:00:00 |
+----------+--------------+---------------------+
1 row in set (0.00 sec)

+------------------------------------------+
| least(int_type, str_type, datetime_type) |
+------------------------------------------+
| 12345                                    |
+------------------------------------------+
1 row in set, 2 warnings (0.00 sec)

Warning (Code 1292): Incorrect datetime value: '12345' for column 'datetime_type' at row 1
Warning (Code 1292): Incorrect datetime value: 'invalid_time' for column 'datetime_type' at row 1
+---------------------------+
| least(int_type, str_type) |
+---------------------------+
| 12345                     |
+---------------------------+
1 row in set (0.00 sec)

+--------------------------------+
| least(int_type, datetime_type) |
+--------------------------------+
| 12345                          |
+--------------------------------+
1 row in set, 1 warning (0.01 sec)

Warning (Code 1292): Incorrect datetime value: '12345' for column 'datetime_type' at row 1
+--------------------------------+
| least(str_type, datetime_type) |
+--------------------------------+
| 2020-01-01 00:00:00            |
+--------------------------------+
1 row in set, 1 warning (0.00 sec)

Warning (Code 1292): Incorrect datetime value: 'invalid_time' for column 'datetime_type' at row 1
+---------------------------------------------+
| greatest(int_type, str_type, datetime_type) |
+---------------------------------------------+
| 2020-01-01 00:00:00.000000                  |
+---------------------------------------------+
1 row in set, 2 warnings (0.00 sec)

Warning (Code 1292): Incorrect datetime value: '12345' for column 'datetime_type' at row 1
Warning (Code 1292): Incorrect datetime value: 'invalid_time' for column 'datetime_type' at row 1
+------------------------------+
| greatest(int_type, str_type) |
+------------------------------+
| invalid_time                 |
+------------------------------+
1 row in set (0.00 sec)

+-----------------------------------+
| greatest(int_type, datetime_type) |
+-----------------------------------+
| 2020-01-01 00:00:00               |
+-----------------------------------+
1 row in set, 1 warning (0.00 sec)

Warning (Code 1292): Incorrect datetime value: '12345' for column 'datetime_type' at row 1
+-----------------------------------+
| greatest(str_type, datetime_type) |
+-----------------------------------+
| 2020-01-01 00:00:00.000000        |
+-----------------------------------+
1 row in set, 1 warning (0.00 sec)

Warning (Code 1292): Incorrect datetime value: 'invalid_time' for column 'datetime_type' at row 1

3. What did you see instead (Required)

In TiDB:

mysql> source /Users/jmpotato/test.sql
Query OK, 0 rows affected (0.03 sec)

Query OK, 0 rows affected (0.02 sec)

Query OK, 1 row affected (0.01 sec)

+----------+--------------+---------------------+
| int_type | str_type     | datetime_type       |
+----------+--------------+---------------------+
|    12345 | invalid_time | 2020-01-01 00:00:00 |
+----------+--------------+---------------------+
1 row in set (0.00 sec)

+------------------------------------------+
| least(int_type, str_type, datetime_type) |
+------------------------------------------+
|                                        0 |
+------------------------------------------+
1 row in set, 1 warning (0.00 sec)

+---------------------------+
| least(int_type, str_type) |
+---------------------------+
|                         0 |
+---------------------------+
1 row in set, 1 warning (0.00 sec)

+--------------------------------+
| least(int_type, datetime_type) |
+--------------------------------+
|                          12345 |
+--------------------------------+
1 row in set (0.00 sec)

+--------------------------------+
| least(str_type, datetime_type) |
+--------------------------------+
| invalid_time                   |
+--------------------------------+
1 row in set, 1 warning (0.00 sec)

+---------------------------------------------+
| greatest(int_type, str_type, datetime_type) |
+---------------------------------------------+
|                              20200101000000 |
+---------------------------------------------+
1 row in set, 1 warning (0.00 sec)

+------------------------------+
| greatest(int_type, str_type) |
+------------------------------+
|                        12345 |
+------------------------------+
1 row in set, 1 warning (0.01 sec)

+-----------------------------------+
| greatest(int_type, datetime_type) |
+-----------------------------------+
|                    20200101000000 |
+-----------------------------------+
1 row in set (0.00 sec)

+-----------------------------------+
| greatest(str_type, datetime_type) |
+-----------------------------------+
| 2020-01-01 00:00:00               |
+-----------------------------------+
1 row in set, 1 warning (0.00 sec)

4. Affected version (Required)

+ TiDB version:
Release Version: v4.0.0-beta.2-624-g4e74d4039-dirty
Edition: Community
Git Commit Hash: 4e74d40392e9b81f2a90cbc2f7777fd35f87b2e7
Git Branch: master
UTC Build Time: 2020-06-12 02:31:49
GoVersion: go1.14.3
Race Enabled: false
TiKV Min Version: v3.0.0-60965b006877ca7234adaced7890d7b029ed1306
Check Table Before Drop: false

5. Root Cause Analysis

SIG slack channel

#sig-exec

Score

• 900

Mentor

• @lzmhhh123

[g1eny0ung]

/label component/expression

[yufan022]

/pick-up

[ti-challenge-bot]

Pick up success.

[yufan022]

/give-up

[ti-challenge-bot]

Give up success.

[newcworld]

/pick-up

[ti-challenge-bot]

Pick up success.

[newcworld]

I'm confusing why. It seems str_type will convert to datatime ?
Why the results of these two queries are the same ？

mysql> select greatest(str_type, datetime_type) from t;
+-----------------------------------+
| greatest(str_type, datetime_type) |
+-----------------------------------+
| 2020-01-01 00:00:00.000000        |
+-----------------------------------+
1 row in set, 1 warning (0.00 sec)

mysql> select least(str_type, datetime_type) from t;
+--------------------------------+
| least(str_type, datetime_type) |
+--------------------------------+
| 2020-01-01 00:00:00            |
+--------------------------------+
1 row in set, 1 warning (0.00 sec)

[ti-challenge-bot]

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

[XuHuaiyu]

dup with #9765
I'll close this issue, if you have any further questions, feel free to reopen 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