I encountered a MySQL (5.7.14) crash when executing a request with MyDAC (8.7.27).
Here is the tables' structure :
Code: Select all
CREATE TABLE `table1` (
`RECNO` int(10) unsigned NOT NULL AUTO_INCREMENT,
`KEY` varchar(10) NOT NULL DEFAULT '',
PRIMARY KEY (`RECNO`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
CREATE TABLE `table2` (
`RECNO` int(10) unsigned NOT NULL AUTO_INCREMENT,
`KEY` varchar(10) NOT NULL DEFAULT '',
`ID` varchar(23) DEFAULT NULL,
PRIMARY KEY (`RECNO`),
UNIQUE KEY `KEY` (`KEY`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
CREATE TABLE `table3` (
`RECNO` int(10) unsigned NOT NULL AUTO_INCREMENT,
`KEY` varchar(10) NOT NULL DEFAULT '',
`ID` varchar(23) DEFAULT NULL,
PRIMARY KEY (`RECNO`),
UNIQUE KEY `KEY` (`KEY`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
Now, if you execute these statements with MySQL Workbench, the request is executed with no problem.
Code: Select all
SET @REQ = 'UPDATE table1 t1 INNER JOIN (
SELECT
t2_KEY, t3_KEY
FROM (
SELECT t2.KEY AS t2_KEY, t2.ID, t3.KEY AS t3_KEY FROM
(SELECT `KEY`, REPLACE(ID, " ", "") AS ID FROM table2) t2 INNER JOIN
(SELECT `KEY`, REPLACE(ID, " ", "") AS ID FROM table3) t3 ON (t2.ID = t3.ID)
WHERE
(t2.ID <> " ")
) r
GROUP BY t2_KEY
) mc ON (t1.KEY = mc.t2_KEY)
SET
t1.KEY = mc.t3_KEY';
PREPARE Req FROM @REQ;
EXECUTE Req;
DEALLOCATE PREPARE Req;
Code: Select all
procedure TForm1.Button1Click(Sender: TObject);
begin
Query.SQL.Text := 'SET @REQ = ''UPDATE table1 t1 INNER JOIN ('#13#10 +
' SELECT'#13#10 +
' t2_KEY, t3_KEY'#13#10 +
' FROM ('#13#10 +
' SELECT t2.KEY AS t2_KEY, t2.ID, t3.KEY AS t3_KEY FROM'#13#10 +
' (SELECT `KEY`, REPLACE(ID, " ", "") AS ID FROM table2) t2 INNER JOIN'#13#10 +
' (SELECT `KEY`, REPLACE(ID, " ", "") AS ID FROM table3) t3 ON (t2.ID = t3.ID)'#13#10 +
' ) r'#13#10 +
' GROUP BY t2_KEY'#13#10 +
' ) k ON (t1.KEY = k.t2_KEY)'#13#10 +
'SET'#13#10 +
' t1.KEY = k.t3_KEY'';'#13#10+
'PREPARE Req FROM @REQ;'#13#10 +
'EXECUTE Req;'#13#10 +
'DEALLOCATE PREPARE Req;';
Query.ExecSQL;
end;
Regards.