| Bug #87332 | date_add/date_sub returns unexpected '0000-00-00' | ||
|---|---|---|---|
| Submitted: | 6 Aug 2017 23:23 | Modified: | 9 Aug 2017 19:28 |
| Reporter: | Toshiyuki Goto | Email Updates: | |
| Status: | Not a Bug | Impact on me: | |
| Category: | MySQL Server: Data Types | Severity: | S3 (Non-critical) |
| Version: | 5.7.19 | OS: | CentOS (7.3.1611) |
| Assigned to: | CPU Architecture: | Any | |
[7 Aug 2017 15:16]
MySQL Verification Team
Hi! I have got the same results as you have: d d+1 d-1 0000-12-30 0000-00-00 0000-00-00 0000-12-31 0001-01-01 0000-00-00 0001-01-01 0001-01-02 0000-00-00 0001-01-02 0001-01-03 0001-01-01 This is a bug, hence it is verified. I must point out that this is a minor bug.
[9 Aug 2017 19:28]
Roy Lyseng
I think this is not a bug. The manual describes the rules DATE values: MySQL permits you to store dates where the day or month and day are zero in a DATE or DATETIME column. This is useful for applications that need to store birthdates for which you may not know the exact date. In this case, you simply store the date as '2009-00-00' or '2009-01-00'. If you store dates such as these, you should not expect to get correct results for functions such as DATE_SUB() or DATE_ADD() that require complete dates. To disallow zero month or day parts in dates, enable the NO_ZERO_IN_DATE mode. Thus, semantics for addition or subtraction involving a DATE value with a zero component is undefined, and I do not think that it is possible to define any useful rules. I also wonder why we allow storing a year zero. The calendar specified by the SQL standard starts with the date 0001-01-01, and the ISO calendar does not have a year zero.
Description: When year becomes to '0000' with calculation result of date_add/date_sub, then it always returns '0000-00-00'. How to repeat: ``` set session sql_mode = 'STRICT_ALL_TABLES,NO_ZERO_DATE,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO'; create table t (d date); insert into t values ('0000-00-00'); /* ERROR 1292 (22007): Incorrect date value */ insert into t values ('0001-01-00'); /* ERROR 1292 (22007): Incorrect date value */ insert into t values ('0001-00-01'); /* ERROR 1292 (22007): Incorrect date value: */ insert into t values ('0000-12-30'); /* Query OK */ insert into t values ('0000-12-31'); /* Query OK */ insert into t values ('0001-01-01'); /* Query OK */ insert into t values ('0001-01-02'); /* Query OK */ ``` ``` select d, date_add(d, interval 1 day) as `d+1`, date_sub(d, interval 1 day) as `d-1` from t; ``` I expect the following results. ``` +------------+------------+------------+ | d | d+1 | d-1 | +------------+------------+------------+ | 0000-12-30 | 0000-12-31 | 0000-12-29 | | 0000-12-31 | 0001-01-01 | 0000-12-30 | | 0001-01-01 | 0001-01-02 | 0000-12-31 | | 0001-01-02 | 0001-01-03 | 0001-01-01 | +------------+------------+------------+ ``` But, actually the following results. ``` +------------+------------+------------+ | d | d+1 | d-1 | +------------+------------+------------+ | 0000-12-30 | 0000-00-00 | 0000-00-00 | | 0000-12-31 | 0001-01-01 | 0000-00-00 | | 0001-01-01 | 0001-01-02 | 0000-00-00 | | 0001-01-02 | 0001-01-03 | 0001-01-01 | +------------+------------+------------+ ```