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 Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server: DML Severity:S3 (Non-critical)
Version:5.5.43 OS:Linux
Assigned to: 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

Portions of this website are copyright © 2001, 2002 The PHP Group

Page generated in 0.037 sec. using MySQL 8.4.7-u6-cloud

Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.