MDEV-9750: Quick memory exhaustion with 'extended_keys=on' ...

Part #3: Introduce a user-visible @@optimizer_max_sel_arg_weight
to control the optimization.

5 files changed, 138 insertions, 6 deletions

diff --git a/mysql-test/main/range.result b/mysql-test/main/range.result
index 9800d931dd6..c5178885d7f 100644
--- a/mysql-test/main/range.result
+++ b/mysql-test/main/range.result
@@ -3146,6 +3146,9 @@ 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 
@@ -3179,6 +3182,94 @@ left(@json, 500)
                 "(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 @json= (select json_detailed(JSON_EXTRACT(trace, '$**.range_scan_alternatives'))
+from information_schema.optimizer_trace);
+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)"
+           
+## 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;
diff --git a/mysql-test/main/range.test b/mysql-test/main/range.test
index 642ae3f8a08..a9f374afa3d 100644
--- a/mysql-test/main/range.test
+++ b/mysql-test/main/range.test
@@ -2133,6 +2133,8 @@ create table t1 (
 
 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;
@@ -2150,9 +2152,37 @@ set @json= (select json_detailed(JSON_EXTRACT(trace, '$**.range_scan_alternative
 --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 @json= (select json_detailed(JSON_EXTRACT(trace, '$**.range_scan_alternatives'))
+            from information_schema.optimizer_trace);
+select left(@json, 500);
+
+--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/sql/opt_range.cc b/sql/opt_range.cc
index aef9654c646..26d76075e60 100644
--- a/sql/opt_range.cc
+++ b/sql/opt_range.cc
@@ -415,7 +415,7 @@ static bool null_part_in_key(KEY_PART *key_part, const uchar *key,
                              uint length);
 static bool is_key_scan_ror(PARAM *param, uint keynr, uint8 nparts);
 
-static SEL_ARG *enforce_sel_arg_weight_limit(SEL_ARG *sel_arg);
+static SEL_ARG *enforce_sel_arg_weight_limit(THD *thd, SEL_ARG *sel_arg);
 
 #include "opt_range_mrr.cc"
 
@@ -9971,7 +9971,8 @@ uint SEL_ARG::verify_weight()
 static SEL_ARG *key_or_with_limit(RANGE_OPT_PARAM *param,
                                   SEL_ARG *key1, SEL_ARG *key2)
 {
-  SEL_ARG *res= enforce_sel_arg_weight_limit(key_or(param, key1, key2));
+  SEL_ARG *res= enforce_sel_arg_weight_limit(param->thd, key_or(param,
+                                                                key1, key2));
 #ifndef DBUG_OFF
   if (res)
     res->verify_weight();
@@ -9984,7 +9985,9 @@ static SEL_ARG *key_and_with_limit(RANGE_OPT_PARAM *param,
                                   SEL_ARG *key1, SEL_ARG *key2,
                                   uint clone_flag)
 {
-  SEL_ARG *res= enforce_sel_arg_weight_limit(key_and(param, key1, key2, clone_flag));
+  SEL_ARG *res= enforce_sel_arg_weight_limit(param->thd, key_and(param, key1,
+                                                                 key2,
+                                                                 clone_flag));
 #ifndef DBUG_OFF
   if (res)
     res->verify_weight();
@@ -10770,14 +10773,15 @@ void prune_sel_arg_graph(SEL_ARG *sel_arg, uint max_part)
                   limit.
 */
 
-SEL_ARG *enforce_sel_arg_weight_limit(SEL_ARG *sel_arg)
+SEL_ARG *enforce_sel_arg_weight_limit(THD *thd, SEL_ARG *sel_arg)
 {
-  if (!sel_arg || sel_arg->type != SEL_ARG::KEY_RANGE)
+  if (!sel_arg || sel_arg->type != SEL_ARG::KEY_RANGE ||
+      !thd->variables.optimizer_max_sel_arg_weight)
     return sel_arg;
 
   while (1)
   {
-    if (sel_arg->weight <= SEL_ARG::MAX_WEIGHT)
+    if (sel_arg->weight <= thd->variables.optimizer_max_sel_arg_weight)
       return sel_arg;
 
     uint max_part= sel_arg->get_max_key_part();
diff --git a/sql/sql_class.h b/sql/sql_class.h
index 64cd1ed6ba3..6063c51bccf 100644
--- a/sql/sql_class.h
+++ b/sql/sql_class.h
@@ -766,6 +766,7 @@ typedef struct system_variables
   uint column_compression_threshold;
   uint column_compression_zlib_level;
   uint in_subquery_conversion_threshold;
+  ulong optimizer_max_sel_arg_weight;
   ulonglong max_rowid_filter_size;
 
   vers_asof_timestamp_t vers_asof_timestamp;
diff --git a/sql/sys_vars.cc b/sql/sys_vars.cc
index 1c84f308a81..6b859363453 100644
--- a/sql/sys_vars.cc
+++ b/sql/sys_vars.cc
@@ -6367,6 +6367,12 @@ static Sys_var_uint Sys_in_subquery_conversion_threshold(
        SESSION_VAR(in_subquery_conversion_threshold), CMD_LINE(REQUIRED_ARG),
        VALID_RANGE(0, UINT_MAX), DEFAULT(IN_SUBQUERY_CONVERSION_THRESHOLD), BLOCK_SIZE(1));
 
+static Sys_var_ulong Sys_optimizer_max_sel_arg_weight(
+       "optimizer_max_sel_arg_weight",
+       "The maximum weight of the SEL_ARG graph. Set to 0 for no limit",
+       SESSION_VAR(optimizer_max_sel_arg_weight), CMD_LINE(REQUIRED_ARG),
+       VALID_RANGE(0, ULONG_MAX), DEFAULT(SEL_ARG::MAX_WEIGHT), BLOCK_SIZE(1));
+
 static Sys_var_enum Sys_secure_timestamp(
        "secure_timestamp", "Restricts direct setting of a session "
        "timestamp. Possible levels are: YES - timestamp cannot deviate from "