Igor Babaev (Inactive) added a comment - 2022-01-12 19:06 The output of EXPLAIN FORMAT=JSON for the last query shows the the condition reporting_person = 1 has been lost: MariaDB [test]> EXPLAIN FORMAT=JSON -> SELECT t1.id -> FROM t1 -> JOIN t3 ON t3.t1_id = t1.id -> JOIN (SELECT t1_id FROM t2 WHERE reporting_person = 1 GROUP BY t1_id) tx -> ON tx.t1_id = t1.id -> WHERE t1.id in(1, 2070); +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | EXPLAIN | +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | { "query_block": { "select_id": 1, "table": { "table_name": "t3", "access_type": "index", "possible_keys": ["t1_id"], "key": "t1_id", "key_length": "15", "used_key_parts": ["t1_id", "YEAR", "quarter"], "rows": 1, "filtered": 100, "attached_condition": "t3.t1_id in (1,2070) and t3.t1_id is not null and t3.t1_id is not null", "using_index": true }, "table": { "table_name": "t1", "access_type": "eq_ref", "possible_keys": ["id"], "key": "id", "key_length": "5", "used_key_parts": ["id"], "ref": ["test.t3.t1_id"], "rows": 1, "filtered": 100, "using_index": true }, "table": { "table_name": "<derived2>", "access_type": "ref", "possible_keys": ["key0"], "key": "key0", "key_length": "5", "used_key_parts": ["t1_id"], "ref": ["test.t3.t1_id"], "rows": 2, "filtered": 100, "materialized": { "lateral": 1, "query_block": { "select_id": 2, "outer_ref_condition": "t1.`id` is not null", "table": { "table_name": "t2", "access_type": "ref", "possible_keys": ["t1_id"], "key": "t1_id", "key_length": "5", "used_key_parts": ["t1_id"], "ref": ["test.t1.id"], "rows": 1, "filtered": 100, "index_condition": "t2.t1_id in (1,2070)" } } } } } } | +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.001 sec)

Igor Babaev (Inactive) added a comment - 2022-01-13 06:50 alice , Please create a new MDEV for the bug of mhadji@gmail.com . The cause of his problem is quite different.

Oleksandr Byelkin added a comment - 2022-01-14 18:14 OK to push.

Igor Babaev (Inactive) added a comment - 2022-01-18 20:49 If we look at the output of EXPLAIN FORMAT=JSON for the first reported test we see that the condition ptld.date_revision_id = dr.`id` has been lost: MariaDB [test]> EXPLAIN FORMAT=JSON -> SELECT COUNT(*) -> FROM project_time_lines_dates ptld -> -> INNER JOIN date_revision dr -> ON dr.id = ptld.date_revision_id -> -> INNER JOIN ( -> SELECT -> ptld.project_id AS project_id, -> ptld.project_type_id AS project_type_id, -> ptld.leaf_component_id AS leaf_component_id, -> dr.date_id AS date_id, -> MAX(dr.order_idx) AS max_order -> FROM project_time_lines_dates ptld -> INNER JOIN date_revision dr -> ON dr.id = ptld.date_revision_id -> WHERE dr.date_id IN ( -> SELECT DISTINCT dr.date_id -> FROM time_sheet_activities_time_sheet_roles_date_revisions tstrdr -> INNER JOIN date_revision dr -> ON dr.id = tstrdr.start_date_revision_id -> WHERE tstrdr.offered_for_overdue -> ) -> -> GROUP BY -> ptld.project_id, -> ptld.project_type_id, -> ptld.leaf_component_id, -> dr.date_id -> ) max_anticipated -> ON max_anticipated.project_id = ptld.project_id -> AND max_anticipated.project_type_id = ptld.project_type_id -> AND max_anticipated.date_id = dr.date_id -> AND max_anticipated.max_order = dr.order_idx -> -> WHERE dr.is_anticipated = TRUE -> AND ptld.project_id = 5896 -> ; ERROR 2006 (HY000): MySQL server has gone away No connection. Trying to reconnect... Connection id: 8 Current database: test   +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | EXPLAIN | +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | { "query_block": { "select_id": 1, "table": { "table_name": "ptld", "access_type": "ref", "possible_keys": [ "project_id_project_type_id_leaf_component_id_date_revision_id", "project_id", "project_id_2" ], "key": "project_id_project_type_id_leaf_component_id_date_revision_id", "key_length": "4", "used_key_parts": ["project_id"], "ref": ["const"], "rows": 34, "filtered": 100, "using_index": true }, "table": { "table_name": "dr", "access_type": "eq_ref", "possible_keys": ["PRIMARY", "date_id"], "key": "PRIMARY", "key_length": "4", "used_key_parts": ["id"], "ref": ["test.ptld.date_revision_id"], "rows": 1, "filtered": 100, "attached_condition": "dr.is_anticipated = 1" }, "table": { "table_name": "<derived2>", "access_type": "ref", "possible_keys": ["key0"], "key": "key0", "key_length": "4", "used_key_parts": ["project_type_id"], "ref": ["test.ptld.project_type_id"], "rows": 2, "filtered": 100, "attached_condition": "max_anticipated.project_id = 5896 and max_anticipated.date_id = dr.date_id and max_anticipated.max_order = dr.order_idx", "materialized": { "lateral": 1, "query_block": { "select_id": 2, "filesort": { "sort_key": "ptld.leaf_component_id", "temporary_table": { "table": { "table_name": "dr", "access_type": "ref", "possible_keys": ["PRIMARY", "date_id"], "key": "date_id", "key_length": "4", "used_key_parts": ["date_id"], "ref": ["test.dr.date_id"], "rows": 1, "filtered": 100 }, "duplicates_removal": { "table": { "table_name": "dr", "access_type": "ref", "possible_keys": ["PRIMARY", "date_id"], "key": "date_id", "key_length": "4", "used_key_parts": ["date_id"], "ref": ["test.dr.date_id"], "rows": 1, "filtered": 100, "using_index": true }, "table": { "table_name": "tstrdr", "access_type": "ref", "possible_keys": ["start_date_revision_id"], "key": "start_date_revision_id", "key_length": "5", "used_key_parts": ["start_date_revision_id"], "ref": ["test.dr.id"], "rows": 4, "filtered": 100, "attached_condition": "tstrdr.offered_for_overdue <> 0" } }, "table": { "table_name": "ptld", "access_type": "ref", "possible_keys": [ "project_id_project_type_id_leaf_component_id_date_revision_id", "project_id", "project_id_2" ], "key": "project_id_project_type_id_leaf_component_id_date_revision_id", "key_length": "4", "used_key_parts": ["project_id"], "ref": ["const"], "rows": 34, "filtered": 100, "using_index": true } } } } } } } } | +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ After the fix we have: MariaDB [test]> EXPLAIN FORMAT=JSON -> SELECT COUNT(*) -> FROM project_time_lines_dates ptld -> -> INNER JOIN date_revision dr -> ON dr.id = ptld.date_revision_id -> -> INNER JOIN ( -> SELECT -> ptld.project_id AS project_id, -> ptld.project_type_id AS project_type_id, -> ptld.leaf_component_id AS leaf_component_id, -> dr.date_id AS date_id, -> MAX(dr.order_idx) AS max_order -> FROM project_time_lines_dates ptld -> INNER JOIN date_revision dr -> ON dr.id = ptld.date_revision_id -> WHERE dr.date_id IN ( -> SELECT DISTINCT dr.date_id -> FROM time_sheet_activities_time_sheet_roles_date_revisions tstrdr -> INNER JOIN date_revision dr -> ON dr.id = tstrdr.start_date_revision_id -> WHERE tstrdr.offered_for_overdue -> ) -> -> GROUP BY -> ptld.project_id, -> ptld.project_type_id, -> ptld.leaf_component_id, -> dr.date_id -> ) max_anticipated -> ON max_anticipated.project_id = ptld.project_id -> AND max_anticipated.project_type_id = ptld.project_type_id -> AND max_anticipated.date_id = dr.date_id -> AND max_anticipated.max_order = dr.order_idx -> -> WHERE dr.is_anticipated = TRUE -> AND ptld.project_id = 5896 -> ; +---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | EXPLAIN | +---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | { "query_block": { "select_id": 1, "table": { "table_name": "ptld", "access_type": "ref", "possible_keys": [ "project_id_project_type_id_leaf_component_id_date_revision_id", "project_id", "project_id_2" ], "key": "project_id_project_type_id_leaf_component_id_date_revision_id", "key_length": "4", "used_key_parts": ["project_id"], "ref": ["const"], "rows": 34, "filtered": 100, "using_index": true }, "table": { "table_name": "dr", "access_type": "eq_ref", "possible_keys": ["PRIMARY", "date_id"], "key": "PRIMARY", "key_length": "4", "used_key_parts": ["id"], "ref": ["test.ptld.date_revision_id"], "rows": 1, "filtered": 100, "attached_condition": "dr.is_anticipated = 1" }, "table": { "table_name": "<derived2>", "access_type": "ref", "possible_keys": ["key0"], "key": "key0", "key_length": "4", "used_key_parts": ["project_type_id"], "ref": ["test.ptld.project_type_id"], "rows": 2, "filtered": 100, "attached_condition": "max_anticipated.project_id = 5896 and max_anticipated.date_id = dr.date_id and max_anticipated.max_order = dr.order_idx", "materialized": { "lateral": 1, "query_block": { "select_id": 2, "filesort": { "sort_key": "ptld.leaf_component_id", "temporary_table": { "table": { "table_name": "dr", "access_type": "ref", "possible_keys": ["PRIMARY", "date_id"], "key": "date_id", "key_length": "4", "used_key_parts": ["date_id"], "ref": ["test.dr.date_id"], "rows": 1, "filtered": 100 }, "duplicates_removal": { "table": { "table_name": "dr", "access_type": "ref", "possible_keys": ["PRIMARY", "date_id"], "key": "date_id", "key_length": "4", "used_key_parts": ["date_id"], "ref": ["test.dr.date_id"], "rows": 1, "filtered": 100, "using_index": true }, "table": { "table_name": "tstrdr", "access_type": "ref", "possible_keys": ["start_date_revision_id"], "key": "start_date_revision_id", "key_length": "5", "used_key_parts": ["start_date_revision_id"], "ref": ["test.dr.id"], "rows": 4, "filtered": 100, "attached_condition": "tstrdr.offered_for_overdue <> 0" } }, "table": { "table_name": "ptld", "access_type": "ref", "possible_keys": [ "project_id_project_type_id_leaf_component_id_date_revision_id", "project_id", "project_id_2" ], "key": "project_id_project_type_id_leaf_component_id_date_revision_id", "key_length": "4", "used_key_parts": ["project_id"], "ref": ["const"], "rows": 34, "filtered": 100, "attached_condition": "ptld.date_revision_id = dr.`id`", "using_index": true } } } } } } } } | +---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ MariaDB [test]> SELECT COUNT(*) -> FROM project_time_lines_dates ptld -> -> INNER JOIN date_revision dr -> ON dr.id = ptld.date_revision_id -> -> INNER JOIN ( -> SELECT -> ptld.project_id AS project_id, -> ptld.project_type_id AS project_type_id, -> ptld.leaf_component_id AS leaf_component_id, -> dr.date_id AS date_id, -> MAX(dr.order_idx) AS max_order -> FROM project_time_lines_dates ptld -> INNER JOIN date_revision dr -> ON dr.id = ptld.date_revision_id -> WHERE dr.date_id IN ( -> SELECT DISTINCT dr.date_id -> FROM time_sheet_activities_time_sheet_roles_date_revisions tstrdr -> INNER JOIN date_revision dr -> ON dr.id = tstrdr.start_date_revision_id -> WHERE tstrdr.offered_for_overdue -> ) -> -> GROUP BY -> ptld.project_id, -> ptld.project_type_id, -> ptld.leaf_component_id, -> dr.date_id -> ) max_anticipated -> ON max_anticipated.project_id = ptld.project_id -> AND max_anticipated.project_type_id = ptld.project_type_id -> AND max_anticipated.date_id = dr.date_id -> AND max_anticipated.max_order = dr.order_idx -> -> WHERE dr.is_anticipated = TRUE -> AND ptld.project_id = 5896 -> ; +----------+ | COUNT(*) | +----------+ | 2 | +----------+

Igor Babaev (Inactive) added a comment - 2022-01-18 20:59 A fix for this bug was pushed into 10.3. It has to be merged upstream as it is.