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:
None 
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
Description:
Select is returning incorrect value for min (year). 

insert_year test from engine dependent suite is failing because in difference in result file.

We are getting "1901" as min value instead of "0000".

How to repeat:
perl mysql-test-run.pl --suite=engines/iuds insert_year

or

Get the latest source from mysql-5.1-bugteam, build it, and run the following statements
:
mysql> use test;
Database changed
mysql> CREATE TABLE t1(c1 YEAR(4));
Query OK, 0 rows affected (0.18 sec)

mysql> INSERT INTO t1 VALUES (1901),(2155),(0000);
Query OK, 3 rows affected (0.04 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM t1;
+------+
| c1   |
+------+
| 1901 |
| 2155 |
| 0000 |
+------+
3 rows in set (0.02 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.00 sec)
[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.