[MDEV-8258] GROUP_CONCAT shows 1 result having all null values when no result can be found Created: 2015-06-02  Updated: 2015-06-02  Resolved: 2015-06-02

Status: Closed
Project: MariaDB Server
Component/s: OTHER
Affects Version/s: 5.5.43-galera
Fix Version/s: N/A

Type: Bug Priority: Major
Reporter: Randy Geraads Assignee: Unassigned
Resolution: Not a Bug Votes: 0
Labels: None
Environment:

Debian


 Description   

Also issued at mysql bugs: http://bugs.mysql.com/bug.php?id=77222

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

Example: 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)

 Comments   
Comment by Elena Stepanova [ 2015-06-02 ]

It's common behavior for aggregate functions.

MariaDB [test]> SELECT GROUP_CONCAT(name) FROM test;
 
+--------------------+
 
| GROUP_CONCAT(name) |
 
+--------------------+
 
| NULL               |
 
+--------------------+
 
1 row in set (0.00 sec)
 
 
 
MariaDB [test]> SELECT MAX(name) FROM test;
 
+-----------+
 
| MAX(name) |
 
+-----------+
 
| NULL      |
 
+-----------+
 
1 row in set (0.01 sec)
 
 
 
# etc

It is also explicitly documented, both in MySQL manual and MariaDB KB:
https://mariadb.com/kb/en/mariadb/group_concat/

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

Generated at Thu Feb 08 07:25:48 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.