range_notembedded.result 7.72 KB
Newer Older
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15
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
16
1	SIMPLE	t2	range	PRIMARY,key1	key1	5	NULL	3	Using index condition
17 18 19 20 21 22 23
# 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'))
[
    
24 25 26 27 28 29
    [
        "(1) <= (key1) <= (1)",
        "(2) <= (key1) <= (2)",
        "(3) <= (key1) <= (3)"
    ],
    
30 31 32 33 34 35 36 37
    [
        "(1) <= (key1) <= (1)",
        "(2) <= (key1) <= (2)",
        "(3) <= (key1) <= (3)"
    ]
]
set optimizer_trace=@tmp_21958;
drop table t2;
38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215
#
# 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 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;
216 217 218 219 220 221 222 223 224 225 226
#
# 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
227 228 229 230 231 232 233 234 235 236 237 238 239 240 241
#
# 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;