Description:
According to the MySQL Reference Manual, a user variable can
be assigned either an integer, real, string, or NULL value. But
"real" appears not to accept a double-precision value (as is
customary for MySQL) with a precision of 14 or more digits.
(Note that IEEE double is a number with 15 decimal digits
precision). This is causing a problem: MySQL isn't able to pass
the JDBC compliance test suite, because a decimal value being
passed from a stored procedure to a user variable is rounding
that value.
How to repeat:
mysql> -- test with original specs
mysql> create table decimal_tab (max_val dec(30,15) default null, min_val dec(30,15) default null, null_val dec(30,15) default null) engine=innodb;
Query OK, 0 rows affected (0.01 sec)
mysql> insert into decimal_tab values (999999999999999.000000000000000,0.000000000000001,null);
Query OK, 1 row affected (0.00 sec)
mysql> delimiter //;
mysql> create procedure decimal_proc (out max_param decimal(30,15), out min_param decimal(30,15), out null_param decimal(30,15)) begin select max_val,min_val,null_val into max_param,min_param,null_param from decimal_tab; select max_param, min_param, null_param; set @max_val=max_param; set @min_val=min_param; set @null_val=null_param; select @max_val, @min_val, @null_val; end//
Query OK, 0 rows affected (0.01 sec)
mysql> delimiter ;//
mysql> set @max_val=5.2E+3, @min_val=5.2E+3, @null_val=5.2E+3;
Query OK, 0 rows affected (0.00 sec)
-- Note that this should tell the server to treat the user
variables as "real" types.
mysql> call decimal_proc(@max_val,@min_val,@null_val);
+---------------------------------+-------------------+------------+
| max_param | min_param | null_param |
+---------------------------------+-------------------+------------+
| 999999999999999.000000000000000 | 0.000000000000001 | NULL |
+---------------------------------+-------------------+------------+
1 row in set (0.00 sec)
-- This is the correct result, showing that the SP is setting the
parameter values correctly.
+----------+----------+-----------+
| @max_val | @min_val | @null_val |
+----------+----------+-----------+
| 1e+15 | 1e-15 | NULL |
+----------+----------+-----------+
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
mysql> select @max_val, @min_val, @null_val;
+----------+----------+-----------+
| @max_val | @min_val | @null_val |
+----------+----------+-----------+
| 1e+15 | 1e-15 | NULL |
+----------+----------+-----------+
1 row in set (0.00 sec)
-- These last two SELECTs show that the @max_value, however, has
lost the precision required to pass the JDBC test.
mysql> -- test with less precision; it works if precision drops to 28
mysql> drop table decimal_tab;
Query OK, 0 rows affected (0.01 sec)
mysql> drop procedure decimal_proc;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> create table decimal_tab (max_val dec(28,15) default null, min_val dec(28,15) default null, null_val dec(28,15) default null) engine=innodb;
Query OK, 0 rows affected (0.01 sec)
mysql> insert into decimal_tab values (9999999999999.000000000000000,0.000000000000001,null);
Query OK, 1 row affected (0.00 sec)
mysql> delimiter //;
mysql> create procedure decimal_proc (out max_param decimal(28,15), out min_param decimal(28,15), out null_param decimal(28,15)) begin select max_val,min_val,null_val into max_param,min_param,null_param from decimal_tab; select max_param, min_param, null_param; set @max_val=max_param; set @min_val=min_param; set @null_val=null_param; select @max_val, @min_val, @null_val; end//
Query OK, 0 rows affected (0.00 sec)
mysql> delimiter ;//
mysql> set @max_val=5.2E+3, @min_val=5.2E+3, @null_val=5.2E+3;
Query OK, 0 rows affected (0.00 sec)
mysql> call decimal_proc(@max_val,@min_val,@null_val);
+-------------------------------+-------------------+------------+
| max_param | min_param | null_param |
+-------------------------------+-------------------+------------+
| 9999999999999.000000000000000 | 0.000000000000001 | NULL |
+-------------------------------+-------------------+------------+
1 row in set (0.00 sec)
-- This, again, is the correct result.
+---------------+----------+-----------+
| @max_val | @min_val | @null_val |
+---------------+----------+-----------+
| 9999999999999 | 1e-15 | NULL |
+---------------+----------+-----------+
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
mysql> select @max_val, @min_val, @null_val;
+---------------+----------+-----------+
| @max_val | @min_val | @null_val |
+---------------+----------+-----------+
| 9999999999999 | 1e-15 | NULL |
+---------------+----------+-----------+
1 row in set (0.00 sec)
-- This time, both SELECTs show that all three user variables have
the expected value.
