Merge commit '10.3' into 10.4

1 files changed, 161 insertions, 0 deletions

diff --git a/mysql-test/main/partition_mrr_innodb.result b/mysql-test/main/partition_mrr_innodb.result
index c188f7e9929..2efb10de694 100644
--- a/mysql-test/main/partition_mrr_innodb.result
+++ b/mysql-test/main/partition_mrr_innodb.result
@@ -39,6 +39,7 @@ A.a,
 B.a,
 123456
 from t1 A, t1 B;
+set @save_optimizer_switch=@@optimizer_switch;
 set optimizer_switch='mrr=on';
 explain 
 select * from t3 force index (key_col) where key_col < 3;
@@ -76,4 +77,164 @@ ID	part_id	key_col	col2
 27	7	2	123456
 28	8	2	123456
 29	9	2	123456
+set optimizer_switch=@save_optimizer_switch;
 drop table t1,t3;
+#
+# MDEV-21544: partitioned table is joined with BKA+MRR
+#
+set @save_join_cache_level=@@join_cache_level;
+set @save_optimizer_switch=@@optimizer_switch;
+create table t0 (
+tp int, a int, b int not null, c varchar(12), index idx (a,b)
+);
+insert into t0 values
+(1,3,30,'yyzy'), (1,3,30,'yxxyy'), (1,3,30,'yyxy'), (1,3,30,'xxyy'),
+(1,3,30,'yyxz'), (1,3,30,'yyzz'), (1,3,30,'xxyzy'), (1,3,30,'yyyy'),
+(1,3,30,'yzzy'), (1,93,30,'zzzy'),
+(2,3,40,'yxx'), (2,4,40,'xx'), (2,3,10,'zxz'),
+(2,3,40,'yyx'), (2,4,40,'xxx'), (2,3,10,'zyyz'),
+(2,3,40,'xzzzz'), (2,4,40,'yyyxx'), (2,3,10,'zyz'),
+(2,3,40,'xxx'), (2,4,40,'xx'), (2,3,10,'zzz'),
+(2,3,40,'yyxzx'), (2,4,40,'xyx'), (2,3,10,'xzz'),
+(2,3,40,'xxxzz'), (2,4,40,'xxz'), (2,3,10,'zzzy'),
+(2,3,40,'zzxxx'), (2,4,40,'zxx'), (2,3,10,'yzzz'),
+(2,3,40,'xyyxx'), (2,4,40,'xzzzx'), (2,3,10,'zzxxz'),
+(2,3,40,'yzxxx'), (2,4,40,'xxzy'), (2,3,10,'zzzyx'),
+(2,93,40,'xzx'), (2,94,40,'xz'), (2,93,10,'zyyyz'),
+(3,4,30,'yx'), (3,4,30,'yyxxx'), (3,4,30,'zzyy'), (3,4,30,'zxyy'),
+(3,4,30,'xxyy'), (3,4,30,'yyzx'),  (3,4,30,'zyyy'), (3,4,30,'yzy'),
+(3,4,30,'zzzyy'), (3,94,30,'yyz');
+create table t1 (
+tp int, a int, b int not null, c varchar(12), index idx (a,b)
+)
+partition by list (tp)
+( partition p1 values in (1),
+partition p2 values in (2),
+partition p3 values in (3));
+insert into t1 select * from t0;
+create table t2 (a int, index idx(a));
+insert into t2 values
+(1), (2), (3), (4), (5);
+insert into t2 select a+10 from t2;
+insert into t2 select a+20 from t2;
+analyze table t0,t1,t2;
+Table	Op	Msg_type	Msg_text
+test.t0	analyze	status	Engine-independent statistics collected
+test.t0	analyze	status	OK
+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	OK
+set join_cache_level=6;
+set optimizer_switch='mrr=on';
+explain extended select * from t0,t2 where t2.a in (3,4) and t0.a=t2.a and (t0.b / 10) = t2.a-1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t2	range	idx	idx	5	NULL	2	100.00	Using where; Using index
+1	SIMPLE	t0	ref	idx	idx	5	test.t2.a	12	100.00	Using index condition(BKA); Using join buffer (flat, BKA join); Rowid-ordered scan
+Warnings:
+Note	1003	select `test`.`t0`.`tp` AS `tp`,`test`.`t0`.`a` AS `a`,`test`.`t0`.`b` AS `b`,`test`.`t0`.`c` AS `c`,`test`.`t2`.`a` AS `a` from `test`.`t0` join `test`.`t2` where `test`.`t0`.`a` = `test`.`t2`.`a` and `test`.`t2`.`a` in (3,4) and `test`.`t0`.`b` / 10 = `test`.`t2`.`a` - 1
+select * from t0,t2 where t2.a in (3,4) and t0.a=t2.a and (t0.b / 10) = t2.a-1;
+tp	a	b	c	a
+3	4	30	yx	4
+3	4	30	yyxxx	4
+3	4	30	zzyy	4
+3	4	30	zxyy	4
+3	4	30	xxyy	4
+3	4	30	yyzx	4
+3	4	30	zyyy	4
+3	4	30	yzy	4
+3	4	30	zzzyy	4
+explain extended select * from t1,t2 where t2.a in (3,4) and t1.a=t2.a and (t1.b / 10) = t2.a-1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t2	range	idx	idx	5	NULL	2	100.00	Using where; Using index
+1	SIMPLE	t1	ref	idx	idx	5	test.t2.a	12	100.00	Using index condition(BKA); Using join buffer (flat, BKA join); Rowid-ordered scan
+Warnings:
+Note	1003	select `test`.`t1`.`tp` AS `tp`,`test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c`,`test`.`t2`.`a` AS `a` from `test`.`t1` join `test`.`t2` where `test`.`t1`.`a` = `test`.`t2`.`a` and `test`.`t2`.`a` in (3,4) and `test`.`t1`.`b` / 10 = `test`.`t2`.`a` - 1
+select * from t1,t2 where t2.a in (3,4) and t1.a=t2.a and (t1.b / 10) = t2.a-1;
+tp	a	b	c	a
+3	4	30	yx	4
+3	4	30	yyxxx	4
+3	4	30	zzyy	4
+3	4	30	zxyy	4
+3	4	30	xxyy	4
+3	4	30	yyzx	4
+3	4	30	zyyy	4
+3	4	30	yzy	4
+3	4	30	zzzyy	4
+explain extended select * from t0,t2 where t2.a in (3,4) and t0.a=t2.a and (t0.b / 10) = 4;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t2	range	idx	idx	5	NULL	2	100.00	Using where; Using index
+1	SIMPLE	t0	ref	idx	idx	5	test.t2.a	12	100.00	Using index condition; Using join buffer (flat, BKA join); Rowid-ordered scan
+Warnings:
+Note	1003	select `test`.`t0`.`tp` AS `tp`,`test`.`t0`.`a` AS `a`,`test`.`t0`.`b` AS `b`,`test`.`t0`.`c` AS `c`,`test`.`t2`.`a` AS `a` from `test`.`t0` join `test`.`t2` where `test`.`t0`.`a` = `test`.`t2`.`a` and `test`.`t2`.`a` in (3,4) and `test`.`t0`.`b` / 10 = 4
+select * from t0,t2 where t2.a in (3,4) and t0.a=t2.a and (t0.b / 10) = 4;
+tp	a	b	c	a
+2	3	40	yxx	3
+2	4	40	xx	4
+2	3	40	yyx	3
+2	4	40	xxx	4
+2	3	40	xzzzz	3
+2	4	40	yyyxx	4
+2	3	40	xxx	3
+2	4	40	xx	4
+2	3	40	yyxzx	3
+2	4	40	xyx	4
+2	3	40	xxxzz	3
+2	4	40	xxz	4
+2	3	40	zzxxx	3
+2	4	40	zxx	4
+2	3	40	xyyxx	3
+2	4	40	xzzzx	4
+2	3	40	yzxxx	3
+2	4	40	xxzy	4
+explain extended select * from t1,t2 where t2.a in (3,4) and t1.a=t2.a and (t1.b / 10) = 4;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t2	range	idx	idx	5	NULL	2	100.00	Using where; Using index
+1	SIMPLE	t1	ref	idx	idx	5	test.t2.a	12	100.00	Using index condition(BKA); Using join buffer (flat, BKA join); Rowid-ordered scan
+Warnings:
+Note	1003	select `test`.`t1`.`tp` AS `tp`,`test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c`,`test`.`t2`.`a` AS `a` from `test`.`t1` join `test`.`t2` where `test`.`t1`.`a` = `test`.`t2`.`a` and `test`.`t2`.`a` in (3,4) and `test`.`t1`.`b` / 10 = 4
+select * from t1,t2 where t2.a in (3,4) and t1.a=t2.a and (t1.b / 10) = 4;
+tp	a	b	c	a
+2	3	40	yxx	3
+2	4	40	xx	4
+2	3	40	yyx	3
+2	4	40	xxx	4
+2	3	40	xzzzz	3
+2	4	40	yyyxx	4
+2	3	40	xxx	3
+2	4	40	xx	4
+2	3	40	yyxzx	3
+2	4	40	xyx	4
+2	3	40	xxxzz	3
+2	4	40	xxz	4
+2	3	40	zzxxx	3
+2	4	40	zxx	4
+2	3	40	xyyxx	3
+2	4	40	xzzzx	4
+2	3	40	yzxxx	3
+2	4	40	xxzy	4
+insert into t2 values
+(3), (4), (5);
+analyze table t2;
+Table	Op	Msg_type	Msg_text
+test.t2	analyze	status	Engine-independent statistics collected
+test.t2	analyze	status	OK
+explain extended select * from t2 left join t0 on t2.a=t0.a where t2.a in (3,4) and t0.b is null;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t2	range	idx	idx	5	NULL	4	100.00	Using where; Using index
+1	SIMPLE	t0	ref	idx	idx	5	test.t2.a	12	100.00	Using where; Not exists; Using join buffer (flat, BKA join); Rowid-ordered scan
+Warnings:
+Note	1003	select `test`.`t2`.`a` AS `a`,`test`.`t0`.`tp` AS `tp`,`test`.`t0`.`a` AS `a`,`test`.`t0`.`b` AS `b`,`test`.`t0`.`c` AS `c` from `test`.`t2` left join `test`.`t0` on(`test`.`t0`.`a` = `test`.`t2`.`a` and `test`.`t2`.`a` is not null) where `test`.`t2`.`a` in (3,4) and `test`.`t0`.`b` is null
+select * from t2 left join t0 on t2.a=t0.a where t2.a in (3,4) and t0.b is null;
+a	tp	a	b	c
+explain extended select * from t2 left join t1 on t2.a=t1.a where t2.a in (3,4) and t1.b is null;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t2	range	idx	idx	5	NULL	4	100.00	Using where; Using index
+1	SIMPLE	t1	ref	idx	idx	5	test.t2.a	12	100.00	Using where; Not exists; Using join buffer (flat, BKA join); Rowid-ordered scan
+Warnings:
+Note	1003	select `test`.`t2`.`a` AS `a`,`test`.`t1`.`tp` AS `tp`,`test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c` from `test`.`t2` left join `test`.`t1` on(`test`.`t1`.`a` = `test`.`t2`.`a` and `test`.`t2`.`a` is not null) where `test`.`t2`.`a` in (3,4) and `test`.`t1`.`b` is null
+select * from t2 left join t1 on t2.a=t1.a where t2.a in (3,4) and t1.b is null;
+a	tp	a	b	c
+set join_cache_level=@save_join_cache_level;
+set optimizer_switch=@save_optimizer_switch;
+drop table t0,t1,t2;