Bug #94614 In cases like ifnull(datetime(2), time(3)), the wrong answer is returned.

• Submitted: 10 Mar 2019 2:47
• Modified: 25 Apr 2019 2:07
• Reporter: Hope Lee (OCA)
• Status: Closed
• Category: MySQL Server: Optimizer
• Severity: S2 (Serious)
• Version: 8.0.15-rds-dev, 8.0.15, 5.7.25,5.6.43
• OS: Any
• CPU Architecture: Any

[10 Mar 2019 2:47] Hope Lee

Description:
In cases like ifnull(datetime(2), time(3)), the wrong answer is returned with "group by".

How to repeat:
mysql> SET timestamp=UNIX_TIMESTAMP('2011-11-10 18:04:30');
mysql> CREATE TABLE test_table4 (dt2 DATETIME(2), t3 TIME(3), d DATE);
mysql> INSERT INTO test_table4 VALUES ('2001-01-01 00:00:00.12', '00:00:00.567', '2002-01-01');
mysql> select CONCAT(IFNULL(t3, d)) as col1 from test_table4;
+-------------------------+
| col1                    |
+-------------------------+
| 2011-11-10 00:00:00.567 |
+-------------------------+
1 row in set (0.00 sec)
mysql> select CONCAT(IFNULL(t3, d)) as col1 from test_table4 group by col1;
+----------------+
| col1           |
+----------------+
| 2011-11-10 00: |
+----------------+
1 row in set (0.00 sec)

[10 Mar 2019 5:27] MySQL Verification Team

Hello Lee,

Thank you for the report and test case.

regards,
Umesh

[25 Apr 2019 2:07] Jon Stephens

Documented fix in the MySQL 8.0.18 changelog as follows:

    The resolution procedure for the IFNULL() function differed from
    that for all other functions derived from CASE(), including
    COALESCE(), which caused incorrect length information to be
    generated for certain numeric expressions.

Closed.