Bug#54416 MAX from JOIN with HAVING returning NULL with 5.1 and Empty set

The problem there is that HAVING condition evaluates const
parts of condition despite the condition has references
on aggregate functions. Table t1 became const tables
after make_join_statistics and table1.pk = 1, HAVING is
transformed into MAX(1) < 7 and taken away from HAVING.
The fix is to skip evaluation of HAVING conts parts if
HAVING condition has references on aggregate functions.


mysql-test/r/having.result:
  test case
mysql-test/t/having.test:
  test case
sql/sql_select.cc:
  skip evaluation of HAVING conts parts if
  HAVING condition has references on aggregate functions.

3 files changed, 38 insertions, 1 deletions

diff --git a/mysql-test/r/having.result b/mysql-test/r/having.result
index 54293e9d02e..cd1b4ae0218 100644
--- a/mysql-test/r/having.result
+++ b/mysql-test/r/having.result
@@ -530,3 +530,19 @@ MAX(t2.f2)
 NULL
 DROP TABLE t1,t2;
 End of 5.0 tests
+#
+# Bug#54416 MAX from JOIN with HAVING returning NULL with 5.1 and Empty set
+#
+CREATE TABLE t1 (f1 INT(11), f2 VARCHAR(1), PRIMARY KEY (f1));
+INSERT INTO t1 VALUES (1,'f');
+CREATE TABLE t2 (f1 INT(11), f2 VARCHAR(1));
+INSERT INTO t2 VALUES (2,'m');
+INSERT INTO t2 VALUES (3,'m');
+INSERT INTO t2 VALUES (11,NULL);
+INSERT INTO t2 VALUES (12,'k');
+SELECT MAX(t1.f1) field1
+FROM t1 JOIN t2 ON t2.f2 LIKE 'x'
+HAVING field1 < 7;
+field1
+DROP TABLE t1,t2;
+End of 5.1 tests
diff --git a/mysql-test/t/having.test b/mysql-test/t/having.test
index 65bf9518a5c..c808e747523 100644
--- a/mysql-test/t/having.test
+++ b/mysql-test/t/having.test
@@ -544,3 +544,24 @@ ORDER BY t1.f2;
 DROP TABLE t1,t2;
 
 --echo End of 5.0 tests
+
+--echo #
+--echo # Bug#54416 MAX from JOIN with HAVING returning NULL with 5.1 and Empty set
+--echo #
+
+CREATE TABLE t1 (f1 INT(11), f2 VARCHAR(1), PRIMARY KEY (f1));
+INSERT INTO t1 VALUES (1,'f');
+
+CREATE TABLE t2 (f1 INT(11), f2 VARCHAR(1));
+INSERT INTO t2 VALUES (2,'m');
+INSERT INTO t2 VALUES (3,'m');
+INSERT INTO t2 VALUES (11,NULL);
+INSERT INTO t2 VALUES (12,'k');
+
+SELECT MAX(t1.f1) field1
+FROM t1 JOIN t2 ON t2.f2 LIKE 'x'
+HAVING field1 < 7;
+
+DROP TABLE t1,t2;
+
+--echo End of 5.1 tests
diff --git a/sql/sql_select.cc b/sql/sql_select.cc
index b20726fc151..fe391b50bb9 100644
--- a/sql/sql_select.cc
+++ b/sql/sql_select.cc
@@ -1132,7 +1132,7 @@ JOIN::optimize()
     elements may be lost during further having
     condition transformation in JOIN::exec.
   */
-  if (having && const_table_map)
+  if (having && const_table_map && !having->with_sum_func)
   {
     having->update_used_tables();
     having= remove_eq_conds(thd, having, &having_value);