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.

3 files changed, 83 insertions, 3 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
diff --git a/mysql-test/t/subselect_sj_jcl6.test b/mysql-test/t/subselect_sj_jcl6.test
index 9fe60cdd3d9..e86ed017951 100644
--- a/mysql-test/t/subselect_sj_jcl6.test
+++ b/mysql-test/t/subselect_sj_jcl6.test
@@ -170,6 +170,44 @@ DROP TABLE t1,t2,t3;
 
 --echo # End
 
+--echo #
+--echo # BUG#934348: GROUP BY with HAVING + semijoin materialization 
+--echo #             + join_cache_level > 2
+--echo #
+
+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';
+SELECT a FROM t1 t WHERE a IN (SELECT b FROM t1, t2 WHERE b = a)
+  GROUP BY a HAVING a != 'z';
+
+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';
+SELECT a FROM t1 t WHERE a IN (SELECT b FROM t1, t2 WHERE b = a)
+  GROUP BY a HAVING a != 'z';
+
+set optimizer_switch=@tmp_optimizer_switch;
+set join_cache_level=@tmp_join_cache_level;
+
+DROP TABLE t1,t2;
+
+--echo # End
+
 set join_cache_level=default;
 show variables like 'join_cache_level';
 
diff --git a/sql/sql_select.cc b/sql/sql_select.cc
index d8c4ebddc29..eb4c7f7faad 100644
--- a/sql/sql_select.cc
+++ b/sql/sql_select.cc
@@ -12695,9 +12695,8 @@ optimize_cond(JOIN *join, COND *conds, List<TABLE_LIST> *join_list,
       multiple equality contains a constant.
     */ 
     DBUG_EXECUTE("where", print_where(conds, "original", QT_ORDINARY););
-    conds= build_equal_items(join->thd, conds, NULL, join_list,
-                             &join->cond_equal);
-    DBUG_EXECUTE("where",print_where(conds,"after equal_items", QT_ORDINARY););
+    conds= build_equal_items(join->thd, conds, NULL, join_list, cond_equal);
+     DBUG_EXECUTE("where",print_where(conds,"after equal_items", QT_ORDINARY););
 
     /* change field = field to field = const for each found field = const */
     propagate_cond_constants(thd, (I_List<COND_CMP> *) 0, conds, conds);