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

diff --git a/mysql-test/main/partition_mrr_myisam.result b/mysql-test/main/partition_mrr_myisam.result
new file mode 100644
index 00000000..c3ce2935
--- /dev/null
+++ b/mysql-test/main/partition_mrr_myisam.result
@@ -0,0 +1,299 @@
+drop table if exists t1,t3;
+#
+# MDEV-20611: MRR scan over partitioned InnoDB table produces "Out of memory" error
+#
+create table t1(a int);
+insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
+set @tmp=@@default_storage_engine;
+set default_storage_engine=myisam;
+create table t3 (
+ID bigint(20) NOT NULL AUTO_INCREMENT,
+part_id int,
+key_col int,
+col2 int,
+key(key_col),
+PRIMARY KEY (ID,part_id)
+) PARTITION BY RANGE (part_id)
+(PARTITION p1 VALUES LESS THAN (3),
+PARTITION p2 VALUES LESS THAN (7),
+PARTITION p3 VALUES LESS THAN (10)
+);
+show create table t3;
+Table	Create Table
+t3	CREATE TABLE `t3` (
+  `ID` bigint(20) NOT NULL AUTO_INCREMENT,
+  `part_id` int(11) NOT NULL,
+  `key_col` int(11) DEFAULT NULL,
+  `col2` int(11) DEFAULT NULL,
+  PRIMARY KEY (`ID`,`part_id`),
+  KEY `key_col` (`key_col`)
+) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
+ PARTITION BY RANGE (`part_id`)
+(PARTITION `p1` VALUES LESS THAN (3) ENGINE = MyISAM,
+ PARTITION `p2` VALUES LESS THAN (7) ENGINE = MyISAM,
+ PARTITION `p3` VALUES LESS THAN (10) ENGINE = MyISAM)
+set default_storage_engine= @tmp;
+insert into t3 select 
+A.a+10*B.a,
+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;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t3	range	key_col	key_col	5	NULL	#	Using where; Rowid-ordered scan
+select * from t3 force index (key_col) where key_col < 3;
+ID	part_id	key_col	col2
+1	0	0	123456
+1	1	0	123456
+2	2	0	123456
+10	0	1	123456
+11	1	1	123456
+12	2	1	123456
+20	0	2	123456
+21	1	2	123456
+22	2	2	123456
+3	3	0	123456
+4	4	0	123456
+5	5	0	123456
+6	6	0	123456
+13	3	1	123456
+14	4	1	123456
+15	5	1	123456
+16	6	1	123456
+23	3	2	123456
+24	4	2	123456
+25	5	2	123456
+26	6	2	123456
+7	7	0	123456
+8	8	0	123456
+9	9	0	123456
+17	7	1	123456
+18	8	1	123456
+19	9	1	123456
+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;
+#
+# MDEV-21628: Index condition pushdown for a simple condition over 
+# index fields is not used for ref access of partitioned tables when employing BKA
+#
+create table t0 (
+tp int, a int, b int, 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, c varchar(12), index idx (a,b)
+) engine=myisam
+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)) engine=myisam;
+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 @tmp1=@@join_cache_level, @tmp2=@@optimizer_switch;
+set join_cache_level=6, optimizer_switch='mrr=on';
+explain
+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	Extra
+1	SIMPLE	t2	range	idx	idx	5	NULL	2	Using where; Using index
+1	SIMPLE	t0	ref	idx	idx	5	test.t2.a	12	Using index condition; Using join buffer (flat, BKA join); Rowid-ordered scan
+# This will use "Using index condition(BKA)"
+explain
+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	Extra
+1	SIMPLE	t2	range	idx	idx	5	NULL	2	Using where; Using index
+1	SIMPLE	t1	ref	idx	idx	5	test.t2.a	12	Using index condition(BKA); Using join buffer (flat, BKA join); Rowid-ordered scan
+set join_cache_level=@tmp1, optimizer_switch=@tmp2;
+drop table t0,t1,t2;