Bug #2563 subquery kills mysqld-nt

• Submitted: 29 Jan 2004 15:12
• Modified: 29 Jan 2004 19:37
• Reporter: David Yao
• Status: Closed
• Category: MySQL Server: InnoDB storage engine
• Severity: S3 (Non-critical)
• Version: 4.1.1 alpha
• OS: Windows (WinXP)
• CPU Architecture: Any

[29 Jan 2004 15:12] David Yao

Description:
when run subquery in the main select query for INNODB table, mysqld-nt is killed, you have to run net start mysql to restart mysql service

How to repeat:
use test;

create table test1 (id int not null, description varchar(20), factor1 int not null, primary key (id)) TYPE=INNODB;;

insert into test1 (id, description, factor1) values (1, 'hello', 1), (2, 'hello', 2),(3,'hello', 3),(4,'hello', 4), (5,'hello', 5), (6,'hello', 6), (7,'hello', 7);

create table test2 (id int not null, description varchar(20),  factor2 int not null, primary key (id)) TYPE=INNODB;;

insert into test2 (id, description, factor2) values (1, 'hello', 1), (2,'hello',  2),(3,'hello', 3),(4,'hello', 4), (5,'hello', 5), (6,'hello', 6), (7,'hello', 7);

select test1.id, test1.factor1 * (select factor2 from test2 where id=2) AS multiply from test1;

Suggested fix:
1. not using INNODB tables
2. using subquerys in the where clause like the one below

select test1.id, test1.factor1 * test2.factor2 AS multiply from test1, test2 where test2.id = (select id from test2 where id=2) ;

or 

select test1.id, test1.factor1 * test2.factor2 AS multiply from test1, test2 where test2.id = 2;

[29 Jan 2004 19:37] MySQL Verification Team

I was able to repeat with release 4.1.1a however testing with the
latest BK 4.1 tree I verified that this issue was already fixed.
Thank you for the bug report:

mysql> create table test1 (id int not null, description varchar(20),
    -> factor1 int not null, primary key (id)) TYPE=INNODB;
Query OK, 0 rows affected, 1 warning (0.16 sec)

mysql>
mysql> insert into test1 (id, description, factor1) values
    -> (1, 'hello', 1), (2,'hello', 2),(3,'hello', 3),
    -> (4,'hello', 4), (5,'hello', 5), (6,'hello', 6),
    -> (7,'hello', 7);
Query OK, 7 rows affected (0.01 sec)
Records: 7  Duplicates: 0  Warnings: 0

mysql>
mysql> create table test2 (id int not null, description
    -> varchar(20),  factor2 int not null,
    ->  primary key (id)) TYPE=INNODB;
Query OK, 0 rows affected, 1 warning (0.10 sec)

mysql>
mysql> insert into test2 (id, description, factor2) values
    -> (1, 'hello', 1), (2,'hello',  2),(3,'hello', 3),
    -> (4,'hello', 4), (5,'hello', 5), (6,'hello',6),
    -> (7,'hello', 7);
Query OK, 7 rows affected (0.01 sec)
Records: 7  Duplicates: 0  Warnings: 0

mysql>
mysql> select test1.id, test1.factor1 * (select factor2
    -> from test2 where id=2) AS multiply from test1;
+----+----------+
| id | multiply |
+----+----------+
|  1 |        2 |
|  2 |        4 |
|  3 |        6 |
|  4 |        8 |
|  5 |       10 |
|  6 |       12 |
|  7 |       14 |
+----+----------+
7 rows in set (0.03 sec)

mysql> select version();
+-----------------------+
| version()             |
+-----------------------+
| 4.1.2-alpha-max-debug |
+-----------------------+
1 row in set (0.00 sec)