Hi Varun, On Sat, Jun 17, 2017 at 05:35:41PM +0530, Varun wrote:
revision-id: ee73b331d97d33ae7aa1403a4ac7312f2d261754 (mariadb-10.1.20-323-gee73b33) parent(s): 056bab0880544d91ea67d18fe8db65b4f6625482 author: Varun Gupta committer: Varun Gupta timestamp: 2017-06-17 17:34:07 +0530 message:
MDEV-8840: ANALYZE FORMAT=JSON produces wrong data with BKA
The issue was that r_loops, r_rows and r_filtered in ANALYZE FORMAT= JSON were not calculated for the table on which we were performing the MRR scan in the BKA join Fixed this by adding respective counter in the JOIN_CACHE_MRR::open and JOIN_CACHE::next
So I apply the patch and run this example (the dataset is from explain_json.test, also pasted below): MariaDB [j5]> analyze format=json select * from t3,t4 where t3.a=t4.a and (t4.c+1 < t3.b+1)\G *************************** 1. row *************************** ANALYZE: { "query_block": { "select_id": 1, "r_loops": 1, "r_total_time_ms": 0.8859, "table": { "table_name": "t3", "access_type": "ALL", "r_loops": 1, "rows": 10, "r_rows": 10, "r_total_time_ms": 0.0202, "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": ["j5.t3.a"], "r_loops": 1, "rows": 1, "r_rows": 11, "r_total_time_ms": 0.0379, "filtered": 100, "r_filtered": 90.909 }, "buffer_type": "flat", "buffer_size": "256Kb", "join_type": "BKA", "mrr_type": "Rowid-ordered scan", "attached_condition": "t4.c + 1 < t3.b + 1", "r_filtered": 0 } } } I think, these value are wrong:
"r_rows": 11, "r_filtered": 90.909
r_rows is actually 10, r_filtered=100 (no attached condition) I guess wrong r_filtered is caused by r_rows being wrong. ## Dataset: set storage_engine=myisam; set default_storage_engine=myisam; 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; BR Sergei -- Sergei Petrunia, Software Developer MariaDB Corporation | Skype: sergefp | Blog: http://s.petrunia.net/blog