fixed zero result case for group functions in subquery (Bug #3505)

fixed LIMIT 0 for zero rows optimisation


mysql-test/r/subselect.result:
  test case for bugreport
mysql-test/t/subselect.test:
  test case for bugreport
sql/sql_select.cc:
  cleanup sum function on reinit
  take in account LIMIT if zero result optimisation case
  do not forgot sum function in zero result case for subqueries

3 files changed, 34 insertions, 0 deletions

diff --git a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result
index cf0f9b4cc14..32770614221 100644
--- a/mysql-test/r/subselect.result
+++ b/mysql-test/r/subselect.result
@@ -1707,3 +1707,15 @@ create table t3(flag int);
 select (select * from t3 where id not null) from t1, t2;
 ERROR 42000: You have an error in your SQL syntax.  Check the manual that corresponds to your MySQL server version for the right syntax to use near 'null) from t1, t2' at line 1
 drop table t1,t2,t3;
+CREATE TABLE t1 (id INT);
+CREATE TABLE t2 (id INT);
+INSERT INTO t1 VALUES (1), (2);
+INSERT INTO t2 VALUES (1);
+SELECT t1.id, ( SELECT COUNT(t.id) FROM t2 AS t WHERE t.id = t1.id ) AS c FROM t1 LEFT JOIN t2 USING (id);
+id	c
+1	1
+2	0
+SELECT t1.id, ( SELECT COUNT(t.id) FROM t2 AS t WHERE t.id = t1.id ) AS c FROM t1 LEFT JOIN t2 USING (id) ORDER BY t1.id;
+id	c
+1	1
+2	0
diff --git a/mysql-test/t/subselect.test b/mysql-test/t/subselect.test
index 9e82a77b036..bf7a8a6292d 100644
--- a/mysql-test/t/subselect.test
+++ b/mysql-test/t/subselect.test
@@ -1115,3 +1115,14 @@ create table t3(flag int);
 -- error 1064
 select (select * from t3 where id not null) from t1, t2;
 drop table t1,t2,t3;
+
+#
+# aggregate functions (Bug #3505)
+#
+CREATE TABLE t1 (id INT);
+CREATE TABLE t2 (id INT);
+INSERT INTO t1 VALUES (1), (2);
+INSERT INTO t2 VALUES (1);
+SELECT t1.id, ( SELECT COUNT(t.id) FROM t2 AS t WHERE t.id = t1.id ) AS c FROM t1 LEFT JOIN t2 USING (id);
+SELECT t1.id, ( SELECT COUNT(t.id) FROM t2 AS t WHERE t.id = t1.id ) AS c FROM t1 LEFT JOIN t2 USING (id) ORDER BY t1.id;
+DROP TABLE t1,t2
diff --git a/sql/sql_select.cc b/sql/sql_select.cc
index a97c484c483..119cdf94401 100644
--- a/sql/sql_select.cc
+++ b/sql/sql_select.cc
@@ -1051,6 +1051,13 @@ JOIN::reinit()
   if (tmp_join)
     restore_tmp();
 
+  if (sum_funcs)
+  {
+    Item_sum *func, **func_ptr= sum_funcs;
+    while ((func= *(func_ptr++)))
+      func->clear();
+  }
+
   DBUG_RETURN(0);
 }
 
@@ -1115,6 +1122,7 @@ JOIN::exec()
   if (zero_result_cause)
   {
     (void) return_zero_rows(this, result, tables_list, fields_list,
+			    do_send_rows &&
 			    tmp_table_param.sum_func_count != 0 &&
 			    !group_list,
 			    select_options,
@@ -5666,6 +5674,9 @@ do_select(JOIN *join,List<Item> *fields,TABLE *table,Procedure *procedure)
       if (!(error=(*end_select)(join,join_tab,0)) || error == -3)
 	error=(*end_select)(join,join_tab,1);
     }
+    else if (join->do_send_rows && join->tmp_table_param.sum_func_count != 0 &&
+	     !join->group_list)
+      error= join->result->send_data(*join->fields);
   }
   else
   {