Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-17211

Server crash on query

    XMLWordPrintable

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Fixed
    • 10.3.9, 10.3
    • 10.3.10
    • Optimizer
    • 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.

      Attachments

        Activity

          People

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

            Dates

              Created:
              Updated:
              Resolved:

              Git Integration

                Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.