Make main.subselect_sj2* tests stable

Use EITS statistics to avoid changing query plans

4 files changed, 75 insertions, 24 deletions

diff --git a/mysql-test/main/subselect_sj2.result b/mysql-test/main/subselect_sj2.result
index 31453801220..bab21da8243 100644
--- a/mysql-test/main/subselect_sj2.result
+++ b/mysql-test/main/subselect_sj2.result
@@ -813,6 +813,14 @@ CREATE TABLE t2 (f14 int(11) NOT NULL, f12 varchar(1) NOT NULL, KEY (f12,f14)) E
 INSERT INTO t2 VALUES (6,'y');
 CREATE TABLE t3 (f12 varchar(1) NOT NULL) ENGINE=InnoDB;
 INSERT INTO t3 VALUES ('r'),('s'),('t'),('v'),('w'),('x'),('y');
+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	OK
+test.t3	analyze	status	Engine-independent statistics collected
+test.t3	analyze	status	OK
 # The following must use LooseScan but not join buffering
 explain
 SELECT * FROM t3 
@@ -1102,6 +1110,14 @@ INSERT INTO t2 VALUES
 (9,'d','d'),(10,'s','s'),(11,'r','r'),(12,'m','m'),
 (13,'b','b'),(14,'x','x'),(15,'g','g'),(16,'p','p'),
 (17,'q','q'),(18,'w','w'),(19,'d','d');
+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
+analyze table t2 persistent for all;
+Table	Op	Msg_type	Msg_text
+test.t2	analyze	status	Engine-independent statistics collected
+test.t2	analyze	status	OK
 EXPLAIN
 SELECT COUNT(*) FROM t1 AS alias1, t1 AS alias2, t2 AS alias3
 WHERE alias3.d IN (
@@ -1110,11 +1126,11 @@ WHERE alias5.b = alias4.b
 AND ( alias5.b >= alias3.b OR alias5.c != alias3.c )
 );
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	alias3	ALL	PRIMARY	NULL	NULL	NULL	#	
-1	PRIMARY	alias5	index	PRIMARY	c	4	NULL	#	Using where; Using index
-1	PRIMARY	alias4	eq_ref	PRIMARY,c	PRIMARY	4	test.alias5.b	#	Using where; FirstMatch(alias3)
-1	PRIMARY	alias1	ALL	NULL	NULL	NULL	NULL	#	Using join buffer (flat, BNL join)
+1	PRIMARY	alias3	ALL	PRIMARY	NULL	NULL	NULL	#	Using where
+1	PRIMARY	alias4	ref	PRIMARY,c	c	4	test.alias3.d	#	Using index
+1	PRIMARY	alias5	eq_ref	PRIMARY	PRIMARY	4	test.alias4.b	#	Using where; FirstMatch(alias3)
 1	PRIMARY	alias2	ALL	NULL	NULL	NULL	NULL	#	Using join buffer (flat, BNL join)
+1	PRIMARY	alias1	ALL	NULL	NULL	NULL	NULL	#	Using join buffer (flat, BNL join)
 SELECT COUNT(*) FROM t1 AS alias1, t1 AS alias2, t2 AS alias3
 WHERE alias3.d IN (
 SELECT alias4.c FROM t2 AS alias4, t2 AS alias5
@@ -1131,11 +1147,11 @@ WHERE alias5.b = alias4.b
 AND ( alias5.b >= alias3.b OR alias3.c != alias5.c )
 );
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	alias3	ALL	PRIMARY	NULL	NULL	NULL	#	
-1	PRIMARY	alias5	index	PRIMARY	c	4	NULL	#	Using where; Using index
-1	PRIMARY	alias4	eq_ref	PRIMARY,c	PRIMARY	4	test.alias5.b	#	Using where; FirstMatch(alias3)
-1	PRIMARY	alias1	ALL	NULL	NULL	NULL	NULL	#	Using join buffer (flat, BNL join)
+1	PRIMARY	alias3	ALL	PRIMARY	NULL	NULL	NULL	#	Using where
+1	PRIMARY	alias4	ref	PRIMARY,c	c	4	test.alias3.d	#	Using index
+1	PRIMARY	alias5	eq_ref	PRIMARY	PRIMARY	4	test.alias4.b	#	Using where; FirstMatch(alias3)
 1	PRIMARY	alias2	ALL	NULL	NULL	NULL	NULL	#	Using join buffer (flat, BNL join)
+1	PRIMARY	alias1	ALL	NULL	NULL	NULL	NULL	#	Using join buffer (flat, BNL join)
 SELECT COUNT(*) FROM t1 AS alias1, t1 AS alias2, t2 AS alias3
 WHERE alias3.d IN (
 SELECT alias4.c FROM t2 AS alias4, t2 AS alias5
diff --git a/mysql-test/main/subselect_sj2.test b/mysql-test/main/subselect_sj2.test
index 2b4f619a615..8886c42eb55 100644
--- a/mysql-test/main/subselect_sj2.test
+++ b/mysql-test/main/subselect_sj2.test
@@ -994,6 +994,7 @@ INSERT INTO t2 VALUES (6,'y');
 CREATE TABLE t3 (f12 varchar(1) NOT NULL) ENGINE=InnoDB;
 INSERT INTO t3 VALUES ('r'),('s'),('t'),('v'),('w'),('x'),('y');
 
+analyze table t1,t2,t3 persistent for all;
 --echo # The following must use LooseScan but not join buffering
 
 --replace_column 9 #
@@ -1225,6 +1226,8 @@ INSERT INTO t2 VALUES
   (13,'b','b'),(14,'x','x'),(15,'g','g'),(16,'p','p'),
   (17,'q','q'),(18,'w','w'),(19,'d','d');
 
+analyze table t1 persistent for all;
+analyze table t2 persistent for all;
 --replace_column 9 #
 EXPLAIN
 SELECT COUNT(*) FROM t1 AS alias1, t1 AS alias2, t2 AS alias3
diff --git a/mysql-test/main/subselect_sj2_jcl6.result b/mysql-test/main/subselect_sj2_jcl6.result
index 3321ba221f3..ac2d12fc5df 100644
--- a/mysql-test/main/subselect_sj2_jcl6.result
+++ b/mysql-test/main/subselect_sj2_jcl6.result
@@ -826,6 +826,14 @@ CREATE TABLE t2 (f14 int(11) NOT NULL, f12 varchar(1) NOT NULL, KEY (f12,f14)) E
 INSERT INTO t2 VALUES (6,'y');
 CREATE TABLE t3 (f12 varchar(1) NOT NULL) ENGINE=InnoDB;
 INSERT INTO t3 VALUES ('r'),('s'),('t'),('v'),('w'),('x'),('y');
+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	OK
+test.t3	analyze	status	Engine-independent statistics collected
+test.t3	analyze	status	OK
 # The following must use LooseScan but not join buffering
 explain
 SELECT * FROM t3 
@@ -1115,6 +1123,14 @@ INSERT INTO t2 VALUES
 (9,'d','d'),(10,'s','s'),(11,'r','r'),(12,'m','m'),
 (13,'b','b'),(14,'x','x'),(15,'g','g'),(16,'p','p'),
 (17,'q','q'),(18,'w','w'),(19,'d','d');
+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
+analyze table t2 persistent for all;
+Table	Op	Msg_type	Msg_text
+test.t2	analyze	status	Engine-independent statistics collected
+test.t2	analyze	status	OK
 EXPLAIN
 SELECT COUNT(*) FROM t1 AS alias1, t1 AS alias2, t2 AS alias3
 WHERE alias3.d IN (
@@ -1123,11 +1139,11 @@ WHERE alias5.b = alias4.b
 AND ( alias5.b >= alias3.b OR alias5.c != alias3.c )
 );
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	alias3	ALL	PRIMARY	NULL	NULL	NULL	#	
-1	PRIMARY	alias5	index	PRIMARY	c	4	NULL	#	Using where; Using index; Using join buffer (flat, BNL join)
-1	PRIMARY	alias4	eq_ref	PRIMARY,c	PRIMARY	4	test.alias5.b	#	Using where; FirstMatch(alias3); Using join buffer (incremental, BKA join); Key-ordered scan
+1	PRIMARY	alias3	ALL	PRIMARY	NULL	NULL	NULL	#	Using where
+1	PRIMARY	alias4	ref	PRIMARY,c	c	4	test.alias3.d	#	Using index
+1	PRIMARY	alias5	eq_ref	PRIMARY	PRIMARY	4	test.alias4.b	#	Using where; FirstMatch(alias3)
+1	PRIMARY	alias2	ALL	NULL	NULL	NULL	NULL	#	Using join buffer (flat, BNL join)
 1	PRIMARY	alias1	ALL	NULL	NULL	NULL	NULL	#	Using join buffer (incremental, BNL join)
-1	PRIMARY	alias2	ALL	NULL	NULL	NULL	NULL	#	Using join buffer (incremental, BNL join)
 SELECT COUNT(*) FROM t1 AS alias1, t1 AS alias2, t2 AS alias3
 WHERE alias3.d IN (
 SELECT alias4.c FROM t2 AS alias4, t2 AS alias5
@@ -1144,11 +1160,11 @@ WHERE alias5.b = alias4.b
 AND ( alias5.b >= alias3.b OR alias3.c != alias5.c )
 );
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	alias3	ALL	PRIMARY	NULL	NULL	NULL	#	
-1	PRIMARY	alias5	index	PRIMARY	c	4	NULL	#	Using where; Using index; Using join buffer (flat, BNL join)
-1	PRIMARY	alias4	eq_ref	PRIMARY,c	PRIMARY	4	test.alias5.b	#	Using where; FirstMatch(alias3); Using join buffer (incremental, BKA join); Key-ordered scan
+1	PRIMARY	alias3	ALL	PRIMARY	NULL	NULL	NULL	#	Using where
+1	PRIMARY	alias4	ref	PRIMARY,c	c	4	test.alias3.d	#	Using index
+1	PRIMARY	alias5	eq_ref	PRIMARY	PRIMARY	4	test.alias4.b	#	Using where; FirstMatch(alias3)
+1	PRIMARY	alias2	ALL	NULL	NULL	NULL	NULL	#	Using join buffer (flat, BNL join)
 1	PRIMARY	alias1	ALL	NULL	NULL	NULL	NULL	#	Using join buffer (incremental, BNL join)
-1	PRIMARY	alias2	ALL	NULL	NULL	NULL	NULL	#	Using join buffer (incremental, BNL join)
 SELECT COUNT(*) FROM t1 AS alias1, t1 AS alias2, t2 AS alias3
 WHERE alias3.d IN (
 SELECT alias4.c FROM t2 AS alias4, t2 AS alias5
diff --git a/mysql-test/main/subselect_sj2_mat.result b/mysql-test/main/subselect_sj2_mat.result
index 7245e74b242..e55025f4fa8 100644
--- a/mysql-test/main/subselect_sj2_mat.result
+++ b/mysql-test/main/subselect_sj2_mat.result
@@ -815,6 +815,14 @@ CREATE TABLE t2 (f14 int(11) NOT NULL, f12 varchar(1) NOT NULL, KEY (f12,f14)) E
 INSERT INTO t2 VALUES (6,'y');
 CREATE TABLE t3 (f12 varchar(1) NOT NULL) ENGINE=InnoDB;
 INSERT INTO t3 VALUES ('r'),('s'),('t'),('v'),('w'),('x'),('y');
+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	OK
+test.t3	analyze	status	Engine-independent statistics collected
+test.t3	analyze	status	OK
 # The following must use LooseScan but not join buffering
 explain
 SELECT * FROM t3 
@@ -1104,6 +1112,14 @@ INSERT INTO t2 VALUES
 (9,'d','d'),(10,'s','s'),(11,'r','r'),(12,'m','m'),
 (13,'b','b'),(14,'x','x'),(15,'g','g'),(16,'p','p'),
 (17,'q','q'),(18,'w','w'),(19,'d','d');
+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
+analyze table t2 persistent for all;
+Table	Op	Msg_type	Msg_text
+test.t2	analyze	status	Engine-independent statistics collected
+test.t2	analyze	status	OK
 EXPLAIN
 SELECT COUNT(*) FROM t1 AS alias1, t1 AS alias2, t2 AS alias3
 WHERE alias3.d IN (
@@ -1112,11 +1128,11 @@ WHERE alias5.b = alias4.b
 AND ( alias5.b >= alias3.b OR alias5.c != alias3.c )
 );
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	alias3	ALL	PRIMARY	NULL	NULL	NULL	#	
-1	PRIMARY	alias5	index	PRIMARY	c	4	NULL	#	Using where; Using index
-1	PRIMARY	alias4	eq_ref	PRIMARY,c	PRIMARY	4	test.alias5.b	#	Using where; FirstMatch(alias3)
-1	PRIMARY	alias1	ALL	NULL	NULL	NULL	NULL	#	Using join buffer (flat, BNL join)
+1	PRIMARY	alias3	ALL	PRIMARY	NULL	NULL	NULL	#	Using where
+1	PRIMARY	alias4	ref	PRIMARY,c	c	4	test.alias3.d	#	Using index
+1	PRIMARY	alias5	eq_ref	PRIMARY	PRIMARY	4	test.alias4.b	#	Using where; FirstMatch(alias3)
 1	PRIMARY	alias2	ALL	NULL	NULL	NULL	NULL	#	Using join buffer (flat, BNL join)
+1	PRIMARY	alias1	ALL	NULL	NULL	NULL	NULL	#	Using join buffer (flat, BNL join)
 SELECT COUNT(*) FROM t1 AS alias1, t1 AS alias2, t2 AS alias3
 WHERE alias3.d IN (
 SELECT alias4.c FROM t2 AS alias4, t2 AS alias5
@@ -1133,11 +1149,11 @@ WHERE alias5.b = alias4.b
 AND ( alias5.b >= alias3.b OR alias3.c != alias5.c )
 );
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	alias3	ALL	PRIMARY	NULL	NULL	NULL	#	
-1	PRIMARY	alias5	index	PRIMARY	c	4	NULL	#	Using where; Using index
-1	PRIMARY	alias4	eq_ref	PRIMARY,c	PRIMARY	4	test.alias5.b	#	Using where; FirstMatch(alias3)
-1	PRIMARY	alias1	ALL	NULL	NULL	NULL	NULL	#	Using join buffer (flat, BNL join)
+1	PRIMARY	alias3	ALL	PRIMARY	NULL	NULL	NULL	#	Using where
+1	PRIMARY	alias4	ref	PRIMARY,c	c	4	test.alias3.d	#	Using index
+1	PRIMARY	alias5	eq_ref	PRIMARY	PRIMARY	4	test.alias4.b	#	Using where; FirstMatch(alias3)
 1	PRIMARY	alias2	ALL	NULL	NULL	NULL	NULL	#	Using join buffer (flat, BNL join)
+1	PRIMARY	alias1	ALL	NULL	NULL	NULL	NULL	#	Using join buffer (flat, BNL join)
 SELECT COUNT(*) FROM t1 AS alias1, t1 AS alias2, t2 AS alias3
 WHERE alias3.d IN (
 SELECT alias4.c FROM t2 AS alias4, t2 AS alias5