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

Spider handle ">=" as ">" in some cases

    XMLWordPrintable

    Details

      Description

      Testcase 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 TABLE IF EXISTS `test`.`spider_data_tab2`;
      CREATE TABLE `test`.`spider_data_tab2` (
      `col0` varchar(10) NOT NULL,
      `col1` varchar(10) NOT NULL,
      `col2` varchar(10) NOT NULL,
      `col3` datetime NOT NULL DEFAULT '0001-01-01 00:00:00',
      `col4` int,
      PRIMARY KEY (`col1`,`col2`,`col3`),
      KEY `IX_1` (`col3`,`col4`)
      ) 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);
      DROP TABLE IF EXISTS `test`.`spider_tab2`;
      CREATE TABLE IF NOT EXISTS `test`.`spider_tab2` (
      `col0` varchar(10) NOT NULL,
      `col1` varchar(10) NOT NULL,
      `col2` varchar(17) NOT NULL,
      `col3` datetime NOT NULL DEFAULT '0001-01-01 00:00:00',
      `col4` int,
      PRIMARY KEY (`col0`,`col1`,`col2`,`col3`)
      ,KEY `IX_1` (`col3`,`col4`)
      ) ENGINE=SPIDER DEFAULT CHARSET=utf8 COMMENT='wrapper "mariadb", table "spider_data_tab2"'
      PARTITION BY LIST COLUMNS(`col1`)
      (PARTITION `ptdef` DEFAULT COMMENT = 'srv "data1"' ENGINE = SPIDER);
      DROP TABLE IF EXISTS `test`.`local_tab`;
      CREATE TABLE IF NOT EXISTS `test`.`local_tab` (
      `col1` varchar(10) NOT NULL,
      `col2` varchar(17) NOT NULL,
      `col3` varchar(100) NOT NULL,
      PRIMARY KEY (`col1`,`col2`,`col3`)
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8;
      INSERT INTO test.spider_data_tab2 VALUES
      ("8522d5bd69","00174","037ec5b849","2021-03-31 00:00:00",3),
      ("8522d5bd70","00174","04b11de592","2021-04-01 00:00:00",3),
      ("8522d5bd71","00174","0f29529250","2021-04-02 00:00:00",1),
      ("8522d5bd72","00174","1bb3fecc59","2021-04-02 00:00:00",3),
      ("8522d5bd70","00174","293f5f916a","2021-04-03 00:00:00",3),
      ("8522d5bd70","00174","3ddba35681","2021-04-04 00:00:00",3),
      ("8522d5bd70","00166","024009786c","2021-03-31 00:00:00",3),
      ("8522d5bd69","00166","037ec5b849","2021-04-01 00:00:00",3),
      ("8522d5bd73","00166","04b11de592","2021-04-02 00:00:00",1),
      ("8522d5bd72","00166","04f62d1dbf","2021-04-02 00:00:00",3),
      ("8522d5bd71","00166","0ba4eacc85","2021-04-03 00:00:00",3),
      ("8522d5bd66","00166","1104ef747c","2021-04-04 00:00:00",3);
      INSERT INTO test.local_tab VALUES
      ("00166", "024009786c", "d1cde678-cd97-11eb-9ff8-020017000a63"),
      ("00166", "037ec5b849", "d60e4c51-cd97-11eb-9ff8-020017000a63"),
      ("00166", "04b11de592", "d007ede3-cd97-11eb-9ff8-020017000a63"),
      ("00166", "04f62d1dbf", "d0fd469c-cd97-11eb-9ff8-020017000a63"),
      ("00166", "0ba4eacc85", "cfaf324d-cd97-11eb-9ff8-020017000a63"),
      ("00166", "1104ef747c", "cceafd90-cd97-11eb-9ff8-020017000a63"),
      ("00174", "037ec5b849", "d60e6dd5-cd97-11eb-9ff8-020017000a63"),
      ("00174", "04b11de592", "d007f3f3-cd97-11eb-9ff8-020017000a63"),
      ("00174", "0f29529250", "d0fd4d7e-cd97-11eb-9ff8-020017000a63"),
      ("00174", "1bb3fecc59", "cceb03e5-cd97-11eb-9ff8-020017000a63"),
      ("00174", "293f5f916a", "d419c420-cd97-11eb-9ff8-020017000a63");
       
      SELECT DISTINCT A.col1, A.col2, B.col3 FROM test.local_tab A INNER JOIN test.spider_data_tab2 B ON A.col1 = B.col1 AND A.col2 = B.col2 AND A.col1 IN ('00166' , '00174') AND B.col4 <> 1 AND B.col3 >= '2021-04-01 00:00:00' AND B.col3 <= '2021-04-03 00:00:00';
      SELECT DISTINCT A.col1, A.col2, B.col3 FROM test.local_tab A INNER JOIN test.spider_tab2 B ON A.col1 = B.col1 AND A.col2 = B.col2 AND A.col1 IN ('00166' , '00174') AND B.col4 <> 1 AND B.col3 >= '2021-04-01 00:00:00' AND B.col3 <= '2021-04-03 00:00:00'; 
      
      

      Happens on Spider 3.3.15 and 3.4

        Attachments

          Activity

            People

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

              Dates

              Created:
              Updated:
              Resolved:

                Git Integration