Bug #1250 Server Crash on nested statement
Submitted: 11 Sep 2003 9:11 Modified: 16 Jun 2004 12:54
Reporter: [ name withheld ] Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S1 (Critical)
Version:4.1.0 alpha OS:Windows (Windows 2000)
Assigned to: CPU Architecture:Any

[11 Sep 2003 9:11] [ name withheld ] 
Description:
Server crashes (!!!) while executing the statement:

UPDATE at_person, at_personpalm SET KategorieNr=(select at_kategorie.id from at_kategorie where palmid=1), at_person.Name='bbbbb', Vorname='bbbbb', Addresse='bbbbb' WHERE at_person.Id=33 AND at_person.Id = at_personpalm.personnr

whereas:

UPDATE at_person SET KategorieNr=(select at_kategorie.id from at_kategorie where palmid=1), at_person.Name='bbbbb', Vorname='bbbbb', Addresse='bbbbb'
				WHERE at_person.Id=33 

and: 

UPDATE at_person, at_personpalm SET KategorieNr=3, at_person.Name='bbbbb', Vorname='bbbbb', Addresse='bbbbb' WHERE at_person.Id=33 AND at_person.Id = at_personpalm.personnr  = at_personpalm.personnr

work fine.

How to repeat:
see Description.

used Tables are:
CREATE TABLE `AT_Kategorie` (
	`Id` int(11)  NOT NULL  AUTO_INCREMENT,
	`PalmId` int(11) NULL ,
	`Idx` int(11) NULL ,
	`Name` varchar (255) NULL, 
	`FileName` varchar (255) NULL, 
	`dirty` bool NULL ,
             PRIMARY KEY (  `Id` ),
             INDEX( `PalmId` )
) Type=InnoDB;

INSERT INTO `AT_Kategorie` (`PalmId`  , `Name` ) values (0,'Unfiled');

CREATE TABLE `AT_Person` (
	`Id` int(11)  NOT NULL  AUTO_INCREMENT,
	`KategorieNr` int(11) NULL ,
	`Name` varchar (255) NOT NULL ,
	`Vorname` varchar (255) NULL ,
	`Addresse` varchar (255) NULL ,
	`Land` varchar (64) NULL ,
	`Bundesland` varchar (255) NULL ,
	`PLZ` varchar (30) NULL ,
	`Stadt` varchar (255) NULL ,
	`TelefonNr1` varchar (64) NULL ,
	`TelefonTyp1` int(11) NULL ,
	`TelefonNr2` varchar (64) NULL ,
	`TelefonTyp2` int(11) NULL ,
	`TelefonNr3` varchar (64) NULL ,
	`TelefonTyp3` int(11) NULL ,
	`TelefonNr4` varchar (64) NULL ,
	`TelefonTyp4` int(11) NULL ,
	`TelefonNr5` varchar (64) NULL ,
	`TelefonTyp5` int(11) NULL ,
	`TelefonDisplay` int(11) NULL ,
	`Custom1` varchar (255) NULL ,
	`Custom2` varchar (255) NULL ,
	`Custom3` varchar (255) NULL ,
	`Custom4` varchar (255) NULL ,
	`Notiz` BLOB NULL , 
             PRIMARY KEY (  `Id` ),
             INDEX ( `KategorieNr`)
) Type=innoDB;

ALTER TABLE  `AT_Person` 
	ADD CONSTRAINT `FK_AT_Person_AT_Kategorie` FOREIGN KEY 
	(
		`KategorieNr`
	) REFERENCES  `AT_Kategorie` (
		`Id`
	);

CREATE TABLE  `AT_PersonPalm` (
	`PersonNr` int(11) NOT NULL ,
	`PalmRecId` int(11) NOT NULL ,
	`Changed` bool NULL ,
             `Deleted`  bool NULL,
             PRIMARY KEY (  `PalmRecId` ),
             UNIQUE INDEX (`PersonNr` )
) Type=innoDB;

ALTER TABLE  `AT_PersonPalm`  ADD 
	CONSTRAINT `FK_AT_PersonPalm_AT_Person` FOREIGN KEY 
	(
		`PersonNr`
	) REFERENCES  `AT_Person` (
		`Id`
	);
[11 Sep 2003 13:24] MySQL Verification Team 
I tested your query with a server built from a BK 4.1 tree 2 days older:

mysql> UPDATE at_person, at_personpalm SET KategorieNr=(select at_kategorie.id f
rom
    -> at_kategorie where palmid=1), at_person.Name='bbbbb', Vorname='bbbbb',
    -> Addresse='bbbbb' WHERE at_person.Id=33 AND at_person.Id =
    -> at_personpalm.personnr;
Query OK, 1 row affected (0.04 sec)
Rows matched: 2  Changed: 1  Warnings: 0

Then this issue was already fixed for the next release.
[16 Jun 2004 12:52] Oleksandr Byelkin 
global ORDER BY do not belong to any table, it belong to result of union, 
result have only one field with such name, i.e. field is fully  qualified
[16 Jun 2004 12:54] Oleksandr Byelkin 
sorry, it was comment for other bug record