Bug #9078 STORED PROCDURE: Decimal digits are not displayed when we use DECIMAL datatype
Submitted: 9 Mar 2005 16:14 Modified: 7 Dec 2005 20:40
Reporter: Disha Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Stored Routines Severity:S3 (Non-critical)
Version:5.0.12-beta-nt, 5.0.13-BK OS:Linux (Linux, Windows Server 2003)
Assigned to: Alexander Nozdrin CPU Architecture:Any

[9 Mar 2005 16:14] Disha
Description:
f we create a procedure using DECIMAL datatype variable and select it, it does not display the decimal digits of that variable.

How to repeat:
Repro Steps : 

1. Use database 'test' i.e. execute the following SQL statement:
	     use test;
2. Create and Run the follwing query:

Drop Procedure if exists sp1 // 
Create procedure sp1 (i1 decimal (64,2))
Begin
	Declare Var1 decimal (64,2);
	Set Var1 = i1;
	Select Var1;
End //

Call sp1 (1929)//
			
3. Observe that the procedure execution displays the following result:

			+------+
			| Var1 |
			+------+
			| 1929 |
			+------+
				
 Expected Results: It should display the 2 decimal digits after the number.
    
 Actual Results  : The execution does not display the decimal digits.
[14 Sep 2005 7:32] Valeriy Kravchuk
Problem still exists. DECIMAL datatype behaviour in SPs is inconsistent with table columns' one:

mysql> use test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> delimiter //
mysql> Create procedure sp1 (i1 decimal (64,2))
    -> BBegin
    ->  Declare Var1 decimal (64,2);
    -> Set Var1 = i1;
    -> Select Var1;
    -> End //
Query OK, 0 rows affected (0,13 sec)

mysql> call sp1(1929)//
+------+
| Var1 |
+------+
| 1929 |
+------+
1 row in set (0,02 sec)

Query OK, 0 rows affected (0,02 sec)

mysql> call sp1(1929.00)//
+---------+
| Var1    |
+---------+
| 1929.00 |
+---------+
1 row in set (0,01 sec)

Query OK, 0 rows affected (0,01 sec)

mysql> call sp1(1929.003)//
+----------+
| Var1     |
+----------+
| 1929.003 |
+----------+
1 row in set (0,00 sec)

Query OK, 0 rows affected (0,01 sec)

mysql> select version()//
+-------------------+
| version()         |
+-------------------+
| 5.0.13-beta-debug |
+-------------------+
1 row in set (0,00 sec)

mysql> create table tt1 (c1 decimal(64,2))//
Query OK, 0 rows affected (0,25 sec)

mysql> insert into tt1 values(1929)//
Query OK, 1 row affected (0,11 sec)

mysql> select * from tt1//
+---------+
| c1      |
+---------+
| 1929.00 |
+---------+
1 row in set (0,03 sec)

mysql> insert into tt1 values(1929.003)//
Query OK, 1 row affected, 1 warning (0,00 sec)

mysql> select * from tt1//
+---------+
| c1      |
+---------+
| 1929.00 |
| 1929.00 |
+---------+
2 rows in set (0,00 sec)
[7 Dec 2005 20:40] Paul Dubois
Noted in 5.0.18 changelog.