GREATEST function with plus operations to time/date type gives wrong result.

[espresso98]

Bug Report

1. Minimal reproduce step

SELECT GREATEST(time '20:00:00', 120000) + 0.00;
SELECT GREATEST(date '2005-05-05', 20010101, 20040404, 20030303) + 0;
SELECT GREATEST(date '1995-05-05', 19910101, 20050505, 19930303) + 0.00;

2. What did you expect to see?

mysql> SELECT GREATEST(time '20:00:00', 120000) + 0.00;
+------------------------------------------+
| GREATEST(time '20:00:00', 120000) + 0.00 |
+------------------------------------------+
|                                200000.00 |
+------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT GREATEST(date '2005-05-05', 20010101, 20040404, 20030303) + 0;
+---------------------------------------------------------------+
| GREATEST(date '2005-05-05', 20010101, 20040404, 20030303) + 0 |
+---------------------------------------------------------------+
|                                                      20050505 |
+---------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT GREATEST(date '1995-05-05', 19910101, 20050505, 19930303) + 0.00;
+------------------------------------------------------------------+
| GREATEST(date '1995-05-05', 19910101, 20050505, 19930303) + 0.00 |
+------------------------------------------------------------------+
|                                                      20050505.00 |
+------------------------------------------------------------------+
1 row in set (0.00 sec)

3. What did you see instead

TiDB> SELECT GREATEST(time '20:00:00', 120000) + 0.00;
+------------------------------------------+
| GREATEST(time '20:00:00', 120000) + 0.00 |
+------------------------------------------+
|                                       20 |
+------------------------------------------+
1 row in set, 1 warning (0.00 sec)

TiDB> SELECT GREATEST(date '2005-05-05', 20010101, 20040404, 20030303) + 0;
+---------------------------------------------------------------+
| GREATEST(date '2005-05-05', 20010101, 20040404, 20030303) + 0 |
+---------------------------------------------------------------+
|                                                          2005 |
+---------------------------------------------------------------+
1 row in set, 1 warning (0.00 sec)

TiDB> SELECT GREATEST(date '1995-05-05', 19910101, 20050505, 19930303) + 0.00;
+------------------------------------------------------------------+
| GREATEST(date '1995-05-05', 19910101, 20050505, 19930303) + 0.00 |
+------------------------------------------------------------------+
|                                                             2005 |
+------------------------------------------------------------------+
1 row in set, 1 warning (0.00 sec)

4. What is your TiDB version? (Required)

tidb_version(): Release Version: v5.2.2
Edition: Community
Git Commit Hash: da1c21fd45a4ea5900ac16d2f4a248143f378d18
Git Branch: heads/refs/tags/v5.2.2
UTC Build Time: 2021-10-20 06:03:29
GoVersion: go1.16.4
Race Enabled: false
TiKV Min Version: v3.0.0-60965b006877ca7234adaced7890d7b029ed1306
Check Table Before Drop: false

[mjonss]

related to #29498

[ramanich1]

more issues with DATE/DATETIME comparison using LEAST()/GREATEST() functions:

set sql_mode = default;
select least(cast('01-01-01' as date), '01-01-02') as a,
greatest(cast('01-01-01' as date), '01-01-02') as b,
least(cast('01-01-01' as date), '01-01-02') + 0 as c,
greatest(cast('01-01-01' as date), '01-01-02') + 0 as d,
least(cast('01-01-01' as datetime), '01-01-02') + 0 as e,
cast(least(cast('01-01-01' as datetime), '01-01-02') as signed) as f,
cast(least(cast('01-01-01' as datetime), '01-01-02') as decimal(16,2)) as g;

---mysql 5.7:
+------------+----------+----------+----------+----------------+----------------+-------------------+
| a          | b        | c        | d        | e              | f              | g                 |
+------------+----------+----------+----------+----------------+----------------+-------------------+
| 2001-01-01 | 01-01-02 | 20010101 | 20010102 | 20010101000000 | 20010101000000 | 20010101000000.00 |
+------------+----------+----------+----------+----------------+----------------+-------------------+
--mysql 8.0
+------------+------------+----------+----------+----------------+----------------+-------------------+
| a          | b          | c        | d        | e              | f              | g                 |
+------------+------------+----------+----------+----------------+----------------+-------------------+
| 2001-01-01 | 2001-01-02 | 20010101 | 20010102 | 20010101000000 | 20010101000000 | 20010101000000.00 |
+------------+------------+----------+----------+----------------+----------------+-------------------+
---Tidb:
+---------------------+---------------------+------+------+------+------+---------+
| a                   | b                   | c    | d    | e    | f    | g       |
+---------------------+---------------------+------+------+------+------+---------+
| 2001-01-01 00:00:00 | 2001-01-02 00:00:00 | 2001 | 2001 | 2001 | 2001 | 2001.00 |
+---------------------+---------------------+------+------+------+------+---------+

[yibin87]

Reproduced on dev machine.

[yibin87]

Update some observations of MySQL confusing type conversion behavior:

1. Least, Greatest function don't follow the same type conversion rules as '<', '>' when arguments are not of the same type: the former one will convert numeric to string while the later will convert string to numeric:mysql> select '11' < 2;
   +----------+
   | '11' < 2 |
   +----------+
   | 0 |
   +----------+
   1 row in set (0.01 sec)

mysql> select least('11', 2);
+----------------+
| least('11', 2) |
+----------------+
| 11 |
+----------------+
1 row in set (0.00 sec)

2. MySQL has some tricky type inference mechnism that will affect least/greatest's behavior, which means the same function call might behavior differently when combined with different expressions:
   mysql> select greatest(time '20:00:00', 30000), greatest(time '20:00:00', 30000) + 0.00, concat(greatest(time '20:00:00', 30000), 'end');
   +----------------------------------+-----------------------------------------+-------------------------------------------------+
   | greatest(time '20:00:00', 30000) | greatest(time '20:00:00', 30000) + 0.00 | concat(greatest(time '20:00:00', 30000), 'end') |
   +----------------------------------+-----------------------------------------+-------------------------------------------------+
   | 30000 | 200000.00 | 30000end |
   +----------------------------------+-----------------------------------------+-------------------------------------------------+
   1 row in set (0.01 sec)
   BTW, MySQL annouced to fix this issue from version 8.0.4. However, I can still observe this behavior in 8.0.27.

[yibin87]

So TiDB's previous behavior seems reasonable, GREATEST(time '20:00:00', 120000) + 0.00

1. First convert both two arguments of GREATEST to time type and do comparision and get time'20:00:00'
2. Cast time'20:00:00' as string as the return of greatest function: '20:00:00'
3. Finally plus('20:00:00', 0.00) => 20.
   Unfortunately, now TiDB latest trunk code returns '2020-00-00 00:00:00' when calculate GREATEST(time '20:00:00', 120000), I'll look at this strange behavior.

[XuHuaiyu]

• SELECT GREATEST(time '20:00:00', 120000) + 0.00; should return 20, I reported a bug to MySQL and got verified.
  https://bugs.mysql.com/bug.php?id=105983
• But the return type of greatest/least should be polished further.

[github-actions]

Please check whether the issue should be labeled with 'affects-x.y' or 'fixes-x.y.z', and then remove 'needs-more-info' label.