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
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