Fix for bug#52051: Aggregate functions incorrectly returns

      NULL from outer join query
      
      Problem: optimising MIN/MAX() queries without GROUP BY clause
      by replacing the aggregate expression with a constant, we may set it
      to NULL disregarding the fact that there may be outer joins involved.
      
      Fix: don't replace MIN/MAX() with NULL if there're outer joins.
      
      Note: the fix itself is just
      - if (!count)
      + if (!count && !outer_tables)
          set to NULL
      
      The rest of the patch eliminates repeated code to improve speed
      and for easy maintenance of the code.


mysql-test/r/group_by.result:
          Fix for bug#52051: Aggregate functions incorrectly returns
          NULL from outer join query
            - test result.
mysql-test/t/group_by.test:
          Fix for bug#52051: Aggregate functions incorrectly returns
          NULL from outer join query
            - test case.
sql/opt_sum.cc:
          Fix for bug#52051: Aggregate functions incorrectly returns
          NULL from outer join query
            - optimising MIN/MAX() queries without GROUP BY clause by
          replacing them with a constant, take into account that
          there're may be outer joins involved.
            - repeated code for MIN/MAX optimization in the opt_sum_query()
          eliminated by introducing new functions that read MIN/MAX values
          using index and combining MIN/MAX cases to one.

1 files changed, 15 insertions, 0 deletions

diff --git a/mysql-test/t/group_by.test b/mysql-test/t/group_by.test
index e6ea5ecc7f6..c5b27ee1a62 100644
--- a/mysql-test/t/group_by.test
+++ b/mysql-test/t/group_by.test
@@ -1205,6 +1205,21 @@ SELECT (SELECT t2.a FROM t2 WHERE t2.a = t1.a) aa, b, COUNT(         b)
 
 DROP TABLE t1, t2;
 
+
+--echo #
+--echo # Bug#52051: Aggregate functions incorrectly returns NULL from outer
+--echo # join query
+--echo #
+CREATE TABLE t1 (a INT PRIMARY KEY);
+CREATE TABLE t2 (a INT PRIMARY KEY);
+INSERT INTO t2 VALUES (1), (2);
+EXPLAIN SELECT MIN(t2.a) FROM t2 LEFT JOIN t1 ON t2.a = t1.a;
+SELECT MIN(t2.a) FROM t2 LEFT JOIN t1 ON t2.a = t1.a;
+EXPLAIN SELECT MAX(t2.a) FROM t2 LEFT JOIN t1 ON t2.a = t1.a;
+SELECT MAX(t2.a) FROM t2 LEFT JOIN t1 ON t2.a = t1.a;
+DROP TABLE t1, t2;
+
+
 --echo #
 
 --echo # End of 5.1 tests