MySQL Bugs: #103028: year() return null

Bug #103028	year() return null
Submitted:	18 Mar 5:03	Modified:	18 Mar 7:45
Reporter:	Guo Wenxuan	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S2 (Serious)
Version:	8.0.23	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
correct:
mysql> select year(col1) from ta1;
+------------+
| year(col1) |
+------------+
| 2021       |
+------------+
incorrect:
mysql> select year(col1) from ta1;
+------------+
| year(col1) |
+------------+
| NULL       |
+------------+

How to repeat:
mysql> create table ta1(col1 year);
Query OK, 0 rows affected (0.09 sec)

mysql> insert into ta1 values(2021);
Query OK, 1 row affected (0.07 sec)

Hello xuan wen,

Thank you for the report and test case.
Imho this is not a bug because YEAR() function expecting a date argument and you are providing just incomplete date hence it is returning NULL. 

Quoting from manual - "Some date functions can be used with “zero” dates or incomplete dates such as '2001-11-00', whereas others cannot. Functions that extract parts of dates typically work with incomplete dates and thus can return 0 when you might otherwise expect a nonzero value."

mysql> SELECT DAYOFMONTH('2001-11-00'), MONTH('2005-00-00');
        -> 0, 0

Other functions expect complete dates and return NULL for incomplete dates. These include functions that perform date arithmetic or that map parts of dates to names. For example:

mysql> SELECT DATE_ADD('2006-05-00',INTERVAL 1 DAY);
        -> NULL
mysql> SELECT DAYNAME('2006-05-00');
        -> NULL

Please see https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html#function_year

Same can be observed in 5.6/5.7.

-

mysql> SELECT YEAR('2021');
+--------------+
| YEAR('2021') |
+--------------+
|         NULL |
+--------------+
1 row in set, 1 warning (0.00 sec)

mysql> SELECT YEAR(2021);
+------------+
| YEAR(2021) |
+------------+
|       NULL |
+------------+
1 row in set, 1 warning (0.00 sec)

mysql> SELECT YEAR('2021-00-00');
+--------------------+
| YEAR('2021-00-00') |
+--------------------+
|               2021 |
+--------------------+
1 row in set (0.00 sec)

regards,
Umesh

Hi Xuan, 

Additionally to what my colleague answered, I would add that when you perform the select, MySQL let you know that :

mysql> show warnings;
+---------+------+----------------------------------+
| Level   | Code | Message                          |
+---------+------+----------------------------------+
| Warning | 1292 | Incorrect datetime value: '2021' |
+---------+------+----------------------------------+
1 row in set (0.00 sec)

And finally, the logic doesn't seem correct to me, you define `col1` to be YEAR data type, and when you select you perform again YEAR() function on it.

I would suggest that or you store it as YEAR and then SELECT it as it is, or you store it as DATE/DATETIME and use YEAR() in the SELECT but not mix both.

Regards,

Is there any risk in this?

Risk? no other than have the wrong result ;)