Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
10.3.9, 10.3(EOL)
-
None
-
Crash is reproducible on server versions 10.3.5 to 10.3.9 inclusive.
Description
Run these statements and most of the time the server crashes on the SELECT statement:
SET optimizer_switch='split_materialized=on'; |
|
|
CREATE DATABASE IF NOT EXISTS `testdb` DEFAULT CHARACTER SET utf8; |
USE testdb; |
|
|
DROP TABLE IF EXISTS `t`; |
CREATE TABLE `t` ( |
`tId` bigint(20) NOT NULL, |
`tgId` bigint(20) DEFAULT NULL, |
`sgId` bigint(20) DEFAULT NULL, |
PRIMARY KEY (`tId`), |
KEY `fk_t_tgId` (`tgId`), |
KEY `sgId` (`sgId`) |
) ENGINE=InnoDB DEFAULT CHARSET=utf8; |
INSERT INTO t (tId, tgId, sgId) |
VALUES ('1', NULL, '1'); |
|
|
DROP TABLE IF EXISTS `sgh`; |
CREATE TABLE `sgh` ( |
`sgId` bigint(20) NOT NULL, |
`psd` timestamp(3) NOT NULL DEFAULT '0000-00-00 00:00:00.000', |
PRIMARY KEY (`sgId`,`psd`) |
) ENGINE=InnoDB DEFAULT CHARSET=utf8; |
INSERT INTO sgh (sgId, psd) VALUES |
('1', '2018-08-16 07:30:00.000'); |
|
|
DROP TABLE IF EXISTS `l`; |
CREATE TABLE `l` ( |
`lId` bigint(20) NOT NULL AUTO_INCREMENT, |
`a` bigint(20) NOT NULL, |
PRIMARY KEY (`lId`) |
) ENGINE=InnoDB DEFAULT CHARSET=utf8; |
INSERT INTO l (a) VALUES (1); |
|
|
SELECT lId, sdate |
FROM
|
(
|
SELECT l.lId, sgh.psd AS sdate, t.sgId |
FROM l |
INNER JOIN t ON (l.a=t.tId) |
INNER JOIN sgh ON sgh.sgId=t.sgId |
GROUP BY l.lId, sdate, t.sgId |
) us
|
INNER JOIN sgh ON sgh.sgId=us.sgId AND sgh.psd=sdate |
GROUP BY lId, sdate; |
If you change the optization switch to this:
SET optimizer_switch='split_materialized=off'; |
You get the single (expected) row returned.
The split_materialized switch was added 10.3.4 and the code works in that version regardless of its value, a change in 10.3.5 seems to have broken it.
Note the crash occurs on Windows and Ubuntu host machines. I have attached the crash output from my syslog.
Regards,
Mark.