MDEV-8919 Wrong result for CAST(9999999999999999999.0)

2 files changed, 150 insertions, 2 deletions

diff --git a/mysql-test/main/cast.result b/mysql-test/main/cast.result
index 17329cb596f..268999c9ca0 100644
--- a/mysql-test/main/cast.result
+++ b/mysql-test/main/cast.result
@@ -584,6 +584,8 @@ Note	1105	Cast to signed converted positive out-of-range integer to it's negativ
 select cast(1.0e+300 as signed int);
 cast(1.0e+300 as signed int)
 9223372036854775807
+Warnings:
+Note	1916	Got overflow when converting '1e300' to SIGNED BIGINT. Value truncated
 create table t1  select cast(1 as unsigned), cast(1 as signed), cast(1 as double(5,2)), cast(1 as decimal(5,3)), cast("A" as binary), cast("A" as char(100)), cast("2001-1-1" as DATE), cast("2001-1-1" as DATETIME), cast("1:2:3" as TIME);
 show create table t1;
 Table	Create Table
@@ -607,8 +609,8 @@ double_val	cast_val
 -1e30	-9223372036854775808
 1e30	9223372036854775807
 Warnings:
-Warning	1916	Got overflow when converting '-1e30' to INT. Value truncated
-Warning	1916	Got overflow when converting '1e30' to INT. Value truncated
+Note	1916	Got overflow when converting '-1e30' to SIGNED BIGINT. Value truncated
+Note	1916	Got overflow when converting '1e30' to SIGNED BIGINT. Value truncated
 DROP TABLE t1;
 select isnull(date(NULL)), isnull(cast(NULL as DATE));
 isnull(date(NULL))	isnull(cast(NULL as DATE))
@@ -1283,3 +1285,95 @@ SET sql_mode=DEFAULT;
 SELECT CAST(11068046444225730969 AS SIGNED);
 CAST(11068046444225730969 AS SIGNED)
 -7378697629483820647
+#
+# MDEV-8919 Wrong result for CAST(9999999999999999999.0)
+#
+SET sql_mode='';
+SELECT CAST(9999999999999999999e0 AS UNSIGNED);
+CAST(9999999999999999999e0 AS UNSIGNED)
+10000000000000000000
+CREATE TABLE t1 (a BIGINT UNSIGNED);
+INSERT INTO t1 VALUES (9999999999999999999e0);
+SELECT * FROM t1;
+a
+10000000000000000000
+DROP TABLE t1;
+SELECT CAST(9999999999999999999.0 AS UNSIGNED);
+CAST(9999999999999999999.0 AS UNSIGNED)
+9999999999999999999
+CREATE TABLE t1 (a BIGINT UNSIGNED);
+INSERT INTO t1 VALUES (9999999999999999999.0);
+SELECT * FROM t1;
+a
+9999999999999999999
+DROP TABLE t1;
+SELECT CAST(-1.0 AS UNSIGNED);
+CAST(-1.0 AS UNSIGNED)
+0
+Warnings:
+Warning	1916	Got overflow when converting '-1.0' to UNSIGNED INT. Value truncated
+CREATE TABLE t1 (a BIGINT UNSIGNED);
+INSERT INTO t1 VALUES (-1.0);
+Warnings:
+Warning	1264	Out of range value for column 'a' at row 1
+SELECT * FROM t1;
+a
+0
+DROP TABLE t1;
+SELECT CAST(-1e0 AS UNSIGNED);
+CAST(-1e0 AS UNSIGNED)
+0
+Warnings:
+Note	1916	Got overflow when converting '-1' to UNSIGNED BIGINT. Value truncated
+CREATE TABLE t1 (a BIGINT UNSIGNED);
+INSERT INTO t1 VALUES (-1e0);
+Warnings:
+Warning	1264	Out of range value for column 'a' at row 1
+SELECT * FROM t1;
+a
+0
+DROP TABLE t1;
+SELECT CAST(-1e308 AS UNSIGNED);
+CAST(-1e308 AS UNSIGNED)
+0
+Warnings:
+Note	1916	Got overflow when converting '-1e308' to UNSIGNED BIGINT. Value truncated
+CREATE TABLE t1 (a BIGINT UNSIGNED);
+INSERT INTO t1 VALUES (-1e308);
+Warnings:
+Warning	1264	Out of range value for column 'a' at row 1
+SELECT * FROM t1;
+a
+0
+DROP TABLE t1;
+SELECT CAST(TIME'-00:00:01.123' AS UNSIGNED);
+CAST(TIME'-00:00:01.123' AS UNSIGNED)
+0
+Warnings:
+Note	1916	Got overflow when converting '-00:00:01.123000' to UNSIGNED BIGINT. Value truncated
+CREATE TABLE t1 (a BIGINT UNSIGNED);
+INSERT INTO t1 VALUES (TIME'-00:00:01.123');
+Warnings:
+Warning	1264	Out of range value for column 'a' at row 1
+SELECT * FROM t1;
+a
+0
+DROP TABLE t1;
+CREATE TABLE t1 (a DOUBLE UNSIGNED);
+INSERT INTO t1 VALUES (1.9e19);
+SELECT CAST(a AS SIGNED), CAST(MIN(a) AS SIGNED) FROM t1;
+CAST(a AS SIGNED)	CAST(MIN(a) AS SIGNED)
+9223372036854775807	9223372036854775807
+Warnings:
+Note	1916	Got overflow when converting '1.9e19' to SIGNED BIGINT. Value truncated
+Note	1916	Got overflow when converting '1.9e19' to SIGNED BIGINT. Value truncated
+DROP TABLE t1;
+CREATE TABLE t1 (a DECIMAL(30,1) UNSIGNED);
+INSERT INTO t1 VALUES (1e19);
+SELECT a, CAST(a AS SIGNED) FROM t1;
+a	CAST(a AS SIGNED)
+10000000000000000000.0	9223372036854775807
+Warnings:
+Warning	1916	Got overflow when converting '10000000000000000000.0' to INT. Value truncated
+DROP TABLE t1;
+SET sql_mode=DEFAULT;
diff --git a/mysql-test/main/cast.test b/mysql-test/main/cast.test
index f48d6d9f95f..4ec5470e892 100644
--- a/mysql-test/main/cast.test
+++ b/mysql-test/main/cast.test
@@ -730,3 +730,57 @@ SET sql_mode=DEFAULT;
 --echo #
 
 SELECT CAST(11068046444225730969 AS SIGNED);
+
+--echo #
+--echo # MDEV-8919 Wrong result for CAST(9999999999999999999.0)
+--echo #
+
+SET sql_mode='';
+
+SELECT CAST(9999999999999999999e0 AS UNSIGNED);
+CREATE TABLE t1 (a BIGINT UNSIGNED);
+INSERT INTO t1 VALUES (9999999999999999999e0);
+SELECT * FROM t1;
+DROP TABLE t1;
+
+SELECT CAST(9999999999999999999.0 AS UNSIGNED);
+CREATE TABLE t1 (a BIGINT UNSIGNED);
+INSERT INTO t1 VALUES (9999999999999999999.0);
+SELECT * FROM t1;
+DROP TABLE t1;
+
+SELECT CAST(-1.0 AS UNSIGNED);
+CREATE TABLE t1 (a BIGINT UNSIGNED);
+INSERT INTO t1 VALUES (-1.0);
+SELECT * FROM t1;
+DROP TABLE t1;
+
+SELECT CAST(-1e0 AS UNSIGNED);
+CREATE TABLE t1 (a BIGINT UNSIGNED);
+INSERT INTO t1 VALUES (-1e0);
+SELECT * FROM t1;
+DROP TABLE t1;
+
+SELECT CAST(-1e308 AS UNSIGNED);
+CREATE TABLE t1 (a BIGINT UNSIGNED);
+INSERT INTO t1 VALUES (-1e308);
+SELECT * FROM t1;
+DROP TABLE t1;
+
+SELECT CAST(TIME'-00:00:01.123' AS UNSIGNED);
+CREATE TABLE t1 (a BIGINT UNSIGNED);
+INSERT INTO t1 VALUES (TIME'-00:00:01.123');
+SELECT * FROM t1;
+DROP TABLE t1;
+
+CREATE TABLE t1 (a DOUBLE UNSIGNED);
+INSERT INTO t1 VALUES (1.9e19);
+SELECT CAST(a AS SIGNED), CAST(MIN(a) AS SIGNED) FROM t1;
+DROP TABLE t1;
+
+CREATE TABLE t1 (a DECIMAL(30,1) UNSIGNED);
+INSERT INTO t1 VALUES (1e19);
+SELECT a, CAST(a AS SIGNED) FROM t1;
+DROP TABLE t1;
+
+SET sql_mode=DEFAULT;