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.
