[MDEV-19258] RIGHT JOIN hangs in MariaDB - Jira

Details

Type: Bug
Status: Closed (View Workflow)
Priority: Major
Resolution: Fixed
Affects Version/s: 10.3.13, 5.5(EOL), 10.0(EOL), 10.1(EOL), 10.2(EOL), 10.3(EOL), 10.4(EOL)
Fix Version/s: 10.1.41
Component/s: Storage Engine - InnoDB
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

Attachments

Activity

Ascending order - Click to sort in descending order

Rok Jaklic added a comment - 2019-04-23 06:53 - edited

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".

Rok Jaklic added a comment - 2019-04-23 06:53 - edited 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".

Alice Sherepa added a comment - 2019-05-13 15:40 - edited

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)

Alice Sherepa added a comment - 2019-05-13 15:40 - edited 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)

Igor Babaev (Inactive) added a comment - 2019-05-15 00:19

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)

Igor Babaev (Inactive) added a comment - 2019-05-15 00:19 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)

Igor Babaev (Inactive) added a comment - 2019-05-24 17:41

Review was actually not done.

Igor Babaev (Inactive) added a comment - 2019-05-24 17:41 Review was actually not done.

Igor Babaev (Inactive) added a comment - 2019-05-24 18:23

This patch was pushed into 10.1

Igor Babaev (Inactive) added a comment - 2019-05-24 18:23 This patch was pushed into 10.1

People

Assignee:: Igor Babaev (Inactive)

Reporter:: Rok Jaklic

Votes:: 0 Vote for this issue

Watchers:: 4 Start watching this issue

Dates

Created:: 2019-04-16 08:56

Updated:: 2019-05-24 18:23

Resolved:: 2019-05-24 18:23

Git Integration

Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.

MariaDB Server