MySQL Bugs: #821: Aggregate function or group by in derived table causes disconnect from server

Bug #821	Aggregate function or group by in derived table causes disconnect from server
Submitted:	9 Jul 2003 15:59	Modified:	16 Jul 2003 2:52
Reporter:	Lee Krawczyk	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	4.1.0-alpha	OS:	Linux (Linux (Redhat 6.1) 2.2.12-20)
Assigned to:	Oleksandr Byelkin	CPU Architecture:	Any

Description:
I have been testing 4.1.0 subqueries on Linux (Redhat 6.1) and I ran into [root@localhost:3306] ERROR 2013: Lost connection to MySQL server during query after executing the following: 
 
select sum(tbl.sum), sum(cnt) from (select sum, count(*) cnt from wb1 group by sum order by cnt desc) tbl
 
This was done using MySQL Control Center 0.9.2-beta, the latest version of Navicat, and the MySQL client for the 4.1.0 version.  The error message came up after the results were returned correctly to the client.
 
In the case of MySQL Control Center 0.9.2-beta the message came up immediately after the result set was returned.  In the latter two cases the message did not come up until I tried to execute another select statement off of the same database.  I was able to reconnect on the subsequent execution.
 
It appears to have something to do with using the group by clause or an aggregate function within the derived table.  I have had no problems doing simple subqueries.

How to repeat:
Set up a table with 2 columns of type numeric.  My column names were id and sum and the table name was wb1. I had 53 rows in my table. Execute the query stated above.

Suggested fix:
None other than to create a temporary table to hold the first result set and do operations on.

Had the wrong version number.

Thank you for good bug report! 
 
I can't repeat it on last mysql 4.1 from bitkeeper repository. It looks like 
problem already solved.

Can you upload a table so that we can re-check it ???

It appears that it is not the query itself that is causing the problem, but it is the explain plan for the query.

The following is the table I used (id is a primary key):

$ mysql -u root -p ranking
Enter password: 
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2 to server version: 4.1.0-alpha-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> select * from wb1;
+----+-----+
| id | sum |
+----+-----+
| 27 |  19 |
| 17 |  19 |
|  2 |  17 |
| 31 |  16 |
| 37 |  16 |
| 24 |  15 |
| 19 |  15 |
| 49 |  15 |
| 22 |  14 |
| 45 |  14 |
|  7 |  14 |
| 12 |  14 |
| 16 |  14 |
| 40 |  13 |
| 32 |  13 |
|  3 |  13 |
|  5 |  13 |
|  4 |  13 |
| 25 |  13 |
| 14 |  12 |
|  9 |  12 |
| 42 |  12 |
| 46 |  12 |
| 39 |  12 |
| 41 |  12 |
| 44 |  12 |
| 23 |  11 |
| 36 |  11 |
| 30 |  11 |
| 48 |  11 |
| 28 |  11 |
|  6 |  11 |
| 35 |  11 |
| 26 |  11 |
| 29 |  10 |
| 43 |  10 |
| 15 |  10 |
| 18 |  10 |
|  8 |  10 |
| 11 |  10 |
| 13 |   9 |
| 10 |   9 |
| 38 |   9 |
| 21 |   9 |
| 34 |   9 |
|  1 |   9 |
| 20 |   8 |
| 33 |   6 |
| 47 |   5 |
| 50 |   2 |
| 53 |   2 |
| 52 |   2 |
| 51 |   1 |
+----+-----+
53 rows in set (0.00 sec)

The following is an excerpt from a test session showing the query results and error message produced from attempting the EXPLAIN:

mysql> select sum(tbl.sum), sum(cnt) from (select sum, count(*) cnt from wb1
    -> group by sum order by cnt desc) tbl;
+--------------+----------+
| sum(tbl.sum) | sum(cnt) |
+--------------+----------+
|          158 |       53 |
+--------------+----------+
1 row in set (0.00 sec)

mysql> explain select sum(tbl.sum), sum(cnt) from (select sum, count(*) cnt from wb1
    -> group by sum order by cnt desc) tbl;
ERROR 2013: Lost connection to MySQL server during query
mysql> select sum(tbl.sum), sum(cnt) from (select sum, count(*) cnt from wb1
    -> group by sum order by cnt desc) tbl;
ERROR 2006: MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    1
Current database: ranking

+--------------+----------+
| sum(tbl.sum) | sum(cnt) |
+--------------+----------+
|          158 |       53 |
+--------------+----------+
1 row in set (0.01 sec)

mysql> select sum, count(*) cnt from wb1 group by sum order by cnt desc;
+-----+-----+
| sum | cnt |
+-----+-----+
|  11 |   8 |
|  12 |   7 |
|  13 |   6 |
|  10 |   6 |
|   9 |   6 |
|  14 |   5 |
|   2 |   3 |
|  15 |   3 |
|  16 |   2 |
|  19 |   2 |
|   1 |   1 |
|   8 |   1 |
|   6 |   1 |
|  17 |   1 |
|   5 |   1 |
+-----+-----+
15 rows in set (0.00 sec)

mysql> explain select sum, count(*) cnt from wb1 group by sum order by cnt desc;
+----+-------------+-------+------+---------------+------+---------+------+------+---------------------------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra                           |
+----+-------------+-------+------+---------------+------+---------+------+------+---------------------------------+
|  1 | SIMPLE      | wb1   | ALL  | NULL          | NULL |    NULL | NULL |   53 | Using temporary; Using filesort |
+----+-------------+-------+------+---------------+------+---------+------+------+---------------------------------+
1 row in set (0.00 sec)

Notice that the EXPLAIN works fine in queries that do not use derived tables.

Hope this helps!

I try following sequence on last mysql built from repository: 
+ drop table if exists wb1; 
+ Warnings: 
+ Note  1051    Unknown table 'wb1' 
+ create table wb1(id int,sum int); 
+ insert into wb1 values 
(27,19),(17,19),(2,17),(31,16),(37,16),(24,15),(19,15),(49,15),(22,14),(45,14),(7,14),(12,14),(16,14),(40,13),(32,13),(3,13),(5,13),(4,13),(25,13),(14,12),(9,12),(42,12),(46,12),(39,12),(41,12),(44,12),(23,11),(36,11),(30,11),(48,11),(28,11),(6,11),(35,11),(26,11),(29,10),(43,10),(15,10),(18,10),(8,10),(11,10),(13,9),(10,9),(38,9),(21,9),(34,9),(1,9),(20,8),(33,6),(47,5),(50,2),(53,2),(52,2),(51,1); 
+ select sum(tbl.sum), sum(cnt) from (select sum, count(*) cnt from wb1 group 
by sum order by cnt desc) tbl; 
+ sum(tbl.sum)  sum(cnt) 
+ 158   53 
+ explain select sum(tbl.sum), sum(cnt) from (select sum, count(*) cnt from 
wb1 group by sum order by cnt desc) tbl; 
+ id    select_type     table   type    possible_keys   key     key_len ref     
rows    Extra 
+ 1     PRIMARY <derived2>      ALL     NULL    NULL    NULL    NULL    15 
+ 2     DERIVED wb1     ALL     NULL    NULL    NULL    NULL    53      Using 
temporary; Using filesort 
+ drop table wb1; 
 
Can you provide output show create table wb1? (may be it depend of table 
structure)

wb1 was created using MySQL Control Center 0.9.2-beta. Rows were inserted using the following command:

insert into wb1
select wb1, count(*) from pblot
group by wb1

pblot was a standard MyISAM table loaded by an import from Navicat that created 560 rows.

Create table output is:

CREATE TABLE `wb1` (
  `id` decimal(10,0) NOT NULL default '0',
  `sum` decimal(10,0) NOT NULL default '0',
  PRIMARY KEY  (`id`)
) TYPE=MyISAM CHARSET=latin1; 

I replicated the test on another machine (Redhat 9.0 linux 2.4.20-13.9).  Here is the result I got:

$ mysql -u root mysql
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7 to server version: 4.1.0-alpha

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> create database ranking;
Query OK, 1 row affected (0.02 sec)

mysql> connect ranking;
Connection id:    8
Current database: ranking

mysql> show tables;
Empty set (0.00 sec)

mysql> create table wb1(id int,sum int);
Query OK, 0 rows affected (0.01 sec)

mysql> show tables;
+-------------------+
| Tables_in_ranking |
+-------------------+
| wb1               |
+-------------------+
1 row in set (0.00 sec)

mysql> insert into wb1 values 
    -> (27,19),(17,19),(2,17),(31,16),(37,16),(24,15),(19,15),(49,15),(22,14),(45,14),(7,14),(12,14),(16,14),(40,13),(32,13),(3,13),(5,13),(4,13),(25,
13),(14,12),(9,12),(42,12),(46,12),(39,12),(41,12),(44,12),(23,11),(36,11),(30,11),(48,11),(28,11),(6,11),(35,11),(26,11),(29,10),(43,10),(15,10),(18,
10),(8,10),(11,10),(13,9),(10,9),(38,9),(21,9),(34,9),(1,9),(20,8),(33,6),(47,5),(50,2),(53,2),(52,2),(51,1);
Query OK, 53 rows affected (0.00 sec)
Records: 53  Duplicates: 0  Warnings: 0

mysql> select sum(tbl.sum), sum(cnt) from (select sum, count(*) cnt from wb1
    -> group 
    -> by sum order by cnt desc) tbl;
+--------------+----------+
| sum(tbl.sum) | sum(cnt) |
+--------------+----------+
|          158 |       53 |
+--------------+----------+
1 row in set (0.02 sec)

mysql> explain select sum(tbl.sum), sum(cnt) from (select sum, count(*) cnt
    -> from 
    -> wb1 group by sum order by cnt desc) tbl; 
/usr/bin/mysqld_safe: line 320: 29597 Aborted                 $NOHUP_NICENESS $ledir/$MYSQLD $defaults --basedir=$MY_BASEDIR_VERSION --datadir=$DATADI
R $USER_OPTION --pid-file=$pid_file --skip-locking >>$err_log 2>&1

Number of processes running now: 1
ERROR 2013: Lost connection to MySQL server during query
mysql> mysqld process hanging, pid 29606 - killed
030715 14:46:39  mysqld restarted

I downloaded the binary version of 4.1.0-alpha on May 16.  Perhaps I should get the latest build and try again.

I tested this with the latest bk 4.1 tree and seems that
was already fixed:

C:\mysql\bin>mysql test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 4.1.1-alpha-max-debug

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> explain select sum(tbl.sum), sum(cnt) from
    -> (select sum, count(*) cnt from wb1 group by sum order by cnt desc) tbl\G
*************************** 1. row ***************************
           id: 1
  select_type: PRIMARY
        table: <derived2>
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 15
        Extra:
*************************** 2. row ***************************
           id: 2
  select_type: DERIVED
        table: wb1
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 53
        Extra: Using temporary; Using filesort
2 rows in set (0.02 sec)

Thank you for bug report. 
I tested it on last bk tree. and found that this bug is fixed.