diff options
Diffstat (limited to 'mysql-test/main/range_notembedded.result')
| -rw-r--r-- | mysql-test/main/range_notembedded.result | 179 |
1 files changed, 179 insertions, 0 deletions
diff --git a/mysql-test/main/range_notembedded.result b/mysql-test/main/range_notembedded.result index 4973f449631..1f6db102624 100644 --- a/mysql-test/main/range_notembedded.result +++ b/mysql-test/main/range_notembedded.result @@ -35,3 +35,182 @@ json_detailed(JSON_EXTRACT(trace, '$**.ranges')) ] set optimizer_trace=@tmp_21958; drop table t2; +# +# MDEV-9750: Quick memory exhaustion with 'extended_keys=on'... +# +create table t1 ( +kp1 int, +kp2 int, +kp3 int, +kp4 int, +key key1(kp1, kp2, kp3,kp4) +); +insert into t1 values (1,1,1,1),(2,2,2,2),(3,3,3,3); +analyze table t1; +Table Op Msg_type Msg_text +test.t1 analyze status Engine-independent statistics collected +test.t1 analyze status OK +show variables like 'optimizer_max_sel_arg_weight'; +Variable_name Value +optimizer_max_sel_arg_weight 32000 +set @tmp_9750=@@optimizer_trace; +set optimizer_trace=1; +explain select * from t1 where +kp1 in (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20) and +kp2 in (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20) and +kp3 in (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20) and +kp4 in (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20) +; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 index key1 key1 20 NULL 3 Using where; Using index +set @json= (select json_detailed(JSON_EXTRACT(trace, '$**.range_scan_alternatives')) +from information_schema.optimizer_trace); +# This will show 3-component ranges. +# The ranges were produced, but the optimizer has cut away kp4 +# to keep the number of ranges at manageable level: +select left(@json, 500); +left(@json, 500) +[ + + [ + + { + "index": "key1", + "ranges": + [ + "(1,1,1) <= (kp1,kp2,kp3) <= (1,1,1)", + "(1,1,2) <= (kp1,kp2,kp3) <= (1,1,2)", + "(1,1,3) <= (kp1,kp2,kp3) <= (1,1,3)", + "(1,1,4) <= (kp1,kp2,kp3) <= (1,1,4)", + "(1,1,5) <= (kp1,kp2,kp3) <= (1,1,5)", + "(1,1,6) <= (kp1,kp2,kp3) <= (1,1,6)", + "(1,1,7) <= (kp1,kp2,kp3) <= (1,1,7)", + " +## Repeat the above with low max_weight: +set @tmp9750_weight=@@optimizer_max_sel_arg_weight; +set optimizer_max_sel_arg_weight=20; +explain select * from t1 where +kp1 in (1,2,3,4,5,6,7,8,9,10) and +kp2 in (1,2,3,4,5,6,7,8,9,10) and +kp3 in (1,2,3,4,5,6,7,8,9,10) and +kp4 in (1,2,3,4,5,6,7,8,9,10) +; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 index key1 key1 20 NULL 3 Using where; Using index +set @trace= (select trace from information_schema.optimizer_trace); +set @json= json_detailed(json_extract(@trace, '$**.range_scan_alternatives')); +select left(@json, 500); +left(@json, 500) +[ + + [ + + { + "index": "key1", + "ranges": + [ + "(1) <= (kp1) <= (1)", + "(2) <= (kp1) <= (2)", + "(3) <= (kp1) <= (3)", + "(4) <= (kp1) <= (4)", + "(5) <= (kp1) <= (5)", + "(6) <= (kp1) <= (6)", + "(7) <= (kp1) <= (7)", + "(8) <= (kp1) <= (8)", + "(9) <= (kp1) <= (9)", + "(10) <= (kp1) <= (10)" + +set @json= json_detailed(json_extract(@trace, '$**.setup_range_conditions')); +select left(@json, 2500); +left(@json, 2500) +[ + + [ + + { + "sel_arg_weight_heuristic": + { + "key1_field": "kp1", + "key2_field": "kp2", + "key1_weight": 10, + "key2_weight": 10 + } + }, + + { + "sel_arg_weight_heuristic": + { + "key1_field": "kp1", + "key2_field": "kp3", + "key1_weight": 10, + "key2_weight": 10 + } + }, + + { + "sel_arg_weight_heuristic": + { + "key1_field": "kp1", + "key2_field": "kp4", + "key1_weight": 10, + "key2_weight": 10 + } + } + ] +] +## Repeat the above with a bit higher max_weight: +set @tmp9750_weight=@@optimizer_max_sel_arg_weight; +set optimizer_max_sel_arg_weight=120; +explain select * from t1 where +kp1 in (1,2,3,4,5,6,7,8,9,10) and +kp2 in (1,2,3,4,5,6,7,8,9,10) and +kp3 in (1,2,3,4,5,6,7,8,9,10) and +kp4 in (1,2,3,4,5,6,7,8,9,10) +; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 index key1 key1 20 NULL 3 Using where; Using index +set @json= (select json_detailed(JSON_EXTRACT(trace, '$**.range_scan_alternatives')) +from information_schema.optimizer_trace); +select left(@json, 1500); +left(@json, 1500) +[ + + [ + + { + "index": "key1", + "ranges": + [ + "(1,1) <= (kp1,kp2) <= (1,1)", + "(1,2) <= (kp1,kp2) <= (1,2)", + "(1,3) <= (kp1,kp2) <= (1,3)", + "(1,4) <= (kp1,kp2) <= (1,4)", + "(1,5) <= (kp1,kp2) <= (1,5)", + "(1,6) <= (kp1,kp2) <= (1,6)", + "(1,7) <= (kp1,kp2) <= (1,7)", + "(1,8) <= (kp1,kp2) <= (1,8)", + "(1,9) <= (kp1,kp2) <= (1,9)", + "(1,10) <= (kp1,kp2) <= (1,10)", + "(2,1) <= (kp1,kp2) <= (2,1)", + "(2,2) <= (kp1,kp2) <= (2,2)", + "(2,3) <= (kp1,kp2) <= (2,3)", + "(2,4) <= (kp1,kp2) <= (2,4)", + "(2,5) <= (kp1,kp2) <= (2,5)", + "(2,6) <= (kp1,kp2) <= (2,6)", + "(2,7) <= (kp1,kp2) <= (2,7)", + "(2,8) <= (kp1,kp2) <= (2,8)", + "(2,9) <= (kp1,kp2) <= (2,9)", + "(2,10) <= (kp1,kp2) <= (2,10)", + "(3,1) <= (kp1,kp2) <= (3,1)", + "(3,2) <= (kp1,kp2) <= (3,2)", + "(3,3) <= (kp1,kp2) <= (3,3)", + "(3,4) <= (kp1,kp2) <= (3,4)", + "(3,5) <= (kp1,kp2) <= (3,5)", + "(3,6) <= (kp1,kp2) <= (3,6)", + "(3,7) <= (kp1,kp2) <= (3,7)", + "(3,8) <= (kp1,kp2) <= (3,8)", + "(3,9) <= (kp1,kp2) <= (3,9)", + "(3,10) <= (kp1,kp2 +set optimizer_max_sel_arg_weight= @tmp9750_weight; +set optimizer_trace=@tmp_9750; +drop table t1; |