MySQL Bugs: #15307: GROUP_CONCAT() with ORDER BY returns empty set on information

Bug #15307	GROUP_CONCAT() with ORDER BY returns empty set on information_schema
Submitted:	29 Nov 2005 11:33	Modified:	26 Jan 2006 3:20
Reporter:	Beat Vontobel (Silver Quality Contributor) (SCA)
Status:	Closed
Category:	Server	Severity:	S2 (Serious)
Version:	5.0.16-standard-log-i686-glibc23/5.0.17 BK	OS:	Linux (Linux 2.4.21 (SuSE Professional))
Assigned to:	Sergey Gluhov	Target Version:

Description:
When an ORDER BY is applied to some queries with a GROUP_CONCAT() in the SELECT part the
server suddenly returns an empty result set, even if the query without the ORDER BY
returned rows.

I don't know if this could will be fixed at the same time as Bug #14169 (currently marked
as in progress) that concerns a change of data types when an ORDER BY is applied to a
query with a GROUP_CONCAT() or if this is a completely different issue.

How to repeat:
SELECT COLUMN_TYPE, COUNT(*) AS n FROM information_schema.COLUMNS GROUP BY COLUMN_TYPE
ORDER BY n DESC;
...
211 rows in set (0.39 sec)

SELECT COLUMN_TYPE, GROUP_CONCAT(TABLE_SCHEMA, '.', TABLE_NAME), COUNT(*) AS n FROM
information_schema.COLUMNS GROUP BY COLUMN_TYPE;
...
1410 rows in set, 1 warning (0.40 sec)

SELECT COLUMN_TYPE, GROUP_CONCAT(TABLE_SCHEMA, '.', TABLE_NAME), COUNT(*) AS n FROM
information_schema.COLUMNS GROUP BY COLUMN_TYPE ORDER BY n DESC;
...
Empty set (0.23 sec)

Suggested fix:
-

I was able to repeat the behavior reported with information_schema, however
I was unable to repeat with tables defined on my own. Do you have a test
case without to use information_schema?

Thanks in advance.

No, that's actually where I found the bug. I just added an "on information_schema" to the
bug synopsis to reflect this.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/33243

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/1267

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/1561

Fixed in 5.0.19

Added to 5.0.19 changelog:

      <listitem>
        <para>
          A <literal>SELECT</literal> query which contained a
          <literal>GROUP_CONCAT()</literal> and an <literal>ORDER
            BY</literal> clause against the
          <literal>INFORMATION_SCHEMA</literal> resulted in an empty
          result set. (Bug #15307)
        </para>
      </listitem>