Bug #103028 | year() return null | ||
---|---|---|---|
Submitted: | 18 Mar 2021 5:03 | Modified: | 18 Mar 2021 7:45 |
Reporter: | Guo Wenxuan | Email Updates: | |
Status: | Not a Bug | Impact on me: | |
Category: | MySQL Server: DML | Severity: | S2 (Serious) |
Version: | 8.0.23 | OS: | Any |
Assigned to: | CPU Architecture: | Any |
[18 Mar 2021 5:03]
Guo Wenxuan
[18 Mar 2021 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 2021 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 2021 7:45]
Guo Wenxuan
Is there any risk in this?
[18 Mar 2021 7:48]
Frederic Descamps
Risk? no other than have the wrong result ;)