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

        Details

        • Type: Bug
        • Status: Confirmed
        • Priority: Major
        • Resolution: Unresolved
        • Affects Version/s: 10.1, 10.0, 5.5
        • Fix Version/s: 10.1, 10.0, 5.5
        • Component/s: Data Manipulation - Update, Optimizer
        • Labels:
          None
        • Environment:
          One master / one slave configuration with parallel replication.

          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.

            Gliffy Diagrams

              Attachments

                Activity

                Hide
                Permalink
                elenst Elena Stepanova added a comment -

                Thanks for the report and the test case.

                Show
                elenst Elena Stepanova added a comment - Thanks for the report and the test case.

                  People

                  • Assignee:
                    sanja Oleksandr Byelkin
                    Reporter:
                    Ender Ender Wiggin
                  • Votes:
                    0 Vote for this issue
                    Watchers:
                    2 Start watching this issue

                    Dates

                    • Created:
                      Updated: