Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
10.2(EOL)
-
None
Description
Consider the following example:
CREATE TABLE t1 ( |
f FLOAT, |
d10_10 DOUBLE PRECISION (10,10), |
d53_10 DOUBLE(53,10) |
) ENGINE=MyISAM;
|
|
|
INSERT INTO t1 (f,d10_10,d53_10) VALUES ( |
-9999999999999999999999999999999999999999999.9999999999,
|
-9999999999999999999999999999999999999999999.9999999999,
|
-9999999999999999999999999999999999999999999.9999999999
|
);
|
SELECT * FROM t1 \G |
INSERT INTO t1 (f,d10_10,d53_10) SELECT d53_10, d53_10, d53_10 FROM t1; |
SELECT * FROM t1 \G |
ALTER TABLE t1 ADD COLUMN i INT; |
SELECT * FROM t1 \G |
The first INSERT inserts out of range values into `f` and `d10_10` columns. The warnings are issued, and values are adjusted (whether they are adjusted correctly is another question, but it's not the point of this report):
Warnings:
|
Warning 1264 Out of range value for column 'f' at row 1 |
Warning 1264 Out of range value for column 'd10_10' at row 1 |
SELECT * FROM t1; |
f -3.40282e38
|
d10_10 -0.9999999999
|
d53_10 -10000000000000000000000000000000000000000000.0000000000
|
The second INSERT also inserts out of range values into these columns, but somehow the server only complains about the float column, but not about `d10_10` (and it was so in previous versions):
INSERT INTO t1 (f,d10_10,d53_10) SELECT d53_10, d53_10, d53_10 FROM t1; |
Warnings:
|
Warning 1264 Out of range value for column 'f' at row 1 |
SELECT * FROM t1; |
f -3.40282e38
|
d10_10 -0.9999999999
|
d53_10 -10000000000000000000000000000000000000000000.0000000000
|
f -3.40282e38
|
d10_10 -10000000000000000000000000000000000000000000.0000000000
|
d53_10 -10000000000000000000000000000000000000000000.0000000000
|
But when we are adding a new unrelated column to the table, the server suddenly realizes that the value was wrong, issues a warning and amends the value:
|
10.2 |
ALTER TABLE t1 ADD COLUMN i INT; |
Warnings:
|
Warning 1264 Out of range value for column 'd10_10' at row 2 |
SELECT * FROM t1; |
f -3.40282e38
|
d10_10 -0.9999999999
|
d53_10 -10000000000000000000000000000000000000000000.0000000000
|
i NULL |
f -3.40282e38
|
d10_10 -0.9999999999
|
d53_10 -10000000000000000000000000000000000000000000.0000000000
|
i NULL |
It is not so in 10.1 and MySQL 5.7, both versions stick to the inserted value:
|
MySQL 5.7 |
ALTER TABLE t1 ADD COLUMN i INT; |
SELECT * FROM t1; |
f -3.40282e38
|
d10_10 -0.9999999999
|
d53_10 -10000000000000000000000000000000000000000000.0000000000
|
i NULL |
f -3.40282e38
|
d10_10 -10000000000000000000000000000000000000000000.0000000000
|
d53_10 -10000000000000000000000000000000000000000000.0000000000
|
i NULL |
It does not seem right to change the value upon ALTER – if there is a problem with the value, it should be noticed and dealt with upon INSERT.
Attachments
Issue Links
- relates to
-
-
- Closed
-