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 |
[14 Mar 2010 19:09]
Ole John Aske
[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.