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: | |
| 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
[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.