MySQL Bugs: #77636: decimal field takes in values from other fields

Bug #77636	decimal field takes in values from other fields
Submitted:	7 Jul 2015 9:29	Modified:	23 Jul 2015 1:44
Reporter:	Björn Tantau	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Data Types	Severity:	S3 (Non-critical)
Version:	5.1/5.5/5.6/5.7	OS:	Debian (Wheezy)
Assigned to:		CPU Architecture:	Any

Description:
If you fill a decimal field with a SELECT statement inside a CREATE TABLE statement, the decimal field will get an additional value from the field after it.

eg if you fill the decimal with 1000 and an int after that with 3 the decimal will get the value 1000.03. Probably depending on the precision.

As a workaround you can reorder your fields or simply not fill the table while creating it.

Tested with MySQL 5.5 and 5.1.

How to repeat:
DROP TABLE IF EXISTS TEMP_TEST;
CREATE TABLE TEMP_TEST (
`dec` decimal(18,2) unsigned DEFAULT NULL,
`int` int(11) unsigned DEFAULT NULL
) ENGINE=InnoDB
SELECT
1000 AS `dec`,
3 AS `int`;
SELECT * FROM TEMP_TEST;

Thank you for the bug report. Same behavior since 5.0:

C:\dbs>c:\dbs\5.7\bin\mysql -uroot -p --port=3570 --prompt="mysql 5.7 > "
Enter password: ******
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.8-rc Source distribution PULL: 2015/06/28

Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql 5.7 > USE test;
Database changed
mysql 5.7 > DROP TABLE IF EXISTS TEMP_TEST;
Query OK, 0 rows affected (0.34 sec)

mysql 5.7 > CREATE TABLE TEMP_TEST (
    -> `dec` decimal(18,2) unsigned DEFAULT NULL,
    -> `int` int(11) unsigned DEFAULT NULL
    -> ) ENGINE=InnoDB
    -> SELECT
    -> 1000 AS `dec`,
    -> 3 AS `int`;
Query OK, 1 row affected (0.30 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql 5.7 > SELECT * FROM TEMP_TEST;
+---------+------+
| dec     | int  |
+---------+------+
| 1000.03 |    3 |
+---------+------+
1 row in set (0.00 sec)

mysql 5.7 >

Noted in 5.7.9 changelog.

For statements of the form CREATE TABLE ... SELECT, where the table 
was defined to contain a DECIMAL UNSIGNED, calculation of the row
size was incorrect, leading to incorrect values in the table.