MySQL Bugs: #31805: strored procedure returns double data incorrectly

Bug #31805	strored procedure returns double data incorrectly
Submitted:	24 Oct 2007 3:11	Modified:	15 Nov 2007 18:20
Reporter:	Ashiq Khan	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S3 (Non-critical)
Version:	5.0.19, 5.0.45	OS:	Linux
Assigned to:	Paul DuBois	CPU Architecture:	Any

Description:
Stored procedure returns double data with incorrect scale.

How to repeat:
Create stored procedure as below:

DELIMITER $$

DROP PROCEDURE IF EXISTS `test`.`sp_decimal`$$

CREATE PROCEDURE `sp_decimal`(IN data1 double(10,2),OUT out1 double(10,2))
BEGIN
	SET out1=data1;
    END$$

DELIMITER ;

execute stored procedure as following:
CALL test.sp_decimal(2.10,@out5);
SELECT @out5;

The value expected is 2.10, but it returns 2.1.

Suggested fix:
Please let me know is this an bug in the msyql.

Thank you for a bug report. I think, it is a bug. Although @var is converted to string according to http://dev.mysql.com/doc/refman/5.0/en/user-variables.html, but explicit conversion of 2.10 still gives:

mysql> select cast(2.10 as CHAR)//
Field   1:  `cast(2.10 as CHAR)`
Catalog:    `def`
Database:   ``
Table:      ``
Org_table:  ``
Type:       VAR_STRING
Collation:  latin1_swedish_ci (8)
Length:     5
Max_length: 4
Decimals:   31
Flags:

+--------------------+
| cast(2.10 as CHAR) |
+--------------------+
| 2.10               |
+--------------------+
1 row in set (0.00 sec)

While:

mysql> show create procedure sp_decimal\G
*************************** 1. row ***************************
       Procedure: sp_decimal
        sql_mode: STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER
Create Procedure: CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_decimal`(IN da
ta1 double(10,2),OUT out1 double(10,2))
begin
select data1;
set out1=data1;
end
1 row in set (0.00 sec)

mysql> call sp_decimal(2.10,@out)//
Field   1:  `data1`
Catalog:    `def`
Database:   ``
Table:      ``
Org_table:  ``
Type:       DOUBLE
Collation:  binary (63)
Length:     10
Max_length: 4
Decimals:   2
Flags:      NUM

+-------+
| data1 |
+-------+
|  2.10 |
+-------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.02 sec)

mysql> select @out//
Field   1:  `@out`
Catalog:    `def`
Database:   ``
Table:      ``
Org_table:  ``
Type:       VAR_STRING
Collation:  binary (63)
Length:     23
Max_length: 3
Decimals:   31
Flags:      BINARY

+------+
| @out |
+------+
| 2.1  |
+------+
1 row in set (0.00 sec)

This is inconsistent, and, thus, a bug.

It needs to be checked with the documentation whether this is
really expected behavior or not. If you believe this is not a bug
please provide a clear explanation why. Notice that the procure
arguments are of DOUBLE type.

Valeriy, Jim, this is not a bug
A user variable can not have type DOUBLE(10,2). It is either DECIMAL, DOUBLE, INT or STRING with the default values of length ans scale.
Assigning a value to the user variable changes its type only within the set of these 4.
Since I can't find this in the manual, setting category to Documentation

Thank you for your bug report. This issue has been addressed in the documentation. The updated documentation will appear on our website shortly, and will be included in the next release of the relevant products.