| 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: | |
| 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 |
[1 Oct 2009 14:29]
Jørgen Løland
[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]
MySQL Verification Team
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.
