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

SELECT MIN on Spider table returns more rows than expected

    XMLWordPrintable

Details

    Description

      How to reproduce:

      SET @@session.spider_same_server_link = ON;
       
       
      GRANT ALL PRIVILEGES ON *.* TO 'spinne'@'127.0.0.1'  IDENTIFIED BY 'Widow2021!';
       
      DROP SERVER data1;
       
      CREATE SERVER IF NOT EXISTS data1
       
      FOREIGN DATA WRAPPER mysql
      OPTIONS(
      HOST '127.0.0.1',
      DATABASE 'test',
      USER 'spinne',
      PORT 3307,
      PASSWORD 'Widow2021!'
      );
       
      DROP DATABASE IF EXISTS auto_test_local;
      DROP DATABASE IF EXISTS auto_test_remote;
       
      CREATE DATABASE auto_test_local;
      USE auto_test_local;
      CREATE TABLE IF NOT EXISTS `sp` (
        `c1` varchar(10) NOT NULL,
        `c2` varchar(17) NOT NULL,
        `c3` datetime NOT NULL DEFAULT '0001-01-01 00:00:00',
        PRIMARY KEY (`c1`,`c2`,`c3`)
      ) ENGINE=SPIDER DEFAULT CHARSET=utf8 COMMENT='wrapper "mariadb", table "sp"'
      PARTITION BY LIST COLUMNS(`c2`)
      (PARTITION `pt1` DEFAULT COMMENT = 'srv "data1"' ENGINE = SPIDER);
       
      CREATE DATABASE auto_test_remote;
      USE auto_test_remote;
      CREATE TABLE IF NOT EXISTS `sp` (
        `c1` varchar(10) NOT NULL,
        `c2` varchar(17) NOT NULL,
        `c3` datetime NOT NULL DEFAULT '0001-01-01 00:00:00',
        PRIMARY KEY (`c1`,`c2`,`c3`)
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8
       PARTITION BY RANGE  COLUMNS(`c3`)
      (PARTITION `pmax` VALUES LESS THAN (MAXVALUE) ENGINE = InnoDB);
       
      INSERT INTO auto_test_remote.sp VALUES
      ('00166','1','2020-05-05 00:00:00'),
      ('00166','2','2020-05-03 00:00:00'),
      ('00166','3','2020-05-02 00:00:00'),
      ('00166','4','2020-05-01 00:00:00'),
      ('00166','5','2020-05-06 00:00:00'),
      ('00174','6','2020-05-06 00:00:00'),
      ('00174','7','2020-05-06 00:00:00'),
      ('00174','8','2020-05-04 00:00:00');
       
      SELECT MIN(c2),c1 FROM auto_test_local.sp WHERE c1='00166';
      

      Result of c2 is not correct, resultset have 5 times the same value for c2.

      MariaDB > SELECT MIN(c2),c1 FROM auto_test_local.sp WHERE c1='00166';
      +---------+-------+
      | MIN(c2) | c1    |
      +---------+-------+
      | 1       | 00166 |
      | 1       | 00166 |
      | 1       | 00166 |
      | 1       | 00166 |
      | 1       | 00166 |
      +---------+-------+
      5 rows in set (0.042 sec)
      

      Attachments

        Issue Links

          Activity

            People

              ycp Yuchen Pei
              Richard Richard Stracke
              Votes:
              0 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.