Bug #37947 Server crash using View, group_concat, order by
Submitted: 7 Jul 2008 19:53 Modified: 8 Jul 2008 0:14
Reporter: Dave Pullin (Basic Quality Contributor) Email Updates:
Status: Can't repeat Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:5.0.27-community-nt-log OS:Windows (w2k server. Does not reproduce on Linux)
Assigned to: CPU Architecture:Any
Tags: qc

[7 Jul 2008 19:53] Dave Pullin
Description:
Server crashes repeatable on query

select group_concat(personNbr order by full_name)
 from bug.personView
;

'bug.personView' is a view that joins bug.person on itself.

The bug goes away if
1. smaller number of rows created in the table (it needs approx 1000 )
2. view is replaced with equivalent subselect
3. the first operand of the concatenation (ifnull(c.first_name||' ','')||)
in the view is removed.
4. 'order by' clause removed from the group_concat

CONSOLE LOG

mysql> create database if not exists bug ;
Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql> drop table if exists bug.persons,bug.numbers  ;
Query OK, 0 rows affected (0.00 sec)

mysql> create table bug.numbers (number int);
Query OK, 0 rows affected (0.00 sec)

mysql> insert into bug.numbers values
    -> (1), (2), ......, (1083), (1084)
    -> ;
Query OK, 1084 rows affected (0.02 sec)
Records: 1084  Duplicates: 0  Warnings: 0

mysql> CREATE TABLE bug.persons ( `personNbr` int(11) NOT NULL auto_increment, `
first_name` varchar(254) default NULL, `last_name` varchar(254) default NULL, `p
arentNbr` int(11) default NULL, PRIMARY KEY (`personNbr`),  KEY `parentNbr` (`pa
rentNbr`) )
    ->  ;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into bug.persons
    -> select number,'first','last',number from bug.numbers where number between
 1 and 1084
    ->  ;
Query OK, 1084 rows affected (0.02 sec)
Records: 1084  Duplicates: 0  Warnings: 0

mysql> create or replace  algorithm=merge view bug.personview as
    -> select
    -> c.personNbr
    -> ,ifnull(c.first_name||' ','')||ifnull(c.Last_name,p.last_name) as full_na
me
    -> from bug.persons as c
    -> left join bug.persons as p on p.personNbr=c.parentNbr
    -> ;
Query OK, 0 rows affected (0.00 sec)

mysql> select group_concat(personNbr order by full_name)
    ->  from bug.personView
    -> ;
ERROR 2013 (HY000): Lost connection to MySQL server during query
mysql> select version();
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    1
Current database: *** NONE ***

+-------------------------+
| version()               |
+-------------------------+
| 5.0.27-community-nt-log |
+-------------------------+

How to repeat:
run the sql in the attached file
[7 Jul 2008 19:54] Dave Pullin
sql to repeat the bug

Attachment: repeat.sql (application/octet-stream, text), 8.21 KiB.

[7 Jul 2008 20:26] Valeriy Kravchuk
Thank you for a problem report. Please, try to repeat with a newer version, 5.0.51a at least, and inform about the results.
[8 Jul 2008 0:14] Dave Pullin
Does NOT reproduce under  5.0.51b-community-nt-log