|
The problem is also reproducible on MySQL 5.1-5.6 and filed as http://bugs.mysql.com/bug.php?id=68192
A DOUBLE (or REAL) column definition has a limitation on the total number of digits. It's honored when values are inserted explicitly, but not on INSERT .. SELECT:
CREATE TABLE t1 (d1 DOUBLE(5,2), d2 DOUBLE(10,2)) ENGINE=InnoDB;
|
|
INSERT INTO t1 VALUES (10000000.55, 10000000.55);
|
Warnings:
|
Warning 1264 Out of range value for column 'd1' at row 1
|
|
# As expected, d1 is truncated:
|
|
SELECT * FROM t1;
|
d1 d2
|
999.99 10000000.55
|
|
INSERT INTO t1 SELECT d2, d2 FROM t1;
|
|
# Now d1 is not truncated, and no warnings:
|
|
SELECT * FROM t1;
|
d1 d2
|
999.99 10000000.55
|
10000000.55 10000000.55
|
Reproducible on 5.1-10.0.
Test case:
CREATE TABLE t1 (d1 DOUBLE(5,2), d2 DOUBLE(10,2)) ENGINE=InnoDB;
|
INSERT INTO t1 VALUES (10000000.55, 10000000.55);
|
INSERT INTO t1 SELECT d2, d2 FROM t1;
|
SELECT * FROM t1;
|
|