[#MDEV-16926] CAST(COALESCE(year_field)) returns wrong value

This script:

CREATE OR REPLACE TABLE t1 (a YEAR);

INSERT INTO t1 VALUES (2000);

SELECT CAST(a AS DATE), CAST(COALESCE(a) AS DATE) FROM t1;

SHOW WARNINGS;

produces the following output:

+-----------------+---------------------------+

| CAST(a AS DATE) | CAST(COALESCE(a) AS DATE) |

+-----------------+---------------------------+

| 2000-00-00      | NULL                      |

+-----------------+---------------------------+

1 row in set, 1 warning (0.00 sec)

+---------+------+----------------------------------+

| Level   | Code | Message                          |

+---------+------+----------------------------------+

| Warning | 1292 | Incorrect datetime value: '2000' |

+---------+------+----------------------------------+

NULL in the second column is wrong. Both columns should return the same value, without warnings.

This happens because Type_handler_year has a special implementation in Item_get_date():

bool Type_handler_year::Item_get_date(Item *item, MYSQL_TIME *ltime,

                                             ulonglong fuzzydate) const

  return item->get_date_from_year(ltime, fuzzydate);

but does not have a similar implementation for Item_func_hybrid_field_type_get_date().

[MDEV-16926] CAST(COALESCE(year_field)) returns wrong value Created: 2018-08-09 Updated: 2018-11-03 Resolved: 2018-08-09
Status:	Closed
Project:	MariaDB Server
Component/s:	Data types, Temporal Types
Affects Version/s:	10.0, 10.1, 10.2, 10.3, 10.4
Fix Version/s:	10.4.0