Fixed bug #934348.

This bug is the result of an incomplete/inconsistent change introduced into
5.3 code when the cond_equal parameter were added to the function optimize_cond.
The change was made during a merge from 5.2 in October 2010.
The bug could affect only queries with HAVING.

1 files changed, 43 insertions, 0 deletions

diff --git a/mysql-test/r/subselect_sj_jcl6.result b/mysql-test/r/subselect_sj_jcl6.result
index 4ac15ea654f..769373429da 100644
--- a/mysql-test/r/subselect_sj_jcl6.result
+++ b/mysql-test/r/subselect_sj_jcl6.result
@@ -2833,6 +2833,49 @@ set optimizer_switch=@tmp_optimizer_switch;
 set join_cache_level=@tmp_join_cache_level;
 DROP TABLE t1,t2,t3;
 # End
+#
+# BUG#934348: GROUP BY with HAVING + semijoin materialization 
+#             + join_cache_level > 2
+#
+CREATE TABLE t1 (a varchar(1), INDEX idx_a(a));
+INSERT INTO t1 VALUES ('c'), ('v'), ('c');
+CREATE TABLE t2 (b varchar(1));
+INSERT INTO t2 VALUES ('v'), ('c');
+set @tmp_otimizer_switch= @@optimizer_switch;
+set @tmp_join_cache_level=@@join_cache_level;
+set optimizer_switch = 'materialization=on,semijoin=on,join_cache_hashed=on';
+set join_cache_level=0;
+EXPLAIN
+SELECT a FROM t1 t WHERE a IN (SELECT b FROM t1, t2 WHERE b = a)
+GROUP BY a HAVING a != 'z';
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	<subquery2>	ALL	distinct_key	NULL	NULL	NULL	2	Using temporary; Using filesort
+1	PRIMARY	t	ref	idx_a	idx_a	4	test.t2.b	2	Using index
+2	MATERIALIZED	t2	ALL	NULL	NULL	NULL	NULL	2	Using where
+2	MATERIALIZED	t1	ref	idx_a	idx_a	4	test.t2.b	2	Using index
+SELECT a FROM t1 t WHERE a IN (SELECT b FROM t1, t2 WHERE b = a)
+GROUP BY a HAVING a != 'z';
+a
+c
+v
+set join_cache_level=6;
+EXPLAIN
+SELECT a FROM t1 t WHERE a IN (SELECT b FROM t1, t2 WHERE b = a)
+GROUP BY a HAVING a != 'z';
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	<subquery2>	ALL	distinct_key	NULL	NULL	NULL	2	Using temporary; Using filesort
+1	PRIMARY	t	ref	idx_a	idx_a	4	test.t2.b	2	Using index
+2	MATERIALIZED	t2	ALL	NULL	NULL	NULL	NULL	2	Using where
+2	MATERIALIZED	t1	ref	idx_a	idx_a	4	test.t2.b	2	Using index
+SELECT a FROM t1 t WHERE a IN (SELECT b FROM t1, t2 WHERE b = a)
+GROUP BY a HAVING a != 'z';
+a
+c
+v
+set optimizer_switch=@tmp_optimizer_switch;
+set join_cache_level=@tmp_join_cache_level;
+DROP TABLE t1,t2;
+# End
 set join_cache_level=default;
 show variables like 'join_cache_level';
 Variable_name	Value