MySQL Bugs: #9855: inconsitent column type (float) for create table select calculated value

Bug #9855	inconsitent column type (float) for create table select calculated value
Submitted:	12 Apr 2005 19:11	Modified:	26 Jan 2006 2:40
Reporter:	Martin Friebe (Gold Quality Contributor) (OCA)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	4.1.11	OS:	Any (*)
Assigned to:	Alexey Botchkov	CPU Architecture:	Any

Description:
create table chooses a different column type for the same value, dependend  on its entered as a fixed value, or simple equations

Adding 0.0 to 105213674794682365.00 should not make a difference. The isue also arises
with value pairs like 105213674794682365.0 and 105213674794682365 +0.0

The double(21.2) from the 2nd example seems to be correct, it also avaids the data loss by truncation.

create table tbl1 select  105213674794682365.00 + 0.0 x;
desc  tbl1;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| x     | double(19,2) |      |     | 0.00    |       |
+-------+--------------+------+-----+---------+-------+

create table tbl1 select  105213674794682365.00  x;
desc  tbl1; 
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| x     | double(21,2) |      |     | 0.00    |       |
+-------+--------------+------+-----+---------+-------+

How to repeat:
create table tbl1 select  105213674794682365.00 + 0.0 x;
show warnings;
desc  tbl1;
drop table tbl1;

create table tbl1 select  105213674794682365.00  x;
desc  tbl1;
drop table tbl1;

Suggested fix:
-

The inconsistent behavior is due to the float number, even is commented
in the Manual when performing comparison with float numbers you can see the reason for.

http://dev.mysql.com/doc/mysql/en/problems-with-float.html

this applies to the 2nd example, wich contains an integer.

The bug here is, that mysql chooses a column type, that is not good enough to store the number. even so mysql does provide column types that could stire the number without truncation.

create table tbl1 select 105213674794682365.00 x;
creates a column of a type double(21,2), which can hold the number without truncation.

I would not have a problem if (105213674794682365.00 + 0.0) would due to the floating point conversion result in a number that would require smaller storage. If mysql coul store the result in a double(19,2), then it should to that.

However mysql chpooses a column type of double(19,2), just to realize it is to small, and then it truncates the data.

the truncation means that mysql realizes that the dat does not fit in a (19.2).

float numbers are not exact, which means the result of any operation including float numbers can differ from the "mathematical correct" result. But any ONE single float number has fixed property. A single choosen float number does not change it requirement in storage length (unless a new operation is performed with it)

Therefore mysql should calculate 105213674794682365.00 + 0.0. It should look at the result (the final result) and take the storage length required for this. If so it would not have need for truncation.

It appears to me that mysql does in some attempt of optimization look at the formula and tries to predict the column type early.

At all it determine it right in the first case, and the resulting number of the 2nd case if you do a select 105213674794682365.00 + 0.0 is the same

Ok I will left Serg to comment your last post.

Ok I bring this in a bit late, anyway. This is an example, in case mysql looks of the individual types, of all involved columns (in the original that is 2 constants), and derives the new column type of the involved columns.

This example creates to tables, with columns of (21.2) and (3.1). 
From all I can see, if I add two float values together with this specs, I have all reason to believe the result will be 21.2 as well (It could even be 22.2).

mysql things the result of the additipon is going to fit into (19.2)

create table t1 select 0.0 x ; desc t1;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| x     | double(3,1) |      |     | 0.0     |       |
+-------+-------------+------+-----+---------+-------+

create table t2 select 105213674794682365.00 y ; desc t2;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| y     | double(21,2) |      |     | 0.00    |       |
+-------+--------------+------+-----+---------+-------+

create table t3 select x+y a from t1,t2 ; show warnings; desc t3;
+---------+------+------------------------------------------------------+
| Level   | Code | Message                                              |
+---------+------+------------------------------------------------------+
| Warning | 1264 | Data truncated; out of range for column 'a' at row 1 |
+---------+------+------------------------------------------------------+

+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| a     | double(19,2) |      |     | 0.00    |       |
+-------+--------------+------+-----+---------+-------+
1 row in set (0.00 sec)

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/31132

This one seem ok to push for me.

Documented in 4.1.18 changelog:

      <listitem>
        <para>
          A <literal>CREATE TABLE ... SELECT ...</literal> on an
          equation involving <literal>DOUBLE</literal> values could
          result in the table being created with columns too small to
          hold the equation result. (Bug #9855)
        </para>
      </listitem>