Merge gkodinov@bk-internal.mysql.com:/home/bk/mysql-4.1-opt

into  rakia.(none):/home/kgeorge/mysql/autopush/B20792-4.1-opt


sql/sql_select.cc:
  Auto merged

3 files changed, 38 insertions, 2 deletions

diff --git a/mysql-test/r/subselect2.result b/mysql-test/r/subselect2.result
index 8fcfa06a8ae..db1848105f8 100644
--- a/mysql-test/r/subselect2.result
+++ b/mysql-test/r/subselect2.result
@@ -130,3 +130,15 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 5	DEPENDENT SUBQUERY	t3	unique_subquery	PRIMARY,FFOLDERID_IDX	PRIMARY	32	func	1	Using index; Using where
 6	DEPENDENT SUBQUERY	t3	unique_subquery	PRIMARY,FFOLDERID_IDX,CMFLDRPARNT_IDX	PRIMARY	32	func	1	Using index; Using where
 drop table t1, t2, t3, t4;
+CREATE TABLE t1 (a int(10) , PRIMARY KEY (a)) Engine=InnoDB;
+INSERT INTO t1 VALUES (1),(2);
+CREATE TABLE t2 (a int(10), PRIMARY KEY (a)) Engine=InnoDB;
+INSERT INTO t2 VALUES (1);
+CREATE TABLE t3 (a int(10), b int(10), c int(10),
+PRIMARY KEY (a)) Engine=InnoDB;
+INSERT INTO t3 VALUES (1,2,1);
+SELECT t1.* FROM t1 WHERE (SELECT COUNT(*) FROM t3,t2 WHERE t3.c=t2.a 
+and t2.a='1' AND t1.a=t3.b) > 0;
+a
+2
+DROP TABLE t1,t2,t3;
diff --git a/mysql-test/t/subselect2.test b/mysql-test/t/subselect2.test
index b21eda176b6..162bdd0d90a 100644
--- a/mysql-test/t/subselect2.test
+++ b/mysql-test/t/subselect2.test
@@ -150,3 +150,21 @@ EXPLAIN SELECT t2.*, t4.DOCTYPENAME, t1.CONTENTSIZE,t1.MIMETYPE FROM t2 INNER JO
 
 drop table t1, t2, t3, t4;
 # End of 4.1 tests
+
+#
+# Bug #20792: Incorrect results from aggregate subquery
+#
+CREATE TABLE t1 (a int(10) , PRIMARY KEY (a)) Engine=InnoDB;
+INSERT INTO t1 VALUES (1),(2);
+
+CREATE TABLE t2 (a int(10), PRIMARY KEY (a)) Engine=InnoDB;
+INSERT INTO t2 VALUES (1);
+
+CREATE TABLE t3 (a int(10), b int(10), c int(10),
+                PRIMARY KEY (a)) Engine=InnoDB;
+INSERT INTO t3 VALUES (1,2,1);
+
+SELECT t1.* FROM t1 WHERE (SELECT COUNT(*) FROM t3,t2 WHERE t3.c=t2.a 
+                           and t2.a='1' AND t1.a=t3.b) > 0;
+
+DROP TABLE t1,t2,t3;
diff --git a/sql/sql_select.cc b/sql/sql_select.cc
index 96ac98cfb88..339826b3692 100644
--- a/sql/sql_select.cc
+++ b/sql/sql_select.cc
@@ -4470,10 +4470,16 @@ return_zero_rows(JOIN *join, select_result *result,TABLE_LIST *tables,
   DBUG_RETURN(0);
 }
 
-
+/*
+  used only in JOIN::clear
+*/
 static void clear_tables(JOIN *join)
 {
-  for (uint i=0 ; i < join->tables ; i++)
+  /* 
+    must clear only the non-const tables, as const tables
+    are not re-calculated.
+  */
+  for (uint i=join->const_tables ; i < join->tables ; i++)
     mark_as_null_row(join->table[i]);		// All fields are NULL
 }