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

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

    XMLWordPrintable

    Details

      Description

      Through the investigation for MDEV-25985, I found another bug of the Spider's query rewiring process.

      How to reproduce:

      source ../storage/spider/scripts/install_spider.sql;
      GRANT ALL PRIVILEGES ON *.* TO 'spinne'@'127.0.0.1'  IDENTIFIED BY 'password';
       
      CREATE SERVER IF NOT EXISTS data1
      FOREIGN DATA WRAPPER mysql
      OPTIONS(
      HOST '127.0.0.1',
      DATABASE 'test',
      USER 'spinne',
      PORT 16000,
      PASSWORD 'password'
      );
       
      DROP TABLE IF EXISTS `test`.`td`;
       
      CREATE TABLE `test`.`td` (
      `a` int,
      `b` int,
      `c` int,
      PRIMARY KEY (`a`),
      KEY (`b`,`c`)
      ) ENGINE=InnoDB ;
       
      DROP TABLE IF EXISTS `test`.`ts`;
       
      CREATE TABLE `test`.`ts` (
      `a` int,
      `b` int,
      `c` int,
      PRIMARY KEY (`a`),
      KEY (`b`,`c`)
      ) ENGINE=SPIDER COMMENT='table "td"'
      PARTITION BY LIST COLUMNS(`a`)
      (PARTITION `ptdef` DEFAULT COMMENT = 'srv "data1"' ENGINE = SPIDER);
       
      INSERT INTO test.td VALUES (1,1,1), (2,2,1);
      

      MariaDB [(none)]> SELECT * FROM test.td B WHERE B.c > 0 AND B.b >= 1;
      +---+------+------+
      | a | b    | c    |
      +---+------+------+
      | 1 |    1 |    1 |
      | 2 |    2 |    1 |
      +---+------+------+
      2 rows in set (0.00 sec)
       
      MariaDB [(none)]>  SELECT * FROM test.ts B WHERE B.c > 0 AND B.b >= 1;
      +---+------+------+
      | a | b    | c    |
      +---+------+------+
      | 1 |    1 |    1 |
      +---+------+------+
      1 row in set (0.05 sec)
      

      The query, executed on the data node, corresponding to one executed on the Spider node is the following:

       select `a`,`b`,`c` from `test`.`td` where `b` = 1 and `c` > 0 and ((`c` > 0) and (`b` >= 1))
      

        Attachments

          Activity

            People

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

              Dates

              Created:
              Updated:

                Git Integration

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