| 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: | |
| 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 | CPU Architecture: | Any |
[15 Mar 2010 4:22]
Valeriy 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]
Valeriy 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.
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.