Any

Reproducible on all of 5.5-10.5 and MySQL 5.6, 8.0 (didn't try other MySQL versions).

This is not a bug. This expression:

IFNULL(DATE(NULL),0)

returns a string '0'.

Then the string '0' is converted to DATE, and gives NULL.

MariaDB [test]> SELECT IFNULL(DATE(NULL),0);

Field   1:  `IFNULL(DATE(NULL),0)`

Catalog:    `def`

Database:   ``

Table:      ``

Org_table:  ``

Type:       VAR_STRING

Collation:  utf8_general_ci (33)

Length:     30

Max_length: 1

Decimals:   39

Flags:      NOT_NULL 

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

| IFNULL(DATE(NULL),0) |

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

| 0                    |

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

1 row in set (0.001 sec)

MariaDB [test]> SELECT DATE('0');

Field   1:  `DATE('0')`

Catalog:    `def`

Database:   ``

Table:      ``

Org_table:  ``

Type:       DATE

Collation:  binary (63)

Length:     10

Max_length: 0

Decimals:   0

Flags:      BINARY 

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

| DATE('0') |

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

| NULL      |

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

1 row in set, 1 warning (0.001 sec)

I know that DATE('0') is NULL, but there are no strings in examples provided.

Why IFNULL(DATE(NULL),0) is '0' string?
Second parameter in IFNULL is INT type.

Historically all hybrid functions in MariaDB (and in MySQL), like:

• IFNULL(date_arg,int_arg)
• COALESCE(date_arg,int_arg)
• CASE .. THEN date_arg .. THEN int_arg

resolve their return data type as VARCHAR.

VARCHAR is the common datatype that can store both DATE and INT without data loss.

The same happens with UNION:

SELECT date_arg ... UNION SELECT int_arg ...

If you need a different behaviour instead of this style automatic data type conversion, please use the explicit CAST operator.

Thank you! That makes everything clear.

"IFNULL(?, 0)" changing to "IFNULL(?, DATE(0))" and it works as expected:
DATE(IFNULL(DATE(NULL), DATE(0)))