revision-id: 63e8f07e468c5cf44a0910e955887d01f87e7d96 (mariadb-10.4.11-424-g63e8f07e468) parent(s): b6a5d3e87a8c33540d7cda3e4a512598fa5b1233 author: Sergei Petrunia committer: Sergei Petrunia timestamp: 2021-01-19 18:16:58 +0300 message: MDEV-9750: Quick memory exhaustion with 'extended_keys=on', part#5 Fix tests. --- mysql-test/main/mysqld--help.result | 4 + mysql-test/main/range.result | 172 --------------------- mysql-test/main/range.test | 67 -------- mysql-test/main/range_mrr_icp.result | 46 ------ mysql-test/main/range_notembedded.result | 172 +++++++++++++++++++++ mysql-test/main/range_notembedded.test | 71 +++++++++ .../sys_vars/r/sysvars_server_notembedded.result | 10 ++ 7 files changed, 257 insertions(+), 285 deletions(-) diff --git a/mysql-test/main/mysqld--help.result b/mysql-test/main/mysqld--help.result index 45b1e4afe05..abfd86f8d50 100644 --- a/mysql-test/main/mysqld--help.result +++ b/mysql-test/main/mysqld--help.result @@ -680,6 +680,9 @@ The following specify which files/extra groups are read (specified before remain max_connections*5 or max_connections + table_cache*2 (whichever is larger) number of file descriptors (Automatically configured unless set explicitly) + --optimizer-max-sel-arg-weight=# + The maximum weight of the SEL_ARG graph. Set to 0 for no + limit --optimizer-prune-level=# Controls the heuristic(s) applied during query optimization to prune less-promising partial plans from @@ -1595,6 +1598,7 @@ old-alter-table DEFAULT old-mode old-passwords FALSE old-style-user-limits FALSE +optimizer-max-sel-arg-weight 32000 optimizer-prune-level 1 optimizer-search-depth 62 optimizer-selectivity-sampling-limit 100 diff --git a/mysql-test/main/range.result b/mysql-test/main/range.result index 982a70f94f8..b708628b625 100644 --- a/mysql-test/main/range.result +++ b/mysql-test/main/range.result @@ -3135,178 +3135,6 @@ drop table t1,ten,t2; # # End of 10.2 tests # -# -# 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); -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) -[ - - [ - - { - "enforce_sel_arg_weight_limit": - { - "index": "key1", - "old_weight": 110, - "new_weight": 10 - } - }, - - { - "enforce_sel_arg_weight_limit": - { - "index": "key1", - "old_weight": 110, - "new_weight": 10 - } - }, - - { - "enforce_sel_arg_weight_limit": - { - "index": "key1", - "old_weight": 110, - "new_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; set global innodb_stats_persistent= @innodb_stats_persistent_save; set global innodb_stats_persistent_sample_pages= @innodb_stats_persistent_sample_pages_save; diff --git a/mysql-test/main/range.test b/mysql-test/main/range.test index 6a1c51f8939..b5980a8f616 100644 --- a/mysql-test/main/range.test +++ b/mysql-test/main/range.test @@ -2119,73 +2119,6 @@ drop table t1,ten,t2; --echo # End of 10.2 tests --echo # ---echo # ---echo # MDEV-9750: Quick memory exhaustion with 'extended_keys=on'... ---echo # - -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); - -show variables like 'optimizer_max_sel_arg_weight'; - -# 20 * 20 * 20 *20 = 400*400 = 160,000 ranges -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) -; - -set @json= (select json_detailed(JSON_EXTRACT(trace, '$**.range_scan_alternatives')) - from information_schema.optimizer_trace); ---echo # This will show 3-component ranges. ---echo # The ranges were produced, but the optimizer has cut away kp4 ---echo # to keep the number of ranges at manageable level: -select left(@json, 500); - ---echo ## 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) -; -set @trace= (select trace from information_schema.optimizer_trace); -set @json= json_detailed(json_extract(@trace, '$**.range_scan_alternatives')); -select left(@json, 500); - -set @json= json_detailed(json_extract(@trace, '$**.setup_range_conditions')); -select left(@json, 2500); - ---echo ## 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) -; -set @json= (select json_detailed(JSON_EXTRACT(trace, '$**.range_scan_alternatives')) - from information_schema.optimizer_trace); -select left(@json, 1500); - -set optimizer_max_sel_arg_weight= @tmp9750_weight; -set optimizer_trace=@tmp_9750; -drop table t1; - - set global innodb_stats_persistent= @innodb_stats_persistent_save; set global innodb_stats_persistent_sample_pages= @innodb_stats_persistent_sample_pages_save; diff --git a/mysql-test/main/range_mrr_icp.result b/mysql-test/main/range_mrr_icp.result index 128f23d71f6..04c3ad2780d 100644 --- a/mysql-test/main/range_mrr_icp.result +++ b/mysql-test/main/range_mrr_icp.result @@ -3132,52 +3132,6 @@ drop table t1,ten,t2; # # End of 10.2 tests # -# -# 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); -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)", - " -set optimizer_trace=@tmp_9750; -drop table t1; set global innodb_stats_persistent= @innodb_stats_persistent_save; set global innodb_stats_persistent_sample_pages= @innodb_stats_persistent_sample_pages_save; diff --git a/mysql-test/main/range_notembedded.result b/mysql-test/main/range_notembedded.result new file mode 100644 index 00000000000..7d5dc41d7ed --- /dev/null +++ b/mysql-test/main/range_notembedded.result @@ -0,0 +1,172 @@ +# +# 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); +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) +[ + + [ + + { + "enforce_sel_arg_weight_limit": + { + "index": "key1", + "old_weight": 110, + "new_weight": 10 + } + }, + + { + "enforce_sel_arg_weight_limit": + { + "index": "key1", + "old_weight": 110, + "new_weight": 10 + } + }, + + { + "enforce_sel_arg_weight_limit": + { + "index": "key1", + "old_weight": 110, + "new_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; diff --git a/mysql-test/main/range_notembedded.test b/mysql-test/main/range_notembedded.test new file mode 100644 index 00000000000..c1224550a1c --- /dev/null +++ b/mysql-test/main/range_notembedded.test @@ -0,0 +1,71 @@ +# +# Range tests that require optimizer trace (which is not available in embedded +# server) +# +-- source include/not_embedded.inc + +--echo # +--echo # MDEV-9750: Quick memory exhaustion with 'extended_keys=on'... +--echo # + +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); + +show variables like 'optimizer_max_sel_arg_weight'; + +# 20 * 20 * 20 *20 = 400*400 = 160,000 ranges +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) +; + +set @json= (select json_detailed(JSON_EXTRACT(trace, '$**.range_scan_alternatives')) + from information_schema.optimizer_trace); +--echo # This will show 3-component ranges. +--echo # The ranges were produced, but the optimizer has cut away kp4 +--echo # to keep the number of ranges at manageable level: +select left(@json, 500); + +--echo ## 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) +; +set @trace= (select trace from information_schema.optimizer_trace); +set @json= json_detailed(json_extract(@trace, '$**.range_scan_alternatives')); +select left(@json, 500); + +set @json= json_detailed(json_extract(@trace, '$**.setup_range_conditions')); +select left(@json, 2500); + +--echo ## 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) +; +set @json= (select json_detailed(JSON_EXTRACT(trace, '$**.range_scan_alternatives')) + from information_schema.optimizer_trace); +select left(@json, 1500); + +set optimizer_max_sel_arg_weight= @tmp9750_weight; +set optimizer_trace=@tmp_9750; +drop table t1; diff --git a/mysql-test/suite/sys_vars/r/sysvars_server_notembedded.result b/mysql-test/suite/sys_vars/r/sysvars_server_notembedded.result index 2543cb12453..87f488e3653 100644 --- a/mysql-test/suite/sys_vars/r/sysvars_server_notembedded.result +++ b/mysql-test/suite/sys_vars/r/sysvars_server_notembedded.result @@ -2402,6 +2402,16 @@ NUMERIC_BLOCK_SIZE 1 ENUM_VALUE_LIST NULL READ_ONLY YES COMMAND_LINE_ARGUMENT REQUIRED +VARIABLE_NAME OPTIMIZER_MAX_SEL_ARG_WEIGHT +VARIABLE_SCOPE SESSION +VARIABLE_TYPE BIGINT UNSIGNED +VARIABLE_COMMENT The maximum weight of the SEL_ARG graph. Set to 0 for no limit +NUMERIC_MIN_VALUE 0 +NUMERIC_MAX_VALUE 18446744073709551615 +NUMERIC_BLOCK_SIZE 1 +ENUM_VALUE_LIST NULL +READ_ONLY NO +COMMAND_LINE_ARGUMENT REQUIRED VARIABLE_NAME OPTIMIZER_PRUNE_LEVEL VARIABLE_SCOPE SESSION VARIABLE_TYPE BIGINT UNSIGNED