Ajay Kumar added a comment - 2017-07-31 06:49 - edited Tried set optimizer_switch='materialization=off'; working Thanks

Sergei Petrunia added a comment - 2017-08-01 11:50 ANALYZE: { "query_block": { "select_id": 1, "r_loops": 1, "r_total_time_ms": 1.1786, "read_sorted_file": { "r_rows": 2, "filesort": { "sort_key": "t1.`Id`", "r_loops": 1, "r_total_time_ms": 0.1815, "r_used_priority_queue": false, "r_output_rows": 2, "r_buffer_size": "240", "table": { "table_name": "<subquery2>", "access_type": "ALL", "possible_keys": ["distinct_key"], "r_loops": 1, "rows": 2, "r_rows": 2, "r_total_time_ms": 0.2101, "filtered": 100, "r_filtered": 1, "materialized": { "unique": 1, "query_block": { "select_id": 2, "table": { "table_name": "t2", "access_type": "range", "possible_keys": ["Name", "id_t2"], "key": "Name", "key_length": "202", "used_key_parts": ["Name"], "r_loops": 1, "rows": 2, "r_rows": 2, "r_total_time_ms": 0.4923, "filtered": 100, "r_filtered": 100, "index_condition": "t2.`Name` in ('aa','bb')" } } } } } }, "table": { "table_name": "t1", "access_type": "eq_ref", "possible_keys": ["PRIMARY"], "key": "PRIMARY", "key_length": "4", "used_key_parts": ["Id"], "ref": ["test.t2.id_t2"], "r_loops": 2, "rows": 1, "r_rows": 1, "r_total_time_ms": 0.0924, "filtered": 100, "r_filtered": 100 } } }

Sergei Petrunia added a comment - 2017-08-01 12:11 Peculiar things about this query plan: first, we do the subquery materialization (this reads data from table t2) then, materialized subquery is used as a source data for filesort(). filesort has "sort_key": "t1.`Id`" . That is, sort criteria uses table t1. This is valid since the query has WHERE (api.Id = x.ApiResourceId)) -- this is in the subquery ... ORDER BY api.Id however, this is where the bug most likely is. SJ-Materialization-scan strategy unpacks in-equality columns to the outer select' columns. I mean, join execution does that but does this happen when the filesort() is employed? The guess agrees with the observation that the error started to occur after commit a95e384d54702 (which made such query plans possible).

Alice Sherepa added a comment - 2017-09-14 11:41 Sergei Petrunia, after fixing please check if initially reported problem and one from mdev-12959 are fixed

Varun Gupta (Inactive) added a comment - 2020-07-09 05:57 Fixed by MDEV-13994