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
#
CREATE TABLE r (t INT, k INT, PRIMARY KEY(k,t), KEY(t));
INSERT INTO r VALUES (5,1), (5,2), (10,2), (16,2), (100,2), (400, 5), (300, 10), (600, 42) ;
SELECT * FROM r WHERE NOT (t != 8 AND k != 0 OR k = 2 OR t < 1900);
t k
SELECT * FROM r ignore index (t) WHERE (t = 8 OR k = 0) AND k != 2 AND t >= 1900;
t k
DROP TABLE r;
#
# 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);
insert into t1 select seq,seq,seq,seq from seq_10_to_30;
# Run crashing query
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 range PRIMARY PRIMARY 2 NULL 5 Using where
drop table t1;
#
# MDEV-25069: Assertion `root->weight >= ...' failed in SEL_ARG::tree_delete #2
#
CREATE TABLE r (t INT, k INT, PRIMARY KEY(k,t), KEY(t));
INSERT INTO r VALUES (5,1), (5,2), (10,2), (16,2), (100,2), (400, 5), (300, 10), (600, 42) ;
SELECT * FROM r WHERE (t < '2' OR t != 8 OR t < 1) AND k = t;
t k
DROP TABLE r;
#
# 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;
#
# MDEV-34620: Many index_merge variants made and discarded for a big OR
#
CREATE TABLE t1 (
a1 int NOT NULL,
a2 int NOT NULL,
filler char(100),
KEY key1 (a1,a2),
KEY key2 (a2,a1)
);
insert into t1 (a1,a2) values (1,1),(2,2),(3,3);
set @query= concat(
"explain select * from t1 where\n",
(select
group_concat(concat("a1=", seq, " and a2=", seq, " ") separator "\nor " )
from seq_1_to_30)
);
set optimizer_trace=1;
prepare s from @query;
execute s;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 ALL key1,key2 NULL NULL NULL 3 Using where
set @trace=json_extract((select trace from information_schema.optimizer_trace), '$**.range_analysis');
# Observe that "key1" is a a part of several index_merge_union:
select json_pretty(json_search(@trace, 'all', 'key1'));
json_pretty(json_search(@trace, 'all', 'key1'))
[
    "$[0].potential_range_indexes[0].index",
    "$[0].analyzing_range_alternatives.range_scan_alternatives[0].index",
    "$[0].analyzing_range_alternatives.analyzing_index_merge_union[0].indexes_to_merge[0].range_scan_alternatives[0].index",
    "$[0].analyzing_range_alternatives.analyzing_index_merge_union[0].indexes_to_merge[1].range_scan_alternatives[0].index",
    "$[0].analyzing_range_alternatives.analyzing_index_merge_union[1].indexes_to_merge[0].range_scan_alternatives[0].index",
    "$[0].analyzing_range_alternatives.analyzing_index_merge_union[1].indexes_to_merge[1].range_scan_alternatives[0].index",
    "$[0].analyzing_range_alternatives.analyzing_index_merge_union[1].indexes_to_merge[2].range_scan_alternatives[0].index",
    "$[0].analyzing_range_alternatives.analyzing_index_merge_union[2].indexes_to_merge[0].range_scan_alternatives[0].index",
    "$[0].analyzing_range_alternatives.analyzing_index_merge_union[2].indexes_to_merge[1].range_scan_alternatives[0].index"
]
#
# Now, same as above but for a long IN-list
#
set @query= concat(
"explain select * from t1 where\n",
(select
group_concat(concat("a1=", seq, " and a2=", seq, " ") separator "\nor " )
from seq_1_to_120)
);
set optimizer_trace=1;
prepare s from @query;
execute s;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 ALL key1,key2 NULL NULL NULL 3 Using where
set @trace=json_extract((select trace from information_schema.optimizer_trace), '$**.range_analysis');
# Observe that there are NO index_merge_union candidates. Only one potential range scan:
select json_pretty(json_search(@trace, 'all', 'key1'));
json_pretty(json_search(@trace, 'all', 'key1'))
[
    "$[0].potential_range_indexes[0].index",
    "$[0].analyzing_range_alternatives.range_scan_alternatives[0].index"
]
drop table t1;
#
# MDEV-37510: Crash in sel_arg_and_weight_heuristic
#
SET @save_optimizer_trace=@@optimizer_trace;
SET @save_optimizer_max_sel_arg_weight=@@optimizer_max_sel_arg_weight;
SET optimizer_trace= 'enabled=on';
SET optimizer_max_sel_arg_weight=1;
CREATE TABLE t1 (c1 INT, c2 VARCHAR(10), c3 VARCHAR(10));
CREATE TABLE t2 (c1 INT, c2 VARCHAR(10), c3 VARCHAR(10));
INSERT INTO t1 values (2, 'two', 'prime'), (3, 'three', 'prime');
INSERT INTO t2 values (2, 'two', '2_table'), (4, 'four', '2_table');
select * from
t1
JOIN
(
SELECT c1, c2, c3 FROM t2
UNION
SELECT c1, c2, c3 FROM t2
) dt1
ON ((t1.c1 = dt1.c1 OR dt1.c1 IS NULL) AND
(t1.c2 = dt1.c2 OR dt1.c3 IS NULL));
c1 c2 c3 c1 c2 c3
2 two prime 2 two 2_table
set @trace= (select trace from information_schema.optimizer_trace);
set @json= json_detailed(json_extract(@trace, '$**.setup_range_conditions'));
select left(@json, 2500);
left(@json, 2500)
[
    [
        {
            "sel_arg_weight_heuristic": 
            {
                "key1_type": 2,
                "key2_type": 2,
                "key1_weight": 1,
                "key2_weight": 1
            }
        },
        {
            "sel_arg_weight_heuristic": 
            {
                "key1_type": 2,
                "key2_field": "c3",
                "key1_weight": 1,
                "key2_weight": 1
            }
        }
    ]
]
DROP TABLE t1, t2;
SET optimizer_trace=@save_optimizer_trace;
SET optimizer_max_sel_arg_weight=@save_optimizer_max_sel_arg_weight;
# end of 11.4 tests