Bug #52303 MIN() outputs 0 as the minimum year(4) corresponding to 0000
Submitted: 23 Mar 2010 13:52 Modified: 13 Apr 2010 5:29
Reporter: Nirbhay Choubey Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S3 (Non-critical)
Version:5.1.46, 5.5.1-m2, 5.5.3-m3 OS:Any
Assigned to: CPU Architecture:Any
Tags: mysql-5.1-bugteam, regression
Triage: Needs Triage: D2 (Serious)

[23 Mar 2010 13:52] Nirbhay Choubey
Description:
A MySQL build from latest source from mysql-5.1-bugteam shows difference in the output of MIN() function, which now considers 0000 as minimum YEAR(4).

CREATE TABLE t1(c1 YEAR(4));
INSERT INTO t1 VALUES (1901),(2155),(0000);
SELECT COUNT(*) AS total_rows, MIN(c1) AS min_value, MAX(c1) FROM t1;
+------------+-----------+---------+
| total_rows | min_value | MAX(c1) |
+------------+-----------+---------+
|          3 |         0 |    2155 |
+------------+-----------+---------+
1 row in set (0.01 sec)

While for 5.1.45,

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)

0000 represents bad/invalid year(4), so IMO, it should not be taken into account
by MIN() to get the minimum year. 
OR, Is MIN() expected to behave this way?

How to repeat:
Get the latest source from mysql-5.1-bugteam, build it, and run the following statements :

CREATE TABLE t1(c1 YEAR(4));
INSERT INTO t1 VALUES (1901),(2155),(0000);
SELECT COUNT(*) AS total_rows, MIN(c1) AS min_value, MAX(c1) FROM t1;

Suggested fix:
Looks related to the fix pushed for Bug #49910.
[23 Mar 2010 14:37] Valeriy Kravchuk
On recent mysql-5.1 tree from bzr I still get correct results:

openxs@suse:/home2/openxs/dbs/5.1> 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.1.46-debug Source distribution

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.01 sec)

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

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.02 sec)

So it looks like a mysql-5.1-bugteam specific bug for now.
[23 Mar 2010 14:56] Sveta Smirnova
Thank you for the report.

Verified as described.
[29 Mar 2010 16:52] Sinisa Milivojevic
YEAR is a column type in MySQL, which accepts 0 as a valid value. In some situations it is handled as year 2000 and in other as 0. So, 0 is a valid value for domain YEAR, as explained in our manual.
[2 Apr 2010 16:42] Omer Barnir
This change is a bug fix.
See http://dev.mysql.com/doc/refman/5.5/en/year.html for more details
[12 Apr 2010 13:27] Nirbhay Choubey
5.5.1-m2, 5.5.3-m3 servers gives the following output when subjected
to the above set of statements :

mysql> SELECT COUNT(*) AS total_rows, MIN(c1) AS min_value, MAX(c1) FROM t1;
+------------+-----------+---------+
| total_rows | min_value | MAX(c1) |
+------------+-----------+---------+
|          3 |      1901 |    2155 |
+------------+-----------+---------+

But, the min_value should show '0' (based on the above discussion)
[13 Apr 2010 5:29] Sveta Smirnova
Thank you for the feedback.

Problem is not repeatable with current development sources.