|
Consider the query where the derived table is used in the IN subquery.
SELECT * FROM t1
|
WHERE t1.a>1 AND
|
(t1.a,t1.b,t1.c) IN
|
(
|
SELECT d_tab.e,d_tab.f,d_tab.max_g
|
FROM (
|
SELECT t2.e, t2.f, MAX(t2.g) AS max_g
|
FROM t2
|
GROUP BY t2.e
|
HAVING max_g>25
|
) as d_tab
|
WHERE d_tab.e<5
|
)
|
;
|
There the condition (d_tab.e<5) that is used in the WHERE clause of the IN subquery
should be pushed down into the WHERE clause of the derived table but the pushdown
isn't done. Explain in json format for this query:
| {
|
"query_block": {
|
"select_id": 1,
|
"table": {
|
"table_name": "<subquery2>",
|
"access_type": "ALL",
|
"possible_keys": ["distinct_key"],
|
"rows": 12,
|
"filtered": 100,
|
"materialized": {
|
"unique": 1,
|
"query_block": {
|
"select_id": 2,
|
"table": {
|
"table_name": "<derived3>",
|
"access_type": "ALL",
|
"rows": 12,
|
"filtered": 100,
|
"attached_condition": "d_tab.e > 1 and d_tab.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
|
}
|
}
|
}
|
}
|
}
|
}
|
}
|
}
|
},
|
"block-nl-join": {
|
"table": {
|
"table_name": "t1",
|
"access_type": "ALL",
|
"rows": 15,
|
"filtered": 100
|
},
|
"buffer_type": "flat",
|
"buffer_size": "256Kb",
|
"join_type": "BNL",
|
"attached_condition": "t1.a = d_tab.e and t1.b = d_tab.f and t1.c = d_tab.max_g"
|
}
|
}
|
} |
|
Pushdown also isn't down when the derived table is used in the materialized IN subquery that is converted to semijoin:
SELECT * FROM t1
|
WHERE t1.a>1 AND
|
(t1.a,t1.b,t1.c) IN
|
(
|
SELECT d_tab.e,MAX(d_tab.f),d_tab.max_g
|
FROM (
|
SELECT t2.e, t2.f, MAX(t2.g) AS max_g
|
FROM t2
|
GROUP BY t2.e
|
HAVING max_g>25
|
) as d_tab
|
WHERE d_tab.e<5
|
GROUP BY d_tab.e
|
)
|
;
|
Explain in json format for this query:
| {
|
"query_block": {
|
"select_id": 1,
|
"table": {
|
"table_name": "t1",
|
"access_type": "ALL",
|
"rows": 15,
|
"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",
|
"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
|
}
|
}
|
}
|
}
|
}
|
}
|
}
|
}
|
}
|
}
|
}
|
} |
|
|