Adding upstream version 1:10.11.6.upstream/1%10.11.6upstream

Signed-off-by: Daniel Baumann <daniel.baumann@progress-linux.org>

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;
+