MDEV-20374: innodb.innodb_mysql fails sporadically in BB

Stabilize the test:
- replace Rows column in EXPLAIN output for one query
- Use EITS statistics for another query (in that testcase, the
  query must use LooseScan)

3 files changed, 30 insertions, 2 deletions

diff --git a/mysql-test/include/mix1.inc b/mysql-test/include/mix1.inc
index 6a47b39e5bc..30b17c2e063 100644
--- a/mysql-test/include/mix1.inc
+++ b/mysql-test/include/mix1.inc
@@ -455,6 +455,16 @@ INSERT INTO t1(id, dept, age, name) VALUES
   (4011, 'cs7', 10, 'rs4'), (4012, 'cs8', 20, 'rs4'), (4019, 'cs9', 10, 'rs5'),
   (4020, 'cs10', 20, 'rs5'),(4027, 'cs11', 10, 'rs6'),(4028, 'cs12', 20, 'rs6');
 
+set 
+  @tmp_uss=@@use_stat_tables,
+  @tmp_occ=@@optimizer_use_condition_selectivity;
+set 
+  use_stat_tables='preferably',
+  optimizer_use_condition_selectivity=4;
+
+analyze table t1 persistent for all;
+flush tables;
+
 EXPLAIN SELECT DISTINCT t1.name, t1.dept FROM t1 WHERE t1.name='rs5';
 SELECT DISTINCT t1.name, t1.dept FROM t1 WHERE t1.name='rs5';
 DELETE FROM t1;
@@ -464,6 +474,9 @@ EXPLAIN SELECT DISTINCT t1.name, t1.dept FROM t1 WHERE t1.name='rs5';
 SELECT DISTINCT t1.name, t1.dept FROM t1 WHERE t1.name='rs5';
 
 DROP TABLE t1;
+set 
+  use_stat_tables=@tmp_uss,
+  optimizer_use_condition_selectivity=@tmp_occ;
 
 --source include/innodb_rollback_on_timeout.inc
 
diff --git a/mysql-test/suite/innodb/r/innodb_mysql.result b/mysql-test/suite/innodb/r/innodb_mysql.result
index b78988e08a8..b83dba5345e 100644
--- a/mysql-test/suite/innodb/r/innodb_mysql.result
+++ b/mysql-test/suite/innodb/r/innodb_mysql.result
@@ -383,6 +383,17 @@ INSERT INTO t1(id, dept, age, name) VALUES
 (3996, 'cs4', 20, 'rs2'), (4003, 'cs5', 10, 'rs3'), (4004, 'cs6', 20, 'rs3'),
 (4011, 'cs7', 10, 'rs4'), (4012, 'cs8', 20, 'rs4'), (4019, 'cs9', 10, 'rs5'),
 (4020, 'cs10', 20, 'rs5'),(4027, 'cs11', 10, 'rs6'),(4028, 'cs12', 20, 'rs6');
+set 
+@tmp_uss=@@use_stat_tables,
+@tmp_occ=@@optimizer_use_condition_selectivity;
+set 
+use_stat_tables='preferably',
+optimizer_use_condition_selectivity=4;
+analyze table t1 persistent for all;
+Table	Op	Msg_type	Msg_text
+test.t1	analyze	status	Engine-independent statistics collected
+test.t1	analyze	status	OK
+flush tables;
 EXPLAIN SELECT DISTINCT t1.name, t1.dept FROM t1 WHERE t1.name='rs5';
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	ref	name	name	22	const	2	Using where; Using index
@@ -398,6 +409,9 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 SELECT DISTINCT t1.name, t1.dept FROM t1 WHERE t1.name='rs5';
 name	dept
 DROP TABLE t1;
+set 
+use_stat_tables=@tmp_uss,
+optimizer_use_condition_selectivity=@tmp_occ;
 drop table if exists t1;
 show variables like 'innodb_rollback_on_timeout';
 Variable_name	Value
@@ -3166,8 +3180,8 @@ SELECT COUNT(*) FROM
 (SELECT * FROM t1 FORCE INDEX (idx,PRIMARY)
 WHERE a BETWEEN 2 AND 7 OR pk=1000000) AS t;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	<derived2>	ALL	NULL	NULL	NULL	NULL	1537	
-2	DERIVED	t1	index_merge	PRIMARY,idx	idx,PRIMARY	5,4	NULL	1537	Using sort_union(idx,PRIMARY); Using where
+1	PRIMARY	<derived2>	ALL	NULL	NULL	NULL	NULL	#	
+2	DERIVED	t1	index_merge	PRIMARY,idx	idx,PRIMARY	5,4	NULL	#	Using sort_union(idx,PRIMARY); Using where
 SELECT COUNT(*) FROM
 (SELECT * FROM t1 FORCE INDEX (idx,PRIMARY)
 WHERE a BETWEEN 2 AND 7 OR pk=1000000) AS t;
diff --git a/mysql-test/suite/innodb/t/innodb_mysql.test b/mysql-test/suite/innodb/t/innodb_mysql.test
index 23bba81c6bd..d495186db25 100644
--- a/mysql-test/suite/innodb/t/innodb_mysql.test
+++ b/mysql-test/suite/innodb/t/innodb_mysql.test
@@ -850,6 +850,7 @@ set @optimizer_switch_saved=@@optimizer_switch;
 SET SESSION optimizer_switch='derived_merge=off';
 SET SESSION sort_buffer_size = 1024*36;
 
+--replace_column 9 #
 EXPLAIN
 SELECT COUNT(*) FROM
   (SELECT * FROM t1 FORCE INDEX (idx,PRIMARY)