drop table if exists t1,t2;
#
# MDEV-21958: Query having many NOT-IN clauses running forever
#
create table t2 (
pk int primary key,
key1 int,
col1 int,
key (key1, pk)
);
insert into t2 (pk, key1) values (1,1),(2,2),(3,3),(4,4),(5,5);
set @tmp_21958=@@optimizer_trace;
set optimizer_trace=1;
explain select * from t2 where key1 in (1,2,3) and pk not in (1,2,3);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t2 range PRIMARY,key1 key1 5 NULL 3 Using index condition
# This should show only ranges in form "(1) <= (key1) <= (1)"
# ranges over "pk" should not be constructed.
select json_detailed(JSON_EXTRACT(trace, '$**.ranges'))
from information_schema.optimizer_trace;
json_detailed(JSON_EXTRACT(trace, '$**.ranges'))
[
[
"(1) <= (key1) <= (1)",
"(2) <= (key1) <= (2)",
"(3) <= (key1) <= (3)"
],
[
"(1) <= (key1) <= (1)",
"(2) <= (key1) <= (2)",
"(3) <= (key1) <= (3)"
]
]
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)",
"(1,1,8) <= (kp
## 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 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) <= (3,10)",
set optimizer_max_sel_arg_weight= @tmp9750_weight;
set optimizer_trace=@tmp_9750;
drop table t1;
#
# MDEV-24739: Assertion `root->weight >= ...' failed in SEL_ARG::tree_delete
#
SELECT *
FROM mysql.help_relation
WHERE NOT (help_topic_id != 8 AND help_keyword_id != 0 OR help_keyword_id = 2 OR help_topic_id < 1900);
help_topic_id help_keyword_id
SELECT *
FROM mysql.help_relation ignore index (help_topic_id)
WHERE (help_topic_id = 8 OR help_keyword_id = 0) AND help_keyword_id != 2 AND help_topic_id >= 1900;
help_topic_id help_keyword_id
#
# MDEV-24953: 10.5.9 crashes with large IN() list
#
CREATE TABLE t1 (
notification_type_id smallint(4) unsigned NOT NULL DEFAULT 0,
item_id int(10) unsigned NOT NULL DEFAULT 0,
item_parent_id int(10) unsigned NOT NULL DEFAULT 0,
user_id int(10) unsigned NOT NULL DEFAULT 0,
PRIMARY KEY (notification_type_id,item_id,item_parent_id,user_id)
);
insert into t1 values (1,1,1,1), (2,2,2,2), (3,3,3,3);
# Run crashing query
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 range PRIMARY PRIMARY 2 NULL 3 Using where
drop table t1;
#
# MDEV-25069: Assertion `root->weight >= ...' failed in SEL_ARG::tree_delete #2
#
SELECT *
FROM mysql.help_relation
WHERE
(help_topic_id < '2' OR help_topic_id != 8 OR help_topic_id < 1) AND
help_keyword_id = help_topic_id;
help_topic_id help_keyword_id
#
# MDEV-29242: Assertion `computed_weight == weight' failed SEL_ARG::verify_weight
#
CREATE TABLE t1 (id INT, KEY (id));
INSERT INTO t1 VALUES (1),(5);
SELECT id FROM t1 WHERE id IS NULL OR id NOT BETWEEN 1 AND 4;
id
5
DROP TABLE t1;