mirror/mariadb
1
0
Fork 0
mirror of https://github.com/MariaDB/server.git synced 2025-11-04 04:46:15 +01:00
Code Issues Projects Releases Packages Wiki Activity
mariadb/mysql-test/main/selectivity_innodb_notembedded.result

353 lines
17 KiB
Text
Raw Permalink Blame History

SET SESSION STORAGE_ENGINE='InnoDB';
Warnings:
Warning 1287 '@@storage_engine' is deprecated and will be removed in a future release. Please use '@@default_storage_engine' instead
set @save_optimizer_switch_for_selectivity_test=@@optimizer_switch;
set optimizer_switch='extended_keys=on';
drop table if exists t0,t1,t2,t3;
select @@global.use_stat_tables;
@@global.use_stat_tables
COMPLEMENTARY
select @@session.use_stat_tables;
@@session.use_stat_tables
COMPLEMENTARY
set @save_use_stat_tables=@@use_stat_tables;
set use_stat_tables='preferably';
set @save_optimizer_use_condition_selectivity=@@optimizer_use_condition_selectivity;
set @save_histogram_size=@@histogram_size;
set @save_histogram_type=@@histogram_type;
set join_cache_level=2;
set @@global.histogram_size=0,@@local.histogram_size=0;
set histogram_type='single_prec_hb';
set optimizer_use_condition_selectivity=3;
#
# MDEV-31067: selectivity_from_histogram >1.0 for a DOUBLE_PREC_HB histogram
#
create table t0(a int);
insert into t0 select 1 from seq_1_to_78;
create table t1(a int);
insert into t1 select 1 from seq_1_to_26;
create table t10 (a int);
insert into t10 select 0 from t0, seq_1_to_4;
insert into t10 select 8693 from t1;
insert into t10 select 8694 from t1;
insert into t10 select 8695 from t1;
insert into t10 select 34783 from t1;
insert into t10 select 34784 from t1;
insert into t10 select 34785 from t1;
insert into t10 select 34785 from t0, seq_1_to_8;
insert into t10 select 65214 from t1;
insert into t10 select 65215 from t1;
insert into t10 select 65216 from t1;
insert into t10 select 65216 from t0, seq_1_to_52;
insert into t10 select 65217 from t1;
insert into t10 select 65218 from t1;
insert into t10 select 65219 from t1;
insert into t10 select 65219 from t0;
insert into t10 select 73913 from t1;
insert into t10 select 73914 from t1;
insert into t10 select 73915 from t1;
insert into t10 select 73915 from t0, seq_1_to_40;
insert into t10 select 78257 from t1;
insert into t10 select 78258 from t1;
insert into t10 select 78259 from t1;
insert into t10 select 91300 from t1;
insert into t10 select 91301 from t1;
insert into t10 select 91302 from t1;
insert into t10 select 91302 from t0, seq_1_to_6;
insert into t10 select 91303 from t1;
insert into t10 select 91304 from t1;
insert into t10 select 91305 from t1;
insert into t10 select 91305 from t0, seq_1_to_8;
insert into t10 select 99998 from t1;
insert into t10 select 99999 from t1;
insert into t10 select 100000 from t1;
set use_stat_tables=preferably;
analyze table t10 persistent for all;
Table Op Msg_type Msg_text
test.t10 analyze status Engine-independent statistics collected
test.t10 analyze status OK
flush tables;
set @tmp=@@optimizer_trace;
set optimizer_trace=1;
explain select * from t10 where a in (91303);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t10 ALL NULL NULL NULL NULL 9984 Using where
# Must have selectivity_from_histogram <= 1.0:
select json_detailed(json_extract(trace, '$**.selectivity_for_columns')) as sel
from information_schema.optimizer_trace;
sel
[
[
{
"column_name": "a",
"ranges":
["91303 <= a <= 91303"],
"selectivity_from_histogram": 0.035714283
}
]
]
set optimizer_trace=@tmp;
drop table t0,t1,t10;
set histogram_size=@save_histogram_size;
#
# End of 10.4 tests
#
#
# MDEV-33314: Crash inside calculate_cond_selectivity_for_table() with many columns
#
set optimizer_use_condition_selectivity= 4;
set use_stat_tables= preferably;
#
# create table t1 (col0 int, col1 int, col2 int, ...);
#
$create_tbl;
#
# insert into t1 select seq, ... seq from seq_1_to_10;
#
$insert_cmd;
analyze table t1 persistent for all;
Table Op Msg_type Msg_text
test.t1 analyze status Engine-independent statistics collected
test.t1 analyze status OK
set @trace_tmp=@@optimizer_trace;
set optimizer_trace=1;
#
# Basic testcase: don't crash for many-column selectivity
# explain extended select * from t1 where col0>1 and col1>1 and col2>1 and ...
#
$query_tbl;
EXPLAIN
{
"query_block": {
"select_id": 1,
"table": {
"table_name": "t1",
"access_type": "ALL",
"rows": 100,
"filtered": 53.32928848,
"attached_condition": "t1.col0 > 1 and t1.col1 > 1 and t1.col2 > 1 and t1.col3 > 1 and t1.col4 > 1 and t1.col5 > 1 and t1.col6 > 1 and t1.col7 > 1 and t1.col8 > 1 and t1.col9 > 1 and t1.col10 > 1 and t1.col11 > 1 and t1.col12 > 1 and t1.col13 > 1 and t1.col14 > 1 and t1.col15 > 1 and t1.col16 > 1 and t1.col17 > 1 and t1.col18 > 1 and t1.col19 > 1 and t1.col20 > 1 and t1.col21 > 1 and t1.col22 > 1 and t1.col23 > 1 and t1.col24 > 1 and t1.col25 > 1 and t1.col26 > 1 and t1.col27 > 1 and t1.col28 > 1 and t1.col29 > 1 and t1.col30 > 1 and t1.col31 > 1 and t1.col32 > 1 and t1.col33 > 1 and t1.col34 > 1 and t1.col35 > 1 and t1.col36 > 1 and t1.col37 > 1 and t1.col38 > 1 and t1.col39 > 1 and t1.col40 > 1 and t1.col41 > 1 and t1.col42 > 1 and t1.col43 > 1 and t1.col44 > 1 and t1.col45 > 1 and t1.col46 > 1 and t1.col47 > 1 and t1.col48 > 1 and t1.col49 > 1 and t1.col50 > 1 and t1.col51 > 1 and t1.col52 > 1 and t1.col53 > 1 and t1.col54 > 1 and t1.col55 > 1 and t1.col56 > 1 and t1.col57 > 1 and t1.col58 > 1 and t1.col59 > 1 and t1.col60 > 1 and t1.col61 > 1 and t1.col62 > 1 and t1.col63 > 1 and t1.col64 > 1 and t1.col65 > 1 and t1.col66 > 1 and t1.col67 > 1 and t1.col68 > 1 and t1.col69 > 1 and t1.col70 > 1 and t1.col71 > 1 and t1.col72 > 1 and t1.col73 > 1 and t1.col74 > 1 and t1.col75 > 1 and t1.col76 > 1 and t1.col77 > 1 and t1.col78 > 1 and t1.col79 > 1 and t1.col80 > 1 and t1.col81 > 1 and t1.col82 > 1 and t1.col83 > 1 and t1.col84 > 1 and t1.col85 > 1 and t1.col86 > 1 and t1.col87 > 1 and t1.col88 > 1 and t1.col89 > 1 and t1.col90 > 1 and t1.col91 > 1 and t1.col92 > 1 and t1.col93 > 1 and t1.col94 > 1 and t1.col95 > 1 and t1.col96 > 1 and t1.col97 > 1 and t1.col98 > 1 and t1.col99 > 1 and t1.col100 > 1 and t1.col101 > 1 and t1.col102 > 1 and t1.col103 > 1 and t1.col104 > 1 and t1.col105 > 1 and t1.col106 > 1 and t1.col107 > 1 and t1.col108 > 1 and t1.col109 > 1 and t1.col110 > 1 and t1.col111 > 1 and t1.col112 > 1 and t1.col113 > 1 and t1.col114 > 1 and t1.col115 > 1 and t1.col116 > 1 and t1.col117 > 1 and t1.col118 > 1 and t1.col119 > 1 and t1.col120 > 1 and t1.col121 > 1 and t1.col122 > 1 and t1.col123 > 1 and t1.col124 > 1 and t1.col125 > 1 and t1.col126 > 1 and t1.col127 > 1 and t1.col128 > 1 and t1.col129 > 1 and t1.col130 > 1 and t1.col131 > 1 and t1.col132 > 1 and t1.col133 > 1 and t1.col134 > 1 and t1.col135 > 1 and t1.col136 > 1 and t1.col137 > 1 and t1.col138 > 1 and t1.col139 > 1 and t1.col140 > 1 and t1.col141 > 1 and t1.col142 > 1 and t1.col143 > 1 and t1.col144 > 1 and t1.col145 > 1 and t1.col146 > 1 and t1.col147 > 1 and t1.col148 > 1 and t1.col149 > 1 and t1.col150 > 1 and t1.col151 > 1 and t1.col152 > 1 and t1.col153 > 1 and t1.col154 > 1 and t1.col155 > 1 and t1.col156 > 1 and t1.col157 > 1 and t1.col158 > 1 and t1.col159 > 1"
}
}
}
select
json_detailed(json_extract(trace,'$**.selectivity_for_columns[0]')) as JS
from
information_schema.optimizer_trace;
JS
[
{
"column_name": "col0",
"ranges":
["1 < col0"],
"selectivity_from_histogram": 0.996078431
}
]
$query_tbl;
EXPLAIN
{
"query_block": {
"select_id": 1,
"table": {
"table_name": "t1",
"access_type": "ALL",
"rows": 100,
"filtered": 53.32928848,
"attached_condition": "t1.col0 > 1 and t1.col1 > 1 and t1.col2 > 1 and t1.col3 > 1 and t1.col4 > 1 and t1.col5 > 1 and t1.col6 > 1 and t1.col7 > 1 and t1.col8 > 1 and t1.col9 > 1 and t1.col10 > 1 and t1.col11 > 1 and t1.col12 > 1 and t1.col13 > 1 and t1.col14 > 1 and t1.col15 > 1 and t1.col16 > 1 and t1.col17 > 1 and t1.col18 > 1 and t1.col19 > 1 and t1.col20 > 1 and t1.col21 > 1 and t1.col22 > 1 and t1.col23 > 1 and t1.col24 > 1 and t1.col25 > 1 and t1.col26 > 1 and t1.col27 > 1 and t1.col28 > 1 and t1.col29 > 1 and t1.col30 > 1 and t1.col31 > 1 and t1.col32 > 1 and t1.col33 > 1 and t1.col34 > 1 and t1.col35 > 1 and t1.col36 > 1 and t1.col37 > 1 and t1.col38 > 1 and t1.col39 > 1 and t1.col40 > 1 and t1.col41 > 1 and t1.col42 > 1 and t1.col43 > 1 and t1.col44 > 1 and t1.col45 > 1 and t1.col46 > 1 and t1.col47 > 1 and t1.col48 > 1 and t1.col49 > 1 and t1.col50 > 1 and t1.col51 > 1 and t1.col52 > 1 and t1.col53 > 1 and t1.col54 > 1 and t1.col55 > 1 and t1.col56 > 1 and t1.col57 > 1 and t1.col58 > 1 and t1.col59 > 1 and t1.col60 > 1 and t1.col61 > 1 and t1.col62 > 1 and t1.col63 > 1 and t1.col64 > 1 and t1.col65 > 1 and t1.col66 > 1 and t1.col67 > 1 and t1.col68 > 1 and t1.col69 > 1 and t1.col70 > 1 and t1.col71 > 1 and t1.col72 > 1 and t1.col73 > 1 and t1.col74 > 1 and t1.col75 > 1 and t1.col76 > 1 and t1.col77 > 1 and t1.col78 > 1 and t1.col79 > 1 and t1.col80 > 1 and t1.col81 > 1 and t1.col82 > 1 and t1.col83 > 1 and t1.col84 > 1 and t1.col85 > 1 and t1.col86 > 1 and t1.col87 > 1 and t1.col88 > 1 and t1.col89 > 1 and t1.col90 > 1 and t1.col91 > 1 and t1.col92 > 1 and t1.col93 > 1 and t1.col94 > 1 and t1.col95 > 1 and t1.col96 > 1 and t1.col97 > 1 and t1.col98 > 1 and t1.col99 > 1 and t1.col100 > 1 and t1.col101 > 1 and t1.col102 > 1 and t1.col103 > 1 and t1.col104 > 1 and t1.col105 > 1 and t1.col106 > 1 and t1.col107 > 1 and t1.col108 > 1 and t1.col109 > 1 and t1.col110 > 1 and t1.col111 > 1 and t1.col112 > 1 and t1.col113 > 1 and t1.col114 > 1 and t1.col115 > 1 and t1.col116 > 1 and t1.col117 > 1 and t1.col118 > 1 and t1.col119 > 1 and t1.col120 > 1 and t1.col121 > 1 and t1.col122 > 1 and t1.col123 > 1 and t1.col124 > 1 and t1.col125 > 1 and t1.col126 > 1 and t1.col127 > 1 and t1.col128 > 1 and t1.col129 > 1 and t1.col130 > 1 and t1.col131 > 1 and t1.col132 > 1 and t1.col133 > 1 and t1.col134 > 1 and t1.col135 > 1 and t1.col136 > 1 and t1.col137 > 1 and t1.col138 > 1 and t1.col139 > 1 and t1.col140 > 1 and t1.col141 > 1 and t1.col142 > 1 and t1.col143 > 1 and t1.col144 > 1 and t1.col145 > 1 and t1.col146 > 1 and t1.col147 > 1 and t1.col148 > 1 and t1.col149 > 1 and t1.col150 > 1 and t1.col151 > 1 and t1.col152 > 1 and t1.col153 > 1 and t1.col154 > 1 and t1.col155 > 1 and t1.col156 > 1 and t1.col157 > 1 and t1.col158 > 1 and t1.col159 > 1"
}
}
}
select
json_detailed(json_extract(trace,'$**.selectivity_for_columns[159]')) as JS
from
information_schema.optimizer_trace;
JS
[
{
"column_name": "col159",
"ranges":
["1 < col159"],
"selectivity_from_histogram": 0.996078431
}
]
#
# Check if not being able to infer anything for the first MAX_KEY
# columns doesn't prevent further inferences.
#
# explain extended select * from t1
# where (1>2 or col0>1 or col1>1 or ...) and col99>1
#
$query_tbl;
EXPLAIN
{
"query_block": {
"select_id": 1,
"table": {
"table_name": "t1",
"access_type": "ALL",
"rows": 100,
"filtered": 99.60784149,
"attached_condition": "(t1.col1 > 1 or t1.col2 > 1 or t1.col3 > 1 or t1.col4 > 1 or t1.col5 > 1 or t1.col6 > 1 or t1.col7 > 1 or t1.col8 > 1 or t1.col9 > 1 or t1.col10 > 1 or t1.col11 > 1 or t1.col12 > 1 or t1.col13 > 1 or t1.col14 > 1 or t1.col15 > 1 or t1.col16 > 1 or t1.col17 > 1 or t1.col18 > 1 or t1.col19 > 1 or t1.col20 > 1 or t1.col21 > 1 or t1.col22 > 1 or t1.col23 > 1 or t1.col24 > 1 or t1.col25 > 1 or t1.col26 > 1 or t1.col27 > 1 or t1.col28 > 1 or t1.col29 > 1 or t1.col30 > 1 or t1.col31 > 1 or t1.col32 > 1 or t1.col33 > 1 or t1.col34 > 1 or t1.col35 > 1 or t1.col36 > 1 or t1.col37 > 1 or t1.col38 > 1 or t1.col39 > 1 or t1.col40 > 1 or t1.col41 > 1 or t1.col42 > 1 or t1.col43 > 1 or t1.col44 > 1 or t1.col45 > 1 or t1.col46 > 1 or t1.col47 > 1 or t1.col48 > 1 or t1.col49 > 1 or t1.col50 > 1 or t1.col51 > 1 or t1.col52 > 1 or t1.col53 > 1 or t1.col54 > 1 or t1.col55 > 1 or t1.col56 > 1 or t1.col57 > 1 or t1.col58 > 1 or t1.col59 > 1 or t1.col60 > 1 or t1.col61 > 1 or t1.col62 > 1 or t1.col63 > 1 or t1.col64 > 1 or t1.col65 > 1 or t1.col66 > 1 or t1.col67 > 1 or t1.col68 > 1 or t1.col69 > 1 or t1.col70 > 1 or t1.col71 > 1 or t1.col72 > 1 or t1.col73 > 1 or t1.col74 > 1 or t1.col75 > 1 or t1.col76 > 1 or t1.col77 > 1 or t1.col78 > 1 or t1.col79 > 1 or t1.col80 > 1 or t1.col81 > 1 or t1.col82 > 1 or t1.col83 > 1 or t1.col84 > 1 or t1.col85 > 1 or t1.col86 > 1 or t1.col87 > 1 or t1.col88 > 1 or t1.col89 > 1 or t1.col90 > 1 or t1.col91 > 1 or t1.col92 > 1 or t1.col93 > 1 or t1.col94 > 1 or t1.col95 > 1 or t1.col96 > 1 or t1.col97 > 1 or t1.col98 > 1 or t1.col99 > 1 or t1.col100 > 1 or t1.col101 > 1 or t1.col102 > 1 or t1.col103 > 1 or t1.col104 > 1 or t1.col105 > 1 or t1.col106 > 1 or t1.col107 > 1 or t1.col108 > 1 or t1.col109 > 1 or t1.col110 > 1 or t1.col111 > 1 or t1.col112 > 1 or t1.col113 > 1 or t1.col114 > 1 or t1.col115 > 1 or t1.col116 > 1 or t1.col117 > 1 or t1.col118 > 1 or t1.col119 > 1 or t1.col120 > 1 or t1.col121 > 1 or t1.col122 > 1 or t1.col123 > 1 or t1.col124 > 1 or t1.col125 > 1 or t1.col126 > 1 or t1.col127 > 1 or t1.col128 > 1 or t1.col129 > 1 or t1.col130 > 1 or t1.col131 > 1 or t1.col132 > 1 or t1.col133 > 1 or t1.col134 > 1 or t1.col135 > 1 or t1.col136 > 1 or t1.col137 > 1 or t1.col138 > 1 or t1.col139 > 1 or t1.col140 > 1 or t1.col141 > 1 or t1.col142 > 1 or t1.col143 > 1 or t1.col144 > 1 or t1.col145 > 1 or t1.col146 > 1 or t1.col147 > 1 or t1.col148 > 1 or t1.col149 > 1 or t1.col150 > 1 or t1.col151 > 1 or t1.col152 > 1 or t1.col153 > 1 or t1.col154 > 1 or t1.col155 > 1 or t1.col156 > 1 or t1.col157 > 1 or t1.col158 > 1) and t1.col159 > 1"
}
}
}
select
json_detailed(json_extract(trace,'$**.selectivity_for_columns')) as JS
from
information_schema.optimizer_trace;
JS
[
[
{
"column_name": "col159",
"ranges":
["1 < col159"],
"selectivity_from_histogram": 0.996078431
}
]
]
set optimizer_trace=@trace_tmp;
drop table t1;
#
# MDEV-34993: Incorrect cardinality estimation causes poor query plan
#
create table t1 (
pk int,
key1 int,
filler char(100),
index (key1, pk),
primary key (pk)
);
insert into t1
select
seq, FLOOR(seq/100), 'filler'
from
seq_1_to_1000;
analyze table t1;
Table Op Msg_type Msg_text
test.t1 analyze status Engine-independent statistics collected
test.t1 analyze status OK
set optimizer_trace=1;
explain select * from t1
where
pk in (1,2,3,4,5) and
key1 <= 4;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 range PRIMARY,key1 PRIMARY 4 NULL 5 Using where
create temporary table opt_trace as
select * from information_schema.optimizer_trace;
# Must have a note that "multiplier is too high":
select
json_detailed(json_extract(trace,'$**.selectivity_for_indexes')) as JS
from
opt_trace;
JS
[
[
{
"index_name": "PRIMARY",
"selectivity_from_index": 0.005
},
{
"index_name": "key1",
"selectivity_from_index": 0.399,
"selectivity_multiplier": 90.9091,
"note": "multiplier too high, clipping",
"clipped_multiplier": 2.506265664
}
]
]
# Must not include 1.79...e308 as cost:
select
json_detailed(json_extract(trace,'$**.best_access_path')) as JS
from
opt_trace;
JS
[
{
"considered_access_paths":
[
{
"access_type": "range",
"resulting_rows": 5,
"cost": 6.83879845,
"chosen": true
}
],
"chosen_access_method":
{
"type": "range",
"records": 5,
"cost": 6.83879845,
"uses_join_buffering": false
}
}
]
drop table opt_trace;
# Disable the fix and try the same:
set @@optimizer_adjust_secondary_key_costs='';
explain select * from t1
where
pk in (1,2,3,4,5) and
key1 <= 4;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 range PRIMARY,key1 PRIMARY 4 NULL 5 Using where
create temporary table opt_trace as
select * from information_schema.optimizer_trace;
# Shows a high multiplier, without a "note":
select
json_detailed(json_extract(trace,'$**.selectivity_for_indexes')) as JS
from
opt_trace;
JS
[
[
{
"index_name": "PRIMARY",
"selectivity_from_index": 0.005
},
{
"index_name": "key1",
"selectivity_from_index": 0.399,
"selectivity_multiplier": 90.9091
}
]
]
# Includes 1.79...e308 as cost:
select
json_detailed(json_extract(trace,'$**.best_access_path')) as JS
from
opt_trace;
JS
[
{
"considered_access_paths":
[
{
"access_type": "range",
"resulting_rows": 181.3636545,
"cost": 1.79769e308,
"chosen": true
}
],
"chosen_access_method":
{
"type": "range",
"records": 181.3636545,
"cost": 1.79769e308,
"uses_join_buffering": false
}
}
]
drop table opt_trace;
set optimizer_adjust_secondary_key_costs=default;
drop table t1;
#
# Clean up
#
set optimizer_use_condition_selectivity= @save_optimizer_use_condition_selectivity;
set use_stat_tables= @save_use_stat_tables;
set @@global.histogram_size=@save_histogram_size;
set optimizer_switch=@save_optimizer_switch_for_selectivity_test;
SET SESSION STORAGE_ENGINE=DEFAULT;
Warnings:
Warning 1287 '@@storage_engine' is deprecated and will be removed in a future release. Please use '@@default_storage_engine' instead
Reference in a new issue View git blame Copy permalink