Bug #95274 Unexpected result in PERIOD_ADD

Description:
In function

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

and function

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);
}

As period is unsigned long, it will never less than 0. And will produce wrong result when period is less than 0 in PERIOD_ADD and PERIOD_DIFF

How to repeat:
mysql> SELECT PERIOD_ADD(-100912, 10);select version();
+-------------------------+
| PERIOD_ADD(-100912, 10) |
+-------------------------+
|                 -100814 |
+-------------------------+
1 row in set (0.00 sec)

+-----------+
| version() |
+-----------+
| 8.0.15    |
+-----------+
1 row in set (0.00 sec)

mysql> SELECT PERIOD_ADD(-100915, 10);select version();
+-------------------------+
| PERIOD_ADD(-100915, 10) |
+-------------------------+
|                 -100905 |
+-------------------------+
1 row in set (0.00 sec)                                
                           
+-----------+              
| version() |              
+-----------+              
| 8.0.15    |              
+-----------+          
1 row in set (0.00 sec)
             
mysql> SELECT PERIOD_ADD(-100916, 10);select version();
ERROR 1210 (HY000): Incorrect arguments to period_add

Suggested fix:
bool valid_period(longlong period) {
  if (period <= 0) return false;
  if ((period % 100) == 0) return false;
  if ((period % 100) > 12) return false;
  return true;
}

and function

longlong Item_func_period_add::val_int() {
  DBUG_ASSERT(fixed == 1);
  longlong period = 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);
}

longlong Item_func_period_diff::val_int() {
  DBUG_ASSERT(fixed == 1);
  longlong period1 = args[0]->val_int();
  longlong period2 = args[1]->val_int();

Also should take care of integer overflow.

Hello Song Guo,

Thank you for the report.
Imho this is properly handled in 8.0.16 but issue still exists in 5.6/5.7.

###
-
 bin/mysql -uroot -S /tmp/mysql_ushastry.sock
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.16 MySQL Community Server - GPL

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show variables like 'sql_mode';
+---------------+-----------------------------------------------------------------------------------------------------------------------+
| Variable_name | Value                                                                                                                 |
+---------------+-----------------------------------------------------------------------------------------------------------------------+
| sql_mode      | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION |
+---------------+-----------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT PERIOD_ADD(-100915, 10);
ERROR 1210 (HY000): Incorrect arguments to period_add
mysql> SELECT PERIOD_ADD(-100912, 10);
ERROR 1210 (HY000): Incorrect arguments to period_add
mysql>
mysql> SELECT PERIOD_ADD(-100916, 10);
ERROR 1210 (HY000): Incorrect arguments to period_add
mysql>

- 5.7.26/5.6.44 - behaves wrongly

 bin/mysql -uroot -S /tmp/mysql_ushastry.sock                          
 Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.26 MySQL Community Server (GPL)

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show variables like 'sql_mode';
+---------------+-------------------------------------------------------------------------------------------------------------------------------------------+
| Variable_name | Value                                                                                                                                     |
+---------------+-------------------------------------------------------------------------------------------------------------------------------------------+
| sql_mode      | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+---------------+-------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT PERIOD_ADD(-100915, 10);
+-------------------------+
| PERIOD_ADD(-100915, 10) |
+-------------------------+
|             18611424103 |
+-------------------------+
1 row in set (0.00 sec)

mysql> SELECT PERIOD_ADD(-100912, 10);
+-------------------------+
| PERIOD_ADD(-100912, 10) |
+-------------------------+
|             18611424106 |
+-------------------------+
1 row in set (0.00 sec)

mysql> SELECT PERIOD_ADD(-100916, 10);
+-------------------------+
| PERIOD_ADD(-100916, 10) |
+-------------------------+
|             18611424102 |
+-------------------------+
1 row in set (0.00 sec)

regards,
Umesh

Posted by developer:
 
Fixed by patch for Bug#29175262

Fixed in 8.0.16 under the heading of

Bug#29175262 ITEM_FUNC_PERIOD_ADD::VAL_INT BEHAVES DIFFERENTLY ON WINDOWS: GIVES ERROR