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

Resullt of SELECT from partitioned Spider table depends on the partition targeted by the previous SELECT

    XMLWordPrintable

    Details

      Description

      Consider the following primitive test case executed on an instance with Spider engine loaded and spider_same_server_link=1, and databases test, db1, db2 and db3 created:

      MariaDB [test]> create server srv1 foreign data wrapper mysql
          -> options (host '127.0.0.1', database 'db1', user  'root', port 3306);
      Query OK, 0 rows affected (0.000 sec)
       
      MariaDB [test]> create server srv2 foreign data wrapper mysql options (host '127.0.0.1', database 'db2', user  'root', port 3306);
      Query OK, 0 rows affected (0.000 sec)
       
      MariaDB [test]> create server srv3 foreign data wrapper mysql options (host '127.0.0.1', database 'db3', user  'root', port 3306);
      Query OK, 0 rows affected (0.000 sec)
       
      MariaDB [test]> create table db1.ts(id int primary key, c1 int) engine=innodb;
      Query OK, 0 rows affected (0.183 sec)
       
      MariaDB [test]> create table db2.ts(id int primary key, c1 int) engine=innodb;
      Query OK, 0 rows affected (0.185 sec)
       
      MariaDB [test]> create table db3.ts(id int primary key, c1 int) engine=innodb;
      Query OK, 0 rows affected (0.194 sec)
       
      MariaDB [test]> insert into db1.ts values (1,1);
      Query OK, 1 row affected (0.038 sec)
       
      MariaDB [test]> insert into db2.ts values (2,2);
      Query OK, 1 row affected (0.032 sec)
       
      MariaDB [test]> insert into db3.ts values (3,3), (4,5);
      Query OK, 2 rows affected (0.033 sec)
      Records: 2  Duplicates: 0  Warnings: 0
       
      MariaDB [test]> create table ts(id int primary key, c1 int) engine=spider partition by list columns(id) (partition p1 values in (1) comment='srv "srv1", table "ts"' engine=spider,  partition p2 values in (2) comment='srv "srv2", table "ts"' engine=spider,  partition prest default comment='srv "srv3", table "ts"' engine=spider);
      Query OK, 0 rows affected (0.033 sec)
       
      MariaDB [test]> select * from ts;
      +----+------+
      | id | c1   |
      +----+------+
      |  1 |    1 |
      |  2 |    2 |
      |  3 |    3 |
      |  4 |    5 |
      +----+------+
      4 rows in set (0.007 sec)
       
      MariaDB [test]> select * from ts where id=1;
      +----+------+
      | id | c1   |
      +----+------+
      |  1 |    1 |
      +----+------+
      1 row in set (0.002 sec)
       
      MariaDB [test]> select * from ts;
      +----+------+
      | id | c1   |
      +----+------+
      |  1 |    1 |
      +----+------+
      1 row in set (0.002 sec)
       
      MariaDB [test]> select version();
      +-----------------+
      | version()       |
      +-----------------+
      | 10.3.31-MariaDB |
      +-----------------+
      1 row in set (0.000 sec)
       
      MariaDB [test]> select * from ts where id=2;
      +----+------+
      | id | c1   |
      +----+------+
      |  2 |    2 |
      +----+------+
      1 row in set (0.002 sec)
       
      MariaDB [test]> select * from ts;
      +----+------+
      | id | c1   |
      +----+------+
      |  2 |    2 |
      +----+------+
      1 row in set (0.002 sec)
      

      As you can see, the result of:

      select * from ts;

      depends on what specific partition was targeted by the previous SELECT. Something is stored somewhere and not cleaned up properly.

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              nayuta-yanagisawa Nayuta Yanagisawa
              Reporter:
              valerii Valerii Kravchuk
              Votes:
              0 Vote for this issue
              Watchers:
              4 Start watching this issue

                Dates

                Created:
                Updated:
                Resolved:

                  Git Integration