| Bug #95274 | Unexpected result in PERIOD_ADD | ||
|---|---|---|---|
| Submitted: | 7 May 2019 9:20 | Modified: | 31 Jan 2020 13:29 |
| Reporter: | Song Guo | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: DML | Severity: | S3 (Non-critical) |
| Version: | 8.0.15, 5.7.26, 5.6.44 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
[7 May 2019 10:42]
MySQL Verification Team
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
[6 Jun 2019 12:29]
Tor Didriksen
Posted by developer: Fixed by patch for Bug#29175262
[31 Jan 2020 13:29]
Erlend Dahl
Fixed in 8.0.16 under the heading of Bug#29175262 ITEM_FUNC_PERIOD_ADD::VAL_INT BEHAVES DIFFERENTLY ON WINDOWS: GIVES ERROR
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.