MySQL Bugs: #104384: GROUP_CONCAT returns different data.

Bug #104384	GROUP_CONCAT returns different data.
Submitted:	22 Jul 2021 7:58	Modified:	22 Jul 2021 13:00
Reporter:	Vyacheslav Korolev	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Data Types	Severity:	S3 (Non-critical)
Version:	8.0	OS:	Ubuntu
Assigned to:		CPU Architecture:	Any
Tags:	group_concat

Description:
I get different results depending on the number of records in the table. Although i expect NULL.
A scenario in which everything works as expected:
Three tables: 
1. table_categories
+-------------+
| category_id |
+-------------+
|         271 |
+-------------+

2. table_products_categories
+------------+-------------+
| product_id | category_id |
+------------+-------------+
|        256 |         270 |
|        257 |         271 |
+------------+-------------+

3. table_products

Query:

SELECT cp.product_id, p.company_id, GROUP_CONCAT(c.category_id) AS owner_groups FROM table_products p LEFT JOIN table_products_categories cp ON p.product_id = cp.product_id LEFT JOIN tablet_categories c ON cp.category_= c.category_id id AND p.company_id = c.company_id WHERE p.product_id IN ("256") GROUP BY cp.product_id;
+------------+------------+--------------+
| product_id | company_id | owner_groups |
+------------+------------+--------------+
|        256 |          1 | NULL         |
+------------+------------+--------------+

How to repeat:
Three tables:
1. table_categories
2. table_products_categories
3. table_products

I have one product. table_categories is empty.

table_products_categories:

+------------+-------------+
| product_id | category_id |
+------------+-------------+
|        253 |         266 |
+------------+-------------+

Query:

SELECT cp.product_id, p.company_id, GROUP_CONCAT(c.category_id) AS owner_groups FROM table_products p LEFT JOIN table_products_categories cp ON p.product_id = cp.product_id LEFT JOIN tablet_categories c ON cp.category_id = c.category_id AND p.company_id = c.company_id WHERE p.product_id IN ("253") GROUP BY cp.product_id;

+------------+------------+--------------+
| product_id | company_id | owner_groups |
+------------+------------+--------------+
|        253 |          1 |              |
+------------+------------+--------------+

owner_groups will be equal to an empty string although I expect NULL

I made a mistake in the description and wrote a "tablet", I made a query locally without errors.

Hi Mr. Korolev,

Thank you for your bug report.

However, this is, probably not a bug.

GROUP_CONCAT() will return a NULL if there are no non-NULL values for that particular query. This is how it works. It is not according to SQL standard, since this is MySQL-specific feature.

To check that, please send us the output from the following query:

SELECT cp.product_id, p.company_id, c.category_id AS owner_groups FROM table_products p LEFT JOIN table_products_categories cp ON p.product_id = cp.product_id LEFT JOIN tablet_categories c ON cp.category_= c.category_id id AND p.company_id = c.company_id WHERE p.product_id IN ("256") GROUP BY cp.product_id;

Many thanks in advance.

This behavior is only on mysql v8, on v5 this is not observed.

SELECT cp.product_id, p.company_id, c.category_id AS owner_groups FROM table_products p LEFT JOIN table_products_categories cp ON p.product_id = cp.product_id LEFT JOIN table_categories c ON cp.category_id = c.cat
egory_id AND p.company_id = c.company_id WHERE p.product_id IN ("249") GROUP BY cp.product_id, c.category_id;
+------------+------------+--------------+
| product_id | company_id | owner_groups |
+------------+------------+--------------+
|        249 |          1 |         NULL |
+------------+------------+--------------+
1 row in set (0,00 sec)

SELECT cp.product_id, p.company_id, GROUP_CONCAT(c.category_id) AS owner_groups FROM table_products p LEFT JOIN table_products_categories cp ON p.product_id = cp.product_id LEFT JOIN table_categories c ON cp.categ
ory_id = c.category_id AND p.company_id = c.company_id WHERE p.product_id IN ("249") GROUP BY cp.product_id, c.category_id;
+------------+------------+--------------+
| product_id | company_id | owner_groups |
+------------+------------+--------------+
|        249 |          1 |              |
+------------+------------+--------------+
1 row in set (0,01 sec)

Hi Mr. Korolev,

You are correct.

This is intended change in MySQL 8.0 and it is properly documented in our Reference Manual.

That is why new versions are necessary. To change incorrect behaviour, so that applications that rely on the old behaviour do not break and need not be changed, as long as they use the same version.

Not a bug.

Please, I cannot find where this is properly documented.

The documentation from the link (https://dev.mysql.com/doc/refman/8.0/en/aggregate-functions.html#function_group-concat) says:

> It returns NULL if there are no non-NULL values.

From the query that you asked me to do, you can see that null values are concatenated. Shouldn't I get null?

Hi Mr. Korolev,

Since you have a mixture of NULL and non-NULL values, in the query with multiple rows, the empty string is a correct result.