Details
-
Bug
-
Status: Open (View Workflow)
-
Major
-
Resolution: Unresolved
-
12.0(EOL)
-
None
Description
Discovered this while working on https://github.com/MariaDB/server/pull/4019.
CREATE TABLE `t1000` ( |
`grp` int(11) DEFAULT NULL, |
`val` int(11) DEFAULT NULL, |
KEY `grp` (`grp`) |
);
|
insert into t1000 select A.seq, B.seq from seq_1_to_100 A, seq_1_to_10 B; |
|
|
create table one_k(a int); |
insert into one_k select seq from seq_1_to_1000; |
|
|
analyze table t1000; |
explain format=json select * from one_k T1, (select grp, count(*) from t1000 group by grp) TBL where TBL.grp=T1.a\G |
gives
{
|
"query_block": {
|
"select_id": 1,
|
"cost": 2.047789395,
|
"nested_loop": [
|
{
|
"table": {
|
"table_name": "T1",
|
"access_type": "ALL",
|
"loops": 1,
|
"rows": 1000,
|
"cost": 0.1764192,
|
"filtered": 100,
|
"attached_condition": "T1.a is not null"
|
}
|
},
|
{
|
"table": {
|
"table_name": "<derived2>",
|
"access_type": "ref",
|
"possible_keys": ["key0"],
|
"key": "key0",
|
"key_length": "5",
|
"used_key_parts": ["grp"],
|
"ref": ["j20.T1.a"],
|
"loops": 1000,
|
"rows": 10,
|
"cost": 1.871370195,
|
"filtered": 100,
|
"materialized": {
|
"query_block": {
|
"select_id": 2,
|
"cost": 0.009481154,
|
"filesort": {
|
"sort_key": "t1000.grp",
|
"temporary_table": {
|
"nested_loop": [
|
{
|
"table": {
|
"table_name": "t1000",
|
"access_type": "index",
|
"possible_keys": ["grp"],
|
"key": "grp",
|
"key_length": "5",
|
"used_key_parts": ["grp"],
|
"loops": 1,
|
"rows": 1000,
|
"cost": 0.15333092,
|
"filtered": 100,
|
"using_index": true
|
}
|
}
|
]
|
}
|
}
|
}
|
}
|
}
|
}
|
]
|
}
|
}
|
for select_id=2, why query_block.cost=0.009481154 is less than nested_loop[1].table.cost=0.15333092 ?
One can do set optimizer_switch='derived_with_keys=off'; and still observe a similar effect.