STR_TO_DATE discrepancy between MySQL 5.5 and MySQL 5.6

Question

This SQL returns a DATE in MySQL 5.5 but NULL in MySQL 5.6. Why?

select date(STR_TO_DATE('2015-01', '%Y-%m')) + INTERVAL 1 DAY;

Here's a SQL Fiddle for MySQL 5.5 and 5.6. STR_TO_DATE returns a date in both instances. Converting the result using DATE works. The problem comes when trying to add an INTERVAL. It doesn't matter if I add an INTERVAL to STR_TO_DATE(...) or DATE(STR_TO_DATE(...)), the result is the same. However, removing the STR_TO_DATE makes it work.

select
    STR_TO_DATE('2015-01', '%Y-%m') as same_a1,
    STR_TO_DATE('2015-01-01', '%Y-%m') as same_a2,
    STR_TO_DATE('2015-01', '%Y-%m-%d') as same_a3,
    STR_TO_DATE('2015-01-01', '%Y-%m-%d') as same_a4,
    date(STR_TO_DATE('2015-01', '%Y-%m')) as same_b1,
    date(STR_TO_DATE('2015-01-01', '%Y-%m')) as same_b2,
    date(STR_TO_DATE('2015-01', '%Y-%m-%d')) as same_b3,
    date(STR_TO_DATE('2015-01-01', '%Y-%m-%d')) as same_b4,
    STR_TO_DATE('2015-01', '%Y-%m') + INTERVAL 1 DAY as same_c1,
    STR_TO_DATE('2015-01-01', '%Y-%m') + INTERVAL 1 DAY as same_c2,
    STR_TO_DATE('2015-01', '%Y-%m-%d') + INTERVAL 1 DAY as same_c3,
    STR_TO_DATE('2015-01-01', '%Y-%m-%d') + INTERVAL 1 DAY as same_c4,
    date(STR_TO_DATE('2015-01', '%Y-%m')) + INTERVAL 1 DAY as different_d1,
    date(STR_TO_DATE('2015-01-01', '%Y-%m')) + INTERVAL 1 DAY as different_d2,
    date(STR_TO_DATE('2015-01', '%Y-%m-%d')) + INTERVAL 1 DAY as different_d3,
    date(STR_TO_DATE('2015-01-01', '%Y-%m-%d')) + INTERVAL 1 DAY as same_d4,
    date('2015-01') + INTERVAL 1 DAY as same_e1,
    date('2015-01-01') + INTERVAL 1 DAY as same_e2
;

I searched the release notes but couldn't find anything. What is going on? Is this a known change? A bug?

Answer 1

It's a known change, that was unwittingly introduced into versions 5.1.59, 5.5.16 and 5.6.3 (but was then rolled back in versions 5.1.62 and 5.5.21; it was retained in the 5.6 line):

Incompatible Change: Handling of a date-related assertion was modified.

However, a consequence of this change is that several functions become more strict when passed a DATE() function value as their argument and reject incomplete dates with a day part of zero. These functions are affected: CONVERT_TZ(), DATE_ADD(), DATE_SUB(), DAYOFYEAR(), LAST_DAY(), TIMESTAMPDIFF(), TO_DAYS(), TO_SECONDS(), WEEK(), WEEKDAY(), WEEKOFYEAR(), YEARWEEK(). Because this changes date-handling behavior in General Availability-status series (MySQL 5.1 and 5.5), it was reverted in 5.1.62 and 5.5.21. The change is retained in MySQL 5.6.

References: See also Bug #13458237.

You are affected because the + INTERVAL notation is just syntactic sugar around the DATE_ADD() function.