summaryrefslogtreecommitdiff
path: root/mysql-test
diff options
context:
space:
mode:
Diffstat (limited to 'mysql-test')
-rw-r--r--mysql-test/main/derived_split_innodb.result527
-rw-r--r--mysql-test/main/derived_split_innodb.test236
-rw-r--r--mysql-test/main/explain_innodb.result3
-rw-r--r--mysql-test/main/explain_innodb.test4
-rw-r--r--mysql-test/main/insert_update.result41
-rw-r--r--mysql-test/main/insert_update.test26
-rw-r--r--mysql-test/main/opt_trace.result114
-rw-r--r--mysql-test/main/opt_trace.test70
-rw-r--r--mysql-test/main/selectivity.test7
-rw-r--r--mysql-test/main/selectivity_innodb_notembedded.result100
-rw-r--r--mysql-test/main/selectivity_innodb_notembedded.test16
-rw-r--r--mysql-test/main/selectivity_notembedded.result42
-rw-r--r--mysql-test/main/selectivity_notembedded.test51
-rw-r--r--mysql-test/main/view.result18
-rw-r--r--mysql-test/main/view.test19
-rw-r--r--mysql-test/std_data/rpl/master-bin-seq_10.3.36.000001bin0 -> 1245 bytes
-rw-r--r--mysql-test/suite/rpl/r/rpl_parallel_seq.result85
-rw-r--r--mysql-test/suite/rpl/t/rpl_parallel_seq.test131
18 files changed, 1456 insertions, 34 deletions
diff --git a/mysql-test/main/derived_split_innodb.result b/mysql-test/main/derived_split_innodb.result
index 04f79d3f018..736e6a2c020 100644
--- a/mysql-test/main/derived_split_innodb.result
+++ b/mysql-test/main/derived_split_innodb.result
@@ -284,3 +284,530 @@ id select_type table type possible_keys key key_len ref rows Extra
2 DERIVED t4 ALL NULL NULL NULL NULL 40 Using filesort
drop table t3, t4;
# End of 10.3 tests
+#
+# MDEV-26301: Split optimization refills temporary table too many times
+#
+create table t1(a int, b int);
+insert into t1 select seq,seq from seq_1_to_5;
+create table t2(a int, b int, key(a));
+insert into t2
+select A.seq,B.seq from seq_1_to_25 A, seq_1_to_2 B;
+create table t3(a int, b int, key(a));
+insert into t3
+select A.seq,B.seq from seq_1_to_5 A, seq_1_to_3 B;
+analyze table t1,t2,t3 persistent for all;
+Table Op Msg_type Msg_text
+test.t1 analyze status Engine-independent statistics collected
+test.t1 analyze status OK
+test.t2 analyze status Engine-independent statistics collected
+test.t2 analyze status Table is already up to date
+test.t3 analyze status Engine-independent statistics collected
+test.t3 analyze status Table is already up to date
+explain
+select * from
+(t1 left join t2 on t2.a=t1.b) left join t3 on t3.a=t1.b;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ALL NULL NULL NULL NULL 5
+1 SIMPLE t2 ref a a 5 test.t1.b 2 Using where
+1 SIMPLE t3 ref a a 5 test.t1.b 3 Using where
+create table t10 (
+grp_id int,
+col1 int,
+key(grp_id)
+);
+insert into t10
+select
+A.seq,
+B.seq
+from
+seq_1_to_100 A,
+seq_1_to_100 B;
+create table t11 (
+col1 int,
+col2 int
+);
+insert into t11
+select A.seq, A.seq from seq_1_to_10 A;
+analyze table t10,t11 persistent for all;
+Table Op Msg_type Msg_text
+test.t10 analyze status Engine-independent statistics collected
+test.t10 analyze status Table is already up to date
+test.t11 analyze status Engine-independent statistics collected
+test.t11 analyze status OK
+explain select * from
+(
+(t1 left join t2 on t2.a=t1.b)
+left join t3 on t3.a=t1.b
+) left join (select grp_id, count(*)
+from t10 left join t11 on t11.col1=t10.col1
+group by grp_id) T on T.grp_id=t1.b;
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t1 ALL NULL NULL NULL NULL 5
+1 PRIMARY t2 ref a a 5 test.t1.b 2 Using where
+1 PRIMARY t3 ref a a 5 test.t1.b 3 Using where
+1 PRIMARY <derived2> ref key0 key0 5 test.t1.b 10 Using where
+2 LATERAL DERIVED t10 ref grp_id grp_id 5 test.t1.b 100
+2 LATERAL DERIVED t11 ALL NULL NULL NULL NULL 10 Using where; Using join buffer (flat, BNL join)
+# The important part in the below output is:
+# "lateral": 1,
+# "query_block": {
+# "select_id": 2,
+# "r_loops": 5, <-- must be 5, not 30.
+analyze format=json select * from
+(
+(t1 left join t2 on t2.a=t1.b)
+left join t3 on t3.a=t1.b
+) left join (select grp_id, count(*)
+from t10 left join t11 on t11.col1=t10.col1
+group by grp_id) T on T.grp_id=t1.b;
+ANALYZE
+{
+ "query_block": {
+ "select_id": 1,
+ "r_loops": 1,
+ "r_total_time_ms": "REPLACED",
+ "const_condition": "1",
+ "table": {
+ "table_name": "t1",
+ "access_type": "ALL",
+ "r_loops": 1,
+ "rows": 5,
+ "r_rows": 5,
+ "r_total_time_ms": "REPLACED",
+ "filtered": 100,
+ "r_filtered": 100
+ },
+ "table": {
+ "table_name": "t2",
+ "access_type": "ref",
+ "possible_keys": ["a"],
+ "key": "a",
+ "key_length": "5",
+ "used_key_parts": ["a"],
+ "ref": ["test.t1.b"],
+ "r_loops": 5,
+ "rows": 2,
+ "r_rows": 2,
+ "r_total_time_ms": "REPLACED",
+ "filtered": 100,
+ "r_filtered": 100,
+ "attached_condition": "trigcond(trigcond(t1.b is not null))"
+ },
+ "table": {
+ "table_name": "t3",
+ "access_type": "ref",
+ "possible_keys": ["a"],
+ "key": "a",
+ "key_length": "5",
+ "used_key_parts": ["a"],
+ "ref": ["test.t1.b"],
+ "r_loops": 10,
+ "rows": 3,
+ "r_rows": 3,
+ "r_total_time_ms": "REPLACED",
+ "filtered": 100,
+ "r_filtered": 100,
+ "attached_condition": "trigcond(trigcond(t1.b is not null))"
+ },
+ "table": {
+ "table_name": "<derived2>",
+ "access_type": "ref",
+ "possible_keys": ["key0"],
+ "key": "key0",
+ "key_length": "5",
+ "used_key_parts": ["grp_id"],
+ "ref": ["test.t1.b"],
+ "r_loops": 30,
+ "rows": 10,
+ "r_rows": 1,
+ "r_total_time_ms": "REPLACED",
+ "filtered": 100,
+ "r_filtered": 100,
+ "attached_condition": "trigcond(trigcond(t1.b is not null))",
+ "materialized": {
+ "lateral": 1,
+ "query_block": {
+ "select_id": 2,
+ "r_loops": 5,
+ "r_total_time_ms": "REPLACED",
+ "outer_ref_condition": "t1.b is not null",
+ "table": {
+ "table_name": "t10",
+ "access_type": "ref",
+ "possible_keys": ["grp_id"],
+ "key": "grp_id",
+ "key_length": "5",
+ "used_key_parts": ["grp_id"],
+ "ref": ["test.t1.b"],
+ "r_loops": 5,
+ "rows": 100,
+ "r_rows": 100,
+ "r_total_time_ms": "REPLACED",
+ "filtered": 100,
+ "r_filtered": 100
+ },
+ "block-nl-join": {
+ "table": {
+ "table_name": "t11",
+ "access_type": "ALL",
+ "r_loops": 5,
+ "rows": 10,
+ "r_rows": 10,
+ "r_total_time_ms": "REPLACED",
+ "filtered": 100,
+ "r_filtered": 100
+ },
+ "buffer_type": "flat",
+ "buffer_size": "1Kb",
+ "join_type": "BNL",
+ "attached_condition": "trigcond(t11.col1 = t10.col1)",
+ "r_filtered": 10
+ }
+ }
+ }
+ }
+ }
+}
+create table t21 (pk int primary key);
+insert into t21 values (1),(2),(3);
+create table t22 (pk int primary key);
+insert into t22 values (1),(2),(3);
+explain
+select * from
+t21, t22,
+(
+(t1 left join t2 on t2.a=t1.b)
+left join t3 on t3.a=t1.b
+) left join (select grp_id, count(*)
+from t10 left join t11 on t11.col1=t10.col1
+group by grp_id) T on T.grp_id=t1.b
+where
+t21.pk=1 and t22.pk=2;
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t21 const PRIMARY PRIMARY 4 const 1 Using index
+1 PRIMARY t22 const PRIMARY PRIMARY 4 const 1 Using index
+1 PRIMARY t1 ALL NULL NULL NULL NULL 5
+1 PRIMARY t2 ref a a 5 test.t1.b 2 Using where
+1 PRIMARY t3 ref a a 5 test.t1.b 3 Using where
+1 PRIMARY <derived2> ref key0 key0 5 test.t1.b 10 Using where
+2 LATERAL DERIVED t10 ref grp_id grp_id 5 test.t1.b 100
+2 LATERAL DERIVED t11 ALL NULL NULL NULL NULL 10 Using where; Using join buffer (flat, BNL join)
+explain
+select * from
+t21,
+(
+(t1 left join t2 on t2.a=t1.b)
+left join t3 on t3.a=t1.b
+) left join (select grp_id, count(*)
+from
+t22 join t10 left join t11 on t11.col1=t10.col1
+where
+t22.pk=1
+group by grp_id) T on T.grp_id=t1.b
+where
+t21.pk=1;
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t21 const PRIMARY PRIMARY 4 const 1 Using index
+1 PRIMARY t1 ALL NULL NULL NULL NULL 5
+1 PRIMARY t2 ref a a 5 test.t1.b 2 Using where
+1 PRIMARY t3 ref a a 5 test.t1.b 3 Using where
+1 PRIMARY <derived2> ref key0 key0 5 test.t1.b 10 Using where
+2 LATERAL DERIVED t22 const PRIMARY PRIMARY 4 const 1 Using index
+2 LATERAL DERIVED t10 ref grp_id grp_id 5 test.t1.b 100
+2 LATERAL DERIVED t11 ALL NULL NULL NULL NULL 10 Using where; Using join buffer (flat, BNL join)
+create table t5 (
+pk int primary key
+);
+insert into t5 select seq from seq_1_to_1000;
+explain
+select * from
+t21,
+(
+(((t1 join t5 on t5.pk=t1.b)) left join t2 on t2.a=t1.b)
+left join t3 on t3.a=t1.b
+) left join (select grp_id, count(*)
+from
+t22 join t10 left join t11 on t11.col1=t10.col1
+where
+t22.pk=1
+group by grp_id) T on T.grp_id=t1.b
+where
+t21.pk=1;
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t21 const PRIMARY PRIMARY 4 const 1 Using index
+1 PRIMARY t1 ALL NULL NULL NULL NULL 5 Using where
+1 PRIMARY t5 eq_ref PRIMARY PRIMARY 4 test.t1.b 1 Using index
+1 PRIMARY t2 ref a a 5 test.t1.b 2
+1 PRIMARY t3 ref a a 5 test.t1.b 3
+1 PRIMARY <derived2> ref key0 key0 5 test.t1.b 10 Using where
+2 LATERAL DERIVED t22 const PRIMARY PRIMARY 4 const 1 Using index
+2 LATERAL DERIVED t10 ref grp_id grp_id 5 test.t5.pk 100 Using index condition
+2 LATERAL DERIVED t11 ALL NULL NULL NULL NULL 10 Using where; Using join buffer (flat, BNL join)
+drop table t1,t2,t3,t5, t10, t11, t21, t22;
+create table t1(a int, b int);
+insert into t1 select seq,seq from seq_1_to_5;
+create table t2(a int, b int, key(a));
+insert into t2
+select A.seq,B.seq from seq_1_to_25 A, seq_1_to_2 B;
+create table t3(a int, b int, key(a));
+insert into t3
+select A.seq,B.seq from seq_1_to_5 A, seq_1_to_3 B;
+analyze table t1,t2,t3 persistent for all;
+Table Op Msg_type Msg_text
+test.t1 analyze status Engine-independent statistics collected
+test.t1 analyze status OK
+test.t2 analyze status Engine-independent statistics collected
+test.t2 analyze status Table is already up to date
+test.t3 analyze status Engine-independent statistics collected
+test.t3 analyze status Table is already up to date
+create table t10 (
+grp_id int,
+col1 int,
+key(grp_id)
+);
+insert into t10
+select
+A.seq,
+B.seq
+from
+seq_1_to_100 A,
+seq_1_to_100 B;
+create table t11 (
+col1 int,
+col2 int
+);
+insert into t11
+select A.seq, A.seq from seq_1_to_10 A;
+analyze table t10,t11 persistent for all;
+Table Op Msg_type Msg_text
+test.t10 analyze status Engine-independent statistics collected
+test.t10 analyze status Table is already up to date
+test.t11 analyze status Engine-independent statistics collected
+test.t11 analyze status OK
+explain select *
+from
+(
+(t1 left join t2 on t2.a=t1.b)
+left join
+t3
+on t3.a=t1.b
+)
+left join
+(
+select grp_id, count(*)
+from t10 left join t11 on t11.col1=t10.col1
+group by grp_id
+)dt
+on dt.grp_id=t1.b;
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t1 ALL NULL NULL NULL NULL 5
+1 PRIMARY t2 ref a a 5 test.t1.b 2 Using where
+1 PRIMARY t3 ref a a 5 test.t1.b 3 Using where
+1 PRIMARY <derived2> ref key0 key0 5 test.t1.b 10 Using where
+2 LATERAL DERIVED t10 ref grp_id grp_id 5 test.t1.b 100
+2 LATERAL DERIVED t11 ALL NULL NULL NULL NULL 10 Using where; Using join buffer (flat, BNL join)
+select *
+from
+(
+(t1 left join t2 on t2.a=t1.b)
+left join
+t3
+on t3.a=t1.b
+)
+left join
+(
+select grp_id, count(*)
+from t10 left join t11 on t11.col1=t10.col1
+group by grp_id
+)dt
+on dt.grp_id=t1.b;
+a b a b a b grp_id count(*)
+1 1 1 1 1 1 1 100
+1 1 1 1 1 2 1 100
+1 1 1 1 1 3 1 100
+1 1 1 2 1 1 1 100
+1 1 1 2 1 2 1 100
+1 1 1 2 1 3 1 100
+2 2 2 1 2 1 2 100
+2 2 2 1 2 2 2 100
+2 2 2 1 2 3 2 100
+2 2 2 2 2 1 2 100
+2 2 2 2 2 2 2 100
+2 2 2 2 2 3 2 100
+3 3 3 1 3 1 3 100
+3 3 3 1 3 2 3 100
+3 3 3 1 3 3 3 100
+3 3 3 2 3 1 3 100
+3 3 3 2 3 2 3 100
+3 3 3 2 3 3 3 100
+4 4 4 1 4 1 4 100
+4 4 4 1 4 2 4 100
+4 4 4 1 4 3 4 100
+4 4 4 2 4 1 4 100
+4 4 4 2 4 2 4 100
+4 4 4 2 4 3 4 100
+5 5 5 1 5 1 5 100
+5 5 5 1 5 2 5 100
+5 5 5 1 5 3 5 100
+5 5 5 2 5 1 5 100
+5 5 5 2 5 2 5 100
+5 5 5 2 5 3 5 100
+set join_cache_level=4;
+explain select *
+from
+(
+(t1 left join t2 on t2.a=t1.b)
+left join
+t3
+on t3.a=t1.b
+)
+left join
+(
+select grp_id, count(*)
+from t10 left join t11 on t11.col1=t10.col1
+group by grp_id
+)dt
+on dt.grp_id=t1.b;
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t1 ALL NULL NULL NULL NULL 5
+1 PRIMARY t2 ref a a 5 test.t1.b 2 Using where
+1 PRIMARY t3 ref a a 5 test.t1.b 3 Using where
+1 PRIMARY <derived2> ref key0 key0 5 test.t1.b 10 Using where
+2 LATERAL DERIVED t10 ref grp_id grp_id 5 test.t1.b 100
+2 LATERAL DERIVED t11 hash_ALL NULL #hash#$hj 5 test.t10.col1 10 Using where; Using join buffer (flat, BNLH join)
+select *
+from
+(
+(t1 left join t2 on t2.a=t1.b)
+left join
+t3
+on t3.a=t1.b
+)
+left join
+(
+select grp_id, count(*)
+from t10 left join t11 on t11.col1=t10.col1
+group by grp_id
+)dt
+on dt.grp_id=t1.b;
+a b a b a b grp_id count(*)
+1 1 1 1 1 1 1 100
+1 1 1 1 1 2 1 100
+1 1 1 1 1 3 1 100
+1 1 1 2 1 1 1 100
+1 1 1 2 1 2 1 100
+1 1 1 2 1 3 1 100
+2 2 2 1 2 1 2 100
+2 2 2 1 2 2 2 100
+2 2 2 1 2 3 2 100
+2 2 2 2 2 1 2 100
+2 2 2 2 2 2 2 100
+2 2 2 2 2 3 2 100
+3 3 3 1 3 1 3 100
+3 3 3 1 3 2 3 100
+3 3 3 1 3 3 3 100
+3 3 3 2 3 1 3 100
+3 3 3 2 3 2 3 100
+3 3 3 2 3 3 3 100
+4 4 4 1 4 1 4 100
+4 4 4 1 4 2 4 100
+4 4 4 1 4 3 4 100
+4 4 4 2 4 1 4 100
+4 4 4 2 4 2 4 100
+4 4 4 2 4 3 4 100
+5 5 5 1 5 1 5 100
+5 5 5 1 5 2 5 100
+5 5 5 1 5 3 5 100
+5 5 5 2 5 1 5 100
+5 5 5 2 5 2 5 100
+5 5 5 2 5 3 5 100
+set join_cache_level=default;
+drop index a on t2;
+drop index a on t3;
+explain select *
+from
+(
+(t1 left join t2 on t2.a=t1.b)
+left join
+t3
+on t3.a=t1.b
+)
+left join
+(
+select grp_id, count(*)
+from t10 left join t11 on t11.col1=t10.col1
+group by grp_id
+)dt
+on dt.grp_id=t1.b;
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t1 ALL NULL NULL NULL NULL 5
+1 PRIMARY t2 ALL NULL NULL NULL NULL 50 Using where; Using join buffer (flat, BNL join)
+1 PRIMARY t3 ALL NULL NULL NULL NULL 15 Using where; Using join buffer (incremental, BNL join)
+1 PRIMARY <derived2> ref key0 key0 5 test.t1.b 1000 Using where
+2 DERIVED t10 ALL grp_id NULL NULL NULL 10000 Using temporary; Using filesort
+2 DERIVED t11 ALL NULL NULL NULL NULL 10 Using where; Using join buffer (flat, BNL join)
+select *
+from
+(
+(t1 left join t2 on t2.a=t1.b)
+left join
+t3
+on t3.a=t1.b
+)
+left join
+(
+select grp_id, count(*)
+from t10 left join t11 on t11.col1=t10.col1
+group by grp_id
+)dt
+on dt.grp_id=t1.b;
+a b a b a b grp_id count(*)
+1 1 1 1 1 1 1 100
+1 1 1 2 1 1 1 100
+1 1 1 1 1 2 1 100
+1 1 1 2 1 2 1 100
+1 1 1 1 1 3 1 100
+1 1 1 2 1 3 1 100
+2 2 2 1 2 1 2 100
+2 2 2 2 2 1 2 100
+2 2 2 1 2 2 2 100
+2 2 2 2 2 2 2 100
+2 2 2 1 2 3 2 100
+2 2 2 2 2 3 2 100
+3 3 3 1 3 1 3 100
+3 3 3 2 3 1 3 100
+3 3 3 1 3 2 3 100
+3 3 3 2 3 2 3 100
+3 3 3 1 3 3 3 100
+3 3 3 2 3 3 3 100
+4 4 4 1 4 1 4 100
+4 4 4 2 4 1 4 100
+4 4 4 1 4 2 4 100
+4 4 4 2 4 2 4 100
+4 4 4 1 4 3 4 100
+4 4 4 2 4 3 4 100
+5 5 5 1 5 1 5 100
+5 5 5 2 5 1 5 100
+5 5 5 1 5 2 5 100
+5 5 5 2 5 2 5 100
+5 5 5 1 5 3 5 100
+5 5 5 2 5 3 5 100
+drop table t1,t2,t3;
+drop table t10, t11;
+#
+# MDEV-31194: Server crash or assertion failure with join_cache_level=4
+# (a followup to the above bug, MDEV-26301)
+#
+CREATE TABLE t1 (a INT);
+INSERT INTO t1 VALUES (3),(4);
+CREATE TABLE t2 (id INT PRIMARY KEY) ENGINE=Aria;
+INSERT INTO t2 VALUES (1),(2);
+set @tmp1= @@optimizer_switch, @tmp2= @@join_cache_level;
+set
+optimizer_switch= 'derived_with_keys=off',
+join_cache_level= 4;
+SELECT t1.* FROM t1 JOIN (SELECT id, COUNT(*) FROM t2 GROUP BY id) sq ON sq.id= t1.a;
+a
+set optimizer_switch= @tmp1, join_cache_level= @tmp2;
+DROP TABLE t1, t2;
+# End of 10.4 tests
diff --git a/mysql-test/main/derived_split_innodb.test b/mysql-test/main/derived_split_innodb.test
index 2f74f5fe747..b8dd5ad20e1 100644
--- a/mysql-test/main/derived_split_innodb.test
+++ b/mysql-test/main/derived_split_innodb.test
@@ -227,3 +227,239 @@ where t3.b > 15;
drop table t3, t4;
--echo # End of 10.3 tests
+
+--source include/have_sequence.inc
+
+--echo #
+--echo # MDEV-26301: Split optimization refills temporary table too many times
+--echo #
+
+# 5 values
+create table t1(a int, b int);
+insert into t1 select seq,seq from seq_1_to_5;
+
+# 5 value groups of size 2 each
+create table t2(a int, b int, key(a));
+insert into t2
+select A.seq,B.seq from seq_1_to_25 A, seq_1_to_2 B;
+
+# 5 value groups of size 3 each
+create table t3(a int, b int, key(a));
+insert into t3
+select A.seq,B.seq from seq_1_to_5 A, seq_1_to_3 B;
+
+analyze table t1,t2,t3 persistent for all;
+
+explain
+select * from
+ (t1 left join t2 on t2.a=t1.b) left join t3 on t3.a=t1.b;
+
+# Now, create tables for Groups.
+
+create table t10 (
+ grp_id int,
+ col1 int,
+ key(grp_id)
+);
+
+# 100 groups of 100 values each
+insert into t10
+select
+ A.seq,
+ B.seq
+from
+ seq_1_to_100 A,
+ seq_1_to_100 B;
+
+# and X10 multiplier
+
+create table t11 (
+ col1 int,
+ col2 int
+);
+insert into t11
+select A.seq, A.seq from seq_1_to_10 A;
+
+analyze table t10,t11 persistent for all;
+
+let $q1=
+select * from
+ (
+ (t1 left join t2 on t2.a=t1.b)
+ left join t3 on t3.a=t1.b
+ ) left join (select grp_id, count(*)
+ from t10 left join t11 on t11.col1=t10.col1
+ group by grp_id) T on T.grp_id=t1.b;
+
+eval
+explain $q1;
+
+--echo # The important part in the below output is:
+--echo # "lateral": 1,
+--echo # "query_block": {
+--echo # "select_id": 2,
+--echo # "r_loops": 5, <-- must be 5, not 30.
+--source include/analyze-format.inc
+
+eval
+analyze format=json $q1;
+
+create table t21 (pk int primary key);
+insert into t21 values (1),(2),(3);
+
+create table t22 (pk int primary key);
+insert into t22 values (1),(2),(3);
+
+# Same as above but throw in a couple of const tables.
+explain
+select * from
+ t21, t22,
+ (
+ (t1 left join t2 on t2.a=t1.b)
+ left join t3 on t3.a=t1.b
+ ) left join (select grp_id, count(*)
+ from t10 left join t11 on t11.col1=t10.col1
+ group by grp_id) T on T.grp_id=t1.b
+where
+ t21.pk=1 and t22.pk=2;
+
+explain
+select * from
+ t21,
+ (
+ (t1 left join t2 on t2.a=t1.b)
+ left join t3 on t3.a=t1.b
+ ) left join (select grp_id, count(*)
+ from
+ t22 join t10 left join t11 on t11.col1=t10.col1
+ where
+ t22.pk=1
+ group by grp_id) T on T.grp_id=t1.b
+where
+ t21.pk=1;
+
+# And also add a non-const table
+
+create table t5 (
+ pk int primary key
+ );
+insert into t5 select seq from seq_1_to_1000;
+
+explain
+select * from
+ t21,
+ (
+ (((t1 join t5 on t5.pk=t1.b)) left join t2 on t2.a=t1.b)
+ left join t3 on t3.a=t1.b
+ ) left join (select grp_id, count(*)
+ from
+ t22 join t10 left join t11 on t11.col1=t10.col1
+ where
+ t22.pk=1
+ group by grp_id) T on T.grp_id=t1.b
+where
+ t21.pk=1;
+
+drop table t1,t2,t3,t5, t10, t11, t21, t22;
+
+# 5 values
+create table t1(a int, b int);
+insert into t1 select seq,seq from seq_1_to_5;
+
+# 5 value groups of size 2 each
+create table t2(a int, b int, key(a));
+insert into t2
+select A.seq,B.seq from seq_1_to_25 A, seq_1_to_2 B;
+
+# 5 value groups of size 3 each
+create table t3(a int, b int, key(a));
+insert into t3
+select A.seq,B.seq from seq_1_to_5 A, seq_1_to_3 B;
+
+analyze table t1,t2,t3 persistent for all;
+
+create table t10 (
+ grp_id int,
+ col1 int,
+ key(grp_id)
+);
+
+# 100 groups of 100 values each
+insert into t10
+select
+ A.seq,
+ B.seq
+from
+ seq_1_to_100 A,
+ seq_1_to_100 B;
+
+# and X10 multiplier
+
+create table t11 (
+ col1 int,
+ col2 int
+);
+insert into t11
+select A.seq, A.seq from seq_1_to_10 A;
+
+analyze table t10,t11 persistent for all;
+
+let $q=
+select *
+from
+ (
+ (t1 left join t2 on t2.a=t1.b)
+ left join
+ t3
+ on t3.a=t1.b
+ )
+ left join
+ (
+ select grp_id, count(*)
+ from t10 left join t11 on t11.col1=t10.col1
+ group by grp_id
+ )dt
+ on dt.grp_id=t1.b;
+
+eval explain $q;
+eval $q;
+
+set join_cache_level=4;
+eval explain $q;
+eval $q;
+
+set join_cache_level=default;
+
+drop index a on t2;
+drop index a on t3;
+
+eval explain $q;
+eval $q;
+
+drop table t1,t2,t3;
+drop table t10, t11;
+
+
+--echo #
+--echo # MDEV-31194: Server crash or assertion failure with join_cache_level=4
+--echo # (a followup to the above bug, MDEV-26301)
+--echo #
+CREATE TABLE t1 (a INT);
+INSERT INTO t1 VALUES (3),(4);
+
+CREATE TABLE t2 (id INT PRIMARY KEY) ENGINE=Aria;
+INSERT INTO t2 VALUES (1),(2);
+
+set @tmp1= @@optimizer_switch, @tmp2= @@join_cache_level;
+set
+ optimizer_switch= 'derived_with_keys=off',
+ join_cache_level= 4;
+
+SELECT t1.* FROM t1 JOIN (SELECT id, COUNT(*) FROM t2 GROUP BY id) sq ON sq.id= t1.a;
+
+set optimizer_switch= @tmp1, join_cache_level= @tmp2;
+
+# Cleanup
+DROP TABLE t1, t2;
+
+--echo # End of 10.4 tests
diff --git a/mysql-test/main/explain_innodb.result b/mysql-test/main/explain_innodb.result
index fe51e45e35d..d49b29d940b 100644
--- a/mysql-test/main/explain_innodb.result
+++ b/mysql-test/main/explain_innodb.result
@@ -18,3 +18,6 @@ id select_type table type possible_keys key key_len ref rows Extra
2 DERIVED t1 index NULL id 53 NULL 1 Using index
SET GLOBAL slow_query_log = @sql_tmp;
drop table t1;
+#
+# End of 10.4 tests
+#
diff --git a/mysql-test/main/explain_innodb.test b/mysql-test/main/explain_innodb.test
index 2c29a6e26da..e72cae2cf23 100644
--- a/mysql-test/main/explain_innodb.test
+++ b/mysql-test/main/explain_innodb.test
@@ -18,3 +18,7 @@ SELECT * FROM (SELECT id FROM t1 GROUP BY id) dt WHERE 1=0;
SET GLOBAL slow_query_log = @sql_tmp;
drop table t1;
+
+--echo #
+--echo # End of 10.4 tests
+--echo #
diff --git a/mysql-test/main/insert_update.result b/mysql-test/main/insert_update.result
index 68a1003ad85..83344971c59 100644
--- a/mysql-test/main/insert_update.result
+++ b/mysql-test/main/insert_update.result
@@ -412,3 +412,44 @@ select if( @stamp1 = @stamp2, "correct", "wrong");
if( @stamp1 = @stamp2, "correct", "wrong")
correct
drop table t1;
+#
+# MDEV-31164 default current_timestamp() not working when used INSERT ON DUPLICATE KEY in some cases
+#
+set timestamp=unix_timestamp('2000-10-20 0:0:0');
+create table t1 (pk integer primary key, val varchar(20) not null, ts timestamp);
+insert t1 (pk, val) values(1, 'val1');
+select * from t1;
+pk val ts
+1 val1 2000-10-20 00:00:00
+set timestamp=unix_timestamp('2000-10-20 1:0:0');
+insert t1 (pk, val) select 2, 'val3' union select 3, 'val4'
+ on duplicate key update ts=now();
+select * from t1;
+pk val ts
+1 val1 2000-10-20 00:00:00
+2 val3 2000-10-20 01:00:00
+3 val4 2000-10-20 01:00:00
+set timestamp=unix_timestamp('2000-10-20 2:0:0');
+insert t1 (pk, val) select 1, 'val1' union select 4, 'val2'
+ on duplicate key update ts=now();
+select * from t1;
+pk val ts
+1 val1 2000-10-20 02:00:00
+2 val3 2000-10-20 01:00:00
+3 val4 2000-10-20 01:00:00
+4 val2 2000-10-20 02:00:00
+set timestamp=unix_timestamp('2000-10-20 3:0:0');
+insert t1 (pk, val) select 5, 'val1' union select 1, 'val2'
+ on duplicate key update ts=now();
+select * from t1;
+pk val ts
+1 val1 2000-10-20 03:00:00
+2 val3 2000-10-20 01:00:00
+3 val4 2000-10-20 01:00:00
+4 val2 2000-10-20 02:00:00
+5 val1 2000-10-20 03:00:00
+drop table t1;
+set timestamp=default;
+#
+# End of 10.4 tests
+#
diff --git a/mysql-test/main/insert_update.test b/mysql-test/main/insert_update.test
index 06e16be84d7..25953938ad1 100644
--- a/mysql-test/main/insert_update.test
+++ b/mysql-test/main/insert_update.test
@@ -311,3 +311,29 @@ insert into t1(f1) values(1) on duplicate key update f1=1;
select @stamp2:=f2 from t1;
select if( @stamp1 = @stamp2, "correct", "wrong");
drop table t1;
+
+--echo #
+--echo # MDEV-31164 default current_timestamp() not working when used INSERT ON DUPLICATE KEY in some cases
+--echo #
+set timestamp=unix_timestamp('2000-10-20 0:0:0');
+create table t1 (pk integer primary key, val varchar(20) not null, ts timestamp);
+insert t1 (pk, val) values(1, 'val1');
+select * from t1;
+set timestamp=unix_timestamp('2000-10-20 1:0:0');
+insert t1 (pk, val) select 2, 'val3' union select 3, 'val4'
+ on duplicate key update ts=now();
+select * from t1;
+set timestamp=unix_timestamp('2000-10-20 2:0:0');
+insert t1 (pk, val) select 1, 'val1' union select 4, 'val2'
+ on duplicate key update ts=now();
+select * from t1;
+set timestamp=unix_timestamp('2000-10-20 3:0:0');
+insert t1 (pk, val) select 5, 'val1' union select 1, 'val2'
+ on duplicate key update ts=now();
+select * from t1;
+drop table t1;
+set timestamp=default;
+
+--echo #
+--echo # End of 10.4 tests
+--echo #
diff --git a/mysql-test/main/opt_trace.result b/mysql-test/main/opt_trace.result
index a7a8fb88e6d..7856f9248ba 100644
--- a/mysql-test/main/opt_trace.result
+++ b/mysql-test/main/opt_trace.result
@@ -450,6 +450,11 @@ select * from v2 {
]
},
{
+ "check_split_materialized": {
+ "not_applicable": "no candidate field can be accessed through ref"
+ }
+ },
+ {
"best_join_order": ["t1"]
},
{
@@ -773,6 +778,11 @@ explain select * from v1 {
]
},
{
+ "check_split_materialized": {
+ "not_applicable": "group list has no candidates"
+ }
+ },
+ {
"best_join_order": ["t1"]
},
{
@@ -8861,5 +8871,109 @@ SET optimizer_trace=DEFAULT;
DROP VIEW v;
DROP TABLE t;
#
+# MDEV-26301: Split optimization improvements: Optimizer Trace coverage
+#
+create table t1(a int, b int);
+insert into t1 select seq,seq from seq_1_to_5;
+create table t2(a int, b int, key(a));
+insert into t2
+select A.seq,B.seq from seq_1_to_25 A, seq_1_to_2 B;
+create table t3(a int, b int, key(a));
+insert into t3
+select A.seq,B.seq from seq_1_to_5 A, seq_1_to_3 B;
+analyze table t1,t2,t3 persistent for all;
+Table Op Msg_type Msg_text
+test.t1 analyze status Engine-independent statistics collected
+test.t1 analyze status OK
+test.t2 analyze status Engine-independent statistics collected
+test.t2 analyze status Table is already up to date
+test.t3 analyze status Engine-independent statistics collected
+test.t3 analyze status Table is already up to date
+create table t10 (
+grp_id int,
+col1 int,
+key(grp_id)
+);
+insert into t10
+select
+A.seq,
+B.seq
+from
+seq_1_to_100 A,
+seq_1_to_100 B;
+create table t11 (
+col1 int,
+col2 int
+);
+insert into t11
+select A.seq, A.seq from seq_1_to_10 A;
+analyze table t10,t11 persistent for all;
+Table Op Msg_type Msg_text
+test.t10 analyze status Engine-independent statistics collected
+test.t10 analyze status Table is already up to date
+test.t11 analyze status Engine-independent statistics collected
+test.t11 analyze status OK
+set optimizer_trace=1;
+explain
+select * from
+(
+(t1 left join t2 on t2.a=t1.b)
+left join t3 on t3.a=t1.b
+) left join (select grp_id, count(*)
+from t10 left join t11 on t11.col1=t10.col1
+group by grp_id) T on T.grp_id=t1.b;
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t1 ALL NULL NULL NULL NULL 5
+1 PRIMARY t2 ref a a 5 test.t1.b 2 Using where
+1 PRIMARY t3 ref a a 5 test.t1.b 3 Using where
+1 PRIMARY <derived2> ref key0 key0 5 test.t1.b 10 Using where
+2 LATERAL DERIVED t10 ref grp_id grp_id 5 test.t1.b 100
+2 LATERAL DERIVED t11 ALL NULL NULL NULL NULL 10 Using where; Using join buffer (flat, BNL join)
+select json_detailed(json_extract(trace, '$**.check_split_materialized')) as JS
+from information_schema.optimizer_trace;
+JS
+[
+ {
+ "split_candidates":
+ ["t10.grp_id"]
+ }
+]
+select
+json_detailed(
+json_remove(
+json_extract(trace, '$**.choose_best_splitting')
+, '$[0].split_plan_search[0]'
+ )
+) as JS
+from information_schema.optimizer_trace;
+JS
+[
+ {
+ "considered_keys":
+ [
+ {
+ "table_name": "t10",
+ "index": "grp_id",
+ "rec_per_key": 100,
+ "param_tables": 1
+ }
+ ],
+ "refills": 5,
+ "spl_pd_boundary": 2,
+ "split_plan_search":
+ [],
+ "lead_table": "t10",
+ "index": "grp_id",
+ "parts": 1,
+ "split_sel": 0.001,
+ "cost": 2536,
+ "records": 100,
+ "refills": 5,
+ "chosen": true
+ }
+]
+drop table t1,t2,t3,t10,t11;
+set optimizer_trace=DEFAULT;
+#
# End of 10.4 tests
#
diff --git a/mysql-test/main/opt_trace.test b/mysql-test/main/opt_trace.test
index e0be5360069..2f1047df7c5 100644
--- a/mysql-test/main/opt_trace.test
+++ b/mysql-test/main/opt_trace.test
@@ -697,5 +697,75 @@ DROP VIEW v;
DROP TABLE t;
--echo #
+--echo # MDEV-26301: Split optimization improvements: Optimizer Trace coverage
+--echo #
+
+# 5 values
+create table t1(a int, b int);
+insert into t1 select seq,seq from seq_1_to_5;
+
+# 5 value groups of size 2 each
+create table t2(a int, b int, key(a));
+insert into t2
+select A.seq,B.seq from seq_1_to_25 A, seq_1_to_2 B;
+
+# 5 value groups of size 3 each
+create table t3(a int, b int, key(a));
+insert into t3
+select A.seq,B.seq from seq_1_to_5 A, seq_1_to_3 B;
+
+analyze table t1,t2,t3 persistent for all;
+
+create table t10 (
+ grp_id int,
+ col1 int,
+ key(grp_id)
+);
+
+# 100 groups of 100 values each
+insert into t10
+select
+ A.seq,
+ B.seq
+from
+ seq_1_to_100 A,
+ seq_1_to_100 B;
+
+# and X10 multiplier
+create table t11 (
+ col1 int,
+ col2 int
+);
+insert into t11
+select A.seq, A.seq from seq_1_to_10 A;
+
+analyze table t10,t11 persistent for all;
+
+set optimizer_trace=1;
+explain
+select * from
+ (
+ (t1 left join t2 on t2.a=t1.b)
+ left join t3 on t3.a=t1.b
+ ) left join (select grp_id, count(*)
+ from t10 left join t11 on t11.col1=t10.col1
+ group by grp_id) T on T.grp_id=t1.b;
+
+select json_detailed(json_extract(trace, '$**.check_split_materialized')) as JS
+from information_schema.optimizer_trace;
+
+select
+ json_detailed(
+ json_remove(
+ json_extract(trace, '$**.choose_best_splitting')
+ , '$[0].split_plan_search[0]'
+ )
+ ) as JS
+from information_schema.optimizer_trace;
+
+drop table t1,t2,t3,t10,t11;
+set optimizer_trace=DEFAULT;
+
+--echo #
--echo # End of 10.4 tests
--echo #
diff --git a/mysql-test/main/selectivity.test b/mysql-test/main/selectivity.test
index def74394ec5..b0c440718d1 100644
--- a/mysql-test/main/selectivity.test
+++ b/mysql-test/main/selectivity.test
@@ -1324,11 +1324,12 @@ DROP TABLE t1;
--echo # End of 10.2 tests
-set optimizer_use_condition_selectivity= @save_optimizer_use_condition_selectivity;
-set histogram_size=@save_histogram_size;
-set use_stat_tables= @save_use_stat_tables;
+
#
# Clean up
#
+set optimizer_use_condition_selectivity= @save_optimizer_use_condition_selectivity;
+set histogram_size=@save_histogram_size;
+set use_stat_tables= @save_use_stat_tables;
--source include/restore_charset.inc
set @@global.histogram_size=@save_histogram_size;
diff --git a/mysql-test/main/selectivity_innodb_notembedded.result b/mysql-test/main/selectivity_innodb_notembedded.result
new file mode 100644
index 00000000000..013f9196031
--- /dev/null
+++ b/mysql-test/main/selectivity_innodb_notembedded.result
@@ -0,0 +1,100 @@
+SET SESSION STORAGE_ENGINE='InnoDB';
+set @save_optimizer_switch_for_selectivity_test=@@optimizer_switch;
+set optimizer_switch='extended_keys=on';
+drop table if exists t0,t1,t2,t3;
+select @@global.use_stat_tables;
+@@global.use_stat_tables
+COMPLEMENTARY
+select @@session.use_stat_tables;
+@@session.use_stat_tables
+COMPLEMENTARY
+set @save_use_stat_tables=@@use_stat_tables;
+set use_stat_tables='preferably';
+set @save_optimizer_use_condition_selectivity=@@optimizer_use_condition_selectivity;
+set @save_histogram_size=@@histogram_size;
+set @save_histogram_type=@@histogram_type;
+set join_cache_level=2;
+set @@global.histogram_size=0,@@local.histogram_size=0;
+set histogram_type='single_prec_hb';
+set optimizer_use_condition_selectivity=3;
+#
+# MDEV-31067: selectivity_from_histogram >1.0 for a DOUBLE_PREC_HB histogram
+#
+create table t0(a int);
+insert into t0 select 1 from seq_1_to_78;
+create table t1(a int);
+insert into t1 select 1 from seq_1_to_26;
+create table t10 (a int);
+insert into t10 select 0 from t0, seq_1_to_4;
+insert into t10 select 8693 from t1;
+insert into t10 select 8694 from t1;
+insert into t10 select 8695 from t1;
+insert into t10 select 34783 from t1;
+insert into t10 select 34784 from t1;
+insert into t10 select 34785 from t1;
+insert into t10 select 34785 from t0, seq_1_to_8;
+insert into t10 select 65214 from t1;
+insert into t10 select 65215 from t1;
+insert into t10 select 65216 from t1;
+insert into t10 select 65216 from t0, seq_1_to_52;
+insert into t10 select 65217 from t1;
+insert into t10 select 65218 from t1;
+insert into t10 select 65219 from t1;
+insert into t10 select 65219 from t0;
+insert into t10 select 73913 from t1;
+insert into t10 select 73914 from t1;
+insert into t10 select 73915 from t1;
+insert into t10 select 73915 from t0, seq_1_to_40;
+insert into t10 select 78257 from t1;
+insert into t10 select 78258 from t1;
+insert into t10 select 78259 from t1;
+insert into t10 select 91300 from t1;
+insert into t10 select 91301 from t1;
+insert into t10 select 91302 from t1;
+insert into t10 select 91302 from t0, seq_1_to_6;
+insert into t10 select 91303 from t1;
+insert into t10 select 91304 from t1;
+insert into t10 select 91305 from t1;
+insert into t10 select 91305 from t0, seq_1_to_8;
+insert into t10 select 99998 from t1;
+insert into t10 select 99999 from t1;
+insert into t10 select 100000 from t1;
+set use_stat_tables=preferably;
+analyze table t10 persistent for all;
+Table Op Msg_type Msg_text
+test.t10 analyze status Engine-independent statistics collected
+test.t10 analyze status OK
+flush tables;
+set @tmp=@@optimizer_trace;
+set optimizer_trace=1;
+explain select * from t10 where a in (91303);
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t10 ALL NULL NULL NULL NULL 9984 Using where
+# Must have selectivity_from_histogram <= 1.0:
+select json_detailed(json_extract(trace, '$**.selectivity_for_columns')) as sel
+from information_schema.optimizer_trace;
+sel
+[
+ [
+ {
+ "column_name": "a",
+ "ranges":
+ ["91303 <= a <= 91303"],
+ "selectivity_from_histogram": 0.0357
+ }
+ ]
+]
+set optimizer_trace=@tmp;
+drop table t0,t1,t10;
+set optimizer_use_condition_selectivity= @save_optimizer_use_condition_selectivity;
+set histogram_size=@save_histogram_size;
+set use_stat_tables= @save_use_stat_tables;
+#
+# End of 10.4 tests
+#
+#
+# Clean up
+#
+set @@global.histogram_size=@save_histogram_size;
+set optimizer_switch=@save_optimizer_switch_for_selectivity_test;
+SET SESSION STORAGE_ENGINE=DEFAULT;
diff --git a/mysql-test/main/selectivity_innodb_notembedded.test b/mysql-test/main/selectivity_innodb_notembedded.test
new file mode 100644
index 00000000000..387f7dcb7de
--- /dev/null
+++ b/mysql-test/main/selectivity_innodb_notembedded.test
@@ -0,0 +1,16 @@
+--source include/have_innodb.inc
+# This test is slow on buildbot.
+--source include/big_test.inc
+--source include/default_optimizer_switch.inc
+--source include/not_embedded.inc
+
+SET SESSION STORAGE_ENGINE='InnoDB';
+
+set @save_optimizer_switch_for_selectivity_test=@@optimizer_switch;
+set optimizer_switch='extended_keys=on';
+
+--source selectivity_notembedded.test
+
+set optimizer_switch=@save_optimizer_switch_for_selectivity_test;
+
+SET SESSION STORAGE_ENGINE=DEFAULT;
diff --git a/mysql-test/main/selectivity_notembedded.result b/mysql-test/main/selectivity_notembedded.result
index 8b298a95801..7388c17e991 100644
--- a/mysql-test/main/selectivity_notembedded.result
+++ b/mysql-test/main/selectivity_notembedded.result
@@ -1,14 +1,22 @@
+drop table if exists t0,t1,t2,t3;
+select @@global.use_stat_tables;
+@@global.use_stat_tables
+COMPLEMENTARY
+select @@session.use_stat_tables;
+@@session.use_stat_tables
+COMPLEMENTARY
+set @save_use_stat_tables=@@use_stat_tables;
+set use_stat_tables='preferably';
+set @save_optimizer_use_condition_selectivity=@@optimizer_use_condition_selectivity;
+set @save_histogram_size=@@histogram_size;
+set @save_histogram_type=@@histogram_type;
+set join_cache_level=2;
+set @@global.histogram_size=0,@@local.histogram_size=0;
+set histogram_type='single_prec_hb';
+set optimizer_use_condition_selectivity=3;
#
# MDEV-31067: selectivity_from_histogram >1.0 for a DOUBLE_PREC_HB histogram
#
-set @save_histogram_size=@@histogram_size;
-set @save_histogram_type=@@histogram_type;
-set @save_use_stat_tables=@@use_stat_tables;
-set @save_optimizer_use_condition_selectivity=@@optimizer_use_condition_selectivity;
-SET histogram_size= 255;
-set histogram_type='DOUBLE_PREC_HB';
-set use_stat_tables=preferably;
-SET optimizer_use_condition_selectivity=3;
create table t0(a int);
insert into t0 select 1 from seq_1_to_78;
create table t1(a int);
@@ -48,6 +56,7 @@ insert into t10 select 91305 from t0, seq_1_to_8;
insert into t10 select 99998 from t1;
insert into t10 select 99999 from t1;
insert into t10 select 100000 from t1;
+set use_stat_tables=preferably;
analyze table t10 persistent for all;
Table Op Msg_type Msg_text
test.t10 analyze status Engine-independent statistics collected
@@ -59,9 +68,9 @@ explain select * from t10 where a in (91303);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t10 ALL NULL NULL NULL NULL 9984 Using where
# Must have selectivity_from_histogram <= 1.0:
-select json_detailed(json_extract(trace, '$**.selectivity_for_columns'))
+select json_detailed(json_extract(trace, '$**.selectivity_for_columns')) as sel
from information_schema.optimizer_trace;
-json_detailed(json_extract(trace, '$**.selectivity_for_columns'))
+sel
[
[
{
@@ -72,12 +81,15 @@ json_detailed(json_extract(trace, '$**.selectivity_for_columns'))
}
]
]
-drop table t0,t1,t10;
set optimizer_trace=@tmp;
-set @@histogram_size=@save_histogram_size;
-set @histogram_type=@save_histogram_type;
-set @use_stat_tables=@save_use_stat_tables;
-set @@optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity;
+drop table t0,t1,t10;
+set optimizer_use_condition_selectivity= @save_optimizer_use_condition_selectivity;
+set histogram_size=@save_histogram_size;
+set use_stat_tables= @save_use_stat_tables;
#
# End of 10.4 tests
#
+#
+# Clean up
+#
+set @@global.histogram_size=@save_histogram_size;
diff --git a/mysql-test/main/selectivity_notembedded.test b/mysql-test/main/selectivity_notembedded.test
index f79c370186f..6752bd3c7e1 100644
--- a/mysql-test/main/selectivity_notembedded.test
+++ b/mysql-test/main/selectivity_notembedded.test
@@ -1,21 +1,34 @@
+--source include/no_valgrind_without_big.inc
--source include/have_stat_tables.inc
--source include/have_sequence.inc
+--source include/default_charset.inc
--source include/not_embedded.inc
---echo #
---echo # MDEV-31067: selectivity_from_histogram >1.0 for a DOUBLE_PREC_HB histogram
---echo #
+--disable_warnings
+drop table if exists t0,t1,t2,t3;
+--enable_warnings
+
+select @@global.use_stat_tables;
+select @@session.use_stat_tables;
-set @save_histogram_size=@@histogram_size;
-set @save_histogram_type=@@histogram_type;
set @save_use_stat_tables=@@use_stat_tables;
+set use_stat_tables='preferably';
+
+--source include/default_optimizer_switch.inc
set @save_optimizer_use_condition_selectivity=@@optimizer_use_condition_selectivity;
+set @save_histogram_size=@@histogram_size;
+set @save_histogram_type=@@histogram_type;
+set join_cache_level=2;
+set @@global.histogram_size=0,@@local.histogram_size=0;
+set histogram_type='single_prec_hb';
-SET histogram_size= 255;
-set histogram_type='DOUBLE_PREC_HB';
-set use_stat_tables=preferably;
-SET optimizer_use_condition_selectivity=3;
+# check that statistics on nulls is used
+
+set optimizer_use_condition_selectivity=3;
+--echo #
+--echo # MDEV-31067: selectivity_from_histogram >1.0 for a DOUBLE_PREC_HB histogram
+--echo #
create table t0(a int); # This holds how many rows we hold in a bucket.
insert into t0 select 1 from seq_1_to_78;
@@ -77,6 +90,7 @@ insert into t10 select 99998 from t1;
insert into t10 select 99999 from t1;
insert into t10 select 100000 from t1;
+set use_stat_tables=preferably;
analyze table t10 persistent for all;
flush tables;
@@ -85,18 +99,23 @@ set optimizer_trace=1;
explain select * from t10 where a in (91303);
--echo # Must have selectivity_from_histogram <= 1.0:
---replace_result 0.035714283 0.0357
-select json_detailed(json_extract(trace, '$**.selectivity_for_columns'))
+select json_detailed(json_extract(trace, '$**.selectivity_for_columns')) as sel
from information_schema.optimizer_trace;
+set optimizer_trace=@tmp;
drop table t0,t1,t10;
-set optimizer_trace=@tmp;
-set @@histogram_size=@save_histogram_size;
-set @histogram_type=@save_histogram_type;
-set @use_stat_tables=@save_use_stat_tables;
-set @@optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity;
+set optimizer_use_condition_selectivity= @save_optimizer_use_condition_selectivity;
+set histogram_size=@save_histogram_size;
+set use_stat_tables= @save_use_stat_tables;
+
--echo #
--echo # End of 10.4 tests
--echo #
+
+--echo #
+--echo # Clean up
+--echo #
+--source include/restore_charset.inc
+set @@global.histogram_size=@save_histogram_size;
diff --git a/mysql-test/main/view.result b/mysql-test/main/view.result
index 8c31545eb84..97d19aa2690 100644
--- a/mysql-test/main/view.result
+++ b/mysql-test/main/view.result
@@ -6956,4 +6956,22 @@ create algorithm=merge view v as
select * from t1 left join t2 on t1.a=t2.b and t1.a in (select d from t3);
ERROR 42S22: Unknown column 'd' in 'field list'
drop table t1,t2,t3;
+#
+# MDEV-31189: Server crash or assertion failure in upon 2nd
+# execution of PS with views and HAVING
+#
+CREATE TABLE t (f INT);
+INSERT INTO t VALUES (1),(2);
+CREATE VIEW v1 AS SELECT 1 AS a;
+CREATE VIEW v2 AS SELECT a FROM v1;
+PREPARE stmt FROM "SELECT * FROM v2 HAVING 1 IN (SELECT f FROM t)";
+EXECUTE stmt;
+a
+1
+EXECUTE stmt;
+a
+1
+DROP VIEW v1;
+DROP VIEW v2;
+DROP TABLE t;
# End of 10.4 tests
diff --git a/mysql-test/main/view.test b/mysql-test/main/view.test
index 0e2dce1fb70..385ca523436 100644
--- a/mysql-test/main/view.test
+++ b/mysql-test/main/view.test
@@ -6696,4 +6696,23 @@ create algorithm=merge view v as
drop table t1,t2,t3;
+--echo #
+--echo # MDEV-31189: Server crash or assertion failure in upon 2nd
+--echo # execution of PS with views and HAVING
+--echo #
+
+CREATE TABLE t (f INT);
+INSERT INTO t VALUES (1),(2); # Optional, fails either way
+CREATE VIEW v1 AS SELECT 1 AS a;
+CREATE VIEW v2 AS SELECT a FROM v1;
+
+PREPARE stmt FROM "SELECT * FROM v2 HAVING 1 IN (SELECT f FROM t)";
+EXECUTE stmt;
+EXECUTE stmt;
+
+# Cleanup
+DROP VIEW v1;
+DROP VIEW v2;
+DROP TABLE t;
+
--echo # End of 10.4 tests
diff --git a/mysql-test/std_data/rpl/master-bin-seq_10.3.36.000001 b/mysql-test/std_data/rpl/master-bin-seq_10.3.36.000001
new file mode 100644
index 00000000000..0fa163d0484
--- /dev/null
+++ b/mysql-test/std_data/rpl/master-bin-seq_10.3.36.000001
Binary files differ
diff --git a/mysql-test/suite/rpl/r/rpl_parallel_seq.result b/mysql-test/suite/rpl/r/rpl_parallel_seq.result
new file mode 100644
index 00000000000..60061049ed4
--- /dev/null
+++ b/mysql-test/suite/rpl/r/rpl_parallel_seq.result
@@ -0,0 +1,85 @@
+include/master-slave.inc
+[connection master]
+connection slave;
+include/stop_slave.inc
+ALTER TABLE mysql.gtid_slave_pos ENGINE=InnoDB;
+# MDEV-29621 the sequence engine binlog_row_image-full events
+# MDL-deadlock on the parallel slave.
+connection master;
+CREATE SEQUENCE s1;
+SET @@session.binlog_row_image=FULL;
+SET @@session.debug_dbug="+d,binlog_force_commit_id";
+SET @commit_id=7;
+SET @@gtid_seq_no=100;
+SELECT NEXT VALUE FOR s1;
+NEXT VALUE FOR s1
+1
+INSERT INTO s1 VALUES(2, 1, 10, 1, 2, 1, 1, 0);
+SET @@session.debug_dbug="";
+connection slave;
+SET @@global.slave_parallel_threads=2;
+SET @@global.slave_parallel_mode=optimistic;
+SET @@global.debug_dbug="+d,hold_worker_on_schedule";
+include/start_slave.inc
+SET DEBUG_SYNC = 'now SIGNAL continue_worker';
+connection master;
+DROP SEQUENCE s1;
+connection slave;
+include/stop_slave.inc
+# Simulate buggy 10.3.36 master to prove the parallel applier
+# does not deadlock now at replaying the above master load.
+connection master;
+include/rpl_stop_server.inc [server_number=1]
+include/rpl_start_server.inc [server_number=1]
+connection slave;
+RESET MASTER;
+SET @@global.gtid_slave_pos="";
+CHANGE MASTER TO master_host='127.0.0.1', master_port=SERVER_MYPORT_1, master_user='root', master_use_gtid=slave_pos;
+START SLAVE UNTIL MASTER_GTID_POS='0-1-102';
+SET DEBUG_SYNC = 'now SIGNAL continue_worker';
+# Normal stop is expected
+include/wait_for_slave_to_stop.inc
+# MDEV-31077 ALTER SEQUENCE may end up in optimistic parallel slave binlog out-of-order
+# The test proves ALTER-SEQUENCE binlogs first before the following transaction does so.
+connection slave;
+include/stop_slave.inc
+Warnings:
+Note 1255 Slave already has been stopped
+RESET MASTER;
+SET @@global.gtid_slave_pos="";
+SET @@global.gtid_strict_mode=1;
+connection master;
+RESET MASTER;
+CREATE TABLE ti (a INT) ENGINE=innodb;
+CREATE SEQUENCE s2 ENGINE=innodb;
+SET @@gtid_seq_no=100;
+ALTER SEQUENCE s2 restart with 1;
+INSERT INTO ti SET a=1;
+include/save_master_gtid.inc
+SELECT @@global.gtid_binlog_state "Master gtid state";
+Master gtid state
+0-1-101
+connection slave;
+include/start_slave.inc
+SELECT @@global.gtid_binlog_state, @@global.gtid_slave_pos as "no 100,101 yet in both";
+@@global.gtid_binlog_state no 100,101 yet in both
+0-1-2 0-1-2
+SET DEBUG_SYNC = 'now SIGNAL continue_worker';
+# Normal sync with master proves the fixes correct
+include/sync_with_master_gtid.inc
+SELECT @@global.gtid_binlog_state, @@global.gtid_slave_pos as "all through 101 have been committed";
+@@global.gtid_binlog_state all through 101 have been committed
+0-1-101 0-1-101
+connection slave;
+include/stop_slave.inc
+SET debug_sync = RESET;
+SET @@global.slave_parallel_threads= 0;
+SET @@global.slave_parallel_mode= conservative;
+SET @@global.debug_dbug = "";
+SET @@global.gtid_strict_mode=0;
+include/start_slave.inc
+connection master;
+DROP SEQUENCE s2;
+DROP TABLE ti;
+connection slave;
+include/rpl_end.inc
diff --git a/mysql-test/suite/rpl/t/rpl_parallel_seq.test b/mysql-test/suite/rpl/t/rpl_parallel_seq.test
new file mode 100644
index 00000000000..741859bc588
--- /dev/null
+++ b/mysql-test/suite/rpl/t/rpl_parallel_seq.test
@@ -0,0 +1,131 @@
+--source include/have_innodb.inc
+--source include/have_debug.inc
+--source include/have_debug_sync.inc
+--source include/have_binlog_format_row.inc
+--source include/master-slave.inc
+
+--connection slave
+--source include/stop_slave.inc
+ALTER TABLE mysql.gtid_slave_pos ENGINE=InnoDB;
+
+--echo # MDEV-29621 the sequence engine binlog_row_image-full events
+--echo # MDL-deadlock on the parallel slave.
+--connection master
+CREATE SEQUENCE s1;
+SET @@session.binlog_row_image=FULL;
+SET @@session.debug_dbug="+d,binlog_force_commit_id";
+SET @commit_id=7;
+SET @@gtid_seq_no=100;
+SELECT NEXT VALUE FOR s1;
+INSERT INTO s1 VALUES(2, 1, 10, 1, 2, 1, 1, 0);
+SET @@session.debug_dbug="";
+
+--connection slave
+--let $slave_parallel_threads=`select @@global.slave_parallel_threads`
+--let $slave_parallel_mode=`select @@global.slave_parallel_mode`
+SET @@global.slave_parallel_threads=2;
+SET @@global.slave_parallel_mode=optimistic;
+SET @@global.debug_dbug="+d,hold_worker_on_schedule";
+--source include/start_slave.inc
+
+--let $wait_condition= SELECT count(*) = 1 FROM information_schema.processlist WHERE state LIKE "Waiting for prior transaction to start commit before starting%"
+--source include/wait_condition.inc
+SET DEBUG_SYNC = 'now SIGNAL continue_worker';
+
+--connection master
+DROP SEQUENCE s1;
+--sync_slave_with_master
+--source include/stop_slave.inc
+
+--echo # Simulate buggy 10.3.36 master to prove the parallel applier
+--echo # does not deadlock now at replaying the above master load.
+--connection master
+--let $datadir= `SELECT @@datadir`
+
+--let $rpl_server_number= 1
+--source include/rpl_stop_server.inc
+
+--remove_file $datadir/master-bin.000001
+--copy_file $MYSQL_TEST_DIR/std_data/rpl/master-bin-seq_10.3.36.000001 $datadir/master-bin.000001
+
+--let $rpl_server_number= 1
+--source include/rpl_start_server.inc
+
+--source include/wait_until_connected_again.inc
+--save_master_pos
+
+--connection slave
+RESET MASTER;
+SET @@global.gtid_slave_pos="";
+
+--replace_result $SERVER_MYPORT_1 SERVER_MYPORT_1
+eval CHANGE MASTER TO master_host='127.0.0.1', master_port=$SERVER_MYPORT_1, master_user='root', master_use_gtid=slave_pos;
+
+START SLAVE UNTIL MASTER_GTID_POS='0-1-102';
+
+--let $wait_condition= SELECT count(*) = 1 FROM information_schema.processlist WHERE state LIKE "Waiting for prior transaction to commit"
+--source include/wait_condition.inc
+SET DEBUG_SYNC = 'now SIGNAL continue_worker';
+
+--echo # Normal stop is expected
+--source include/wait_for_slave_to_stop.inc
+
+--echo # MDEV-31077 ALTER SEQUENCE may end up in optimistic parallel slave binlog out-of-order
+--echo # The test proves ALTER-SEQUENCE binlogs first before the following transaction does so.
+
+--connection slave
+--source include/stop_slave.inc
+RESET MASTER;
+SET @@global.gtid_slave_pos="";
+--let $slave_gtid_strict_mode=`select @@global.gtid_strict_mode`
+SET @@global.gtid_strict_mode=1;
+--connection master
+RESET MASTER;
+
+# Load from master
+CREATE TABLE ti (a INT) ENGINE=innodb;
+CREATE SEQUENCE s2 ENGINE=innodb;
+
+SET @@gtid_seq_no=100;
+ALTER SEQUENCE s2 restart with 1;
+INSERT INTO ti SET a=1;
+--source include/save_master_gtid.inc
+SELECT @@global.gtid_binlog_state "Master gtid state";
+
+--connection slave
+--source include/start_slave.inc
+
+--let $wait_condition= SELECT count(*) = 1 FROM information_schema.processlist WHERE state LIKE "Waiting for prior transaction to commit"
+--source include/wait_condition.inc
+
+SELECT @@global.gtid_binlog_state, @@global.gtid_slave_pos as "no 100,101 yet in both";
+
+# DEBUG_DBUG extension point of hold_worker_on_schedule is reused
+# (gets deployed) in Sql_cmd_alter_sequence::execute.
+SET DEBUG_SYNC = 'now SIGNAL continue_worker';
+
+--echo # Normal sync with master proves the fixes correct
+--source include/sync_with_master_gtid.inc
+
+SELECT @@global.gtid_binlog_state, @@global.gtid_slave_pos as "all through 101 have been committed";
+
+#
+# MDEV-29621/MDEV-31077 clean up.
+#
+--connection slave
+--source include/stop_slave.inc
+
+SET debug_sync = RESET;
+--eval SET @@global.slave_parallel_threads= $slave_parallel_threads
+--eval SET @@global.slave_parallel_mode= $slave_parallel_mode
+ SET @@global.debug_dbug = "";
+--eval SET @@global.gtid_strict_mode=$slave_gtid_strict_mode
+--source include/start_slave.inc
+
+--connection master
+DROP SEQUENCE s2;
+DROP TABLE ti;
+
+--sync_slave_with_master
+
+--source include/rpl_end.inc
View Lorry Controller Status