Details
-
Task
-
Status: Open (View Workflow)
-
Major
-
Resolution: Unresolved
-
None
-
None
Description
Consider an example:
create table ten(a int);
|
insert into ten values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
|
 |
create table t21 (a int, b int, c int);
|
insert into t21 select a,a,a from ten;
|
create table t22 (a int, b int, c int);
|
insert into t22 select a,a,a from ten;
|
explain format=json
|
select *
|
from
|
t21 LEFT JOIN
|
(select a,b, COUNT(*) as CNT from t22 group by a,b) TBL
|
ON (t21.a=TBL.a AND TBL.a<5);
|
here, "TBL.a<5" can be pushed down into the subquery, but it is not. EXPLAIN EXTENDED shows:
"query_block": {
|
"select_id": 1,
|
"const_condition": "1",
|
"table": {
|
"table_name": "t21",
|
"access_type": "ALL",
|
"rows": 10,
|
"filtered": 100
|
},
|
"table": {
|
"table_name": "<derived2>",
|
"access_type": "ref",
|
"possible_keys": ["key0"],
|
"key": "key0",
|
"key_length": "5",
|
"used_key_parts": ["a"],
|
"ref": ["test.t21.a"],
|
"rows": 2,
|
"filtered": 100,
|
"attached_condition": "trigcond(trigcond(t21.a < 5 and t21.a is not null))",
|
"materialized": {
|
"query_block": {
|
"select_id": 2,
|
"filesort": {
|
"sort_key": "t22.a, t22.b",
|
"temporary_table": {
|
"table": {
|
"table_name": "t22",
|
"access_type": "ALL",
|
"rows": 10,
|
"filtered": 100
|
}
|
}
|
}
|
}
|
}
|
}
|
Attachments
Issue Links
- relates to
-
MDEV-12845 subquery with group by not using index
- Closed