MySQL Bugs: #12338: correlated SQL crashes the database

Bug #12338	correlated SQL crashes the database
Submitted:	3 Aug 2005 7:45	Modified:	3 Sep 2005 10:35
Reporter:	Brenden Anstey	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	4.1.12	OS:	Windows (XP)
Assigned to:		CPU Architecture:	Any

Description:
this query crashes MySQL 4.1.12-nt: 

update sheet_lines l 
set l.task_name = 
(select t.name 
from tasks t 
where t.project_code = l.project_code 
and t.task_code = l.task_code) 
where l.task_name is null 

whereas this one doesn't: 
update sheet_lines l 
set l.project_name = 
(select p.project_name 
from projects p 
where p.project_code = l.project_code) 
where l.project_name is null 

there is nothing in the mysql server log. I have to restart the database after this happens and reconnect using Query Browser. The syntax appears to be valid. I couln't find such a problem in the bug list or forums.

How to repeat:
table: task
project code (varchar)
task code (varchar)
name (varchar)(this is the task name)

table2: sheet_lines
project_code 
task_code
task_name

populate both tables with values
run the following:
update sheet_lines l 
set l.task_name = 
(select t.name 
from tasks t 
where t.project_code = l.project_code 
and t.task_code = l.task_code) 
where l.task_name is null 

Suggested fix:
unknown

I tried to repeat your test case:

mysql> use test
Database changed
mysql> select version();
+------------+
| version()  |
+------------+
| 4.1.12a-nt |
+------------+
1 row in set (0.03 sec)

mysql> create table task (project_code varchar(10), task_code varchar(10), name varchar(20));
Query OK, 0 rows affected (0.79 sec)

mysql> create table sheet_lines (project_code varchar(10), task_code varchar(10), task_name varchar(20));
Query OK, 0 rows affected (0.13 sec)

Then I inserted several rows to get the following:

mysql> select * from task;
+--------------+-----------+--------+
| project_code | task_code | name   |
+--------------+-----------+--------+
| 1            | 1         | task 1 |
| 1            | 2         | task 2 |
| 1            | 3         | task 3 |
+--------------+-----------+--------+
3 rows in set (0.06 sec)

mysql> select * from sheet_lines;
+--------------+-----------+-----------+
| project_code | task_code | task_name |
+--------------+-----------+-----------+
| 1            | 1         | task 1    |
| 1            | 2         | task 2    |
| 1            | 3         | NULL      |
+--------------+-----------+-----------+
3 rows in set (0.00 sec)

mysql> select * from sheet_lines where task_name is null;
+--------------+-----------+-----------+
| project_code | task_code | task_name |
+--------------+-----------+-----------+
| 1            | 3         | NULL      |
+--------------+-----------+-----------+
1 row in set (0.00 sec)

mysql> update sheet_lines l
    -> set l.task_name =
    -> (select t.name
    -> from task t
    -> where t.project_code = l.project_code
    -> and t.task_code = l.task_code)
    -> where l.task_name is null;
Query OK, 1 row affected (0.07 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from sheet_lines;
+--------------+-----------+-----------+
| project_code | task_code | task_name |
+--------------+-----------+-----------+
| 1            | 1         | task 1    |
| 1            | 2         | task 2    |
| 1            | 3         | task 3    |
+--------------+-----------+-----------+
3 rows in set (0.00 sec)

So, it works for me as expected. 

Please, provide some additional information: what type of the tables (InnoDB, MyISAM)? Are there any indexes, etc.

It there was a server crash, there should be something written into the <hostname>.err file located in your data directory. Please, check it, and post the appropriate part here.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".