Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
10.1.8
-
None
-
10.1.9-1, 10.1.9-2, 10.2.4-1, 10.2.4-2, 10.1.22
Description
ANALYZE FORMAT=JSON may produce wrong incorrect runtime information for queries using BKA.
create table t1(a int);
|
insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
|
|
create table t2(a int);
|
insert into t2 select A.a + B.a* 10 + C.a * 100 from t1 A, t1 B, t1 C;
|
|
create table t3(a int, b int);
|
insert into t3 select a,a from t1;
|
|
create table t4(a int, b int, c int, filler char(100), key (a,b));
|
insert into t4 select a,a,a, 'filler-data' from t2;
|
set optimizer_switch='mrr=on';
|
set join_cache_level=6;
|
explain
|
select * from t3,t4 where t3.a=t4.a and (t4.b+1 <= t3.b+1);
|
+------+-------------+-------+------+---------------+------+---------+---------+------+------------------------------------------------------------------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+-------+------+---------------+------+---------+---------+------+------------------------------------------------------------------------------------+
|
| 1 | SIMPLE | t3 | ALL | NULL | NULL | NULL | NULL | 10 | Using where |
|
| 1 | SIMPLE | t4 | ref | a | a | 5 | j2.t3.a | 1 | Using index condition(BKA); Using join buffer (flat, BKA join); Rowid-ordered scan |
|
+------+-------------+-------+------+---------------+------+---------+---------+------+------------------------------------------------------------------------------------+
|
{
|
"query_block": {
|
"select_id": 1,
|
"r_loops": 1,
|
"r_total_time_ms": 3.0276,
|
"table": {
|
"table_name": "t3",
|
"access_type": "ALL",
|
"r_loops": 1,
|
"rows": 10,
|
"r_rows": 10,
|
"r_total_time_ms": 0.5097,
|
"filtered": 100,
|
"r_filtered": 100,
|
"attached_condition": "(t3.a is not null)"
|
},
|
"block-nl-join": {
|
"table": {
|
"table_name": "t4",
|
"access_type": "ref",
|
"possible_keys": ["a"],
|
"key": "a",
|
"key_length": "5",
|
"used_key_parts": ["a"],
|
"ref": ["j2.t3.a"],
|
"r_loops": 0,
|
"rows": 1,
|
"r_rows": null,
|
"r_total_time_ms": 0.8301,
|
"filtered": 100,
|
"r_filtered": null,
|
"index_condition_bka": "((t4.b + 1) <= (t3.b + 1))"
|
},
|
"buffer_type": "flat",
|
"buffer_size": "256Kb",
|
"join_type": "BKA",
|
"mrr_type": "Rowid-ordered scan",
|
"r_filtered": 100
|
}
|
}
|
}
|
Note that the second table has r_rows=null and r_filtered=null. We also get r_loops=0 (which means "never executed") although "r_total_time_ms": 0.8301, which means we spent time in this node.