Bug #112319 GROUP_CONCAT with GROUP BY and WHERE clause turns NULL into empty strings.

Description:
When using GROUP_CONCAT with GROUP BY, the GROUP_CONCAT column sometimes becomes an empty string when it should be NULL.

How to repeat:
I created 3 tables:

account:
id int not null primary key auto_increment
global_account_id int not null
latest_version_id int not null

account_version:
id int not null primary key auto_increment
account_type_id int not null
global_account_id int not null

account_type
id int not null primary key auto_increment
type varchar(64) not null
description varchar(64)

Then, I populate them with the following data:

in account:
id | global_account_id | latest_version_id
1 | 1 | 1
2 | 2 | 4
3 | 2 | 6

in account_version:
id | account_type_id | global_account_id
1 | 1 | 1
4 | 2 | 2
6 | 3 | 2

in account_type:
id | type | description
1 | customer | Customer
2 | manager | Manager
3 | other | NULL

If you run the following query:

select account.id, account.global_account_id, group_concat(description) from account join account_version on account.global_account_id = account_version.global_account_id and account_version.id = account.latest_version_id join account_type on account_version.account_type_id = account_type.id group by account.id;

You get:
+----+-------------------+---------------------------+
| id | global_account_id | group_concat(description) |
+----+-------------------+---------------------------+
|  1 |                 1 | Customer                  |
|  2 |                 2 | Manager                   |
|  3 |                 2 | NULL                      |
+----+-------------------+---------------------------+

As one might expect.

Simply add 'where account.id = 3' to the query, though and it breaks:

select account.id, account.global_account_id, group_concat(description) from account join account_version on account.global_account_id = account_version.global_account_id and account_version.id = account.latest_version_id join account_type on account_version.account_type_id = account_type.id where account.id = 3 group by account.id;
+----+-------------------+---------------------------+
| id | global_account_id | group_concat(description) |
+----+-------------------+---------------------------+
|  3 |                 2 |                           |
+----+-------------------+---------------------------+

group_concat is now returning an empty string where it should be NULL.

Hello Justin W,

Thank you for the report and test case.
Verified as described.

regards,
Umesh

Already fixed in the upcoming releases 8.0.35, 8.2.0 under the heading of

Bug#35730982: GROUP_CONCAT results were displayed as NULL before 8.0.23