Description:
When doing an alter column of a decimal field, if increasing the precision by an increment of 1, the resulting value adds the 0 in front of the decimal place rather than the end. It's also possible to lose the data (zero'd out) when increasing the second decimal.
How to repeat:
CREATE TABLE `t_2814` (`sys_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `QualData` decimal(9,3) DEFAULT NULL, PRIMARY KEY (`sys_id`));
INSERT INTO t_2814 (QualData) VALUES ('121.304');
select * from t_2814;
alter table t_2814 modify column QualData decimal(9,4) DEFAULT NULL;
select * from t_2814; # result = 121.0304
delete from t_2814;
alter table t_2814 modify column QualData decimal(9,3) DEFAULT NULL;
INSERT INTO t_2814 (QualData) VALUES ('121.304');
alter table t_2814 modify column QualData decimal(10,4) DEFAULT NULL;
select * from t_2814; # result = 121.0304
delete from t_2814;
alter table t_2814 modify column QualData decimal(9,3) DEFAULT NULL;
INSERT INTO t_2814 (QualData) VALUES ('121.304');
alter table t_2814 modify column QualData decimal(9,5) DEFAULT NULL;
select * from t_2814; # result = 0
delete from t_2814;
alter table t_2814 modify column QualData decimal(9,3) DEFAULT NULL;
alter table t_2814 modify column QualData decimal(10,5) DEFAULT NULL;
select * from t_2814; # result = 121.30400
alter table t_2814 modify column QualData decimal(9,4) DEFAULT NULL;
select * from t_2814; # result = 121.3040
Suggested fix:
fix the decimal precision, and prevent 0 being input for a value if the precision is slightly off.
Description: When doing an alter column of a decimal field, if increasing the precision by an increment of 1, the resulting value adds the 0 in front of the decimal place rather than the end. It's also possible to lose the data (zero'd out) when increasing the second decimal. How to repeat: CREATE TABLE `t_2814` (`sys_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `QualData` decimal(9,3) DEFAULT NULL, PRIMARY KEY (`sys_id`)); INSERT INTO t_2814 (QualData) VALUES ('121.304'); select * from t_2814; alter table t_2814 modify column QualData decimal(9,4) DEFAULT NULL; select * from t_2814; # result = 121.0304 delete from t_2814; alter table t_2814 modify column QualData decimal(9,3) DEFAULT NULL; INSERT INTO t_2814 (QualData) VALUES ('121.304'); alter table t_2814 modify column QualData decimal(10,4) DEFAULT NULL; select * from t_2814; # result = 121.0304 delete from t_2814; alter table t_2814 modify column QualData decimal(9,3) DEFAULT NULL; INSERT INTO t_2814 (QualData) VALUES ('121.304'); alter table t_2814 modify column QualData decimal(9,5) DEFAULT NULL; select * from t_2814; # result = 0 delete from t_2814; alter table t_2814 modify column QualData decimal(9,3) DEFAULT NULL; alter table t_2814 modify column QualData decimal(10,5) DEFAULT NULL; select * from t_2814; # result = 121.30400 alter table t_2814 modify column QualData decimal(9,4) DEFAULT NULL; select * from t_2814; # result = 121.3040 Suggested fix: fix the decimal precision, and prevent 0 being input for a value if the precision is slightly off.