maria-developers team mailing list archive
-
maria-developers team
-
Mailing list archive
-
Message #09801
MDEV-8919 Wrong result for CAST(9999999999999999999.0)
Hi Sergei,
Please review a patch for mdev-8919.
Thanks!
commit f683cd85b3ba14aa7a40bf774dd2a0e0d5dd4d6f
Author: Alexander Barkov <bar@xxxxxxxxxxx>
Date: Mon Jul 4 13:29:58 2016 +0400
MDEV-8919 Wrong result for CAST(9999999999999999999.0)
diff --git a/mysql-test/r/cast.result b/mysql-test/r/cast.result
index e196ba2..08e8a92 100644
--- a/mysql-test/r/cast.result
+++ b/mysql-test/r/cast.result
@@ -9,6 +9,7 @@ CAST(CAST(1-2 AS UNSIGNED) AS SIGNED INTEGER)
-1
Warnings:
Note 1105 Cast to unsigned converted negative integer to it's positive complement
+Note 1105 Cast to signed converted positive out-of-range integer to it's negative complement
select CAST('10 ' as unsigned integer);
CAST('10 ' as unsigned integer)
10
@@ -29,6 +30,8 @@ Note 1105 Cast to unsigned converted negative integer to it's positive complemen
select ~5, cast(~5 as signed);
~5 cast(~5 as signed)
18446744073709551610 -6
+Warnings:
+Note 1105 Cast to signed converted positive out-of-range integer to it's negative complement
explain extended select ~5, cast(~5 as signed);
id select_type table type possible_keys key key_len ref rows filtered Extra
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used
@@ -37,6 +40,8 @@ Note 1003 select ~(5) AS `~5`,cast(~(5) as signed) AS `cast(~5 as signed)`
select cast(18446744073709551615 as signed);
cast(18446744073709551615 as signed)
-1
+Warnings:
+Note 1105 Cast to signed converted positive out-of-range integer to it's negative complement
select cast(5 as unsigned) -6.0;
cast(5 as unsigned) -6.0
-1.0
@@ -208,12 +213,16 @@ CAST(0xb3 as signed)
select CAST(0x8fffffffffffffff as signed);
CAST(0x8fffffffffffffff as signed)
-8070450532247928833
+Warnings:
+Note 1105 Cast to signed converted positive out-of-range integer to it's negative complement
select CAST(0xffffffffffffffff as unsigned);
CAST(0xffffffffffffffff as unsigned)
18446744073709551615
select CAST(0xfffffffffffffffe as signed);
CAST(0xfffffffffffffffe as signed)
-2
+Warnings:
+Note 1105 Cast to signed converted positive out-of-range integer to it's negative complement
select cast('-10a' as signed integer);
cast('-10a' as signed integer)
-10
@@ -548,6 +557,8 @@ cast(18446744073709551615 as unsigned)
select cast(18446744073709551615 as signed);
cast(18446744073709551615 as signed)
-1
+Warnings:
+Note 1105 Cast to signed converted positive out-of-range integer to it's negative complement
select cast('18446744073709551615' as unsigned);
cast('18446744073709551615' as unsigned)
18446744073709551615
@@ -578,6 +589,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:
+Warning 1916 Got overflow when converting '1e300' to INT. 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
@@ -822,3 +835,87 @@ utf8_bin
select collation(cast("a" as char(10) binary ascii));
collation(cast("a" as char(10) binary ascii))
latin1_bin
+#
+# Start of 10.2 tests
+#
+#
+# MDEV-8919 Wrong result for CAST(9999999999999999999.0)
+#
+SELECT CAST(-9e0 AS UNSIGNED) AS c1;
+c1
+0
+Warnings:
+Warning 1916 Got overflow when converting '-9' to UNSIGNED INT. Value truncated.
+SELECT CAST(-9.0 AS UNSIGNED) AS c2;
+c2
+0
+Warnings:
+Warning 1916 Got overflow when converting '-9.0' to UNSIGNED INT. Value truncated.
+SELECT CAST(9999999999999999999e0 AS UNSIGNED) AS c1;
+c1
+10000000000000000000
+SELECT CAST(9999999999999999999.0 AS UNSIGNED) AS c2;
+c2
+9999999999999999999
+SELECT CAST(99999999999999999999e0 AS UNSIGNED) AS c1;
+c1
+18446744073709551615
+Warnings:
+Warning 1916 Got overflow when converting '1e20' to UNSIGNED INT. Value truncated.
+SELECT CAST(99999999999999999999.0 AS UNSIGNED) AS c2;
+c2
+18446744073709551615
+Warnings:
+Warning 1916 Got overflow when converting '99999999999999999999.0' to UNSIGNED INT. Value truncated.
+SELECT CAST(-1e0 AS UNSIGNED), CAST(-1e308 AS UNSIGNED);
+CAST(-1e0 AS UNSIGNED) CAST(-1e308 AS UNSIGNED)
+0 0
+Warnings:
+Warning 1916 Got overflow when converting '-1' to UNSIGNED INT. Value truncated.
+Warning 1916 Got overflow when converting '-1e308' to UNSIGNED INT. Value truncated.
+SELECT CAST(TIME'-00:00:01.567' AS UNSIGNED);
+CAST(TIME'-00:00:01.567' AS UNSIGNED)
+0
+Warnings:
+Warning 1916 Got overflow when converting '-00:00:01.567000' to UNSIGNED INT. Value truncated.
+CREATE TABLE t1 (a DOUBLE UNSIGNED, b DOUBLE);
+INSERT INTO t1 VALUES (1e19, 1e19),(1.9e19, 1.9e19);
+SELECT a, CAST(a AS SIGNED), CAST(b AS SIGNED) FROM t1;
+a CAST(a AS SIGNED) CAST(b AS SIGNED)
+1e19 9223372036854775807 9223372036854775807
+1.9e19 9223372036854775807 9223372036854775807
+Warnings:
+Warning 1916 Got overflow when converting '1e19' to INT. Value truncated.
+Warning 1916 Got overflow when converting '1e19' to INT. Value truncated.
+Warning 1916 Got overflow when converting '1.9e19' to INT. Value truncated.
+Warning 1916 Got overflow when converting '1.9e19' to INT. Value truncated.
+SELECT a, CAST(a AS UNSIGNED), CAST(b AS UNSIGNED) FROM t1;
+a CAST(a AS UNSIGNED) CAST(b AS UNSIGNED)
+1e19 10000000000000000000 10000000000000000000
+1.9e19 18446744073709551615 18446744073709551615
+Warnings:
+Warning 1916 Got overflow when converting '1.9e19' to UNSIGNED INT. Value truncated.
+Warning 1916 Got overflow when converting '1.9e19' to UNSIGNED INT. Value truncated.
+DROP TABLE t1;
+CREATE TABLE t1 (a DECIMAL(30,1) UNSIGNED, b DECIMAL(30,1));
+INSERT INTO t1 VALUES (1e19, 1e19),(1.9e19, 1.9e19);
+SELECT a, CAST(a AS SIGNED), CAST(b AS SIGNED) FROM t1;
+a CAST(a AS SIGNED) CAST(b AS SIGNED)
+10000000000000000000.0 9223372036854775807 9223372036854775807
+19000000000000000000.0 9223372036854775807 9223372036854775807
+Warnings:
+Warning 1916 Got overflow when converting '10000000000000000000.0' to INT. Value truncated.
+Warning 1916 Got overflow when converting '10000000000000000000.0' to INT. Value truncated.
+Warning 1916 Got overflow when converting '19000000000000000000.0' to INT. Value truncated.
+Warning 1916 Got overflow when converting '19000000000000000000.0' to INT. Value truncated.
+SELECT a, CAST(a AS UNSIGNED), CAST(b AS UNSIGNED) FROM t1;
+a CAST(a AS UNSIGNED) CAST(b AS UNSIGNED)
+10000000000000000000.0 10000000000000000000 10000000000000000000
+19000000000000000000.0 18446744073709551615 18446744073709551615
+Warnings:
+Warning 1916 Got overflow when converting '19000000000000000000.0' to UNSIGNED INT. Value truncated.
+Warning 1916 Got overflow when converting '19000000000000000000.0' to UNSIGNED INT. Value truncated.
+DROP TABLE t1;
+#
+# End of 10.2 tests
+#
diff --git a/mysql-test/r/default.result b/mysql-test/r/default.result
index 7817016..029796c 100644
--- a/mysql-test/r/default.result
+++ b/mysql-test/r/default.result
@@ -2196,6 +2196,8 @@ t1 CREATE TABLE `t1` (
`b` bigint(20) DEFAULT (CAST(a AS SIGNED))
) ENGINE=MyISAM DEFAULT CHARSET=latin1
INSERT INTO t1 (a) VALUES (0xFFFFFFFFFFFFFFFF);
+Warnings:
+Note 1105 Cast to signed converted positive out-of-range integer to it's negative complement
SELECT * FROM t1;
a b
18446744073709551615 -1
diff --git a/mysql-test/r/strict.result b/mysql-test/r/strict.result
index 9dcd597..c9ad6df 100644
--- a/mysql-test/r/strict.result
+++ b/mysql-test/r/strict.result
@@ -1524,6 +1524,55 @@ Warnings:
Warning 1411 Incorrect datetime value: '2001' for function str_to_date
Warning 1411 Incorrect datetime value: '2001' for function str_to_date
Warning 1411 Incorrect datetime value: '2001' for function str_to_date
+SET sql_mode=DEFAULT;
#
# End of 5.6 tests
#
+#
+# Start of 10.2 tests
+#
+#
+# MDEV-8919 Wrong result for CAST(9999999999999999999.0)
+#
+CREATE TABLE t1 (a DATE);
+INSERT INTO t1 VALUES ('0000-00-00'),('2001-00-00');
+SELECT
+CAST(a AS SIGNED),
+CAST(a AS UNSIGNED),
+CAST(a AS DOUBLE),
+CAST(a AS DECIMAL(20,0)),
+CAST(a AS CHAR)
+FROM t1;
+CAST(a AS SIGNED) 0
+CAST(a AS UNSIGNED) 0
+CAST(a AS DOUBLE) 0
+CAST(a AS DECIMAL(20,0)) 0
+CAST(a AS CHAR) 0000-00-00
+CAST(a AS SIGNED) 20010000
+CAST(a AS UNSIGNED) 20010000
+CAST(a AS DOUBLE) 20010000
+CAST(a AS DECIMAL(20,0)) 20010000
+CAST(a AS CHAR) 2001-00-00
+SET sql_mode='NO_ZERO_IN_DATE,NO_ZERO_IN_DATE';
+SELECT
+CAST(a AS SIGNED),
+CAST(a AS UNSIGNED),
+CAST(a AS DOUBLE),
+CAST(a AS DECIMAL(20,0)),
+CAST(a AS CHAR)
+FROM t1;
+CAST(a AS SIGNED) 0
+CAST(a AS UNSIGNED) 0
+CAST(a AS DOUBLE) 0
+CAST(a AS DECIMAL(20,0)) 0
+CAST(a AS CHAR) 0000-00-00
+CAST(a AS SIGNED) 20010000
+CAST(a AS UNSIGNED) 20010000
+CAST(a AS DOUBLE) 20010000
+CAST(a AS DECIMAL(20,0)) 20010000
+CAST(a AS CHAR) 2001-00-00
+DROP TABLE t1;
+SET sql_mode=DEFAULT;
+#
+# End of 10.2 tests
+#
diff --git a/mysql-test/r/type_float.result b/mysql-test/r/type_float.result
index 4f4c365..7323410 100644
--- a/mysql-test/r/type_float.result
+++ b/mysql-test/r/type_float.result
@@ -772,5 +772,68 @@ t1 CREATE TABLE `t1` (
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table if exists t1;
#
+# MDEV-8919 Wrong result for CAST(9999999999999999999.0)
+# Testing that DOUBLE->[UN]SIGNED conversion returns a warning on overflow
+CREATE TABLE t1 (a DOUBLE);
+INSERT INTO t1 VALUES (-1e100),(-1e101);
+SELECT CAST(a AS SIGNED), CAST(a AS UNSIGNED) FROM t1;
+CAST(a AS SIGNED) CAST(a AS UNSIGNED)
+-9223372036854775808 0
+-9223372036854775808 0
+Warnings:
+Warning 1916 Got overflow when converting '-1e100' to INT. Value truncated.
+Warning 1916 Got overflow when converting '-1e100' to UNSIGNED INT. Value truncated.
+Warning 1916 Got overflow when converting '-1e101' to INT. Value truncated.
+Warning 1916 Got overflow when converting '-1e101' to UNSIGNED INT. Value truncated.
+SELECT CAST(COALESCE(a) AS SIGNED), CAST(COALESCE(a) AS UNSIGNED) FROM t1;
+CAST(COALESCE(a) AS SIGNED) CAST(COALESCE(a) AS UNSIGNED)
+-9223372036854775808 0
+-9223372036854775808 0
+Warnings:
+Warning 1916 Got overflow when converting '-1e100' to INT. Value truncated.
+Warning 1916 Got overflow when converting '-1e100' to UNSIGNED INT. Value truncated.
+Warning 1916 Got overflow when converting '-1e101' to INT. Value truncated.
+Warning 1916 Got overflow when converting '-1e101' to UNSIGNED INT. Value truncated.
+SELECT CAST(SUM(a) AS SIGNED), CAST(SUM(a) AS UNSIGNED) FROM t1;
+CAST(SUM(a) AS SIGNED) CAST(SUM(a) AS UNSIGNED)
+-9223372036854775808 0
+Warnings:
+Warning 1916 Got overflow when converting '-1.1e101' to INT. Value truncated.
+Warning 1916 Got overflow when converting '-1.1e101' to UNSIGNED INT. Value truncated.
+SELECT CAST(AVG(a) AS SIGNED), CAST(AVG(a) AS UNSIGNED) FROM t1;
+CAST(AVG(a) AS SIGNED) CAST(AVG(a) AS UNSIGNED)
+-9223372036854775808 0
+Warnings:
+Warning 1916 Got overflow when converting '-5.5e100' to INT. Value truncated.
+Warning 1916 Got overflow when converting '-5.5e100' to UNSIGNED INT. Value truncated.
+SELECT CAST(VARIANCE(a) AS SIGNED), CAST(VARIANCE(a) AS UNSIGNED) FROM t1;
+CAST(VARIANCE(a) AS SIGNED) CAST(VARIANCE(a) AS UNSIGNED)
+9223372036854775807 18446744073709551615
+Warnings:
+Warning 1916 Got overflow when converting '2.025e201' to INT. Value truncated.
+Warning 1916 Got overflow when converting '2.025e201' to UNSIGNED INT. Value truncated.
+DROP TABLE t1;
+CREATE TABLE t1 (id INT, a DOUBLE);
+INSERT INTO t1 VALUES (1, -1e100),(1, -1e101);
+SELECT CAST(SUM(a) AS SIGNED), CAST(SUM(a) AS UNSIGNED) FROM t1 GROUP BY id;
+CAST(SUM(a) AS SIGNED) CAST(SUM(a) AS UNSIGNED)
+-9223372036854775808 0
+Warnings:
+Warning 1916 Got overflow when converting '-1.1e101' to INT. Value truncated.
+Warning 1916 Got overflow when converting '-1.1e101' to UNSIGNED INT. Value truncated.
+SELECT CAST(AVG(a) AS SIGNED), CAST(AVG(a) AS UNSIGNED) FROM t1 GROUP BY id;
+CAST(AVG(a) AS SIGNED) CAST(AVG(a) AS UNSIGNED)
+-9223372036854775808 0
+Warnings:
+Warning 1916 Got overflow when converting '-5.5e100' to INT. Value truncated.
+Warning 1916 Got overflow when converting '-5.5e100' to UNSIGNED INT. Value truncated.
+SELECT CAST(VARIANCE(a) AS SIGNED), CAST(VARIANCE(a) AS UNSIGNED) FROM t1 GROUP BY id;
+CAST(VARIANCE(a) AS SIGNED) CAST(VARIANCE(a) AS UNSIGNED)
+9223372036854775807 18446744073709551615
+Warnings:
+Warning 1916 Got overflow when converting '2.025e201' to INT. Value truncated.
+Warning 1916 Got overflow when converting '2.025e201' to UNSIGNED INT. Value truncated.
+DROP TABLE t1;
+#
# End of 10.2 tests
#
diff --git a/mysql-test/suite/funcs_1/r/innodb_func_view.result b/mysql-test/suite/funcs_1/r/innodb_func_view.result
index 1264342..f569155 100644
--- a/mysql-test/suite/funcs_1/r/innodb_func_view.result
+++ b/mysql-test/suite/funcs_1/r/innodb_func_view.result
@@ -2473,12 +2473,12 @@ my_time, id FROM t1_values
WHERE select_id = 93 OR select_id IS NULL order by id;
CAST(my_time AS UNSIGNED INTEGER) my_time id
NULL NULL 1
-18446744073701165657 -838:59:59 2
+0 -838:59:59 2
8385959 838:59:59 3
130000 13:00:00 4
100000 10:00:00 5
Warnings:
-Note 1105 Cast to unsigned converted negative integer to it's positive complement
+Warning 1916 Got overflow when converting '-838:59:59' to UNSIGNED INT. Value truncated.
SHOW CREATE VIEW v1;
View Create View character_set_client collation_connection
v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select cast(`t1_values`.`my_time` as unsigned) AS `CAST(my_time AS UNSIGNED INTEGER)`,`t1_values`.`my_time` AS `my_time`,`t1_values`.`id` AS `id` from `t1_values` latin1 latin1_swedish_ci
@@ -2487,12 +2487,12 @@ WHERE v1.id IN (SELECT id FROM t1_values
WHERE select_id = 93 OR select_id IS NULL) order by id;
CAST(my_time AS UNSIGNED INTEGER) my_time id
NULL NULL 1
-18446744073701165657 -838:59:59 2
+0 -838:59:59 2
8385959 838:59:59 3
130000 13:00:00 4
100000 10:00:00 5
Warnings:
-Note 1105 Cast to unsigned converted negative integer to it's positive complement
+Warning 1916 Got overflow when converting '-838:59:59' to UNSIGNED INT. Value truncated.
DROP VIEW v1;
@@ -2581,15 +2581,14 @@ my_double, id FROM t1_values
WHERE select_id = 89 OR select_id IS NULL order by id;
CAST(my_double AS UNSIGNED INTEGER) my_double id
NULL NULL 1
-9223372036854775808 -1.7976931348623e308 2
-9223372036854775807 1.7976931348623e308 3
+0 -1.7976931348623e308 2
+18446744073709551615 1.7976931348623e308 3
0 0 4
-18446744073709551615 -1 5
+0 -1 5
Warnings:
-Warning 1916 Got overflow when converting '-1.7976931348623e308' to INT. Value truncated.
-Note 1105 Cast to unsigned converted negative integer to it's positive complement
-Warning 1916 Got overflow when converting '1.7976931348623e308' to INT. Value truncated.
-Note 1105 Cast to unsigned converted negative integer to it's positive complement
+Warning 1916 Got overflow when converting '-1.7976931348623e308' to UNSIGNED INT. Value truncated.
+Warning 1916 Got overflow when converting '1.7976931348623e308' to UNSIGNED INT. Value truncated.
+Warning 1916 Got overflow when converting '-1' to UNSIGNED INT. Value truncated.
SHOW CREATE VIEW v1;
View Create View character_set_client collation_connection
v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select cast(`t1_values`.`my_double` as unsigned) AS `CAST(my_double AS UNSIGNED INTEGER)`,`t1_values`.`my_double` AS `my_double`,`t1_values`.`id` AS `id` from `t1_values` latin1 latin1_swedish_ci
@@ -2598,15 +2597,14 @@ WHERE v1.id IN (SELECT id FROM t1_values
WHERE select_id = 89 OR select_id IS NULL) order by id;
CAST(my_double AS UNSIGNED INTEGER) my_double id
NULL NULL 1
-9223372036854775808 -1.7976931348623e308 2
-9223372036854775807 1.7976931348623e308 3
+0 -1.7976931348623e308 2
+18446744073709551615 1.7976931348623e308 3
0 0 4
-18446744073709551615 -1 5
+0 -1 5
Warnings:
-Warning 1916 Got overflow when converting '-1.7976931348623e308' to INT. Value truncated.
-Note 1105 Cast to unsigned converted negative integer to it's positive complement
-Warning 1916 Got overflow when converting '1.7976931348623e308' to INT. Value truncated.
-Note 1105 Cast to unsigned converted negative integer to it's positive complement
+Warning 1916 Got overflow when converting '-1.7976931348623e308' to UNSIGNED INT. Value truncated.
+Warning 1916 Got overflow when converting '1.7976931348623e308' to UNSIGNED INT. Value truncated.
+Warning 1916 Got overflow when converting '-1' to UNSIGNED INT. Value truncated.
DROP VIEW v1;
diff --git a/mysql-test/suite/funcs_1/r/memory_func_view.result b/mysql-test/suite/funcs_1/r/memory_func_view.result
index c78cf16..48546ad 100644
--- a/mysql-test/suite/funcs_1/r/memory_func_view.result
+++ b/mysql-test/suite/funcs_1/r/memory_func_view.result
@@ -2474,12 +2474,12 @@ my_time, id FROM t1_values
WHERE select_id = 93 OR select_id IS NULL order by id;
CAST(my_time AS UNSIGNED INTEGER) my_time id
NULL NULL 1
-18446744073701165657 -838:59:59 2
+0 -838:59:59 2
8385959 838:59:59 3
130000 13:00:00 4
100000 10:00:00 5
Warnings:
-Note 1105 Cast to unsigned converted negative integer to it's positive complement
+Warning 1916 Got overflow when converting '-838:59:59' to UNSIGNED INT. Value truncated.
SHOW CREATE VIEW v1;
View Create View character_set_client collation_connection
v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select cast(`t1_values`.`my_time` as unsigned) AS `CAST(my_time AS UNSIGNED INTEGER)`,`t1_values`.`my_time` AS `my_time`,`t1_values`.`id` AS `id` from `t1_values` latin1 latin1_swedish_ci
@@ -2488,12 +2488,12 @@ WHERE v1.id IN (SELECT id FROM t1_values
WHERE select_id = 93 OR select_id IS NULL) order by id;
CAST(my_time AS UNSIGNED INTEGER) my_time id
NULL NULL 1
-18446744073701165657 -838:59:59 2
+0 -838:59:59 2
8385959 838:59:59 3
130000 13:00:00 4
100000 10:00:00 5
Warnings:
-Note 1105 Cast to unsigned converted negative integer to it's positive complement
+Warning 1916 Got overflow when converting '-838:59:59' to UNSIGNED INT. Value truncated.
DROP VIEW v1;
@@ -2582,15 +2582,14 @@ my_double, id FROM t1_values
WHERE select_id = 89 OR select_id IS NULL order by id;
CAST(my_double AS UNSIGNED INTEGER) my_double id
NULL NULL 1
-9223372036854775808 -1.7976931348623e308 2
-9223372036854775807 1.7976931348623e308 3
+0 -1.7976931348623e308 2
+18446744073709551615 1.7976931348623e308 3
0 0 4
-18446744073709551615 -1 5
+0 -1 5
Warnings:
-Warning 1916 Got overflow when converting '-1.7976931348623e308' to INT. Value truncated.
-Note 1105 Cast to unsigned converted negative integer to it's positive complement
-Warning 1916 Got overflow when converting '1.7976931348623e308' to INT. Value truncated.
-Note 1105 Cast to unsigned converted negative integer to it's positive complement
+Warning 1916 Got overflow when converting '-1.7976931348623e308' to UNSIGNED INT. Value truncated.
+Warning 1916 Got overflow when converting '1.7976931348623e308' to UNSIGNED INT. Value truncated.
+Warning 1916 Got overflow when converting '-1' to UNSIGNED INT. Value truncated.
SHOW CREATE VIEW v1;
View Create View character_set_client collation_connection
v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select cast(`t1_values`.`my_double` as unsigned) AS `CAST(my_double AS UNSIGNED INTEGER)`,`t1_values`.`my_double` AS `my_double`,`t1_values`.`id` AS `id` from `t1_values` latin1 latin1_swedish_ci
@@ -2599,15 +2598,14 @@ WHERE v1.id IN (SELECT id FROM t1_values
WHERE select_id = 89 OR select_id IS NULL) order by id;
CAST(my_double AS UNSIGNED INTEGER) my_double id
NULL NULL 1
-9223372036854775808 -1.7976931348623e308 2
-9223372036854775807 1.7976931348623e308 3
+0 -1.7976931348623e308 2
+18446744073709551615 1.7976931348623e308 3
0 0 4
-18446744073709551615 -1 5
+0 -1 5
Warnings:
-Warning 1916 Got overflow when converting '-1.7976931348623e308' to INT. Value truncated.
-Note 1105 Cast to unsigned converted negative integer to it's positive complement
-Warning 1916 Got overflow when converting '1.7976931348623e308' to INT. Value truncated.
-Note 1105 Cast to unsigned converted negative integer to it's positive complement
+Warning 1916 Got overflow when converting '-1.7976931348623e308' to UNSIGNED INT. Value truncated.
+Warning 1916 Got overflow when converting '1.7976931348623e308' to UNSIGNED INT. Value truncated.
+Warning 1916 Got overflow when converting '-1' to UNSIGNED INT. Value truncated.
DROP VIEW v1;
diff --git a/mysql-test/suite/funcs_1/r/myisam_func_view.result b/mysql-test/suite/funcs_1/r/myisam_func_view.result
index c78cf16..48546ad 100644
--- a/mysql-test/suite/funcs_1/r/myisam_func_view.result
+++ b/mysql-test/suite/funcs_1/r/myisam_func_view.result
@@ -2474,12 +2474,12 @@ my_time, id FROM t1_values
WHERE select_id = 93 OR select_id IS NULL order by id;
CAST(my_time AS UNSIGNED INTEGER) my_time id
NULL NULL 1
-18446744073701165657 -838:59:59 2
+0 -838:59:59 2
8385959 838:59:59 3
130000 13:00:00 4
100000 10:00:00 5
Warnings:
-Note 1105 Cast to unsigned converted negative integer to it's positive complement
+Warning 1916 Got overflow when converting '-838:59:59' to UNSIGNED INT. Value truncated.
SHOW CREATE VIEW v1;
View Create View character_set_client collation_connection
v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select cast(`t1_values`.`my_time` as unsigned) AS `CAST(my_time AS UNSIGNED INTEGER)`,`t1_values`.`my_time` AS `my_time`,`t1_values`.`id` AS `id` from `t1_values` latin1 latin1_swedish_ci
@@ -2488,12 +2488,12 @@ WHERE v1.id IN (SELECT id FROM t1_values
WHERE select_id = 93 OR select_id IS NULL) order by id;
CAST(my_time AS UNSIGNED INTEGER) my_time id
NULL NULL 1
-18446744073701165657 -838:59:59 2
+0 -838:59:59 2
8385959 838:59:59 3
130000 13:00:00 4
100000 10:00:00 5
Warnings:
-Note 1105 Cast to unsigned converted negative integer to it's positive complement
+Warning 1916 Got overflow when converting '-838:59:59' to UNSIGNED INT. Value truncated.
DROP VIEW v1;
@@ -2582,15 +2582,14 @@ my_double, id FROM t1_values
WHERE select_id = 89 OR select_id IS NULL order by id;
CAST(my_double AS UNSIGNED INTEGER) my_double id
NULL NULL 1
-9223372036854775808 -1.7976931348623e308 2
-9223372036854775807 1.7976931348623e308 3
+0 -1.7976931348623e308 2
+18446744073709551615 1.7976931348623e308 3
0 0 4
-18446744073709551615 -1 5
+0 -1 5
Warnings:
-Warning 1916 Got overflow when converting '-1.7976931348623e308' to INT. Value truncated.
-Note 1105 Cast to unsigned converted negative integer to it's positive complement
-Warning 1916 Got overflow when converting '1.7976931348623e308' to INT. Value truncated.
-Note 1105 Cast to unsigned converted negative integer to it's positive complement
+Warning 1916 Got overflow when converting '-1.7976931348623e308' to UNSIGNED INT. Value truncated.
+Warning 1916 Got overflow when converting '1.7976931348623e308' to UNSIGNED INT. Value truncated.
+Warning 1916 Got overflow when converting '-1' to UNSIGNED INT. Value truncated.
SHOW CREATE VIEW v1;
View Create View character_set_client collation_connection
v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select cast(`t1_values`.`my_double` as unsigned) AS `CAST(my_double AS UNSIGNED INTEGER)`,`t1_values`.`my_double` AS `my_double`,`t1_values`.`id` AS `id` from `t1_values` latin1 latin1_swedish_ci
@@ -2599,15 +2598,14 @@ WHERE v1.id IN (SELECT id FROM t1_values
WHERE select_id = 89 OR select_id IS NULL) order by id;
CAST(my_double AS UNSIGNED INTEGER) my_double id
NULL NULL 1
-9223372036854775808 -1.7976931348623e308 2
-9223372036854775807 1.7976931348623e308 3
+0 -1.7976931348623e308 2
+18446744073709551615 1.7976931348623e308 3
0 0 4
-18446744073709551615 -1 5
+0 -1 5
Warnings:
-Warning 1916 Got overflow when converting '-1.7976931348623e308' to INT. Value truncated.
-Note 1105 Cast to unsigned converted negative integer to it's positive complement
-Warning 1916 Got overflow when converting '1.7976931348623e308' to INT. Value truncated.
-Note 1105 Cast to unsigned converted negative integer to it's positive complement
+Warning 1916 Got overflow when converting '-1.7976931348623e308' to UNSIGNED INT. Value truncated.
+Warning 1916 Got overflow when converting '1.7976931348623e308' to UNSIGNED INT. Value truncated.
+Warning 1916 Got overflow when converting '-1' to UNSIGNED INT. Value truncated.
DROP VIEW v1;
diff --git a/mysql-test/t/cast.test b/mysql-test/t/cast.test
index 58f9157..9b1694a 100644
--- a/mysql-test/t/cast.test
+++ b/mysql-test/t/cast.test
@@ -472,3 +472,38 @@ select collation(cast("a" as char(10) ascii binary));
select collation(cast("a" as char(10) binary charset utf8));
select collation(cast("a" as char(10) binary ascii));
+--echo #
+--echo # Start of 10.2 tests
+--echo #
+
+--echo #
+--echo # MDEV-8919 Wrong result for CAST(9999999999999999999.0)
+--echo #
+SELECT CAST(-9e0 AS UNSIGNED) AS c1;
+SELECT CAST(-9.0 AS UNSIGNED) AS c2;
+
+SELECT CAST(9999999999999999999e0 AS UNSIGNED) AS c1;
+SELECT CAST(9999999999999999999.0 AS UNSIGNED) AS c2;
+
+SELECT CAST(99999999999999999999e0 AS UNSIGNED) AS c1;
+SELECT CAST(99999999999999999999.0 AS UNSIGNED) AS c2;
+
+SELECT CAST(-1e0 AS UNSIGNED), CAST(-1e308 AS UNSIGNED);
+
+SELECT CAST(TIME'-00:00:01.567' AS UNSIGNED);
+
+CREATE TABLE t1 (a DOUBLE UNSIGNED, b DOUBLE);
+INSERT INTO t1 VALUES (1e19, 1e19),(1.9e19, 1.9e19);
+SELECT a, CAST(a AS SIGNED), CAST(b AS SIGNED) FROM t1;
+SELECT a, CAST(a AS UNSIGNED), CAST(b AS UNSIGNED) FROM t1;
+DROP TABLE t1;
+
+CREATE TABLE t1 (a DECIMAL(30,1) UNSIGNED, b DECIMAL(30,1));
+INSERT INTO t1 VALUES (1e19, 1e19),(1.9e19, 1.9e19);
+SELECT a, CAST(a AS SIGNED), CAST(b AS SIGNED) FROM t1;
+SELECT a, CAST(a AS UNSIGNED), CAST(b AS UNSIGNED) FROM t1;
+DROP TABLE t1;
+
+--echo #
+--echo # End of 10.2 tests
+--echo #
diff --git a/mysql-test/t/strict.test b/mysql-test/t/strict.test
index 93b3149..6569782 100644
--- a/mysql-test/t/strict.test
+++ b/mysql-test/t/strict.test
@@ -1366,7 +1366,40 @@ SET sql_mode='NO_ZERO_DATE';
SELECT STR_TO_DATE('2001','%Y'),CONCAT(STR_TO_DATE('2001','%Y')), STR_TO_DATE('2001','%Y')+1, STR_TO_DATE('0','%Y')+1, STR_TO_DATE('0000','%Y')+1;
SET sql_mode='NO_ZERO_IN_DATE';
SELECT STR_TO_DATE('2001','%Y'),CONCAT(STR_TO_DATE('2001','%Y')), STR_TO_DATE('2001','%Y')+1, STR_TO_DATE('0000','%Y')+1;
+SET sql_mode=DEFAULT;
--echo #
--echo # End of 5.6 tests
--echo #
+
+--echo #
+--echo # Start of 10.2 tests
+--echo #
+
+--echo #
+--echo # MDEV-8919 Wrong result for CAST(9999999999999999999.0)
+--echo #
+
+# Checking that CAST consistently handles NO_ZERO_DATE/NO_ZERO_IN_DATE
+# across all cast targets
+
+CREATE TABLE t1 (a DATE);
+INSERT INTO t1 VALUES ('0000-00-00'),('2001-00-00');
+let $query=SELECT
+ CAST(a AS SIGNED),
+ CAST(a AS UNSIGNED),
+ CAST(a AS DOUBLE),
+ CAST(a AS DECIMAL(20,0)),
+ CAST(a AS CHAR)
+FROM t1;
+--vertical_results
+--eval $query
+SET sql_mode='NO_ZERO_IN_DATE,NO_ZERO_IN_DATE';
+--eval $query
+--horizontal_results
+DROP TABLE t1;
+SET sql_mode=DEFAULT;
+
+--echo #
+--echo # End of 10.2 tests
+--echo #
diff --git a/mysql-test/t/type_float.test b/mysql-test/t/type_float.test
index f54eacf..5332299 100644
--- a/mysql-test/t/type_float.test
+++ b/mysql-test/t/type_float.test
@@ -534,5 +534,23 @@ show create table t1;
drop table if exists t1;
--echo #
+--echo # MDEV-8919 Wrong result for CAST(9999999999999999999.0)
+--echo # Testing that DOUBLE->[UN]SIGNED conversion returns a warning on overflow
+CREATE TABLE t1 (a DOUBLE);
+INSERT INTO t1 VALUES (-1e100),(-1e101);
+SELECT CAST(a AS SIGNED), CAST(a AS UNSIGNED) FROM t1;
+SELECT CAST(COALESCE(a) AS SIGNED), CAST(COALESCE(a) AS UNSIGNED) FROM t1;
+SELECT CAST(SUM(a) AS SIGNED), CAST(SUM(a) AS UNSIGNED) FROM t1;
+SELECT CAST(AVG(a) AS SIGNED), CAST(AVG(a) AS UNSIGNED) FROM t1;
+SELECT CAST(VARIANCE(a) AS SIGNED), CAST(VARIANCE(a) AS UNSIGNED) FROM t1;
+DROP TABLE t1;
+CREATE TABLE t1 (id INT, a DOUBLE);
+INSERT INTO t1 VALUES (1, -1e100),(1, -1e101);
+SELECT CAST(SUM(a) AS SIGNED), CAST(SUM(a) AS UNSIGNED) FROM t1 GROUP BY id;
+SELECT CAST(AVG(a) AS SIGNED), CAST(AVG(a) AS UNSIGNED) FROM t1 GROUP BY id;
+SELECT CAST(VARIANCE(a) AS SIGNED), CAST(VARIANCE(a) AS UNSIGNED) FROM t1 GROUP BY id;
+DROP TABLE t1;
+
+--echo #
--echo # End of 10.2 tests
--echo #
diff --git a/sql/item.cc b/sql/item.cc
index 2fb507c..4679f09 100644
--- a/sql/item.cc
+++ b/sql/item.cc
@@ -396,14 +396,14 @@ double Item::val_real_from_decimal()
}
-longlong Item::val_int_from_decimal()
+longlong Item::val_int_from_decimal(bool want_unsigned_value)
{
/* Note that fix_fields may not be called for Item_avg_field items */
longlong result;
my_decimal value, *dec_val= val_decimal(&value);
if (null_value)
return 0;
- my_decimal2int(E_DEC_FATAL_ERROR, dec_val, unsigned_flag, &result);
+ my_decimal2int(E_DEC_FATAL_ERROR, dec_val, want_unsigned_value, &result);
return result;
}
diff --git a/sql/item.h b/sql/item.h
index e43b4d5..2ca85fb 100644
--- a/sql/item.h
+++ b/sql/item.h
@@ -1039,7 +1039,7 @@ class Item: public Value_source,
my_decimal *val_decimal_from_string(my_decimal *decimal_value);
my_decimal *val_decimal_from_date(my_decimal *decimal_value);
my_decimal *val_decimal_from_time(my_decimal *decimal_value);
- longlong val_int_from_decimal();
+ longlong val_int_from_decimal(bool want_unsigned_value);
longlong val_int_from_date();
longlong val_int_from_real()
{
diff --git a/sql/item_func.cc b/sql/item_func.cc
index 7f8c89c..2f96a39 100644
--- a/sql/item_func.cc
+++ b/sql/item_func.cc
@@ -1097,14 +1097,33 @@ longlong Item::val_int_from_str(int *error)
longlong Item::val_int_signed_typecast()
{
- if (cast_to_int_type() != STRING_RESULT)
- return val_int();
-
- int error;
- longlong value= val_int_from_str(&error);
- if (!null_value && value < 0 && error == 0)
- push_note_converted_to_negative_complement(current_thd);
- return value;
+ switch (cast_to_int_type()) {
+ case DECIMAL_RESULT:
+ return val_int_from_decimal(false);
+ case REAL_RESULT:
+ return Converter_double_to_longlong_with_warn(val_real(), false).result();
+ case TIME_RESULT:
+ return val_int(); // temporal to signed longlong conversion never overflows
+ case INT_RESULT:
+ {
+ longlong value= val_int();
+ if (!null_value && value < 0 && unsigned_flag)
+ push_note_converted_to_negative_complement(current_thd);
+ return value;
+ }
+ case STRING_RESULT:
+ {
+ int error;
+ longlong value= val_int_from_str(&error);
+ if (!null_value && value < 0 && error == 0)
+ push_note_converted_to_negative_complement(current_thd);
+ return value;
+ }
+ case ROW_RESULT:
+ break;
+ }
+ DBUG_ASSERT(0);
+ return 0;
}
@@ -1119,29 +1138,44 @@ void Item_func_unsigned::print(String *str, enum_query_type query_type)
longlong Item::val_int_unsigned_typecast()
{
- if (cast_to_int_type() == DECIMAL_RESULT)
+ switch (cast_to_int_type()) {
+ case DECIMAL_RESULT:
+ return val_int_from_decimal(true);
+ case REAL_RESULT:
+ return Converter_double_to_longlong_with_warn(val_real(), true).result();
+ case TIME_RESULT:
{
- longlong value;
- my_decimal tmp, *dec= val_decimal(&tmp);
- if (!null_value)
- my_decimal2int(E_DEC_FATAL_ERROR, dec, 1, &value);
- else
- value= 0;
- return value;
+ MYSQL_TIME ltime;
+ if (get_date(<ime, 0))
+ return 0;
+ if (!ltime.neg)
+ return (longlong) TIME_to_ulonglong(<ime);
+ THD *thd= current_thd;
+ push_warning_printf(thd, Sql_condition::WARN_LEVEL_WARN,
+ ER_DATA_OVERFLOW, ER_THD(thd, ER_DATA_OVERFLOW),
+ ErrConvTime(<ime).ptr(), "UNSIGNED INT");
+ return 0;
}
- else if (cast_to_int_type() != STRING_RESULT)
+ case INT_RESULT:
{
longlong value= val_int();
- if (!null_value && unsigned_flag == 0 && value < 0)
+ if (!null_value && value < 0 && !unsigned_flag)
push_note_converted_to_positive_complement(current_thd);
return value;
}
-
- int error;
- longlong value= val_int_from_str(&error);
- if (!null_value && error < 0)
- push_note_converted_to_positive_complement(current_thd);
- return value;
+ case STRING_RESULT:
+ {
+ int error;
+ longlong value= val_int_from_str(&error);
+ if (!null_value && error < 0)
+ push_note_converted_to_positive_complement(current_thd);
+ return value;
+ }
+ case ROW_RESULT:
+ break;
+ }
+ DBUG_ASSERT(0);
+ return 0;
}
diff --git a/sql/item_sum.cc b/sql/item_sum.cc
index cc7a762..4f679fc 100644
--- a/sql/item_sum.cc
+++ b/sql/item_sum.cc
@@ -2863,7 +2863,7 @@ double Item_sum_udf_decimal::val_real()
longlong Item_sum_udf_decimal::val_int()
{
- return val_int_from_decimal();
+ return val_int_from_decimal(unsigned_flag);
}
diff --git a/sql/item_sum.h b/sql/item_sum.h
index 4cb5529..edaadad 100644
--- a/sql/item_sum.h
+++ b/sql/item_sum.h
@@ -1203,7 +1203,7 @@ class Item_avg_field_decimal :public Item_avg_field
enum_field_types field_type() const { return MYSQL_TYPE_NEWDECIMAL; }
enum Item_result result_type () const { return DECIMAL_RESULT; }
double val_real() { return val_real_from_decimal(); }
- longlong val_int() { return val_int_from_decimal(); }
+ longlong val_int() { return val_int_from_decimal(unsigned_flag); }
String *val_str(String *str) { return val_string_from_decimal(str); }
my_decimal *val_decimal(my_decimal *);
};
