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

            nayuta-yanagisawa Nayuta Yanagisawa (Inactive)
            Richard Richard Stracke
            Votes:
            2 Vote for this issue
            Watchers:
            9 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved:

              Git Integration

                Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.