Bug #72098 select * from (subquery) get different result with subquery.
Submitted: 21 Mar 2014 20:47 Modified: 22 Mar 2014 6:45
Reporter: Ben Lin Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: DML Severity:S3 (Non-critical)
Version:5.5, 5.5.38 OS:Any
Assigned to:
Tags: isnull, subquery

[21 Mar 2014 20:47] Ben Lin
Description:
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'
Solution:
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.

How to repeat:
drop table if exists test;
create table test(
firstdate datetime default null,
lastdate datetime default null
);
insert into test(firstdate, lastdate) values('2014-03-08', '2012-03-08');
insert into test(firstdate, lastdate) values('2013-03-08', '2011-03-08');
select GREATEST( IFNULL(MAX(firstdate), 0), IFNULL(MAX(lastdate), 0)) from test;
-- expect: '2014-03-08 00:00:00'. got it.
select * from (select GREATEST( IFNULL(MAX(firstdate), 0), IFNULL(MAX(lastdate), 0)) from test ) t;
-- expect: '2014-03-08 00:00:00'. got '2014-0'
select * from (select GREATEST( IFNULL(MAX(firstdate), '0000-00-00 00:00:00'), IFNULL(MAX(lastdate), '0000-00-00 00:00:00')) from test) t;
-- expect: '2014-03-08 00:00:00'. got it.
[22 Mar 2014 6:45] Umesh Shastry
Hello Ben,

Thank you for the bug report and test case.
Verified as described.

Thanks,
Umesh