Description:
When a SELECT statement includes an expression which divides one column by another, the resulting column usually has a number of decimal places that is two greater than the number of decimal places in the numerator or denominator, whichever is greater. However, if the resulting number of decimal places d>=32, then the column is created with a number of decimal places equal to mod(d, 32), which can have undesirable effects (i.e., a major loss of precision in calculations).
How to repeat:
# This code will reproduce the effect:
drop temporary table if exists a1;
create temporary table a1 (val1 double(35,5), val2 double(12,10), val3 double(12,5));
drop temporary table if exists a2;
create temporary table a2
select val1, val2, val3, val1/val2, val1/val2/val3 from a1;
show columns from a2;
# val1/val2 has d=12, which is max(5,10)+2, as expected
# val1/val2/val3 has d=12=max(5,10,5)+2+2, as expected
drop temporary table if exists a1;
create temporary table a1 (val1 double(35,27), val2 double(12,10), val3 double(12,5));
drop temporary table if exists a2;
create temporary table a2
select val1, val2, val3, val1/val2, val1/val2/val3 from a1;
show columns from a2;
# val1/val2: d=29=max(27,10)+2, as expected
# val1/val2/val3: specified as "double," equivalent to d=31=max(27,10,5)+2+2, as expected
drop temporary table if exists a1;
create temporary table a1 (val1 double(35,30), val2 double(12,10), val3 double(12,5));
drop temporary table if exists a2;
create temporary table a2
select val1, val2, val3, val1/val2, val1/val2/val3 from a1;
show columns from a2;
# val1/val2: d=0=mod(max(30,10)+2, 32), which is unexpected
# val1/val2/val3: d=2=mod(max(30,10,5)+2+2), which is unexpected
Suggested fix:
When calculating the decimal width of expressions made via division, don't allow the number of decimal places to exceed 31, which appears to be the maximum for column type "double." Actually, however, this problem may be the result of a bug in the code which creates table columns based on expressions. I already filed a bug report for that (http://bugs.mysql.com/?id=4393); once that is fixed, the problem with division should go away (i.e., the table column width will be set appropriately, even for division expressions which have more than 31 decimal places).
Description: When a SELECT statement includes an expression which divides one column by another, the resulting column usually has a number of decimal places that is two greater than the number of decimal places in the numerator or denominator, whichever is greater. However, if the resulting number of decimal places d>=32, then the column is created with a number of decimal places equal to mod(d, 32), which can have undesirable effects (i.e., a major loss of precision in calculations). How to repeat: # This code will reproduce the effect: drop temporary table if exists a1; create temporary table a1 (val1 double(35,5), val2 double(12,10), val3 double(12,5)); drop temporary table if exists a2; create temporary table a2 select val1, val2, val3, val1/val2, val1/val2/val3 from a1; show columns from a2; # val1/val2 has d=12, which is max(5,10)+2, as expected # val1/val2/val3 has d=12=max(5,10,5)+2+2, as expected drop temporary table if exists a1; create temporary table a1 (val1 double(35,27), val2 double(12,10), val3 double(12,5)); drop temporary table if exists a2; create temporary table a2 select val1, val2, val3, val1/val2, val1/val2/val3 from a1; show columns from a2; # val1/val2: d=29=max(27,10)+2, as expected # val1/val2/val3: specified as "double," equivalent to d=31=max(27,10,5)+2+2, as expected drop temporary table if exists a1; create temporary table a1 (val1 double(35,30), val2 double(12,10), val3 double(12,5)); drop temporary table if exists a2; create temporary table a2 select val1, val2, val3, val1/val2, val1/val2/val3 from a1; show columns from a2; # val1/val2: d=0=mod(max(30,10)+2, 32), which is unexpected # val1/val2/val3: d=2=mod(max(30,10,5)+2+2), which is unexpected Suggested fix: When calculating the decimal width of expressions made via division, don't allow the number of decimal places to exceed 31, which appears to be the maximum for column type "double." Actually, however, this problem may be the result of a bug in the code which creates table columns based on expressions. I already filed a bug report for that (http://bugs.mysql.com/?id=4393); once that is fixed, the problem with division should go away (i.e., the table column width will be set appropriately, even for division expressions which have more than 31 decimal places).