Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
5.5(EOL), 10.0(EOL), 10.1(EOL)
-
None
-
One master / one slave configuration with parallel replication.
-
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.