diff options
Diffstat (limited to 'mysql-test/main/in_subq_cond_pushdown.result')
| -rw-r--r-- | mysql-test/main/in_subq_cond_pushdown.result | 344 |
1 files changed, 340 insertions, 4 deletions
diff --git a/mysql-test/main/in_subq_cond_pushdown.result b/mysql-test/main/in_subq_cond_pushdown.result index f114fc68..d0431852 100644 --- a/mysql-test/main/in_subq_cond_pushdown.result +++ b/mysql-test/main/in_subq_cond_pushdown.result @@ -51,12 +51,15 @@ EXPLAIN { "query_block": { "select_id": 1, + "cost": "COST_REPLACED", "nested_loop": [ { "table": { "table_name": "t1", "access_type": "ALL", + "loops": 1, "rows": 16, + "cost": "COST_REPLACED", "filtered": 100, "attached_condition": "t1.c < 25 and t1.a is not null and t1.c is not null" } @@ -70,12 +73,15 @@ EXPLAIN "key_length": "8", "used_key_parts": ["e", "MAX(t2.g)"], "ref": ["test.t1.a", "test.t1.c"], + "loops": 16, "rows": 1, + "cost": "COST_REPLACED", "filtered": 100, "materialized": { "unique": 1, "query_block": { "select_id": 2, + "cost": "COST_REPLACED", "having_condition": "`MAX(t2.g)` < 25", "temporary_table": { "nested_loop": [ @@ -83,7 +89,9 @@ EXPLAIN "table": { "table_name": "t2", "access_type": "ALL", + "loops": 1, "rows": 12, + "cost": "COST_REPLACED", "filtered": 100, "attached_condition": "t2.e < 5" } @@ -150,12 +158,15 @@ EXPLAIN { "query_block": { "select_id": 1, + "cost": "COST_REPLACED", "nested_loop": [ { "table": { "table_name": "t1", "access_type": "ALL", + "loops": 1, "rows": 16, + "cost": "COST_REPLACED", "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" } @@ -169,12 +180,15 @@ EXPLAIN "key_length": "12", "used_key_parts": ["e", "f", "MAX(t2.g)"], "ref": ["test.t1.a", "test.t1.b", "test.t1.c"], + "loops": 16, "rows": 1, + "cost": "COST_REPLACED", "filtered": 100, "materialized": { "unique": 1, "query_block": { "select_id": 2, + "cost": "COST_REPLACED", "having_condition": "`MAX(t2.g)` > 55 and t2.f < 4", "temporary_table": { "nested_loop": [ @@ -182,7 +196,9 @@ EXPLAIN "table": { "table_name": "t2", "access_type": "ALL", + "loops": 1, "rows": 12, + "cost": "COST_REPLACED", "filtered": 100, "attached_condition": "t2.e < 5" } @@ -251,12 +267,15 @@ EXPLAIN { "query_block": { "select_id": 1, + "cost": "COST_REPLACED", "nested_loop": [ { "table": { "table_name": "t1", "access_type": "ALL", + "loops": 1, "rows": 16, + "cost": "COST_REPLACED", "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" } @@ -270,12 +289,15 @@ EXPLAIN "key_length": "12", "used_key_parts": ["e", "f", "MAX(t2.g)"], "ref": ["test.t1.a", "test.t1.b", "test.t1.c"], + "loops": 16, "rows": 1, + "cost": "COST_REPLACED", "filtered": 100, "materialized": { "unique": 1, "query_block": { "select_id": 2, + "cost": "COST_REPLACED", "having_condition": "`MAX(t2.g)` > 60 or `MAX(t2.g)` < 25", "temporary_table": { "nested_loop": [ @@ -283,7 +305,9 @@ EXPLAIN "table": { "table_name": "t2", "access_type": "ALL", + "loops": 1, "rows": 12, + "cost": "COST_REPLACED", "filtered": 100, "attached_condition": "t2.e < 5" } @@ -350,12 +374,15 @@ EXPLAIN { "query_block": { "select_id": 1, + "cost": "COST_REPLACED", "nested_loop": [ { "table": { "table_name": "t1", "access_type": "ALL", + "loops": 1, "rows": 16, + "cost": "COST_REPLACED", "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" } @@ -369,12 +396,15 @@ EXPLAIN "key_length": "12", "used_key_parts": ["e", "f", "MAX(t2.g)"], "ref": ["test.t1.a", "test.t1.b", "test.t1.c"], + "loops": 16, "rows": 1, + "cost": "COST_REPLACED", "filtered": 100, "materialized": { "unique": 1, "query_block": { "select_id": 2, + "cost": "COST_REPLACED", "having_condition": "(`MAX(t2.g)` > 60 or `MAX(t2.g)` < 25) and t2.f > 2", "temporary_table": { "nested_loop": [ @@ -382,7 +412,9 @@ EXPLAIN "table": { "table_name": "t2", "access_type": "ALL", + "loops": 1, "rows": 12, + "cost": "COST_REPLACED", "filtered": 100, "attached_condition": "t2.e < 5" } @@ -451,12 +483,15 @@ EXPLAIN { "query_block": { "select_id": 1, + "cost": "COST_REPLACED", "nested_loop": [ { "table": { "table_name": "t1", "access_type": "ALL", + "loops": 1, "rows": 16, + "cost": "COST_REPLACED", "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" } @@ -470,12 +505,15 @@ EXPLAIN "key_length": "12", "used_key_parts": ["e", "f", "MAX(t2.g)"], "ref": ["test.t1.a", "test.t1.b", "test.t1.c"], + "loops": 16, "rows": 1, + "cost": "COST_REPLACED", "filtered": 100, "materialized": { "unique": 1, "query_block": { "select_id": 2, + "cost": "COST_REPLACED", "having_condition": "t2.f > 1", "temporary_table": { "nested_loop": [ @@ -483,7 +521,9 @@ EXPLAIN "table": { "table_name": "t2", "access_type": "ALL", + "loops": 1, "rows": 12, + "cost": "COST_REPLACED", "filtered": 100, "attached_condition": "t2.e < 5" } @@ -550,12 +590,15 @@ EXPLAIN { "query_block": { "select_id": 1, + "cost": "COST_REPLACED", "nested_loop": [ { "table": { "table_name": "t1", "access_type": "ALL", + "loops": 1, "rows": 16, + "cost": "COST_REPLACED", "filtered": 100, "attached_condition": "t1.c > 20 and t1.a is not null and t1.c is not null" } @@ -569,12 +612,15 @@ EXPLAIN "key_length": "8", "used_key_parts": ["v1_x", "MAX(v1_y)"], "ref": ["test.t1.a", "test.t1.c"], + "loops": 16, "rows": 1, + "cost": "COST_REPLACED", "filtered": 100, "materialized": { "unique": 1, "query_block": { "select_id": 2, + "cost": "COST_REPLACED", "having_condition": "`MAX(v1_y)` > 20", "temporary_table": { "nested_loop": [ @@ -582,7 +628,9 @@ EXPLAIN "table": { "table_name": "t3", "access_type": "ALL", + "loops": 1, "rows": 8, + "cost": "COST_REPLACED", "filtered": 100, "attached_condition": "t3.x > 1 and t3.x <= 3" } @@ -650,12 +698,15 @@ EXPLAIN { "query_block": { "select_id": 1, + "cost": "COST_REPLACED", "nested_loop": [ { "table": { "table_name": "t3", "access_type": "ALL", + "loops": 1, "rows": 8, + "cost": "COST_REPLACED", "filtered": 100, "attached_condition": "t3.y > 20 and t3.x <= 3 and t3.y is not null" } @@ -665,7 +716,9 @@ EXPLAIN "table": { "table_name": "t1", "access_type": "ALL", + "loops": 8, "rows": 16, + "cost": "COST_REPLACED", "filtered": 100 }, "buffer_type": "flat", @@ -683,12 +736,15 @@ EXPLAIN "key_length": "8", "used_key_parts": ["e", "MAX(t2.g)"], "ref": ["test.t1.a", "test.t3.y"], + "loops": 128, "rows": 1, + "cost": "COST_REPLACED", "filtered": 100, "materialized": { "unique": 1, "query_block": { "select_id": 2, + "cost": "COST_REPLACED", "having_condition": "`MAX(t2.g)` > 20", "temporary_table": { "nested_loop": [ @@ -696,7 +752,9 @@ EXPLAIN "table": { "table_name": "t2", "access_type": "ALL", + "loops": 1, "rows": 12, + "cost": "COST_REPLACED", "filtered": 100, "attached_condition": "t2.e < 5" } @@ -765,12 +823,15 @@ EXPLAIN { "query_block": { "select_id": 1, + "cost": "COST_REPLACED", "nested_loop": [ { "table": { "table_name": "t1", "access_type": "ALL", + "loops": 1, "rows": 16, + "cost": "COST_REPLACED", "filtered": 100, "attached_condition": "t1.a < 2 and t1.a is not null and t1.c is not null" } @@ -784,19 +845,24 @@ EXPLAIN "key_length": "8", "used_key_parts": ["e", "MAX(t2.g)"], "ref": ["test.t1.a", "test.t1.c"], + "loops": 16, "rows": 1, + "cost": "COST_REPLACED", "filtered": 100, "materialized": { "unique": 1, "query_block": { "select_id": 2, + "cost": "COST_REPLACED", "temporary_table": { "nested_loop": [ { "table": { "table_name": "t2", "access_type": "ALL", + "loops": 1, "rows": 12, + "cost": "COST_REPLACED", "filtered": 100, "attached_condition": "t2.e < 5 and t2.e < 2" } @@ -865,12 +931,15 @@ EXPLAIN { "query_block": { "select_id": 1, + "cost": "COST_REPLACED", "nested_loop": [ { "table": { "table_name": "t1", "access_type": "ALL", + "loops": 1, "rows": 16, + "cost": "COST_REPLACED", "filtered": 100, "attached_condition": "t1.a > 2 and t1.a < 5 and t1.a is not null and t1.c is not null" } @@ -884,19 +953,24 @@ EXPLAIN "key_length": "8", "used_key_parts": ["e", "MAX(t2.g)"], "ref": ["test.t1.a", "test.t1.c"], + "loops": 16, "rows": 1, + "cost": "COST_REPLACED", "filtered": 100, "materialized": { "unique": 1, "query_block": { "select_id": 2, + "cost": "COST_REPLACED", "temporary_table": { "nested_loop": [ { "table": { "table_name": "t2", "access_type": "ALL", + "loops": 1, "rows": 12, + "cost": "COST_REPLACED", "filtered": 100, "attached_condition": "t2.e < 5 and t2.e > 2 and t2.e < 5" } @@ -967,12 +1041,15 @@ EXPLAIN { "query_block": { "select_id": 1, + "cost": "COST_REPLACED", "nested_loop": [ { "table": { "table_name": "t1", "access_type": "ALL", + "loops": 1, "rows": 16, + "cost": "COST_REPLACED", "filtered": 100, "attached_condition": "(t1.a < 2 or t1.a >= 4) and t1.a is not null and t1.c is not null" } @@ -986,19 +1063,24 @@ EXPLAIN "key_length": "8", "used_key_parts": ["e", "MAX(t2.g)"], "ref": ["test.t1.a", "test.t1.c"], + "loops": 16, "rows": 1, + "cost": "COST_REPLACED", "filtered": 100, "materialized": { "unique": 1, "query_block": { "select_id": 2, + "cost": "COST_REPLACED", "temporary_table": { "nested_loop": [ { "table": { "table_name": "t2", "access_type": "ALL", + "loops": 1, "rows": 12, + "cost": "COST_REPLACED", "filtered": 100, "attached_condition": "t2.e < 5 and (t2.e < 2 or t2.e >= 4)" } @@ -1065,12 +1147,15 @@ EXPLAIN { "query_block": { "select_id": 1, + "cost": "COST_REPLACED", "nested_loop": [ { "table": { "table_name": "t1", "access_type": "ALL", + "loops": 1, "rows": 16, + "cost": "COST_REPLACED", "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" } @@ -1084,19 +1169,24 @@ EXPLAIN "key_length": "12", "used_key_parts": ["e", "f", "MAX(t2.g)"], "ref": ["test.t1.a", "test.t1.b", "test.t1.c"], + "loops": 16, "rows": 1, + "cost": "COST_REPLACED", "filtered": 100, "materialized": { "unique": 1, "query_block": { "select_id": 2, + "cost": "COST_REPLACED", "temporary_table": { "nested_loop": [ { "table": { "table_name": "t2", "access_type": "ALL", + "loops": 1, "rows": 12, + "cost": "COST_REPLACED", "filtered": 100, "attached_condition": "t2.e < 5 and (t2.e < 2 or t2.e = 5) and t2.f > 3" } @@ -1163,12 +1253,15 @@ EXPLAIN { "query_block": { "select_id": 1, + "cost": "COST_REPLACED", "nested_loop": [ { "table": { "table_name": "t1", "access_type": "ALL", + "loops": 1, "rows": 16, + "cost": "COST_REPLACED", "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" } @@ -1182,19 +1275,24 @@ EXPLAIN "key_length": "12", "used_key_parts": ["e", "f", "MAX(t2.g)"], "ref": ["test.t1.a", "test.t1.b", "test.t1.c"], + "loops": 16, "rows": 1, + "cost": "COST_REPLACED", "filtered": 100, "materialized": { "unique": 1, "query_block": { "select_id": 2, + "cost": "COST_REPLACED", "temporary_table": { "nested_loop": [ { "table": { "table_name": "t2", "access_type": "ALL", + "loops": 1, "rows": 12, + "cost": "COST_REPLACED", "filtered": 100, "attached_condition": "t2.e < 5 and (t2.e < 2 or t2.e = 5) and t2.f > 3" } @@ -1261,12 +1359,15 @@ EXPLAIN { "query_block": { "select_id": 1, + "cost": "COST_REPLACED", "nested_loop": [ { "table": { "table_name": "t1", "access_type": "ALL", + "loops": 1, "rows": 16, + "cost": "COST_REPLACED", "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" } @@ -1280,19 +1381,24 @@ EXPLAIN "key_length": "12", "used_key_parts": ["e", "f", "MAX(t2.g)"], "ref": ["test.t1.a", "test.t1.b", "test.t1.c"], + "loops": 16, "rows": 1, + "cost": "COST_REPLACED", "filtered": 100, "materialized": { "unique": 1, "query_block": { "select_id": 2, + "cost": "COST_REPLACED", "temporary_table": { "nested_loop": [ { "table": { "table_name": "t2", "access_type": "ALL", + "loops": 1, "rows": 12, + "cost": "COST_REPLACED", "filtered": 100, "attached_condition": "t2.e < 5 and t2.e < 2" } @@ -1359,12 +1465,15 @@ EXPLAIN { "query_block": { "select_id": 1, + "cost": "COST_REPLACED", "nested_loop": [ { "table": { "table_name": "t1", "access_type": "ALL", + "loops": 1, "rows": 16, + "cost": "COST_REPLACED", "filtered": 100, "attached_condition": "t1.a = 1 and t1.d = 1 and t1.c is not null" } @@ -1378,18 +1487,23 @@ EXPLAIN "key_length": "8", "used_key_parts": ["e", "MAX(t2.g)"], "ref": ["const", "test.t1.c"], + "loops": 16, "rows": 1, + "cost": "COST_REPLACED", "filtered": 100, "materialized": { "unique": 1, "query_block": { "select_id": 2, + "cost": "COST_REPLACED", "nested_loop": [ { "table": { "table_name": "t2", "access_type": "ALL", + "loops": 1, "rows": 12, + "cost": "COST_REPLACED", "filtered": 100, "attached_condition": "t2.e = 1" } @@ -1455,12 +1569,15 @@ EXPLAIN { "query_block": { "select_id": 1, + "cost": "COST_REPLACED", "nested_loop": [ { "table": { "table_name": "t1", "access_type": "ALL", + "loops": 1, "rows": 16, + "cost": "COST_REPLACED", "filtered": 100, "attached_condition": "t1.d = t1.a and t1.a > 1 and t1.a is not null and t1.c is not null" } @@ -1474,19 +1591,24 @@ EXPLAIN "key_length": "8", "used_key_parts": ["e", "MAX(t2.g)"], "ref": ["test.t1.a", "test.t1.c"], + "loops": 16, "rows": 1, + "cost": "COST_REPLACED", "filtered": 100, "materialized": { "unique": 1, "query_block": { "select_id": 2, + "cost": "COST_REPLACED", "temporary_table": { "nested_loop": [ { "table": { "table_name": "t2", "access_type": "ALL", + "loops": 1, "rows": 12, + "cost": "COST_REPLACED", "filtered": 100, "attached_condition": "t2.e < 5 and t2.e > 1" } @@ -1551,12 +1673,15 @@ EXPLAIN { "query_block": { "select_id": 1, + "cost": "COST_REPLACED", "nested_loop": [ { "table": { "table_name": "t1", "access_type": "ALL", + "loops": 1, "rows": 16, + "cost": "COST_REPLACED", "filtered": 100, "attached_condition": "t1.a < 3 and t1.a is not null and t1.c is not null" } @@ -1570,19 +1695,24 @@ EXPLAIN "key_length": "8", "used_key_parts": ["v1_x", "MAX(v1_y)"], "ref": ["test.t1.a", "test.t1.c"], + "loops": 16, "rows": 1, + "cost": "COST_REPLACED", "filtered": 100, "materialized": { "unique": 1, "query_block": { "select_id": 2, + "cost": "COST_REPLACED", "temporary_table": { "nested_loop": [ { "table": { "table_name": "t3", "access_type": "ALL", + "loops": 1, "rows": 8, + "cost": "COST_REPLACED", "filtered": 100, "attached_condition": "t3.x > 1 and t3.x <= 3 and t3.x < 3" } @@ -1652,12 +1782,15 @@ EXPLAIN { "query_block": { "select_id": 1, + "cost": "COST_REPLACED", "nested_loop": [ { "table": { "table_name": "t3", "access_type": "ALL", + "loops": 1, "rows": 8, + "cost": "COST_REPLACED", "filtered": 100, "attached_condition": "t3.x < 2 and t3.y > 30 and t3.x <= 3 and t3.x is not null" } @@ -1667,7 +1800,9 @@ EXPLAIN "table": { "table_name": "t1", "access_type": "ALL", + "loops": 8, "rows": 16, + "cost": "COST_REPLACED", "filtered": 100 }, "buffer_type": "flat", @@ -1685,19 +1820,24 @@ EXPLAIN "key_length": "8", "used_key_parts": ["e", "MAX(t2.g)"], "ref": ["test.t3.x", "test.t1.c"], + "loops": 128, "rows": 1, + "cost": "COST_REPLACED", "filtered": 100, "materialized": { "unique": 1, "query_block": { "select_id": 2, + "cost": "COST_REPLACED", "temporary_table": { "nested_loop": [ { "table": { "table_name": "t2", "access_type": "ALL", + "loops": 1, "rows": 12, + "cost": "COST_REPLACED", "filtered": 100, "attached_condition": "t2.e < 5 and t2.e <= 3" } @@ -1765,12 +1905,15 @@ EXPLAIN { "query_block": { "select_id": 1, + "cost": "COST_REPLACED", "nested_loop": [ { "table": { "table_name": "t1", "access_type": "ALL", + "loops": 1, "rows": 16, + "cost": "COST_REPLACED", "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" } @@ -1784,12 +1927,15 @@ EXPLAIN "key_length": "12", "used_key_parts": ["e", "f", "MAX(t2.g)"], "ref": ["test.t1.a", "test.t1.b", "test.t1.c"], + "loops": 16, "rows": 1, + "cost": "COST_REPLACED", "filtered": 100, "materialized": { "unique": 1, "query_block": { "select_id": 2, + "cost": "COST_REPLACED", "having_condition": "t2.f < 3 or t2.f = 4", "temporary_table": { "nested_loop": [ @@ -1797,7 +1943,9 @@ EXPLAIN "table": { "table_name": "t2", "access_type": "ALL", + "loops": 1, "rows": 12, + "cost": "COST_REPLACED", "filtered": 100, "attached_condition": "t2.e < 5 and t2.e < 3" } @@ -1864,12 +2012,15 @@ EXPLAIN { "query_block": { "select_id": 1, + "cost": "COST_REPLACED", "nested_loop": [ { "table": { "table_name": "t1", "access_type": "ALL", + "loops": 1, "rows": 16, + "cost": "COST_REPLACED", "filtered": 100, "attached_condition": "t1.a + t1.c > 41 and t1.a is not null and t1.c is not null" } @@ -1883,12 +2034,15 @@ EXPLAIN "key_length": "8", "used_key_parts": ["e", "MAX(t2.g)"], "ref": ["test.t1.a", "test.t1.c"], + "loops": 16, "rows": 1, + "cost": "COST_REPLACED", "filtered": 100, "materialized": { "unique": 1, "query_block": { "select_id": 2, + "cost": "COST_REPLACED", "having_condition": "t2.e + `MAX(t2.g)` > 41", "temporary_table": { "nested_loop": [ @@ -1896,7 +2050,9 @@ EXPLAIN "table": { "table_name": "t2", "access_type": "ALL", + "loops": 1, "rows": 12, + "cost": "COST_REPLACED", "filtered": 100, "attached_condition": "t2.e < 5" } @@ -1965,12 +2121,15 @@ EXPLAIN { "query_block": { "select_id": 1, + "cost": "COST_REPLACED", "nested_loop": [ { "table": { "table_name": "t1", "access_type": "ALL", + "loops": 1, "rows": 16, + "cost": "COST_REPLACED", "filtered": 100, "attached_condition": "t1.c - t1.a < 35 and t1.a is not null and t1.c is not null" } @@ -1984,12 +2143,15 @@ EXPLAIN "key_length": "8", "used_key_parts": ["e", "MAX(t2.g)"], "ref": ["test.t1.a", "test.t1.c"], + "loops": 16, "rows": 1, + "cost": "COST_REPLACED", "filtered": 100, "materialized": { "unique": 1, "query_block": { "select_id": 2, + "cost": "COST_REPLACED", "having_condition": "`MAX(t2.g)` - t2.e < 35", "temporary_table": { "nested_loop": [ @@ -1997,7 +2159,9 @@ EXPLAIN "table": { "table_name": "t2", "access_type": "ALL", + "loops": 1, "rows": 12, + "cost": "COST_REPLACED", "filtered": 100, "attached_condition": "t2.e < 5" } @@ -2064,12 +2228,15 @@ EXPLAIN { "query_block": { "select_id": 1, + "cost": "COST_REPLACED", "nested_loop": [ { "table": { "table_name": "t1", "access_type": "ALL", + "loops": 1, "rows": 16, + "cost": "COST_REPLACED", "filtered": 100, "attached_condition": "t1.c * t1.a > 100 and t1.a is not null and t1.c is not null" } @@ -2083,12 +2250,15 @@ EXPLAIN "key_length": "8", "used_key_parts": ["e", "MAX(t2.g)"], "ref": ["test.t1.a", "test.t1.c"], + "loops": 16, "rows": 1, + "cost": "COST_REPLACED", "filtered": 100, "materialized": { "unique": 1, "query_block": { "select_id": 2, + "cost": "COST_REPLACED", "having_condition": "`MAX(t2.g)` * t2.e > 100", "temporary_table": { "nested_loop": [ @@ -2096,7 +2266,9 @@ EXPLAIN "table": { "table_name": "t2", "access_type": "ALL", + "loops": 1, "rows": 12, + "cost": "COST_REPLACED", "filtered": 100, "attached_condition": "t2.e < 5" } @@ -2167,12 +2339,15 @@ EXPLAIN { "query_block": { "select_id": 1, + "cost": "COST_REPLACED", "nested_loop": [ { "table": { "table_name": "t1", "access_type": "ALL", + "loops": 1, "rows": 16, + "cost": "COST_REPLACED", "filtered": 100, "attached_condition": "t1.c / t1.a > 30 and t1.a is not null and t1.c is not null" } @@ -2186,12 +2361,15 @@ EXPLAIN "key_length": "8", "used_key_parts": ["e", "MAX(t2.g)"], "ref": ["test.t1.a", "test.t1.c"], + "loops": 16, "rows": 1, + "cost": "COST_REPLACED", "filtered": 100, "materialized": { "unique": 1, "query_block": { "select_id": 2, + "cost": "COST_REPLACED", "having_condition": "`MAX(t2.g)` / t2.e > 30", "temporary_table": { "nested_loop": [ @@ -2199,7 +2377,9 @@ EXPLAIN "table": { "table_name": "t2", "access_type": "ALL", + "loops": 1, "rows": 12, + "cost": "COST_REPLACED", "filtered": 100, "attached_condition": "t2.e < 5" } @@ -2266,12 +2446,15 @@ EXPLAIN { "query_block": { "select_id": 1, + "cost": "COST_REPLACED", "nested_loop": [ { "table": { "table_name": "t1", "access_type": "ALL", + "loops": 1, "rows": 16, + "cost": "COST_REPLACED", "filtered": 100, "attached_condition": "t1.c between 50 and 100 and t1.a is not null and t1.c is not null" } @@ -2285,12 +2468,15 @@ EXPLAIN "key_length": "8", "used_key_parts": ["e", "MAX(t2.g)"], "ref": ["test.t1.a", "test.t1.c"], + "loops": 16, "rows": 1, + "cost": "COST_REPLACED", "filtered": 100, "materialized": { "unique": 1, "query_block": { "select_id": 2, + "cost": "COST_REPLACED", "having_condition": "`MAX(t2.g)` between 50 and 100", "temporary_table": { "nested_loop": [ @@ -2298,7 +2484,9 @@ EXPLAIN "table": { "table_name": "t2", "access_type": "ALL", + "loops": 1, "rows": 12, + "cost": "COST_REPLACED", "filtered": 100, "attached_condition": "t2.e < 5" } @@ -2365,12 +2553,15 @@ EXPLAIN { "query_block": { "select_id": 1, + "cost": "COST_REPLACED", "nested_loop": [ { "table": { "table_name": "t1", "access_type": "ALL", + "loops": 1, "rows": 16, + "cost": "COST_REPLACED", "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" } @@ -2384,19 +2575,24 @@ EXPLAIN "key_length": "12", "used_key_parts": ["e", "f", "MAX(t2.g)"], "ref": ["test.t1.a", "test.t1.b", "test.t1.c"], + "loops": 16, "rows": 1, + "cost": "COST_REPLACED", "filtered": 100, "materialized": { "unique": 1, "query_block": { "select_id": 2, + "cost": "COST_REPLACED", "temporary_table": { "nested_loop": [ { "table": { "table_name": "t2", "access_type": "ALL", + "loops": 1, "rows": 12, + "cost": "COST_REPLACED", "filtered": 100, "attached_condition": "t2.e < 5 and t2.e + t2.f > 5" } @@ -2463,12 +2659,15 @@ EXPLAIN { "query_block": { "select_id": 1, + "cost": "COST_REPLACED", "nested_loop": [ { "table": { "table_name": "t1", "access_type": "ALL", + "loops": 1, "rows": 16, + "cost": "COST_REPLACED", "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" } @@ -2482,19 +2681,24 @@ EXPLAIN "key_length": "12", "used_key_parts": ["e", "f", "MAX(t2.g)"], "ref": ["test.t1.a", "test.t1.b", "test.t1.c"], + "loops": 16, "rows": 1, + "cost": "COST_REPLACED", "filtered": 100, "materialized": { "unique": 1, "query_block": { "select_id": 2, + "cost": "COST_REPLACED", "temporary_table": { "nested_loop": [ { "table": { "table_name": "t2", "access_type": "ALL", + "loops": 1, "rows": 12, + "cost": "COST_REPLACED", "filtered": 100, "attached_condition": "t2.e < 5 and t2.e - t2.f > 0" } @@ -2561,12 +2765,15 @@ EXPLAIN { "query_block": { "select_id": 1, + "cost": "COST_REPLACED", "nested_loop": [ { "table": { "table_name": "t1", "access_type": "ALL", + "loops": 1, "rows": 16, + "cost": "COST_REPLACED", "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" } @@ -2580,19 +2787,24 @@ EXPLAIN "key_length": "12", "used_key_parts": ["e", "f", "MAX(t2.g)"], "ref": ["test.t1.a", "test.t1.b", "test.t1.c"], + "loops": 16, "rows": 1, + "cost": "COST_REPLACED", "filtered": 100, "materialized": { "unique": 1, "query_block": { "select_id": 2, + "cost": "COST_REPLACED", "temporary_table": { "nested_loop": [ { "table": { "table_name": "t2", "access_type": "ALL", + "loops": 1, "rows": 12, + "cost": "COST_REPLACED", "filtered": 100, "attached_condition": "t2.e < 5 and t2.e * t2.f > 6" } @@ -2661,12 +2873,15 @@ EXPLAIN { "query_block": { "select_id": 1, + "cost": "COST_REPLACED", "nested_loop": [ { "table": { "table_name": "t1", "access_type": "ALL", + "loops": 1, "rows": 16, + "cost": "COST_REPLACED", "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" } @@ -2680,19 +2895,24 @@ EXPLAIN "key_length": "12", "used_key_parts": ["e", "f", "MAX(t2.g)"], "ref": ["test.t1.a", "test.t1.b", "test.t1.c"], + "loops": 16, "rows": 1, + "cost": "COST_REPLACED", "filtered": 100, "materialized": { "unique": 1, "query_block": { "select_id": 2, + "cost": "COST_REPLACED", "temporary_table": { "nested_loop": [ { "table": { "table_name": "t2", "access_type": "ALL", + "loops": 1, "rows": 12, + "cost": "COST_REPLACED", "filtered": 100, "attached_condition": "t2.e < 5 and t2.f / t2.e > 2" } @@ -2765,12 +2985,15 @@ EXPLAIN { "query_block": { "select_id": 1, + "cost": "COST_REPLACED", "nested_loop": [ { "table": { "table_name": "t1", "access_type": "ALL", + "loops": 1, "rows": 16, + "cost": "COST_REPLACED", "filtered": 100, "attached_condition": "t1.a between 1 and 3 and t1.a is not null and t1.c is not null" } @@ -2784,19 +3007,24 @@ EXPLAIN "key_length": "8", "used_key_parts": ["e", "MAX(t2.g)"], "ref": ["test.t1.a", "test.t1.c"], + "loops": 16, "rows": 1, + "cost": "COST_REPLACED", "filtered": 100, "materialized": { "unique": 1, "query_block": { "select_id": 2, + "cost": "COST_REPLACED", "temporary_table": { "nested_loop": [ { "table": { "table_name": "t2", "access_type": "ALL", + "loops": 1, "rows": 12, + "cost": "COST_REPLACED", "filtered": 100, "attached_condition": "t2.e < 5 and t2.e between 1 and 3" } @@ -2867,12 +3095,15 @@ EXPLAIN { "query_block": { "select_id": 1, + "cost": "COST_REPLACED", "nested_loop": [ { "table": { "table_name": "t1", "access_type": "ALL", + "loops": 1, "rows": 16, + "cost": "COST_REPLACED", "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" } @@ -2886,12 +3117,15 @@ EXPLAIN "key_length": "12", "used_key_parts": ["e", "MAX(v2.f)", "max_g"], "ref": ["test.t1.a", "test.t1.b", "test.t1.c"], + "loops": 16, "rows": 1, + "cost": "COST_REPLACED", "filtered": 100, "materialized": { "unique": 1, "query_block": { "select_id": 2, + "cost": "COST_REPLACED", "having_condition": "v2.max_g > 3", "temporary_table": { "nested_loop": [ @@ -2899,12 +3133,15 @@ EXPLAIN "table": { "table_name": "<derived3>", "access_type": "ALL", + "loops": 1, "rows": 12, + "cost": "COST_REPLACED", "filtered": 100, "attached_condition": "v2.e < 5", "materialized": { "query_block": { "select_id": 3, + "cost": "COST_REPLACED", "having_condition": "max_g > 25", "filesort": { "sort_key": "t2.e", @@ -2914,7 +3151,9 @@ EXPLAIN "table": { "table_name": "t2", "access_type": "ALL", + "loops": 1, "rows": 12, + "cost": "COST_REPLACED", "filtered": 100, "attached_condition": "t2.e < 5" } @@ -2991,12 +3230,15 @@ EXPLAIN { "query_block": { "select_id": 1, + "cost": "COST_REPLACED", "nested_loop": [ { "table": { "table_name": "t1", "access_type": "ALL", + "loops": 1, "rows": 16, + "cost": "COST_REPLACED", "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" } @@ -3010,24 +3252,30 @@ EXPLAIN "key_length": "12", "used_key_parts": ["e", "MAX(v2.f)", "max_g"], "ref": ["test.t1.a", "test.t1.b", "test.t1.c"], + "loops": 16, "rows": 1, + "cost": "COST_REPLACED", "filtered": 100, "materialized": { "unique": 1, "query_block": { "select_id": 2, + "cost": "COST_REPLACED", "temporary_table": { "nested_loop": [ { "table": { "table_name": "<derived3>", "access_type": "ALL", + "loops": 1, "rows": 12, + "cost": "COST_REPLACED", "filtered": 100, "attached_condition": "v2.e < 5 and v2.e > 1", "materialized": { "query_block": { "select_id": 3, + "cost": "COST_REPLACED", "having_condition": "max_g > 25", "filesort": { "sort_key": "t2.e", @@ -3037,7 +3285,9 @@ EXPLAIN "table": { "table_name": "t2", "access_type": "ALL", + "loops": 1, "rows": 12, + "cost": "COST_REPLACED", "filtered": 100, "attached_condition": "t2.e < 5 and t2.e > 1" } @@ -3115,12 +3365,15 @@ EXPLAIN { "query_block": { "select_id": 1, + "cost": "COST_REPLACED", "nested_loop": [ { "table": { "table_name": "t1", "access_type": "ALL", + "loops": 1, "rows": 16, + "cost": "COST_REPLACED", "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" } @@ -3134,12 +3387,15 @@ EXPLAIN "key_length": "12", "used_key_parts": ["e", "MAX(v2.f)", "max_g"], "ref": ["test.t1.a", "test.t1.b", "test.t1.c"], + "loops": 16, "rows": 1, + "cost": "COST_REPLACED", "filtered": 100, "materialized": { "unique": 1, "query_block": { "select_id": 2, + "cost": "COST_REPLACED", "having_condition": "v2.max_g < 100", "temporary_table": { "nested_loop": [ @@ -3147,12 +3403,15 @@ EXPLAIN "table": { "table_name": "<derived3>", "access_type": "ALL", + "loops": 1, "rows": 12, + "cost": "COST_REPLACED", "filtered": 100, "attached_condition": "v2.e < 5 and v2.e > 1", "materialized": { "query_block": { "select_id": 3, + "cost": "COST_REPLACED", "having_condition": "max_g > 25", "filesort": { "sort_key": "t2.e", @@ -3162,7 +3421,9 @@ EXPLAIN "table": { "table_name": "t2", "access_type": "ALL", + "loops": 1, "rows": 12, + "cost": "COST_REPLACED", "filtered": 100, "attached_condition": "t2.e < 5 and t2.e > 1" } @@ -3263,12 +3524,15 @@ EXPLAIN { "query_block": { "select_id": 1, + "cost": "COST_REPLACED", "nested_loop": [ { "table": { "table_name": "t1", "access_type": "ALL", + "loops": 1, "rows": 16, + "cost": "COST_REPLACED", "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" } @@ -3282,24 +3546,30 @@ EXPLAIN "key_length": "12", "used_key_parts": ["e", "MAX(d_tab.f)", "max_g"], "ref": ["test.t1.a", "test.t1.b", "test.t1.c"], + "loops": 16, "rows": 1, + "cost": "COST_REPLACED", "filtered": 100, "materialized": { "unique": 1, "query_block": { "select_id": 2, + "cost": "COST_REPLACED", "temporary_table": { "nested_loop": [ { "table": { "table_name": "<derived3>", "access_type": "ALL", + "loops": 1, "rows": 12, + "cost": "COST_REPLACED", "filtered": 100, "attached_condition": "d_tab.e < 5 and d_tab.e > 1", "materialized": { "query_block": { "select_id": 3, + "cost": "COST_REPLACED", "having_condition": "max_g > 25 and t2.e < 5 and t2.e > 1", "filesort": { "sort_key": "t2.f", @@ -3309,7 +3579,9 @@ EXPLAIN "table": { "table_name": "t2", "access_type": "ALL", + "loops": 1, "rows": 12, + "cost": "COST_REPLACED", "filtered": 100 } } @@ -3428,12 +3700,15 @@ EXPLAIN { "query_block": { "select_id": 1, + "cost": "COST_REPLACED", "nested_loop": [ { "table": { "table_name": "t1", "access_type": "ALL", + "loops": 1, "rows": 16, + "cost": "COST_REPLACED", "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" } @@ -3447,24 +3722,30 @@ EXPLAIN "key_length": "12", "used_key_parts": ["e", "MAX(d_tab.f)", "max_g"], "ref": ["test.t1.a", "test.t1.b", "test.t1.c"], + "loops": 16, "rows": 1, + "cost": "COST_REPLACED", "filtered": 100, "materialized": { "unique": 1, "query_block": { "select_id": 2, + "cost": "COST_REPLACED", "temporary_table": { "nested_loop": [ { "table": { "table_name": "<derived3>", "access_type": "ALL", + "loops": 1, "rows": 12, + "cost": "COST_REPLACED", "filtered": 100, "attached_condition": "d_tab.e < 5 and d_tab.e > 1", "materialized": { "query_block": { "select_id": 3, + "cost": "COST_REPLACED", "having_condition": "max_g > 25 and t2.e < 5 and t2.e > 1", "filesort": { "sort_key": "t2.f", @@ -3474,7 +3755,9 @@ EXPLAIN "table": { "table_name": "t2", "access_type": "ALL", + "loops": 1, "rows": 12, + "cost": "COST_REPLACED", "filtered": 100 } } @@ -3594,12 +3877,15 @@ EXPLAIN { "query_block": { "select_id": 1, + "cost": "COST_REPLACED", "nested_loop": [ { "table": { "table_name": "t1", "access_type": "ALL", + "loops": 1, "rows": 16, + "cost": "COST_REPLACED", "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" } @@ -3613,24 +3899,30 @@ EXPLAIN "key_length": "12", "used_key_parts": ["e", "MAX(d_tab.f)", "max_g"], "ref": ["test.t1.a", "test.t1.b", "test.t1.c"], + "loops": 16, "rows": 1, + "cost": "COST_REPLACED", "filtered": 100, "materialized": { "unique": 1, "query_block": { "select_id": 2, + "cost": "COST_REPLACED", "temporary_table": { "nested_loop": [ { "table": { "table_name": "<derived3>", "access_type": "ALL", + "loops": 1, "rows": 12, + "cost": "COST_REPLACED", "filtered": 100, "attached_condition": "d_tab.e < 5 and d_tab.e > 1", "materialized": { "query_block": { "select_id": 3, + "cost": "COST_REPLACED", "having_condition": "max_g > 25 and t2.e < 5 and t2.e > 1", "filesort": { "sort_key": "t2.f", @@ -3640,7 +3932,9 @@ EXPLAIN "table": { "table_name": "t2", "access_type": "ALL", + "loops": 1, "rows": 12, + "cost": "COST_REPLACED", "filtered": 100 } } @@ -3718,7 +4012,7 @@ GROUP BY t1.a WHERE d_tab.a=t3.x AND d_tab.a<5 AND d_tab.max_c<70; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t3 ALL NULL NULL NULL NULL 8 Using where -1 PRIMARY <derived2> ref key0 key0 5 test.t3.x 2 Using where +1 PRIMARY <derived2> ref key0 key0 5 test.t3.x 1 Using where 2 DERIVED t1 ALL NULL NULL NULL NULL 16 Using where; Using temporary; Using filesort 2 DERIVED <subquery3> eq_ref distinct_key distinct_key 12 test.t1.a,test.t1.b,test.t1.c 1 3 MATERIALIZED t2 ALL NULL NULL NULL NULL 12 Using where; Using temporary @@ -3742,12 +4036,15 @@ EXPLAIN { "query_block": { "select_id": 1, + "cost": "COST_REPLACED", "nested_loop": [ { "table": { "table_name": "t3", "access_type": "ALL", + "loops": 1, "rows": 8, + "cost": "COST_REPLACED", "filtered": 100, "attached_condition": "t3.x < 5 and t3.x is not null" } @@ -3761,12 +4058,15 @@ EXPLAIN "key_length": "5", "used_key_parts": ["a"], "ref": ["test.t3.x"], - "rows": 2, + "loops": 8, + "rows": 1, + "cost": "COST_REPLACED", "filtered": 100, "attached_condition": "d_tab.max_c < 70", "materialized": { "query_block": { "select_id": 2, + "cost": "COST_REPLACED", "having_condition": "max_c < 70", "filesort": { "sort_key": "t1.a", @@ -3776,7 +4076,9 @@ EXPLAIN "table": { "table_name": "t1", "access_type": "ALL", + "loops": 1, "rows": 16, + "cost": "COST_REPLACED", "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" } @@ -3790,12 +4092,15 @@ EXPLAIN "key_length": "12", "used_key_parts": ["e", "f", "MAX(t2.g)"], "ref": ["test.t1.a", "test.t1.b", "test.t1.c"], + "loops": 16, "rows": 1, + "cost": "COST_REPLACED", "filtered": 100, "materialized": { "unique": 1, "query_block": { "select_id": 3, + "cost": "COST_REPLACED", "having_condition": "t2.f < 5", "temporary_table": { "nested_loop": [ @@ -3803,7 +4108,9 @@ EXPLAIN "table": { "table_name": "t2", "access_type": "ALL", + "loops": 1, "rows": 12, + "cost": "COST_REPLACED", "filtered": 100, "attached_condition": "t2.e > 1 and t2.e < 5" } @@ -3903,7 +4210,7 @@ GROUP BY t1.a WHERE d_tab.a=t3.x AND d_tab.a<5 AND d_tab.max_c<70; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t3 ALL NULL NULL NULL NULL 8 Using where -1 PRIMARY <derived2> ref key0 key0 5 test.t3.x 2 Using where +1 PRIMARY <derived2> ref key0 key0 5 test.t3.x 1 Using where 2 DERIVED t1 ALL NULL NULL NULL NULL 16 Using where; Using temporary; Using filesort 2 DERIVED <subquery3> eq_ref distinct_key distinct_key 12 test.t1.a,test.t1.b,test.t1.c 1 3 MATERIALIZED t2 ALL NULL NULL NULL NULL 12 Using where; Using temporary @@ -3927,12 +4234,15 @@ EXPLAIN { "query_block": { "select_id": 1, + "cost": "COST_REPLACED", "nested_loop": [ { "table": { "table_name": "t3", "access_type": "ALL", + "loops": 1, "rows": 8, + "cost": "COST_REPLACED", "filtered": 100, "attached_condition": "t3.x < 5 and t3.x is not null" } @@ -3946,12 +4256,15 @@ EXPLAIN "key_length": "5", "used_key_parts": ["a"], "ref": ["test.t3.x"], - "rows": 2, + "loops": 8, + "rows": 1, + "cost": "COST_REPLACED", "filtered": 100, "attached_condition": "d_tab.max_c < 70", "materialized": { "query_block": { "select_id": 2, + "cost": "COST_REPLACED", "having_condition": "max_c < 70", "filesort": { "sort_key": "t1.a", @@ -3961,7 +4274,9 @@ EXPLAIN "table": { "table_name": "t1", "access_type": "ALL", + "loops": 1, "rows": 16, + "cost": "COST_REPLACED", "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" } @@ -3975,12 +4290,15 @@ EXPLAIN "key_length": "12", "used_key_parts": ["e", "f", "MAX(t2.g)"], "ref": ["test.t1.a", "test.t1.b", "test.t1.c"], + "loops": 16, "rows": 1, + "cost": "COST_REPLACED", "filtered": 100, "materialized": { "unique": 1, "query_block": { "select_id": 3, + "cost": "COST_REPLACED", "having_condition": "t2.f < 5", "temporary_table": { "nested_loop": [ @@ -3988,7 +4306,9 @@ EXPLAIN "table": { "table_name": "t2", "access_type": "ALL", + "loops": 1, "rows": 12, + "cost": "COST_REPLACED", "filtered": 100, "attached_condition": "t2.e > 1 and t2.e < 5" } @@ -4065,12 +4385,15 @@ EXPLAIN { "query_block": { "select_id": 1, + "cost": "COST_REPLACED", "nested_loop": [ { "table": { "table_name": "t1", "access_type": "ALL", + "loops": 1, "rows": 16, + "cost": "COST_REPLACED", "filtered": 100, "attached_condition": "t1.b > 1 and t1.b is not null and t1.c is not null" } @@ -4084,12 +4407,15 @@ EXPLAIN "key_length": "8", "used_key_parts": ["f", "MAX(t2.g) OVER (PARTITION BY t2.f)"], "ref": ["test.t1.b", "test.t1.c"], + "loops": 16, "rows": 1, + "cost": "COST_REPLACED", "filtered": 100, "materialized": { "unique": 1, "query_block": { "select_id": 2, + "cost": "COST_REPLACED", "window_functions_computation": { "sorts": [ { @@ -4104,7 +4430,9 @@ EXPLAIN "table": { "table_name": "t2", "access_type": "ALL", + "loops": 1, "rows": 12, + "cost": "COST_REPLACED", "filtered": 100, "attached_condition": "t2.e < 5 and t2.f > 1" } @@ -4169,12 +4497,15 @@ EXPLAIN { "query_block": { "select_id": 1, + "cost": "COST_REPLACED", "nested_loop": [ { "table": { "table_name": "t1", "access_type": "ALL", + "loops": 1, "rows": 16, + "cost": "COST_REPLACED", "filtered": 100, "attached_condition": "t1.b > 1 and t1.b is not null and t1.c is not null" } @@ -4191,13 +4522,16 @@ EXPLAIN "CAST(SUM(t2.g) OVER (PARTITION BY t2.f) AS INT)" ], "ref": ["test.t1.b", "test.t1.c"], + "loops": 16, "rows": 1, + "cost": "COST_REPLACED", "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, + "cost": "COST_REPLACED", "window_functions_computation": { "sorts": [ { @@ -4212,7 +4546,9 @@ EXPLAIN "table": { "table_name": "t2", "access_type": "ALL", + "loops": 1, "rows": 12, + "cost": "COST_REPLACED", "filtered": 100, "attached_condition": "t2.e < 5 and t2.f > 1" } |