MySQL Bugs: #52303: MIN() outputs 0 as the minimum year(4) corresponding to 0000

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

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.

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.

Thank you for the report.

Verified as described.

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.

This change is a bug fix.
See http://dev.mysql.com/doc/refman/5.5/en/year.html for more details

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)

Thank you for the feedback.

Problem is not repeatable with current development sources.