Bug #112422 The outer select group_concat(NULL) return an empty string

• Submitted: 22 Sep 2023 1:54
• Modified: 25 Sep 2023 11:04
• Reporter: bear chen
• Status: Verified
• Category: MySQL Server: Optimizer
• Severity: S3 (Non-critical)
• Version: 8.0
• OS: Any
• CPU Architecture: Any

[22 Sep 2023 1:54] bear chen

Description:
The field value of the inner subquery returns NULL, and the outer GROUP_CONCAT(NULL) returns an empty string.

How to repeat:
8.0.32 version:
root@localhost:db01 8.0.32 09:40:40> SELECT group_concat(a.b) FROM (SELECT NULL as b) as a;
+-------------------+
| group_concat(a.b) |
+-------------------+
|                   |
+-------------------+
1 row in set (0.00 sec)

5.7.38 vesrion:
root@localhost:db01 5.7.38-log 09:50:22> SELECT group_concat(a.b) FROM (SELECT NULL as b) as a;
+-------------------+
| group_concat(a.b) |
+-------------------+
| NULL              |
+-------------------+
1 row in set (0.00 sec)

Suggested fix:
Official documentation:

https://dev.mysql.com/doc/refman/8.0/en/aggregate-functions.html#function_group-concat

GROUP_CONCAT(expr)

This function returns a string result with the concatenated non-NULL values from a group. It returns NULL if there are no non-NULL values.

SQL query results should be NULL.

[22 Sep 2023 10:47] MySQL Verification Team

Hi Mr. chen,

Thank you for your bug report.

However, we can not repeat it with the latest 8.0 and 8.1:

+--------------------------------------+
| group_concat(a.b)                    |
+--------------------------------------+
| 0x                                   |
+--------------------------------------+

Can't repeat.

[24 Sep 2023 2:35] bear chen

The result should be NULL, that is right?

[25 Sep 2023 11:04] MySQL Verification Team

Yes, you are absolutely correct.

Verified as reported.