A bug, definitely a bug, in MySQL

First of all, I have a very complicate query to insert from select group by on duplicate update. So I need to add "select * from () " from the group by subquery, then this bug is shown: The subquery result is different from "select * from (subquery)"!

The subquery is also very complicate, but I can simplify it as following:

create table test(
firstdate datetime default null,
lastdate datetime default null
);

I need to find the last date of this table, but either of the 2 fields can be null. So the subquery I came up with is:

select GREATEST( IFNULL(MAX(firstdate), 0), IFNULL(MAX(lastdate), 0)) from test;

That works perfectly. You can insert some dates for testing:

insert into test(firstdate) values('2014-03-08');
insert into test(firstdate, lastdate) values(' 2012-02-01', '2013-03-08');

Then the query will have expected result:

'2014-03-08 00:00:00'

Here is the wired part: Adding select * from () will have different result!

select * from (
        select GREATEST( IFNULL(MAX(firstdate), 0), IFNULL(MAX(lastdate), 0)) from test) t

'2014-0'

Work-around:

Change the "0" in subquery to a datetime string such as '0000-00-00 00:00:00':

select GREATEST( IFNULL(MAX(firstdate), '0000-00-00 00:00:00'), IFNULL(MAX(lastdate), '0000-00-00 00:00:00')) from test;

Then select * from (subquery) will have the expected result.

Analysis:

Looks like that because of the "0" value, the IFNULL changed the result column somehow into a string? Even if all the firstdate and lastdate are filled (there is no null value in the table), the result is still the same.

But if the result column property is modified, why the subquery itself works fine?

MySQL 5.5.

Reported as  Bug #72098