[MDEV-19258] RIGHT JOIN hangs in MariaDB Created: 2019-04-16  Updated: 2019-05-24  Resolved: 2019-05-24

Status: Closed
Project: MariaDB Server
Component/s: Storage Engine - InnoDB
Affects Version/s: 5.5, 10.0, 10.1, 10.3.13, 10.2, 10.3, 10.4
Fix Version/s: 10.1.41

Type: Bug Priority: Major
Reporter: Rok Jaklic Assignee: Igor Babaev
Resolution: Fixed Votes: 0
Labels: None


 Description   

We are migrating (dump, restore) data from Mysql version 5.5.61-38.13-log to MariaDB version 10.3.13, however we found a problem when RIGHT JOIN statement hangs, but inner join does not.

This one works in Mysql but not in MariaDB:

SELECT t1.modifiedBy, t1.timestamp FROM table1 t1 RIGHT JOIN table2 t2 ON t2.REV=t1.id RIGHT JOIN table3 t3 ON t3.id=t2.profile_id RIGHT JOIN table4 t4 ON t4.id=t3.person_id WHERE t1.timestamp < 1555324525347 AND t1.timestamp > 1544873725347 AND t2.REVTYPE=2;

We found out that INNER JOIN, LEFT JOIN works, but RIGHT JOIN hangs.

Any ideas why?

Dump of the database: http://www.rasca.net/tmp/dump.tar.gz

 Comments   
Comment by Rok Jaklic [ 2019-04-23 ]

However we found out, if we create indexes like:

create index profile_id on table2(profile_id);
 
create index person_id on table3(person_id);

... then query does not "hang".

Comment by Alice Sherepa [ 2019-05-13 ]

Thanks a lot for the report!
I repeated on 5.5.64, 10.4.4, query hangs in "sending data" state. 

MariaDB [test]> explain SELECT t1.modifiedBy, t1.timestamp FROM table1 t1 RIGHT JOIN table2 t2 ON t2.REV=t1.id RIGHT JOIN table3 t3 ON t3.id=t2.profile_id RIGHT JOIN table4 t4 ON t4.id=t3.person_id WHERE t1.timestamp < 1555324525347 AND t1.timestamp > 1544873725347 AND t2.REVTYPE=2;
 
+------+-------------+-------+--------+---------------+---------+---------+-------------------+--------+-------------------------------------------------+
 
| id   | select_type | table | type   | possible_keys | key     | key_len | ref               | rows   | Extra                                           |
 
+------+-------------+-------+--------+---------------+---------+---------+-------------------+--------+-------------------------------------------------+
 
|    1 | SIMPLE      | t3    | ALL    | PRIMARY       | NULL    | NULL    | NULL              | 286024 | Using where                                     |
 
|    1 | SIMPLE      | t4    | ref    | PRIMARY       | PRIMARY | 4       | test.t3.person_id | 1      | Using index                                     |
 
|    1 | SIMPLE      | t2    | ALL    | NULL          | NULL    | NULL    | NULL              | 575811 | Using where; Using join buffer (flat, BNL join) |
 
|    1 | SIMPLE      | t1    | eq_ref | PRIMARY       | PRIMARY | 4       | test.t2.REV       | 1      | Using where                                     |
 
+------+-------------+-------+--------+---------------+---------+---------+-------------------+--------+-------------------------------------------------+
 
4 rows in set (0.001 sec)

Mysql 8.0.15 - returned results in 0.12sec:

mysql> explain SELECT t1.modifiedBy, t1.timestamp FROM table1 t1 RIGHT JOIN table2 t2 ON t2.REV=t1.id RIGHT JOIN table3 t3 ON t3.id=t2.profile_id RIGHT JOIN table4 t4 ON t4.id=t3.person_id WHERE t1.timestamp < 1555324525347 AND t1.timestamp > 1544873725347 AND t2.REVTYPE=2;
 
+----+-------------+-------+------------+--------+---------------+---------+---------+--------------------+--------+----------+-------------+
 
| id | select_type | table | partitions | type   | possible_keys | key     | key_len | ref                | rows   | filtered | Extra       |
 
+----+-------------+-------+------------+--------+---------------+---------+---------+--------------------+--------+----------+-------------+
 
|  1 | SIMPLE      | t2    | NULL       | ALL    | NULL          | NULL    | NULL    | NULL               | 575811 |    10.00 | Using where |
 
|  1 | SIMPLE      | t1    | NULL       | eq_ref | PRIMARY       | PRIMARY | 4       | test.t2.REV        |      1 |    11.11 | Using where |
 
|  1 | SIMPLE      | t3    | NULL       | ref    | PRIMARY       | PRIMARY | 4       | test.t2.profile_id |      1 |   100.00 | Using where |
 
|  1 | SIMPLE      | t4    | NULL       | ref    | PRIMARY       | PRIMARY | 4       | test.t3.person_id  |      1 |   100.00 | Using index |
 
+----+-------------+-------+------------+--------+---------------+---------+---------+--------------------+--------+----------+-------------+
 
4 rows in set, 1 warning (0.00 sec)
 
 
 
Note (Code 1003): /* select#1 */ select `test`.`t1`.`modifiedBy` AS `modifiedBy`,`test`.`t1`.`timestamp` AS `timestamp` from `test`.`table4` `t4` join `test`.`table3` `t3` join `test`.`table2` `t2` join `test`.`table1` `t1` where ((`test`.`t4`.`id` = `test`.`t3`.`person_id`) and (`test`.`t3`.`id` = `test`.`t2`.`profile_id`) and (`test`.`t1`.`id` = `test`.`t2`.`REV`) and (`test`.`t2`.`REVTYPE` = 2) and (`test`.`t1`.`timestamp` < 1555324525347) and (`test`.`t1`.`timestamp` > 1544873725347))
 
mysql>  SELECT t1.modifiedBy, t1.timestamp FROM table1 t1 RIGHT JOIN table2 t2 ON t2.REV=t1.id RIGHT JOIN table3 t3 ON t3.id=t2.profile_id RIGHT JOIN table4 t4 ON t4.id=t3.person_id WHERE t1.timestamp < 1555324525347 AND t1.timestamp > 1544873725347 AND t2.REVTYPE=2;
 
+--------------+---------------+
 
| modifiedBy   | timestamp     |
 
+--------------+---------------+
 
| $2nJP)^Y1Owq | 1545177601090 |
 
| $2nJP)^Y1Owq | 1545177601090 |
 
| Q^HEb@nGq201 | 1550102400207 |
 
| Q^HEb@nGq201 | 1550102400207 |
 
| Q^HEb@nGq201 | 1550102400207 |
 
| Q^HEb@nGq201 | 1550102400207 |
 
| dv%3buXDsFjC | 1545004801365 |
 
| dv%3buXDsFjC | 1545004801365 |
 
| A?kPlT6ZxD.v | 1551139200175 |
 
| A?kPlT6ZxD.v | 1551139200175 |
 
| (*l!Y@H0iwJ# | 1551312000154 |
 
| (*l!Y@H0iwJ# | 1551312000154 |
 
| (*l!Y@H0iwJ# | 1551312000154 |
 
| (*l!Y@H0iwJ# | 1551312000154 |
 
| #K4k0(M@cs%f | 1545782401494 |
 
| #K4k0(M@cs%f | 1545782401494 |
 
| #K4k0(M@cs%f | 1545782401494 |
 
| plTZBGi.I1JO | 1548806400185 |
 
| plTZBGi.I1JO | 1548806400185 |
 
| plTZBGi.I1JO | 1548806400185 |
 
| plTZBGi.I1JO | 1548806400185 |
 
+--------------+---------------+
 
21 rows in set (0.12 sec)

Comment by Igor Babaev [ 2019-05-15 ]

This bug was introduced by the patch for bug "MDEV-10006: optimizer doesn't convert outer join to inner on views with WHERE clause". As a result of this patch the matrix of dependencies between tables could be built incorrectly for some queries. In particular for the reported query not all elements of the matrix contained 0 though all RIGHT JOIN operations were converted to INNER JOIN operations. This forced the optimizer not to evaluate some good plans and the best of the remaining plans was slow.
The patch for MDEV-10006 was corrected and after the correction I got a much better execution plan:

ariaDB [test]> explain extended SELECT t1.modifiedBy, t1.timestamp FROM table1 t1 RIGHT JOIN table2 t2 ON t2.REV=t1.id RIGHT JOIN table3 t3 ON t3.id=t2.profile_id RIGHT JOIN table4 t4 ON t4.id=t3.person_id WHERE t1.timestamp < 1555324525347 AND t1.timestamp > 1544873725347 AND t2.REVTYPE=2;
 
+------+-------------+-------+--------+---------------+---------+---------+--------------------+--------+----------+-------------+
 
| id   | select_type | table | type   | possible_keys | key     | key_len | ref                | rows   | filtered | Extra       |
 
+------+-------------+-------+--------+---------------+---------+---------+--------------------+--------+----------+-------------+
 
|    1 | SIMPLE      | t2    | ALL    | NULL          | NULL    | NULL    | NULL               | 575811 |   100.00 | Using where |
 
|    1 | SIMPLE      | t3    | ref    | PRIMARY       | PRIMARY | 4       | test.t2.profile_id |      1 |   100.00 | Using where |
 
|    1 | SIMPLE      | t4    | ref    | PRIMARY       | PRIMARY | 4       | test.t3.person_id  |      1 |   100.00 | Using index |
 
|    1 | SIMPLE      | t1    | eq_ref | PRIMARY       | PRIMARY | 4       | test.t2.REV        |      1 |   100.00 | Using where |
 
+------+-------------+-------+--------+---------------+---------+---------+--------------------+--------+----------+-------------+
 
4 rows in set, 1 warning (0.00 sec)
 
MariaDB [test]> SELECT t1.modifiedBy, t1.timestamp FROM table1 t1 RIGHT JOIN table2 t2 ON t2.REV=t1.id RIGHT JOIN table3 t3 ON t3.id=t2.profile_id RIGHT JOIN table4 t4 ON t4.id=t3.person_id WHERE t1.timestamp < 1555324525347 AND t1.timestamp > 1544873725347 AND t2.REVTYPE=2;
 
+--------------+---------------+
 
| modifiedBy   | timestamp     |
 
+--------------+---------------+
 
| $2nJP)^Y1Owq | 1545177601090 |
 
| $2nJP)^Y1Owq | 1545177601090 |
 
| Q^HEb@nGq201 | 1550102400207 |
 
| Q^HEb@nGq201 | 1550102400207 |
 
| Q^HEb@nGq201 | 1550102400207 |
 
| Q^HEb@nGq201 | 1550102400207 |
 
| dv%3buXDsFjC | 1545004801365 |
 
| dv%3buXDsFjC | 1545004801365 |
 
| A?kPlT6ZxD.v | 1551139200175 |
 
| A?kPlT6ZxD.v | 1551139200175 |
 
| (*l!Y@H0iwJ# | 1551312000154 |
 
| (*l!Y@H0iwJ# | 1551312000154 |
 
| (*l!Y@H0iwJ# | 1551312000154 |
 
| (*l!Y@H0iwJ# | 1551312000154 |
 
| #K4k0(M@cs%f | 1545782401494 |
 
| #K4k0(M@cs%f | 1545782401494 |
 
| #K4k0(M@cs%f | 1545782401494 |
 
| plTZBGi.I1JO | 1548806400185 |
 
| plTZBGi.I1JO | 1548806400185 |
 
| plTZBGi.I1JO | 1548806400185 |
 
| plTZBGi.I1JO | 1548806400185 |
 
+--------------+---------------+
 
21 rows in set (0.28 sec)

After using settings:

set use_stat_tables=complementary; 
set optimizer_use_condition_selectivity=4;
 
set histogram_size=255;

and collecting some minimal statistics:

MariaDB [test]> analyze table table1 persistent for columns (timestamp) indexes();                                                             +-------------+---------+----------+-----------------------------------------+
 
| Table       | Op      | Msg_type | Msg_text                                |
 
+-------------+---------+----------+-----------------------------------------+
 
| test.table1 | analyze | status   | Engine-independent statistics collected |
 
| test.table1 | analyze | status   | OK                                      |
 
+-------------+---------+----------+-----------------------------------------+
 
2 rows in set (0.61 sec)
 
 
 
MariaDB [test]> analyze table table2 persistent for columns (REVTYPE) indexes();                                                               +-------------+---------+----------+-----------------------------------------+
 
| Table       | Op      | Msg_type | Msg_text                                |
 
+-------------+---------+----------+-----------------------------------------+
 
| test.table2 | analyze | status   | Engine-independent statistics collected |
 
| test.table2 | analyze | status   | OK                                      |
 
+-------------+---------+----------+-----------------------------------------+
 
2 rows in set (0.13 sec)

I got the same execution plan as mysql-8.0 chooses:

MariaDB [test]> explain extended SELECT t1.modifiedBy, t1.timestamp FROM table1 t1 RIGHT JOIN table2 t2 ON t2.REV=t1.id RIGHT JOIN table3 t3 ON t3.id=t2.profile_id RIGHT JOIN table4 t4 ON t4.id=t3.person_id WHERE t1.timestamp < 1555324525347 AND t1.timestamp > 1544873725347 AND t2.REVTYPE=2;
 
+------+-------------+-------+--------+---------------+---------+---------+--------------------+--------+----------+-------------+
 
| id   | select_type | table | type   | possible_keys | key     | key_len | ref                | rows   | filtered | Extra       |
 
+------+-------------+-------+--------+---------------+---------+---------+--------------------+--------+----------+-------------+
 
|    1 | SIMPLE      | t2    | ALL    | NULL          | NULL    | NULL    | NULL               | 575811 |     6.25 | Using where |
 
|    1 | SIMPLE      | t1    | eq_ref | PRIMARY       | PRIMARY | 4       | test.t2.REV        |      1 |     8.20 | Using where |
 
|    1 | SIMPLE      | t3    | ref    | PRIMARY       | PRIMARY | 4       | test.t2.profile_id |      1 |   100.00 | Using where |
 
|    1 | SIMPLE      | t4    | ref    | PRIMARY       | PRIMARY | 4       | test.t3.person_id  |      1 |   100.00 | Using index |
 
+------+-------------+-------+--------+---------------+---------+---------+--------------------+--------+----------+-------------+
 
4 rows in set, 1 warning (0.00 sec)
 
 
 
MariaDB [test]> SELECT t1.modifiedBy, t1.timestamp FROM table1 t1 RIGHT JOIN table2 t2 ON t2.REV=t1.id RIGHT JOIN table3 t3 ON t3.id=t2.profile_id RIGHT JOIN table4 t4 ON t4.id=t3.person_id WHERE t1.timestamp < 1555324525347 AND t1.timestamp > 1544873725347 AND t2.REVTYPE=2;
 
+--------------+---------------+
 
| modifiedBy   | timestamp     |
 
+--------------+---------------+
 
| $2nJP)^Y1Owq | 1545177601090 |
 
| $2nJP)^Y1Owq | 1545177601090 |
 
| Q^HEb@nGq201 | 1550102400207 |
 
| Q^HEb@nGq201 | 1550102400207 |
 
| Q^HEb@nGq201 | 1550102400207 |
 
| Q^HEb@nGq201 | 1550102400207 |
 
| dv%3buXDsFjC | 1545004801365 |
 
| dv%3buXDsFjC | 1545004801365 |
 
| A?kPlT6ZxD.v | 1551139200175 |
 
| A?kPlT6ZxD.v | 1551139200175 |
 
| (*l!Y@H0iwJ# | 1551312000154 |
 
| (*l!Y@H0iwJ# | 1551312000154 |
 
| (*l!Y@H0iwJ# | 1551312000154 |
 
| (*l!Y@H0iwJ# | 1551312000154 |
 
| #K4k0(M@cs%f | 1545782401494 |
 
| #K4k0(M@cs%f | 1545782401494 |
 
| #K4k0(M@cs%f | 1545782401494 |
 
| plTZBGi.I1JO | 1548806400185 |
 
| plTZBGi.I1JO | 1548806400185 |
 
| plTZBGi.I1JO | 1548806400185 |
 
| plTZBGi.I1JO | 1548806400185 |
 
+--------------+---------------+
 
21 rows in set (0.14 sec)

Comment by Igor Babaev [ 2019-05-24 ]

Review was actually not done.

Comment by Igor Babaev [ 2019-05-24 ]

This patch was pushed into 10.1

Generated at Thu Feb 08 08:50:15 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.