Bug #35298 GROUP_CONCAT with DISTINCT can crash the server
Submitted: 14 Mar 2008 17:45 Modified: 14 May 2008 2:26
Reporter: Franz Hänel Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S2 (Serious)
Version:5.0.56/5.0/5.1/6.0BK OS:Linux
Assigned to: Alexey Kopytov CPU Architecture:Any
Tags: crash, distinct, group_concat
Triage: D1 (Critical)

[14 Mar 2008 17:45] Franz Hänel
Description:
The combination of GROUP_CONCAT, DISTINCT and LEFT JOIN can crash the server when ther are no matching rows in the join_table. Works fine in 5.0.52, 5.0.54. In 5.0.56 it crashes when using MyISAM or MEMORY tables but not with INNODB.

How to repeat:
Test case:
USE test;
DROP TABLE IF EXISTS t1,t2;
CREATE TABLE t1 (a int(10) unsigned NOT NULL) ENGINE=MyISAM DEFAULT CHARSET=latin1;
CREATE TABLE t2 (a int(10) unsigned NOT NULL) ENGINE=MyISAM DEFAULT CHARSET=latin1;
INSERT INTO t1 VALUES(1);
SELECT GROUP_CONCAT(DISTINCT t2.a)
FROM t1
LEFT JOIN t2 ON(t2.a = t1.a)
GROUP BY t1.a;

This is what happens:

mysql> USE test;
Database changed
mysql> DROP TABLE IF EXISTS t1,t2;
Query OK, 0 rows affected (0.05 sec)

mysql> CREATE TABLE t1 (a int(10) unsigned NOT NULL) ENGINE=MyISAM DEFAULT CHARSET=latin1;
Query OK, 0 rows affected (0.07 sec)

mysql> CREATE TABLE t2 (a int(10) unsigned NOT NULL) ENGINE=MyISAM DEFAULT CHARSET=latin1;
Query OK, 0 rows affected (0.06 sec)

mysql> INSERT INTO t1 VALUES(1);
Query OK, 1 row affected (0.00 sec)

mysql> SELECT GROUP_CONCAT(DISTINCT t2.a)
    -> FROM t1
    -> LEFT JOIN t2 ON(t2.a = t1.a)
    -> GROUP BY t1.a;
ERROR 2013 (HY000): Lost connection to MySQL server during query
mysql>

This works however:
mysql> SELECT GROUP_CONCAT(t2.a)
    -> FROM t1
    -> LEFT JOIN t2 ON(t2.a = t1.a)
    -> GROUP BY t1.a;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    1
Current database: test

+--------------------+
| GROUP_CONCAT(t2.a) |
+--------------------+
| NULL               |
+--------------------+
1 row in set (0.00 sec)

mysql> INSERT INTO t2 VALUES(1);
Query OK, 1 row affected (0.00 sec)

mysql> SELECT GROUP_CONCAT(DISTINCT t2.a)
    -> FROM t1
    -> LEFT JOIN t2 ON(t2.a = t1.a)
    -> GROUP BY t1.a;
+-----------------------------+
| GROUP_CONCAT(DISTINCT t2.a) |
+-----------------------------+
| 1                           |
+-----------------------------+
1 row in set (0.00 sec)

mysql> TRUNCATE TABLE t2;
Query OK, 0 rows affected (0.00 sec)

mysql> ALTER TABLE t2 ENGINE=INNODB;
Query OK, 0 rows affected (0.12 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> SELECT GROUP_CONCAT(DISTINCT t2.a)
    -> FROM t1
    -> LEFT JOIN t2 ON(t2.a = t1.a)
    -> GROUP BY t1.a;
+-----------------------------+
| GROUP_CONCAT(DISTINCT t2.a) |
+-----------------------------+
| NULL                        |
+-----------------------------+
1 row in set (0.00 sec)

mysql>
[14 Mar 2008 21:18] Miguel Solorzano
Thank you for the bug report.

warning: Missing the separate debug info file: /usr/lib/debug/.build-id/72/67a2ecd318b0f87a0747a6986d0d6dc01c6d8d.debug
[New Thread 1147435344 (LWP 5677)]
080314 18:09:34 [Note] /home/miguel/dbs/5.0/libexec/mysqld: ready for connections.
Version: '5.0.60-debug'  socket: '/tmp/mysql.sock'  port: 3306  Source distribution
[New Thread 1147701584 (LWP 5679)]

Program received signal SIGSEGV, Segmentation fault.
[Switching to Thread 1147701584 (LWP 5679)]
0x0000000000997506 in free_tree (tree=0x170, free_flags=2) at tree.c:141
141       if (tree->root)                               /* If initialized */
Current language:  auto; currently c
(gdb) 

[New Thread 1147435344 (LWP 5694)]
080314 18:11:27 [Note] Event Scheduler: Loaded 0 events
080314 18:11:27 [Note] /home/miguel/dbs/5.1/libexec/mysqld: ready for connections.
Version: '5.1.24-rc-debug'  socket: '/tmp/mysql.sock'  port: 3306  Source distribution
[New Thread 1147701584 (LWP 5696)]

Program received signal SIGSEGV, Segmentation fault.
[Switching to Thread 1147701584 (LWP 5696)]
0x0000000000aa7bae in free_tree (tree=0x178, free_flags=2) at tree.c:141
141       if (tree->root)                               /* If initialized */
Current language:  auto; currently c
(gdb) 

[New Thread 1262823760 (LWP 5722)]
080314 18:12:57 [Note] Event Scheduler: Loaded 0 events
080314 18:12:57 [Note] /home/miguel/dbs/6.0/libexec/mysqld: ready for connections.
Version: '6.0.5-alpha-debug'  socket: '/tmp/mysql.sock'  port: 3306  Source distribution
[New Thread 1263090000 (LWP 5725)]

Program received signal SIGSEGV, Segmentation fault.
[Switching to Thread 1263090000 (LWP 5725)]
0x0000000000c6884a in free_tree (tree=0x178, free_flags=2) at tree.c:141
141       if (tree->root)                               /* If initialized */
Current language:  auto; currently c
(gdb)
[17 Mar 2008 19:32] Omer Barnir
Workaround: rewrite query with select group_concat (t2) from (select distinct t2 a from ....
[16 Apr 2008 16:23] Alexey Kopytov
Bug #35945 and bug #36024 were marked as duplicates of this one.
[17 Apr 2008 23:23] Sergey Petrunya
Approved with comments, see reply to the commit email
[18 Apr 2008 7:01] 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/45589

ChangeSet@1.2606, 2008-04-18 11:00:51+04:00, kaa@kaamos.(none) +3 -0
  Fix for bug #35298: GROUP_CONCAT with DISTINCT can crash the server
  
  The bug is a regression introduced by the patch for bug32798.
  
  The code in Item_func_group_concat::clear() relied on the 'distinct'
  variable to check if 'unique_filter' was initialized. That, however,
  is not always valid because Item_func_group_concat::setup() can do
  shortcuts in some cases w/o initializing 'unique_filter'.
  
  Fixed by checking the value of 'unique_filter' instead of 'distinct'
  before dereferencing.
[6 May 2008 0:25] Bugs System
Pushed into 5.0.62
[6 May 2008 0:29] Bugs System
Pushed into 5.1.25-rc
[6 May 2008 0:33] Bugs System
Pushed into 6.0.6-alpha
[14 May 2008 2:26] Paul Dubois
Noted in 5.0.62, 5.1.25, 6.0.6 changelogs.

The combination of GROUP_CONCAT(), DISTINCT, and LEFT JOIN could crash
the server when the right table is empty.
[28 May 2008 10:02] Bugs System
Pushed into 6.0.6-alpha