diff options
| -rw-r--r-- | mysql-test/r/func_math.result | 718 | ||||
| -rw-r--r-- | mysql-test/t/func_math.test | 47 | ||||
| -rw-r--r-- | sql/item_func.cc | 3 |
3 files changed, 768 insertions, 0 deletions
diff --git a/mysql-test/r/func_math.result b/mysql-test/r/func_math.result index c92549ae45e..23815bcfed4 100644 --- a/mysql-test/r/func_math.result +++ b/mysql-test/r/func_math.result @@ -994,5 +994,723 @@ SELECT -9223372036854775808 MOD -9223372036854775808; -9223372036854775808 MOD -9223372036854775808 0 # +# MDEV-22503 MDB limits DECIMAL column precision to 16 doing CTAS with floor/ceil over DECIMAL(X,Y) where X > 16 +# +CREATE TABLE t44 (d1 decimal(38,0) DEFAULT NULL); +INSERT INTO t44 VALUES (12345678901234567890123456789012345678); +SELECT FLOOR(d1) FROM t44; +FLOOR(d1) +12345678901234567890123456789012345678 +CREATE TABLE t45 AS SELECT FLOOR(d1) FROM t44; +SELECT * FROM t45; +FLOOR(d1) +12345678901234567890123456789012345678 +SHOW CREATE TABLE t45; +Table Create Table +t45 CREATE TABLE `t45` ( + `FLOOR(d1)` decimal(38,0) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +DROP TABLE t44, t45; +CREATE PROCEDURE p1(prec INT, scale INT) +BEGIN +DECLARE maxval VARCHAR(128) DEFAULT ''; +SET @type= CONCAT('DECIMAL(', prec, ',', scale,')'); +SET @stmt= CONCAT('CREATE TABLE t1 (a ', @type, ',b ', @type, 'unsigned)'); +PREPARE stmt FROM @stmt; +EXECUTE stmt; +DEALLOCATE PREPARE stmt; +SET maxval= CONCAT(REPEAT('9', prec-scale), '.', REPEAT('9',scale)); +INSERT INTO t1 VALUES (maxval, maxval); +CREATE TABLE t2 AS SELECT a, b, FLOOR(a) AS fa, FLOOR(b) AS fb FROM t1; +SHOW CREATE TABLE t2; +SELECT * FROM t2; +DROP TABLE t1, t2; +END; +$$ +CREATE PROCEDURE p2(prec INT) +BEGIN +DECLARE scale INT DEFAULT 0; +WHILE scale < prec AND scale <= 30 DO +CALL p1(prec, scale); +SET scale= scale + 1; +END WHILE; +END; +$$ +CALL p2(38); +Table t2 +Create Table CREATE TABLE `t2` ( + `a` decimal(38,0) DEFAULT NULL, + `b` decimal(38,0) unsigned DEFAULT NULL, + `fa` decimal(38,0) DEFAULT NULL, + `fb` decimal(38,0) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +a 99999999999999999999999999999999999999 +b 99999999999999999999999999999999999999 +fa 99999999999999999999999999999999999999 +fb 99999999999999999999999999999999999999 +Table t2 +Create Table CREATE TABLE `t2` ( + `a` decimal(38,1) DEFAULT NULL, + `b` decimal(38,1) unsigned DEFAULT NULL, + `fa` decimal(37,0) DEFAULT NULL, + `fb` decimal(37,0) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +a 9999999999999999999999999999999999999.9 +b 9999999999999999999999999999999999999.9 +fa 9999999999999999999999999999999999999 +fb 9999999999999999999999999999999999999 +Table t2 +Create Table CREATE TABLE `t2` ( + `a` decimal(38,2) DEFAULT NULL, + `b` decimal(38,2) unsigned DEFAULT NULL, + `fa` decimal(36,0) DEFAULT NULL, + `fb` decimal(36,0) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +a 999999999999999999999999999999999999.99 +b 999999999999999999999999999999999999.99 +fa 999999999999999999999999999999999999 +fb 999999999999999999999999999999999999 +Table t2 +Create Table CREATE TABLE `t2` ( + `a` decimal(38,3) DEFAULT NULL, + `b` decimal(38,3) unsigned DEFAULT NULL, + `fa` decimal(35,0) DEFAULT NULL, + `fb` decimal(35,0) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +a 99999999999999999999999999999999999.999 +b 99999999999999999999999999999999999.999 +fa 99999999999999999999999999999999999 +fb 99999999999999999999999999999999999 +Table t2 +Create Table CREATE TABLE `t2` ( + `a` decimal(38,4) DEFAULT NULL, + `b` decimal(38,4) unsigned DEFAULT NULL, + `fa` decimal(34,0) DEFAULT NULL, + `fb` decimal(34,0) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +a 9999999999999999999999999999999999.9999 +b 9999999999999999999999999999999999.9999 +fa 9999999999999999999999999999999999 +fb 9999999999999999999999999999999999 +Table t2 +Create Table CREATE TABLE `t2` ( + `a` decimal(38,5) DEFAULT NULL, + `b` decimal(38,5) unsigned DEFAULT NULL, + `fa` decimal(33,0) DEFAULT NULL, + `fb` decimal(33,0) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +a 999999999999999999999999999999999.99999 +b 999999999999999999999999999999999.99999 +fa 999999999999999999999999999999999 +fb 999999999999999999999999999999999 +Table t2 +Create Table CREATE TABLE `t2` ( + `a` decimal(38,6) DEFAULT NULL, + `b` decimal(38,6) unsigned DEFAULT NULL, + `fa` decimal(32,0) DEFAULT NULL, + `fb` decimal(32,0) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +a 99999999999999999999999999999999.999999 +b 99999999999999999999999999999999.999999 +fa 99999999999999999999999999999999 +fb 99999999999999999999999999999999 +Table t2 +Create Table CREATE TABLE `t2` ( + `a` decimal(38,7) DEFAULT NULL, + `b` decimal(38,7) unsigned DEFAULT NULL, + `fa` decimal(31,0) DEFAULT NULL, + `fb` decimal(31,0) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +a 9999999999999999999999999999999.9999999 +b 9999999999999999999999999999999.9999999 +fa 9999999999999999999999999999999 +fb 9999999999999999999999999999999 +Table t2 +Create Table CREATE TABLE `t2` ( + `a` decimal(38,8) DEFAULT NULL, + `b` decimal(38,8) unsigned DEFAULT NULL, + `fa` decimal(30,0) DEFAULT NULL, + `fb` decimal(30,0) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +a 999999999999999999999999999999.99999999 +b 999999999999999999999999999999.99999999 +fa 999999999999999999999999999999 +fb 999999999999999999999999999999 +Table t2 +Create Table CREATE TABLE `t2` ( + `a` decimal(38,9) DEFAULT NULL, + `b` decimal(38,9) unsigned DEFAULT NULL, + `fa` decimal(29,0) DEFAULT NULL, + `fb` decimal(29,0) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +a 99999999999999999999999999999.999999999 +b 99999999999999999999999999999.999999999 +fa 99999999999999999999999999999 +fb 99999999999999999999999999999 +Table t2 +Create Table CREATE TABLE `t2` ( + `a` decimal(38,10) DEFAULT NULL, + `b` decimal(38,10) unsigned DEFAULT NULL, + `fa` decimal(28,0) DEFAULT NULL, + `fb` decimal(28,0) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +a 9999999999999999999999999999.9999999999 +b 9999999999999999999999999999.9999999999 +fa 9999999999999999999999999999 +fb 9999999999999999999999999999 +Table t2 +Create Table CREATE TABLE `t2` ( + `a` decimal(38,11) DEFAULT NULL, + `b` decimal(38,11) unsigned DEFAULT NULL, + `fa` decimal(27,0) DEFAULT NULL, + `fb` decimal(27,0) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +a 999999999999999999999999999.99999999999 +b 999999999999999999999999999.99999999999 +fa 999999999999999999999999999 +fb 999999999999999999999999999 +Table t2 +Create Table CREATE TABLE `t2` ( + `a` decimal(38,12) DEFAULT NULL, + `b` decimal(38,12) unsigned DEFAULT NULL, + `fa` decimal(26,0) DEFAULT NULL, + `fb` decimal(26,0) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +a 99999999999999999999999999.999999999999 +b 99999999999999999999999999.999999999999 +fa 99999999999999999999999999 +fb 99999999999999999999999999 +Table t2 +Create Table CREATE TABLE `t2` ( + `a` decimal(38,13) DEFAULT NULL, + `b` decimal(38,13) unsigned DEFAULT NULL, + `fa` decimal(25,0) DEFAULT NULL, + `fb` decimal(25,0) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +a 9999999999999999999999999.9999999999999 +b 9999999999999999999999999.9999999999999 +fa 9999999999999999999999999 +fb 9999999999999999999999999 +Table t2 +Create Table CREATE TABLE `t2` ( + `a` decimal(38,14) DEFAULT NULL, + `b` decimal(38,14) unsigned DEFAULT NULL, + `fa` decimal(24,0) DEFAULT NULL, + `fb` decimal(24,0) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +a 999999999999999999999999.99999999999999 +b 999999999999999999999999.99999999999999 +fa 999999999999999999999999 +fb 999999999999999999999999 +Table t2 +Create Table CREATE TABLE `t2` ( + `a` decimal(38,15) DEFAULT NULL, + `b` decimal(38,15) unsigned DEFAULT NULL, + `fa` decimal(23,0) DEFAULT NULL, + `fb` decimal(23,0) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +a 99999999999999999999999.999999999999999 +b 99999999999999999999999.999999999999999 +fa 99999999999999999999999 +fb 99999999999999999999999 +Table t2 +Create Table CREATE TABLE `t2` ( + `a` decimal(38,16) DEFAULT NULL, + `b` decimal(38,16) unsigned DEFAULT NULL, + `fa` decimal(22,0) DEFAULT NULL, + `fb` decimal(22,0) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +a 9999999999999999999999.9999999999999999 +b 9999999999999999999999.9999999999999999 +fa 9999999999999999999999 +fb 9999999999999999999999 +Table t2 +Create Table CREATE TABLE `t2` ( + `a` decimal(38,17) DEFAULT NULL, + `b` decimal(38,17) unsigned DEFAULT NULL, + `fa` decimal(21,0) DEFAULT NULL, + `fb` decimal(21,0) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +a 999999999999999999999.99999999999999999 +b 999999999999999999999.99999999999999999 +fa 999999999999999999999 +fb 999999999999999999999 +Table t2 +Create Table CREATE TABLE `t2` ( + `a` decimal(38,18) DEFAULT NULL, + `b` decimal(38,18) unsigned DEFAULT NULL, + `fa` decimal(20,0) DEFAULT NULL, + `fb` decimal(20,0) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +a 99999999999999999999.999999999999999999 +b 99999999999999999999.999999999999999999 +fa 99999999999999999999 +fb 99999999999999999999 +Table t2 +Create Table CREATE TABLE `t2` ( + `a` decimal(38,19) DEFAULT NULL, + `b` decimal(38,19) unsigned DEFAULT NULL, + `fa` decimal(19,0) DEFAULT NULL, + `fb` decimal(19,0) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +a 9999999999999999999.9999999999999999999 +b 9999999999999999999.9999999999999999999 +fa 9999999999999999999 +fb 9999999999999999999 +Table t2 +Create Table CREATE TABLE `t2` ( + `a` decimal(38,20) DEFAULT NULL, + `b` decimal(38,20) unsigned DEFAULT NULL, + `fa` decimal(18,0) DEFAULT NULL, + `fb` bigint(17) unsigned DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +a 999999999999999999.99999999999999999999 +b 999999999999999999.99999999999999999999 +fa 999999999999999999 +fb 999999999999999999 +Table t2 +Create Table CREATE TABLE `t2` ( + `a` decimal(38,21) DEFAULT NULL, + `b` decimal(38,21) unsigned DEFAULT NULL, + `fa` bigint(17) DEFAULT NULL, + `fb` bigint(17) unsigned DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +a 99999999999999999.999999999999999999999 +b 99999999999999999.999999999999999999999 +fa 99999999999999999 +fb 99999999999999999 +Table t2 +Create Table CREATE TABLE `t2` ( + `a` decimal(38,22) DEFAULT NULL, + `b` decimal(38,22) unsigned DEFAULT NULL, + `fa` bigint(17) DEFAULT NULL, + `fb` bigint(17) unsigned DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +a 9999999999999999.9999999999999999999999 +b 9999999999999999.9999999999999999999999 +fa 9999999999999999 +fb 9999999999999999 +Table t2 +Create Table CREATE TABLE `t2` ( + `a` decimal(38,23) DEFAULT NULL, + `b` decimal(38,23) unsigned DEFAULT NULL, + `fa` bigint(17) DEFAULT NULL, + `fb` bigint(17) unsigned DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +a 999999999999999.99999999999999999999999 +b 999999999999999.99999999999999999999999 +fa 999999999999999 +fb 999999999999999 +Table t2 +Create Table CREATE TABLE `t2` ( + `a` decimal(38,24) DEFAULT NULL, + `b` decimal(38,24) unsigned DEFAULT NULL, + `fa` bigint(17) DEFAULT NULL, + `fb` bigint(16) unsigned DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +a 99999999999999.999999999999999999999999 +b 99999999999999.999999999999999999999999 +fa 99999999999999 +fb 99999999999999 +Table t2 +Create Table CREATE TABLE `t2` ( + `a` decimal(38,25) DEFAULT NULL, + `b` decimal(38,25) unsigned DEFAULT NULL, + `fa` bigint(16) DEFAULT NULL, + `fb` bigint(15) unsigned DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +a 9999999999999.9999999999999999999999999 +b 9999999999999.9999999999999999999999999 +fa 9999999999999 +fb 9999999999999 +Table t2 +Create Table CREATE TABLE `t2` ( + `a` decimal(38,26) DEFAULT NULL, + `b` decimal(38,26) unsigned DEFAULT NULL, + `fa` bigint(15) DEFAULT NULL, + `fb` bigint(14) unsigned DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +a 999999999999.99999999999999999999999999 +b 999999999999.99999999999999999999999999 +fa 999999999999 +fb 999999999999 +Table t2 +Create Table CREATE TABLE `t2` ( + `a` decimal(38,27) DEFAULT NULL, + `b` decimal(38,27) unsigned DEFAULT NULL, + `fa` bigint(14) DEFAULT NULL, + `fb` bigint(13) unsigned DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +a 99999999999.999999999999999999999999999 +b 99999999999.999999999999999999999999999 +fa 99999999999 +fb 99999999999 +Table t2 +Create Table CREATE TABLE `t2` ( + `a` decimal(38,28) DEFAULT NULL, + `b` decimal(38,28) unsigned DEFAULT NULL, + `fa` bigint(13) DEFAULT NULL, + `fb` bigint(12) unsigned DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +a 9999999999.9999999999999999999999999999 +b 9999999999.9999999999999999999999999999 +fa 9999999999 +fb 9999999999 +Table t2 +Create Table CREATE TABLE `t2` ( + `a` decimal(38,29) DEFAULT NULL, + `b` decimal(38,29) unsigned DEFAULT NULL, + `fa` bigint(12) DEFAULT NULL, + `fb` int(11) unsigned DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +a 999999999.99999999999999999999999999999 +b 999999999.99999999999999999999999999999 +fa 999999999 +fb 999999999 +Table t2 +Create Table CREATE TABLE `t2` ( + `a` decimal(38,30) DEFAULT NULL, + `b` decimal(38,30) unsigned DEFAULT NULL, + `fa` int(11) DEFAULT NULL, + `fb` int(10) unsigned DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +a 99999999.999999999999999999999999999999 +b 99999999.999999999999999999999999999999 +fa 99999999 +fb 99999999 +CALL p2(30); +Table t2 +Create Table CREATE TABLE `t2` ( + `a` decimal(30,0) DEFAULT NULL, + `b` decimal(30,0) unsigned DEFAULT NULL, + `fa` decimal(30,0) DEFAULT NULL, + `fb` decimal(31,0) unsigned DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +a 999999999999999999999999999999 +b 999999999999999999999999999999 +fa 999999999999999999999999999999 +fb 999999999999999999999999999999 +Table t2 +Create Table CREATE TABLE `t2` ( + `a` decimal(30,1) DEFAULT NULL, + `b` decimal(30,1) unsigned DEFAULT NULL, + `fa` decimal(29,0) DEFAULT NULL, + `fb` decimal(30,0) unsigned DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +a 99999999999999999999999999999.9 +b 99999999999999999999999999999.9 +fa 99999999999999999999999999999 +fb 99999999999999999999999999999 +Table t2 +Create Table CREATE TABLE `t2` ( + `a` decimal(30,2) DEFAULT NULL, + `b` decimal(30,2) unsigned DEFAULT NULL, + `fa` decimal(28,0) DEFAULT NULL, + `fb` decimal(29,0) unsigned DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +a 9999999999999999999999999999.99 +b 9999999999999999999999999999.99 +fa 9999999999999999999999999999 +fb 9999999999999999999999999999 +Table t2 +Create Table CREATE TABLE `t2` ( + `a` decimal(30,3) DEFAULT NULL, + `b` decimal(30,3) unsigned DEFAULT NULL, + `fa` decimal(27,0) DEFAULT NULL, + `fb` decimal(28,0) unsigned DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +a 999999999999999999999999999.999 +b 999999999999999999999999999.999 +fa 999999999999999999999999999 +fb 999999999999999999999999999 +Table t2 +Create Table CREATE TABLE `t2` ( + `a` decimal(30,4) DEFAULT NULL, + `b` decimal(30,4) unsigned DEFAULT NULL, + `fa` decimal(26,0) DEFAULT NULL, + `fb` decimal(27,0) unsigned DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +a 99999999999999999999999999.9999 +b 99999999999999999999999999.9999 +fa 99999999999999999999999999 +fb 99999999999999999999999999 +Table t2 +Create Table CREATE TABLE `t2` ( + `a` decimal(30,5) DEFAULT NULL, + `b` decimal(30,5) unsigned DEFAULT NULL, + `fa` decimal(25,0) DEFAULT NULL, + `fb` decimal(26,0) unsigned DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +a 9999999999999999999999999.99999 +b 9999999999999999999999999.99999 +fa 9999999999999999999999999 +fb 9999999999999999999999999 +Table t2 +Create Table CREATE TABLE `t2` ( + `a` decimal(30,6) DEFAULT NULL, + `b` decimal(30,6) unsigned DEFAULT NULL, + `fa` decimal(24,0) DEFAULT NULL, + `fb` decimal(25,0) unsigned DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +a 999999999999999999999999.999999 +b 999999999999999999999999.999999 +fa 999999999999999999999999 +fb 999999999999999999999999 +Table t2 +Create Table CREATE TABLE `t2` ( + `a` decimal(30,7) DEFAULT NULL, + `b` decimal(30,7) unsigned DEFAULT NULL, + `fa` decimal(23,0) DEFAULT NULL, + `fb` decimal(24,0) unsigned DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +a 99999999999999999999999.9999999 +b 99999999999999999999999.9999999 +fa 99999999999999999999999 +fb 99999999999999999999999 +Table t2 +Create Table CREATE TABLE `t2` ( + `a` decimal(30,8) DEFAULT NULL, + `b` decimal(30,8) unsigned DEFAULT NULL, + `fa` decimal(22,0) DEFAULT NULL, + `fb` decimal(23,0) unsigned DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +a 9999999999999999999999.99999999 +b 9999999999999999999999.99999999 +fa 9999999999999999999999 +fb 9999999999999999999999 +Table t2 +Create Table CREATE TABLE `t2` ( + `a` decimal(30,9) DEFAULT NULL, + `b` decimal(30,9) unsigned DEFAULT NULL, + `fa` decimal(21,0) DEFAULT NULL, + `fb` decimal(22,0) unsigned DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +a 999999999999999999999.999999999 +b 999999999999999999999.999999999 +fa 999999999999999999999 +fb 999999999999999999999 +Table t2 +Create Table CREATE TABLE `t2` ( + `a` decimal(30,10) DEFAULT NULL, + `b` decimal(30,10) unsigned DEFAULT NULL, + `fa` decimal(20,0) DEFAULT NULL, + `fb` decimal(21,0) unsigned DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +a 99999999999999999999.9999999999 +b 99999999999999999999.9999999999 +fa 99999999999999999999 +fb 99999999999999999999 +Table t2 +Create Table CREATE TABLE `t2` ( + `a` decimal(30,11) DEFAULT NULL, + `b` decimal(30,11) unsigned DEFAULT NULL, + `fa` decimal(19,0) DEFAULT NULL, + `fb` decimal(20,0) unsigned DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +a 9999999999999999999.99999999999 +b 9999999999999999999.99999999999 +fa 9999999999999999999 +fb 9999999999999999999 +Table t2 +Create Table CREATE TABLE `t2` ( + `a` decimal(30,12) DEFAULT NULL, + `b` decimal(30,12) unsigned DEFAULT NULL, + `fa` decimal(18,0) DEFAULT NULL, + `fb` bigint(17) unsigned DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +a 999999999999999999.999999999999 +b 999999999999999999.999999999999 +fa 999999999999999999 +fb 999999999999999999 +Table t2 +Create Table CREATE TABLE `t2` ( + `a` decimal(30,13) DEFAULT NULL, + `b` decimal(30,13) unsigned DEFAULT NULL, + `fa` bigint(17) DEFAULT NULL, + `fb` bigint(17) unsigned DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +a 99999999999999999.9999999999999 +b 99999999999999999.9999999999999 +fa 99999999999999999 +fb 99999999999999999 +Table t2 +Create Table CREATE TABLE `t2` ( + `a` decimal(30,14) DEFAULT NULL, + `b` decimal(30,14) unsigned DEFAULT NULL, + `fa` bigint(17) DEFAULT NULL, + `fb` bigint(17) unsigned DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +a 9999999999999999.99999999999999 +b 9999999999999999.99999999999999 +fa 9999999999999999 +fb 9999999999999999 +Table t2 +Create Table CREATE TABLE `t2` ( + `a` decimal(30,15) DEFAULT NULL, + `b` decimal(30,15) unsigned DEFAULT NULL, + `fa` bigint(17) DEFAULT NULL, + `fb` bigint(17) unsigned DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +a 999999999999999.999999999999999 +b 999999999999999.999999999999999 +fa 999999999999999 +fb 999999999999999 +Table t2 +Create Table CREATE TABLE `t2` ( + `a` decimal(30,16) DEFAULT NULL, + `b` decimal(30,16) unsigned DEFAULT NULL, + `fa` bigint(17) DEFAULT NULL, + `fb` bigint(16) unsigned DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +a 99999999999999.9999999999999999 +b 99999999999999.9999999999999999 +fa 99999999999999 +fb 99999999999999 +Table t2 +Create Table CREATE TABLE `t2` ( + `a` decimal(30,17) DEFAULT NULL, + `b` decimal(30,17) unsigned DEFAULT NULL, + `fa` bigint(16) DEFAULT NULL, + `fb` bigint(15) unsigned DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +a 9999999999999.99999999999999999 +b 9999999999999.99999999999999999 +fa 9999999999999 +fb 9999999999999 +Table t2 +Create Table CREATE TABLE `t2` ( + `a` decimal(30,18) DEFAULT NULL, + `b` decimal(30,18) unsigned DEFAULT NULL, + `fa` bigint(15) DEFAULT NULL, + `fb` bigint(14) unsigned DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +a 999999999999.999999999999999999 +b 999999999999.999999999999999999 +fa 999999999999 +fb 999999999999 +Table t2 +Create Table CREATE TABLE `t2` ( + `a` decimal(30,19) DEFAULT NULL, + `b` decimal(30,19) unsigned DEFAULT NULL, + `fa` bigint(14) DEFAULT NULL, + `fb` bigint(13) unsigned DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +a 99999999999.9999999999999999999 +b 99999999999.9999999999999999999 +fa 99999999999 +fb 99999999999 +Table t2 +Create Table CREATE TABLE `t2` ( + `a` decimal(30,20) DEFAULT NULL, + `b` decimal(30,20) unsigned DEFAULT NULL, + `fa` bigint(13) DEFAULT NULL, + `fb` bigint(12) unsigned DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +a 9999999999.99999999999999999999 +b 9999999999.99999999999999999999 +fa 9999999999 +fb 9999999999 +Table t2 +Create Table CREATE TABLE `t2` ( + `a` decimal(30,21) DEFAULT NULL, + `b` decimal(30,21) unsigned DEFAULT NULL, + `fa` bigint(12) DEFAULT NULL, + `fb` int(11) unsigned DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +a 999999999.999999999999999999999 +b 999999999.999999999999999999999 +fa 999999999 +fb 999999999 +Table t2 +Create Table CREATE TABLE `t2` ( + `a` decimal(30,22) DEFAULT NULL, + `b` decimal(30,22) unsigned DEFAULT NULL, + `fa` int(11) DEFAULT NULL, + `fb` int(10) unsigned DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +a 99999999.9999999999999999999999 +b 99999999.9999999999999999999999 +fa 99999999 +fb 99999999 +Table t2 +Create Table CREATE TABLE `t2` ( + `a` decimal(30,23) DEFAULT NULL, + `b` decimal(30,23) unsigned DEFAULT NULL, + `fa` int(10) DEFAULT NULL, + `fb` int(9) unsigned DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +a 9999999.99999999999999999999999 +b 9999999.99999999999999999999999 +fa 9999999 +fb 9999999 +Table t2 +Create Table CREATE TABLE `t2` ( + `a` decimal(30,24) DEFAULT NULL, + `b` decimal(30,24) unsigned DEFAULT NULL, + `fa` int(9) DEFAULT NULL, + `fb` int(8) unsigned DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +a 999999.999999999999999999999999 +b 999999.999999999999999999999999 +fa 999999 +fb 999999 +Table t2 +Create Table CREATE TABLE `t2` ( + `a` decimal(30,25) DEFAULT NULL, + `b` decimal(30,25) unsigned DEFAULT NULL, + `fa` int(8) DEFAULT NULL, + `fb` int(7) unsigned DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +a 99999.9999999999999999999999999 +b 99999.9999999999999999999999999 +fa 99999 +fb 99999 +Table t2 +Create Table CREATE TABLE `t2` ( + `a` decimal(30,26) DEFAULT NULL, + `b` decimal(30,26) unsigned DEFAULT NULL, + `fa` int(7) DEFAULT NULL, + `fb` int(6) unsigned DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +a 9999.99999999999999999999999999 +b 9999.99999999999999999999999999 +fa 9999 +fb 9999 +Table t2 +Create Table CREATE TABLE `t2` ( + `a` decimal(30,27) DEFAULT NULL, + `b` decimal(30,27) unsigned DEFAULT NULL, + `fa` int(6) DEFAULT NULL, + `fb` int(5) unsigned DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +a 999.999999999999999999999999999 +b 999.999999999999999999999999999 +fa 999 +fb 999 +Table t2 +Create Table CREATE TABLE `t2` ( + `a` decimal(30,28) DEFAULT NULL, + `b` decimal(30,28) unsigned DEFAULT NULL, + `fa` int(5) DEFAULT NULL, + `fb` int(4) unsigned DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +a 99.9999999999999999999999999999 +b 99.9999999999999999999999999999 +fa 99 +fb 99 +Table t2 +Create Table CREATE TABLE `t2` ( + `a` decimal(30,29) DEFAULT NULL, + `b` decimal(30,29) unsigned DEFAULT NULL, + `fa` int(4) DEFAULT NULL, + `fb` int(3) unsigned DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +a 9.99999999999999999999999999999 +b 9.99999999999999999999999999999 +fa 9 +fb 9 +DROP PROCEDURE p2; +DROP PROCEDURE p1; +# # End of 10.1 tests # diff --git a/mysql-test/t/func_math.test b/mysql-test/t/func_math.test index 89e9c4f4d49..6edc342e9df 100644 --- a/mysql-test/t/func_math.test +++ b/mysql-test/t/func_math.test @@ -720,6 +720,53 @@ SELECT -9223372036854775808 MOD 9223372036854775808; SELECT -9223372036854775808 MOD -9223372036854775808; +--echo # +--echo # MDEV-22503 MDB limits DECIMAL column precision to 16 doing CTAS with floor/ceil over DECIMAL(X,Y) where X > 16 +--echo # + +CREATE TABLE t44 (d1 decimal(38,0) DEFAULT NULL); +INSERT INTO t44 VALUES (12345678901234567890123456789012345678); +SELECT FLOOR(d1) FROM t44; +CREATE TABLE t45 AS SELECT FLOOR(d1) FROM t44; +SELECT * FROM t45; +SHOW CREATE TABLE t45; +DROP TABLE t44, t45; + + +DELIMITER $$; +CREATE PROCEDURE p1(prec INT, scale INT) +BEGIN + DECLARE maxval VARCHAR(128) DEFAULT ''; + SET @type= CONCAT('DECIMAL(', prec, ',', scale,')'); + SET @stmt= CONCAT('CREATE TABLE t1 (a ', @type, ',b ', @type, 'unsigned)'); + PREPARE stmt FROM @stmt; + EXECUTE stmt; + DEALLOCATE PREPARE stmt; + SET maxval= CONCAT(REPEAT('9', prec-scale), '.', REPEAT('9',scale)); + INSERT INTO t1 VALUES (maxval, maxval); + CREATE TABLE t2 AS SELECT a, b, FLOOR(a) AS fa, FLOOR(b) AS fb FROM t1; + SHOW CREATE TABLE t2; + SELECT * FROM t2; + DROP TABLE t1, t2; +END; +$$ +CREATE PROCEDURE p2(prec INT) +BEGIN + DECLARE scale INT DEFAULT 0; + WHILE scale < prec AND scale <= 30 DO + CALL p1(prec, scale); + SET scale= scale + 1; + END WHILE; +END; +$$ +DELIMITER ;$$ +--vertical_results +CALL p2(38); +CALL p2(30); +--horizontal_results +DROP PROCEDURE p2; +DROP PROCEDURE p1; + --echo # --echo # End of 10.1 tests diff --git a/sql/item_func.cc b/sql/item_func.cc index 8407dc881be..f76f6515365 100644 --- a/sql/item_func.cc +++ b/sql/item_func.cc @@ -2337,6 +2337,9 @@ void Item_func_int_val::fix_length_and_dec() if ((args[0]->max_length - args[0]->decimals) >= (DECIMAL_LONGLONG_DIGITS - 2)) { + fix_char_length( + my_decimal_precision_to_length_no_truncation( + args[0]->decimal_int_part(), 0, false)); set_handler_by_result_type(DECIMAL_RESULT); } else |