search

pingcap / tidb

TiDB - the open-source, cloud-native, distributed SQL database designed for modern applications.
https://pingcap.com
Apache License 2.0
37.2k stars 5.84k forks source link

DATETIME's behaviour is different from mysql. #11329

Closed wshwsh12 closed 4 years ago

wshwsh12 commented 5 years ago

Bug Report

Please answer these questions before submitting your issue. Thanks!

  1. 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);
  1. 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)
  1. 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)
  1. 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 |
ghost commented 4 years ago

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 commented 4 years ago

/pick-up

ti-challenge-bot[bot] commented 4 years ago

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 commented 4 years ago

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

https://github.com/pingcap/tidb/blob/7cd20c0373c4c0a821e243b6c40ec66cfb93481b/types/time.go#L135

to 

MinDatetime = FromDate(0, 0, 0, 0, 0, 0, 0)
sre-bot commented 4 years ago

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

ti-srebot commented 4 years ago

Please edit this comment to complete the following information

Not a bug

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

Duplicate bug

  1. Add the 'type/duplicate' label
  2. 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 commented 4 years ago

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.99], [v4.0.0:v4.0.7]

  6. Fixed versions 4.0.8