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

Update with subquery from the same table while derived_merge=on produces wrong result

    XMLWordPrintable

Details

    • 10.1.23

    Description

      After switching to MariaDB from MySQL, we noticed an update having different result.
      Possibly related to MDEV-6163, in which I got the idea to turn off derived_merge as a workaround.

      Test case:

      CREATE TABLE `tab` (
        `start` date DEFAULT NULL,
        `end` date DEFAULT NULL
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_czech_ci;
       
      INSERT INTO `tab` (`start`, `end`) VALUES
      ('2014-08-13', NULL),
      ('2014-09-09', NULL);

      Now execute the update:

      set optimizer_switch='derived_merge=on';
      UPDATE tab SET end=(SELECT x.start FROM (SELECT * FROM tab) AS x WHERE x.start > tab.start ORDER BY x.start ASC LIMIT 1);

      Resulting table:

      +------------+------+
      | start      | end  |
      +------------+------+
      | 2014-08-13 | NULL |
      | 2014-09-09 | NULL |
      +------------+------+

      However, with derived_merge=off:

      set optimizer_switch='derived_merge=off';
      UPDATE tab SET end=(SELECT x.start FROM (SELECT * FROM tab) AS x WHERE x.start > tab.start ORDER BY x.start ASC LIMIT 1);

      Resulting table:

      +------------+------------+
      | start      | end        |
      +------------+------------+
      | 2014-08-13 | 2014-09-09 |
      | 2014-09-09 | NULL       |
      +------------+------------+

      This is also how it was before migrating from MySQL.

      Attachments

        Activity

          People

            sanja Oleksandr Byelkin
            Ender Ender Wiggin
            Votes:
            0 Vote for this issue
            Watchers:
            4 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.