Details
-
Bug
-
Status: Open (View Workflow)
-
Major
-
Resolution: Unresolved
-
11.8, 12.1, 12.2
-
None
Description
When we create a procedure, the server is allowing to set a parameter with 81 digits default value, but when increasing the parameter default size to 82 digit, the procedure truncates the default value to 65 digits to that parameter.
The procedure does not throw an ER_DATA_OVERFLOW error for a parameter with 81 digits even though the maximum allowed limit is 65 digits.
When we call the procedure, the server sets the value for both parameters to 65 digits
Test case
SET SQL_MODE=''; |
DELIMITER ||
|
CREATE OR REPLACE PROCEDURE p1( |
a DECIMAL(65,0) DEFAULT 111111111111111111111111111111111111111111111111111111111111111111111111111111111 |
)
|
DETERMINISTIC
|
BEGIN
|
SELECT a; |
END || |
|
|
CREATE OR REPLACE PROCEDURE p2( |
a DECIMAL(65,0) DEFAULT 1111111111111111111111111111111111111111111111111111111111111111111111111111111111 |
)
|
DETERMINISTIC
|
BEGIN
|
SELECT a; |
END || |
SHOW WARNINGS ||
|
DELIMITER ;
|
|
|
select SPECIFIC_NAME,PARAMETER_DEFAULT from information_schema.PARAMETERS where SPECIFIC_NAME like 'p%'; |
call p1();
|
show warnings;
|
call p2();
|
show warnings;
|
Result set
12.2.0-dbg>SET SQL_MODE=''; |
Query OK, 0 rows affected (0.001 sec) |
|
|
12.2.0-dbg>DELIMITER ||
|
12.2.0-dbg>CREATE OR REPLACE PROCEDURE p1( |
-> a DECIMAL(65,0) DEFAULT 111111111111111111111111111111111111111111111111111111111111111111111111111111111 |
-> )
|
-> DETERMINISTIC
|
-> BEGIN |
-> SELECT a; |
-> END || |
Query OK, 0 rows affected (0.012 sec) |
|
|
12.2.0-dbg>
|
12.2.0-dbg>CREATE OR REPLACE PROCEDURE p2( |
-> a DECIMAL(65,0) DEFAULT 1111111111111111111111111111111111111111111111111111111111111111111111111111111111 |
-> )
|
-> DETERMINISTIC
|
-> BEGIN |
-> SELECT a; |
-> END || |
Query OK, 0 rows affected, 1 warning (0.008 sec) |
|
|
12.2.0-dbg>SHOW WARNINGS ||
|
+---------+------+-------------------------------------------------------------+ |
| Level | Code | Message | |
+---------+------+-------------------------------------------------------------+ |
| Warning | 1916 | Got overflow when converting '' to DECIMAL. Value truncated | |
+---------+------+-------------------------------------------------------------+ |
1 row in set (0.003 sec) |
|
|
12.2.0-dbg>DELIMITER ;
|
12.2.0-dbg>
|
12.2.0-dbg>select SPECIFIC_NAME,PARAMETER_DEFAULT from information_schema.PARAMETERS where SPECIFIC_NAME like 'p%'; |
+---------------+-----------------------------------------------------------------------------------+ |
| SPECIFIC_NAME | PARAMETER_DEFAULT |
|
+---------------+-----------------------------------------------------------------------------------+ |
| p1 | 111111111111111111111111111111111111111111111111111111111111111111111111111111111 |
|
| p2 | 99999999999999999999999999999999999999999999999999999999999999999 |
|
+---------------+-----------------------------------------------------------------------------------+ |
2 rows in set, 1 warning (0.005 sec) |
|
|
12.2.0-dbg>call p1();
|
+-------------------------------------------------------------------+ |
| a |
|
+-------------------------------------------------------------------+ |
| 99999999999999999999999999999999999999999999999999999999999999999 |
|
+-------------------------------------------------------------------+ |
1 row in set (0.001 sec) |
|
|
Query OK, 0 rows affected, 1 warning (0.002 sec) |
|
|
12.2.0-dbg>show warnings;
|
+---------+------+--------------------------------------------+ |
| Level | Code | Message | |
+---------+------+--------------------------------------------+ |
| Warning | 1264 | Out of range value for column 'a' at row 0 | |
+---------+------+--------------------------------------------+ |
1 row in set (0.000 sec) |
|
|
12.2.0-dbg>call p2();
|
+-------------------------------------------------------------------+ |
| a |
|
+-------------------------------------------------------------------+ |
| 99999999999999999999999999999999999999999999999999999999999999999 |
|
+-------------------------------------------------------------------+ |
1 row in set (0.001 sec) |
|
|
Query OK, 0 rows affected, 1 warning (0.001 sec) |
|
|
12.2.0-dbg>show warnings;
|
+---------+------+-------------------------------------------------------------+ |
| Level | Code | Message | |
+---------+------+-------------------------------------------------------------+ |
| Warning | 1916 | Got overflow when converting '' to DECIMAL. Value truncated | |
+---------+------+-------------------------------------------------------------+ |
1 row in set (0.000 sec) |
|
|
12.2.0-dbg>
|