| Bug #27573 | MIN() on an indexed column which is always NULL sets _other_ results to NULL | ||
|---|---|---|---|
| Submitted: | 1 Apr 2007 19:27 | Modified: | 9 Jun 2007 20:19 |
| Reporter: | Ralf Neubauer | ||
| Status: | Closed | ||
| Category: | Server: Optimizer | Severity: | S2 (Serious) |
| Version: | 5.0.32 and 5.0.36, 5.0.37 | OS: | Linux (Debian and Win64) |
| Assigned to: | Martin Hansson | Target Version: | |
[1 Apr 2007 20:09]
Valeriy Kravchuk
Thank you for a bug report. Verified just as described.
[11 Apr 2007 13:39]
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/24269 ChangeSet@1.2432, 2007-04-11 14:33:09+03:00, mhansson@linux-st28.site +3 -0 Bug#27573: MIN() on an indexed column which is always NULL sets _other_ results to NULL We have the following case: SELECT MIN(keypart2),... FROM t1 WHERE keypart1 = constant; There is an optimization which will substitute a constant for MIN(keypart2), inserting the 1) minimum non-null value where the WHERE clause still matches, or 2) null if there are only NULL values for keypart2. If no row matches, the result will be 3) a row of NULLs. The previous implementation skipped step 2, and went straight to a row of NULLs.
[20 Apr 2007 10:32]
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/24975 ChangeSet@1.2432, 2007-04-20 10:31:35+03:00, mhansson@linux-st28.site +3 -0 Bug#27573: MIN() on an indexed column which is always NULL sets _other_ results to NULL We have the following case: SELECT MIN(keypart2),... FROM t1 WHERE keypart1 = constant; There is an optimization which will substitute a constant for MIN(keypart2), inserting the 1) minimum non-null value where the WHERE clause still matches, or 2) null if there are only NULL values for keypart2. If no row matches, the result will be 3) a row of NULLs. The previous implementation skipped step 2, and went straight to a row of NULLs.
[20 Apr 2007 11:56]
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/24985 ChangeSet@1.2432, 2007-04-20 11:56:28+03:00, mhansson@linux-st28.site +3 -0 Bug#27573: MIN() on an indexed column which is always NULL sets _other_ results to NULL We have the following case: SELECT MIN(keypart2),... FROM t1 WHERE keypart1 = constant; There is an optimization which will substitute a constant for MIN(keypart2), inserting the 1) minimum non-null value where the WHERE clause still matches, or 2) null if there are only NULL values for keypart2. If no row matches, the result will be 3) a row of NULLs. The previous implementation skipped step 2, and went straight to a row of NULLs.
[23 Apr 2007 13:53]
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/25108 ChangeSet@1.2432, 2007-04-23 13:53:30+03:00, mhansson@linux-st28.site +3 -0 Bug#27573: MIN() on an indexed column which is always NULL sets _other_ results to NULL We have the following case: SELECT MIN(keypart2),... FROM t1 WHERE keypart1 = constant; There is an optimization which will substitute a constant for MIN(keypart2), inserting the 1) minimum non-null value where the WHERE clause still matches, or 2) null if there are only NULL values for keypart2. If no row matches, the result will be 3) a row of NULLs. The previous implementation skipped step 2, and went straight to a row of NULLs.
[23 Apr 2007 13:59]
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/25109 ChangeSet@1.2432, 2007-04-23 13:59:34+03:00, mhansson@linux-st28.site +3 -0 Bug#27573: MIN() on an indexed column which is always NULL sets _other_ results to NULL We have the following case: SELECT MIN(keypart2),... FROM t1 WHERE keypart1 = constant; There is an optimization which will substitute a constant for MIN(keypart2), inserting the 1) minimum non-null value where the WHERE clause still matches, or 2) null if there are only NULL values for keypart2. If no row matches, the result will be 3) a row of NULLs. The previous implementation skipped step 2, and went straight to a row of NULLs.
[23 Apr 2007 18:00]
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/25139 ChangeSet@1.2432, 2007-04-23 18:00:43+03:00, mhansson@linux-st28.site +3 -0 Bug#27573: MIN() on an indexed column which is always NULL sets _other_ results to NULL We have the following case: SELECT MIN(keypart2),... FROM t1 WHERE keypart1 = constant; There is an optimization which will substitute a constant for MIN(keypart2), inserting the 1) minimum non-null value where the WHERE clause still matches, or 2) null if there are only NULL values for keypart2. If no row matches, the result will be 3) a row of NULLs. The previous implementation skipped step 2, and went straight to a row of NULLs.
[10 May 2007 10:55]
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/26423 ChangeSet@1.2432, 2007-05-10 10:55:28+03:00, mhansson@linux-st28.site +3 -0 Bug#27573: MIN() on an indexed column which is always NULL sets _other_ results to NULL We have the following case: SELECT MIN(key_part_k) FROM t1 WHERE key_part_1 = const and ... and key_part_k-1 = const There is an optimization which will substitute a constant for MIN(key_part_k), inserting the 1) minimum non-null value where the WHERE clause still matches, or 2) null if there are only NULL values for keypart2. If no row matches, the result will be 3) a row of NULLs. The previous implementation skipped step 2, and went straight to a row of NULLs.
[14 May 2007 17:13]
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/26618 ChangeSet@1.2432, 2007-05-14 17:13:34+03:00, mhansson@linux-st28.site +3 -0 Bug#27573: MIN() on an indexed column which is always NULL sets _other_ results to NULL For queries of the form SELECT MIN(key_part_k) FROM t1 WHERE key_part_1 = const and ... and key_part_k-1 = const, the opt_sum_query optimization tries to use an index to substitute MIN/MAX functions with their values according to the following rules: 1) Insert the minimum non-null values where the WHERE clause still matches, or 3) A row of nulls However, the correct semantics requires that there is a third case 2) such that a NULL value is substituted if there are only NULL values for key_part_k. The patch modifies opt_sum_query() to handle this missing case.
[15 May 2007 15:29]
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/26694 ChangeSet@1.2432, 2007-05-15 15:29:12+03:00, mhansson@linux-st28.site +3 -0 Bug#27573: MIN() on an indexed column which is always NULL sets _other_ results to NULL For queries of the form SELECT MIN(key_part_k) FROM t1 WHERE key_part_1 = const and ... and key_part_k-1 = const, the opt_sum_query optimization tries to use an index to substitute MIN/MAX functions with their values according to the following rules: 1) Insert the minimum non-null values where the WHERE clause still matches, or 3) A row of nulls However, the correct semantics requires that there is a third case 2) such that a NULL value is substituted if there are only NULL values for key_part_k. The patch modifies opt_sum_query() to handle this missing case.
[20 May 2007 19:11]
Bugs System
Pushed into 5.0.44
[20 May 2007 19:13]
Bugs System
Pushed into 5.1.19-beta
[9 Jun 2007 20:19]
Paul DuBois
Noted 5.0.44, 5.1.19 changelogs. Selecting MIN() on an indexed column that contained only NULL values caused NULL to be returned for other result columns.
Description: I just tried to simplify an error condition I found with perl-DBI on 5.0.36. Works on 5.0.32 too. MIN() on an indexed column which is always NULL sets the _other_ result columns to NULL. MIN() on a non-indexed column or MAX() on the indexed column are no problem. It is nearly impossible to search for bugs involving all of the words "MIN", "INDEX" and "NULL", btw. How to repeat: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 10 Server version: 5.0.32-Debian_7etch1-log Debian etch distribution Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> create table minmax ( a int unsigned, b int unsigned, c int unsigned, index(a) ); Query OK, 0 rows affected (0.03 sec) mysql> insert into minmax (a,b,c) values (NULL, 1, 2), (NULL, 3, 4); Query OK, 2 rows affected (0.00 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> select * from minmax; +------+------+------+ | a | b | c | +------+------+------+ | NULL | 1 | 2 | | NULL | 3 | 4 | +------+------+------+ 2 rows in set (0.00 sec) mysql> select min(a), min(b), min(c) from minmax; +--------+--------+--------+ | min(a) | min(b) | min(c) | +--------+--------+--------+ | NULL | NULL | NULL | +--------+--------+--------+ 1 row in set (0.00 sec) mysql> select max(a), max(b), max(c) from minmax; +--------+--------+--------+ | max(a) | max(b) | max(c) | +--------+--------+--------+ | NULL | 3 | 4 | +--------+--------+--------+ 1 row in set (0.00 sec) mysql> select min(a), min(b), min(c), max(a), max(b), max(c) from minmax; +--------+--------+--------+--------+--------+--------+ | min(a) | min(b) | min(c) | max(a) | max(b) | max(c) | +--------+--------+--------+--------+--------+--------+ | NULL | NULL | NULL | NULL | NULL | NULL | +--------+--------+--------+--------+--------+--------+ 1 row in set (0.01 sec) mysql> select max(a), min(b), min(c), max(a), max(b), max(c) from minmax; +--------+--------+--------+--------+--------+--------+ | max(a) | min(b) | min(c) | max(a) | max(b) | max(c) | +--------+--------+--------+--------+--------+--------+ | NULL | 1 | 2 | NULL | 3 | 4 | +--------+--------+--------+--------+--------+--------+ 1 row in set (0.00 sec) mysql> insert into minmax (a,b,c) values (5, 6, 7); Query OK, 1 row affected (0.00 sec) mysql> select min(a), min(b), min(c), max(a), max(b), max(c) from minmax; +--------+--------+--------+--------+--------+--------+ | min(a) | min(b) | min(c) | max(a) | max(b) | max(c) | +--------+--------+--------+--------+--------+--------+ | 5 | 1 | 2 | 5 | 6 | 7 | +--------+--------+--------+--------+--------+--------+ 1 row in set (0.00 sec) mysql> show create table minmax; +--------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +--------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | minmax | CREATE TABLE `minmax` ( `a` int(10) unsigned default NULL, `b` int(10) unsigned default NULL, `c` int(10) unsigned default NULL, KEY `a` (`a`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 | +--------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec)