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

distinct not work properly in some cases for spider tables

    XMLWordPrintable

    Details

      Description

      To reproduce:

      SET @@session.spider_same_server_link = ON; 
       
      GRANT ALL PRIVILEGES ON *.* TO 'spinne'@'127.0.0.1'  IDENTIFIED BY 'Widow2021!';
       
      CREATE SERVER data1
      FOREIGN DATA WRAPPER mysql
      OPTIONS(
      HOST '127.0.0.1',
      DATABASE 'test',
      USER 'spinne',
      PASSWORD 'Widow2021!'
      ); 
       
      CREATE TABLE IF NOT EXISTS `spider_tab` (
        `col1` varchar(10) NOT NULL,
        `col2` varchar(10) NOT NULL,
        PRIMARY KEY (`col1`,`col2`)
      ) ENGINE=SPIDER DEFAULT CHARSET=utf8 COMMENT='wrapper "mariadb", table "spider_data_tab"'
      PARTITION BY LIST COLUMNS(`col2`)
      (PARTITION `pt1` DEFAULT COMMENT = 'srv "data1"' ENGINE = SPIDER);
       
      CREATE TABLE IF NOT EXISTS `spider_data_tab` (
        `col1` varchar(10) NOT NULL,
        `col2` varchar(10) NOT NULL,
        `col3` datetime NOT NULL DEFAULT '0001-01-01 00:00:00',
        PRIMARY KEY (`col1`,`col2`, `col3`)
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8
       PARTITION BY RANGE  COLUMNS(`col3`)
      (PARTITION `pmax` VALUES LESS THAN (MAXVALUE) ENGINE = InnoDB);
       
      insert into `spider_data_tab` VALUES
      ("aaaaaaaaa","00100","2020-05-05 00:00:00")
      ,("bbbbbbbbb","00999","2020-05-06 00:00:00")
      ,("bbbbbbbbb","00100","2020-05-06 00:00:00")
      ,("ccccccccc","00999","2020-05-06 00:00:00")
      ,("ddddddddd","00999","2020-05-06 00:00:00")
      ,("eeeeeeeee","00999","2020-05-06 00:00:00")
      ,("fffffffff","00999","2020-05-06 00:00:00")
      ,("ggggggggg","00999","2020-05-06 00:00:00")
      ,("hhhhhhhhh","00999","2020-05-06 00:00:00");
       
       SELECT distinct col2 FROM spider_tab WHERE col2="00999";
       
      SELECT distinct col2 FROM spider_data_tab WHERE col2="00999";
      

        Attachments

          Activity

            People

            Assignee:
            nayuta-yanagisawa Nayuta Yanagisawa
            Reporter:
            Richard Richard Stracke
            Votes:
            2 Vote for this issue
            Watchers:
            9 Start watching this issue

              Dates

              Created:
              Updated:
              Resolved:

                Git Integration