Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
10.3.13, 5.5(EOL), 10.0(EOL), 10.1(EOL), 10.2(EOL), 10.3(EOL), 10.4(EOL)
-
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
| Field | Original Value | New Value |
|---|---|---|
| Description |
We are migrating (dump, restore) data from Mysql version 5.5.61-38.13-log, however we found a problem when RIGHT JOIN statement hangs, but inner join does not.
This one works in Mysql: {code:sql} SELECT u.username, u.created, o.name, o.surname, a.modifiedBy, a.timestamp FROM aris_revision a RIGHT JOIN user_arnes_AUD u ON u.REV=a.id RIGHT JOIN profile_AUD p ON p.id=u.profile_id RIGHT JOIN person_AUD o ON o.id=p.person_id WHERE a.timestamp < 1555324525347 AND a.timestamp > 1544873725347 AND u.REVTYPE=2; {code} and hangs in MariaDB. Also explain-s are different where on MariaDB is: {code:java} MariaDB [aris]> explain SELECT u.username, u.created, o.name, o.surname, a.modifiedBy, a.timestamp FROM aris_revision a RIGHT JOIN user_arnes_AUD u ON u.REV=a.id RIGHT JOIN profile_AUD p ON p.id=u.profile_id RIGHT JOIN person_AUD o ON o.id=p.person_id WHERE a.timestamp < 1555324525347 AND a.timestamp > 1544873725347 AND u.REVTYPE=2; +------+-------------+-------+--------+---------------+---------+---------+------------------+--------+-------------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+-------------+-------+--------+---------------+---------+---------+------------------+--------+-------------------------------------------------+ | 1 | SIMPLE | p | ALL | PRIMARY | NULL | NULL | NULL | 291000 | Using where | | 1 | SIMPLE | o | ref | PRIMARY | PRIMARY | 4 | aris.p.person_id | 1 | | | 1 | SIMPLE | u | ALL | NULL | NULL | NULL | NULL | 538480 | Using where; Using join buffer (flat, BNL join) | | 1 | SIMPLE | a | eq_ref | PRIMARY | PRIMARY | 4 | aris.u.REV | 1 | Using where | +------+-------------+-------+--------+---------------+---------+---------+------------------+--------+-------------------------------------------------+ {code} while in Mysql is: {code:sql} MySQL [aris]> explain SELECT u.username, u.created, o.name, o.surname, a.modifiedBy, a.timestamp FROM aris_revision a RIGHT JOIN user_arnes_AUD u ON u.REV=a.id RIGHT JOIN profile_AUD p ON p.id=u.profile_id RIGHT JOIN person_AUD o ON o.id=p.person_id WHERE a.timestamp < 1555324525347 AND a.timestamp > 1544873725347 AND u.REVTYPE=2; +----+-------------+-------+--------+---------------+---------+---------+-------------------+--------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+--------+---------------+---------+---------+-------------------+--------+-------------+ | 1 | SIMPLE | u | ALL | NULL | NULL | NULL | NULL | 591451 | Using where | | 1 | SIMPLE | p | ref | PRIMARY | PRIMARY | 4 | aris.u.profile_id | 1 | Using where | | 1 | SIMPLE | o | ref | PRIMARY | PRIMARY | 4 | aris.p.person_id | 1 | | | 1 | SIMPLE | a | eq_ref | PRIMARY | PRIMARY | 4 | aris.u.REV | 1 | Using where | +----+-------------+-------+--------+---------------+---------+---------+-------------------+--------+-------------+ {code} |
We are migrating (dump, restore) data from Mysql version 5.5.61-38.13-log, however we found a problem when RIGHT JOIN statement hangs, but inner join does not.
This one works in Mysql: {code:sql} SELECT u.username, u.created, o.name, o.surname, a.modifiedBy, a.timestamp FROM aris_revision a RIGHT JOIN user_arnes_AUD u ON u.REV=a.id RIGHT JOIN profile_AUD p ON p.id=u.profile_id RIGHT JOIN person_AUD o ON o.id=p.person_id WHERE a.timestamp < 1555324525347 AND a.timestamp > 1544873725347 AND u.REVTYPE=2; {code} and hangs in MariaDB. Also explain-s are different where on MariaDB is: {code:java} MariaDB [aris]> explain SELECT u.username, u.created, o.name, o.surname, a.modifiedBy, a.timestamp FROM aris_revision a RIGHT JOIN user_arnes_AUD u ON u.REV=a.id RIGHT JOIN profile_AUD p ON p.id=u.profile_id RIGHT JOIN person_AUD o ON o.id=p.person_id WHERE a.timestamp < 1555324525347 AND a.timestamp > 1544873725347 AND u.REVTYPE=2; +------+-------------+-------+--------+---------------+---------+---------+------------------+--------+-------------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+-------------+-------+--------+---------------+---------+---------+------------------+--------+-------------------------------------------------+ | 1 | SIMPLE | p | ALL | PRIMARY | NULL | NULL | NULL | 291000 | Using where | | 1 | SIMPLE | o | ref | PRIMARY | PRIMARY | 4 | aris.p.person_id | 1 | | | 1 | SIMPLE | u | ALL | NULL | NULL | NULL | NULL | 538480 | Using where; Using join buffer (flat, BNL join) | | 1 | SIMPLE | a | eq_ref | PRIMARY | PRIMARY | 4 | aris.u.REV | 1 | Using where | +------+-------------+-------+--------+---------------+---------+---------+------------------+--------+-------------------------------------------------+ {code} while in Mysql is: {code:sql} MySQL [aris]> explain SELECT u.username, u.created, o.name, o.surname, a.modifiedBy, a.timestamp FROM aris_revision a RIGHT JOIN user_arnes_AUD u ON u.REV=a.id RIGHT JOIN profile_AUD p ON p.id=u.profile_id RIGHT JOIN person_AUD o ON o.id=p.person_id WHERE a.timestamp < 1555324525347 AND a.timestamp > 1544873725347 AND u.REVTYPE=2; +----+-------------+-------+--------+---------------+---------+---------+-------------------+--------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+--------+---------------+---------+---------+-------------------+--------+-------------+ | 1 | SIMPLE | u | ALL | NULL | NULL | NULL | NULL | 591451 | Using where | | 1 | SIMPLE | p | ref | PRIMARY | PRIMARY | 4 | aris.u.profile_id | 1 | Using where | | 1 | SIMPLE | o | ref | PRIMARY | PRIMARY | 4 | aris.p.person_id | 1 | | | 1 | SIMPLE | a | eq_ref | PRIMARY | PRIMARY | 4 | aris.u.REV | 1 | Using where | +----+-------------+-------+--------+---------------+---------+---------+-------------------+--------+-------------+ {code} However if we change that RIGHT JOIN statement to: {code:sql} MariaDB [aris]> explain SELECT u.username, u.created, o.name, o.surname, a.modifiedBy, a.timestamp FROM aris_revision a, user_arnes_AUD u, profile_AUD p, person_AUD o WHERE u.REV=a.id AND p.id=u.profile_id AND o.id=p.person_id AND a.timestamp < 1555324525347 AND a.timestamp > 1544873725347 AND u.REVTYPE=2; +------+-------------+-------+--------+---------------+---------+---------+-------------------+--------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+-------------+-------+--------+---------------+---------+---------+-------------------+--------+-------------+ | 1 | SIMPLE | u | ALL | NULL | NULL | NULL | NULL | 538480 | Using where | | 1 | SIMPLE | p | ref | PRIMARY | PRIMARY | 4 | aris.u.profile_id | 1 | Using where | | 1 | SIMPLE | o | ref | PRIMARY | PRIMARY | 4 | aris.p.person_id | 1 | | | 1 | SIMPLE | a | eq_ref | PRIMARY | PRIMARY | 4 | aris.u.REV | 1 | Using where | +------+-------------+-------+--------+---------------+---------+---------+-------------------+--------+-------------+ {code} it works like it should. Any ideas why? |
| Description |
We are migrating (dump, restore) data from Mysql version 5.5.61-38.13-log, however we found a problem when RIGHT JOIN statement hangs, but inner join does not.
This one works in Mysql: {code:sql} SELECT u.username, u.created, o.name, o.surname, a.modifiedBy, a.timestamp FROM aris_revision a RIGHT JOIN user_arnes_AUD u ON u.REV=a.id RIGHT JOIN profile_AUD p ON p.id=u.profile_id RIGHT JOIN person_AUD o ON o.id=p.person_id WHERE a.timestamp < 1555324525347 AND a.timestamp > 1544873725347 AND u.REVTYPE=2; {code} and hangs in MariaDB. Also explain-s are different where on MariaDB is: {code:java} MariaDB [aris]> explain SELECT u.username, u.created, o.name, o.surname, a.modifiedBy, a.timestamp FROM aris_revision a RIGHT JOIN user_arnes_AUD u ON u.REV=a.id RIGHT JOIN profile_AUD p ON p.id=u.profile_id RIGHT JOIN person_AUD o ON o.id=p.person_id WHERE a.timestamp < 1555324525347 AND a.timestamp > 1544873725347 AND u.REVTYPE=2; +------+-------------+-------+--------+---------------+---------+---------+------------------+--------+-------------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+-------------+-------+--------+---------------+---------+---------+------------------+--------+-------------------------------------------------+ | 1 | SIMPLE | p | ALL | PRIMARY | NULL | NULL | NULL | 291000 | Using where | | 1 | SIMPLE | o | ref | PRIMARY | PRIMARY | 4 | aris.p.person_id | 1 | | | 1 | SIMPLE | u | ALL | NULL | NULL | NULL | NULL | 538480 | Using where; Using join buffer (flat, BNL join) | | 1 | SIMPLE | a | eq_ref | PRIMARY | PRIMARY | 4 | aris.u.REV | 1 | Using where | +------+-------------+-------+--------+---------------+---------+---------+------------------+--------+-------------------------------------------------+ {code} while in Mysql is: {code:sql} MySQL [aris]> explain SELECT u.username, u.created, o.name, o.surname, a.modifiedBy, a.timestamp FROM aris_revision a RIGHT JOIN user_arnes_AUD u ON u.REV=a.id RIGHT JOIN profile_AUD p ON p.id=u.profile_id RIGHT JOIN person_AUD o ON o.id=p.person_id WHERE a.timestamp < 1555324525347 AND a.timestamp > 1544873725347 AND u.REVTYPE=2; +----+-------------+-------+--------+---------------+---------+---------+-------------------+--------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+--------+---------------+---------+---------+-------------------+--------+-------------+ | 1 | SIMPLE | u | ALL | NULL | NULL | NULL | NULL | 591451 | Using where | | 1 | SIMPLE | p | ref | PRIMARY | PRIMARY | 4 | aris.u.profile_id | 1 | Using where | | 1 | SIMPLE | o | ref | PRIMARY | PRIMARY | 4 | aris.p.person_id | 1 | | | 1 | SIMPLE | a | eq_ref | PRIMARY | PRIMARY | 4 | aris.u.REV | 1 | Using where | +----+-------------+-------+--------+---------------+---------+---------+-------------------+--------+-------------+ {code} However if we change that RIGHT JOIN statement to: {code:sql} MariaDB [aris]> explain SELECT u.username, u.created, o.name, o.surname, a.modifiedBy, a.timestamp FROM aris_revision a, user_arnes_AUD u, profile_AUD p, person_AUD o WHERE u.REV=a.id AND p.id=u.profile_id AND o.id=p.person_id AND a.timestamp < 1555324525347 AND a.timestamp > 1544873725347 AND u.REVTYPE=2; +------+-------------+-------+--------+---------------+---------+---------+-------------------+--------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+-------------+-------+--------+---------------+---------+---------+-------------------+--------+-------------+ | 1 | SIMPLE | u | ALL | NULL | NULL | NULL | NULL | 538480 | Using where | | 1 | SIMPLE | p | ref | PRIMARY | PRIMARY | 4 | aris.u.profile_id | 1 | Using where | | 1 | SIMPLE | o | ref | PRIMARY | PRIMARY | 4 | aris.p.person_id | 1 | | | 1 | SIMPLE | a | eq_ref | PRIMARY | PRIMARY | 4 | aris.u.REV | 1 | Using where | +------+-------------+-------+--------+---------------+---------+---------+-------------------+--------+-------------+ {code} it works like it should. Any ideas why? |
We are migrating (dump, restore) data from Mysql version 5.5.61-38.13-log, however we found a problem when RIGHT JOIN statement hangs, but inner join does not.
This one works in Mysql: {code:sql} SELECT u.username, u.created, o.name, o.surname, a.modifiedBy, a.timestamp FROM aris_revision a RIGHT JOIN user_arnes_AUD u ON u.REV=a.id RIGHT JOIN profile_AUD p ON p.id=u.profile_id RIGHT JOIN person_AUD o ON o.id=p.person_id WHERE a.timestamp < 1555324525347 AND a.timestamp > 1544873725347 AND u.REVTYPE=2; {code} and hangs in MariaDB. Also explain-s are different where on MariaDB is: {code:java} MariaDB [aris]> explain SELECT u.username, u.created, o.name, o.surname, a.modifiedBy, a.timestamp FROM aris_revision a RIGHT JOIN user_arnes_AUD u ON u.REV=a.id RIGHT JOIN profile_AUD p ON p.id=u.profile_id RIGHT JOIN person_AUD o ON o.id=p.person_id WHERE a.timestamp < 1555324525347 AND a.timestamp > 1544873725347 AND u.REVTYPE=2; +------+-------------+-------+--------+---------------+---------+---------+------------------+--------+-------------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+-------------+-------+--------+---------------+---------+---------+------------------+--------+-------------------------------------------------+ | 1 | SIMPLE | p | ALL | PRIMARY | NULL | NULL | NULL | 291000 | Using where | | 1 | SIMPLE | o | ref | PRIMARY | PRIMARY | 4 | aris.p.person_id | 1 | | | 1 | SIMPLE | u | ALL | NULL | NULL | NULL | NULL | 538480 | Using where; Using join buffer (flat, BNL join) | | 1 | SIMPLE | a | eq_ref | PRIMARY | PRIMARY | 4 | aris.u.REV | 1 | Using where | +------+-------------+-------+--------+---------------+---------+---------+------------------+--------+-------------------------------------------------+ {code} while in Mysql is: {code:sql} MySQL [aris]> explain SELECT u.username, u.created, o.name, o.surname, a.modifiedBy, a.timestamp FROM aris_revision a RIGHT JOIN user_arnes_AUD u ON u.REV=a.id RIGHT JOIN profile_AUD p ON p.id=u.profile_id RIGHT JOIN person_AUD o ON o.id=p.person_id WHERE a.timestamp < 1555324525347 AND a.timestamp > 1544873725347 AND u.REVTYPE=2; +----+-------------+-------+--------+---------------+---------+---------+-------------------+--------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+--------+---------------+---------+---------+-------------------+--------+-------------+ | 1 | SIMPLE | u | ALL | NULL | NULL | NULL | NULL | 591451 | Using where | | 1 | SIMPLE | p | ref | PRIMARY | PRIMARY | 4 | aris.u.profile_id | 1 | Using where | | 1 | SIMPLE | o | ref | PRIMARY | PRIMARY | 4 | aris.p.person_id | 1 | | | 1 | SIMPLE | a | eq_ref | PRIMARY | PRIMARY | 4 | aris.u.REV | 1 | Using where | +----+-------------+-------+--------+---------------+---------+---------+-------------------+--------+-------------+ {code} ---- However if we change that RIGHT JOIN statement to: {code:sql} MariaDB [aris]> explain SELECT u.username, u.created, o.name, o.surname, a.modifiedBy, a.timestamp FROM aris_revision a, user_arnes_AUD u, profile_AUD p, person_AUD o WHERE u.REV=a.id AND p.id=u.profile_id AND o.id=p.person_id AND a.timestamp < 1555324525347 AND a.timestamp > 1544873725347 AND u.REVTYPE=2; +------+-------------+-------+--------+---------------+---------+---------+-------------------+--------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+-------------+-------+--------+---------------+---------+---------+-------------------+--------+-------------+ | 1 | SIMPLE | u | ALL | NULL | NULL | NULL | NULL | 538480 | Using where | | 1 | SIMPLE | p | ref | PRIMARY | PRIMARY | 4 | aris.u.profile_id | 1 | Using where | | 1 | SIMPLE | o | ref | PRIMARY | PRIMARY | 4 | aris.p.person_id | 1 | | | 1 | SIMPLE | a | eq_ref | PRIMARY | PRIMARY | 4 | aris.u.REV | 1 | Using where | +------+-------------+-------+--------+---------------+---------+---------+-------------------+--------+-------------+ {code} it works like it should. Any ideas why? |
| Description |
We are migrating (dump, restore) data from Mysql version 5.5.61-38.13-log, however we found a problem when RIGHT JOIN statement hangs, but inner join does not.
This one works in Mysql: {code:sql} SELECT u.username, u.created, o.name, o.surname, a.modifiedBy, a.timestamp FROM aris_revision a RIGHT JOIN user_arnes_AUD u ON u.REV=a.id RIGHT JOIN profile_AUD p ON p.id=u.profile_id RIGHT JOIN person_AUD o ON o.id=p.person_id WHERE a.timestamp < 1555324525347 AND a.timestamp > 1544873725347 AND u.REVTYPE=2; {code} and hangs in MariaDB. Also explain-s are different where on MariaDB is: {code:java} MariaDB [aris]> explain SELECT u.username, u.created, o.name, o.surname, a.modifiedBy, a.timestamp FROM aris_revision a RIGHT JOIN user_arnes_AUD u ON u.REV=a.id RIGHT JOIN profile_AUD p ON p.id=u.profile_id RIGHT JOIN person_AUD o ON o.id=p.person_id WHERE a.timestamp < 1555324525347 AND a.timestamp > 1544873725347 AND u.REVTYPE=2; +------+-------------+-------+--------+---------------+---------+---------+------------------+--------+-------------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+-------------+-------+--------+---------------+---------+---------+------------------+--------+-------------------------------------------------+ | 1 | SIMPLE | p | ALL | PRIMARY | NULL | NULL | NULL | 291000 | Using where | | 1 | SIMPLE | o | ref | PRIMARY | PRIMARY | 4 | aris.p.person_id | 1 | | | 1 | SIMPLE | u | ALL | NULL | NULL | NULL | NULL | 538480 | Using where; Using join buffer (flat, BNL join) | | 1 | SIMPLE | a | eq_ref | PRIMARY | PRIMARY | 4 | aris.u.REV | 1 | Using where | +------+-------------+-------+--------+---------------+---------+---------+------------------+--------+-------------------------------------------------+ {code} while in Mysql is: {code:sql} MySQL [aris]> explain SELECT u.username, u.created, o.name, o.surname, a.modifiedBy, a.timestamp FROM aris_revision a RIGHT JOIN user_arnes_AUD u ON u.REV=a.id RIGHT JOIN profile_AUD p ON p.id=u.profile_id RIGHT JOIN person_AUD o ON o.id=p.person_id WHERE a.timestamp < 1555324525347 AND a.timestamp > 1544873725347 AND u.REVTYPE=2; +----+-------------+-------+--------+---------------+---------+---------+-------------------+--------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+--------+---------------+---------+---------+-------------------+--------+-------------+ | 1 | SIMPLE | u | ALL | NULL | NULL | NULL | NULL | 591451 | Using where | | 1 | SIMPLE | p | ref | PRIMARY | PRIMARY | 4 | aris.u.profile_id | 1 | Using where | | 1 | SIMPLE | o | ref | PRIMARY | PRIMARY | 4 | aris.p.person_id | 1 | | | 1 | SIMPLE | a | eq_ref | PRIMARY | PRIMARY | 4 | aris.u.REV | 1 | Using where | +----+-------------+-------+--------+---------------+---------+---------+-------------------+--------+-------------+ {code} ---- However if we change that RIGHT JOIN statement to: {code:sql} MariaDB [aris]> explain SELECT u.username, u.created, o.name, o.surname, a.modifiedBy, a.timestamp FROM aris_revision a, user_arnes_AUD u, profile_AUD p, person_AUD o WHERE u.REV=a.id AND p.id=u.profile_id AND o.id=p.person_id AND a.timestamp < 1555324525347 AND a.timestamp > 1544873725347 AND u.REVTYPE=2; +------+-------------+-------+--------+---------------+---------+---------+-------------------+--------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+-------------+-------+--------+---------------+---------+---------+-------------------+--------+-------------+ | 1 | SIMPLE | u | ALL | NULL | NULL | NULL | NULL | 538480 | Using where | | 1 | SIMPLE | p | ref | PRIMARY | PRIMARY | 4 | aris.u.profile_id | 1 | Using where | | 1 | SIMPLE | o | ref | PRIMARY | PRIMARY | 4 | aris.p.person_id | 1 | | | 1 | SIMPLE | a | eq_ref | PRIMARY | PRIMARY | 4 | aris.u.REV | 1 | Using where | +------+-------------+-------+--------+---------------+---------+---------+-------------------+--------+-------------+ {code} it works like it should. Any ideas why? |
We are migrating (dump, restore) data from Mysql version 5.5.61-38.13-log, however we found a problem when RIGHT JOIN statement hangs, but inner join does not.
This one works in Mysql: {code:sql} SELECT u.username, u.created, o.name, o.surname, a.modifiedBy, a.timestamp FROM aris_revision a RIGHT JOIN user_arnes_AUD u ON u.REV=a.id RIGHT JOIN profile_AUD p ON p.id=u.profile_id RIGHT JOIN person_AUD o ON o.id=p.person_id WHERE a.timestamp < 1555324525347 AND a.timestamp > 1544873725347 AND u.REVTYPE=2; {code} and hangs in MariaDB. Also explain-s are different where on MariaDB is: {code:java} MariaDB [aris]> explain SELECT u.username, u.created, o.name, o.surname, a.modifiedBy, a.timestamp FROM aris_revision a RIGHT JOIN user_arnes_AUD u ON u.REV=a.id RIGHT JOIN profile_AUD p ON p.id=u.profile_id RIGHT JOIN person_AUD o ON o.id=p.person_id WHERE a.timestamp < 1555324525347 AND a.timestamp > 1544873725347 AND u.REVTYPE=2; +------+-------------+-------+--------+---------------+---------+---------+------------------+--------+-------------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+-------------+-------+--------+---------------+---------+---------+------------------+--------+-------------------------------------------------+ | 1 | SIMPLE | p | ALL | PRIMARY | NULL | NULL | NULL | 291000 | Using where | | 1 | SIMPLE | o | ref | PRIMARY | PRIMARY | 4 | aris.p.person_id | 1 | | | 1 | SIMPLE | u | ALL | NULL | NULL | NULL | NULL | 538480 | Using where; Using join buffer (flat, BNL join) | | 1 | SIMPLE | a | eq_ref | PRIMARY | PRIMARY | 4 | aris.u.REV | 1 | Using where | +------+-------------+-------+--------+---------------+---------+---------+------------------+--------+-------------------------------------------------+ {code} while in Mysql is: {code:sql} MySQL [aris]> explain SELECT u.username, u.created, o.name, o.surname, a.modifiedBy, a.timestamp FROM aris_revision a RIGHT JOIN user_arnes_AUD u ON u.REV=a.id RIGHT JOIN profile_AUD p ON p.id=u.profile_id RIGHT JOIN person_AUD o ON o.id=p.person_id WHERE a.timestamp < 1555324525347 AND a.timestamp > 1544873725347 AND u.REVTYPE=2; +----+-------------+-------+--------+---------------+---------+---------+-------------------+--------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+--------+---------------+---------+---------+-------------------+--------+-------------+ | 1 | SIMPLE | u | ALL | NULL | NULL | NULL | NULL | 591451 | Using where | | 1 | SIMPLE | p | ref | PRIMARY | PRIMARY | 4 | aris.u.profile_id | 1 | Using where | | 1 | SIMPLE | o | ref | PRIMARY | PRIMARY | 4 | aris.p.person_id | 1 | | | 1 | SIMPLE | a | eq_ref | PRIMARY | PRIMARY | 4 | aris.u.REV | 1 | Using where | +----+-------------+-------+--------+---------------+---------+---------+-------------------+--------+-------------+ {code} ---- However if we change that RIGHT JOIN statement to: {code:sql} MariaDB [aris]> explain SELECT u.username, u.created, o.name, o.surname, a.modifiedBy, a.timestamp FROM aris_revision a, user_arnes_AUD u, profile_AUD p, person_AUD o WHERE u.REV=a.id AND p.id=u.profile_id AND o.id=p.person_id AND a.timestamp < 1555324525347 AND a.timestamp > 1544873725347 AND u.REVTYPE=2; +------+-------------+-------+--------+---------------+---------+---------+-------------------+--------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+-------------+-------+--------+---------------+---------+---------+-------------------+--------+-------------+ | 1 | SIMPLE | u | ALL | NULL | NULL | NULL | NULL | 538480 | Using where | | 1 | SIMPLE | p | ref | PRIMARY | PRIMARY | 4 | aris.u.profile_id | 1 | Using where | | 1 | SIMPLE | o | ref | PRIMARY | PRIMARY | 4 | aris.p.person_id | 1 | | | 1 | SIMPLE | a | eq_ref | PRIMARY | PRIMARY | 4 | aris.u.REV | 1 | Using where | +------+-------------+-------+--------+---------------+---------+---------+-------------------+--------+-------------+ {code} it works like it should. Any ideas why? |
| Description |
We are migrating (dump, restore) data from Mysql version 5.5.61-38.13-log, however we found a problem when RIGHT JOIN statement hangs, but inner join does not.
This one works in Mysql: {code:sql} SELECT u.username, u.created, o.name, o.surname, a.modifiedBy, a.timestamp FROM aris_revision a RIGHT JOIN user_arnes_AUD u ON u.REV=a.id RIGHT JOIN profile_AUD p ON p.id=u.profile_id RIGHT JOIN person_AUD o ON o.id=p.person_id WHERE a.timestamp < 1555324525347 AND a.timestamp > 1544873725347 AND u.REVTYPE=2; {code} and hangs in MariaDB. Also explain-s are different where on MariaDB is: {code:java} MariaDB [aris]> explain SELECT u.username, u.created, o.name, o.surname, a.modifiedBy, a.timestamp FROM aris_revision a RIGHT JOIN user_arnes_AUD u ON u.REV=a.id RIGHT JOIN profile_AUD p ON p.id=u.profile_id RIGHT JOIN person_AUD o ON o.id=p.person_id WHERE a.timestamp < 1555324525347 AND a.timestamp > 1544873725347 AND u.REVTYPE=2; +------+-------------+-------+--------+---------------+---------+---------+------------------+--------+-------------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+-------------+-------+--------+---------------+---------+---------+------------------+--------+-------------------------------------------------+ | 1 | SIMPLE | p | ALL | PRIMARY | NULL | NULL | NULL | 291000 | Using where | | 1 | SIMPLE | o | ref | PRIMARY | PRIMARY | 4 | aris.p.person_id | 1 | | | 1 | SIMPLE | u | ALL | NULL | NULL | NULL | NULL | 538480 | Using where; Using join buffer (flat, BNL join) | | 1 | SIMPLE | a | eq_ref | PRIMARY | PRIMARY | 4 | aris.u.REV | 1 | Using where | +------+-------------+-------+--------+---------------+---------+---------+------------------+--------+-------------------------------------------------+ {code} while in Mysql is: {code:sql} MySQL [aris]> explain SELECT u.username, u.created, o.name, o.surname, a.modifiedBy, a.timestamp FROM aris_revision a RIGHT JOIN user_arnes_AUD u ON u.REV=a.id RIGHT JOIN profile_AUD p ON p.id=u.profile_id RIGHT JOIN person_AUD o ON o.id=p.person_id WHERE a.timestamp < 1555324525347 AND a.timestamp > 1544873725347 AND u.REVTYPE=2; +----+-------------+-------+--------+---------------+---------+---------+-------------------+--------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+--------+---------------+---------+---------+-------------------+--------+-------------+ | 1 | SIMPLE | u | ALL | NULL | NULL | NULL | NULL | 591451 | Using where | | 1 | SIMPLE | p | ref | PRIMARY | PRIMARY | 4 | aris.u.profile_id | 1 | Using where | | 1 | SIMPLE | o | ref | PRIMARY | PRIMARY | 4 | aris.p.person_id | 1 | | | 1 | SIMPLE | a | eq_ref | PRIMARY | PRIMARY | 4 | aris.u.REV | 1 | Using where | +----+-------------+-------+--------+---------------+---------+---------+-------------------+--------+-------------+ {code} ---- However if we change that RIGHT JOIN statement to: {code:sql} MariaDB [aris]> explain SELECT u.username, u.created, o.name, o.surname, a.modifiedBy, a.timestamp FROM aris_revision a, user_arnes_AUD u, profile_AUD p, person_AUD o WHERE u.REV=a.id AND p.id=u.profile_id AND o.id=p.person_id AND a.timestamp < 1555324525347 AND a.timestamp > 1544873725347 AND u.REVTYPE=2; +------+-------------+-------+--------+---------------+---------+---------+-------------------+--------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+-------------+-------+--------+---------------+---------+---------+-------------------+--------+-------------+ | 1 | SIMPLE | u | ALL | NULL | NULL | NULL | NULL | 538480 | Using where | | 1 | SIMPLE | p | ref | PRIMARY | PRIMARY | 4 | aris.u.profile_id | 1 | Using where | | 1 | SIMPLE | o | ref | PRIMARY | PRIMARY | 4 | aris.p.person_id | 1 | | | 1 | SIMPLE | a | eq_ref | PRIMARY | PRIMARY | 4 | aris.u.REV | 1 | Using where | +------+-------------+-------+--------+---------------+---------+---------+-------------------+--------+-------------+ {code} it works like it should. Any ideas why? |
We are migrating (dump, restore) data from Mysql version 5.5.61-38.13-log, however we found a problem when RIGHT JOIN statement hangs, but inner join does not.
This one works in Mysql: {code:sql} SELECT u.username, u.created, o.name, o.surname, a.modifiedBy, a.timestamp FROM aris_revision a RIGHT JOIN user_arnes_AUD u ON u.REV=a.id RIGHT JOIN profile_AUD p ON p.id=u.profile_id RIGHT JOIN person_AUD o ON o.id=p.person_id WHERE a.timestamp < 1555324525347 AND a.timestamp > 1544873725347 AND u.REVTYPE=2; {code} and hangs in MariaDB. Also explain-s are different where on MariaDB is: {code:java} MariaDB [aris]> explain SELECT u.username, u.created, o.name, o.surname, a.modifiedBy, a.timestamp FROM aris_revision a RIGHT JOIN user_arnes_AUD u ON u.REV=a.id RIGHT JOIN profile_AUD p ON p.id=u.profile_id RIGHT JOIN person_AUD o ON o.id=p.person_id WHERE a.timestamp < 1555324525347 AND a.timestamp > 1544873725347 AND u.REVTYPE=2; +------+-------------+-------+--------+---------------+---------+---------+------------------+--------+-------------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+-------------+-------+--------+---------------+---------+---------+------------------+--------+-------------------------------------------------+ | 1 | SIMPLE | p | ALL | PRIMARY | NULL | NULL | NULL | 291000 | Using where | | 1 | SIMPLE | o | ref | PRIMARY | PRIMARY | 4 | aris.p.person_id | 1 | | | 1 | SIMPLE | u | ALL | NULL | NULL | NULL | NULL | 538480 | Using where; Using join buffer (flat, BNL join) | | 1 | SIMPLE | a | eq_ref | PRIMARY | PRIMARY | 4 | aris.u.REV | 1 | Using where | +------+-------------+-------+--------+---------------+---------+---------+------------------+--------+-------------------------------------------------+ {code} while in Mysql is: {code:sql} MySQL [aris]> explain SELECT u.username, u.created, o.name, o.surname, a.modifiedBy, a.timestamp FROM aris_revision a RIGHT JOIN user_arnes_AUD u ON u.REV=a.id RIGHT JOIN profile_AUD p ON p.id=u.profile_id RIGHT JOIN person_AUD o ON o.id=p.person_id WHERE a.timestamp < 1555324525347 AND a.timestamp > 1544873725347 AND u.REVTYPE=2; +----+-------------+-------+--------+---------------+---------+---------+-------------------+--------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+--------+---------------+---------+---------+-------------------+--------+-------------+ | 1 | SIMPLE | u | ALL | NULL | NULL | NULL | NULL | 591451 | Using where | | 1 | SIMPLE | p | ref | PRIMARY | PRIMARY | 4 | aris.u.profile_id | 1 | Using where | | 1 | SIMPLE | o | ref | PRIMARY | PRIMARY | 4 | aris.p.person_id | 1 | | | 1 | SIMPLE | a | eq_ref | PRIMARY | PRIMARY | 4 | aris.u.REV | 1 | Using where | +----+-------------+-------+--------+---------------+---------+---------+-------------------+--------+-------------+ {code} ---- However if we change that RIGHT JOIN statement to: {code:sql} MariaDB [aris]> explain SELECT u.username, u.created, o.name, o.surname, a.modifiedBy, a.timestamp FROM aris_revision a, user_arnes_AUD u, profile_AUD p, person_AUD o WHERE u.REV=a.id AND p.id=u.profile_id AND o.id=p.person_id AND a.timestamp < 1555324525347 AND a.timestamp > 1544873725347 AND u.REVTYPE=2; +------+-------------+-------+--------+---------------+---------+---------+-------------------+--------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+-------------+-------+--------+---------------+---------+---------+-------------------+--------+-------------+ | 1 | SIMPLE | u | ALL | NULL | NULL | NULL | NULL | 538480 | Using where | | 1 | SIMPLE | p | ref | PRIMARY | PRIMARY | 4 | aris.u.profile_id | 1 | Using where | | 1 | SIMPLE | o | ref | PRIMARY | PRIMARY | 4 | aris.p.person_id | 1 | | | 1 | SIMPLE | a | eq_ref | PRIMARY | PRIMARY | 4 | aris.u.REV | 1 | Using where | +------+-------------+-------+--------+---------------+---------+---------+-------------------+--------+-------------+ {code} it works like it should. We found out that INNER JOIN, LEFT JOIN works, but RIGHT JOIN hangs. Any ideas why? |
| Attachment | create.sql [ 47956 ] |
| Description |
We are migrating (dump, restore) data from Mysql version 5.5.61-38.13-log, however we found a problem when RIGHT JOIN statement hangs, but inner join does not.
This one works in Mysql: {code:sql} SELECT u.username, u.created, o.name, o.surname, a.modifiedBy, a.timestamp FROM aris_revision a RIGHT JOIN user_arnes_AUD u ON u.REV=a.id RIGHT JOIN profile_AUD p ON p.id=u.profile_id RIGHT JOIN person_AUD o ON o.id=p.person_id WHERE a.timestamp < 1555324525347 AND a.timestamp > 1544873725347 AND u.REVTYPE=2; {code} and hangs in MariaDB. Also explain-s are different where on MariaDB is: {code:java} MariaDB [aris]> explain SELECT u.username, u.created, o.name, o.surname, a.modifiedBy, a.timestamp FROM aris_revision a RIGHT JOIN user_arnes_AUD u ON u.REV=a.id RIGHT JOIN profile_AUD p ON p.id=u.profile_id RIGHT JOIN person_AUD o ON o.id=p.person_id WHERE a.timestamp < 1555324525347 AND a.timestamp > 1544873725347 AND u.REVTYPE=2; +------+-------------+-------+--------+---------------+---------+---------+------------------+--------+-------------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+-------------+-------+--------+---------------+---------+---------+------------------+--------+-------------------------------------------------+ | 1 | SIMPLE | p | ALL | PRIMARY | NULL | NULL | NULL | 291000 | Using where | | 1 | SIMPLE | o | ref | PRIMARY | PRIMARY | 4 | aris.p.person_id | 1 | | | 1 | SIMPLE | u | ALL | NULL | NULL | NULL | NULL | 538480 | Using where; Using join buffer (flat, BNL join) | | 1 | SIMPLE | a | eq_ref | PRIMARY | PRIMARY | 4 | aris.u.REV | 1 | Using where | +------+-------------+-------+--------+---------------+---------+---------+------------------+--------+-------------------------------------------------+ {code} while in Mysql is: {code:sql} MySQL [aris]> explain SELECT u.username, u.created, o.name, o.surname, a.modifiedBy, a.timestamp FROM aris_revision a RIGHT JOIN user_arnes_AUD u ON u.REV=a.id RIGHT JOIN profile_AUD p ON p.id=u.profile_id RIGHT JOIN person_AUD o ON o.id=p.person_id WHERE a.timestamp < 1555324525347 AND a.timestamp > 1544873725347 AND u.REVTYPE=2; +----+-------------+-------+--------+---------------+---------+---------+-------------------+--------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+--------+---------------+---------+---------+-------------------+--------+-------------+ | 1 | SIMPLE | u | ALL | NULL | NULL | NULL | NULL | 591451 | Using where | | 1 | SIMPLE | p | ref | PRIMARY | PRIMARY | 4 | aris.u.profile_id | 1 | Using where | | 1 | SIMPLE | o | ref | PRIMARY | PRIMARY | 4 | aris.p.person_id | 1 | | | 1 | SIMPLE | a | eq_ref | PRIMARY | PRIMARY | 4 | aris.u.REV | 1 | Using where | +----+-------------+-------+--------+---------------+---------+---------+-------------------+--------+-------------+ {code} ---- However if we change that RIGHT JOIN statement to: {code:sql} MariaDB [aris]> explain SELECT u.username, u.created, o.name, o.surname, a.modifiedBy, a.timestamp FROM aris_revision a, user_arnes_AUD u, profile_AUD p, person_AUD o WHERE u.REV=a.id AND p.id=u.profile_id AND o.id=p.person_id AND a.timestamp < 1555324525347 AND a.timestamp > 1544873725347 AND u.REVTYPE=2; +------+-------------+-------+--------+---------------+---------+---------+-------------------+--------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+-------------+-------+--------+---------------+---------+---------+-------------------+--------+-------------+ | 1 | SIMPLE | u | ALL | NULL | NULL | NULL | NULL | 538480 | Using where | | 1 | SIMPLE | p | ref | PRIMARY | PRIMARY | 4 | aris.u.profile_id | 1 | Using where | | 1 | SIMPLE | o | ref | PRIMARY | PRIMARY | 4 | aris.p.person_id | 1 | | | 1 | SIMPLE | a | eq_ref | PRIMARY | PRIMARY | 4 | aris.u.REV | 1 | Using where | +------+-------------+-------+--------+---------------+---------+---------+-------------------+--------+-------------+ {code} it works like it should. We found out that INNER JOIN, LEFT JOIN works, but RIGHT JOIN hangs. Any ideas why? |
We are migrating (dump, restore) data from Mysql version 5.5.61-38.13-log, however we found a problem when RIGHT JOIN statement hangs, but inner join does not.
This one works in Mysql: {code:sql} SELECT u.username, u.created, o.name, o.surname, a.modifiedBy, a.timestamp FROM aris_revision a RIGHT JOIN user_AUD u ON u.REV=a.id RIGHT JOIN profile_AUD p ON p.id=u.profile_id RIGHT JOIN person_AUD o ON o.id=p.person_id WHERE a.timestamp < 1555324525347 AND a.timestamp > 1544873725347 AND u.REVTYPE=2; {code} and hangs in MariaDB. Also explain-s are different where on MariaDB is: {code:java} MariaDB [aris]> explain SELECT u.username, u.created, o.name, o.surname, a.modifiedBy, a.timestamp FROM aris_revision a RIGHT JOIN user_arnes_AUD u ON u.REV=a.id RIGHT JOIN profile_AUD p ON p.id=u.profile_id RIGHT JOIN person_AUD o ON o.id=p.person_id WHERE a.timestamp < 1555324525347 AND a.timestamp > 1544873725347 AND u.REVTYPE=2; +------+-------------+-------+--------+---------------+---------+---------+------------------+--------+-------------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+-------------+-------+--------+---------------+---------+---------+------------------+--------+-------------------------------------------------+ | 1 | SIMPLE | p | ALL | PRIMARY | NULL | NULL | NULL | 291000 | Using where | | 1 | SIMPLE | o | ref | PRIMARY | PRIMARY | 4 | aris.p.person_id | 1 | | | 1 | SIMPLE | u | ALL | NULL | NULL | NULL | NULL | 538480 | Using where; Using join buffer (flat, BNL join) | | 1 | SIMPLE | a | eq_ref | PRIMARY | PRIMARY | 4 | aris.u.REV | 1 | Using where | +------+-------------+-------+--------+---------------+---------+---------+------------------+--------+-------------------------------------------------+ {code} while in Mysql is: {code:sql} MySQL [aris]> explain SELECT u.username, u.created, o.name, o.surname, a.modifiedBy, a.timestamp FROM aris_revision a RIGHT JOIN user_arnes_AUD u ON u.REV=a.id RIGHT JOIN profile_AUD p ON p.id=u.profile_id RIGHT JOIN person_AUD o ON o.id=p.person_id WHERE a.timestamp < 1555324525347 AND a.timestamp > 1544873725347 AND u.REVTYPE=2; +----+-------------+-------+--------+---------------+---------+---------+-------------------+--------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+--------+---------------+---------+---------+-------------------+--------+-------------+ | 1 | SIMPLE | u | ALL | NULL | NULL | NULL | NULL | 591451 | Using where | | 1 | SIMPLE | p | ref | PRIMARY | PRIMARY | 4 | aris.u.profile_id | 1 | Using where | | 1 | SIMPLE | o | ref | PRIMARY | PRIMARY | 4 | aris.p.person_id | 1 | | | 1 | SIMPLE | a | eq_ref | PRIMARY | PRIMARY | 4 | aris.u.REV | 1 | Using where | +----+-------------+-------+--------+---------------+---------+---------+-------------------+--------+-------------+ {code} ---- However if we change that RIGHT JOIN statement to: {code:sql} MariaDB [aris]> explain SELECT u.username, u.created, o.name, o.surname, a.modifiedBy, a.timestamp FROM aris_revision a, user_arnes_AUD u, profile_AUD p, person_AUD o WHERE u.REV=a.id AND p.id=u.profile_id AND o.id=p.person_id AND a.timestamp < 1555324525347 AND a.timestamp > 1544873725347 AND u.REVTYPE=2; +------+-------------+-------+--------+---------------+---------+---------+-------------------+--------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+-------------+-------+--------+---------------+---------+---------+-------------------+--------+-------------+ | 1 | SIMPLE | u | ALL | NULL | NULL | NULL | NULL | 538480 | Using where | | 1 | SIMPLE | p | ref | PRIMARY | PRIMARY | 4 | aris.u.profile_id | 1 | Using where | | 1 | SIMPLE | o | ref | PRIMARY | PRIMARY | 4 | aris.p.person_id | 1 | | | 1 | SIMPLE | a | eq_ref | PRIMARY | PRIMARY | 4 | aris.u.REV | 1 | Using where | +------+-------------+-------+--------+---------------+---------+---------+-------------------+--------+-------------+ {code} it works like it should. We found out that INNER JOIN, LEFT JOIN works, but RIGHT JOIN hangs. Any ideas why? |
| Description |
We are migrating (dump, restore) data from Mysql version 5.5.61-38.13-log, however we found a problem when RIGHT JOIN statement hangs, but inner join does not.
This one works in Mysql: {code:sql} SELECT u.username, u.created, o.name, o.surname, a.modifiedBy, a.timestamp FROM aris_revision a RIGHT JOIN user_AUD u ON u.REV=a.id RIGHT JOIN profile_AUD p ON p.id=u.profile_id RIGHT JOIN person_AUD o ON o.id=p.person_id WHERE a.timestamp < 1555324525347 AND a.timestamp > 1544873725347 AND u.REVTYPE=2; {code} and hangs in MariaDB. Also explain-s are different where on MariaDB is: {code:java} MariaDB [aris]> explain SELECT u.username, u.created, o.name, o.surname, a.modifiedBy, a.timestamp FROM aris_revision a RIGHT JOIN user_arnes_AUD u ON u.REV=a.id RIGHT JOIN profile_AUD p ON p.id=u.profile_id RIGHT JOIN person_AUD o ON o.id=p.person_id WHERE a.timestamp < 1555324525347 AND a.timestamp > 1544873725347 AND u.REVTYPE=2; +------+-------------+-------+--------+---------------+---------+---------+------------------+--------+-------------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+-------------+-------+--------+---------------+---------+---------+------------------+--------+-------------------------------------------------+ | 1 | SIMPLE | p | ALL | PRIMARY | NULL | NULL | NULL | 291000 | Using where | | 1 | SIMPLE | o | ref | PRIMARY | PRIMARY | 4 | aris.p.person_id | 1 | | | 1 | SIMPLE | u | ALL | NULL | NULL | NULL | NULL | 538480 | Using where; Using join buffer (flat, BNL join) | | 1 | SIMPLE | a | eq_ref | PRIMARY | PRIMARY | 4 | aris.u.REV | 1 | Using where | +------+-------------+-------+--------+---------------+---------+---------+------------------+--------+-------------------------------------------------+ {code} while in Mysql is: {code:sql} MySQL [aris]> explain SELECT u.username, u.created, o.name, o.surname, a.modifiedBy, a.timestamp FROM aris_revision a RIGHT JOIN user_arnes_AUD u ON u.REV=a.id RIGHT JOIN profile_AUD p ON p.id=u.profile_id RIGHT JOIN person_AUD o ON o.id=p.person_id WHERE a.timestamp < 1555324525347 AND a.timestamp > 1544873725347 AND u.REVTYPE=2; +----+-------------+-------+--------+---------------+---------+---------+-------------------+--------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+--------+---------------+---------+---------+-------------------+--------+-------------+ | 1 | SIMPLE | u | ALL | NULL | NULL | NULL | NULL | 591451 | Using where | | 1 | SIMPLE | p | ref | PRIMARY | PRIMARY | 4 | aris.u.profile_id | 1 | Using where | | 1 | SIMPLE | o | ref | PRIMARY | PRIMARY | 4 | aris.p.person_id | 1 | | | 1 | SIMPLE | a | eq_ref | PRIMARY | PRIMARY | 4 | aris.u.REV | 1 | Using where | +----+-------------+-------+--------+---------------+---------+---------+-------------------+--------+-------------+ {code} ---- However if we change that RIGHT JOIN statement to: {code:sql} MariaDB [aris]> explain SELECT u.username, u.created, o.name, o.surname, a.modifiedBy, a.timestamp FROM aris_revision a, user_arnes_AUD u, profile_AUD p, person_AUD o WHERE u.REV=a.id AND p.id=u.profile_id AND o.id=p.person_id AND a.timestamp < 1555324525347 AND a.timestamp > 1544873725347 AND u.REVTYPE=2; +------+-------------+-------+--------+---------------+---------+---------+-------------------+--------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+-------------+-------+--------+---------------+---------+---------+-------------------+--------+-------------+ | 1 | SIMPLE | u | ALL | NULL | NULL | NULL | NULL | 538480 | Using where | | 1 | SIMPLE | p | ref | PRIMARY | PRIMARY | 4 | aris.u.profile_id | 1 | Using where | | 1 | SIMPLE | o | ref | PRIMARY | PRIMARY | 4 | aris.p.person_id | 1 | | | 1 | SIMPLE | a | eq_ref | PRIMARY | PRIMARY | 4 | aris.u.REV | 1 | Using where | +------+-------------+-------+--------+---------------+---------+---------+-------------------+--------+-------------+ {code} it works like it should. We found out that INNER JOIN, LEFT JOIN works, but RIGHT JOIN hangs. Any ideas why? |
We are migrating (dump, restore) data from Mysql version 5.5.61-38.13-log, however we found a problem when RIGHT JOIN statement hangs, but inner join does not.
This one works in Mysql: {code:sql} SELECT u.username, u.created, o.name, o.surname, a.modifiedBy, a.timestamp FROM aris_revision a RIGHT JOIN user_AUD u ON u.REV=a.id RIGHT JOIN profile_AUD p ON p.id=u.profile_id RIGHT JOIN person_AUD o ON o.id=p.person_id WHERE a.timestamp < 1555324525347 AND a.timestamp > 1544873725347 AND u.REVTYPE=2; {code} and hangs in MariaDB. Also explain-s are different where on MariaDB is: {code:java} MariaDB [aris]> explain SELECT u.username, u.created, o.name, o.surname, a.modifiedBy, a.timestamp FROM aris_revision a RIGHT JOIN user_AUD u ON u.REV=a.id RIGHT JOIN profile_AUD p ON p.id=u.profile_id RIGHT JOIN person_AUD o ON o.id=p.person_id WHERE a.timestamp < 1555324525347 AND a.timestamp > 1544873725347 AND u.REVTYPE=2; +------+-------------+-------+--------+---------------+---------+---------+------------------+--------+-------------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+-------------+-------+--------+---------------+---------+---------+------------------+--------+-------------------------------------------------+ | 1 | SIMPLE | p | ALL | PRIMARY | NULL | NULL | NULL | 291000 | Using where | | 1 | SIMPLE | o | ref | PRIMARY | PRIMARY | 4 | aris.p.person_id | 1 | | | 1 | SIMPLE | u | ALL | NULL | NULL | NULL | NULL | 538480 | Using where; Using join buffer (flat, BNL join) | | 1 | SIMPLE | a | eq_ref | PRIMARY | PRIMARY | 4 | aris.u.REV | 1 | Using where | +------+-------------+-------+--------+---------------+---------+---------+------------------+--------+-------------------------------------------------+ {code} while in Mysql is: {code:sql} MySQL [aris]> explain SELECT u.username, u.created, o.name, o.surname, a.modifiedBy, a.timestamp FROM aris_revision a RIGHT JOIN user_arnes_AUD u ON u.REV=a.id RIGHT JOIN profile_AUD p ON p.id=u.profile_id RIGHT JOIN person_AUD o ON o.id=p.person_id WHERE a.timestamp < 1555324525347 AND a.timestamp > 1544873725347 AND u.REVTYPE=2; +----+-------------+-------+--------+---------------+---------+---------+-------------------+--------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+--------+---------------+---------+---------+-------------------+--------+-------------+ | 1 | SIMPLE | u | ALL | NULL | NULL | NULL | NULL | 591451 | Using where | | 1 | SIMPLE | p | ref | PRIMARY | PRIMARY | 4 | aris.u.profile_id | 1 | Using where | | 1 | SIMPLE | o | ref | PRIMARY | PRIMARY | 4 | aris.p.person_id | 1 | | | 1 | SIMPLE | a | eq_ref | PRIMARY | PRIMARY | 4 | aris.u.REV | 1 | Using where | +----+-------------+-------+--------+---------------+---------+---------+-------------------+--------+-------------+ {code} ---- However if we change that RIGHT JOIN statement to: {code:sql} MariaDB [aris]> explain SELECT u.username, u.created, o.name, o.surname, a.modifiedBy, a.timestamp FROM aris_revision a, user_arnes_AUD u, profile_AUD p, person_AUD o WHERE u.REV=a.id AND p.id=u.profile_id AND o.id=p.person_id AND a.timestamp < 1555324525347 AND a.timestamp > 1544873725347 AND u.REVTYPE=2; +------+-------------+-------+--------+---------------+---------+---------+-------------------+--------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+-------------+-------+--------+---------------+---------+---------+-------------------+--------+-------------+ | 1 | SIMPLE | u | ALL | NULL | NULL | NULL | NULL | 538480 | Using where | | 1 | SIMPLE | p | ref | PRIMARY | PRIMARY | 4 | aris.u.profile_id | 1 | Using where | | 1 | SIMPLE | o | ref | PRIMARY | PRIMARY | 4 | aris.p.person_id | 1 | | | 1 | SIMPLE | a | eq_ref | PRIMARY | PRIMARY | 4 | aris.u.REV | 1 | Using where | +------+-------------+-------+--------+---------------+---------+---------+-------------------+--------+-------------+ {code} it works like it should. We found out that INNER JOIN, LEFT JOIN works, but RIGHT JOIN hangs. Any ideas why? |
| Description |
We are migrating (dump, restore) data from Mysql version 5.5.61-38.13-log, however we found a problem when RIGHT JOIN statement hangs, but inner join does not.
This one works in Mysql: {code:sql} SELECT u.username, u.created, o.name, o.surname, a.modifiedBy, a.timestamp FROM aris_revision a RIGHT JOIN user_AUD u ON u.REV=a.id RIGHT JOIN profile_AUD p ON p.id=u.profile_id RIGHT JOIN person_AUD o ON o.id=p.person_id WHERE a.timestamp < 1555324525347 AND a.timestamp > 1544873725347 AND u.REVTYPE=2; {code} and hangs in MariaDB. Also explain-s are different where on MariaDB is: {code:java} MariaDB [aris]> explain SELECT u.username, u.created, o.name, o.surname, a.modifiedBy, a.timestamp FROM aris_revision a RIGHT JOIN user_AUD u ON u.REV=a.id RIGHT JOIN profile_AUD p ON p.id=u.profile_id RIGHT JOIN person_AUD o ON o.id=p.person_id WHERE a.timestamp < 1555324525347 AND a.timestamp > 1544873725347 AND u.REVTYPE=2; +------+-------------+-------+--------+---------------+---------+---------+------------------+--------+-------------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+-------------+-------+--------+---------------+---------+---------+------------------+--------+-------------------------------------------------+ | 1 | SIMPLE | p | ALL | PRIMARY | NULL | NULL | NULL | 291000 | Using where | | 1 | SIMPLE | o | ref | PRIMARY | PRIMARY | 4 | aris.p.person_id | 1 | | | 1 | SIMPLE | u | ALL | NULL | NULL | NULL | NULL | 538480 | Using where; Using join buffer (flat, BNL join) | | 1 | SIMPLE | a | eq_ref | PRIMARY | PRIMARY | 4 | aris.u.REV | 1 | Using where | +------+-------------+-------+--------+---------------+---------+---------+------------------+--------+-------------------------------------------------+ {code} while in Mysql is: {code:sql} MySQL [aris]> explain SELECT u.username, u.created, o.name, o.surname, a.modifiedBy, a.timestamp FROM aris_revision a RIGHT JOIN user_arnes_AUD u ON u.REV=a.id RIGHT JOIN profile_AUD p ON p.id=u.profile_id RIGHT JOIN person_AUD o ON o.id=p.person_id WHERE a.timestamp < 1555324525347 AND a.timestamp > 1544873725347 AND u.REVTYPE=2; +----+-------------+-------+--------+---------------+---------+---------+-------------------+--------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+--------+---------------+---------+---------+-------------------+--------+-------------+ | 1 | SIMPLE | u | ALL | NULL | NULL | NULL | NULL | 591451 | Using where | | 1 | SIMPLE | p | ref | PRIMARY | PRIMARY | 4 | aris.u.profile_id | 1 | Using where | | 1 | SIMPLE | o | ref | PRIMARY | PRIMARY | 4 | aris.p.person_id | 1 | | | 1 | SIMPLE | a | eq_ref | PRIMARY | PRIMARY | 4 | aris.u.REV | 1 | Using where | +----+-------------+-------+--------+---------------+---------+---------+-------------------+--------+-------------+ {code} ---- However if we change that RIGHT JOIN statement to: {code:sql} MariaDB [aris]> explain SELECT u.username, u.created, o.name, o.surname, a.modifiedBy, a.timestamp FROM aris_revision a, user_arnes_AUD u, profile_AUD p, person_AUD o WHERE u.REV=a.id AND p.id=u.profile_id AND o.id=p.person_id AND a.timestamp < 1555324525347 AND a.timestamp > 1544873725347 AND u.REVTYPE=2; +------+-------------+-------+--------+---------------+---------+---------+-------------------+--------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+-------------+-------+--------+---------------+---------+---------+-------------------+--------+-------------+ | 1 | SIMPLE | u | ALL | NULL | NULL | NULL | NULL | 538480 | Using where | | 1 | SIMPLE | p | ref | PRIMARY | PRIMARY | 4 | aris.u.profile_id | 1 | Using where | | 1 | SIMPLE | o | ref | PRIMARY | PRIMARY | 4 | aris.p.person_id | 1 | | | 1 | SIMPLE | a | eq_ref | PRIMARY | PRIMARY | 4 | aris.u.REV | 1 | Using where | +------+-------------+-------+--------+---------------+---------+---------+-------------------+--------+-------------+ {code} it works like it should. We found out that INNER JOIN, LEFT JOIN works, but RIGHT JOIN hangs. Any ideas why? |
We are migrating (dump, restore) data from Mysql version 5.5.61-38.13-log, however we found a problem when RIGHT JOIN statement hangs, but inner join does not.
This one works in Mysql: {code:sql} SELECT u.username, u.created, o.name, o.surname, a.modifiedBy, a.timestamp FROM aris_revision a RIGHT JOIN user_AUD u ON u.REV=a.id RIGHT JOIN profile_AUD p ON p.id=u.profile_id RIGHT JOIN person_AUD o ON o.id=p.person_id WHERE a.timestamp < 1555324525347 AND a.timestamp > 1544873725347 AND u.REVTYPE=2; {code} and hangs in MariaDB. Also explain-s are different where on MariaDB is: {code:java} MariaDB [aris]> explain SELECT u.username, u.created, o.name, o.surname, a.modifiedBy, a.timestamp FROM aris_revision a RIGHT JOIN user_AUD u ON u.REV=a.id RIGHT JOIN profile_AUD p ON p.id=u.profile_id RIGHT JOIN person_AUD o ON o.id=p.person_id WHERE a.timestamp < 1555324525347 AND a.timestamp > 1544873725347 AND u.REVTYPE=2; +------+-------------+-------+--------+---------------+---------+---------+------------------+--------+-------------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+-------------+-------+--------+---------------+---------+---------+------------------+--------+-------------------------------------------------+ | 1 | SIMPLE | p | ALL | PRIMARY | NULL | NULL | NULL | 291000 | Using where | | 1 | SIMPLE | o | ref | PRIMARY | PRIMARY | 4 | aris.p.person_id | 1 | | | 1 | SIMPLE | u | ALL | NULL | NULL | NULL | NULL | 538480 | Using where; Using join buffer (flat, BNL join) | | 1 | SIMPLE | a | eq_ref | PRIMARY | PRIMARY | 4 | aris.u.REV | 1 | Using where | +------+-------------+-------+--------+---------------+---------+---------+------------------+--------+-------------------------------------------------+ {code} while in Mysql is: {code:sql} MySQL [aris]> explain SELECT u.username, u.created, o.name, o.surname, a.modifiedBy, a.timestamp FROM aris_revision a RIGHT JOIN user_AUD u ON u.REV=a.id RIGHT JOIN profile_AUD p ON p.id=u.profile_id RIGHT JOIN person_AUD o ON o.id=p.person_id WHERE a.timestamp < 1555324525347 AND a.timestamp > 1544873725347 AND u.REVTYPE=2; +----+-------------+-------+--------+---------------+---------+---------+-------------------+--------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+--------+---------------+---------+---------+-------------------+--------+-------------+ | 1 | SIMPLE | u | ALL | NULL | NULL | NULL | NULL | 591451 | Using where | | 1 | SIMPLE | p | ref | PRIMARY | PRIMARY | 4 | aris.u.profile_id | 1 | Using where | | 1 | SIMPLE | o | ref | PRIMARY | PRIMARY | 4 | aris.p.person_id | 1 | | | 1 | SIMPLE | a | eq_ref | PRIMARY | PRIMARY | 4 | aris.u.REV | 1 | Using where | +----+-------------+-------+--------+---------------+---------+---------+-------------------+--------+-------------+ {code} ---- However if we change that RIGHT JOIN statement to: {code:sql} MariaDB [aris]> explain SELECT u.username, u.created, o.name, o.surname, a.modifiedBy, a.timestamp FROM aris_revision a, user_AUD u, profile_AUD p, person_AUD o WHERE u.REV=a.id AND p.id=u.profile_id AND o.id=p.person_id AND a.timestamp < 1555324525347 AND a.timestamp > 1544873725347 AND u.REVTYPE=2; +------+-------------+-------+--------+---------------+---------+---------+-------------------+--------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+-------------+-------+--------+---------------+---------+---------+-------------------+--------+-------------+ | 1 | SIMPLE | u | ALL | NULL | NULL | NULL | NULL | 538480 | Using where | | 1 | SIMPLE | p | ref | PRIMARY | PRIMARY | 4 | aris.u.profile_id | 1 | Using where | | 1 | SIMPLE | o | ref | PRIMARY | PRIMARY | 4 | aris.p.person_id | 1 | | | 1 | SIMPLE | a | eq_ref | PRIMARY | PRIMARY | 4 | aris.u.REV | 1 | Using where | +------+-------------+-------+--------+---------------+---------+---------+-------------------+--------+-------------+ {code} it works like it should. We found out that INNER JOIN, LEFT JOIN works, but RIGHT JOIN hangs. Any ideas why? |
| Attachment | create.sql [ 47956 ] |
| Attachment | create.sql [ 47957 ] |
| Description |
We are migrating (dump, restore) data from Mysql version 5.5.61-38.13-log, however we found a problem when RIGHT JOIN statement hangs, but inner join does not.
This one works in Mysql: {code:sql} SELECT u.username, u.created, o.name, o.surname, a.modifiedBy, a.timestamp FROM aris_revision a RIGHT JOIN user_AUD u ON u.REV=a.id RIGHT JOIN profile_AUD p ON p.id=u.profile_id RIGHT JOIN person_AUD o ON o.id=p.person_id WHERE a.timestamp < 1555324525347 AND a.timestamp > 1544873725347 AND u.REVTYPE=2; {code} and hangs in MariaDB. Also explain-s are different where on MariaDB is: {code:java} MariaDB [aris]> explain SELECT u.username, u.created, o.name, o.surname, a.modifiedBy, a.timestamp FROM aris_revision a RIGHT JOIN user_AUD u ON u.REV=a.id RIGHT JOIN profile_AUD p ON p.id=u.profile_id RIGHT JOIN person_AUD o ON o.id=p.person_id WHERE a.timestamp < 1555324525347 AND a.timestamp > 1544873725347 AND u.REVTYPE=2; +------+-------------+-------+--------+---------------+---------+---------+------------------+--------+-------------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+-------------+-------+--------+---------------+---------+---------+------------------+--------+-------------------------------------------------+ | 1 | SIMPLE | p | ALL | PRIMARY | NULL | NULL | NULL | 291000 | Using where | | 1 | SIMPLE | o | ref | PRIMARY | PRIMARY | 4 | aris.p.person_id | 1 | | | 1 | SIMPLE | u | ALL | NULL | NULL | NULL | NULL | 538480 | Using where; Using join buffer (flat, BNL join) | | 1 | SIMPLE | a | eq_ref | PRIMARY | PRIMARY | 4 | aris.u.REV | 1 | Using where | +------+-------------+-------+--------+---------------+---------+---------+------------------+--------+-------------------------------------------------+ {code} while in Mysql is: {code:sql} MySQL [aris]> explain SELECT u.username, u.created, o.name, o.surname, a.modifiedBy, a.timestamp FROM aris_revision a RIGHT JOIN user_AUD u ON u.REV=a.id RIGHT JOIN profile_AUD p ON p.id=u.profile_id RIGHT JOIN person_AUD o ON o.id=p.person_id WHERE a.timestamp < 1555324525347 AND a.timestamp > 1544873725347 AND u.REVTYPE=2; +----+-------------+-------+--------+---------------+---------+---------+-------------------+--------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+--------+---------------+---------+---------+-------------------+--------+-------------+ | 1 | SIMPLE | u | ALL | NULL | NULL | NULL | NULL | 591451 | Using where | | 1 | SIMPLE | p | ref | PRIMARY | PRIMARY | 4 | aris.u.profile_id | 1 | Using where | | 1 | SIMPLE | o | ref | PRIMARY | PRIMARY | 4 | aris.p.person_id | 1 | | | 1 | SIMPLE | a | eq_ref | PRIMARY | PRIMARY | 4 | aris.u.REV | 1 | Using where | +----+-------------+-------+--------+---------------+---------+---------+-------------------+--------+-------------+ {code} ---- However if we change that RIGHT JOIN statement to: {code:sql} MariaDB [aris]> explain SELECT u.username, u.created, o.name, o.surname, a.modifiedBy, a.timestamp FROM aris_revision a, user_AUD u, profile_AUD p, person_AUD o WHERE u.REV=a.id AND p.id=u.profile_id AND o.id=p.person_id AND a.timestamp < 1555324525347 AND a.timestamp > 1544873725347 AND u.REVTYPE=2; +------+-------------+-------+--------+---------------+---------+---------+-------------------+--------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+-------------+-------+--------+---------------+---------+---------+-------------------+--------+-------------+ | 1 | SIMPLE | u | ALL | NULL | NULL | NULL | NULL | 538480 | Using where | | 1 | SIMPLE | p | ref | PRIMARY | PRIMARY | 4 | aris.u.profile_id | 1 | Using where | | 1 | SIMPLE | o | ref | PRIMARY | PRIMARY | 4 | aris.p.person_id | 1 | | | 1 | SIMPLE | a | eq_ref | PRIMARY | PRIMARY | 4 | aris.u.REV | 1 | Using where | +------+-------------+-------+--------+---------------+---------+---------+-------------------+--------+-------------+ {code} it works like it should. We found out that INNER JOIN, LEFT JOIN works, but RIGHT JOIN hangs. Any ideas why? |
We are migrating (dump, restore) data from Mysql version 5.5.61-38.13-log, however we found a problem when RIGHT JOIN statement hangs, but inner join does not.
This one works in Mysql but not in MariaDB: {code:sql} 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; {code} We found out that INNER JOIN, LEFT JOIN works, but RIGHT JOIN hangs. Any ideas why? I have included dump of a database. |
| Attachment | create.sql [ 47957 ] |
| Description |
We are migrating (dump, restore) data from Mysql version 5.5.61-38.13-log, however we found a problem when RIGHT JOIN statement hangs, but inner join does not.
This one works in Mysql but not in MariaDB: {code:sql} 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; {code} We found out that INNER JOIN, LEFT JOIN works, but RIGHT JOIN hangs. Any ideas why? I have included dump of a database. |
We are migrating (dump, restore) data from Mysql version 5.5.61-38.13-log, however we found a problem when RIGHT JOIN statement hangs, but inner join does not.
This one works in Mysql but not in MariaDB: {code:sql} 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; {code} 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 |
| Description |
We are migrating (dump, restore) data from Mysql version 5.5.61-38.13-log, however we found a problem when RIGHT JOIN statement hangs, but inner join does not.
This one works in Mysql but not in MariaDB: {code:sql} 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; {code} 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 |
We are migrating (dump, restore) data from Mysql version 5.5.61-38.13-log to MariaDB, however we found a problem when RIGHT JOIN statement hangs, but inner join does not.
This one works in Mysql but not in MariaDB: {code:sql} 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; {code} 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 |
| Description |
We are migrating (dump, restore) data from Mysql version 5.5.61-38.13-log to MariaDB, however we found a problem when RIGHT JOIN statement hangs, but inner join does not.
This one works in Mysql but not in MariaDB: {code:sql} 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; {code} 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 |
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: {code:sql} 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; {code} 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 |
| Assignee | Alice Sherepa [ alice ] |
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)
|
| Priority | Minor [ 4 ] | Major [ 3 ] |
| Affects Version/s | 5.5 [ 15800 ] | |
| Affects Version/s | 10.0 [ 16000 ] | |
| Affects Version/s | 10.1 [ 16100 ] | |
| Affects Version/s | 10.2 [ 14601 ] | |
| Affects Version/s | 10.3 [ 22126 ] | |
| Affects Version/s | 10.4 [ 22408 ] |
| Fix Version/s | 10.1 [ 16100 ] |
| Status | Open [ 1 ] | Confirmed [ 10101 ] |
| Assignee | Alice Sherepa [ alice ] | Igor Babaev [ igor ] |
| Status | Confirmed [ 10101 ] | In Progress [ 3 ] |
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)
|
| Assignee | Igor Babaev [ igor ] | Sergei Petrunia [ psergey ] |
| Status | In Progress [ 3 ] | In Review [ 10002 ] |
| Assignee | Sergei Petrunia [ psergey ] | Igor Babaev [ igor ] |
| Status | In Review [ 10002 ] | Stalled [ 10000 ] |
| Fix Version/s | 10.1.41 [ 23406 ] | |
| Fix Version/s | 10.1 [ 16100 ] | |
| Resolution | Fixed [ 1 ] | |
| Status | Stalled [ 10000 ] | Closed [ 6 ] |
| Workflow | MariaDB v3 [ 95399 ] | MariaDB v4 [ 156077 ] |
However we found out, if we create indexes like:
... then query does not "hang".