Bug #27573 MIN() on an indexed column which is always NULL sets _other_ results to NULL
Submitted: 1 Apr 2007 17:27 Modified: 9 Jun 2007 18:19
Reporter: Ralf Neubauer Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL 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 CPU Architecture:Any

[1 Apr 2007 17:27] Ralf Neubauer 
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)
[1 Apr 2007 18:09] Valeriy Kravchuk 
Thank you for a bug report. Verified just as described.
[11 Apr 2007 11: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 8: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 9: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 11: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 11: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 16: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 8: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 15: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 13: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 17:11] Bugs System 
Pushed into 5.0.44
[20 May 2007 17:13] Bugs System 
Pushed into 5.1.19-beta
[9 Jun 2007 18: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.