Bug #52051 Aggregate functions incorrectly returns NULL from outer join query
Submitted: 14 Mar 2010 19:09 Modified: 14 Oct 2010 14:22
Reporter: Ole John Aske Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S2 (Serious)
Version:5.0.91, 5.1.37, 5.1.44, 5.1.46-bzr, 5.5.3-m2 OS:Any
Assigned to: Ramil Kalimullin
Triage: Triaged: D2 (Serious)

[14 Mar 2010 19:09] Ole John Aske
Description:
NULL values are incorectly returned as the result when :

- The directly underlying query contains outer join.
- One of the table in the subquery is JT_SYSTEM optimized (<= 1 records).

Looking at the example below: The LEFT OUTER JOIN query itself
returns:

SELECT other_table.pk
 FROM other_table
 LEFT JOIN empty_table ON other_table.pk = empty_table.pk;

+----+
| pk |
+----+
|  1 |
|  2 |
+----+
2 rows in set (0.00 sec)

Selecting MIN() from the same column incorrectly returns NULL. We have the same incorrect behaviour with other aggregate functions (MAX() ....).

Looking at EXPLAIN output with and without MIN() is interesting:

mysql> explain
    -> SELECT other_table.pk
    ->  FROM other_table
    ->  LEFT JOIN empty_table ON other_table.pk = empty_table.pk;
+----+-------------+-------------+--------+---------------+---------+---------+------+------+---------------------+
| id | select_type | table       | type   | possible_keys | key     | key_len | ref  | rows | Extra               |
+----+-------------+-------------+--------+---------------+---------+---------+------+------+---------------------+
|  1 | SIMPLE      | empty_table | system | NULL          | NULL    | NULL    | NULL |    0 | const row not found |
|  1 | SIMPLE      | other_table | index  | NULL          | PRIMARY | 4       | NULL |    2 | Using index         |
+----+-------------+-------------+--------+---------------+---------+---------+------+------+---------------------+
2 rows in set (0.01 sec)

mysql> 
mysql> explain
    -> SELECT min(other_table.pk)
    ->  FROM other_table
    ->  LEFT JOIN empty_table ON other_table.pk = empty_table.pk;
+----+-------------+-------+------+---------------+------+---------+------+------+------------------------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra                        |
+----+-------------+-------+------+---------------+------+---------+------+------+------------------------------+
|  1 | SIMPLE      | NULL  | NULL | NULL          | NULL | NULL    | NULL | NULL | Select tables optimized away |
+----+-------------+-------+------+---------------+------+---------+------+------+------------------------------+
1 row in set (0.00 sec)

The last explain says 'Select tables optimized away' - This is an incorrect execution plan in my humble opinion: Even if the right table in the 'left outer join' is an empty 'const table', the left table need to be accessed in order to fetch the MIN value. 

NOTE1:
Actually *only* the left table will matter in a 'select min/max(<any column from t1>) from t1 left join t2'. This query could be simplified to: 'select min/max(<and column from t1>) from t1'

NOTE2:
HA_STATS_RECORDS_IS_EXACT is required in order for the JT_SYSTEM table optimization to happen. It is therefore not reproducible with 'engine=ndbcluster' as Cluster does not report EXACT statistics.

How to repeat:
create table empty_table (pk int primary key) engine = myisam;

create table other_table (pk int primary key) engine = myisam;
insert into other_table values (1), (2);

SELECT min(other_table.pk)
 FROM other_table
 LEFT JOIN empty_table ON other_table.pk = empty_table.pk;

+---------------------+
| min(other_table.pk) |
+---------------------+
|                NULL |
+---------------------+
1 row in set (0.00 sec)

Suggested fix:
Assumed to be related to when a table is JT_SYSTEM optimized into a const table. Looking at similar code for JT_CONST optimization only tables which are not 'dependent' on an outer table can be const-optimized. This check is not present for JT_SYSTEM optimization, but I can see any reason why the same restriction should not apply here also.
[15 Mar 2010 4:22] Valerii Kravchuk
Thank you for the bug report. Verified with 5.1.46 from bzr on Mac OS X:

mysql> create table other_table (pk int primary key) engine = myisam;
Query OK, 0 rows affected (0.15 sec)

mysql> insert into other_table values (1), (2);
Query OK, 2 rows affected (0.03 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> SELECT min(other_table.pk)
    ->  FROM other_table
    ->  LEFT JOIN empty_table ON other_table.pk = empty_table.pk;
+---------------------+
| min(other_table.pk) |
+---------------------+
|                NULL |
+---------------------+
1 row in set (0.01 sec)

mysql> explain SELECT min(other_table.pk)  FROM other_table  LEFT JOIN empty_table ON other_table.pk = empty_table.pk;
+----+-------------+-------+------+---------------+------+---------+------+------+------------------------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra                        |
+----+-------------+-------+------+---------------+------+---------+------+------+------------------------------+
|  1 | SIMPLE      | NULL  | NULL | NULL          | NULL | NULL    | NULL | NULL | Select tables optimized away |
+----+-------------+-------+------+---------------+------+---------+------+------+------------------------------+
1 row in set (0.02 sec)

mysql> select version();
+--------------+
| version()    |
+--------------+
| 5.1.46-debug |
+--------------+
1 row in set (0.00 sec)
[15 Mar 2010 16:52] Valerii Kravchuk
It is NOT a recent regression:

77-52-28-202:mysql openxs$ bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.1.37 MySQL Community Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> create table empty_table (pk int primary key) engine = myisam;
Query OK, 0 rows affected (0.06 sec)

mysql> create table other_table (pk int primary key) engine = myisam;
Query OK, 0 rows affected (0.09 sec)

mysql> insert into other_table values (1), (2);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> SELECT min(other_table.pk)
    ->  FROM other_table
    ->  LEFT JOIN empty_table ON other_table.pk = empty_table.pk;
+---------------------+
| min(other_table.pk) |
+---------------------+
|                NULL | 
+---------------------+
1 row in set (0.00 sec)
[2 Apr 2010 5:28] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/104951

3436 Ramil Kalimullin	2010-04-02
      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.
[7 May 2010 13:06] Ole John Aske
Similar to bug#53485, Patch for bug#52051 could possibly be extended to also fix this problem.
[12 May 2010 16:11] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/108181

3381 Ramil Kalimullin	2010-05-12
            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.
[28 May 2010 6:02] Bugs System
Pushed into mysql-next-mr (revid:alik@sun.com-20100524190136-egaq7e8zgkwb9aqi) (version source revid:alik@sun.com-20100524190136-egaq7e8zgkwb9aqi) (pib:16)
[28 May 2010 6:31] Bugs System
Pushed into 6.0.14-alpha (revid:alik@sun.com-20100524190941-nuudpx60if25wsvx) (version source revid:alik@sun.com-20100514054548-91z72f0mcskr84kj) (merge vers: 6.0.14-alpha) (pib:16)
[28 May 2010 6:59] Bugs System
Pushed into 5.5.5-m3 (revid:alik@sun.com-20100524185725-c8k5q7v60i5nix3t) (version source revid:alexey.kopytov@sun.com-20100523204118-0tl3goawu658rxh6) (merge vers: 5.5.5-m3) (pib:16)
[2 Jun 2010 8:50] Bugs System
Pushed into 5.1.48 (revid:georgi.kodinov@oracle.com-20100602084411-2yu607bslbmgufl3) (version source revid:ramil@mysql.com-20100512161033-lzwwhugi7g95t0wo) (merge vers: 5.1.47) (pib:16)
[8 Jun 2010 19:02] Paul Dubois
Noted in 5.1.48, 5.5.5, 6.0.14 changelogs.

Aggregate functions could incorrectly return NULL in outer join
queries.
[14 Oct 2010 8:34] Bugs System
Pushed into mysql-5.1-telco-7.0 5.1.51-ndb-7.0.20 (revid:martin.skold@mysql.com-20101014082627-jrmy9xbfbtrebw3c) (version source revid:vasil.dimov@oracle.com-20100513074652-0cvlhgkesgbb2bfh) (merge vers: 5.5.5-m3) (pib:21)
[14 Oct 2010 8:49] Bugs System
Pushed into mysql-5.1-telco-6.3 5.1.51-ndb-6.3.39 (revid:martin.skold@mysql.com-20101014083757-5qo48b86d69zjvzj) (version source revid:vasil.dimov@oracle.com-20100513074652-0cvlhgkesgbb2bfh) (merge vers: 5.5.5-m3) (pib:21)
[14 Oct 2010 9:03] Bugs System
Pushed into mysql-5.1-telco-6.2 5.1.51-ndb-6.2.19 (revid:martin.skold@mysql.com-20101014084420-y54ecj85j5we27oa) (version source revid:vasil.dimov@oracle.com-20100513074652-0cvlhgkesgbb2bfh) (merge vers: 5.5.5-m3) (pib:21)
[14 Oct 2010 14:22] Jon Stephens
Already documented in the 5.1.48 changelog; no additional changelog entries required. Set back to Closed state.
[24 Dec 2012 8:44] Erlend Dahl
Bug #47541 MIN/MAX was marked as a duplicate.