summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
Diffstat
-rw-r--r--mysql-test/main/func_math.result8
-rw-r--r--mysql-test/main/func_str.result2
-rw-r--r--mysql-test/main/type_newdecimal.result31
-rw-r--r--mysql-test/main/type_newdecimal.test19
-rw-r--r--sql/item_strfunc.cc30
5 files changed, 82 insertions, 8 deletions
diff --git a/mysql-test/main/func_math.result b/mysql-test/main/func_math.result
index a397f927265..c0072641d3b 100644
--- a/mysql-test/main/func_math.result
+++ b/mysql-test/main/func_math.result
@@ -2295,7 +2295,7 @@ FORMAT(-1e308,2)
SHOW CREATE TABLE t1;
Table Create Table
t1 CREATE TABLE `t1` (
- `FORMAT(-1e308,2)` varchar(416) DEFAULT NULL
+ `FORMAT(-1e308,2)` varchar(417) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
DROP TABLE t1;
CREATE OR REPLACE TABLE t1 AS SELECT FORMAT('-1e308',2);
@@ -2305,7 +2305,7 @@ FORMAT('-1e308',2)
SHOW CREATE TABLE t1;
Table Create Table
t1 CREATE TABLE `t1` (
- `FORMAT('-1e308',2)` varchar(416) DEFAULT NULL
+ `FORMAT('-1e308',2)` varchar(417) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
DROP TABLE t1;
CREATE OR REPLACE TABLE t1 AS SELECT FORMAT(DATE'20191231',0),FORMAT(TIME'99:05:00',0),FORMAT(TIMESTAMP'2019-12-31 23:59:59.123456',0);
@@ -2317,7 +2317,7 @@ Table Create Table
t1 CREATE TABLE `t1` (
`FORMAT(DATE'20191231',0)` varchar(11) DEFAULT NULL,
`FORMAT(TIME'99:05:00',0)` varchar(10) DEFAULT NULL,
- `FORMAT(TIMESTAMP'2019-12-31 23:59:59.123456',0)` varchar(19) DEFAULT NULL
+ `FORMAT(TIMESTAMP'2019-12-31 23:59:59.123456',0)` varchar(21) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
DROP TABLE t1;
CREATE OR REPLACE TABLE t1 (y YEAR);
@@ -3549,7 +3549,7 @@ FORMAT(f,0)
SHOW CREATE TABLE t2;
Table Create Table
t2 CREATE TABLE `t2` (
- `FORMAT(f,0)` varchar(53) DEFAULT NULL
+ `FORMAT(f,0)` varchar(54) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
DROP TABLE t1,t2;
#
diff --git a/mysql-test/main/func_str.result b/mysql-test/main/func_str.result
index 4a01935785c..7a6af68b1d8 100644
--- a/mysql-test/main/func_str.result
+++ b/mysql-test/main/func_str.result
@@ -2708,7 +2708,7 @@ create table t1(a float);
insert into t1 values (1.33);
select format(a, 2) from t1;
Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr
-def format(a, 2) 253 56 4 Y 0 39 8
+def format(a, 2) 253 57 4 Y 0 39 8
format(a, 2)
1.33
drop table t1;
diff --git a/mysql-test/main/type_newdecimal.result b/mysql-test/main/type_newdecimal.result
index 0a631521d49..dd55d230450 100644
--- a/mysql-test/main/type_newdecimal.result
+++ b/mysql-test/main/type_newdecimal.result
@@ -2700,3 +2700,34 @@ ca 1
LENGTH(FLOOR(a)) 1
LENGTH(CEILING(a)) 1
DROP PROCEDURE p1;
+#
+# MDEV-23118 FORMAT(d1,dec) where dec=0/38 and d1 is DECIMAL(38,38) gives incorrect results
+#
+CREATE OR REPLACE TABLE t1 (a DECIMAL(38,38));
+INSERT INTO t1 VALUES (-0.9999999999999999999999999999999999999), (0.9999999999999999999999999999999999999);
+SELECT a, FORMAT(a,0), FORMAT(a,38) FROM t1;
+a -0.99999999999999999999999999999999999990
+FORMAT(a,0) -1
+FORMAT(a,38) -0.99999999999999999999999999999999999990
+a 0.99999999999999999999999999999999999990
+FORMAT(a,0) 1
+FORMAT(a,38) 0.99999999999999999999999999999999999990
+CREATE OR REPLACE TABLE t2 AS SELECT a, FORMAT(a,0), FORMAT(a,38) FROM t1;
+SELECT * FROM t2;
+a -0.99999999999999999999999999999999999990
+FORMAT(a,0) -1
+FORMAT(a,38) -0.99999999999999999999999999999999999990
+a 0.99999999999999999999999999999999999990
+FORMAT(a,0) 1
+FORMAT(a,38) 0.99999999999999999999999999999999999990
+SHOW CREATE TABLE t2;
+Table t2
+Create Table CREATE TABLE `t2` (
+ `a` decimal(38,38) DEFAULT NULL,
+ `FORMAT(a,0)` varchar(2) DEFAULT NULL,
+ `FORMAT(a,38)` varchar(41) DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+DROP TABLE t2,t1;
+#
+# End of 10.4 tests
+#
diff --git a/mysql-test/main/type_newdecimal.test b/mysql-test/main/type_newdecimal.test
index 7cf0a486268..c2b3dc44b37 100644
--- a/mysql-test/main/type_newdecimal.test
+++ b/mysql-test/main/type_newdecimal.test
@@ -1919,3 +1919,22 @@ CALL p1(10,10,' UNSIGNED');
--horizontal_results
DROP PROCEDURE p1;
+
+
+--echo #
+--echo # MDEV-23118 FORMAT(d1,dec) where dec=0/38 and d1 is DECIMAL(38,38) gives incorrect results
+--echo #
+
+--vertical_results
+CREATE OR REPLACE TABLE t1 (a DECIMAL(38,38));
+INSERT INTO t1 VALUES (-0.9999999999999999999999999999999999999), (0.9999999999999999999999999999999999999);
+SELECT a, FORMAT(a,0), FORMAT(a,38) FROM t1;
+CREATE OR REPLACE TABLE t2 AS SELECT a, FORMAT(a,0), FORMAT(a,38) FROM t1;
+SELECT * FROM t2;
+SHOW CREATE TABLE t2;
+DROP TABLE t2,t1;
+--horizontal_results
+
+--echo #
+--echo # End of 10.4 tests
+--echo #
diff --git a/sql/item_strfunc.cc b/sql/item_strfunc.cc
index 0baa762d25f..ca196acaff3 100644
--- a/sql/item_strfunc.cc
+++ b/sql/item_strfunc.cc
@@ -2661,18 +2661,42 @@ String *Item_func_soundex::val_str(String *str)
This should be 'internationalized' sometimes.
*/
-const int FORMAT_MAX_DECIMALS= 30;
+/*
+ The maximum supported decimal scale:
+ 38 - starting from 10.2.1
+ 30 - before 10.2.1
+*/
+const int FORMAT_MAX_DECIMALS= 38;
bool Item_func_format::fix_length_and_dec()
{
uint32 char_length= args[0]->type_handler()->Item_decimal_notation_int_digits(args[0]);
uint dec= FORMAT_MAX_DECIMALS;
- if (args[1]->const_item() && !args[1]->is_expensive() && !args[1]->null_value)
+ /*
+ Format can require one more integer digit if rounding happens:
+ FORMAT(9.9,0) -> '10'
+ Set need_extra_digit_for_rounding to true by default
+ if args[0] has some decimals: if args[1] is not
+ a constant, then format can potentially reduce
+ the number of decimals and round to the next integer.
+ */
+ bool need_extra_digit_for_rounding= args[0]->decimals > 0;
+ if (args[1]->const_item() && !args[1]->is_expensive())
{
Longlong_hybrid tmp= args[1]->to_longlong_hybrid();
- dec= tmp.to_uint(FORMAT_MAX_DECIMALS);
+ if (!args[1]->null_value)
+ {
+ dec= tmp.to_uint(FORMAT_MAX_DECIMALS);
+ need_extra_digit_for_rounding= (dec < args[0]->decimals);
+ }
}
+ /*
+ In case of a data type with zero integer digits, e.g. DECIMAL(4,4),
+ we'll print at least one integer digit.
+ */
+ if (need_extra_digit_for_rounding || !char_length)
+ char_length++;
uint32 max_sep_count= (char_length / 3) + (dec ? 1 : 0) + /*sign*/1;
collation.set(default_charset());
fix_char_length(char_length + max_sep_count + dec);
View Lorry Controller Status