diff options
Diffstat (limited to 'mysql-test/main/range_notembedded.test')
| -rw-r--r-- | mysql-test/main/range_notembedded.test | 161 |
1 files changed, 161 insertions, 0 deletions
diff --git a/mysql-test/main/range_notembedded.test b/mysql-test/main/range_notembedded.test new file mode 100644 index 00000000..00d16a5d --- /dev/null +++ b/mysql-test/main/range_notembedded.test @@ -0,0 +1,161 @@ +# +# Range tests without embedded server. +# The first reason to have them is that embedded server doesn't have +# optimizer trace. +# +--source include/not_embedded.inc +--disable_warnings +drop table if exists t1,t2; +--enable_warnings + +--echo # +--echo # MDEV-21958: Query having many NOT-IN clauses running forever +--echo # +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); + +--echo # This should show only ranges in form "(1) <= (key1) <= (1)" +--echo # ranges over "pk" should not be constructed. +select json_detailed(JSON_EXTRACT(trace, '$**.ranges')) +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 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; + +--echo # +--echo # MDEV-24739: Assertion `root->weight >= ...' failed in SEL_ARG::tree_delete +--echo # +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); + +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; + +--echo # +--echo # MDEV-24953: 10.5.9 crashes with large IN() list +--echo # +--source include/have_sequence.inc + +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); + +let $consts=`select group_concat(concat("'",seq,"'")) from seq_1_to_4642`; + +--echo # Run crashing query +--disable_query_log +eval +explain +DELETE FROM t1 +WHERE + notification_type_id IN (3, 4, 5, 6, 23) + AND + user_id = '5044' + AND + item_parent_id IN ($consts) +; +--enable_query_log + +drop table t1; + +--echo # +--echo # MDEV-25069: Assertion `root->weight >= ...' failed in SEL_ARG::tree_delete #2 +--echo # + +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; + +--echo # +--echo # MDEV-29242: Assertion `computed_weight == weight' failed SEL_ARG::verify_weight +--echo # +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; +DROP TABLE t1; + |