Description:
When a SELECT statement creates a column from a floating point expression, the column is usually created as type double, and sized to approximately match the width of the values returned by the expression. However, when the expression has d>32 decimal places, the resulting column is given a number of decimal places equal to mod(d, 32), which produces unexpected results.
This can happen if you create a column directly from an expression with d>=32 decimal places, or it can also happen when MySQL analyzes the columns involved in the expression and decides that the result column will need d>=32 decimal places. (I will file a separate bug report about this, with respect to expressions involving division of two type-double columns.)
How to repeat:
This code demonstrates the behavior:
drop temporary table if exists t1;
create table t1 select round(1.1234567890123456789012345678901234567890, 30);
show columns from t1;
# Column is type "double(42,30)", as expected
# (note, however, that the value in the column is truncated after 16 decimal places, which is unexpected, but not the subject of this bug report.)
drop temporary table if exists t1;
create table t1 select round(1.1234567890123456789012345678901234567890, 31);
show columns from t1;
# Column is type "double"; I might have expected "double(42,31)", but this seems reasonable, if the maximum precision for a double is 31 places
drop temporary table if exists t1;
create table t1 select round(1.1234567890123456789012345678901234567890, 32);
show columns from t1;
# Column is type "double(42,0)"; I would have expected "double(42,32)", or just "double"
drop temporary table if exists t1;
create table t1 select round(1.1234567890123456789012345678901234567890, 33);
show columns from t1;
# Column is type "double(42,1)", which is unexpected. This appears to be mod(33, 32)
drop temporary table if exists t1;
create table t1 select round(1.1234567890123456789012345678901234567890, 293);
show columns from t1;
# Column is type "double(42,5)"; this appears to be mod(293, 32)
# The above results can also be generated by using a select statement without the round() function, with just a number with the desired number of decimal places. I just used the round() function to make it more explicit.
# Also, the best workaround for expressions that will have more than 32 decimal places is to convert the expression into round(<expression>, 31)
# Also note: the problem does not manifest if you just do a SELECT to the console, without creating a table.
Suggested fix:
I would guess there is a problem in the code where table columns are automatically specified to match expressions in SELECT statements. This code should be changed to create a column of type "double" or "double(width, maxdecimals)" whenever the expression has more than maxdecimals decimal places.
