Bug #47762 Incorrect result from MIN() when WHERE tests NOT NULL column for NULL
Submitted: 1 Oct 2009 14:29 Modified: 20 Jun 2010 22:46
Reporter: Jørgen Løland Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S3 (Non-critical)
Version:5.0.87, 5.1, 6.0,5.4 OS:Any
Assigned to: Martin Hansson CPU Architecture:Any
Triage: Triaged: D2 (Serious)

[1 Oct 2009 14:29] Jørgen Løland
Description:
The following test returns wrong result for the "SELECT... WHERE i=NULL":

CREATE TABLE t1 (i int(11) NOT NULL primary key);
INSERT INTO t1 VALUES (1),(2);

# Incorrect
SELECT MIN(i)
FROM t1
WHERE i=NULL;
MIN(i)
2

# Correct
SELECT MIN(i)
FROM t1
WHERE i<0;
MIN(i)
NULL

How to repeat:
Run attached test

Suggested fix:
Make the select return NULL
[1 Oct 2009 14:32] Jørgen Løland
explain
SELECT MIN(i)
FROM t1
WHERE i=NULL;
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

This indicates that the problem might be in opt_sum_query().
[1 Oct 2009 14:35] Jørgen Løland
explain
SELECT MIN(i)
FROM t1
WHERE i<0;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No matching min/max row

This "Extra" text also comes from the block after opt_sum_fun(). A fix might be to make the i=NULL condition return "No matching min/max row" code from opt_sum_func().
[1 Oct 2009 14:41] Valeriy Kravchuk
Thank you for the bug report. Verified just as described also with latest 5.0.87 from bzr on Mac OS X:

77-52-242-160:5.0 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.0.87 Source distribution

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

mysql> CREATE TABLE t1 (i int(11) NOT NULL primary key);
Query OK, 0 rows affected (0,03 sec)

mysql> INSERT INTO t1 VALUES (1),(2);
Query OK, 2 rows affected (0,00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> SELECT MIN(i)
    -> FROM t1
    -> WHERE i=NULL;
+--------+
| MIN(i) |
+--------+
|      2 | 
+--------+
1 row in set (0,00 sec)

mysql> SELECT MIN(i) FROM t1 WHERE i<0;
+--------+
| MIN(i) |
+--------+
|   NULL | 
+--------+
1 row in set (0,00 sec)

mysql> explain SELECT MIN(i) FROM t1 WHERE i=NULL;
+----+-------------+-------+------+---------------+------+---------+------+------+------------------------------+
| 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)
[1 Oct 2009 14:44] Miguel Solorzano
Version 5.4 affected too:

mysql 5.4 >SELECT MIN(i)
    -> FROM t1
    -> WHERE i=NULL;
+--------+
| MIN(i) |
+--------+
|      2 |
+--------+
1 row in set (0.02 sec)
[13 Oct 2009 7:03] Jørgen Løland
In func_group.test in the 6.0 codebase, a test for BUG#46680 was pushed that actually shows the wrong result due to this bug. The test is needed to verify that the server does not crash, but when this bug has been fixed, the result file of func_group needs to be updated. From func_group.test:

--echo
--echo #
--echo # 3) Test that subquery materialization is setup for query with
--echo #    premature optimize() exit due to "Select tables optimized away"
--echo #
--echo # NOTE: The result of this query is actually wrong; it should be NULL
--echo # See BUG#47762. Even so, the test case is still needed to test
--echo # that the HAVING subquery does not crash the server
--echo # 
SELECT MIN(pk)
FROM t1
WHERE pk=NULL
HAVING ('m') IN ( 
SELECT v
FROM t2);
[28 Oct 2009 10:30] Roy Lyseng
In the following query, the subquery always produces the value NULL. It means that the problem is not limited to the case where the predicate is field=NULL. We must also consider cases where the right-hand side of the equality predicate is a subquery that evaluates to the constant value NULL.

select min(i) from t1 where i=(select null from t1 where i<2);
+--------+
| min(i) |
+--------+
|      2 |
+--------+
1 row in set (0.00 sec)

mysql> explain select min(i) from t1 where i=(select null from t1 where i<2);
+----+-------------+-------+-------+---------------+---------+---------+------+------+------------------------------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref  | rows | Extra                        |
+----+-------------+-------+-------+---------------+---------+---------+------+------+------------------------------+
|  1 | PRIMARY     | NULL  | NULL  | NULL          | NULL    | NULL    | NULL | NULL | Select tables optimized away |
|  2 | SUBQUERY    | t1    | index | PRIMARY       | PRIMARY | 4       | NULL |    2 | Using where; Using index     |
+----+-------------+-------+-------+---------------+---------+---------+------+------+------------------------------+
2 rows in set (0.00 sec)
[30 Oct 2009 12:25] 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/88737

3197 V Narayanan	2009-10-30
      Bug#47762 Incorrect result from MIN() when WHERE tests NOT NULL column for NULL
      
      The lookup of a NULL value in a column having a NOT NULL
      index was resulting in wrong query result. This was happening
      due to the return value, from the function that stored the
      field value, being ignored.
     @ sql/opt_sum.cc
        Bug#47762 Incorrect result from MIN() when WHERE tests NOT NULL column for NULL
        
        Ensure that the error (or not) from the store_val_in_field
        function is used while determining the usability of the index
        (determined by the return value of the matching_cond function).
     @ sql/sql_select.cc
        Bug#47762 Incorrect result from MIN() when WHERE tests NOT NULL column for NULL
        
        Ensure that all the three cases of no error, conversion
        and error are reported by the store_val_in_field function.
     @ sql/sql_select.h
        Bug#47762 Incorrect result from MIN() when WHERE tests NOT NULL column for NULL
        
        The store_val_in_field function returns a int instead of
        a bool so that it can handle all the three cases, namely,
        error, conversion and no error.
[11 Nov 2009 19:19] 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/90148

3197 V Narayanan	2009-11-12
      Bug#47762 Incorrect result from MIN() when WHERE tests NOT NULL column for NULL
      
      The lookup of a NULL value in a column having a NOT NULL
      index was resulting in wrong query result. This was
      happening because NULLs were not being handled properly
      in opt_sum_query.
     @ mysql-test/r/key.result
        Bug#47762 Incorrect result from MIN() when WHERE tests NOT NULL column for NULL
        
        Result file for test case.
     @ mysql-test/t/key.test
        Bug#47762 Incorrect result from MIN() when WHERE tests NOT NULL column for NULL
        
        Add test cases testing various cases of comparison with NULL.
     @ sql/opt_sum.cc
        Bug#47762 Incorrect result from MIN() when WHERE tests NOT NULL column for NULL
        
        Handle the, WHERE condition being NULL, case separately.
[17 Nov 2009 1: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/90621

3201 V Narayanan	2009-11-17
      Bug#47762 Incorrect result from MIN() when WHERE tests NOT NULL column for NULL
      
      The lookup of a NULL value in a column having a NOT NULL
      index was resulting in wrong query result. This was
      happening because NULLs were not being handled properly
      in opt_sum_query.
     @ mysql-test/r/key.result
        Bug#47762 Incorrect result from MIN() when WHERE tests NOT NULL column for NULL
        
        Result file for test case.
     @ mysql-test/t/key.test
        Bug#47762 Incorrect result from MIN() when WHERE tests NOT NULL column for NULL
        
        Add test cases testing various cases of comparison with NULL.
     @ sql/opt_sum.cc
        Bug#47762 Incorrect result from MIN() when WHERE tests NOT NULL column for NULL
        
        Handle the, WHERE condition being NULL, case separately.
[20 Nov 2009 4:02] 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/91029

3208 V Narayanan	2009-11-20
      Bug#47762 Incorrect result from MIN() when WHERE tests NOT NULL column for NULL
      
      The lookup of a NULL value in a column having a NOT NULL
      index was resulting in wrong query result. This was
      happening because NULLs were not being handled properly.
     @ mysql-test/r/min_null_cond.result
        Bug#47762 Incorrect result from MIN() when WHERE tests NOT NULL column for NULL
        
        Result file for test case.
     @ mysql-test/t/min_null_cond.test
        Bug#47762 Incorrect result from MIN() when WHERE tests NOT NULL column for NULL
        
        Add test cases testing various cases of comparison with NULL.
     @ sql/opt_sum.cc
        Bug#47762 Incorrect result from MIN() when WHERE tests NOT NULL column for NULL
        
        Handle the, WHERE condition being NULL, case separately.
[22 Nov 2009 11:07] 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/91238

3214 V Narayanan	2009-11-22
      Bug#47762 Incorrect result from MIN() when WHERE tests NOT NULL column for NULL
      
      The lookup of a NULL value in a column having a NOT NULL
      index was resulting in wrong query result. This was
      happening because NULLs were not being handled properly.
     @ mysql-test/r/min_null_cond.result
        Bug#47762 Incorrect result from MIN() when WHERE tests NOT NULL column for NULL
        
        Result file for test case.
     @ mysql-test/t/min_null_cond.test
        Bug#47762 Incorrect result from MIN() when WHERE tests NOT NULL column for NULL
        
        Add test cases testing various cases of comparison with NULL.
     @ sql/opt_sum.cc
        Bug#47762 Incorrect result from MIN() when WHERE tests NOT NULL column for NULL
        
        Handle the, WHERE condition being NULL, case separately.
[25 Nov 2009 7:35] Igor Babaev
Narayanan,

With your patch you still have a a problem:

mysql> select min(i) from t1 use index() where i=(select i from t1 where i < 0);
+--------+
| min(i) |
+--------+
|   NULL |
+--------+
1 row in set (0.09 sec)

mysql> select min(i) from t1 where i=(select i from t1 where i < 0);
+--------+
| min(i) |
+--------+
|      1 |
+--------+
1 row in set (2.39 sec)
[25 Nov 2009 8:09] Sergei Golubchik
Indeed, sorry for this. I guess even

  WHERE i=NULL+1

will do
[2 Feb 2010 9:09] 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/98903

3342 Martin Hansson	2010-02-02
      Bug#47762: Incorrect result from MIN() when WHERE tests NOT
      NULL column for NULL
      
      The optimization to read MIN() and MAX() values from an
      index does not properly handle comparisons with NULL
      values. Fixed by giving up the particular optimization step
      if there are comparisons with NULL values, as the result is
      NULL anyway.
     @ sql/field.h
        Bug#47762: Only commenting
     @ sql/opt_sum.cc
        Bug#47762: Fix, clarification of comments
[25 Feb 2010 10:43] 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/101428

3354 Martin Hansson	2010-02-25
      Bug#47762: Incorrect result from MIN() when WHERE tests NOT
      NULL column for NULL
      
      The optimization to read MIN() and MAX() values from an
      index did not properly handle comparisons with NULL
      values. Fixed by giving up the particular optimization step
      if there are non-NULL safe comparisons with NULL values, as 
      the result is NULL anyway.
     @ sql/field.h
        Bug#47762: Only commenting
[2 Mar 2010 12: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/102039
[12 Mar 2010 13:23] 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/103102

3354 Martin Hansson	2010-03-12
      Bug#47762: Incorrect result from MIN() when WHERE tests NOT
      NULL column for NULL
      
      The optimization to read MIN() and MAX() values from an
      index did not properly handle comparisons with NULL
      values. Fixed by giving up the particular optimization step
      if there are non-NULL safe comparisons with NULL values, as 
      the result is NULL anyway.
[12 Mar 2010 14:23] 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/103108

3354 Martin Hansson	2010-03-12
      Bug#47762: Incorrect result from MIN() when WHERE tests NOT
      NULL column for NULL
      
      The optimization to read MIN() and MAX() values from an
      index did not properly handle comparisons with NULL
      values. Fixed by giving up the particular optimization step
      if there are non-NULL safe comparisons with NULL values, as 
      the result is NULL anyway.
[15 Mar 2010 10: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/103206

3400 Martin Hansson	2010-03-15
      Bug#47762: Incorrect result from MIN() when WHERE tests NOT
      NULL column for NULL
      
      The optimization to read MIN() and MAX() values from an
      index did not properly handle comparisons with NULL
      values. Fixed by giving up the particular optimization step
      if there are non-NULL safe comparisons with NULL values, as 
      the result is NULL anyway.
[16 Mar 2010 14:51] 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/103471

3401 Martin Hansson	2010-03-16
      Bug#47762: Incorrect result from MIN() when WHERE tests NOT
      NULL column for NULL
      
      The optimization to read MIN() and MAX() values from an
      index did not properly handle comparisons with NULL
      values. Fixed by giving up the particular optimization step
      if there are non-NULL safe comparisons with NULL values, as 
      the result is NULL anyway.
      
      Also, Oracle copyright notice was added to all files.
[16 Mar 2010 15:54] 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/103476

3972 Martin Hansson	2010-03-16 [merge]
      Merge of fix for Bug#47762. A test in func_group now works correctly.
[16 Mar 2010 16:19] 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/103485

3975 Martin Hansson	2010-03-16 [merge]
      Merge of fix for Bug#47762. A test in func_group now works correctly.
[16 Mar 2010 16:21] 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/103487

3402 Martin Hansson	2010-03-16 [merge]
      Merge of fix for Bug#47762.
[18 Mar 2010 11:08] 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/103681

3980 Martin Hansson	2010-03-18
      Post-push fix to disable a subset of the test case for Bug#47762. 
      The bug exposed two bugs in 6.0. They were reported as follows.
      
      Bug no 52174: Sometimes wrong plan when reading a MAX value 
      from non-NULL index
      
      Bug no 52173: Reading NULL value from non-NULL index gives wrong 
      result in embedded server 
      
      Both bugs taken together affect a much smaller class of queries than #47762, 
      so the fix stays for now.
[18 Mar 2010 15: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/103713

3408 Martin Hansson	2010-03-18
      Post-push fix to disable a subset of the test case for Bug#47762.
      This has been back-ported from 6.0 as the problems proved to afflict 
      5.1 as well.
      The fix exposed two new bugs. They were reported as follows.
            
      Bug no 52174: Sometimes wrong plan when reading a MAX value 
      from non-NULL index
            
      Bug no 52173: Reading NULL value from non-NULL index gives wrong 
      result in embedded server 
            
      Both bugs taken together affect a much smaller class of queries than #47762, 
      so the fix stays for now.
[19 Mar 2010 8:23] 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/103763

3410 Martin Hansson	2010-03-19
      Post-push fix to disable a subset of the test case for Bug#47762.
      This has been back-ported from 6.0 as the problems proved to afflict 
      5.1 as well.
      The fix exposed two new bugs. They were reported as follows.
            
      Bug no 52174: Sometimes wrong plan when reading a MAX value 
      from non-NULL index
            
      Bug no 52173: Reading NULL value from non-NULL index gives wrong 
      result in embedded server 
            
      Both bugs taken together affect a much smaller class of queries than #47762, 
      so the fix stays for now.
[19 Mar 2010 8: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/103766

3982 Martin Hansson	2010-03-19 [merge]
      Merge of backport of a post-push fix for Bug#47762.
[26 Mar 2010 8:22] Bugs System
Pushed into 5.5.4-m3 (revid:alik@sun.com-20100326080914-2pz8ns984e0spu03) (version source revid:alexey.kopytov@sun.com-20100320202342-3oapaq7r0t6qhexq) (merge vers: 5.5.3-m2) (pib:16)
[26 Mar 2010 8:26] Bugs System
Pushed into mysql-next-mr (revid:alik@sun.com-20100326081116-m3v4l34yhr43mtsv) (version source revid:alik@sun.com-20100325072612-4sds00ix8ajo1e84) (pib:16)
[26 Mar 2010 8:30] Bugs System
Pushed into 6.0.14-alpha (revid:alik@sun.com-20100326081944-qja07qklw1p2w7jb) (version source revid:alik@sun.com-20100325073410-4t4i9gu2u1pge7xb) (merge vers: 6.0.14-alpha) (pib:16)
[6 Apr 2010 7:58] Bugs System
Pushed into 5.1.46 (revid:sergey.glukhov@sun.com-20100405111026-7kz1p8qlzglqgfmu) (version source revid:john.embretsen@sun.com-20100322090656-w4ixy7p67fb3vr29) (merge vers: 5.1.46) (pib:16)
[12 Apr 2010 22:52] Paul Dubois
Noted in 5.1.46, 5.5.5, 6.0.14 changelogs.

The optimization to read MIN() or MAX() values from an index did not
properly handle comparisons with NULL values. This could produce
incorrect results for MIN() or MAX()when the WHERE clause tested a
NOT NULL column for NULL.
[17 Jun 2010 11:49] Bugs System
Pushed into 5.1.47-ndb-7.0.16 (revid:martin.skold@mysql.com-20100617114014-bva0dy24yyd67697) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)
[17 Jun 2010 12:26] Bugs System
Pushed into 5.1.47-ndb-6.2.19 (revid:martin.skold@mysql.com-20100617115448-idrbic6gbki37h1c) (version source revid:martin.skold@mysql.com-20100609211156-tsac5qhw951miwtt) (merge vers: 5.1.46-ndb-6.2.19) (pib:16)
[17 Jun 2010 13:14] Bugs System
Pushed into 5.1.47-ndb-6.3.35 (revid:martin.skold@mysql.com-20100617114611-61aqbb52j752y116) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)
[17 Aug 2011 13:51] Jon Stephens
BUG#42114 is a duplicate of this bug.