DATETIME's behaviour is different from mysql. #11329

wshwsh12 · 2019-07-19T01:51:16Z

Bug Report

Please answer these questions before submitting your issue. Thanks!

What did you do?
If possible, provide a recipe for reproducing the error.

select DATE_ADD('1000-01-01 00:00:00',INTERVAL -2 HOUR);
select DATE_ADD('1000-01-01 00:00:00',INTERVAL -200 HOUR);
select DATE_ADD('0001-01-01 00:00:00',INTERVAL -2 HOUR);
select DATE_ADD('0001-01-01 00:00:00',INTERVAL -25 HOUR);
select DATE_ADD('0001-01-01 00:00:00',INTERVAL -8784 HOUR);
select DATE_ADD('0001-01-01 00:00:00',INTERVAL -8785 HOUR);

What did you expect to see?

mysql> select DATE_ADD('1000-01-01 00:00:00',INTERVAL -2 HOUR);
+--------------------------------------------------+
| DATE_ADD('1000-01-01 00:00:00',INTERVAL -2 HOUR) |
+--------------------------------------------------+
| 0999-12-31 22:00:00                              |
+--------------------------------------------------+
1 row in set (0.00 sec)

mysql> select DATE_ADD('1000-01-01 00:00:00',INTERVAL -200 HOUR);
+----------------------------------------------------+
| DATE_ADD('1000-01-01 00:00:00',INTERVAL -200 HOUR) |
+----------------------------------------------------+
| 0999-12-23 16:00:00                                |
+----------------------------------------------------+
1 row in set (0.00 sec)

mysql> select DATE_ADD('0001-01-01 00:00:00',INTERVAL -2 HOUR);
+--------------------------------------------------+
| DATE_ADD('0001-01-01 00:00:00',INTERVAL -2 HOUR) |
+--------------------------------------------------+
| 0000-00-00 22:00:00                              |
+--------------------------------------------------+
1 row in set (0.00 sec)

mysql> select DATE_ADD('0001-01-01 00:00:00',INTERVAL -25 HOUR);
+---------------------------------------------------+
| DATE_ADD('0001-01-01 00:00:00',INTERVAL -25 HOUR) |
+---------------------------------------------------+
| 0000-00-00 23:00:00                               |
+---------------------------------------------------+
1 row in set (0.00 sec)

mysql> select DATE_ADD('0001-01-01 00:00:00',INTERVAL -8784 HOUR);
+-----------------------------------------------------+
| DATE_ADD('0001-01-01 00:00:00',INTERVAL -8784 HOUR) |
+-----------------------------------------------------+
| 0000-00-00 00:00:00                                 |
+-----------------------------------------------------+
1 row in set (0.00 sec)

mysql> select DATE_ADD('0001-01-01 00:00:00',INTERVAL -8785 HOUR);
+-----------------------------------------------------+
| DATE_ADD('0001-01-01 00:00:00',INTERVAL -8785 HOUR) |
+-----------------------------------------------------+
| NULL                                                |
+-----------------------------------------------------+
1 row in set, 1 warning (0.00 sec)

What did you see instead?

mysql> select DATE_ADD('1000-01-01 00:00:00',INTERVAL -2 HOUR);
+--------------------------------------------------+
| DATE_ADD('1000-01-01 00:00:00',INTERVAL -2 HOUR) |
+--------------------------------------------------+
| NULL                                             |
+--------------------------------------------------+
1 row in set, 1 warning (0.00 sec)

mysql> select DATE_ADD('1000-01-01 00:00:00',INTERVAL -200 HOUR);
+----------------------------------------------------+
| DATE_ADD('1000-01-01 00:00:00',INTERVAL -200 HOUR) |
+----------------------------------------------------+
| NULL                                               |
+----------------------------------------------------+
1 row in set, 1 warning (0.00 sec)

mysql> select DATE_ADD('0001-01-01 00:00:00',INTERVAL -2 HOUR);
+--------------------------------------------------+
| DATE_ADD('0001-01-01 00:00:00',INTERVAL -2 HOUR) |
+--------------------------------------------------+
| NULL                                             |
+--------------------------------------------------+
1 row in set, 1 warning (0.00 sec)

mysql> select DATE_ADD('0001-01-01 00:00:00',INTERVAL -25 HOUR);
+---------------------------------------------------+
| DATE_ADD('0001-01-01 00:00:00',INTERVAL -25 HOUR) |
+---------------------------------------------------+
| NULL                                              |
+---------------------------------------------------+
1 row in set, 1 warning (0.00 sec)

mysql> select DATE_ADD('0001-01-01 00:00:00',INTERVAL -8784 HOUR);
+-----------------------------------------------------+
| DATE_ADD('0001-01-01 00:00:00',INTERVAL -8784 HOUR) |
+-----------------------------------------------------+
| NULL                                                |
+-----------------------------------------------------+
1 row in set, 1 warning (0.00 sec)

mysql> select DATE_ADD('0001-01-01 00:00:00',INTERVAL -8785 HOUR);
+-----------------------------------------------------+
| DATE_ADD('0001-01-01 00:00:00',INTERVAL -8785 HOUR) |
+-----------------------------------------------------+
| NULL                                                |
+-----------------------------------------------------+
1 row in set, 1 warning (0.00 sec)

What version of TiDB are you using (tidb-server -V or run select tidb_version(); on TiDB)?

| Release Version: v3.0.0-rc.1-356-g5aef053c1
Git Commit Hash: 5aef053c163524aa167e6bb4ddabd0671a75e712
Git Branch: master
UTC Build Time: 2019-07-19 01:42:00
GoVersion: go version go1.12.6 darwin/amd64
Race Enabled: false
TiKV Min Version: 2.1.0-alpha.1-ff3dd160846b7d1aed9079c389fc188f7f5ea13e
Check Table Before Drop: false |

The text was updated successfully, but these errors were encountered:

ghost · 2020-07-13T19:24:10Z

Confirmed that I can still reproduce this against master:

select DATE_ADD('1000-01-01 00:00:00',INTERVAL -2 HOUR);
select DATE_ADD('1000-01-01 00:00:00',INTERVAL -200 HOUR);
select DATE_ADD('0001-01-01 00:00:00',INTERVAL -2 HOUR);
select DATE_ADD('0001-01-01 00:00:00',INTERVAL -25 HOUR);
select DATE_ADD('0001-01-01 00:00:00',INTERVAL -8784 HOUR);
select DATE_ADD('0001-01-01 00:00:00',INTERVAL -8785 HOUR);
select tidb_version()\G

..

mysql> select DATE_ADD('1000-01-01 00:00:00',INTERVAL -2 HOUR);
+--------------------------------------------------+
| DATE_ADD('1000-01-01 00:00:00',INTERVAL -2 HOUR) |
+--------------------------------------------------+
| NULL                                             |
+--------------------------------------------------+
1 row in set, 1 warning (0.00 sec)

mysql> select DATE_ADD('1000-01-01 00:00:00',INTERVAL -200 HOUR);
+----------------------------------------------------+
| DATE_ADD('1000-01-01 00:00:00',INTERVAL -200 HOUR) |
+----------------------------------------------------+
| NULL                                               |
+----------------------------------------------------+
1 row in set, 1 warning (0.00 sec)

mysql> select DATE_ADD('0001-01-01 00:00:00',INTERVAL -2 HOUR);
+--------------------------------------------------+
| DATE_ADD('0001-01-01 00:00:00',INTERVAL -2 HOUR) |
+--------------------------------------------------+
| NULL                                             |
+--------------------------------------------------+
1 row in set, 1 warning (0.00 sec)

mysql> select DATE_ADD('0001-01-01 00:00:00',INTERVAL -25 HOUR);
+---------------------------------------------------+
| DATE_ADD('0001-01-01 00:00:00',INTERVAL -25 HOUR) |
+---------------------------------------------------+
| NULL                                              |
+---------------------------------------------------+
1 row in set, 1 warning (0.00 sec)

mysql> select DATE_ADD('0001-01-01 00:00:00',INTERVAL -8784 HOUR);
+-----------------------------------------------------+
| DATE_ADD('0001-01-01 00:00:00',INTERVAL -8784 HOUR) |
+-----------------------------------------------------+
| NULL                                                |
+-----------------------------------------------------+
1 row in set, 1 warning (0.01 sec)

mysql> select DATE_ADD('0001-01-01 00:00:00',INTERVAL -8785 HOUR);
+-----------------------------------------------------+
| DATE_ADD('0001-01-01 00:00:00',INTERVAL -8785 HOUR) |
+-----------------------------------------------------+
| NULL                                                |
+-----------------------------------------------------+
1 row in set, 1 warning (0.00 sec)

mysql> select tidb_version()\G
*************************** 1. row ***************************
tidb_version(): Release Version: v4.0.0-beta.2-750-g8a661044c
Edition: Community
Git Commit Hash: 8a661044cedf8daad1de4fbf79a390962b6f6c3b
Git Branch: master
UTC Build Time: 2020-07-10 10:52:37
GoVersion: go1.13
Race Enabled: false
TiKV Min Version: v3.0.0-60965b006877ca7234adaced7890d7b029ed1306
Check Table Before Drop: false
1 row in set (0.00 sec)

9547 · 2020-09-06T17:07:39Z

/pick-up

ti-challenge-bot · 2020-09-06T17:07:43Z

It is not a pickable issue!

More

Tip : If you want this issue to be picked, you need to add a challenge-program label to it.

Warning: None

9547 · 2020-09-06T22:48:05Z

The implementation of date_add_interval in MySQL 8.0 checks only for MAX_DAY_NUMBER date without checking for minimum values( >=0 is valid). This does not correspond to the 1000-00-00 in the MySQL documentation, which we implemented the same to the source code:

The definition of MAX_DAY_NUMBER from mysql/mysql-server/blob/8.0/include/my_time.h#L151

/** Daynumber from year 0 to 9999-12-31 */
constexpr const int64_t MAX_DAY_NUMBER = 3652424;

the implementation of date_add_interval as below:

/**
   Add an interval to a MYSQL_TIME struct.

   @retval true if error
   @retval false otherwise
 */
bool date_add_interval(MYSQL_TIME *ltime, interval_type int_type,
                       Interval interval, int *warnings) {
  ltime->neg = false;


  long long sign = (interval.neg ? -1 : 1);


  switch (int_type) {
    case INTERVAL_SECOND:
    case INTERVAL_SECOND_MICROSECOND:
    case INTERVAL_MICROSECOND:
    case INTERVAL_MINUTE:
    case INTERVAL_HOUR:
    case INTERVAL_MINUTE_MICROSECOND:
    case INTERVAL_MINUTE_SECOND:
    case INTERVAL_HOUR_MICROSECOND:
    case INTERVAL_HOUR_SECOND:
    case INTERVAL_HOUR_MINUTE:
    case INTERVAL_DAY_MICROSECOND:
    case INTERVAL_DAY_SECOND:
    case INTERVAL_DAY_MINUTE:
    case INTERVAL_DAY_HOUR: {
      longlong sec, days, daynr, microseconds, extra_sec;
      ltime->time_type = MYSQL_TIMESTAMP_DATETIME;  // Return full date
      microseconds = ltime->second_part + sign * interval.second_part;
      extra_sec = microseconds / 1000000L;
      microseconds = microseconds % 1000000L;


      if (interval.day > MAX_DAY_NUMBER) goto invalid_date;
      if (interval.hour > MAX_DAY_NUMBER * 24ULL) goto invalid_date;
      if (interval.minute > MAX_DAY_NUMBER * 24ULL * 60ULL) goto invalid_date;
      if (interval.second > MAX_DAY_NUMBER * 24ULL * 60ULL * 60ULL)
        goto invalid_date;
      sec =
          ((ltime->day - 1) * 3600LL * 24LL + ltime->hour * 3600LL +
           ltime->minute * 60LL + ltime->second +
           sign * static_cast<longlong>(
                      interval.day * 3600ULL * 24ULL + interval.hour * 3600ULL +
                      interval.minute * 60ULL + interval.second)) +
          extra_sec;
      if (microseconds < 0) {
        microseconds += 1000000LL;
        sec--;
      }
      days = sec / (3600 * 24LL);
      sec -= days * 3600 * 24LL;
      if (sec < 0) {
        days--;
        sec += 3600 * 24LL;
      }
      ltime->second_part = static_cast<uint>(microseconds);
      ltime->second = static_cast<uint>(sec % 60);
      ltime->minute = static_cast<uint>(sec / 60 % 60);
      ltime->hour = static_cast<uint>(sec / 3600);
      daynr = calc_daynr(ltime->year, ltime->month, 1) + days;
      /* Day number from year 0 to 9999-12-31 */
      if (daynr < 0 || daynr > MAX_DAY_NUMBER) goto invalid_date;
      get_date_from_daynr(daynr, &ltime->year, &ltime->month, &ltime->day);
      break;
    }
    case INTERVAL_DAY:
    case INTERVAL_WEEK: {
      unsigned long period;
      period = calc_daynr(ltime->year, ltime->month, ltime->day);
      if (interval.neg) {
        if (period < interval.day)  // Before 0.
          goto invalid_date;
        period -= interval.day;
      } else {
        if (period + interval.day < period)  // Overflow.
          goto invalid_date;
        if (period + interval.day > MAX_DAY_NUMBER)  // After 9999-12-31.
          goto invalid_date;
        period += interval.day;
      }
      get_date_from_daynr(period, &ltime->year, &ltime->month, &ltime->day);
    } break;
    case INTERVAL_YEAR:
      if (interval.year > 10000UL) goto invalid_date;
      ltime->year += sign * static_cast<long>(interval.year);
      if (static_cast<ulong>(ltime->year) >= 10000L) goto invalid_date;
      if (ltime->month == 2 && ltime->day == 29 &&
          calc_days_in_year(ltime->year) != 366)
        ltime->day = 28;  // Was leap-year
      break;
    case INTERVAL_YEAR_MONTH:
    case INTERVAL_QUARTER:
    case INTERVAL_MONTH: {
      unsigned long long period;


      // Simple guards against arithmetic overflow when calculating period.
      if (interval.month >= UINT_MAX / 2) goto invalid_date;
      if (interval.year >= UINT_MAX / 12) goto invalid_date;


      period = (ltime->year * 12ULL +
                sign * static_cast<unsigned long long>(interval.year) * 12ULL +
                ltime->month - 1ULL +
                sign * static_cast<unsigned long long>(interval.month));
      if (period >= 120000LL) goto invalid_date;
      ltime->year = period / 12;
      ltime->month = (period % 12L) + 1;
      /* Adjust day if the new month doesn't have enough days */
      if (ltime->day > days_in_month[ltime->month - 1]) {
        ltime->day = days_in_month[ltime->month - 1];
        if (ltime->month == 2 && calc_days_in_year(ltime->year) == 366)
          ltime->day++;  // Leap-year
      }
    } break;
    default:
      fprintf(stderr, "Unexpected interval type: %u\n",
              static_cast<unsigned int>(int_type));
      assert(false);
      goto null_date;
  }


  return false;  // Ok


invalid_date:
  if (warnings) {
    *warnings |= MYSQL_TIME_WARN_DATETIME_OVERFLOW;
  }


null_date:


  return true;
}

IMO, we can fix this bug be replace

tidb/types/time.go

Line 135 in 7cd20c0

MinDatetime = FromDate(1000, 1, 1, 0, 0, 0, 0)

to

MinDatetime = FromDate(0, 0, 0, 0, 0, 0, 0)

sre-bot · 2020-09-23T10:44:37Z

Integrity check:
RCA symptom trigger_condition affect_version fix_version fields are empty
@wshwsh12
Please comment /info to get template

ti-srebot · 2020-09-23T10:44:39Z

Please edit this comment to complete the following information

Not a bug

Remove the 'type/bug' label
Add notes to indicate why it is not a bug

Duplicate bug

Add the 'type/duplicate' label
Add the link to the original bug

Bug

Note: Make Sure that 'component', and 'severity' labels are added

1. Root Cause Analysis (RCA)

The implementation of date_add_interval is different from MySQL.

2. Symptom

The result for some SQL contains date_add is different from MySQL.

3. All Trigger Conditions

Input the SQL directly... See the issue describe.

4. Workaround (optional)

No.

5. Affected versions

[v3.0.0:v3.0.19], [v4.0.0:v4.0.7]

6. Fixed versions

4.0.8

seiya-annie · 2020-10-12T02:36:24Z

Please edit this comment to complete the following information
Not a bug
Remove the 'type/bug' label
Add notes to indicate why it is not a bug
Duplicate bug
Add the 'type/duplicate' label
Add the link to the original bug
Bug
Note: Make Sure that 'component', and 'severity' labels are added

Root Cause Analysis (RCA)
The implementation of date_add_interval is different from MySQL.
Symptom
The result for some SQL contains date_add is different from MySQL.
All Trigger Conditions
Input the SQL directly... See the issue describe.
Workaround (optional)
No.
Affected versions
[v3.0.0:v3.0.99], [v4.0.0:v4.0.7]
Fixed versions
4.0.8

wshwsh12 added the type/bug The issue is confirmed as a bug. label Jul 19, 2019

wshwsh12 self-assigned this Jul 19, 2019

wshwsh12 added the component/expression label Jul 19, 2019

SunRunAway mentioned this issue Jul 19, 2019

Incompatible issues about scalar functions(help wanted) #11223

Open

37 tasks

qw4990 added the severity/major label Aug 24, 2020

This was referenced Sep 7, 2020

types: fix datetime lower part overflow check #19822

Merged

another DATETIME's behaviour is different from mysql #19853

Closed

ti-srebot closed this as completed in #19822 Sep 22, 2020

ti-srebot mentioned this issue Sep 22, 2020

types: fix datetime lower part overflow check (#19822) #20155

Merged

sre-bot added the need-more-info label Sep 23, 2020

sre-bot removed the need-more-info label Oct 9, 2020

OliverS929 mentioned this issue Dec 5, 2024

Oracle-compatibility: add_month and next_day function #58003

Merged

13 tasks

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

DATETIME's behaviour is different from mysql. #11329

DATETIME's behaviour is different from mysql. #11329

wshwsh12 commented Jul 19, 2019

ghost commented Jul 13, 2020

9547 commented Sep 6, 2020

ti-challenge-bot bot commented Sep 6, 2020

9547 commented Sep 6, 2020 •

edited

Loading

sre-bot commented Sep 23, 2020

ti-srebot commented Sep 23, 2020 •

edited by seiya-annie

Loading

seiya-annie commented Oct 12, 2020

DATETIME's behaviour is different from mysql. #11329

DATETIME's behaviour is different from mysql. #11329

Comments

wshwsh12 commented Jul 19, 2019

Bug Report

ghost commented Jul 13, 2020

9547 commented Sep 6, 2020

ti-challenge-bot bot commented Sep 6, 2020

9547 commented Sep 6, 2020 • edited Loading

sre-bot commented Sep 23, 2020

ti-srebot commented Sep 23, 2020 • edited by seiya-annie Loading

Please edit this comment to complete the following information

Not a bug

Duplicate bug

Bug

1. Root Cause Analysis (RCA)

2. Symptom

3. All Trigger Conditions

4. Workaround (optional)

5. Affected versions

6. Fixed versions

seiya-annie commented Oct 12, 2020

9547 commented Sep 6, 2020 •

edited

Loading

ti-srebot commented Sep 23, 2020 •

edited by seiya-annie

Loading