Bug #59211 | Select Returns Different Value for min(year) Function | ||
---|---|---|---|
Submitted: | 29 Dec 2010 11:57 | Modified: | 9 Feb 2011 5:01 |
Reporter: | Vinay Fisrekar | Email Updates: | |
Status: | Closed | Impact on me: | |
Category: | MySQL Server: DML | Severity: | S3 (Non-critical) |
Version: | mysql-5.1,5.5, 5.6.1, 5.6.2 | OS: | Any |
Assigned to: | Øystein Grøvlen | CPU Architecture: | Any |
Tags: | regression |
[29 Dec 2010 11:57]
Vinay Fisrekar
[29 Dec 2010 13:42]
John Embretsen
Similar to http://bugs.mysql.com/bug.php?id=49771
[29 Dec 2010 15:10]
Valeriy Kravchuk
Verified just as described with current mysql-trunk: macbook-pro:trunk 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 3 Server version: 5.6.2-m5-debug Source distribution Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> drop table t1; Query OK, 0 rows affected (0.01 sec) mysql> CREATE TABLE t1(c1 YEAR(4)); Query OK, 0 rows affected (0.06 sec) mysql> INSERT INTO t1 VALUES (1901),(2155),(0000); Query OK, 3 rows affected (0.00 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql> SELECT * FROM t1; +------+ | c1 | +------+ | 1901 | | 2155 | | 0000 | +------+ 3 rows in set (0.01 sec) mysql> SELECT COUNT(*) AS total_rows, MIN(c1) AS min_value, MAX(c1) FROM t1; +------------+-----------+---------+ | total_rows | min_value | MAX(c1) | +------------+-----------+---------+ | 3 | 1901 | 2155 | +------------+-----------+---------+ 1 row in set (0.03 sec)
[6 Jan 2011 15:20]
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/128088 3536 oystein.grovlen@sun.com 2011-01-06 Bug#59211: Select Returns Different Value for min(year) Function get_year_value() contains code to convert 2-digits year to 4-digits. The fix for Bug#49910 added a check on the size of the underlying field so that this conversion is not done for YEAR(4) values. (Since otherwise one would convert invalid YEAR(4) values to valid ones.) The existing check does not work when Item_cache is used, since it is not detected when the cache is based on a Field. The reported change in behavior is due to Bug#58030 which added extra cached items in min/max computations. The elegant solution would be to implement Item_cache::real_item() to return the underlying Item. However, some side effects are observed (change in explain output) that indicatse that such a change is not straight-forward, and definitely not appropriate for an MRU. Instead, a Item_cache::field() method has been added in order to get access to the underlying field. (This field() method eliminates the need for Item_cache::eq_def() used in test_if_ref(), but in order to limit the scope of this fix, that code has been left as is.) @ mysql-test/r/type_year.result Added test case for Bug#59211 @ mysql-test/t/type_year.test Added test case for Bug#59211 @ sql/item.h Added function Item_cache::field() to get access to the underlying Field of a cached field Value. @ sql/item_cmpfunc.cc Also check underlying fields of Item_cache, not just Item_Field, when checking whether the value is of type YEAR(4) or not.
[7 Jan 2011 5:14]
MySQL Verification Team
Oystein, I wonder, does this fix bug #59343 also ?
[7 Jan 2011 11:49]
Øystein Grøvlen
Shane, I have checked, and my patch does not fix Bug#59343.
[12 Jan 2011 17:46]
Paul DuBois
Noted in 5.1.55, 5.5.9 changelogs. MIN(year_col) could return an incorrect result in some cases.
[26 Jan 2011 7:16]
Vinay Fisrekar
This bug is seen across all version and branches. We expected to see in some cases as per comment. Failures: http://pb2.norway.sun.com/web.py?template=mysql_show_test_failure&test_failure_id=&test_ou... Please see if can re-open the bug.
[26 Jan 2011 8:10]
Øystein Grøvlen
The bug fix was pushed to security trees. It seems it has not reached the main trees yet. I will investigate why this has not been done yet.
[8 Feb 2011 11:57]
Bugs System
Pushed into mysql-5.1 5.1.56 (revid:karen.langford@oracle.com-20110208115233-t04e8k0pwhwtbc1u) (version source revid:karen.langford@oracle.com-20110208115233-t04e8k0pwhwtbc1u) (merge vers: 5.1.56) (pib:24)
[8 Feb 2011 14:09]
Bugs System
Pushed into mysql-5.5 5.5.10 (revid:jonathan.perkin@oracle.com-20110208140736-1173xnoipufbhowh) (version source revid:jonathan.perkin@oracle.com-20110208135903-jhzy6wq16b2fx7pg) (merge vers: 5.5.10) (pib:24)
[8 Feb 2011 16:45]
Bugs System
Pushed into mysql-trunk 5.6.2 (revid:georgi.kodinov@oracle.com-20110208155412-tfy4l5hqxi0g7o41) (version source revid:georgi.kodinov@oracle.com-20110208154951-gzqgr74u4bndswi8) (merge vers: 5.6.2) (pib:24)
[8 Mar 2011 0:04]
Paul DuBois
Noted in 5.6.2 changelog.