Details

    • Type: Bug
    • Status: Closed (View Workflow)
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: 10.3, 10.3.9
    • Fix Version/s: 10.3.10
    • Component/s: Optimizer
    • Labels:
      None
    • Environment:
      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.

        Attachments

          Activity

            People

            • Assignee:
              igor Igor Babaev
              Reporter:
              MrMark Mark Anstice
            • Votes:
              1 Vote for this issue
              Watchers:
              3 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: