Bug #20987670 : sporadic test failure in subquery_mat_all subquery_mat and subquery_mat_none.

mithuncy · mithuncy · commit 3e49b6e66df8 · 2015-10-29T11:13:33.000+05:30
ISSUE:
------
After ALTER TABLE test case has called EXPLAIN query. But
since ANALYZE was not called and stats were not updated,
EXPLAINs results started varying for different runs.
Hence sporadic failures.

FIX:
----
Add ANALYZE TABLE before EXPLAIN query.
diff --git a/mysql-test/include/subquery_mat.inc b/mysql-test/include/subquery_mat.inc
@@ -1457,6 +1457,8 @@ eval $query;
 ALTER TABLE t1 MODIFY a VARCHAR(332) CHARACTER SET UTF8;
 --enable_warnings
 
+ANALYZE TABLE t1;
+
 # Now subquery materialization can be used, and result is still correct:
 eval EXPLAIN $query;
 eval $query;
diff --git a/mysql-test/r/subquery_mat.result b/mysql-test/r/subquery_mat.result
@@ -1899,14 +1899,17 @@ SELECT t2.a FROM t1 as t2
 COUNT(*)
 0
 ALTER TABLE t1 MODIFY a VARCHAR(332) CHARACTER SET UTF8;
+ANALYZE TABLE t1;
+Table	Op	Msg_type	Msg_text
+test.t1	analyze	status	OK
 EXPLAIN SELECT COUNT(*)
 FROM t1
 WHERE t1.a NOT IN (
 SELECT t2.a FROM t1 as t2
 );
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	1	Using where
-2	SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	1	NULL
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	32	Using where
+2	SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	32	NULL
 SELECT COUNT(*)
 FROM t1
 WHERE t1.a NOT IN (
diff --git a/mysql-test/r/subquery_mat_all.result b/mysql-test/r/subquery_mat_all.result
@@ -1912,14 +1912,17 @@ SELECT t2.a FROM t1 as t2
 COUNT(*)
 0
 ALTER TABLE t1 MODIFY a VARCHAR(332) CHARACTER SET UTF8;
+ANALYZE TABLE t1;
+Table	Op	Msg_type	Msg_text
+test.t1	analyze	status	OK
 EXPLAIN SELECT COUNT(*)
 FROM t1
 WHERE t1.a NOT IN (
 SELECT t2.a FROM t1 as t2
 );
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	1	Using where
-2	SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	1	NULL
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	32	Using where
+2	SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	32	NULL
 SELECT COUNT(*)
 FROM t1
 WHERE t1.a NOT IN (
diff --git a/mysql-test/r/subquery_mat_none.result b/mysql-test/r/subquery_mat_none.result
@@ -1898,14 +1898,17 @@ SELECT t2.a FROM t1 as t2
 COUNT(*)
 0
 ALTER TABLE t1 MODIFY a VARCHAR(332) CHARACTER SET UTF8;
+ANALYZE TABLE t1;
+Table	Op	Msg_type	Msg_text
+test.t1	analyze	status	OK
 EXPLAIN SELECT COUNT(*)
 FROM t1
 WHERE t1.a NOT IN (
 SELECT t2.a FROM t1 as t2
 );
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	1	Using where
-2	DEPENDENT SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	1	Using where
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	32	Using where
+2	DEPENDENT SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	32	Using where
 SELECT COUNT(*)
 FROM t1
 WHERE t1.a NOT IN (
