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
-
-
- Closed
-
Activity
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) |
I'm a bit surprised but the bug is not reproducible in 10.4.20. 10.5.11, 10.6.3.
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
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.
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
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
I confirmed that the bug is reproducible on 10.3 HEAD (7f26499), but not on 10.2 HEAD.