summaryrefslogtreecommitdiff
path: root/mysql-test
diff options
context:
space:
mode:
Diffstat (limited to 'mysql-test')
-rw-r--r--mysql-test/main/mysqld--help.result4
-rw-r--r--mysql-test/main/range_notembedded.result179
-rw-r--r--mysql-test/main/range_notembedded.test66
-rw-r--r--mysql-test/suite/sys_vars/r/sysvars_server_embedded.result10
-rw-r--r--mysql-test/suite/sys_vars/r/sysvars_server_notembedded.result10
5 files changed, 269 insertions, 0 deletions
diff --git a/mysql-test/main/mysqld--help.result b/mysql-test/main/mysqld--help.result
index 7671dfeef59..141ef94ad59 100644
--- a/mysql-test/main/mysqld--help.result
+++ b/mysql-test/main/mysqld--help.result
@@ -681,6 +681,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
@@ -1637,6 +1640,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_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;
diff --git a/mysql-test/main/range_notembedded.test b/mysql-test/main/range_notembedded.test
index 4dc49429ff1..d50bec23148 100644
--- a/mysql-test/main/range_notembedded.test
+++ b/mysql-test/main/range_notembedded.test
@@ -31,3 +31,69 @@ from information_schema.optimizer_trace;
set optimizer_trace=@tmp_21958;
drop table t2;
+--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);
+analyze table t1;
+
+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_embedded.result b/mysql-test/suite/sys_vars/r/sysvars_server_embedded.result
index 0648284e580..e0b4e3565bc 100644
--- a/mysql-test/suite/sys_vars/r/sysvars_server_embedded.result
+++ b/mysql-test/suite/sys_vars/r/sysvars_server_embedded.result
@@ -2233,6 +2233,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
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 78b8ca0263d..10773514295 100644
--- a/mysql-test/suite/sys_vars/r/sysvars_server_notembedded.result
+++ b/mysql-test/suite/sys_vars/r/sysvars_server_notembedded.result
@@ -2393,6 +2393,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
View Lorry Controller Status