MDEV-24790 CAST('0e1111111111' AS DECIMAL(38,0)) returns a wrong result

5 files changed, 198 insertions, 20 deletions

diff --git a/mysql-test/r/type_decimal.result b/mysql-test/r/type_decimal.result
index 2a410d2ce97..519f0d547a2 100644
--- a/mysql-test/r/type_decimal.result
+++ b/mysql-test/r/type_decimal.result
@@ -176,9 +176,8 @@ Note	1265	Data truncated for column 'a' at row 2
 insert ignore into t1 values ("1e+18446744073709551615"),("1e+18446744073709551616"),("1e-9223372036854775807"),("1e-9223372036854775809");
 Warnings:
 Warning	1264	Out of range value for column 'a' at row 1
-Warning	1366	Incorrect decimal value: '1e+18446744073709551616' for column `test`.`t1`.`a` at row 2
+Warning	1264	Out of range value for column 'a' at row 2
 Note	1265	Data truncated for column 'a' at row 3
-Warning	1366	Incorrect decimal value: '1e-9223372036854775809' for column `test`.`t1`.`a` at row 4
 insert ignore into t1 values ("123.4e"),("123.4e+2"),("123.4e-2"),("123e1"),("123e+0");
 Warnings:
 Warning	1265	Data truncated for column 'a' at row 1
@@ -209,7 +208,7 @@ a
 99999999.99
 0.00
 99999999.99
-0.00
+99999999.99
 0.00
 0.00
 123.40
@@ -1078,5 +1077,89 @@ t1	CREATE TABLE `t1` (
 DROP TABLE t1;
 DROP TABLE t1dec102;
 #
+# MDEV-24790 CAST('0e1111111111' AS DECIMAL(38,0)) returns a wrong result
+#
+SELECT CAST('0e111111111' AS DECIMAL(38,0)) AS a;
+a
+0
+SELECT CAST('0e1111111111' AS DECIMAL(38,0)) AS a;
+a
+0
+SELECT CAST('.00000000000000000000000000000000000001e111111111111111111111' AS DECIMAL(38,0)) AS a;
+a
+99999999999999999999999999999999999999
+Warnings:
+Warning	1916	Got overflow when converting '' to DECIMAL. Value truncated
+Warning	1292	Truncated incorrect DECIMAL value: '.00000000000000000000000000000000000001e111111111111111111111'
+Warning	1264	Out of range value for column 'a' at row 1
+CREATE TABLE t1 (str VARCHAR(128), comment VARCHAR(128));
+INSERT INTO t1 VALUES
+('0e111111111111111111111', 'Zero mantissa and a huge positive exponent'),
+('1e111111111111111111111', 'Non-zero mantissa, huge positive exponent'),
+('0e-111111111111111111111', 'Zero mantissa and a huge negative exponent'),
+('1e-111111111111111111111', 'Non-zero mantissa and a huge negative exponent');
+BEGIN NOT ATOMIC
+DECLARE done INT DEFAULT FALSE;
+DECLARE vstr, vcomment VARCHAR(128);
+DECLARE cur1 CURSOR FOR SELECT str, comment FROM t1 ORDER BY str;
+DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
+OPEN cur1;
+read_loop:
+LOOP
+FETCH cur1 INTO vstr, vcomment;
+IF done THEN
+LEAVE read_loop;
+END IF;
+SELECT vstr AS `--------`, vcomment AS `--------`;
+SELECT CAST(str AS DECIMAL(38,0)) FROM t1 WHERE str=vstr;
+SHOW WARNINGS;
+SELECT CAST(CONCAT(str,'garbage') AS DECIMAL(38,0)) FROM t1 WHERE str=vstr;
+SHOW WARNINGS;
+END LOOP;
+END;
+$$
+--------	--------
+0e-111111111111111111111	Zero mantissa and a huge negative exponent
+CAST(str AS DECIMAL(38,0))
+0
+Level	Code	Message
+CAST(CONCAT(str,'garbage') AS DECIMAL(38,0))
+0
+Level	Code	Message
+Warning	1292	Truncated incorrect DECIMAL value: '0e-111111111111111111111garbage'
+--------	--------
+0e111111111111111111111	Zero mantissa and a huge positive exponent
+CAST(str AS DECIMAL(38,0))
+0
+Level	Code	Message
+CAST(CONCAT(str,'garbage') AS DECIMAL(38,0))
+0
+Level	Code	Message
+Warning	1292	Truncated incorrect DECIMAL value: '0e111111111111111111111garbage'
+--------	--------
+1e-111111111111111111111	Non-zero mantissa and a huge negative exponent
+CAST(str AS DECIMAL(38,0))
+0
+Level	Code	Message
+CAST(CONCAT(str,'garbage') AS DECIMAL(38,0))
+0
+Level	Code	Message
+Warning	1292	Truncated incorrect DECIMAL value: '1e-111111111111111111111garbage'
+--------	--------
+1e111111111111111111111	Non-zero mantissa, huge positive exponent
+CAST(str AS DECIMAL(38,0))
+99999999999999999999999999999999999999
+Level	Code	Message
+Warning	1916	Got overflow when converting '' to DECIMAL. Value truncated
+Warning	1292	Truncated incorrect DECIMAL value: '1e111111111111111111111'
+Warning	1264	Out of range value for column 'CAST(str AS DECIMAL(38,0))' at row 1
+CAST(CONCAT(str,'garbage') AS DECIMAL(38,0))
+99999999999999999999999999999999999999
+Level	Code	Message
+Warning	1916	Got overflow when converting '' to DECIMAL. Value truncated
+Warning	1292	Truncated incorrect DECIMAL value: '1e111111111111111111111garbage'
+Warning	1264	Out of range value for column 'CAST(CONCAT(str,'garbage') AS DECIMAL(38,0))' at row 1
+DROP TABLE t1;
+#
 # End of 10.2 tests
 #
diff --git a/mysql-test/suite/engines/iuds/r/insert_decimal.result b/mysql-test/suite/engines/iuds/r/insert_decimal.result
index c0f3224e26f..4a6ad168a1d 100644
--- a/mysql-test/suite/engines/iuds/r/insert_decimal.result
+++ b/mysql-test/suite/engines/iuds/r/insert_decimal.result
@@ -110,15 +110,12 @@ Warnings:
 Warning	1264	Out of range value for column 'c1' at row 1
 Warning	1264	Out of range value for column 'c2' at row 1
 Warning	1264	Out of range value for column 'c3' at row 1
-Warning	1366	Incorrect decimal value: '1e+18446744073709551616' for column `test`.`t2`.`c1` at row 2
-Warning	1366	Incorrect decimal value: '1e+18446744073709551616' for column `test`.`t2`.`c2` at row 2
-Warning	1366	Incorrect decimal value: '1e+18446744073709551616' for column `test`.`t2`.`c3` at row 2
+Warning	1264	Out of range value for column 'c1' at row 2
+Warning	1264	Out of range value for column 'c2' at row 2
+Warning	1264	Out of range value for column 'c3' at row 2
 Note	1265	Data truncated for column 'c1' at row 3
 Note	1265	Data truncated for column 'c2' at row 3
 Note	1265	Data truncated for column 'c3' at row 3
-Warning	1366	Incorrect decimal value: '1e-9223372036854775809' for column `test`.`t2`.`c1` at row 4
-Warning	1366	Incorrect decimal value: '1e-9223372036854775809' for column `test`.`t2`.`c2` at row 4
-Warning	1366	Incorrect decimal value: '1e-9223372036854775809' for column `test`.`t2`.`c3` at row 4
 SELECT * FROM t1;
 c1	c2	c3	c4
 0.00000	-0.10000	0	13
@@ -142,7 +139,6 @@ c1	c2	c3	c4
 0	0	0	15
 0	0	0	26
 0	0	0	29
-0	0	0	31
 0	0	0	32
 0	0	0	33
 0	0	0	7
@@ -160,6 +156,7 @@ c1	c2	c3	c4
 9999999999	9999999999	9999999999	25
 9999999999	9999999999	9999999999	28
 9999999999	9999999999	9999999999	30
+9999999999	9999999999	9999999999	31
 SELECT count(*) as total_rows, min(c1) as min_value, max(c1) as max_value, sum(c1) as sum, avg(c1) as avg FROM t1;
 total_rows	min_value	max_value	sum	avg
 7	0.00000	99999.99999	212446.04999	30349.435712857
@@ -171,13 +168,13 @@ total_rows	min_value	max_value	sum	avg
 7	0	111111111	111211212	18535202.0000
 SELECT count(*) as total_rows, min(c1) as min_value, max(c1) as max_value, sum(c1) as sum, avg(c1) as avg FROM t2;
 total_rows	min_value	max_value	sum	avg
-30	-9999999999	9999999999	21322222222	710740740.7333
+30	-9999999999	9999999999	31322222221	1044074074.0333
 SELECT count(*) as total_rows, min(c2) as min_value, max(c2) as max_value, sum(c2) as sum, avg(c2) as avg FROM t2;
 total_rows	min_value	max_value	sum	avg
-30	0	9999999999	33444444445	1114814814.8333
+30	0	9999999999	43444444444	1448148148.1333
 SELECT count(*) as total_rows, min(c3) as min_value, max(c3) as max_value, sum(c3) as sum, avg(c3) as avg FROM t2;
 total_rows	min_value	max_value	sum	avg
-30	-9999999999	9999999999	43322222220	1444074074.0000
+30	-9999999999	9999999999	53322222219	1777407407.3000
 SELECT * FROM t1;
 c1	c2	c3	c4
 0.00000	-0.10000	0	13
diff --git a/mysql-test/t/type_decimal.test b/mysql-test/t/type_decimal.test
index 77c9c2875e2..80c196b11f1 100644
--- a/mysql-test/t/type_decimal.test
+++ b/mysql-test/t/type_decimal.test
@@ -670,5 +670,49 @@ DROP TABLE t1;
 DROP TABLE t1dec102;
 
 --echo #
+--echo # MDEV-24790 CAST('0e1111111111' AS DECIMAL(38,0)) returns a wrong result
+--echo #
+
+SELECT CAST('0e111111111' AS DECIMAL(38,0)) AS a;
+SELECT CAST('0e1111111111' AS DECIMAL(38,0)) AS a;
+SELECT CAST('.00000000000000000000000000000000000001e111111111111111111111' AS DECIMAL(38,0)) AS a;
+
+CREATE TABLE t1 (str VARCHAR(128), comment VARCHAR(128));
+INSERT INTO t1 VALUES
+('0e111111111111111111111', 'Zero mantissa and a huge positive exponent'),
+('1e111111111111111111111', 'Non-zero mantissa, huge positive exponent'),
+('0e-111111111111111111111', 'Zero mantissa and a huge negative exponent'),
+('1e-111111111111111111111', 'Non-zero mantissa and a huge negative exponent');
+
+# The loop below issues SHOW WARNINGS manually, disable automatic warnings
+--disable_warnings
+DELIMITER $$;
+BEGIN NOT ATOMIC
+  DECLARE done INT DEFAULT FALSE;
+  DECLARE vstr, vcomment VARCHAR(128);
+  DECLARE cur1 CURSOR FOR SELECT str, comment FROM t1 ORDER BY str;
+  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
+  OPEN cur1;
+read_loop:
+  LOOP
+    FETCH cur1 INTO vstr, vcomment;
+    IF done THEN
+      LEAVE read_loop;
+    END IF;
+    SELECT vstr AS `--------`, vcomment AS `--------`;
+    SELECT CAST(str AS DECIMAL(38,0)) FROM t1 WHERE str=vstr;
+    SHOW WARNINGS;
+    SELECT CAST(CONCAT(str,'garbage') AS DECIMAL(38,0)) FROM t1 WHERE str=vstr;
+    SHOW WARNINGS;
+  END LOOP;
+END;
+$$
+DELIMITER ;$$
+--enable_warnings
+
+
+DROP TABLE t1;
+
+--echo #
 --echo # End of 10.2 tests
 --echo #
diff --git a/storage/tokudb/mysql-test/tokudb/r/type_decimal.result b/storage/tokudb/mysql-test/tokudb/r/type_decimal.result
index 3b82bbcef4f..c01edef283e 100644
--- a/storage/tokudb/mysql-test/tokudb/r/type_decimal.result
+++ b/storage/tokudb/mysql-test/tokudb/r/type_decimal.result
@@ -177,9 +177,8 @@ Note	1265	Data truncated for column 'a' at row 2
 insert into t1 values ("1e+18446744073709551615"),("1e+18446744073709551616"),("1e-9223372036854775807"),("1e-9223372036854775809");
 Warnings:
 Warning	1264	Out of range value for column 'a' at row 1
-Warning	1366	Incorrect decimal value: '1e+18446744073709551616' for column `test`.`t1`.`a` at row 2
+Warning	1264	Out of range value for column 'a' at row 2
 Note	1265	Data truncated for column 'a' at row 3
-Warning	1366	Incorrect decimal value: '1e-9223372036854775809' for column `test`.`t1`.`a` at row 4
 insert into t1 values ("123.4e"),("123.4e+2"),("123.4e-2"),("123e1"),("123e+0");
 Warnings:
 Warning	1265	Data truncated for column 'a' at row 1
@@ -210,7 +209,7 @@ a
 99999999.99
 0.00
 99999999.99
-0.00
+99999999.99
 0.00
 0.00
 123.40
diff --git a/strings/decimal.c b/strings/decimal.c
index 2dd7c960a3e..9d18a9ce52a 100644
--- a/strings/decimal.c
+++ b/strings/decimal.c
@@ -921,20 +921,75 @@ internal_str2dec(const char *from, decimal_t *to, char **end, my_bool fixed)
   if (endp+1 < end_of_string && (*endp == 'e' || *endp == 'E'))
   {
     int str_error;
-    longlong exponent= my_strtoll10(endp+1, (char**) &end_of_string,
+    const char *end_of_exponent= end_of_string;
+    longlong exponent= my_strtoll10(endp+1, (char**) &end_of_exponent,
                                     &str_error);
 
-    if (end_of_string != endp +1)               /* If at least one digit */
+    if (end_of_exponent != endp +1)               /* If at least one digit */
     {
-      *end= (char*) end_of_string;
+      *end= (char*) end_of_exponent;
       if (str_error > 0)
       {
+        if (str_error == MY_ERRNO_ERANGE)
+        {
+          /*
+            Exponent is:
+            - a huge positive number that does not fit into ulonglong
+            - a huge negative number that does not fit into longlong
+            Skip all remaining digits.
+          */
+          for ( ; end_of_exponent < end_of_string &&
+                  my_isdigit(&my_charset_latin1, *end_of_exponent)
+                ; end_of_exponent++)
+          { }
+          *end= (char*) end_of_exponent;
+          if (exponent == ~0)
+          {
+            if (!decimal_is_zero(to))
+            {
+              /*
+                Non-zero mantissa and a huge positive exponent that
+                does not fit into ulonglong, e.g.:
+                  1e111111111111111111111
+              */
+              error= E_DEC_OVERFLOW;
+            }
+            else
+            {
+              /*
+                Zero mantissa and a huge positive exponent that
+                does not fit into ulonglong, e.g.:
+                  0e111111111111111111111
+                Return zero without warnings.
+              */
+            }
+          }
+          else
+          {
+            /*
+              Huge negative exponent that does not fit into longlong, e.g.
+                1e-111111111111111111111
+                0e-111111111111111111111
+              Return zero without warnings.
+            */
+          }
+          goto fatal_error;
+        }
+
+        /*
+          Some other error, e.g. MY_ERRNO_EDOM
+        */
         error= E_DEC_BAD_NUM;
         goto fatal_error;
       }
       if (exponent > INT_MAX/2 || (str_error == 0 && exponent < 0))
       {
-        error= E_DEC_OVERFLOW;
+        /*
+          The exponent fits into ulonglong, but it's still huge, e.g.
+            1e1111111111
+        */
+        if (!decimal_is_zero(to))
+          error= E_DEC_OVERFLOW;
         goto fatal_error;
       }
       if (exponent < INT_MIN/2 && error != E_DEC_OVERFLOW)