PERIOD_ADD is not compatible with Mysql

[qw4990]

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 PERIOD_ADD(-11924, 2892462180);

2. What did you expect to see?
   In Mysql 8.0.12

mysql> SELECT PERIOD_ADD(-11924, 2892462180);
ERROR 1210 (HY000): Incorrect arguments to period_add

3. What did you see instead?
   In TiDB

mysql> SELECT PERIOD_ADD(-11924, 2892462180);
+--------------------------------+
| PERIOD_ADD(-11924, 2892462180) |
+--------------------------------+
|                    24103840192 |
+--------------------------------+
1 row in set (0.00 sec)

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

+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tidb_version()                                                                                                                                                                                                                                                                                                                     |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Release Version: v3.0.0-beta-212-g0681b8125
Git Commit Hash: 0681b8125080045ee0d4747ce61923164c6f2dd1
Git Branch: master
UTC Build Time: 2019-03-14 04:39:19
GoVersion: go version go1.12 darwin/amd64
Race Enabled: false
TiKV Min Version: 2.1.0-alpha.1-ff3dd160846b7d1aed9079c389fc188f7f5ea13e
Check Table Before Drop: false |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

[wjhuang2016]

What version of Mysql are you using?
My Mysql version is 8.0.15, and in this version I get

mysql> SELECT PERIOD_ADD(-11924, 2892462180);
ERROR 1210 (HY000): Incorrect arguments to period_add

I think the new verison makes sense, since month should not be negetive

Here is the logic I found in the source code of Mysql 8.0.15

longlong Item_func_period_add::val_int() {
  DBUG_ASSERT(fixed == 1);
  ulong period = (ulong)args[0]->val_int();
  int months = (int)args[1]->val_int();

  if ((null_value = args[0]->null_value || args[1]->null_value))
    return 0; /* purecov: inspected */
  if (!valid_period(period)) {
    my_error(ER_WRONG_ARGUMENTS, MYF(0), func_name());
    return error_int();
  }
  return (longlong)convert_month_to_period(convert_period_to_month(period) +
                                           months);
}

and

bool valid_period(ulong period) {
  if (period <= 0) return false;
  if ((period % 100) == 0) return false;
  if ((period % 100) > 12) return false;
  return true;
}

It seems that we have many things to do to be compatible with the newest Mysql.