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, 866 insertions, 0 deletions

diff --git a/mysql-test/main/derived_split_innodb.result b/mysql-test/main/derived_split_innodb.result
new file mode 100644
index 00000000..31522379
--- /dev/null
+++ b/mysql-test/main/derived_split_innodb.result
@@ -0,0 +1,866 @@
+SET @save_innodb_stats_persistent=@@GLOBAL.innodb_stats_persistent;
+SET GLOBAL innodb_stats_persistent=0;
+#
+# MDEV-16917: do not use splitting for derived with join cache
+#
+CREATE TABLE t1 (
+n1 int(10) NOT NULL,
+n2 int(10) NOT NULL,
+c1 char(1) NOT NULL,
+KEY c1 (c1),
+KEY n1_c1_n2 (n1,c1,n2)
+) ENGINE=InnoDB;
+INSERT INTO t1 VALUES (0, 2, 'a'), (1, 3, 'a');
+insert into t1 select seq+1,seq+2,'c' from seq_1_to_1000;
+ANALYZE TABLE t1;
+Table	Op	Msg_type	Msg_text
+test.t1	analyze	status	Engine-independent statistics collected
+test.t1	analyze	status	OK
+EXPLAIN SELECT t1.n1 FROM t1, (SELECT n1, n2 FROM t1 WHERE c1 = 'a' GROUP BY n1) as t
+WHERE t.n1 = t1.n1 AND t.n2 = t1.n2 AND c1 = 'a' GROUP BY n1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ref	c1,n1_c1_n2	c1	1	const	2	Using index condition; Using where; Using temporary; Using filesort
+1	PRIMARY	<derived2>	ref	key0	key0	8	test.t1.n1,test.t1.n2	2	
+2	LATERAL DERIVED	t1	ref	c1,n1_c1_n2	n1_c1_n2	4	test.t1.n1	1	Using where; Using index
+SELECT t1.n1 FROM t1, (SELECT n1, n2 FROM t1 WHERE c1 = 'a' GROUP BY n1) as t
+WHERE t.n1 = t1.n1 AND t.n2 = t1.n2 AND c1 = 'a' GROUP BY n1;
+n1
+0
+1
+DROP TABLE t1;
+#
+# MDEV-17211: splittable materialized derived joining 3 tables with
+#             GROUP BY list containing fields from 2 of them
+#
+CREATE TABLE t1 (
+id1 int, i1 int, id2 int,
+PRIMARY KEY (id1), KEY (i1), KEY (id2)
+) ENGINE=InnoDB;
+INSERT INTO t1 VALUES (1,1,1);
+CREATE TABLE t2 (id2 int, i2 int) ENGINE=InnoDB;
+INSERT INTO t2  VALUES (1, 1);
+CREATE TABLE t3 (id3 int, i3 int, PRIMARY KEY (id3)) ENGINE=InnoDB;
+INSERT INTO t3 VALUES (1,1);
+EXPLAIN SELECT id3
+FROM (SELECT t3.id3, t2.i2, t1.id2  FROM t3,t1,t2
+WHERE t3.i3=t1.id1 AND t2.id2=t1.id2
+GROUP BY t3.id3, t1.id2) AS t,
+t2
+WHERE t2.id2=t.id2;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	1	Using where
+1	PRIMARY	<derived2>	ref	key0	key0	5	test.t2.id2	2	
+2	DERIVED	t3	ALL	NULL	NULL	NULL	NULL	1	Using where; Using temporary; Using filesort
+2	DERIVED	t1	eq_ref	PRIMARY,id2	PRIMARY	4	test.t3.i3	1	
+2	DERIVED	t2	ALL	NULL	NULL	NULL	NULL	1	Using where; Using join buffer (flat, BNL join)
+SELECT id3
+FROM (SELECT t3.id3, t2.i2, t1.id2  FROM t3,t1,t2
+WHERE t3.i3=t1.id1 AND t2.id2=t1.id2
+GROUP BY t3.id3, t1.id2) AS t,
+t2
+WHERE t2.id2=t.id2;
+id3
+1
+DROP TABLE t1,t2,t3;
+#
+# Bug mdev-17381: equi-join of derived table with join_cache_level=4
+#
+CREATE TABLE t1 (
+id int NOT NULL,
+amount decimal DEFAULT NULL,
+PRIMARY KEY (id)
+) ENGINE=INNODB;
+CREATE TABLE t2 (
+id int NOT NULL,
+name varchar(50) DEFAULT NULL,
+PRIMARY KEY (id)
+) ENGINE=INNODB;
+INSERT INTO t1 VALUES
+(1, 10.0000), (2, 20.0000), (3, 30.0000), (4, 40.0000),
+(5, NULL), (6, NULL), (7, 70.0000), (8, 80.0000);
+INSERT INTO t2 VALUES
+(1,'A'), (2,'B'), (3,'C'), (4,'D'), (5, NULL), (6, NULL),
+(7,'E'), (8,'F'), (9,'G'), (10,'H'), (11, NULL), (12, NULL);
+set join_cache_level=4;
+SELECT t2.id,t2.name,t.total_amt
+FROM  t2
+LEFT JOIN
+(SELECT id, sum(amount) total_amt FROM t1 GROUP BY id) AS t
+ON t2.id=t.id
+WHERE t2.id < 3;
+id	name	total_amt
+1	A	10
+2	B	20
+EXPLAIN SELECT t2.id,t2.name,t.total_amt
+FROM  t2
+LEFT JOIN
+(SELECT id, sum(amount) total_amt FROM t1 GROUP BY id) AS t
+ON t2.id=t.id
+WHERE t2.id < 3;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t2	range	PRIMARY	PRIMARY	4	NULL	2	Using where
+1	PRIMARY	<derived2>	ref	key0	key0	5	test.t2.id	2	
+2	LATERAL DERIVED	t1	eq_ref	PRIMARY	PRIMARY	4	test.t2.id	1	
+set join_cache_level=default;
+DROP TABLE t1,t2;
+#
+# Bug mdev-18467: join of grouping view and a base table as inner operand
+#                 of left join with on condition containing impossible range
+#
+create table t1 (f1 int, f2 int, key(f2)) engine=InnoDB;
+insert into t1 values (3,33), (7,77), (1,11);
+create table t2 (f1 int, f2 int, primary key (f1)) engine=InnoDB;
+insert into t2 values (3,33), (9,99), (1,11);
+create view v1 as
+select f1, max(f2) as f2 from t2 group by f1;
+select t.f2
+from t1
+left join
+(v1 join t1 as t on v1.f1=t.f1 and t.f2 = null)
+on t1.f1=t.f1;
+f2
+NULL
+NULL
+NULL
+explain select t.f2
+from t1
+left join
+(v1 join t1 as t on v1.f1=t.f1 and t.f2 = null)
+on t1.f1=t.f1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t	const	f2	NULL	NULL	NULL	1	Impossible ON condition
+1	PRIMARY	<derived2>	const	key1	NULL	NULL	NULL	1	Impossible ON condition
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	3	
+2	DERIVED	t2	ALL	PRIMARY	NULL	NULL	NULL	3	Using temporary; Using filesort
+set statement optimizer_switch='split_materialized=off' for explain select t.f2
+from t1
+left join
+(v1 join t1 as t on v1.f1=t.f1 and t.f2 = null)
+on t1.f1=t.f1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t	const	f2	NULL	NULL	NULL	1	Impossible ON condition
+1	PRIMARY	<derived3>	const	key1	NULL	NULL	NULL	1	Impossible ON condition
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	3	
+3	DERIVED	t2	index	NULL	PRIMARY	4	NULL	3	
+drop view v1;
+drop table t1,t2;
+#
+#  MDEV-23723: Crash when test_if_skip_sort_order() is checked for derived table subject to split
+#
+CREATE TABLE t1 (a INT, b INT, KEY (a), KEY (a,b)) ENGINE=InnoDB;
+CREATE TABLE t2 (c INT, KEY (c)) ENGINE=InnoDB;
+SELECT * FROM t1 t1a JOIN t1 t1b;
+a	b	a	b
+INSERT INTO t2 VALUES (1),(2);
+INSERT INTO t1 VALUES (1,2),(3,4),(5,6),(7,8),(9,10),(11,12);
+set statement optimizer_switch='split_materialized=off' for EXPLAIN
+SELECT *
+FROM
+t1 JOIN
+(SELECT t1.a, t1.b FROM t1, t2 WHERE t1.b = t2.c GROUP BY t1.a, t1.b) as dt
+WHERE
+t1.a = dt.a;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	index	a,a_2	a_2	10	NULL	6	Using where; Using index
+1	PRIMARY	<derived3>	ref	key0	key0	5	test.t1.a	2	
+3	DERIVED	t1	index	NULL	a_2	10	NULL	6	Using where; Using index
+3	DERIVED	t2	ref	c	c	5	test.t1.b	1	Using index
+set statement optimizer_switch='split_materialized=on' for  EXPLAIN
+SELECT *
+FROM
+t1 JOIN
+(SELECT t1.a, t1.b FROM t1, t2 WHERE t1.b = t2.c GROUP BY t1.a, t1.b) as dt
+WHERE
+t1.a = dt.a;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	index	a,a_2	a_2	10	NULL	6	Using where; Using index
+1	PRIMARY	<derived3>	ref	key0	key0	5	test.t1.a	2	
+3	LATERAL DERIVED	t1	ref	a,a_2	a	5	test.t1.a	1	Using where; Using temporary; Using filesort
+3	LATERAL DERIVED	t2	ref	c	c	5	test.t1.b	1	Using index
+DROP TABLE t1, t2;
+#
+# Bug mdev-25714: usage non-splitting covering index is cheaper than
+#                 usage of the best splitting index for one group
+#
+create table t1 (
+id int not null, itemid int not null, index idx (itemid)
+) engine=innodb;
+insert into t1 values (1, 2), (2,2), (4,2), (4,2), (0,3), (3,3);
+create table t2 (id int not null) engine=innodb;
+insert into t2 values (2);
+create table t3 (
+id int not null, itemid int not null, userid int not null, primary key (id),
+index idx1 (userid, itemid), index idx2 (itemid)
+) engine innodb;
+insert into t3 values (1,1,1), (2,1,1), (3,2,1), (4,2,1), (5,3,1);
+set use_stat_tables='never';
+set optimizer_use_condition_selectivity=1;
+analyze table t1,t2,t3;
+Table	Op	Msg_type	Msg_text
+test.t1	analyze	status	OK
+test.t2	analyze	status	OK
+test.t3	analyze	status	OK
+set optimizer_switch='split_materialized=on';
+explain select t1.id, t1.itemid, dt.id, t2.id
+from t1,
+(select itemid, max(id) as id from t3 where userid = 1 group by itemid) dt,
+t2
+where t1.id = dt.id and t1.itemid = dt.itemid and t2.id=t1.itemid;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	1	
+1	PRIMARY	<derived2>	ref	key1	key1	4	test.t2.id	2	
+1	PRIMARY	t1	ALL	idx	NULL	NULL	NULL	6	Using where; Using join buffer (flat, BNL join)
+2	DERIVED	t3	ref	idx1,idx2	idx1	4	const	5	Using where; Using index
+select t1.id, t1.itemid, dt.id, t2.id
+from t1,
+(select itemid, max(id) as id from t3 where userid = 1 group by itemid) dt,
+t2
+where t1.id = dt.id and t1.itemid = dt.itemid and t2.id=t1.itemid;
+id	itemid	id	id
+4	2	4	2
+4	2	4	2
+set optimizer_switch='split_materialized=off';
+explain select t1.id, t1.itemid, dt.id, t2.id
+from t1,
+(select itemid, max(id) as id from t3 where userid = 1 group by itemid) dt,
+t2
+where t1.id = dt.id and t1.itemid = dt.itemid and t2.id=t1.itemid;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	1	
+1	PRIMARY	<derived2>	ref	key1	key1	4	test.t2.id	2	
+1	PRIMARY	t1	ALL	idx	NULL	NULL	NULL	6	Using where; Using join buffer (flat, BNL join)
+2	DERIVED	t3	ref	idx1	idx1	4	const	5	Using where; Using index
+select t1.id, t1.itemid, dt.id, t2.id
+from t1,
+(select itemid, max(id) as id from t3 where userid = 1 group by itemid) dt,
+t2
+where t1.id = dt.id and t1.itemid = dt.itemid and t2.id=t1.itemid;
+id	itemid	id	id
+4	2	4	2
+4	2	4	2
+drop table t1,t2,t3;
+set optimizer_switch='split_materialized=default';
+set use_stat_tables=default;
+set optimizer_use_condition_selectivity=default;
+#
+# MDEV-26337: subquery with groupby and ROLLUP returns incorrect results 
+#  (The testcase is taken from testcase for MDEV-13389 due to it being
+#   much smaller)
+#
+create table t3 (a int, b int, c char(127), index idx_b(b)) engine=myisam;
+insert into t3 values
+(8,11,'aa'), (5,15,'cc'), (1,14,'bb'), (2,12,'aa'), (7,17,'cc'),
+(7,18,'aa'), (2,11,'aa'), (7,10,'bb'), (3,11,'dd'), (4,12,'ee'),
+(5,14,'dd'), (9,12,'ee');
+create table t4 (a int, b int, c char(127), index idx(a,c)) engine=myisam;
+insert into t4 values
+(7,10,'cc'), (1,20,'aa'), (2,23,'bb'), (7,18,'cc'), (1,30,'bb'),
+(4,71,'xx'), (3,15,'aa'), (7,82,'aa'), (8,12,'dd'), (4,15,'aa'),
+(11,33,'yy'), (10,42,'zz'), (4,53,'xx'), (10,17,'yy'), (7,12,'cc'),
+(8,20,'dd'), (7,32,'bb'), (1,50,'aa'), (3,40,'bb'), (3,77,'aa');
+insert into t4 select a+10, b+10, concat(c,'f') from t4;
+analyze table t3,t4;
+Table	Op	Msg_type	Msg_text
+test.t3	analyze	status	Engine-independent statistics collected
+test.t3	analyze	status	OK
+test.t4	analyze	status	Engine-independent statistics collected
+test.t4	analyze	status	OK
+# This should use a plan with LATERAL DERIVED:
+explain select t3.a,t3.c,t.max,t.min
+from t3 join
+(select a, c, max(b) max, min(b) min from t4 group by a,c) t
+on t3.a=t.a and t3.c=t.c
+where t3.b > 15;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t3	range	idx_b	idx_b	5	NULL	2	Using index condition; Using where
+1	PRIMARY	<derived2>	ref	key0	key0	133	test.t3.a,test.t3.c	2	
+2	LATERAL DERIVED	t4	ref	idx	idx	133	test.t3.a,test.t3.c	1	
+# ... and if one adds WITH ROLLUP, then LATERAL DERIVED is no longer used:
+explain select t3.a,t3.c,t.max,t.min
+from t3 join
+(select a, c, max(b) max, min(b) min from t4 group by a,c with rollup) t
+on t3.a=t.a and t3.c=t.c
+where t3.b > 15;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t3	range	idx_b	idx_b	5	NULL	2	Using index condition; Using where
+1	PRIMARY	<derived2>	ref	key0	key0	133	test.t3.a,test.t3.c	4	
+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_optimization": {
+    "r_total_time_ms": "REPLACED"
+  },
+  "query_block": {
+    "select_id": 1,
+    "r_loops": 1,
+    "r_total_time_ms": "REPLACED",
+    "const_condition": "1",
+    "nested_loop": [
+      {
+        "table": {
+          "table_name": "t1",
+          "access_type": "ALL",
+          "r_loops": 1,
+          "rows": 5,
+          "r_rows": 5,
+          "r_table_time_ms": "REPLACED",
+          "r_other_time_ms": "REPLACED",
+          "r_engine_stats": 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_table_time_ms": "REPLACED",
+          "r_other_time_ms": "REPLACED",
+          "r_engine_stats": 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_table_time_ms": "REPLACED",
+          "r_other_time_ms": "REPLACED",
+          "r_engine_stats": 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_table_time_ms": "REPLACED",
+          "r_other_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",
+              "nested_loop": [
+                {
+                  "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_table_time_ms": "REPLACED",
+                    "r_other_time_ms": "REPLACED",
+                    "r_engine_stats": 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_table_time_ms": "REPLACED",
+                      "r_other_time_ms": "REPLACED",
+                      "r_engine_stats": REPLACED,
+                      "filtered": 100,
+                      "r_filtered": 100
+                    },
+                    "buffer_type": "flat",
+                    "buffer_size": "1Kb",
+                    "join_type": "BNL",
+                    "attached_condition": "trigcond(t11.col1 = t10.col1)",
+                    "r_loops": 500,
+                    "r_filtered": 10,
+                    "r_unpack_time_ms": "REPLACED",
+                    "r_other_time_ms": "REPLACED",
+                    "r_effective_rows": 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;
+#
+# MDEV-31403: Server crashes in st_join_table::choose_best_splitting (still)
+#
+CREATE TABLE t1 (a INT) ENGINE=InnoDB;
+INSERT INTO t1 VALUES
+(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),(13),(14),(15);
+CREATE TABLE t2 (b INT) ENGINE=InnoDB;
+INSERT INTO t2 VALUES (100),(200);
+CREATE TABLE t3 (c INT, d INT, KEY(c)) ENGINE=InnoDB;
+INSERT INTO t3 VALUES (1,1),(2,2);
+CREATE VIEW v AS SELECT c, d FROM t3 GROUP BY c, d;
+SELECT * FROM t1 JOIN t2 WHERE (t1.a, t2.b) IN (SELECT * FROM v);
+a	b
+DROP VIEW v;
+DROP TABLE t1, t2, t3;
+# End of 10.4 tests
+SET GLOBAL innodb_stats_persistent=@save_innodb_stats_persistent;