Bug #99485 Setting variable to CONCAT('') sets it to NULL instead of ''
Submitted: 8 May 2020 9:08 Modified: 11 May 2020 18:11
Reporter: Lars Tangvald Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Data Types Severity:S3 (Non-critical)
Version:5.7.31 OS:Any
Assigned to: CPU Architecture:Any

[8 May 2020 9:08] Lars Tangvald
Description:
Original Ubuntu bug report: https://bugs.launchpad.net/ubuntu/+source/mysql-5.7/+bug/1877442

Running CONCAT('') returns empty string, but setting a variable to the result of CONCAT('') sets it to NULL instead.

Works as expected in 8.0 and 5.7.29

How to repeat:
SELECT CONCAT('');
will result in an empty string as expected however if the result is assigned to a variable with:
SET @var = ""; SELECT @var := CONCAT('');
the variable will be NULL instead of an empty string.
[8 May 2020 9:17] Terje Røsten
Posted by developer:
 
Problem was introduced in 

commit d00361962f2836026c0661fb1ecb9067c5aed073 (HEAD)
Author: Maheedhar PV <maheedhar.panchalamarri.venka@oracle.com>
Date:   Wed Feb 5 11:32:05 2020 +0530

    Bug#30350206 - CONCAT() CAN GENERATE CORRUPTED OUTPUT

commit: d8d0c798e5

mysql> select concat('');
+------------+
| concat('') |
+------------+
|            |
+------------+
1 row in set (0,00 sec)

mysql> SET @var = ""; SELECT @var := CONCAT('');
Query OK, 0 rows affected (0,00 sec)

+--------------------+
| @var := CONCAT('') |
+--------------------+
|                    |
+--------------------+
1 row in set (0,00 sec)

commit: d00361962f2

mysql> select concat('');
+------------+
| concat('') |
+------------+
|            |
+------------+

1 row in set (0,00 sec)

mysql> SET @var = ""; SELECT @var := CONCAT('');
Query OK, 0 rows affected (0,00 sec)
+--------------------+
| @var := CONCAT('') |
+--------------------+
| NULL               |
+--------------------+
1 row in set (0,00 sec)
[11 May 2020 9:57] Erik Sørnes
I will say this is critical. Our application, "slurm", crashes because of this. We cannot control jobs in our cluster.
[11 May 2020 18:11] Paul DuBois
Posted by developer:
 
Fixed in 5.7.31, 8.0.21.

Assigning CONCAT(') or CONCAT_WS(') to a variable set the variable
to NULL, not the empty string.
[28 May 2020 9:00] Ralf Spettnagel
The bug is also present in 5.6.48 and will hopefully be fixed in 5.6.49