Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
10.3.29, 10.3.31
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
- duplicates
-
MDEV-19866 With a Spider table, a SELECT with WHERE involving primary key breaks following SELECTs
- Closed