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?

For what it's worth, I see the same discrepancy between 5.1.73 and MariaDB 10.0.21. — Michael Berkowski, Jan 16 '16 at 20:54
With the MySQL CLI client instead of SQLFiddle, I see that `STR_TO_DATE()` produces a weird result when no day value is given: `SELECT STR_TO_DATE('2015-03')` produces `2015-03-00`. I cannot add a date. The fact that it produces "day 00" in both older and newer versions seems wrong to begin with, but then the fact that you cannot add an interval to that invalid date without receiving NULL in later versions seems correct given the weird behavior. The older version allows me to add 1 day to "day 00", as `SELECT DATE(STR_TO_DATE('2015-03', '%Y-%m')) + INTERVAL 1 DAY;` resulting in `2015-03-01` — Michael Berkowski, Jan 16 '16 at 21:06
^^... and that is _not_ the result I would expect. I would expect it to start from `2015-03-01` and add 1 day resulting in `2015-03-02`. — Michael Berkowski, Jan 16 '16 at 21:08
I have found some blog posts referring to the supported 00 day or 00 month value but no official docs other than for the well known zero date `0000-00-00 00:00:00`. — Michael Berkowski, Jan 16 '16 at 21:13
@MichaelBerkowski: "*The [`NO_ZERO_IN_DATE`](https://dev.mysql.com/doc/en/sql-mode.html#sqlmode_no_zero_in_date) mode affects whether the server permits dates in which the year part is nonzero but the month or day part is 0.*" — eggyal, Jan 16 '16 at 21:20
@MichaelBerkowski: Also, the documentation for [`STR_TO_DATE()`](https://dev.mysql.com/doc/en/date-and-time-functions.html#function_str-to-date) itself states (my emphasis) "*Range checking on the parts of date values is as described in Section 11.3.1, “The DATE, DATETIME, and TIMESTAMP Types”. This means, for example, that “zero” dates or **dates with part values of 0 are permitted** unless the SQL mode is set to disallow such values.*". Of course, this isn't quite the same as dates with absent part values, but nevertheless is again official acknowledgement of 0 being valid in date parts. — eggyal, Jan 16 '16 at 21:24

score 2 · Accepted Answer · answered Jan 16 '16 at 21:59

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.

Incidentally, the change was reverted in the 5.6 line for `LAST_DAY()` only, at [5.6.5](https://dev.mysql.com/doc/relnotes/mysql/5.6/en/news-5-6-5.html). — eggyal, Jan 16 '16 at 22:30
+1 Thanks @eggyal! This is exactly what I was looking for. Now I can prove the problem was not caused by my code. — Nice Guy IT, Jan 17 '16 at 11:58

STR_TO_DATE discrepancy between MySQL 5.5 and MySQL 5.6

1 Answers1