revision-id: 45e3dace5a7b25cdc55c6b5ddb3f26bb5576ea71 (mariadb-10.1.38-112-g45e3dac) parent(s): f4b27400185bab217da11f8781eebb09a8502304 author: Igor Babaev committer: Igor Babaev timestamp: 2019-04-22 17:10:42 -0700 message: MDEV-17605 Statistics for InnoDB table is wrong if persistent statistics is used The command SHOW INDEXES ignored setting of the system variable use_stat_tables to the value of 'preferably' and and showed statistical data received from the engine. Similarly queries over the table STATISTICS from INFORMATION_SCHEMA ignored this setting. It happened because the function fill_schema_table_by_open() did not read any data from statistical tables. --- mysql-test/r/stat_tables.result | 69 ++++++++++++++++++++++++++++++++++ mysql-test/r/stat_tables_innodb.result | 69 ++++++++++++++++++++++++++++++++++ mysql-test/t/stat_tables.test | 54 ++++++++++++++++++++++++++ sql/sql_class.h | 3 ++ sql/sql_show.cc | 5 +++ sql/sql_statistics.cc | 5 ++- 6 files changed, 204 insertions(+), 1 deletion(-) diff --git a/mysql-test/r/stat_tables.result b/mysql-test/r/stat_tables.result index 3ebc3b4..bd3e9ed 100644 --- a/mysql-test/r/stat_tables.result +++ b/mysql-test/r/stat_tables.result @@ -625,3 +625,72 @@ MAX(pk) NULL DROP TABLE t1; set use_stat_tables=@save_use_stat_tables; +# +# MDEV-17605: SHOW INDEXES with use_stat_tables='preferably' +# +set use_stat_tables='preferably'; +CREATE DATABASE dbt3_s001; +use dbt3_s001; +set @save_optimizer_switch=@@optimizer_switch; +set optimizer_switch='extended_keys=off'; +select * from mysql.table_stats; +db_name table_name cardinality +dbt3_s001 lineitem 6005 +select * from mysql.index_stats; +db_name table_name index_name prefix_arity avg_frequency +dbt3_s001 lineitem PRIMARY 1 4.0033 +dbt3_s001 lineitem PRIMARY 2 1.0000 +dbt3_s001 lineitem i_l_shipdate 1 2.6500 +dbt3_s001 lineitem i_l_suppkey_partkey 1 30.0250 +dbt3_s001 lineitem i_l_suppkey_partkey 2 8.5786 +dbt3_s001 lineitem i_l_partkey 1 30.0250 +dbt3_s001 lineitem i_l_suppkey 1 600.5000 +dbt3_s001 lineitem i_l_receiptdate 1 2.6477 +dbt3_s001 lineitem i_l_orderkey 1 4.0033 +dbt3_s001 lineitem i_l_orderkey_quantity 1 4.0033 +dbt3_s001 lineitem i_l_orderkey_quantity 2 1.0404 +dbt3_s001 lineitem i_l_commitdate 1 2.7160 +SHOW INDEXES FROM lineitem; +Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment +lineitem 0 PRIMARY 1 l_orderkey A 1500 NULL NULL BTREE +lineitem 0 PRIMARY 2 l_linenumber A 6005 NULL NULL BTREE +lineitem 1 i_l_shipdate 1 l_shipDATE A 2266 NULL NULL YES BTREE +lineitem 1 i_l_suppkey_partkey 1 l_partkey A 200 NULL NULL YES BTREE +lineitem 1 i_l_suppkey_partkey 2 l_suppkey A 699 NULL NULL YES BTREE +lineitem 1 i_l_partkey 1 l_partkey A 200 NULL NULL YES BTREE +lineitem 1 i_l_suppkey 1 l_suppkey A 10 NULL NULL YES BTREE +lineitem 1 i_l_receiptdate 1 l_receiptDATE A 2268 NULL NULL YES BTREE +lineitem 1 i_l_orderkey 1 l_orderkey A 1500 NULL NULL BTREE +lineitem 1 i_l_orderkey_quantity 1 l_orderkey A 1500 NULL NULL BTREE +lineitem 1 i_l_orderkey_quantity 2 l_quantity A 5771 NULL NULL YES BTREE +lineitem 1 i_l_commitdate 1 l_commitDATE A 2210 NULL NULL YES BTREE +SELECT * FROM INFORMATION_SCHEMA.STATISTICS WHERE table_name='lineitem'; +TABLE_CATALOG TABLE_SCHEMA TABLE_NAME NON_UNIQUE INDEX_SCHEMA INDEX_NAME SEQ_IN_INDEX COLUMN_NAME COLLATION CARDINALITY SUB_PART PACKED NULLABLE INDEX_TYPE COMMENT INDEX_COMMENT +def dbt3_s001 lineitem 0 dbt3_s001 PRIMARY 1 l_orderkey A 1500 NULL NULL BTREE +def dbt3_s001 lineitem 0 dbt3_s001 PRIMARY 2 l_linenumber A 6005 NULL NULL BTREE +def dbt3_s001 lineitem 1 dbt3_s001 i_l_shipdate 1 l_shipDATE A 2266 NULL NULL YES BTREE +def dbt3_s001 lineitem 1 dbt3_s001 i_l_suppkey_partkey 1 l_partkey A 200 NULL NULL YES BTREE +def dbt3_s001 lineitem 1 dbt3_s001 i_l_suppkey_partkey 2 l_suppkey A 699 NULL NULL YES BTREE +def dbt3_s001 lineitem 1 dbt3_s001 i_l_partkey 1 l_partkey A 200 NULL NULL YES BTREE +def dbt3_s001 lineitem 1 dbt3_s001 i_l_suppkey 1 l_suppkey A 10 NULL NULL YES BTREE +def dbt3_s001 lineitem 1 dbt3_s001 i_l_receiptdate 1 l_receiptDATE A 2268 NULL NULL YES BTREE +def dbt3_s001 lineitem 1 dbt3_s001 i_l_orderkey 1 l_orderkey A 1500 NULL NULL BTREE +def dbt3_s001 lineitem 1 dbt3_s001 i_l_orderkey_quantity 1 l_orderkey A 1500 NULL NULL BTREE +def dbt3_s001 lineitem 1 dbt3_s001 i_l_orderkey_quantity 2 l_quantity A 5771 NULL NULL YES BTREE +def dbt3_s001 lineitem 1 dbt3_s001 i_l_commitdate 1 l_commitDATE A 2210 NULL NULL YES BTREE +SELECT +COUNT(DISTINCT l_orderkey), COUNT(DISTINCT l_orderkey,l_linenumber), +COUNT(DISTINCT l_shipDATE), +COUNT(DISTINCT l_partkey), COUNT(DISTINCT l_partkey,l_suppkey), +COUNT(DISTINCT l_suppkey), COUNT(DISTINCT l_receiptDATE), +COUNT(DISTINCT l_orderkey, l_quantity), COUNT(DISTINCT l_commitDATE) +FROM lineitem; +COUNT(DISTINCT l_orderkey) COUNT(DISTINCT l_orderkey,l_linenumber) COUNT(DISTINCT l_shipDATE) COUNT(DISTINCT l_partkey) COUNT(DISTINCT l_partkey,l_suppkey) COUNT(DISTINCT l_suppkey) COUNT(DISTINCT l_receiptDATE) COUNT(DISTINCT l_orderkey, l_quantity) COUNT(DISTINCT l_commitDATE) +1500 6005 2266 200 700 10 2268 5772 2211 +set optimizer_switch=@save_optimizer_switch; +DROP DATABASE dbt3_s001; +delete from mysql.table_stats; +delete from mysql.column_stats; +delete from mysql.index_stats; +set @save_optimizer_switch=@@optimizer_switch; +set use_stat_tables=@save_use_stat_tables; diff --git a/mysql-test/r/stat_tables_innodb.result b/mysql-test/r/stat_tables_innodb.result index b0f794c..579d194 100644 --- a/mysql-test/r/stat_tables_innodb.result +++ b/mysql-test/r/stat_tables_innodb.result @@ -652,5 +652,74 @@ MAX(pk) NULL DROP TABLE t1; set use_stat_tables=@save_use_stat_tables; +# +# MDEV-17605: SHOW INDEXES with use_stat_tables='preferably' +# +set use_stat_tables='preferably'; +CREATE DATABASE dbt3_s001; +use dbt3_s001; +set @save_optimizer_switch=@@optimizer_switch; +set optimizer_switch='extended_keys=off'; +select * from mysql.table_stats; +db_name table_name cardinality +dbt3_s001 lineitem 6005 +select * from mysql.index_stats; +db_name table_name index_name prefix_arity avg_frequency +dbt3_s001 lineitem PRIMARY 1 4.0033 +dbt3_s001 lineitem PRIMARY 2 1.0000 +dbt3_s001 lineitem i_l_shipdate 1 2.6500 +dbt3_s001 lineitem i_l_suppkey_partkey 1 30.0250 +dbt3_s001 lineitem i_l_suppkey_partkey 2 8.5786 +dbt3_s001 lineitem i_l_partkey 1 30.0250 +dbt3_s001 lineitem i_l_suppkey 1 600.5000 +dbt3_s001 lineitem i_l_receiptdate 1 2.6477 +dbt3_s001 lineitem i_l_orderkey 1 4.0033 +dbt3_s001 lineitem i_l_orderkey_quantity 1 4.0033 +dbt3_s001 lineitem i_l_orderkey_quantity 2 1.0404 +dbt3_s001 lineitem i_l_commitdate 1 2.7160 +SHOW INDEXES FROM lineitem; +Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment +lineitem 0 PRIMARY 1 l_orderkey A 1500 NULL NULL BTREE +lineitem 0 PRIMARY 2 l_linenumber A 6005 NULL NULL BTREE +lineitem 1 i_l_shipdate 1 l_shipDATE A 2266 NULL NULL YES BTREE +lineitem 1 i_l_suppkey_partkey 1 l_partkey A 200 NULL NULL YES BTREE +lineitem 1 i_l_suppkey_partkey 2 l_suppkey A 699 NULL NULL YES BTREE +lineitem 1 i_l_partkey 1 l_partkey A 200 NULL NULL YES BTREE +lineitem 1 i_l_suppkey 1 l_suppkey A 10 NULL NULL YES BTREE +lineitem 1 i_l_receiptdate 1 l_receiptDATE A 2268 NULL NULL YES BTREE +lineitem 1 i_l_orderkey 1 l_orderkey A 1500 NULL NULL BTREE +lineitem 1 i_l_orderkey_quantity 1 l_orderkey A 1500 NULL NULL BTREE +lineitem 1 i_l_orderkey_quantity 2 l_quantity A 5771 NULL NULL YES BTREE +lineitem 1 i_l_commitdate 1 l_commitDATE A 2210 NULL NULL YES BTREE +SELECT * FROM INFORMATION_SCHEMA.STATISTICS WHERE table_name='lineitem'; +TABLE_CATALOG TABLE_SCHEMA TABLE_NAME NON_UNIQUE INDEX_SCHEMA INDEX_NAME SEQ_IN_INDEX COLUMN_NAME COLLATION CARDINALITY SUB_PART PACKED NULLABLE INDEX_TYPE COMMENT INDEX_COMMENT +def dbt3_s001 lineitem 0 dbt3_s001 PRIMARY 1 l_orderkey A 1500 NULL NULL BTREE +def dbt3_s001 lineitem 0 dbt3_s001 PRIMARY 2 l_linenumber A 6005 NULL NULL BTREE +def dbt3_s001 lineitem 1 dbt3_s001 i_l_shipdate 1 l_shipDATE A 2266 NULL NULL YES BTREE +def dbt3_s001 lineitem 1 dbt3_s001 i_l_suppkey_partkey 1 l_partkey A 200 NULL NULL YES BTREE +def dbt3_s001 lineitem 1 dbt3_s001 i_l_suppkey_partkey 2 l_suppkey A 699 NULL NULL YES BTREE +def dbt3_s001 lineitem 1 dbt3_s001 i_l_partkey 1 l_partkey A 200 NULL NULL YES BTREE +def dbt3_s001 lineitem 1 dbt3_s001 i_l_suppkey 1 l_suppkey A 10 NULL NULL YES BTREE +def dbt3_s001 lineitem 1 dbt3_s001 i_l_receiptdate 1 l_receiptDATE A 2268 NULL NULL YES BTREE +def dbt3_s001 lineitem 1 dbt3_s001 i_l_orderkey 1 l_orderkey A 1500 NULL NULL BTREE +def dbt3_s001 lineitem 1 dbt3_s001 i_l_orderkey_quantity 1 l_orderkey A 1500 NULL NULL BTREE +def dbt3_s001 lineitem 1 dbt3_s001 i_l_orderkey_quantity 2 l_quantity A 5771 NULL NULL YES BTREE +def dbt3_s001 lineitem 1 dbt3_s001 i_l_commitdate 1 l_commitDATE A 2210 NULL NULL YES BTREE +SELECT +COUNT(DISTINCT l_orderkey), COUNT(DISTINCT l_orderkey,l_linenumber), +COUNT(DISTINCT l_shipDATE), +COUNT(DISTINCT l_partkey), COUNT(DISTINCT l_partkey,l_suppkey), +COUNT(DISTINCT l_suppkey), COUNT(DISTINCT l_receiptDATE), +COUNT(DISTINCT l_orderkey, l_quantity), COUNT(DISTINCT l_commitDATE) +FROM lineitem; +COUNT(DISTINCT l_orderkey) COUNT(DISTINCT l_orderkey,l_linenumber) COUNT(DISTINCT l_shipDATE) COUNT(DISTINCT l_partkey) COUNT(DISTINCT l_partkey,l_suppkey) COUNT(DISTINCT l_suppkey) COUNT(DISTINCT l_receiptDATE) COUNT(DISTINCT l_orderkey, l_quantity) COUNT(DISTINCT l_commitDATE) +1500 6005 2266 200 700 10 2268 5772 2211 +set optimizer_switch=@save_optimizer_switch; +DROP DATABASE dbt3_s001; +delete from mysql.table_stats; +delete from mysql.column_stats; +delete from mysql.index_stats; +set @save_optimizer_switch=@@optimizer_switch; +set use_stat_tables=@save_use_stat_tables; set optimizer_switch=@save_optimizer_switch_for_stat_tables_test; SET SESSION STORAGE_ENGINE=DEFAULT; diff --git a/mysql-test/t/stat_tables.test b/mysql-test/t/stat_tables.test index b89ab2b..97f9f08 100644 --- a/mysql-test/t/stat_tables.test +++ b/mysql-test/t/stat_tables.test @@ -402,3 +402,57 @@ SELECT MAX(pk) FROM t1; DROP TABLE t1; set use_stat_tables=@save_use_stat_tables; + +--echo # +--echo # MDEV-17605: SHOW INDEXES with use_stat_tables='preferably' +--echo # + +set use_stat_tables='preferably'; + +CREATE DATABASE dbt3_s001; + +use dbt3_s001; + +set @save_optimizer_switch=@@optimizer_switch; +set optimizer_switch='extended_keys=off'; + +--disable_query_log +--disable_result_log +--disable_warnings +--source include/dbt3_s001.inc +create index i_p_retailprice on part(p_retailprice); +delete from mysql.table_stats; +delete from mysql.column_stats; +delete from mysql.index_stats; +ANALYZE TABLE lineitem; +FLUSH TABLE mysql.table_stats, mysql.index_stats; +--enable_warnings +--enable_result_log +--enable_query_log + +select * from mysql.table_stats; +select * from mysql.index_stats; + +SHOW INDEXES FROM lineitem; + +SELECT * FROM INFORMATION_SCHEMA.STATISTICS WHERE table_name='lineitem'; + +SELECT + COUNT(DISTINCT l_orderkey), COUNT(DISTINCT l_orderkey,l_linenumber), + COUNT(DISTINCT l_shipDATE), + COUNT(DISTINCT l_partkey), COUNT(DISTINCT l_partkey,l_suppkey), + COUNT(DISTINCT l_suppkey), COUNT(DISTINCT l_receiptDATE), + COUNT(DISTINCT l_orderkey, l_quantity), COUNT(DISTINCT l_commitDATE) +FROM lineitem; + +set optimizer_switch=@save_optimizer_switch; + +DROP DATABASE dbt3_s001; + +delete from mysql.table_stats; +delete from mysql.column_stats; +delete from mysql.index_stats; + +set @save_optimizer_switch=@@optimizer_switch; + +set use_stat_tables=@save_use_stat_tables; diff --git a/sql/sql_class.h b/sql/sql_class.h index 6640e02..2517f5c 100644 --- a/sql/sql_class.h +++ b/sql/sql_class.h @@ -2199,6 +2199,9 @@ class THD :public Statement, */ bool create_tmp_table_for_derived; + /* The flag to force reading statistics from EITS tables */ + bool force_read_stats; + bool save_prep_leaf_list; /* container for handler's private per-connection data */ diff --git a/sql/sql_show.cc b/sql/sql_show.cc index 73f0f56..46914ea 100644 --- a/sql/sql_show.cc +++ b/sql/sql_show.cc @@ -4273,6 +4273,7 @@ fill_schema_table_by_open(THD *thd, bool is_show_fields_or_keys, 'only_view_structure()'. */ lex->sql_command= SQLCOM_SHOW_FIELDS; + thd->force_read_stats= get_schema_table_idx(schema_table) == SCH_STATISTICS; result= (open_temporary_tables(thd, table_list) || open_normal_and_derived_tables(thd, table_list, (MYSQL_OPEN_IGNORE_FLUSH | @@ -4280,6 +4281,10 @@ fill_schema_table_by_open(THD *thd, bool is_show_fields_or_keys, (can_deadlock ? MYSQL_OPEN_FAIL_ON_MDL_CONFLICT : 0)), DT_PREPARE | DT_CREATE)); + + (void) read_statistics_for_tables_if_needed(thd, table_list); + thd->force_read_stats= false; + /* Restore old value of sql_command back as it is being looked at in process_table() function. diff --git a/sql/sql_statistics.cc b/sql/sql_statistics.cc index d3a2094..b435971 100644 --- a/sql/sql_statistics.cc +++ b/sql/sql_statistics.cc @@ -2177,7 +2177,10 @@ inline bool statistics_for_command_is_needed(THD *thd) { if (thd->bootstrap || thd->variables.use_stat_tables == NEVER) return FALSE; - + + if (thd->force_read_stats) + return TRUE; + switch(thd->lex->sql_command) { case SQLCOM_SELECT: case SQLCOM_INSERT: