MDEV-30240 Wrong result upon aggregate function with SQL_BUFFER_RESULT

The problem was that when storing rows into a temporary table,
MIN/MAX items that where marked as constants (as theire value had
been computed at start of query) would be reset.

Fixed by not reseting MIN/MAX items that are marked as const in
Item_sum_min_max::clear().

5 files changed, 50 insertions, 4 deletions

diff --git a/mysql-test/main/group_min_max.result b/mysql-test/main/group_min_max.result
index 214f64df874..d1bd4d8cedb 100644
--- a/mysql-test/main/group_min_max.result
+++ b/mysql-test/main/group_min_max.result
@@ -4065,3 +4065,23 @@ SELECT DISTINCT owner_id FROM t1 WHERE foo = true GROUP BY owner_id HAVING (COUN
 owner_id
 1
 DROP TABLE t1;
+#
+# MDEV-30240 Wrong result upon aggregate function with SQL_BUFFER_RESULT
+#
+drop table if exists t1,t2;
+Warnings:
+Note	1051	Unknown table 'test.t1,test.t2'
+CREATE TABLE t1 (pk INT PRIMARY KEY);
+INSERT INTO t1 VALUES (1),(2);
+CREATE TABLE t2 (a INT);
+INSERT INTO t2 VALUES (1),(2);
+SELECT SQL_BUFFER_RESULT MIN(pk) FROM t1, t2;
+MIN(pk)
+1
+SELECT MIN(pk) FROM t1, t2;
+MIN(pk)
+1
+DROP TABLE t1, t2;
+#
+# End of 10.5 tests
+#
diff --git a/mysql-test/main/group_min_max.test b/mysql-test/main/group_min_max.test
index 3b043fc0842..7de57d75d36 100644
--- a/mysql-test/main/group_min_max.test
+++ b/mysql-test/main/group_min_max.test
@@ -1723,3 +1723,20 @@ EXPLAIN
 SELECT DISTINCT owner_id FROM t1 WHERE foo = true GROUP BY owner_id HAVING (COUNT(*) = 1);
 SELECT DISTINCT owner_id FROM t1 WHERE foo = true GROUP BY owner_id HAVING (COUNT(*) = 1);
 DROP TABLE t1;
+
+--echo #
+--echo # MDEV-30240 Wrong result upon aggregate function with SQL_BUFFER_RESULT
+--echo #
+
+drop table if exists t1,t2;
+CREATE TABLE t1 (pk INT PRIMARY KEY);
+INSERT INTO t1 VALUES (1),(2);
+CREATE TABLE t2 (a INT);
+INSERT INTO t2 VALUES (1),(2);
+SELECT SQL_BUFFER_RESULT MIN(pk) FROM t1, t2;
+SELECT MIN(pk) FROM t1, t2;
+DROP TABLE t1, t2;
+
+--echo #
+--echo # End of 10.5 tests
+--echo #
diff --git a/sql/item_sum.cc b/sql/item_sum.cc
index 1c17b5c6409..9baf945644e 100644
--- a/sql/item_sum.cc
+++ b/sql/item_sum.cc
@@ -2361,8 +2361,15 @@ Item *Item_sum_variance::result_item(THD *thd, Field *field)
 void Item_sum_min_max::clear()
 {
   DBUG_ENTER("Item_sum_min_max::clear");
-  value->clear();
-  null_value= 1;
+  /*
+    We should not clear const items (from SELECT MIN(key) from t1) as then we would loose the
+    value cached in opt_sum_query() where we replace MIN/MAX/COUNT with constants.
+  */
+  if (!const_item())
+  {
+    value->clear();
+    null_value= 1;
+  }
   DBUG_VOID_RETURN;
 }
 
@@ -2475,9 +2482,12 @@ void Item_sum_min_max::no_rows_in_result()
   /* We may be called here twice in case of ref field in function */
   if (was_values)
   {
+    bool org_const_item_cache= const_item_cache;
     was_values= FALSE;
     was_null_value= value->null_value;
+    const_item_cache= 0;             // Ensure that clear works on const items
     clear();
+    const_item_cache= org_const_item_cache;
   }
   DBUG_VOID_RETURN;
 }
diff --git a/sql/opt_sum.cc b/sql/opt_sum.cc
index 27360d4a10c..8664ccae53c 100644
--- a/sql/opt_sum.cc
+++ b/sql/opt_sum.cc
@@ -438,7 +438,7 @@ int opt_sum_query(THD *thd,
             The optimization is not applicable in both cases:
             (a) 'expr' is a non-constant expression. Then we can't
             replace 'expr' by a constant.
-            (b) 'expr' is a costant. According to ANSI, MIN/MAX must return
+            (b) 'expr' is a constant. According to ANSI, MIN/MAX must return
             NULL if the query does not return any rows. Thus, if we are not
             able to determine if the query returns any rows, we can't apply
             the optimization and replace MIN/MAX with a constant.
diff --git a/sql/sql_select.cc b/sql/sql_select.cc
index 8de9a3e8b58..1ce7439e06e 100644
--- a/sql/sql_select.cc
+++ b/sql/sql_select.cc
@@ -25871,7 +25871,6 @@ bool JOIN::alloc_func_list()
   @param field_list        All items
   @param send_result_set_metadata       Items in select list
   @param before_group_by   Set to 1 if this is called before GROUP BY handling
-  @param recompute         Set to TRUE if sum_funcs must be recomputed
 
   @retval
     0  ok