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

[9 Jul 2003 15:59] Lee Krawczyk
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.
[9 Jul 2003 16:07] Lee Krawczyk
Had the wrong version number.
[10 Jul 2003 13:23] Oleksandr Byelkin
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.
[10 Jul 2003 14:42] Sinisa Milivojevic
Can you upload a table so that we can re-check it ???
[14 Jul 2003 13:18] Lee Krawczyk
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!
[14 Jul 2003 22:44] Oleksandr Byelkin
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)
[15 Jul 2003 14:54] Lee Krawczyk
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.
[15 Jul 2003 16:18] Miguel Solorzano
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)
[16 Jul 2003 2:52] Oleksandr Byelkin
Thank you for bug report. 
I tested it on last bk tree. and found that this bug is fixed.