MySQL Bugs: #15352: Crash: View within "Group by"-Statement

Bug #15352	Crash: View within "Group by"-Statement
Submitted:	30 Nov 2005 16:05	Modified:	30 Nov 2005 16:48
Reporter:	Martin Huber	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S1 (Critical)
Version:	5.0.16-nt	OS:	Windows (Windows XP, 2000 Server)
Assigned to:		CPU Architecture:	Any

Description:
If I create a view which contains a "Group By"-Clausel and I sent two SELECT-queries on it, the database crashes with the following message: "ERROR 2013 (HY000): Lost connection to MySQL server during query".

How to repeat:
Create a table:

CREATE TABLE t1
(
   id INT NOT NULL ,
   field INT NOT NULL ,
   PRIMARY KEY (id)
);

Insert some data:

INSERT INTO t1 (id, field)
VALUES
(1, 5),
(2, 10),
(3, 15),
(4, 10),
(5, 10),
(6, 15);

Create a view:

CREATE OR REPLACE VIEW v1 AS
SELECT field, MAX(id)
FROM t1
GROUP BY field;

Sent two queries:

SELECT * FROM v1;
SELECT * FROM v1;

Thank you for a bug report. Verified just as described on 5.0.16-nt on XP 64bit also:

C:\Documents and Settings\Administrator>mysql -uroot -p -P3307
Enter password: ****
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 5.0.16-nt

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

mysql> use test;
Database changed
mysql> CREATE TABLE t1
    -> (
    ->    id INT NOT NULL ,
    ->    field INT NOT NULL ,
    ->    PRIMARY KEY (id)
    -> );
Query OK, 0 rows affected (0.14 sec)

mysql> INSERT INTO t1 (id, field)
    -> VALUES
    -> (1, 5),
    -> (2, 10),
    -> (3, 15),
    -> (4, 10),
    -> (5, 10),
    -> (6, 15);
Query OK, 6 rows affected (0.01 sec)
Records: 6  Duplicates: 0  Warnings: 0

mysql> CREATE OR REPLACE VIEW v1 AS
    -> SELECT field, MAX(id)
    -> FROM t1
    -> GROUP BY field;
Query OK, 0 rows affected (0.03 sec)

mysql> select * from v1;
+-------+---------+
| field | MAX(id) |
+-------+---------+
|     5 |       1 |
|    10 |       5 |
|    15 |       6 |
+-------+---------+
3 rows in set (0.02 sec)

mysql> select * from v1;
ERROR 2013 (HY000): Lost connection to MySQL server during query
mysql>

I was not able to repeat the problem you described with 5.0.17-BK on Linux, by the way. It is weird Windows-specific bug.

Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.

If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information 
about accessing the source trees is available at
    http://www.mysql.com/doc/en/Installing_source_tree.html

Additional info:

C:\mysql5017>bin\mysql --defaults-file=c:\mysql5017\s5017.ini -uroot -p --prompt="win5017>"
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 5.0.17-nt-max

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

win5017>use test
Database changed
win5017>CREATE TABLE t1
    -> (
    ->    id INT NOT NULL ,
    ->    field INT NOT NULL ,
    ->    PRIMARY KEY (id)
    -> );
Query OK, 0 rows affected (0.05 sec)

win5017>INSERT INTO t1 (id, field)
    -> VALUES
    -> (1, 5),
    -> (2, 10),
    -> (3, 15),
    -> (4, 10),
    -> (5, 10),
    -> (6, 15);
Query OK, 6 rows affected (0.03 sec)
Records: 6  Duplicates: 0  Warnings: 0

win5017>CREATE OR REPLACE VIEW v1 AS
    -> SELECT field, MAX(id)
    -> FROM t1
    -> GROUP BY field;
Query OK, 0 rows affected (0.02 sec)

win5017>
win5017>SELECT * FROM v1;
+-------+---------+
| field | MAX(id) |
+-------+---------+
|     5 |       1 |
|    10 |       5 |
|    15 |       6 |
+-------+---------+
3 rows in set (0.03 sec)

win5017>SELECT * FROM v1;
+-------+---------+
| field | MAX(id) |
+-------+---------+
|     5 |       1 |
|    10 |       5 |
|    15 |       6 |
+-------+---------+
3 rows in set (0.00 sec)

win5017>select version();
+---------------+
| version()     |
+---------------+
| 5.0.17-nt-max |
+---------------+
1 row in set (0.02 sec)

win5017>