Bug #15307 GROUP_CONCAT() with ORDER BY returns empty set on information_schema
Submitted: 29 Nov 2005 10:33 Modified: 26 Jan 2006 2:20
Reporter: Beat Vontobel (Silver Quality Contributor) (OCA) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL 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: Sergei Glukhov CPU Architecture:Any

[29 Nov 2005 10:33] Beat Vontobel
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:
-
[29 Nov 2005 12:13] Miguel Solorzano
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.
[29 Nov 2005 12:21] Beat Vontobel
No, that's actually where I found the bug. I just added an "on information_schema" to the bug synopsis to reflect this.
[14 Dec 2005 12:26] Bugs System
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
[18 Jan 2006 14:25] Bugs System
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
[24 Jan 2006 12:43] Bugs System
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
[24 Jan 2006 12:46] Sergei Glukhov
Fixed in 5.0.19
[26 Jan 2006 2:20] Mike Hillyer
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>