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

Multi-range Read fails on Spider

    XMLWordPrintable

    Details

      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).

        Attachments

          Activity

            People

            Assignee:
            nayuta-yanagisawa Nayuta Yanagisawa
            Reporter:
            yongxinxu Yongxin Xu
            Votes:
            0 Vote for this issue
            Watchers:
            3 Start watching this issue

              Dates

              Created:
              Updated:

                Git Integration