Varun Gupta (Inactive) added a comment - 2018-05-22 03:46 Output for analyze format=json ANALYZE { "query_block": { "select_id": 1, "r_loops": 1, "r_total_time_ms": 6.5435, "table": { "table_name": "sl", "access_type": "index", "possible_keys": ["PRIMARY"], "key": "PRIMARY", "key_length": "4", "used_key_parts": ["id"], "r_loops": 1, "rows": 13, "r_rows": 13, "r_total_time_ms": 0.8685, "filtered": 100, "r_filtered": 53.846, "attached_condition": "<in_optimizer>(sl.`id`,sl.`id` in (subquery#2)) or sl.`id` in (0,4,12,13,1,10,3,11)", "using_index": true }, "subqueries": [ { "query_block": { "select_id": 2, "r_loops": 1, "r_total_time_ms": 5.5768, "table": { "table_name": "<subquery3>", "access_type": "ALL", "possible_keys": ["distinct_key"], "r_loops": 1, "rows": 8, "r_rows": 7, "r_total_time_ms": 0.0975, "filtered": 100, "r_filtered": 100, "materialized": { "unique": 1, "query_block": { "select_id": 3, "table": { "table_name": "B", "access_type": "ALL", "possible_keys": ["PRIMARY"], "r_loops": 1, "rows": 13, "r_rows": 13, "r_total_time_ms": 4.2749, "filtered": 61.538, "r_filtered": 53.846, "attached_condition": "b.`id` in (0,4,12,13,1,10,3,11)" } } } }, "table": { "table_name": "A", "access_type": "eq_ref", "possible_keys": ["PRIMARY"], "key": "PRIMARY", "key_length": "4", "used_key_parts": ["id"], "ref": ["func"], "r_loops": 7, "rows": 1, "r_rows": 1, "r_total_time_ms": 0.5449, "filtered": 100, "r_filtered": 14.286, "attached_condition": "a.local_name = b.local_name" } } } ] } }

Varun Gupta (Inactive) added a comment - 2018-05-22 22:03 So after discussion with psergey , we concluded that the eq_ref access on table A should not happen when we have non-semi-join materialisation. This eq_ref access is expected for the IN->EXISTS strategy for the optimisation of non-semi-join subquery. So there is somewhere an A.id = sl.id getting injected and because of that we are made to do the ref access

Varun Gupta (Inactive) added a comment - 2018-05-22 22:19 So a way by which the equality can be injected is when we do the IN -> EXIST transformation. For non-semi join subqueries we have 2 strategies Materialisation IN -> EXIST transformation We have a cost based approach to decide which technique should be used. So trying to see if the equality is actually injected by IN -> EXIST transformation but somehow we forget to remove it when we pick up the materialisation strategy. Lets try to run the query with in_to_exists = off MariaDB [test]> set optimizer_switch='in_to_exists=off'; Query OK, 0 rows affected (0.000 sec)   MariaDB [test]> SELECT SQL_NO_CACHE sl.id -> FROM store_loan sl -> WHERE ( -> sl.id IN (SELECT A.id FROM store_loan AS A WHERE A.local_name IN (SELECT B.local_name FROM store_loan AS B WHERE B.id IN (0,4,12,13,1,10,3,11))) -> OR -> (sl.id IN (0,4,12,13,1,10,3,11)) -> ); +----+ | id | +----+ | 1 | | 2 | | 3 | | 4 | | 5 | | 6 | | 7 | | 8 | | 9 | | 10 | | 11 | | 12 | | 13 | +----+ 13 rows in set (0.004 sec) The results are as expected, so IN->EXIST strategy injects A.id = sl.id and then it is not removed when we pick materialisation as the optimum strategy.

Varun Gupta (Inactive) added a comment - 2018-05-23 21:48 Patch http://lists.askmonty.org/pipermail/commits/2018-May/012576.html

Varun Gupta (Inactive) added a comment - 2018-06-02 06:25 Pushed to 5.5