MySQL Bugs: #82841: year function with invalid value should have failed

Bug #82841	year function with invalid value should have failed
Submitted:	2 Sep 2016 7:05	Modified:	2 Sep 2016 13:44
Reporter:	帅 Bang	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	5.6, 5.7	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
When referring with doc of mysql, we are noticed that MySQL displays YEAR values in YYYY format, with a range of 1901 to 2155, or 0000  (http://dev.mysql.com/doc/refman/5.7/en/year.html)

So, when we insert 1024 to a column of type year, it will fail 

mysql> set sql_mode = 'STRICT_ALL_TABLES';
Query OK, 0 rows affected (0.00 sec)

mysql> create table sb(a year);
Query OK, 0 rows affected (0.00 sec)

mysql> insert into sb values(1024);
ERROR 1264 (22003): Out of range value for column 'a' at row 1

Year, this is ok. it is very good so far. But ,when we type this:

mysql> select year("1024-01-01 11:11:11");
+-----------------------------+
| year("1024-01-01 11:11:11") |
+-----------------------------+
|                        1024 |
+-----------------------------+
1 row in set (0.00 sec)

It returns 1024 which is an invalid value for year.

How to repeat:
select year("1024-01-01 11:11:11");

Suggested fix:
returns NULL with a warning rather than 1024.

In my understanding the "range of 1901 to 2155" applies to the YEAR datatype, but not the year() function (what returns a string). 

-- Peter
-- not a MySQL/Oracle person

Hi!

Thank you for your bug report. However, Peter Laursen is quite correct in his analysis.

Indeed, the "range of 1901 to 2155" applies to the YEAR datatype, but not to the year() function, which returns a string.

It is explained in the documentation.