[MDEV-26333] Resullt of SELECT from partitioned Spider table depends on the partition targeted by the previous SELECT Created: 2021-08-10  Updated: 2021-11-23  Resolved: 2021-10-18

Status: Closed
Project: MariaDB Server
Component/s: Partitioning, Storage Engine - Spider
Affects Version/s: 10.3.29, 10.3.31
Fix Version/s: 10.3.32

Type: Bug Priority: Major
Reporter: Valerii Kravchuk Assignee: Nayuta Yanagisawa (Inactive)
Resolution: Fixed Votes: 0
Labels: not-10.2, not-10.4, not-10.5, not-10.6

Issue Links:
Duplicate
duplicates MDEV-19866 With a Spider table, a SELECT with WH... Closed

 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.

 Comments   
Comment by Nayuta Yanagisawa (Inactive) [ 2021-08-10 ]

I confirmed that the bug is reproducible on 10.3 HEAD (7f26499), but not on 10.2 HEAD.

Comment by Nayuta Yanagisawa (Inactive) [ 2021-08-10 ]

select * from ts is a multi-partition search, so it should not use the group by the handler. However, after select * from ts where id = 1, the multi-partition search uses the group by handler. 

MariaDB [test]> explain select * from ts;
 
+------+-------------+-------+------+---------------+------+---------+------+------+-------+
 
| id   | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra |
 
+------+-------------+-------+------+---------------+------+---------+------+------+-------+
 
|    1 | SIMPLE      | ts    | ALL  | NULL          | NULL | NULL    | NULL |    6 |       |
 
+------+-------------+-------+------+---------------+------+---------+------+------+-------+
 
1 row in set (0.128 sec)
 
 
 
MariaDB [test]> select * from ts;
 
+----+------+
 
| id | c1   |
 
+----+------+
 
|  1 |    1 |
 
|  2 |    2 |
 
|  3 |    3 |
 
|  4 |    5 |
 
+----+------+
 
4 rows in set (0.042 sec)
 
 
 
MariaDB [test]> select * from ts where id=1;
 
+----+------+
 
| id | c1   |
 
+----+------+
 
|  1 |    1 |
 
+----+------+
 
1 row in set (0.072 sec)
 
 
 
MariaDB [test]> explain select * from ts;
 
+------+-------------+-------+------+---------------+------+---------+------+------+---------------------------------+
 
| id   | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra                           |
 
+------+-------------+-------+------+---------------+------+---------+------+------+---------------------------------+
 
|    1 | SIMPLE      | NULL  | NULL | NULL          | NULL | NULL    | NULL | NULL | Storage engine handles GROUP BY |
 
+------+-------------+-------+------+---------------+------+---------+------+------+---------------------------------+
 
1 row in set (0.032 sec)

Comment by Nayuta Yanagisawa (Inactive) [ 2021-08-10 ]

I'm a bit surprised but the bug is not reproducible in 10.4.20. 10.5.11, 10.6.3.

Comment by Nayuta Yanagisawa (Inactive) [ 2021-08-10 ]

I did GDB debugging on spider_create_group_by_handler() (10.3 HEAD). The problem is that the second SELECT without WHERE clause does not return NULL here. https://github.com/MariaDB/server/blob/mariadb-10.3.31/storage/spider/spd_group_by_handler.cc#L1662-L1672

Comment by Nayuta Yanagisawa (Inactive) [ 2021-08-10 ]

The above is because partition->get_part_spec() returns {start_part = 0, end_part = 0} for the second SELECT without WHERE clause. The member function is just to returns &m_part_spec. So, the content of m_part_spec seems to wrong.

Comment by Nayuta Yanagisawa (Inactive) [ 2021-08-10 ]

I am convinced that the reason why the bug is not reproducible on 10.4 or later is the following commit is merged in 10.4 or later: https://github.com/MariaDB/server/commit/9d6b601e797dd8333340dadaefae09ebafc787db

Comment by Nayuta Yanagisawa (Inactive) [ 2021-08-10 ]

I cherry-picked the fix for MDEV-19866 and did a follow-up fix. Please see the most recent two commits in https://github.com/MariaDB/server/tree/bb-10.3-mdev-26333

Comment by Sergei Golubchik [ 2021-10-16 ]

ok to push

Generated at Thu Feb 08 09:44:30 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.