Details
-
Bug
-
Status: Confirmed (View Workflow)
-
Major
-
Resolution: Unresolved
-
10.3.25
-
All (centOS, Windows10, MacOS etc.)
Description
How to repeat
On backends:
|
bk.sql |
CREATE TABLE `tbl` (
|
`id` int(11) NOT NULL,
|
`flag` int(11) DEFAULT NULL,
|
PRIMARY KEY (`id`)
|
)ENGINE=INNODB;
|
On Spider
|
spider.sql |
CREATE TABLE `tbl` (
|
`id` int(11) NOT NULL,
|
`flag` int(11) DEFAULT NULL,
|
PRIMARY KEY (`id`)
|
) ENGINE=SPIDER DEFAULT CHARSET=utf8
|
PARTITION BY LIST (id % 2)
|
(PARTITION `pt0` VALUES IN (0) COMMENT = 'database "test", table "tbl", server "bk0"' ENGINE = SPIDER,
|
PARTITION `pt1` VALUES IN (1) COMMENT = 'database "test", table "tbl", server "bk1"' ENGINE = SPIDER);
|
|
|
CREATE TABLE `m` (
|
`id` int(11) NOT NULL,
|
PRIMARY KEY (`id`)
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
|
|
|
INSERT INTO `tbl` VALUES (101624109, 1);
|
INSERT INTO `tbl` VALUES (91272871, 1);
|
INSERT INTO `tbl` VALUES (94933594, 1);
|
INSERT INTO `tbl` VALUES (98646655, 1);
|
INSERT INTO `tbl` VALUES (13914947, 0);
|
INSERT INTO `tbl` VALUES (45051390, 0);
|
INSERT INTO `tbl` VALUES (30864034, 1);
|
INSERT INTO `tbl` VALUES (33671239, 0);
|
INSERT INTO `tbl` VALUES (39109063, 1);
|
INSERT INTO `tbl` VALUES (91137966, 1);
|
INSERT INTO `tbl` VALUES (95897689, 0);
|
INSERT INTO `tbl` VALUES (571307512, 1);
|
INSERT INTO `tbl` VALUES (35706155, 1);
|
INSERT INTO `tbl` VALUES (34044708, 0);
|
And then, excute
|
insert_select.sql |
insert into `m`(id) select id from tbl where id not in (94933594) and flag = 1;
|
on spider, an error occurs
ERROR 1062 (23000): Duplicate entry '30864034' for key 'PRIMARY'
I traced the source code, it seems that as long as Spider uses the MRR feature, it will get twice as much data as the correct result (correct data and its replica).