From 6a2ee9c8bbec437e7eb50b1a273a017cdefc6e15 Mon Sep 17 00:00:00 2001 From: Alexander Barkov Date: Mon, 3 Aug 2020 13:56:10 +0400 Subject: MDEV-23032 FLOOR()/CEIL() incorrectly calculate the precision of a DECIMAL(M,D) column The code in Item_func_int_val::fix_length_and_dec_int_or_decimal() calculated badly the result data type for FLOOR()/CEIL(), so for example the decimal(38,10) input created a decimal(28,0) result. That was not correct, because one extra integer digit is needed. floor(-9.9) -> -10 ceil(9.9) -> 10 Rewritting the code in a more straightforward way. Additional changes: - FLOOR() now takes into account the presence of the UNSIGNED flag of the argument: FLOOR(unsigned decimal) does not need an extra digits. - FLOOR()/CEILING() now preserve the unsigned flag in the result data type is decimal. These changes give nicer data types. --- mysql-test/main/ctype_latin1.result | 10 ++++++---- 1 file changed, 6 insertions(+), 4 deletions(-) (limited to 'mysql-test/main/ctype_latin1.result') diff --git a/mysql-test/main/ctype_latin1.result b/mysql-test/main/ctype_latin1.result index a4925af69ae..b4005a4a73b 100644 --- a/mysql-test/main/ctype_latin1.result +++ b/mysql-test/main/ctype_latin1.result @@ -1066,21 +1066,23 @@ drop table t1; select hex(concat(ceiling(0.5))); hex(concat(ceiling(0.5))) 31 -create table t1 as select concat(ceiling(0.5)) as c1; +create table t1 as select ceiling(0.5) as c0, concat(ceiling(0.5)) as c1; show create table t1; Table Create Table t1 CREATE TABLE `t1` ( - `c1` varchar(4) DEFAULT NULL + `c0` int(3) NOT NULL, + `c1` varchar(3) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 drop table t1; select hex(concat(floor(0.5))); hex(concat(floor(0.5))) 30 -create table t1 as select concat(floor(0.5)) as c1; +create table t1 as select floor(0.5) as c0, concat(floor(0.5)) as c1; show create table t1; Table Create Table t1 CREATE TABLE `t1` ( - `c1` varchar(4) DEFAULT NULL + `c0` int(3) NOT NULL, + `c1` varchar(3) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 drop table t1; select hex(concat(round(0.5))); -- cgit v1.2.1