MySQL Bugs: #12067: update with subselect causes crash

Bug #12067	update with subselect causes crash
Submitted:	20 Jul 2005 18:40	Modified:	20 Aug 2005 21:31
Reporter:	Alvin Wang	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server	Severity:	S1 (Critical)
Version:	4/5	OS:	Windows (windows xp)
Assigned to:		CPU Architecture:	Any

Description:
update table1 set datafield1 = ( select datafield1 from table2 where
table2.indexfield = table1.indexfield);

I realize now that this is not valid mysql sql.  However, I really think that it should give an error instead of crashing. 

How to repeat:
run

update table1 set datafield1 = ( select datafield1 from table2 where
table2.indexfield = table1.indexfield);

Suggested fix:
give an error

Could you please provide a test case with table definition and data
and the exactly server version you got the crash.

Thanks in advance.

It happens on the latest 5 and latest 4 versions.  

CREATE TABLE `ab` (
  `FactID` bigint(20) NOT NULL default '0',
  `ModeID` int(5) default NULL,
  `Mode_Display` varchar(100) default NULL,
  PRIMARY KEY  (`FactID`)
) TYPE=MyISAM;

CREATE TABLE `b` (
  `modeid` int(11) NOT NULL auto_increment,
  `mode_display` varchar(100) default NULL,
  `ttl` int(11) default NULL,
  PRIMARY KEY  (`modeid`),
  KEY `mode_display` (`mode_display`)
) TYPE=MyISAM;

update ab
set modeid=(select modeid from b 
where ab.mode_display=b.mode_display);

mysql 5.0.9

I am pretty sure it happens on 4.1.13 too but I don't have time to retest

I was unable for to repeat the issue reported with server 5.0.9.
Maybe if you provide a dump of the tables when the crash occurs
I will able to repeat. Thanks

c:\mysql\bin>mysql -uroot test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2 to server version: 5.0.9-beta-nt

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

mysql> CREATE TABLE `ab` (
    ->   `FactID` bigint(20) NOT NULL default '0',
    ->   `ModeID` int(5) default NULL,
    ->   `Mode_Display` varchar(100) default NULL,
    ->   PRIMARY KEY  (`FactID`)
    -> ) TYPE=MyISAM;
Query OK, 0 rows affected, 1 warning (0.06 sec)

mysql>
mysql> CREATE TABLE `b` (
    ->   `modeid` int(11) NOT NULL auto_increment,
    ->   `mode_display` varchar(100) default NULL,
    ->   `ttl` int(11) default NULL,
    ->   PRIMARY KEY  (`modeid`),
    ->   KEY `mode_display` (`mode_display`)
    -> ) TYPE=MyISAM;
Query OK, 0 rows affected, 1 warning (0.05 sec)

mysql>
mysql> insert into b (mode_display) values ("colour");
Query OK, 1 row affected (0.02 sec)

mysql> insert into ab (mode_display) values ("colour");
Query OK, 1 row affected (0.00 sec)

mysql>
mysql> update ab
    -> set modeid=(select modeid from b
    -> where ab.mode_display=b.mode_display);
Query OK, 1 row affected (0.03 sec)
Rows matched: 1  Changed: 1  Warnings: 0

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".