diff options
Diffstat (limited to 'mysql-test/main/stat_tables.result')
| -rw-r--r-- | mysql-test/main/stat_tables.result | 70 |
1 files changed, 70 insertions, 0 deletions
diff --git a/mysql-test/main/stat_tables.result b/mysql-test/main/stat_tables.result index be868e55e84..7fd0b5902ec 100644 --- a/mysql-test/main/stat_tables.result +++ b/mysql-test/main/stat_tables.result @@ -625,6 +625,76 @@ MAX(pk) NULL DROP TABLE t1; # +# 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; +USE test; +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; +# # MDEV-18899: Server crashes in Field::set_warning_truncated_wrong_value # set names utf8; |