Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Not a Bug
-
10.2(EOL)
-
None
Description
Equality propagation is not working to a full extent when one of the members of the multi-equality is a field of derived table
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
|
(select a,b, COUNT(*) as CNT from t22 group by a,b) TBL,
|
t21
|
where
|
TBL.a=CNT AND CNT=t21.c AND CNT<3;
|
I would expect that in the above query:
- the optimizer constructs multiple-equal(TBL.a, CNT, t21.c)
- Then, CNT<3 is used to create t21.c < 3
However, this EXPLAIN plan shows that the latter didn't occur:
*************************** 1. row ***************************
|
EXPLAIN: {
|
"query_block": {
|
"select_id": 1,
|
"table": {
|
"table_name": "t21",
|
"access_type": "ALL",
|
"rows": 10,
|
"filtered": 100,
|
"attached_condition": "t21.c is not null"
|
},
|
"table": {
|
"table_name": "<derived2>",
|
"access_type": "ref",
|
"possible_keys": ["key0"],
|
"key": "key0",
|
"key_length": "8",
|
"used_key_parts": ["CNT"],
|
"ref": ["test2.t21.c"],
|
"rows": 2,
|
"filtered": 100,
|
"attached_condition": "TBL.a = TBL.CNT and TBL.CNT = t21.c and TBL.CNT < 3",
|
"materialized": {
|
"query_block": {
|
"select_id": 2,
|
"having_condition": "t22.a = CNT and CNT < 3",
|
"filesort": {
|
"sort_key": "t22.a, t22.b",
|
"temporary_table": {
|
"table": {
|
"table_name": "t22",
|
"access_type": "ALL",
|
"rows": 10,
|
"filtered": 100
|
}
|
}
|
}
|
}
|
}
|
}
|
}
|
}
|
Attachments
Activity
| Transition | Time In Source Status | Execution Times |
|---|
|
14h 33m | 1 |
