diff options
Diffstat (limited to 'mysql-test/main/in_subq_cond_pushdown.result')
| -rw-r--r-- | mysql-test/main/in_subq_cond_pushdown.result | 2987 |
1 files changed, 1707 insertions, 1280 deletions
diff --git a/mysql-test/main/in_subq_cond_pushdown.result b/mysql-test/main/in_subq_cond_pushdown.result index eef320d2d04..f114fc6824e 100644 --- a/mysql-test/main/in_subq_cond_pushdown.result +++ b/mysql-test/main/in_subq_cond_pushdown.result @@ -51,40 +51,50 @@ EXPLAIN { "query_block": { "select_id": 1, - "table": { - "table_name": "t1", - "access_type": "ALL", - "rows": 16, - "filtered": 100, - "attached_condition": "t1.c < 25 and t1.a is not null and t1.c is not null" - }, - "table": { - "table_name": "<subquery2>", - "access_type": "eq_ref", - "possible_keys": ["distinct_key"], - "key": "distinct_key", - "key_length": "8", - "used_key_parts": ["e", "MAX(t2.g)"], - "ref": ["test.t1.a", "test.t1.c"], - "rows": 1, - "filtered": 100, - "materialized": { - "unique": 1, - "query_block": { - "select_id": 2, - "having_condition": "`MAX(t2.g)` < 25", - "temporary_table": { - "table": { - "table_name": "t2", - "access_type": "ALL", - "rows": 12, - "filtered": 100, - "attached_condition": "t2.e < 5" + "nested_loop": [ + { + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 16, + "filtered": 100, + "attached_condition": "t1.c < 25 and t1.a is not null and t1.c is not null" + } + }, + { + "table": { + "table_name": "<subquery2>", + "access_type": "eq_ref", + "possible_keys": ["distinct_key"], + "key": "distinct_key", + "key_length": "8", + "used_key_parts": ["e", "MAX(t2.g)"], + "ref": ["test.t1.a", "test.t1.c"], + "rows": 1, + "filtered": 100, + "materialized": { + "unique": 1, + "query_block": { + "select_id": 2, + "having_condition": "`MAX(t2.g)` < 25", + "temporary_table": { + "nested_loop": [ + { + "table": { + "table_name": "t2", + "access_type": "ALL", + "rows": 12, + "filtered": 100, + "attached_condition": "t2.e < 5" + } + } + ] + } } } } } - } + ] } } # extracted AND formula : pushing into HAVING @@ -140,40 +150,50 @@ EXPLAIN { "query_block": { "select_id": 1, - "table": { - "table_name": "t1", - "access_type": "ALL", - "rows": 16, - "filtered": 100, - "attached_condition": "t1.c > 55 and t1.b < 4 and t1.a is not null and t1.b is not null and t1.c is not null" - }, - "table": { - "table_name": "<subquery2>", - "access_type": "eq_ref", - "possible_keys": ["distinct_key"], - "key": "distinct_key", - "key_length": "12", - "used_key_parts": ["e", "f", "MAX(t2.g)"], - "ref": ["test.t1.a", "test.t1.b", "test.t1.c"], - "rows": 1, - "filtered": 100, - "materialized": { - "unique": 1, - "query_block": { - "select_id": 2, - "having_condition": "`MAX(t2.g)` > 55 and t2.f < 4", - "temporary_table": { - "table": { - "table_name": "t2", - "access_type": "ALL", - "rows": 12, - "filtered": 100, - "attached_condition": "t2.e < 5" + "nested_loop": [ + { + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 16, + "filtered": 100, + "attached_condition": "t1.c > 55 and t1.b < 4 and t1.a is not null and t1.b is not null and t1.c is not null" + } + }, + { + "table": { + "table_name": "<subquery2>", + "access_type": "eq_ref", + "possible_keys": ["distinct_key"], + "key": "distinct_key", + "key_length": "12", + "used_key_parts": ["e", "f", "MAX(t2.g)"], + "ref": ["test.t1.a", "test.t1.b", "test.t1.c"], + "rows": 1, + "filtered": 100, + "materialized": { + "unique": 1, + "query_block": { + "select_id": 2, + "having_condition": "`MAX(t2.g)` > 55 and t2.f < 4", + "temporary_table": { + "nested_loop": [ + { + "table": { + "table_name": "t2", + "access_type": "ALL", + "rows": 12, + "filtered": 100, + "attached_condition": "t2.e < 5" + } + } + ] + } } } } } - } + ] } } # extracted OR formula : pushing into HAVING @@ -231,40 +251,50 @@ EXPLAIN { "query_block": { "select_id": 1, - "table": { - "table_name": "t1", - "access_type": "ALL", - "rows": 16, - "filtered": 100, - "attached_condition": "(t1.c > 60 or t1.c < 25) and t1.a is not null and t1.b is not null and t1.c is not null" - }, - "table": { - "table_name": "<subquery2>", - "access_type": "eq_ref", - "possible_keys": ["distinct_key"], - "key": "distinct_key", - "key_length": "12", - "used_key_parts": ["e", "f", "MAX(t2.g)"], - "ref": ["test.t1.a", "test.t1.b", "test.t1.c"], - "rows": 1, - "filtered": 100, - "materialized": { - "unique": 1, - "query_block": { - "select_id": 2, - "having_condition": "`MAX(t2.g)` > 60 or `MAX(t2.g)` < 25", - "temporary_table": { - "table": { - "table_name": "t2", - "access_type": "ALL", - "rows": 12, - "filtered": 100, - "attached_condition": "t2.e < 5" + "nested_loop": [ + { + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 16, + "filtered": 100, + "attached_condition": "(t1.c > 60 or t1.c < 25) and t1.a is not null and t1.b is not null and t1.c is not null" + } + }, + { + "table": { + "table_name": "<subquery2>", + "access_type": "eq_ref", + "possible_keys": ["distinct_key"], + "key": "distinct_key", + "key_length": "12", + "used_key_parts": ["e", "f", "MAX(t2.g)"], + "ref": ["test.t1.a", "test.t1.b", "test.t1.c"], + "rows": 1, + "filtered": 100, + "materialized": { + "unique": 1, + "query_block": { + "select_id": 2, + "having_condition": "`MAX(t2.g)` > 60 or `MAX(t2.g)` < 25", + "temporary_table": { + "nested_loop": [ + { + "table": { + "table_name": "t2", + "access_type": "ALL", + "rows": 12, + "filtered": 100, + "attached_condition": "t2.e < 5" + } + } + ] + } } } } } - } + ] } } # extracted AND-OR formula : pushing into HAVING @@ -320,40 +350,50 @@ EXPLAIN { "query_block": { "select_id": 1, - "table": { - "table_name": "t1", - "access_type": "ALL", - "rows": 16, - "filtered": 100, - "attached_condition": "(t1.c > 60 or t1.c < 25) and t1.b > 2 and t1.a is not null and t1.b is not null and t1.c is not null" - }, - "table": { - "table_name": "<subquery2>", - "access_type": "eq_ref", - "possible_keys": ["distinct_key"], - "key": "distinct_key", - "key_length": "12", - "used_key_parts": ["e", "f", "MAX(t2.g)"], - "ref": ["test.t1.a", "test.t1.b", "test.t1.c"], - "rows": 1, - "filtered": 100, - "materialized": { - "unique": 1, - "query_block": { - "select_id": 2, - "having_condition": "(`MAX(t2.g)` > 60 or `MAX(t2.g)` < 25) and t2.f > 2", - "temporary_table": { - "table": { - "table_name": "t2", - "access_type": "ALL", - "rows": 12, - "filtered": 100, - "attached_condition": "t2.e < 5" + "nested_loop": [ + { + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 16, + "filtered": 100, + "attached_condition": "(t1.c > 60 or t1.c < 25) and t1.b > 2 and t1.a is not null and t1.b is not null and t1.c is not null" + } + }, + { + "table": { + "table_name": "<subquery2>", + "access_type": "eq_ref", + "possible_keys": ["distinct_key"], + "key": "distinct_key", + "key_length": "12", + "used_key_parts": ["e", "f", "MAX(t2.g)"], + "ref": ["test.t1.a", "test.t1.b", "test.t1.c"], + "rows": 1, + "filtered": 100, + "materialized": { + "unique": 1, + "query_block": { + "select_id": 2, + "having_condition": "(`MAX(t2.g)` > 60 or `MAX(t2.g)` < 25) and t2.f > 2", + "temporary_table": { + "nested_loop": [ + { + "table": { + "table_name": "t2", + "access_type": "ALL", + "rows": 12, + "filtered": 100, + "attached_condition": "t2.e < 5" + } + } + ] + } } } } } - } + ] } } # conjunctive subformula : pushing into HAVING @@ -411,40 +451,50 @@ EXPLAIN { "query_block": { "select_id": 1, - "table": { - "table_name": "t1", - "access_type": "ALL", - "rows": 16, - "filtered": 100, - "attached_condition": "(t1.a < 2 or t1.d > 3) and t1.b > 1 and t1.a is not null and t1.b is not null and t1.c is not null" - }, - "table": { - "table_name": "<subquery2>", - "access_type": "eq_ref", - "possible_keys": ["distinct_key"], - "key": "distinct_key", - "key_length": "12", - "used_key_parts": ["e", "f", "MAX(t2.g)"], - "ref": ["test.t1.a", "test.t1.b", "test.t1.c"], - "rows": 1, - "filtered": 100, - "materialized": { - "unique": 1, - "query_block": { - "select_id": 2, - "having_condition": "t2.f > 1", - "temporary_table": { - "table": { - "table_name": "t2", - "access_type": "ALL", - "rows": 12, - "filtered": 100, - "attached_condition": "t2.e < 5" + "nested_loop": [ + { + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 16, + "filtered": 100, + "attached_condition": "(t1.a < 2 or t1.d > 3) and t1.b > 1 and t1.a is not null and t1.b is not null and t1.c is not null" + } + }, + { + "table": { + "table_name": "<subquery2>", + "access_type": "eq_ref", + "possible_keys": ["distinct_key"], + "key": "distinct_key", + "key_length": "12", + "used_key_parts": ["e", "f", "MAX(t2.g)"], + "ref": ["test.t1.a", "test.t1.b", "test.t1.c"], + "rows": 1, + "filtered": 100, + "materialized": { + "unique": 1, + "query_block": { + "select_id": 2, + "having_condition": "t2.f > 1", + "temporary_table": { + "nested_loop": [ + { + "table": { + "table_name": "t2", + "access_type": "ALL", + "rows": 12, + "filtered": 100, + "attached_condition": "t2.e < 5" + } + } + ] + } } } } } - } + ] } } # using view IN subquery defINition : pushing into HAVING @@ -500,40 +550,50 @@ EXPLAIN { "query_block": { "select_id": 1, - "table": { - "table_name": "t1", - "access_type": "ALL", - "rows": 16, - "filtered": 100, - "attached_condition": "t1.c > 20 and t1.a is not null and t1.c is not null" - }, - "table": { - "table_name": "<subquery2>", - "access_type": "eq_ref", - "possible_keys": ["distinct_key"], - "key": "distinct_key", - "key_length": "8", - "used_key_parts": ["v1_x", "MAX(v1_y)"], - "ref": ["test.t1.a", "test.t1.c"], - "rows": 1, - "filtered": 100, - "materialized": { - "unique": 1, - "query_block": { - "select_id": 2, - "having_condition": "`MAX(v1_y)` > 20", - "temporary_table": { - "table": { - "table_name": "t3", - "access_type": "ALL", - "rows": 8, - "filtered": 100, - "attached_condition": "t3.x > 1 and t3.x <= 3" + "nested_loop": [ + { + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 16, + "filtered": 100, + "attached_condition": "t1.c > 20 and t1.a is not null and t1.c is not null" + } + }, + { + "table": { + "table_name": "<subquery2>", + "access_type": "eq_ref", + "possible_keys": ["distinct_key"], + "key": "distinct_key", + "key_length": "8", + "used_key_parts": ["v1_x", "MAX(v1_y)"], + "ref": ["test.t1.a", "test.t1.c"], + "rows": 1, + "filtered": 100, + "materialized": { + "unique": 1, + "query_block": { + "select_id": 2, + "having_condition": "`MAX(v1_y)` > 20", + "temporary_table": { + "nested_loop": [ + { + "table": { + "table_name": "t3", + "access_type": "ALL", + "rows": 8, + "filtered": 100, + "attached_condition": "t3.x > 1 and t3.x <= 3" + } + } + ] + } } } } } - } + ] } } # using equality : pushing into WHERE @@ -590,52 +650,64 @@ EXPLAIN { "query_block": { "select_id": 1, - "table": { - "table_name": "t3", - "access_type": "ALL", - "rows": 8, - "filtered": 100, - "attached_condition": "t3.y > 20 and t3.x <= 3 and t3.y is not null" - }, - "block-nl-join": { - "table": { - "table_name": "t1", - "access_type": "ALL", - "rows": 16, - "filtered": 100 + "nested_loop": [ + { + "table": { + "table_name": "t3", + "access_type": "ALL", + "rows": 8, + "filtered": 100, + "attached_condition": "t3.y > 20 and t3.x <= 3 and t3.y is not null" + } + }, + { + "block-nl-join": { + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 16, + "filtered": 100 + }, + "buffer_type": "flat", + "buffer_size": "119", + "join_type": "BNL", + "attached_condition": "t1.c = t3.y and t1.a is not null" + } }, - "buffer_type": "flat", - "buffer_size": "119", - "join_type": "BNL", - "attached_condition": "t1.c = t3.y and t1.a is not null" - }, - "table": { - "table_name": "<subquery2>", - "access_type": "eq_ref", - "possible_keys": ["distinct_key"], - "key": "distinct_key", - "key_length": "8", - "used_key_parts": ["e", "MAX(t2.g)"], - "ref": ["test.t1.a", "test.t3.y"], - "rows": 1, - "filtered": 100, - "materialized": { - "unique": 1, - "query_block": { - "select_id": 2, - "having_condition": "`MAX(t2.g)` > 20", - "temporary_table": { - "table": { - "table_name": "t2", - "access_type": "ALL", - "rows": 12, - "filtered": 100, - "attached_condition": "t2.e < 5" + { + "table": { + "table_name": "<subquery2>", + "access_type": "eq_ref", + "possible_keys": ["distinct_key"], + "key": "distinct_key", + "key_length": "8", + "used_key_parts": ["e", "MAX(t2.g)"], + "ref": ["test.t1.a", "test.t3.y"], + "rows": 1, + "filtered": 100, + "materialized": { + "unique": 1, + "query_block": { + "select_id": 2, + "having_condition": "`MAX(t2.g)` > 20", + "temporary_table": { + "nested_loop": [ + { + "table": { + "table_name": "t2", + "access_type": "ALL", + "rows": 12, + "filtered": 100, + "attached_condition": "t2.e < 5" + } + } + ] + } } } } } - } + ] } } # conjunctive subformula : pushing into WHERE @@ -693,39 +765,49 @@ EXPLAIN { "query_block": { "select_id": 1, - "table": { - "table_name": "t1", - "access_type": "ALL", - "rows": 16, - "filtered": 100, - "attached_condition": "t1.a < 2 and t1.a is not null and t1.c is not null" - }, - "table": { - "table_name": "<subquery2>", - "access_type": "eq_ref", - "possible_keys": ["distinct_key"], - "key": "distinct_key", - "key_length": "8", - "used_key_parts": ["e", "MAX(t2.g)"], - "ref": ["test.t1.a", "test.t1.c"], - "rows": 1, - "filtered": 100, - "materialized": { - "unique": 1, - "query_block": { - "select_id": 2, - "temporary_table": { - "table": { - "table_name": "t2", - "access_type": "ALL", - "rows": 12, - "filtered": 100, - "attached_condition": "t2.e < 5 and t2.e < 2" + "nested_loop": [ + { + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 16, + "filtered": 100, + "attached_condition": "t1.a < 2 and t1.a is not null and t1.c is not null" + } + }, + { + "table": { + "table_name": "<subquery2>", + "access_type": "eq_ref", + "possible_keys": ["distinct_key"], + "key": "distinct_key", + "key_length": "8", + "used_key_parts": ["e", "MAX(t2.g)"], + "ref": ["test.t1.a", "test.t1.c"], + "rows": 1, + "filtered": 100, + "materialized": { + "unique": 1, + "query_block": { + "select_id": 2, + "temporary_table": { + "nested_loop": [ + { + "table": { + "table_name": "t2", + "access_type": "ALL", + "rows": 12, + "filtered": 100, + "attached_condition": "t2.e < 5 and t2.e < 2" + } + } + ] + } } } } } - } + ] } } # extracted AND formula : pushing into WHERE @@ -783,39 +865,49 @@ EXPLAIN { "query_block": { "select_id": 1, - "table": { - "table_name": "t1", - "access_type": "ALL", - "rows": 16, - "filtered": 100, - "attached_condition": "t1.a > 2 and t1.a < 5 and t1.a is not null and t1.c is not null" - }, - "table": { - "table_name": "<subquery2>", - "access_type": "eq_ref", - "possible_keys": ["distinct_key"], - "key": "distinct_key", - "key_length": "8", - "used_key_parts": ["e", "MAX(t2.g)"], - "ref": ["test.t1.a", "test.t1.c"], - "rows": 1, - "filtered": 100, - "materialized": { - "unique": 1, - "query_block": { - "select_id": 2, - "temporary_table": { - "table": { - "table_name": "t2", - "access_type": "ALL", - "rows": 12, - "filtered": 100, - "attached_condition": "t2.e < 5 and t2.e > 2 and t2.e < 5" + "nested_loop": [ + { + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 16, + "filtered": 100, + "attached_condition": "t1.a > 2 and t1.a < 5 and t1.a is not null and t1.c is not null" + } + }, + { + "table": { + "table_name": "<subquery2>", + "access_type": "eq_ref", + "possible_keys": ["distinct_key"], + "key": "distinct_key", + "key_length": "8", + "used_key_parts": ["e", "MAX(t2.g)"], + "ref": ["test.t1.a", "test.t1.c"], + "rows": 1, + "filtered": 100, + "materialized": { + "unique": 1, + "query_block": { + "select_id": 2, + "temporary_table": { + "nested_loop": [ + { + "table": { + "table_name": "t2", + "access_type": "ALL", + "rows": 12, + "filtered": 100, + "attached_condition": "t2.e < 5 and t2.e > 2 and t2.e < 5" + } + } + ] + } } } } } - } + ] } } # extracted OR formula : pushing into WHERE @@ -875,39 +967,49 @@ EXPLAIN { "query_block": { "select_id": 1, - "table": { - "table_name": "t1", - "access_type": "ALL", - "rows": 16, - "filtered": 100, - "attached_condition": "(t1.a < 2 or t1.a >= 4) and t1.a is not null and t1.c is not null" - }, - "table": { - "table_name": "<subquery2>", - "access_type": "eq_ref", - "possible_keys": ["distinct_key"], - "key": "distinct_key", - "key_length": "8", - "used_key_parts": ["e", "MAX(t2.g)"], - "ref": ["test.t1.a", "test.t1.c"], - "rows": 1, - "filtered": 100, - "materialized": { - "unique": 1, - "query_block": { - "select_id": 2, - "temporary_table": { - "table": { - "table_name": "t2", - "access_type": "ALL", - "rows": 12, - "filtered": 100, - "attached_condition": "t2.e < 5 and (t2.e < 2 or t2.e >= 4)" + "nested_loop": [ + { + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 16, + "filtered": 100, + "attached_condition": "(t1.a < 2 or t1.a >= 4) and t1.a is not null and t1.c is not null" + } + }, + { + "table": { + "table_name": "<subquery2>", + "access_type": "eq_ref", + "possible_keys": ["distinct_key"], + "key": "distinct_key", + "key_length": "8", + "used_key_parts": ["e", "MAX(t2.g)"], + "ref": ["test.t1.a", "test.t1.c"], + "rows": 1, + "filtered": 100, + "materialized": { + "unique": 1, + "query_block": { + "select_id": 2, + "temporary_table": { + "nested_loop": [ + { + "table": { + "table_name": "t2", + "access_type": "ALL", + "rows": 12, + "filtered": 100, + "attached_condition": "t2.e < 5 and (t2.e < 2 or t2.e >= 4)" + } + } + ] + } } } } } - } + ] } } # extracted AND-OR formula : pushing into WHERE @@ -963,39 +1065,49 @@ EXPLAIN { "query_block": { "select_id": 1, - "table": { - "table_name": "t1", - "access_type": "ALL", - "rows": 16, - "filtered": 100, - "attached_condition": "(t1.a < 2 or t1.a = 5) and t1.b > 3 and t1.a is not null and t1.b is not null and t1.c is not null" - }, - "table": { - "table_name": "<subquery2>", - "access_type": "eq_ref", - "possible_keys": ["distinct_key"], - "key": "distinct_key", - "key_length": "12", - "used_key_parts": ["e", "f", "MAX(t2.g)"], - "ref": ["test.t1.a", "test.t1.b", "test.t1.c"], - "rows": 1, - "filtered": 100, - "materialized": { - "unique": 1, - "query_block": { - "select_id": 2, - "temporary_table": { - "table": { - "table_name": "t2", - "access_type": "ALL", - "rows": 12, - "filtered": 100, - "attached_condition": "t2.e < 5 and (t2.e < 2 or t2.e = 5) and t2.f > 3" + "nested_loop": [ + { + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 16, + "filtered": 100, + "attached_condition": "(t1.a < 2 or t1.a = 5) and t1.b > 3 and t1.a is not null and t1.b is not null and t1.c is not null" + } + }, + { + "table": { + "table_name": "<subquery2>", + "access_type": "eq_ref", + "possible_keys": ["distinct_key"], + "key": "distinct_key", + "key_length": "12", + "used_key_parts": ["e", "f", "MAX(t2.g)"], + "ref": ["test.t1.a", "test.t1.b", "test.t1.c"], + "rows": 1, + "filtered": 100, + "materialized": { + "unique": 1, + "query_block": { + "select_id": 2, + "temporary_table": { + "nested_loop": [ + { + "table": { + "table_name": "t2", + "access_type": "ALL", + "rows": 12, + "filtered": 100, + "attached_condition": "t2.e < 5 and (t2.e < 2 or t2.e = 5) and t2.f > 3" + } + } + ] + } } } } } - } + ] } } # extracted AND-OR formula : pushing into WHERE @@ -1051,39 +1163,49 @@ EXPLAIN { "query_block": { "select_id": 1, - "table": { - "table_name": "t1", - "access_type": "ALL", - "rows": 16, - "filtered": 100, - "attached_condition": "(t1.a < 2 or t1.a = 5) and t1.b > 3 and t1.a is not null and t1.b is not null and t1.c is not null" - }, - "table": { - "table_name": "<subquery2>", - "access_type": "eq_ref", - "possible_keys": ["distinct_key"], - "key": "distinct_key", - "key_length": "12", - "used_key_parts": ["e", "f", "MAX(t2.g)"], - "ref": ["test.t1.a", "test.t1.b", "test.t1.c"], - "rows": 1, - "filtered": 100, - "materialized": { - "unique": 1, - "query_block": { - "select_id": 2, - "temporary_table": { - "table": { - "table_name": "t2", - "access_type": "ALL", - "rows": 12, - "filtered": 100, - "attached_condition": "t2.e < 5 and (t2.e < 2 or t2.e = 5) and t2.f > 3" + "nested_loop": [ + { + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 16, + "filtered": 100, + "attached_condition": "(t1.a < 2 or t1.a = 5) and t1.b > 3 and t1.a is not null and t1.b is not null and t1.c is not null" + } + }, + { + "table": { + "table_name": "<subquery2>", + "access_type": "eq_ref", + "possible_keys": ["distinct_key"], + "key": "distinct_key", + "key_length": "12", + "used_key_parts": ["e", "f", "MAX(t2.g)"], + "ref": ["test.t1.a", "test.t1.b", "test.t1.c"], + "rows": 1, + "filtered": 100, + "materialized": { + "unique": 1, + "query_block": { + "select_id": 2, + "temporary_table": { + "nested_loop": [ + { + "table": { + "table_name": "t2", + "access_type": "ALL", + "rows": 12, + "filtered": 100, + "attached_condition": "t2.e < 5 and (t2.e < 2 or t2.e = 5) and t2.f > 3" + } + } + ] + } } } } } - } + ] } } # conjunctive subformula : pushing into WHERE @@ -1139,39 +1261,49 @@ EXPLAIN { "query_block": { "select_id": 1, - "table": { - "table_name": "t1", - "access_type": "ALL", - "rows": 16, - "filtered": 100, - "attached_condition": "(t1.b < 3 or t1.d > 2) and t1.a < 2 and t1.a is not null and t1.b is not null and t1.c is not null" - }, - "table": { - "table_name": "<subquery2>", - "access_type": "eq_ref", - "possible_keys": ["distinct_key"], - "key": "distinct_key", - "key_length": "12", - "used_key_parts": ["e", "f", "MAX(t2.g)"], - "ref": ["test.t1.a", "test.t1.b", "test.t1.c"], - "rows": 1, - "filtered": 100, - "materialized": { - "unique": 1, - "query_block": { - "select_id": 2, - "temporary_table": { - "table": { - "table_name": "t2", - "access_type": "ALL", - "rows": 12, - "filtered": 100, - "attached_condition": "t2.e < 5 and t2.e < 2" + "nested_loop": [ + { + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 16, + "filtered": 100, + "attached_condition": "(t1.b < 3 or t1.d > 2) and t1.a < 2 and t1.a is not null and t1.b is not null and t1.c is not null" + } + }, + { + "table": { + "table_name": "<subquery2>", + "access_type": "eq_ref", + "possible_keys": ["distinct_key"], + "key": "distinct_key", + "key_length": "12", + "used_key_parts": ["e", "f", "MAX(t2.g)"], + "ref": ["test.t1.a", "test.t1.b", "test.t1.c"], + "rows": 1, + "filtered": 100, + "materialized": { + "unique": 1, + "query_block": { + "select_id": 2, + "temporary_table": { + "nested_loop": [ + { + "table": { + "table_name": "t2", + "access_type": "ALL", + "rows": 12, + "filtered": 100, + "attached_condition": "t2.e < 5 and t2.e < 2" + } + } + ] + } } } } } - } + ] } } # using equalities : pushing into WHERE @@ -1227,37 +1359,47 @@ EXPLAIN { "query_block": { "select_id": 1, - "table": { - "table_name": "t1", - "access_type": "ALL", - "rows": 16, - "filtered": 100, - "attached_condition": "t1.a = 1 and t1.d = 1 and t1.c is not null" - }, - "table": { - "table_name": "<subquery2>", - "access_type": "eq_ref", - "possible_keys": ["distinct_key"], - "key": "distinct_key", - "key_length": "8", - "used_key_parts": ["e", "MAX(t2.g)"], - "ref": ["const", "test.t1.c"], - "rows": 1, - "filtered": 100, - "materialized": { - "unique": 1, - "query_block": { - "select_id": 2, - "table": { - "table_name": "t2", - "access_type": "ALL", - "rows": 12, - "filtered": 100, - "attached_condition": "t2.e = 1" + "nested_loop": [ + { + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 16, + "filtered": 100, + "attached_condition": "t1.a = 1 and t1.d = 1 and t1.c is not null" + } + }, + { + "table": { + "table_name": "<subquery2>", + "access_type": "eq_ref", + "possible_keys": ["distinct_key"], + "key": "distinct_key", + "key_length": "8", + "used_key_parts": ["e", "MAX(t2.g)"], + "ref": ["const", "test.t1.c"], + "rows": 1, + "filtered": 100, + "materialized": { + "unique": 1, + "query_block": { + "select_id": 2, + "nested_loop": [ + { + "table": { + "table_name": "t2", + "access_type": "ALL", + "rows": 12, + "filtered": 100, + "attached_condition": "t2.e = 1" + } + } + ] + } } } } - } + ] } } # using equality : pushing into WHERE @@ -1313,39 +1455,49 @@ EXPLAIN { "query_block": { "select_id": 1, - "table": { - "table_name": "t1", - "access_type": "ALL", - "rows": 16, - "filtered": 100, - "attached_condition": "t1.d = t1.a and t1.a > 1 and t1.a is not null and t1.c is not null" - }, - "table": { - "table_name": "<subquery2>", - "access_type": "eq_ref", - "possible_keys": ["distinct_key"], - "key": "distinct_key", - "key_length": "8", - "used_key_parts": ["e", "MAX(t2.g)"], - "ref": ["test.t1.a", "test.t1.c"], - "rows": 1, - "filtered": 100, - "materialized": { - "unique": 1, - "query_block": { - "select_id": 2, - "temporary_table": { - "table": { - "table_name": "t2", - "access_type": "ALL", - "rows": 12, - "filtered": 100, - "attached_condition": "t2.e < 5 and t2.e > 1" + "nested_loop": [ + { + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 16, + "filtered": 100, + "attached_condition": "t1.d = t1.a and t1.a > 1 and t1.a is not null and t1.c is not null" + } + }, + { + "table": { + "table_name": "<subquery2>", + "access_type": "eq_ref", + "possible_keys": ["distinct_key"], + "key": "distinct_key", + "key_length": "8", + "used_key_parts": ["e", "MAX(t2.g)"], + "ref": ["test.t1.a", "test.t1.c"], + "rows": 1, + "filtered": 100, + "materialized": { + "unique": 1, + "query_block": { + "select_id": 2, + "temporary_table": { + "nested_loop": [ + { + "table": { + "table_name": "t2", + "access_type": "ALL", + "rows": 12, + "filtered": 100, + "attached_condition": "t2.e < 5 and t2.e > 1" + } + } + ] + } } } } } - } + ] } } # using view IN subquery definition : pushing into WHERE @@ -1399,39 +1551,49 @@ EXPLAIN { "query_block": { "select_id": 1, - "table": { - "table_name": "t1", - "access_type": "ALL", - "rows": 16, - "filtered": 100, - "attached_condition": "t1.a < 3 and t1.a is not null and t1.c is not null" - }, - "table": { - "table_name": "<subquery2>", - "access_type": "eq_ref", - "possible_keys": ["distinct_key"], - "key": "distinct_key", - "key_length": "8", - "used_key_parts": ["v1_x", "MAX(v1_y)"], - "ref": ["test.t1.a", "test.t1.c"], - "rows": 1, - "filtered": 100, - "materialized": { - "unique": 1, - "query_block": { - "select_id": 2, - "temporary_table": { - "table": { - "table_name": "t3", - "access_type": "ALL", - "rows": 8, - "filtered": 100, - "attached_condition": "t3.x > 1 and t3.x <= 3 and t3.x < 3" + "nested_loop": [ + { + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 16, + "filtered": 100, + "attached_condition": "t1.a < 3 and t1.a is not null and t1.c is not null" + } + }, + { + "table": { + "table_name": "<subquery2>", + "access_type": "eq_ref", + "possible_keys": ["distinct_key"], + "key": "distinct_key", + "key_length": "8", + "used_key_parts": ["v1_x", "MAX(v1_y)"], + "ref": ["test.t1.a", "test.t1.c"], + "rows": 1, + "filtered": 100, + "materialized": { + "unique": 1, + "query_block": { + "select_id": 2, + "temporary_table": { + "nested_loop": [ + { + "table": { + "table_name": "t3", + "access_type": "ALL", + "rows": 8, + "filtered": 100, + "attached_condition": "t3.x > 1 and t3.x <= 3 and t3.x < 3" + } + } + ] + } } } } } - } + ] } } # using equality : pushing into WHERE @@ -1490,51 +1652,63 @@ EXPLAIN { "query_block": { "select_id": 1, - "table": { - "table_name": "t3", - "access_type": "ALL", - "rows": 8, - "filtered": 100, - "attached_condition": "t3.x < 2 and t3.y > 30 and t3.x <= 3 and t3.x is not null" - }, - "block-nl-join": { - "table": { - "table_name": "t1", - "access_type": "ALL", - "rows": 16, - "filtered": 100 + "nested_loop": [ + { + "table": { + "table_name": "t3", + "access_type": "ALL", + "rows": 8, + "filtered": 100, + "attached_condition": "t3.x < 2 and t3.y > 30 and t3.x <= 3 and t3.x is not null" + } + }, + { + "block-nl-join": { + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 16, + "filtered": 100 + }, + "buffer_type": "flat", + "buffer_size": "119", + "join_type": "BNL", + "attached_condition": "t1.a = t3.x and t1.c is not null" + } }, - "buffer_type": "flat", - "buffer_size": "119", - "join_type": "BNL", - "attached_condition": "t1.a = t3.x and t1.c is not null" - }, - "table": { - "table_name": "<subquery2>", - "access_type": "eq_ref", - "possible_keys": ["distinct_key"], - "key": "distinct_key", - "key_length": "8", - "used_key_parts": ["e", "MAX(t2.g)"], - "ref": ["test.t3.x", "test.t1.c"], - "rows": 1, - "filtered": 100, - "materialized": { - "unique": 1, - "query_block": { - "select_id": 2, - "temporary_table": { - "table": { - "table_name": "t2", - "access_type": "ALL", - "rows": 12, - "filtered": 100, - "attached_condition": "t2.e < 5 and t2.e <= 3" + { + "table": { + "table_name": "<subquery2>", + "access_type": "eq_ref", + "possible_keys": ["distinct_key"], + "key": "distinct_key", + "key_length": "8", + "used_key_parts": ["e", "MAX(t2.g)"], + "ref": ["test.t3.x", "test.t1.c"], + "rows": 1, + "filtered": 100, + "materialized": { + "unique": 1, + "query_block": { + "select_id": 2, + "temporary_table": { + "nested_loop": [ + { + "table": { + "table_name": "t2", + "access_type": "ALL", + "rows": 12, + "filtered": 100, + "attached_condition": "t2.e < 5 and t2.e <= 3" + } + } + ] + } } } } } - } + ] } } # conjunctive subformula : pushing into WHERE @@ -1591,40 +1765,50 @@ EXPLAIN { "query_block": { "select_id": 1, - "table": { - "table_name": "t1", - "access_type": "ALL", - "rows": 16, - "filtered": 100, - "attached_condition": "(t1.b < 3 or t1.b = 4) and t1.a < 3 and t1.a is not null and t1.b is not null and t1.c is not null" - }, - "table": { - "table_name": "<subquery2>", - "access_type": "eq_ref", - "possible_keys": ["distinct_key"], - "key": "distinct_key", - "key_length": "12", - "used_key_parts": ["e", "f", "MAX(t2.g)"], - "ref": ["test.t1.a", "test.t1.b", "test.t1.c"], - "rows": 1, - "filtered": 100, - "materialized": { - "unique": 1, - "query_block": { - "select_id": 2, - "having_condition": "t2.f < 3 or t2.f = 4", - "temporary_table": { - "table": { - "table_name": "t2", - "access_type": "ALL", - "rows": 12, - "filtered": 100, - "attached_condition": "t2.e < 5 and t2.e < 3" + "nested_loop": [ + { + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 16, + "filtered": 100, + "attached_condition": "(t1.b < 3 or t1.b = 4) and t1.a < 3 and t1.a is not null and t1.b is not null and t1.c is not null" + } + }, + { + "table": { + "table_name": "<subquery2>", + "access_type": "eq_ref", + "possible_keys": ["distinct_key"], + "key": "distinct_key", + "key_length": "12", + "used_key_parts": ["e", "f", "MAX(t2.g)"], + "ref": ["test.t1.a", "test.t1.b", "test.t1.c"], + "rows": 1, + "filtered": 100, + "materialized": { + "unique": 1, + "query_block": { + "select_id": 2, + "having_condition": "t2.f < 3 or t2.f = 4", + "temporary_table": { + "nested_loop": [ + { + "table": { + "table_name": "t2", + "access_type": "ALL", + "rows": 12, + "filtered": 100, + "attached_condition": "t2.e < 5 and t2.e < 3" + } + } + ] + } } } } } - } + ] } } # conjunctive subformula using addition : pushing into HAVING @@ -1680,40 +1864,50 @@ EXPLAIN { "query_block": { "select_id": 1, - "table": { - "table_name": "t1", - "access_type": "ALL", - "rows": 16, - "filtered": 100, - "attached_condition": "t1.a + t1.c > 41 and t1.a is not null and t1.c is not null" - }, - "table": { - "table_name": "<subquery2>", - "access_type": "eq_ref", - "possible_keys": ["distinct_key"], - "key": "distinct_key", - "key_length": "8", - "used_key_parts": ["e", "MAX(t2.g)"], - "ref": ["test.t1.a", "test.t1.c"], - "rows": 1, - "filtered": 100, - "materialized": { - "unique": 1, - "query_block": { - "select_id": 2, - "having_condition": "t2.e + `MAX(t2.g)` > 41", - "temporary_table": { - "table": { - "table_name": "t2", - "access_type": "ALL", - "rows": 12, - "filtered": 100, - "attached_condition": "t2.e < 5" + "nested_loop": [ + { + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 16, + "filtered": 100, + "attached_condition": "t1.a + t1.c > 41 and t1.a is not null and t1.c is not null" + } + }, + { + "table": { + "table_name": "<subquery2>", + "access_type": "eq_ref", + "possible_keys": ["distinct_key"], + "key": "distinct_key", + "key_length": "8", + "used_key_parts": ["e", "MAX(t2.g)"], + "ref": ["test.t1.a", "test.t1.c"], + "rows": 1, + "filtered": 100, + "materialized": { + "unique": 1, + "query_block": { + "select_id": 2, + "having_condition": "t2.e + `MAX(t2.g)` > 41", + "temporary_table": { + "nested_loop": [ + { + "table": { + "table_name": "t2", + "access_type": "ALL", + "rows": 12, + "filtered": 100, + "attached_condition": "t2.e < 5" + } + } + ] + } } } } } - } + ] } } # conjunctive subformula using substitution : pushing into HAVING @@ -1771,40 +1965,50 @@ EXPLAIN { "query_block": { "select_id": 1, - "table": { - "table_name": "t1", - "access_type": "ALL", - "rows": 16, - "filtered": 100, - "attached_condition": "t1.c - t1.a < 35 and t1.a is not null and t1.c is not null" - }, - "table": { - "table_name": "<subquery2>", - "access_type": "eq_ref", - "possible_keys": ["distinct_key"], - "key": "distinct_key", - "key_length": "8", - "used_key_parts": ["e", "MAX(t2.g)"], - "ref": ["test.t1.a", "test.t1.c"], - "rows": 1, - "filtered": 100, - "materialized": { - "unique": 1, - "query_block": { - "select_id": 2, - "having_condition": "`MAX(t2.g)` - t2.e < 35", - "temporary_table": { - "table": { - "table_name": "t2", - "access_type": "ALL", - "rows": 12, - "filtered": 100, - "attached_condition": "t2.e < 5" + "nested_loop": [ + { + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 16, + "filtered": 100, + "attached_condition": "t1.c - t1.a < 35 and t1.a is not null and t1.c is not null" + } + }, + { + "table": { + "table_name": "<subquery2>", + "access_type": "eq_ref", + "possible_keys": ["distinct_key"], + "key": "distinct_key", + "key_length": "8", + "used_key_parts": ["e", "MAX(t2.g)"], + "ref": ["test.t1.a", "test.t1.c"], + "rows": 1, + "filtered": 100, + "materialized": { + "unique": 1, + "query_block": { + "select_id": 2, + "having_condition": "`MAX(t2.g)` - t2.e < 35", + "temporary_table": { + "nested_loop": [ + { + "table": { + "table_name": "t2", + "access_type": "ALL", + "rows": 12, + "filtered": 100, + "attached_condition": "t2.e < 5" + } + } + ] + } } } } } - } + ] } } # conjunctive subformula using multiplication : pushing into HAVING @@ -1860,40 +2064,50 @@ EXPLAIN { "query_block": { "select_id": 1, - "table": { - "table_name": "t1", - "access_type": "ALL", - "rows": 16, - "filtered": 100, - "attached_condition": "t1.c * t1.a > 100 and t1.a is not null and t1.c is not null" - }, - "table": { - "table_name": "<subquery2>", - "access_type": "eq_ref", - "possible_keys": ["distinct_key"], - "key": "distinct_key", - "key_length": "8", - "used_key_parts": ["e", "MAX(t2.g)"], - "ref": ["test.t1.a", "test.t1.c"], - "rows": 1, - "filtered": 100, - "materialized": { - "unique": 1, - "query_block": { - "select_id": 2, - "having_condition": "`MAX(t2.g)` * t2.e > 100", - "temporary_table": { - "table": { - "table_name": "t2", - "access_type": "ALL", - "rows": 12, - "filtered": 100, - "attached_condition": "t2.e < 5" + "nested_loop": [ + { + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 16, + "filtered": 100, + "attached_condition": "t1.c * t1.a > 100 and t1.a is not null and t1.c is not null" + } + }, + { + "table": { + "table_name": "<subquery2>", + "access_type": "eq_ref", + "possible_keys": ["distinct_key"], + "key": "distinct_key", + "key_length": "8", + "used_key_parts": ["e", "MAX(t2.g)"], + "ref": ["test.t1.a", "test.t1.c"], + "rows": 1, + "filtered": 100, + "materialized": { + "unique": 1, + "query_block": { + "select_id": 2, + "having_condition": "`MAX(t2.g)` * t2.e > 100", + "temporary_table": { + "nested_loop": [ + { + "table": { + "table_name": "t2", + "access_type": "ALL", + "rows": 12, + "filtered": 100, + "attached_condition": "t2.e < 5" + } + } + ] + } } } } } - } + ] } } # conjunctive subformula using division : pushing into HAVING @@ -1953,40 +2167,50 @@ EXPLAIN { "query_block": { "select_id": 1, - "table": { - "table_name": "t1", - "access_type": "ALL", - "rows": 16, - "filtered": 100, - "attached_condition": "t1.c / t1.a > 30 and t1.a is not null and t1.c is not null" - }, - "table": { - "table_name": "<subquery2>", - "access_type": "eq_ref", - "possible_keys": ["distinct_key"], - "key": "distinct_key", - "key_length": "8", - "used_key_parts": ["e", "MAX(t2.g)"], - "ref": ["test.t1.a", "test.t1.c"], - "rows": 1, - "filtered": 100, - "materialized": { - "unique": 1, - "query_block": { - "select_id": 2, - "having_condition": "`MAX(t2.g)` / t2.e > 30", - "temporary_table": { - "table": { - "table_name": "t2", - "access_type": "ALL", - "rows": 12, - "filtered": 100, - "attached_condition": "t2.e < 5" + "nested_loop": [ + { + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 16, + "filtered": 100, + "attached_condition": "t1.c / t1.a > 30 and t1.a is not null and t1.c is not null" + } + }, + { + "table": { + "table_name": "<subquery2>", + "access_type": "eq_ref", + "possible_keys": ["distinct_key"], + "key": "distinct_key", + "key_length": "8", + "used_key_parts": ["e", "MAX(t2.g)"], + "ref": ["test.t1.a", "test.t1.c"], + "rows": 1, + "filtered": 100, + "materialized": { + "unique": 1, + "query_block": { + "select_id": 2, + "having_condition": "`MAX(t2.g)` / t2.e > 30", + "temporary_table": { + "nested_loop": [ + { + "table": { + "table_name": "t2", + "access_type": "ALL", + "rows": 12, + "filtered": 100, + "attached_condition": "t2.e < 5" + } + } + ] + } } } } } - } + ] } } # conjunctive subformula using BETWEEN : pushing into HAVING @@ -2042,40 +2266,50 @@ EXPLAIN { "query_block": { "select_id": 1, - "table": { - "table_name": "t1", - "access_type": "ALL", - "rows": 16, - "filtered": 100, - "attached_condition": "t1.c between 50 and 100 and t1.a is not null and t1.c is not null" - }, - "table": { - "table_name": "<subquery2>", - "access_type": "eq_ref", - "possible_keys": ["distinct_key"], - "key": "distinct_key", - "key_length": "8", - "used_key_parts": ["e", "MAX(t2.g)"], - "ref": ["test.t1.a", "test.t1.c"], - "rows": 1, - "filtered": 100, - "materialized": { - "unique": 1, - "query_block": { - "select_id": 2, - "having_condition": "`MAX(t2.g)` between 50 and 100", - "temporary_table": { - "table": { - "table_name": "t2", - "access_type": "ALL", - "rows": 12, - "filtered": 100, - "attached_condition": "t2.e < 5" + "nested_loop": [ + { + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 16, + "filtered": 100, + "attached_condition": "t1.c between 50 and 100 and t1.a is not null and t1.c is not null" + } + }, + { + "table": { + "table_name": "<subquery2>", + "access_type": "eq_ref", + "possible_keys": ["distinct_key"], + "key": "distinct_key", + "key_length": "8", + "used_key_parts": ["e", "MAX(t2.g)"], + "ref": ["test.t1.a", "test.t1.c"], + "rows": 1, + "filtered": 100, + "materialized": { + "unique": 1, + "query_block": { + "select_id": 2, + "having_condition": "`MAX(t2.g)` between 50 and 100", + "temporary_table": { + "nested_loop": [ + { + "table": { + "table_name": "t2", + "access_type": "ALL", + "rows": 12, + "filtered": 100, + "attached_condition": "t2.e < 5" + } + } + ] + } } } } } - } + ] } } # conjunctive subformula using addition : pushing into WHERE @@ -2131,39 +2365,49 @@ EXPLAIN { "query_block": { "select_id": 1, - "table": { - "table_name": "t1", - "access_type": "ALL", - "rows": 16, - "filtered": 100, - "attached_condition": "t1.a + t1.b > 5 and t1.a is not null and t1.b is not null and t1.c is not null" - }, - "table": { - "table_name": "<subquery2>", - "access_type": "eq_ref", - "possible_keys": ["distinct_key"], - "key": "distinct_key", - "key_length": "12", - "used_key_parts": ["e", "f", "MAX(t2.g)"], - "ref": ["test.t1.a", "test.t1.b", "test.t1.c"], - "rows": 1, - "filtered": 100, - "materialized": { - "unique": 1, - "query_block": { - "select_id": 2, - "temporary_table": { - "table": { - "table_name": "t2", - "access_type": "ALL", - "rows": 12, - "filtered": 100, - "attached_condition": "t2.e < 5 and t2.e + t2.f > 5" + "nested_loop": [ + { + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 16, + "filtered": 100, + "attached_condition": "t1.a + t1.b > 5 and t1.a is not null and t1.b is not null and t1.c is not null" + } + }, + { + "table": { + "table_name": "<subquery2>", + "access_type": "eq_ref", + "possible_keys": ["distinct_key"], + "key": "distinct_key", + "key_length": "12", + "used_key_parts": ["e", "f", "MAX(t2.g)"], + "ref": ["test.t1.a", "test.t1.b", "test.t1.c"], + "rows": 1, + "filtered": 100, + "materialized": { + "unique": 1, + "query_block": { + "select_id": 2, + "temporary_table": { + "nested_loop": [ + { + "table": { + "table_name": "t2", + "access_type": "ALL", + "rows": 12, + "filtered": 100, + "attached_condition": "t2.e < 5 and t2.e + t2.f > 5" + } + } + ] + } } } } } - } + ] } } # conjunctive subformula using substitution : pushing into WHERE @@ -2219,39 +2463,49 @@ EXPLAIN { "query_block": { "select_id": 1, - "table": { - "table_name": "t1", - "access_type": "ALL", - "rows": 16, - "filtered": 100, - "attached_condition": "t1.a - t1.b > 0 and t1.a is not null and t1.b is not null and t1.c is not null" - }, - "table": { - "table_name": "<subquery2>", - "access_type": "eq_ref", - "possible_keys": ["distinct_key"], - "key": "distinct_key", - "key_length": "12", - "used_key_parts": ["e", "f", "MAX(t2.g)"], - "ref": ["test.t1.a", "test.t1.b", "test.t1.c"], - "rows": 1, - "filtered": 100, - "materialized": { - "unique": 1, - "query_block": { - "select_id": 2, - "temporary_table": { - "table": { - "table_name": "t2", - "access_type": "ALL", - "rows": 12, - "filtered": 100, - "attached_condition": "t2.e < 5 and t2.e - t2.f > 0" + "nested_loop": [ + { + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 16, + "filtered": 100, + "attached_condition": "t1.a - t1.b > 0 and t1.a is not null and t1.b is not null and t1.c is not null" + } + }, + { + "table": { + "table_name": "<subquery2>", + "access_type": "eq_ref", + "possible_keys": ["distinct_key"], + "key": "distinct_key", + "key_length": "12", + "used_key_parts": ["e", "f", "MAX(t2.g)"], + "ref": ["test.t1.a", "test.t1.b", "test.t1.c"], + "rows": 1, + "filtered": 100, + "materialized": { + "unique": 1, + "query_block": { + "select_id": 2, + "temporary_table": { + "nested_loop": [ + { + "table": { + "table_name": "t2", + "access_type": "ALL", + "rows": 12, + "filtered": 100, + "attached_condition": "t2.e < 5 and t2.e - t2.f > 0" + } + } + ] + } } } } } - } + ] } } # conjunctive subformula using multiplication : pushing into WHERE @@ -2307,39 +2561,49 @@ EXPLAIN { "query_block": { "select_id": 1, - "table": { - "table_name": "t1", - "access_type": "ALL", - "rows": 16, - "filtered": 100, - "attached_condition": "t1.a * t1.b > 6 and t1.a is not null and t1.b is not null and t1.c is not null" - }, - "table": { - "table_name": "<subquery2>", - "access_type": "eq_ref", - "possible_keys": ["distinct_key"], - "key": "distinct_key", - "key_length": "12", - "used_key_parts": ["e", "f", "MAX(t2.g)"], - "ref": ["test.t1.a", "test.t1.b", "test.t1.c"], - "rows": 1, - "filtered": 100, - "materialized": { - "unique": 1, - "query_block": { - "select_id": 2, - "temporary_table": { - "table": { - "table_name": "t2", - "access_type": "ALL", - "rows": 12, - "filtered": 100, - "attached_condition": "t2.e < 5 and t2.e * t2.f > 6" + "nested_loop": [ + { + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 16, + "filtered": 100, + "attached_condition": "t1.a * t1.b > 6 and t1.a is not null and t1.b is not null and t1.c is not null" + } + }, + { + "table": { + "table_name": "<subquery2>", + "access_type": "eq_ref", + "possible_keys": ["distinct_key"], + "key": "distinct_key", + "key_length": "12", + "used_key_parts": ["e", "f", "MAX(t2.g)"], + "ref": ["test.t1.a", "test.t1.b", "test.t1.c"], + "rows": 1, + "filtered": 100, + "materialized": { + "unique": 1, + "query_block": { + "select_id": 2, + "temporary_table": { + "nested_loop": [ + { + "table": { + "table_name": "t2", + "access_type": "ALL", + "rows": 12, + "filtered": 100, + "attached_condition": "t2.e < 5 and t2.e * t2.f > 6" + } + } + ] + } } } } } - } + ] } } # conjunctive subformula using division : pushing into WHERE @@ -2397,39 +2661,49 @@ EXPLAIN { "query_block": { "select_id": 1, - "table": { - "table_name": "t1", - "access_type": "ALL", - "rows": 16, - "filtered": 100, - "attached_condition": "t1.b / t1.a > 2 and t1.a is not null and t1.b is not null and t1.c is not null" - }, - "table": { - "table_name": "<subquery2>", - "access_type": "eq_ref", - "possible_keys": ["distinct_key"], - "key": "distinct_key", - "key_length": "12", - "used_key_parts": ["e", "f", "MAX(t2.g)"], - "ref": ["test.t1.a", "test.t1.b", "test.t1.c"], - "rows": 1, - "filtered": 100, - "materialized": { - "unique": 1, - "query_block": { - "select_id": 2, - "temporary_table": { - "table": { - "table_name": "t2", - "access_type": "ALL", - "rows": 12, - "filtered": 100, - "attached_condition": "t2.e < 5 and t2.f / t2.e > 2" + "nested_loop": [ + { + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 16, + "filtered": 100, + "attached_condition": "t1.b / t1.a > 2 and t1.a is not null and t1.b is not null and t1.c is not null" + } + }, + { + "table": { + "table_name": "<subquery2>", + "access_type": "eq_ref", + "possible_keys": ["distinct_key"], + "key": "distinct_key", + "key_length": "12", + "used_key_parts": ["e", "f", "MAX(t2.g)"], + "ref": ["test.t1.a", "test.t1.b", "test.t1.c"], + "rows": 1, + "filtered": 100, + "materialized": { + "unique": 1, + "query_block": { + "select_id": 2, + "temporary_table": { + "nested_loop": [ + { + "table": { + "table_name": "t2", + "access_type": "ALL", + "rows": 12, + "filtered": 100, + "attached_condition": "t2.e < 5 and t2.f / t2.e > 2" + } + } + ] + } } } } } - } + ] } } # conjunctive subformula using BETWEEN : pushing into WHERE @@ -2491,39 +2765,49 @@ EXPLAIN { "query_block": { "select_id": 1, - "table": { - "table_name": "t1", - "access_type": "ALL", - "rows": 16, - "filtered": 100, - "attached_condition": "t1.a between 1 and 3 and t1.a is not null and t1.c is not null" - }, - "table": { - "table_name": "<subquery2>", - "access_type": "eq_ref", - "possible_keys": ["distinct_key"], - "key": "distinct_key", - "key_length": "8", - "used_key_parts": ["e", "MAX(t2.g)"], - "ref": ["test.t1.a", "test.t1.c"], - "rows": 1, - "filtered": 100, - "materialized": { - "unique": 1, - "query_block": { - "select_id": 2, - "temporary_table": { - "table": { - "table_name": "t2", - "access_type": "ALL", - "rows": 12, - "filtered": 100, - "attached_condition": "t2.e < 5 and t2.e between 1 and 3" + "nested_loop": [ + { + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 16, + "filtered": 100, + "attached_condition": "t1.a between 1 and 3 and t1.a is not null and t1.c is not null" + } + }, + { + "table": { + "table_name": "<subquery2>", + "access_type": "eq_ref", + "possible_keys": ["distinct_key"], + "key": "distinct_key", + "key_length": "8", + "used_key_parts": ["e", "MAX(t2.g)"], + "ref": ["test.t1.a", "test.t1.c"], + "rows": 1, + "filtered": 100, + "materialized": { + "unique": 1, + "query_block": { + "select_id": 2, + "temporary_table": { + "nested_loop": [ + { + "table": { + "table_name": "t2", + "access_type": "ALL", + "rows": 12, + "filtered": 100, + "attached_condition": "t2.e < 5 and t2.e between 1 and 3" + } + } + ] + } } } } } - } + ] } } # conjunctive subformula : pushing into HAVING of the IN subquery @@ -2583,58 +2867,72 @@ EXPLAIN { "query_block": { "select_id": 1, - "table": { - "table_name": "t1", - "access_type": "ALL", - "rows": 16, - "filtered": 100, - "attached_condition": "t1.c > 3 and t1.a is not null and t1.b is not null and t1.c is not null" - }, - "table": { - "table_name": "<subquery2>", - "access_type": "eq_ref", - "possible_keys": ["distinct_key"], - "key": "distinct_key", - "key_length": "12", - "used_key_parts": ["e", "MAX(v2.f)", "max_g"], - "ref": ["test.t1.a", "test.t1.b", "test.t1.c"], - "rows": 1, - "filtered": 100, - "materialized": { - "unique": 1, - "query_block": { - "select_id": 2, - "having_condition": "v2.max_g > 3", - "temporary_table": { - "table": { - "table_name": "<derived3>", - "access_type": "ALL", - "rows": 12, - "filtered": 100, - "attached_condition": "v2.e < 5", - "materialized": { - "query_block": { - "select_id": 3, - "having_condition": "max_g > 25", - "filesort": { - "sort_key": "t2.e", - "temporary_table": { - "table": { - "table_name": "t2", - "access_type": "ALL", - "rows": 12, - "filtered": 100, - "attached_condition": "t2.e < 5" + "nested_loop": [ + { + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 16, + "filtered": 100, + "attached_condition": "t1.c > 3 and t1.a is not null and t1.b is not null and t1.c is not null" + } + }, + { + "table": { + "table_name": "<subquery2>", + "access_type": "eq_ref", + "possible_keys": ["distinct_key"], + "key": "distinct_key", + "key_length": "12", + "used_key_parts": ["e", "MAX(v2.f)", "max_g"], + "ref": ["test.t1.a", "test.t1.b", "test.t1.c"], + "rows": 1, + "filtered": 100, + "materialized": { + "unique": 1, + "query_block": { + "select_id": 2, + "having_condition": "v2.max_g > 3", + "temporary_table": { + "nested_loop": [ + { + "table": { + "table_name": "<derived3>", + "access_type": "ALL", + "rows": 12, + "filtered": 100, + "attached_condition": "v2.e < 5", + "materialized": { + "query_block": { + "select_id": 3, + "having_condition": "max_g > 25", + "filesort": { + "sort_key": "t2.e", + "temporary_table": { + "nested_loop": [ + { + "table": { + "table_name": "t2", + "access_type": "ALL", + "rows": 12, + "filtered": 100, + "attached_condition": "t2.e < 5" + } + } + ] + } + } + } } } } - } + ] } } } } } - } + ] } } # conjunctive subformula : pushing into WHERE of the IN subquery @@ -2693,57 +2991,71 @@ EXPLAIN { "query_block": { "select_id": 1, - "table": { - "table_name": "t1", - "access_type": "ALL", - "rows": 16, - "filtered": 100, - "attached_condition": "t1.a > 1 and t1.a is not null and t1.b is not null and t1.c is not null" - }, - "table": { - "table_name": "<subquery2>", - "access_type": "eq_ref", - "possible_keys": ["distinct_key"], - "key": "distinct_key", - "key_length": "12", - "used_key_parts": ["e", "MAX(v2.f)", "max_g"], - "ref": ["test.t1.a", "test.t1.b", "test.t1.c"], - "rows": 1, - "filtered": 100, - "materialized": { - "unique": 1, - "query_block": { - "select_id": 2, - "temporary_table": { - "table": { - "table_name": "<derived3>", - "access_type": "ALL", - "rows": 12, - "filtered": 100, - "attached_condition": "v2.e < 5 and v2.e > 1", - "materialized": { - "query_block": { - "select_id": 3, - "having_condition": "max_g > 25", - "filesort": { - "sort_key": "t2.e", - "temporary_table": { - "table": { - "table_name": "t2", - "access_type": "ALL", - "rows": 12, - "filtered": 100, - "attached_condition": "t2.e < 5 and t2.e > 1" + "nested_loop": [ + { + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 16, + "filtered": 100, + "attached_condition": "t1.a > 1 and t1.a is not null and t1.b is not null and t1.c is not null" + } + }, + { + "table": { + "table_name": "<subquery2>", + "access_type": "eq_ref", + "possible_keys": ["distinct_key"], + "key": "distinct_key", + "key_length": "12", + "used_key_parts": ["e", "MAX(v2.f)", "max_g"], + "ref": ["test.t1.a", "test.t1.b", "test.t1.c"], + "rows": 1, + "filtered": 100, + "materialized": { + "unique": 1, + "query_block": { + "select_id": 2, + "temporary_table": { + "nested_loop": [ + { + "table": { + "table_name": "<derived3>", + "access_type": "ALL", + "rows": 12, + "filtered": 100, + "attached_condition": "v2.e < 5 and v2.e > 1", + "materialized": { + "query_block": { + "select_id": 3, + "having_condition": "max_g > 25", + "filesort": { + "sort_key": "t2.e", + "temporary_table": { + "nested_loop": [ + { + "table": { + "table_name": "t2", + "access_type": "ALL", + "rows": 12, + "filtered": 100, + "attached_condition": "t2.e < 5 and t2.e > 1" + } + } + ] + } + } + } } } } - } + ] } } } } } - } + ] } } # conjunctive subformula : pushing into WHERE and HAVING @@ -2803,58 +3115,72 @@ EXPLAIN { "query_block": { "select_id": 1, - "table": { - "table_name": "t1", - "access_type": "ALL", - "rows": 16, - "filtered": 100, - "attached_condition": "t1.a > 1 and t1.c < 100 and t1.a is not null and t1.b is not null and t1.c is not null" - }, - "table": { - "table_name": "<subquery2>", - "access_type": "eq_ref", - "possible_keys": ["distinct_key"], - "key": "distinct_key", - "key_length": "12", - "used_key_parts": ["e", "MAX(v2.f)", "max_g"], - "ref": ["test.t1.a", "test.t1.b", "test.t1.c"], - "rows": 1, - "filtered": 100, - "materialized": { - "unique": 1, - "query_block": { - "select_id": 2, - "having_condition": "v2.max_g < 100", - "temporary_table": { - "table": { - "table_name": "<derived3>", - "access_type": "ALL", - "rows": 12, - "filtered": 100, - "attached_condition": "v2.e < 5 and v2.e > 1", - "materialized": { - "query_block": { - "select_id": 3, - "having_condition": "max_g > 25", - "filesort": { - "sort_key": "t2.e", - "temporary_table": { - "table": { - "table_name": "t2", - "access_type": "ALL", - "rows": 12, - "filtered": 100, - "attached_condition": "t2.e < 5 and t2.e > 1" + "nested_loop": [ + { + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 16, + "filtered": 100, + "attached_condition": "t1.a > 1 and t1.c < 100 and t1.a is not null and t1.b is not null and t1.c is not null" + } + }, + { + "table": { + "table_name": "<subquery2>", + "access_type": "eq_ref", + "possible_keys": ["distinct_key"], + "key": "distinct_key", + "key_length": "12", + "used_key_parts": ["e", "MAX(v2.f)", "max_g"], + "ref": ["test.t1.a", "test.t1.b", "test.t1.c"], + "rows": 1, + "filtered": 100, + "materialized": { + "unique": 1, + "query_block": { + "select_id": 2, + "having_condition": "v2.max_g < 100", + "temporary_table": { + "nested_loop": [ + { + "table": { + "table_name": "<derived3>", + "access_type": "ALL", + "rows": 12, + "filtered": 100, + "attached_condition": "v2.e < 5 and v2.e > 1", + "materialized": { + "query_block": { + "select_id": 3, + "having_condition": "max_g > 25", + "filesort": { + "sort_key": "t2.e", + "temporary_table": { + "nested_loop": [ + { + "table": { + "table_name": "t2", + "access_type": "ALL", + "rows": 12, + "filtered": 100, + "attached_condition": "t2.e < 5 and t2.e > 1" + } + } + ] + } + } + } } } } - } + ] } } } } } - } + ] } } # conjunctive subformula : pushing into WHERE of the IN subquery @@ -2937,56 +3263,70 @@ EXPLAIN { "query_block": { "select_id": 1, - "table": { - "table_name": "t1", - "access_type": "ALL", - "rows": 16, - "filtered": 100, - "attached_condition": "t1.a > 1 and t1.a is not null and t1.b is not null and t1.c is not null" - }, - "table": { - "table_name": "<subquery2>", - "access_type": "eq_ref", - "possible_keys": ["distinct_key"], - "key": "distinct_key", - "key_length": "12", - "used_key_parts": ["e", "MAX(d_tab.f)", "max_g"], - "ref": ["test.t1.a", "test.t1.b", "test.t1.c"], - "rows": 1, - "filtered": 100, - "materialized": { - "unique": 1, - "query_block": { - "select_id": 2, - "temporary_table": { - "table": { - "table_name": "<derived3>", - "access_type": "ALL", - "rows": 12, - "filtered": 100, - "attached_condition": "d_tab.e < 5 and d_tab.e > 1", - "materialized": { - "query_block": { - "select_id": 3, - "having_condition": "max_g > 25 and t2.e < 5 and t2.e > 1", - "filesort": { - "sort_key": "t2.f", - "temporary_table": { - "table": { - "table_name": "t2", - "access_type": "ALL", - "rows": 12, - "filtered": 100 + "nested_loop": [ + { + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 16, + "filtered": 100, + "attached_condition": "t1.a > 1 and t1.a is not null and t1.b is not null and t1.c is not null" + } + }, + { + "table": { + "table_name": "<subquery2>", + "access_type": "eq_ref", + "possible_keys": ["distinct_key"], + "key": "distinct_key", + "key_length": "12", + "used_key_parts": ["e", "MAX(d_tab.f)", "max_g"], + "ref": ["test.t1.a", "test.t1.b", "test.t1.c"], + "rows": 1, + "filtered": 100, + "materialized": { + "unique": 1, + "query_block": { + "select_id": 2, + "temporary_table": { + "nested_loop": [ + { + "table": { + "table_name": "<derived3>", + "access_type": "ALL", + "rows": 12, + "filtered": 100, + "attached_condition": "d_tab.e < 5 and d_tab.e > 1", + "materialized": { + "query_block": { + "select_id": 3, + "having_condition": "max_g > 25 and t2.e < 5 and t2.e > 1", + "filesort": { + "sort_key": "t2.f", + "temporary_table": { + "nested_loop": [ + { + "table": { + "table_name": "t2", + "access_type": "ALL", + "rows": 12, + "filtered": 100 + } + } + ] + } + } + } } } } - } + ] } } } } } - } + ] } } # conjunctive subformula : pushing into HAVING of the derived table @@ -3088,56 +3428,70 @@ EXPLAIN { "query_block": { "select_id": 1, - "table": { - "table_name": "t1", - "access_type": "ALL", - "rows": 16, - "filtered": 100, - "attached_condition": "t1.a > 1 and t1.a is not null and t1.b is not null and t1.c is not null" - }, - "table": { - "table_name": "<subquery2>", - "access_type": "eq_ref", - "possible_keys": ["distinct_key"], - "key": "distinct_key", - "key_length": "12", - "used_key_parts": ["e", "MAX(d_tab.f)", "max_g"], - "ref": ["test.t1.a", "test.t1.b", "test.t1.c"], - "rows": 1, - "filtered": 100, - "materialized": { - "unique": 1, - "query_block": { - "select_id": 2, - "temporary_table": { - "table": { - "table_name": "<derived3>", - "access_type": "ALL", - "rows": 12, - "filtered": 100, - "attached_condition": "d_tab.e < 5 and d_tab.e > 1", - "materialized": { - "query_block": { - "select_id": 3, - "having_condition": "max_g > 25 and t2.e < 5 and t2.e > 1", - "filesort": { - "sort_key": "t2.f", - "temporary_table": { - "table": { - "table_name": "t2", - "access_type": "ALL", - "rows": 12, - "filtered": 100 + "nested_loop": [ + { + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 16, + "filtered": 100, + "attached_condition": "t1.a > 1 and t1.a is not null and t1.b is not null and t1.c is not null" + } + }, + { + "table": { + "table_name": "<subquery2>", + "access_type": "eq_ref", + "possible_keys": ["distinct_key"], + "key": "distinct_key", + "key_length": "12", + "used_key_parts": ["e", "MAX(d_tab.f)", "max_g"], + "ref": ["test.t1.a", "test.t1.b", "test.t1.c"], + "rows": 1, + "filtered": 100, + "materialized": { + "unique": 1, + "query_block": { + "select_id": 2, + "temporary_table": { + "nested_loop": [ + { + "table": { + "table_name": "<derived3>", + "access_type": "ALL", + "rows": 12, + "filtered": 100, + "attached_condition": "d_tab.e < 5 and d_tab.e > 1", + "materialized": { + "query_block": { + "select_id": 3, + "having_condition": "max_g > 25 and t2.e < 5 and t2.e > 1", + "filesort": { + "sort_key": "t2.f", + "temporary_table": { + "nested_loop": [ + { + "table": { + "table_name": "t2", + "access_type": "ALL", + "rows": 12, + "filtered": 100 + } + } + ] + } + } + } } } } - } + ] } } } } } - } + ] } } # conjunctive subformula : pushing into WHERE of the derived table @@ -3240,56 +3594,70 @@ EXPLAIN { "query_block": { "select_id": 1, - "table": { - "table_name": "t1", - "access_type": "ALL", - "rows": 16, - "filtered": 100, - "attached_condition": "t1.a > 1 and t1.a is not null and t1.b is not null and t1.c is not null" - }, - "table": { - "table_name": "<subquery2>", - "access_type": "eq_ref", - "possible_keys": ["distinct_key"], - "key": "distinct_key", - "key_length": "12", - "used_key_parts": ["e", "MAX(d_tab.f)", "max_g"], - "ref": ["test.t1.a", "test.t1.b", "test.t1.c"], - "rows": 1, - "filtered": 100, - "materialized": { - "unique": 1, - "query_block": { - "select_id": 2, - "temporary_table": { - "table": { - "table_name": "<derived3>", - "access_type": "ALL", - "rows": 12, - "filtered": 100, - "attached_condition": "d_tab.e < 5 and d_tab.e > 1", - "materialized": { - "query_block": { - "select_id": 3, - "having_condition": "max_g > 25 and t2.e < 5 and t2.e > 1", - "filesort": { - "sort_key": "t2.f", - "temporary_table": { - "table": { - "table_name": "t2", - "access_type": "ALL", - "rows": 12, - "filtered": 100 + "nested_loop": [ + { + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 16, + "filtered": 100, + "attached_condition": "t1.a > 1 and t1.a is not null and t1.b is not null and t1.c is not null" + } + }, + { + "table": { + "table_name": "<subquery2>", + "access_type": "eq_ref", + "possible_keys": ["distinct_key"], + "key": "distinct_key", + "key_length": "12", + "used_key_parts": ["e", "MAX(d_tab.f)", "max_g"], + "ref": ["test.t1.a", "test.t1.b", "test.t1.c"], + "rows": 1, + "filtered": 100, + "materialized": { + "unique": 1, + "query_block": { + "select_id": 2, + "temporary_table": { + "nested_loop": [ + { + "table": { + "table_name": "<derived3>", + "access_type": "ALL", + "rows": 12, + "filtered": 100, + "attached_condition": "d_tab.e < 5 and d_tab.e > 1", + "materialized": { + "query_block": { + "select_id": 3, + "having_condition": "max_g > 25 and t2.e < 5 and t2.e > 1", + "filesort": { + "sort_key": "t2.f", + "temporary_table": { + "nested_loop": [ + { + "table": { + "table_name": "t2", + "access_type": "ALL", + "rows": 12, + "filtered": 100 + } + } + ] + } + } + } } } } - } + ] } } } } } - } + ] } } # conjunctive subformula : pushing into WHERE and HAVING @@ -3374,70 +3742,86 @@ EXPLAIN { "query_block": { "select_id": 1, - "table": { - "table_name": "t3", - "access_type": "ALL", - "rows": 8, - "filtered": 100, - "attached_condition": "t3.x < 5 and t3.x is not null" - }, - "table": { - "table_name": "<derived2>", - "access_type": "ref", - "possible_keys": ["key0"], - "key": "key0", - "key_length": "5", - "used_key_parts": ["a"], - "ref": ["test.t3.x"], - "rows": 2, - "filtered": 100, - "attached_condition": "d_tab.max_c < 70", - "materialized": { - "query_block": { - "select_id": 2, - "having_condition": "max_c < 70", - "filesort": { - "sort_key": "t1.a", - "temporary_table": { - "table": { - "table_name": "t1", - "access_type": "ALL", - "rows": 16, - "filtered": 100, - "attached_condition": "t1.a > 1 and t1.a < 5 and t1.a is not null and t1.b is not null and t1.c is not null" - }, - "table": { - "table_name": "<subquery3>", - "access_type": "eq_ref", - "possible_keys": ["distinct_key"], - "key": "distinct_key", - "key_length": "12", - "used_key_parts": ["e", "f", "MAX(t2.g)"], - "ref": ["test.t1.a", "test.t1.b", "test.t1.c"], - "rows": 1, - "filtered": 100, - "materialized": { - "unique": 1, - "query_block": { - "select_id": 3, - "having_condition": "t2.f < 5", - "temporary_table": { + "nested_loop": [ + { + "table": { + "table_name": "t3", + "access_type": "ALL", + "rows": 8, + "filtered": 100, + "attached_condition": "t3.x < 5 and t3.x is not null" + } + }, + { + "table": { + "table_name": "<derived2>", + "access_type": "ref", + "possible_keys": ["key0"], + "key": "key0", + "key_length": "5", + "used_key_parts": ["a"], + "ref": ["test.t3.x"], + "rows": 2, + "filtered": 100, + "attached_condition": "d_tab.max_c < 70", + "materialized": { + "query_block": { + "select_id": 2, + "having_condition": "max_c < 70", + "filesort": { + "sort_key": "t1.a", + "temporary_table": { + "nested_loop": [ + { "table": { - "table_name": "t2", + "table_name": "t1", "access_type": "ALL", - "rows": 12, + "rows": 16, + "filtered": 100, + "attached_condition": "t1.a > 1 and t1.a < 5 and t1.a is not null and t1.b is not null and t1.c is not null" + } + }, + { + "table": { + "table_name": "<subquery3>", + "access_type": "eq_ref", + "possible_keys": ["distinct_key"], + "key": "distinct_key", + "key_length": "12", + "used_key_parts": ["e", "f", "MAX(t2.g)"], + "ref": ["test.t1.a", "test.t1.b", "test.t1.c"], + "rows": 1, "filtered": 100, - "attached_condition": "t2.e > 1 and t2.e < 5" + "materialized": { + "unique": 1, + "query_block": { + "select_id": 3, + "having_condition": "t2.f < 5", + "temporary_table": { + "nested_loop": [ + { + "table": { + "table_name": "t2", + "access_type": "ALL", + "rows": 12, + "filtered": 100, + "attached_condition": "t2.e > 1 and t2.e < 5" + } + } + ] + } + } + } } } - } + ] } } } } } } - } + ] } } # conjunctive subformula : pushing into WHERE of the derived table @@ -3543,70 +3927,86 @@ EXPLAIN { "query_block": { "select_id": 1, - "table": { - "table_name": "t3", - "access_type": "ALL", - "rows": 8, - "filtered": 100, - "attached_condition": "t3.x < 5 and t3.x is not null" - }, - "table": { - "table_name": "<derived2>", - "access_type": "ref", - "possible_keys": ["key0"], - "key": "key0", - "key_length": "5", - "used_key_parts": ["a"], - "ref": ["test.t3.x"], - "rows": 2, - "filtered": 100, - "attached_condition": "d_tab.max_c < 70", - "materialized": { - "query_block": { - "select_id": 2, - "having_condition": "max_c < 70", - "filesort": { - "sort_key": "t1.a", - "temporary_table": { - "table": { - "table_name": "t1", - "access_type": "ALL", - "rows": 16, - "filtered": 100, - "attached_condition": "t1.a > 1 and t1.a < 5 and t1.a is not null and t1.b is not null and t1.c is not null" - }, - "table": { - "table_name": "<subquery3>", - "access_type": "eq_ref", - "possible_keys": ["distinct_key"], - "key": "distinct_key", - "key_length": "12", - "used_key_parts": ["e", "f", "MAX(t2.g)"], - "ref": ["test.t1.a", "test.t1.b", "test.t1.c"], - "rows": 1, - "filtered": 100, - "materialized": { - "unique": 1, - "query_block": { - "select_id": 3, - "having_condition": "t2.f < 5", - "temporary_table": { + "nested_loop": [ + { + "table": { + "table_name": "t3", + "access_type": "ALL", + "rows": 8, + "filtered": 100, + "attached_condition": "t3.x < 5 and t3.x is not null" + } + }, + { + "table": { + "table_name": "<derived2>", + "access_type": "ref", + "possible_keys": ["key0"], + "key": "key0", + "key_length": "5", + "used_key_parts": ["a"], + "ref": ["test.t3.x"], + "rows": 2, + "filtered": 100, + "attached_condition": "d_tab.max_c < 70", + "materialized": { + "query_block": { + "select_id": 2, + "having_condition": "max_c < 70", + "filesort": { + "sort_key": "t1.a", + "temporary_table": { + "nested_loop": [ + { "table": { - "table_name": "t2", + "table_name": "t1", "access_type": "ALL", - "rows": 12, + "rows": 16, + "filtered": 100, + "attached_condition": "t1.a > 1 and t1.a < 5 and t1.a is not null and t1.b is not null and t1.c is not null" + } + }, + { + "table": { + "table_name": "<subquery3>", + "access_type": "eq_ref", + "possible_keys": ["distinct_key"], + "key": "distinct_key", + "key_length": "12", + "used_key_parts": ["e", "f", "MAX(t2.g)"], + "ref": ["test.t1.a", "test.t1.b", "test.t1.c"], + "rows": 1, "filtered": 100, - "attached_condition": "t2.e > 1 and t2.e < 5" + "materialized": { + "unique": 1, + "query_block": { + "select_id": 3, + "having_condition": "t2.f < 5", + "temporary_table": { + "nested_loop": [ + { + "table": { + "table_name": "t2", + "access_type": "ALL", + "rows": 12, + "filtered": 100, + "attached_condition": "t2.e > 1 and t2.e < 5" + } + } + ] + } + } + } } } - } + ] } } } } } } - } + ] } } # conjunctive subformula : pushing into WHERE @@ -3665,46 +4065,58 @@ EXPLAIN { "query_block": { "select_id": 1, - "table": { - "table_name": "t1", - "access_type": "ALL", - "rows": 16, - "filtered": 100, - "attached_condition": "t1.b > 1 and t1.b is not null and t1.c is not null" - }, - "table": { - "table_name": "<subquery2>", - "access_type": "eq_ref", - "possible_keys": ["distinct_key"], - "key": "distinct_key", - "key_length": "8", - "used_key_parts": ["f", "MAX(t2.g) OVER (PARTITION BY t2.f)"], - "ref": ["test.t1.b", "test.t1.c"], - "rows": 1, - "filtered": 100, - "materialized": { - "unique": 1, - "query_block": { - "select_id": 2, - "window_functions_computation": { - "sorts": { - "filesort": { - "sort_key": "t2.f" - } - }, - "temporary_table": { - "table": { - "table_name": "t2", - "access_type": "ALL", - "rows": 12, - "filtered": 100, - "attached_condition": "t2.e < 5 and t2.f > 1" + "nested_loop": [ + { + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 16, + "filtered": 100, + "attached_condition": "t1.b > 1 and t1.b is not null and t1.c is not null" + } + }, + { + "table": { + "table_name": "<subquery2>", + "access_type": "eq_ref", + "possible_keys": ["distinct_key"], + "key": "distinct_key", + "key_length": "8", + "used_key_parts": ["f", "MAX(t2.g) OVER (PARTITION BY t2.f)"], + "ref": ["test.t1.b", "test.t1.c"], + "rows": 1, + "filtered": 100, + "materialized": { + "unique": 1, + "query_block": { + "select_id": 2, + "window_functions_computation": { + "sorts": [ + { + "filesort": { + "sort_key": "t2.f" + } + } + ], + "temporary_table": { + "nested_loop": [ + { + "table": { + "table_name": "t2", + "access_type": "ALL", + "rows": 12, + "filtered": 100, + "attached_condition": "t2.e < 5 and t2.f > 1" + } + } + ] + } } } } } } - } + ] } } # conjunctive subformula : pushing into WHERE @@ -3757,47 +4169,62 @@ EXPLAIN { "query_block": { "select_id": 1, - "table": { - "table_name": "t1", - "access_type": "ALL", - "rows": 16, - "filtered": 100, - "attached_condition": "t1.b > 1 and t1.b is not null and t1.c is not null" - }, - "table": { - "table_name": "<subquery2>", - "access_type": "eq_ref", - "possible_keys": ["distinct_key"], - "key": "distinct_key", - "key_length": "12", - "used_key_parts": ["f", "CAST(SUM(t2.g) OVER (PARTITION BY t2.f) AS INT)"], - "ref": ["test.t1.b", "test.t1.c"], - "rows": 1, - "filtered": 100, - "attached_condition": "t1.c = `<subquery2>`.`CAST(SUM(t2.g) OVER (PARTITION BY t2.f) AS INT)`", - "materialized": { - "unique": 1, - "query_block": { - "select_id": 2, - "window_functions_computation": { - "sorts": { - "filesort": { - "sort_key": "t2.f" - } - }, - "temporary_table": { - "table": { - "table_name": "t2", - "access_type": "ALL", - "rows": 12, - "filtered": 100, - "attached_condition": "t2.e < 5 and t2.f > 1" + "nested_loop": [ + { + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 16, + "filtered": 100, + "attached_condition": "t1.b > 1 and t1.b is not null and t1.c is not null" + } + }, + { + "table": { + "table_name": "<subquery2>", + "access_type": "eq_ref", + "possible_keys": ["distinct_key"], + "key": "distinct_key", + "key_length": "12", + "used_key_parts": [ + "f", + "CAST(SUM(t2.g) OVER (PARTITION BY t2.f) AS INT)" + ], + "ref": ["test.t1.b", "test.t1.c"], + "rows": 1, + "filtered": 100, + "attached_condition": "t1.c = `<subquery2>`.`CAST(SUM(t2.g) OVER (PARTITION BY t2.f) AS INT)`", + "materialized": { + "unique": 1, + "query_block": { + "select_id": 2, + "window_functions_computation": { + "sorts": [ + { + "filesort": { + "sort_key": "t2.f" + } + } + ], + "temporary_table": { + "nested_loop": [ + { + "table": { + "table_name": "t2", + "access_type": "ALL", + "rows": 12, + "filtered": 100, + "attached_condition": "t2.e < 5 and t2.f > 1" + } + } + ] + } } } } } } - } + ] } } DROP TABLE t1,t2,t3; |