Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-8258

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

        Details

        • Type: Bug
        • Status: Closed
        • Priority: Major
        • Resolution: Not a Bug
        • Affects Version/s: 5.5.43-galera
        • Fix Version/s: N/A
        • Component/s: OTHER
        • 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)

            Gliffy Diagrams

              Attachments

                Activity

                Hide
                Permalink
                elenst Elena Stepanova added a comment -

                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.

                Show
                elenst Elena Stepanova added a comment - 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.

                  People

                  • Assignee:
                    Unassigned
                    Reporter:
                    rgeraads Randy Geraads
                  • Votes:
                    0 Vote for this issue
                    Watchers:
                    2 Start watching this issue

                    Dates

                    • Created:
                      Updated:
                      Resolved: