Bug #93430 Inconsistent output of SHOW STATUS LIKE 'Handler_read_key';
Submitted: 30 Nov 2018 15:05 Modified: 30 Nov 2018 15:18
Reporter: Przemysław Skibiński (OCA) Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: DML Severity:S3 (Non-critical)
Version:8.0.13 OS:Any
Assigned to: CPU Architecture:Any

[30 Nov 2018 15:05] Przemysław Skibiński
Description:
Repeating two times the following calls:

FLUSH STATUS;
SELECT * FROM t1 WHERE f2 <= 3 AND 3 <= f3;
SHOW STATUS LIKE 'Handler_read_key';

returns different values:

Handler_read_key	6
Handler_read_key	3

How to repeat:
CREATE TABLE t1
(
  f1 int NOT NULL DEFAULT '0',
  f2 int NOT NULL DEFAULT '0',
  f3 int NOT NULL DEFAULT '0',
  INDEX idx2(f3)
);

INSERT INTO t1(f2, f3) VALUES (3,4), (3,4);
set optimizer_switch='mrr=on,mrr_cost_based=off';

ANALYZE TABLE t1;

FLUSH STATUS;
SELECT * FROM t1 WHERE f2 <= 3 AND 3 <= f3;
SHOW STATUS LIKE 'Handler_read_key';

FLUSH STATUS;
SELECT * FROM t1 WHERE f2 <= 3 AND 3 <= f3;
SHOW STATUS LIKE 'Handler_read_key';

Suggested fix:
3 additional calls to ha_innobase::index_read come from dd::Column_statistics:

#0  ha_innobase::index_read (this=0x7fff381074e8, buf=0x7fff38171e38 "", key_ptr=0x7fff382f9500 "\001", key_len=775, find_flag=HA_READ_KEY_EXACT) at /github/percona-server/mysql-8.0/storage/innobase/handler/ha_innodb.cc:8672
#1  0x000055555844a4f8 in handler::index_read_map (this=0x7fff381074e8, buf=0x7fff38171e38 "", key=0x7fff382f9500 "\001", keypart_map=18446744073709551615, find_flag=HA_READ_KEY_EXACT) at /github/percona-server/mysql-8.0/sql/handler.h:4256
#2  0x0000555558443358 in handler::index_read_idx_map (this=0x7fff381074e8, buf=0x7fff38171e38 "", index=1, key=0x7fff382f9500 "\001", keypart_map=18446744073709551615, find_flag=HA_READ_KEY_EXACT) at /github/percona-server/mysql-8.0/sql/handler.cc:7280
#3  0x00005555584381c9 in handler::ha_index_read_idx_map (this=0x7fff381074e8, buf=0x7fff38171e38 "", index=1, key=0x7fff382f9500 "\001", keypart_map=18446744073709551615, find_flag=HA_READ_KEY_EXACT) at /github/percona-server/mysql-8.0/sql/handler.cc:3135
#4  0x0000555559452fc9 in dd::Raw_table::find_record (this=0x7fff38009f60, key=..., r=std::unique_ptr<dd::Raw_record> = {...}) at /github/percona-server/mysql-8.0/sql/dd/impl/raw/raw_table.cc:78
#5  0x000055555943d587 in dd::cache::Storage_adapter::get<dd::Item_name_key, dd::Column_statistics> (thd=0x7fff38494230, key=..., isolation=ISO_READ_COMMITTED, bypass_core_registry=false, object=0x7fffe8d18180) at /github/percona-server/mysql-8.0/sql/dd/impl/cache/storage_adapter.cc:160
#6  0x0000555559435b0c in dd::cache::Shared_dictionary_cache::get_uncached<dd::Item_name_key, dd::Column_statistics> (this=0x55555be05f40 <dd::cache::Shared_dictionary_cache::instance()::s_cache>, thd=0x7fff38494230, key=..., isolation=ISO_READ_COMMITTED, object=0x7fffe8d18180) at /github/percona-server/mysql-8.0/sql/dd/impl/cache/shared_dictionary_cache.cc:113
#7  0x00005555594358c1 in dd::cache::Shared_dictionary_cache::get<dd::Item_name_key, dd::Column_statistics> (this=0x55555be05f40 <dd::cache::Shared_dictionary_cache::instance()::s_cache>, thd=0x7fff38494230, key=..., element=0x7fffe8d181e8) at /github/percona-server/mysql-8.0/sql/dd/impl/cache/shared_dictionary_cache.cc:98
#8  0x0000555559353eff in dd::cache::Dictionary_client::acquire<dd::Item_name_key, dd::Column_statistics> (this=0x7fff382e50c0, key=..., object=0x7fffe8d18268, local_committed=0x7fffe8d18265, local_uncommitted=0x7fffe8d18266) at /github/percona-server/mysql-8.0/sql/dd/impl/cache/dictionary_client.cc:869
#9  0x000055555932a788 in dd::cache::Dictionary_client::acquire<dd::Column_statistics> (this=0x7fff382e50c0, object_name="test\037t1\037f1", object=0x7fffe8d18380) at /github/percona-server/mysql-8.0/sql/dd/impl/cache/dictionary_client.cc:1214
#10 0x0000555558453f4f in histograms::find_histogram (thd=0x7fff38494230, schema_name="test", table_name="t1", column_name="f1", histogram=0x7fffe8d18468) at /github/percona-server/mysql-8.0/sql/histograms/histogram.cc:1289
#11 0x00005555580275bc in read_histograms (thd=0x7fff38494230, share=0x7fff3852cc68, schema=0x7fff3800b458, table_def=0x7fff38100b68) at /github/percona-server/mysql-8.0/sql/sql_base.cc:585
#12 0x0000555558028072 in get_table_share (thd=0x7fff38494230, db=0x7fff38337378 "test", table_name=0x7fff381af478 "t1", key=0x7fff384748f7 "test", key_length=8, open_view=true, open_secondary=false) at /github/percona-server/mysql-8.0/sql/sql_base.cc:789
#13 0x00005555580284fc in get_table_share_with_discover (thd=0x7fff38494230, table_list=0x7fff38474528, key=0x7fff384748f7 "test", key_length=8, open_secondary=false, error=0x7fffe8d1899c) at /github/percona-server/mysql-8.0/sql/sql_base.cc:860
#14 0x000055555802df23 in open_table (thd=0x7fff38494230, table_list=0x7fff38474528, ot_ctx=0x7fffe8d18e40) at /github/percona-server/mysql-8.0/sql/sql_base.cc:3162
[30 Nov 2018 15:18] Miguel Solorzano
Thank you for the bug report.