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

[18 Mar 5:03] Guo Wenxuan
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)
[18 Mar 7:00] MySQL Verification Team
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
[18 Mar 7:41] Frederic Descamps
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,
[18 Mar 7:45] Guo Wenxuan
Is there any risk in this?
[18 Mar 7:48] Frederic Descamps
Risk? no other than have the wrong result ;)