[MDEV-9709] Unexpected modification of value and warning about out of range value upon ALTER Created: 2016-03-10  Updated: 2016-03-21  Resolved: 2016-03-14

Status: Closed
Project: MariaDB Server
Component/s: Data Definition - Alter Table
Affects Version/s: 10.2
Fix Version/s: 10.2.0

Type: Bug Priority: Major
Reporter: Elena Stepanova Assignee: Alexander Barkov
Resolution: Fixed Votes: 0
Labels: None

Issue Links:
Relates
relates to MDEV-4102 Limitation on DOUBLE or REAL length i... Closed

 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.



 Comments   
Comment by Alexander Barkov [ 2016-03-11 ]

A smaller script demonstrating the same problem:

CREATE OR REPLACE TABLE t1 (d10_10 DOUBLE (10,10));
 
CREATE OR REPLACE TABLE t2 (d53_10 DOUBLE (53,10));
 
INSERT INTO t2 VALUES (-9999999999999999999999999999999999999999999.9999999999);
 
INSERT INTO t1 (d10_10) SELECT d53_10 FROM t2;
 
SELECT * FROM t1;


+----------------------------------------------------------+
 
| d10_10                                                   |
 
+----------------------------------------------------------+
 
| -10000000000000000000000000000000000000000000.0000000000 |
 
+----------------------------------------------------------+

The expected result is -0.9999999999.

This is repeatable in 10.0, 10.1, 10.2.

Comment by Alexander Barkov [ 2016-03-11 ]

The same problem is repeatable with FLOAT:

CREATE OR REPLACE TABLE t1 (d2_2 FLOAT (2,2));
 
CREATE OR REPLACE TABLE t2 (d4_2 FLOAT (4,2));
 
INSERT INTO t2 VALUES (99.99);
 
INSERT INTO t1 (d2_2) SELECT d4_2 FROM t2;
 
SELECT * FROM t1;


+-------+
 
| d2_2  |
 
+-------+
 
| 99.99 |
 
+-------+

The expected result is 0.99.

Generated at Thu Feb 08 07:36:44 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.