Bug #77222 GROUP_CONCAT shows 1 result having all null values when no result can be found

• Submitted: 2 Jun 2015 8:09
• Modified: 3 Jun 2015 10:49
• Reporter: Randy Geraads
• Status: Not a Bug
• Category: MySQL Server: DML
• Severity: S3 (Non-critical)
• Version: 5.5.43
• OS: Linux
• CPU Architecture: Any

[2 Jun 2015 8:09] Randy Geraads

Description:
GROUP_CONCAT shows 1 result having all null values when no result can be found, instead of showing 0 results.

http://sqlfiddle.com/#!9/2b29e/1

How to repeat:
CREATE TABLE `test` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;

SELECT *, GROUP_CONCAT(name) FROM test

Expected result:
Record Count: 0

Actual result:
Record Count: 1
id	name	GROUP_CONCAT(name)
(null)	(null)		(null)

Suggested fix:
GROUP BY NULL should be executed by default when not stated in the SQL statement.

[2 Jun 2015 8:16] Randy Geraads

wrong version

[3 Jun 2015 10:49] MySQL Verification Team

Hello Randy Geraads,

Thank you for the report.
Imho, this is by design and similar to other aggregation functions(GROUP_CONCAT is one of MySQL extensions to ANSI where you don't need to name all selected columns in the GROUP BY clause). 

-- This behavior occurs if the ONLY_FULL_GROUP_BY SQL mode is not enabled. If that mode is enabled, the server rejects the query as illegal.

mysql> SELECT *, GROUP_CONCAT(name) FROM test;
+----+------+--------------------+
| id | name | GROUP_CONCAT(name) |
+----+------+--------------------+
| NULL | NULL | NULL               |
+----+------+--------------------+
1 row in set (0.00 sec)

-- with GROUP BY, you would see correct results

mysql> SELECT *, GROUP_CONCAT(name) FROM test group by name;
Empty set (0.00 sec)

-- with only_full_group_by

mysql>  set sql_mode='only_full_group_by';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT *, GROUP_CONCAT(name) FROM test;
ERROR 1140 (42000): Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause
mysql>
mysql> SELECT *, GROUP_CONCAT(name) FROM test group by id,name;
Empty set (0.00 sec)

Also, GROUP_CONCAT is extension to ANSI, please see https://dev.mysql.com/doc/refman/5.5/en/extensions-to-ansi.html
http://dev.mysql.com/doc/refman/5.5/en/group-by-handling.html

Thanks,
Umesh